mysql学习整理

MySQL 可以分为 Server 层和存储引擎层两部分，Server 层包括连接器、查询缓存、分析器、优化器、执行器等，而存储引擎层负责数据的存储和提取，架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。

连接器

连接器负责跟客户端建立连接、获取权限、维持和管理连接

在完成经典的TCP握手后，连接器就要开始认证身份，如果不对，就会提示Access denied for user，如果认证通过，连接器会到权限表里面查出你拥有的权限。之后，这个连接里面的权限判断逻辑，都将依赖于此时读到的权限（这就意味着，一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限）。

连接完成后，没有后续的动作，这个连接就处于空闲状态，可用show processlist命令查看（command为sleep就是空闲连接），客户端如果太长时间没动静，连接器就会自动将它断开，默认是8小时，如果被断开之后，客户端再次发送请求的话，就会收到一个错误提醒： Lost connection to MySQL server during query。

建立连接的过程通常是比较复杂的，所以我建议你在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。但全部使用长连接后， MySQL 占用内存涨得特别快，这是因为 MySQL 在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是 MySQL 异常重启了。

怎么解决这个问题呢？有两种方案：

定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。

MySQL 5.7 以上版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection 来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

查询缓存

MySQL 拿到一个查询请求后，会先到查询缓存看看，之前是不是执行过这条语句。

之前执行过的语句及其结果可能会以 key-value 对的形式，被直接缓存在内存中。key 是查询的语句，value 是查询的结果。

查询缓存往往弊大于利，查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。因此很可能你费劲地把结果存起来，还没使用呢，就被一个更新全清空了。

MySQL 也提供了这种“按需使用”的方式。你可以将参数 query_cache_type 设置成 DEMAND，MySQL 8.0 版本直接将查询缓存的整块功能删掉了。

分析器

MySQL 需要知道你要做什么，因此需要对 SQL 语句做解析

分析器先会做“词法分析”，MySQL 需要识别出里面的字符串分别是什么，代表什么。

根据词法分析的结果，语法分析器会根据语法规则，判断你输入的这个 SQL 语句是否满足 MySQL 语法。如果你的语句不对，就会收到“You have an error in your SQL syntax”的错误提醒，一般语法错误会提示第一个出现错误的位置，所以你要关注的是紧接“use near”的内容（如下：）

mysql> elect * from t where ID=1;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'elect * from t where ID=1' at line 1

优化器

经过了分析器，MySQL 就知道你要做什么了。在开始执行之前，还要先经过优化器的处理。

优化器是在表里面有多个索引的时候，决定使用哪个索引；或者在一个语句有多表关联（join）的时候，决定各个表的连接顺序。比如你执行下面这样的语句，这个语句是执行两个表的 join：

mysql> select * from t1 join t2 using(ID)  where t1.c=10 and t2.d=20;

• 既可以先从表 t1 里面取出 c=10 的记录的 ID 值，再根据 ID 值关联到表 t2，再判断 t2 里面 d 的值是否等于 20。
• 也可以先从表 t2 里面取出 d=20 的记录的 ID 值，再根据 ID 值关联到 t1，再判断 t1 里面 c 的值是否等于 10。

这两种执行方法的逻辑结果是一样的，但是执行的效率会有不同，而优化器的作用就是决定选择使用哪一个方案。

执行器

执行器就如字面意思是执行sql语句

执行器执行之前，会先判断对表有没有执行查询的权限，如果没有，就会返回没有权限的提示（如果是命中查询缓存的话，就会在上一步优化器之前调用 precheck验证权限）

如果有权限的话，就会调用表定义的引擎接口打开表继续执行，如InnoDB 引擎，会从表中取第一行，判断值是不是要查询的结果（不是则跳过），继续读取下一行，当遍历表的所有结果将返回记录集。

执行器有一个rows_examined 的字段表示sql语句执行过程中扫描的行数，但这个值是执行器每次调用引擎获取数据行的时候累加的。在某些场景下与引擎扫描行数并不一致，原因：执行器调用一次，在引擎内部则扫描了多行。

总结

sql是怎么执行的？

1、先通过连接器连接上mysql服务器，并获取当前权限

2、通过查询缓存命中最新的查询，返回结果(此步骤8.0版本将不再用)

3、通过分析器分析要做什么操作（select？update？delete……）

4、通过优化器确定要怎么做（先明确表的操作顺序、语句的执行顺序）

5、通过执行器调用表引擎读取处理数据，并返回结果（执行之前会做权限检验）