谈一谈一条SQL查询语句究竟是如何执行的?

本篇文章是基于《MySQL45讲》来写的个人理解与感悟。

理解

先看下图：

大体来说，MySQL可以分为Server层和存储引擎层两部分。就是对应着图中的两个圈。

server层包含查询缓存、分析器、优化器、执行器等，以及及所有的内置函数（如日期、时间…）所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

存储引擎层负责数据的存储和提取，而存储引擎架构模式是插件式的，有很多种，比如持InnoDB、MyISAM、Memory等，这也就意味着也就是说不同存储引擎共用一个server层。

从MySQL 5.5.5版本开始，InnoDB成为了默认存储引擎。

执行流程

执行一条select语句的时候首先会去建立连接【TCP三次握手连接】，这是通过连接器执行的。然后建立连接之后先去查询缓存中是否缓存了这条语句【key是查询语句，value是查询结果】。没缓存就去解析器进行分析，先会进行词法分析（因为MySQL需要识别出里面的字符串分别是什么，代表什么。），会把关键字和非关键字挑出来，然后再进行语法分析，看看是不是符合mysql的语法，如果符合的话就会构建一个语法树。

词法分析：输入的"select"这个关键字识别出来，这是一个查询语句。它也要把字符串“T”识别 成“表名T”，把字符串“ID”识别成“列ID”。

然后就会进入预处理阶段，检查 SQL 查询语句中的表或者字段是否存在【没有报错】，以及把*扩展为表中的所有列。之后就会指定执行计划，也就是交给优化器进行优化【选择实用的索引 or 多表join的时候，决定join表的连接顺序】，然后再去执行器进行执行。而执行器其实是去调用存储引擎进行交互的，也就是用执行器来操纵引擎，然后获取记录的，然后写入查询缓存。执行器如何操纵引擎呢？就是首先判断你有没有权限操作你选择的那张表，没权限报错，有权限的话那么就会根据你那张表的存储引擎来调用相关的接口，返回数据。

总结：执行一条 SQL 查询语句，期间发生了什么？

• 连接器：建立连接，管理连接、校验用户身份；
• 查询缓存：查询语句如果命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
• 解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
• 执行 SQL：执行 SQL 共有三个阶段：
  • 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
  • 优化阶段：基于查询成本的考虑， 选择查询成本最小的执行计划；
  • 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端；

衍生知识

1、如何查看 MySQL 服务被多少个客户端连接了？

如果你想知道当前 MySQL 服务被多少个客户端连接了，可以执行 show processlist 命令进行查看。

show processlist：显示当前正在运行的所有连接和进程的信息。

比如上图的显示结果，共有两个用户名为 root 的用户连接了 MySQL 服务，其中 id 为 6 的用户的 Command 列的状态为 Sleep ，这意味着该用户连接完 MySQL 服务就没有再执行过任何命令，也就是说这是一个空闲的连接，并且空闲的时长是 736 秒（ Time 列）。

2、客户端如果太长时间没动静，连接器就会自动将它断开【空闲连接】。

这个时间是由参数wait_timeout控制的，默认值是8小时。如果空闲连接超过了这个时间，连接器就会自动将它断开。

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)

当然，我们自己也可以手动断开空闲的连接，使用的是 kill connection + id 的命令。

mysql> kill connection +6;
Query OK, 0 rows affected (0.00 sec)

一个处于空闲状态的连接被服务端主动断开后，这个客户端并不会马上知道，等到客户端在发起下一个请求的时候，才会收到这样的报错“ERROR 2013 (HY000): Lost connection to MySQL server during query”。

3、为什么长连接后可能会导致MySQL占用内存涨得特别快？

因为 MySQL 在执行查询过程中临时使用内存管理连接对象，这些连接对象资源只有在连接断开时才会释放。如果长连接累计很多，将导致 MySQL 服务占用内存太大，有可能会被系统强制杀掉，这样会发生 MySQL 服务异常重启的现象。

4、如何解决内存占用过大问题？

1. 定期断开长连接。既然断开连接后就会释放连接占用的内存资源，那么我们可以定期断开长连接。所以通常是我们的使用一段时间之后或者程序里面判断执行过一个占用内存的大查询后，就直接断开连接，然后如果以后进行查询的时候再重连。
2. 客户端主动重置连接。MySQL 5.7 版本实现了 mysql_reset_connection() 函数的接口，注意这是接口函数不是命令。那么当客户端每次执行一个比较大的操作后，可以通过执行 mysql_reset_connection 来重新初始化连接资源，达到释放内存的效果。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

5、在MySQL8.0之后查询缓存那一步功能被删除了，为什么？

查询缓存的失效非常频繁，只要有对一个表的更新，这个表上所有的查询缓存都会被清空。
因此很可能缓存之后，还没使用，就被一个更新全清空了。对于更新压力大的数据库来说，查询缓存的命中率会非常低，所以相当于缓存了寂寞…

除非业务就是有一张静态表，很长时间才会更新一次。
比如，一个系统配置表，那这张表上的查询才适合使用查询缓存。

如果大家还想看看更新语句的执行流程，那么请点击下方链接：谈一谈一条SQL的更新语句究竟是如何执行的?在这篇文章里面还介绍了保证事务的持久性和一致性的关键机制：两阶段提交机制，有兴趣的可以看看！

最后

如果小伙伴们觉得我写的文章不错的话，那么请给我点点关注，我们下次见！