一条sql查询语句执行过程解析

客户端

server层：（所有跨存储引擎的操作均在这一层完成，包含下面mysql核心功能及内置函数均在这一层完成）

连接器、查询缓存->分析器、优化器、执行器

存储层：（负责数据的存储和提取，其架构模式是插件式的，支持innodb、myisam、memory等多个存储引擎）

存储引擎

总结：不同的存储引擎共用一个server层，即连接器到执行器的部分是一样的。

连接器

连接器会校验用户的账号和密码，验证通过后，然后会去权限表获取用户拥有的权限。之后，这个连接里面的权限判断都将依赖此时读到的权限。这就意味着，用户连接成功后，再去修改权限，在当前连接下是不生效的，只有再新建连接才会使用新的权限配置。

连接完成后，如果后续没有操作，则该连接处于sleep空闲状态。可通过show processlist查看连接的状态

客户端如果长时间没有动静，则连接器会自动断开，具体时间由wait_timeout控制，默认是8小时。

数据库连接，分长连接和短连接两种。

长连接：数据库连接成功后，如果客户端一直有请求，则会一直使用同一个连接。

短连接：每次执行完很少的几次连接后，会自动断开。下次查询会再重建一个。

建立连接的过程通常很复杂，所以尽量减少连接的动作，也就是尽量使用长连接。

使用长连接，mysql占用内存会涨的比较快。因为mysql在执行过程中临时使用的内存是管理在连接对象里面的，只有在连接断开后，内存再会被释放。如果长连接累计下来，可能导致内存占用过大，被系统强制杀掉（OOM），从现象上来看就是mysql异常重启。

解决方案：

1.定期断开连接，使用一段时间后，或程序执行过一个占用内存比较大的查询后，断开连接，之后查询再重新连。

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

show processlist;

查询缓存

建立连接后，会优先查询缓存，若对应缓存存在，则直接返回结果。

查询缓存已key->value的形式存储在内存中，key为查询的sql，value为查询的结果。

若有对一个表进行更新，那么这个表的所有查询缓存均会失效。因此，查询缓存弊往往大于利，不建议使用。除非对于系统配置类似这样不经常被修改的表（静态表），使用查询缓存更适合些。

好在mysql提供了“按需使用”的方式，可将参数query_cache_type设置为DEMAND，这样对于默认的sql语句就不适用查询缓存，若指定语句想使用，可用SQL_CACHE显式指定

mysql> select SQL_CACHE * from T where ID=10；

需要注意，mysql8.0版本直接将查询缓存整块功能删掉了。因此，mysql8.0及之后版本均不在支持查询缓存。

分析器

作用：知道你要做什么

没有命中查询缓存，则开始真正执行语句了。mysql需要知道你要做什么，因此需要对sql语句做解析。

分析器 先做 词法分析 ，识别出sql语句中的字符串分别是什么，代表什么。

再做 语法分析，根据语法规则，判断sql是否满足mysql语法规则。

列子：语句不对，返回报错

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

优化器

作用：知道该怎么做，确定最终的执行方案。

如在表里存在多个索引时，决定具体哪个索引；在具体执行sql时，决定执行的先后顺序（join关联多个表时，先执行A表的where条件或是B表的）

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

执行器

作用：开始执行语句

先判断是否有对执行表的权限

根据表的引擎定义，去使用引擎所提供的接口

mysql> select * from T where ID=10;

调用 InnoDB 引擎接口取这个表的第一行，判断 ID 值是不是 10，

如果不是则跳过，如果是则将这行存在结果集中；调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。

执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

这些接口都是引擎中已经定义好的。

在数据库的慢查询日志中看到一个 rows_examined 的字段，表示这个语句执行过程中扫描了多少行。这个值就是在执行器每次调用引擎获取数据行的时候累加的。在有些场景下，执行器调用一次，在引擎内部则扫描了多行，因此引擎扫描行数跟 rows_examined 并不是完全相同的