详解MySQL中一条SQL执行过程

MySQL基本架构

如下图所示，从宏观角度来说MySQL架构可以分为server层和存储引擎层，其中Server层包含如下:

1. 连接器:进行身份认证和权限相关校验。
2. 查询缓存:MySQL8.0已废弃，查询缓存主要是用于提高查询效率而加的一层缓存。
3. 分析器:对SQL执行动作、语法、词法进行分析。
4. 优化器:对要被执行的SQL进行优化。
5. 执行器:执行SQL查询语句，然后从存储引擎返回结果。

接下来说说存储引擎，对于MySQL而言存储引擎是支持插拔的，常见的存储引擎有myisam、innodb、memory，而MySQL默认的使用的是innodb。

详解MySQL各层的工作分工

MySQL客户端和服务端的通信协议

对于MySQL而言，客户端和服务端之间采用的是一种半双工的通信协议，这样就意味着同一时刻要么客户端向服务端发送数据，要么服务端向客户端发送数据。这意味着客户端必须完整的收到服务端响应的数据才能断开连接。

这个交互流程也在告诉我们，进行大量数据查询的时候，若无必要尽可能使用limit进行分页查询，避免这种全双工的通信方式导致客户端接收导致资源长时间的占用。

连接器

主要判断用户登录的账户密码是否正确，如果账户密码都正确，则进行权限查询，注意在本次连接期间只要不断开，无论外界如何修改权限，这个会话的权限都是以连接器查询到的为主。

查询缓存

MySQL8已经废弃的功能，这个功能常用于结果的缓存复用以提高查询性能，例如我们进行select * from table where id=1的查询。第一次发现缓存中没有，就从数据库中查出来并放到缓存中下次可以在复用。
MySQL8之所以废弃是因为数据库中的数据经常更新导致缓存失效，就需要清空这个缓存，这期间和开销是非常没必要的，所以索性废掉这个功能。

分析器

分析器主要是负责sql解析和预处理，它会将客户端发来的查询一句进行解析生成一颗解析树，然后解析器根据自定义规则对sql语句进行词法和语法分析。

1. 词法分析:分析关键字是否拼写有误，并通过关键字判断这条SQL做什么。
2. 语法分析:对这条SQL语句的语法进行检查。

优化器

分析器分析无误之后，说明这条语句是可以正常执行的。MySQL优化器就会通过分析找出成本最小的一种方式生成执行计划，交由执行器执行。

对此，我们这里不妨补充一下MySQL能够自己处理的一些优化类型:

1. 将外连接转为内连接:某些场景之下，我们可能会用到外连接，但是在where或者库表结构的调整之后，我们的左外连接后者右外连接可能不存在null的连接。
   例如下面这段sql，我们对table2进行左外连接，但是我们条件关联之后，table1对应的id值在table2中都有，那么查询优化器可能就会对其进行优化，会将其转换为内连接，更加精确的去匹配索要查询的行避免没必要的扫描。

SELECT *
FROM table1
LEFT JOIN table2
ON table1.id = table2.id;

举个例子，上面的sql如果table1对应的id在table2中都有，那么sql语句就会变成这样

SELECT *
FROM table1
LEFT JOIN table2
ON table1.id = table2.id
WHERE table2.id IS NOT NULL;

然后优化器就会将其优化成这样

SELECT *
FROM table1
inner JOIN table2
ON table1.id = table2.id
WHERE table2.id IS NOT NULL;

2. 使用代数等价变换规则，例如我们的查询条件是5=5 and a>5,那么MySQL就会将其优化为:a>5，再比如说我们有这样一条SQL，条件语句为(a<b and b=c) and a=5，那么MySQL就会将其优化为: b > 5 and b=c

3. 优化min、max，对于建立索引的数据表来说，使用索引所在列的进行最大值和最小值查询时，MySQL优化器会将这种sql判定为常数查询,例如笔者建立的下面这张表，我们将table1的id设置为索引。
   然后查询下面这句sql:

SELECT min(id)
FROM table1;

使用explain查看其执行计划，可以看到执行计划显示Select tables optimized away，这就意味查询时它已经将表移除，而是用一个常数查询来代替。

3. 预估并转为为常数表达式:最典型的例子就select * from table1 where id=1+2，MySQL优化器就会将其转为select * fromt table1 where id=3。
4. 索引扫描:这个无需多说，当要查询的列都包含在索引中时，无需进行回表查询，避免没必要的IO操作。
5. 提前终止查询:对于limit查询而言，MySQL优化器会在查询到需要的数据时直接终止查询，还有一些比较特殊的，例如对于某些不可能的条件，MySQL优化器也会提前将其终止，例如我们将tbale1的id设置为主键，然后键入下面这句查询语句。

Select tables optimized away

那么执行计划就会显示Impossible WHERE从而提前终止查询:

执行器

对用户进行权限校验，若权限校验不通过则报错，然后执行器就会根据优化器优化后的执行计划(这里的执行计划是一个数据结构)，执行器根据这个数据结构顺序调用存储引擎提供的API进行数据查询，并将查询结果返回给客户端，从而完成一次完整的SQL查询。

用两条完整的sql走一遍上述的流程

了解SQL执行过程之后，我们不妨通过一个实际的例子带入一下了解全过程。

查询语句的执行流程

sql如下所示:

select * from table where b=1 and a=2;

按照我们上文所说的过程:

1. 校验用户账户密码是否正确，查询权限
2. 查询缓存(mysql8.0之前)，若有数据则直接返回，反之下一步
3. 分析器进行词法、语法分析。
4. MySQL优化器进行优化，以本SQL为例，假如我们创建了一个联合索引(a,b)，那么优化器就会遵循最左匹配原则将a,b条件进行调换。

5. 进行权限校验，若有权限执行器进行查询，将结果从引擎取出返回。

更新语句的执行流程

更新语句我们示例SQL如下：

update table set a=1  where b=1;

步骤还是一样:

1. 连接器的工作，不多赘述
2. 查询缓存，若有则直接操作这条数据(mysql8不走这一步)
3. 分析器的工作，不多赘述
4. 进行更新操作，首先调用引擎API，将这个修改写入内存中，同时记录redo log，此时redo log是prepare状态，然后执行器执行操作，完成后提交事务成功，写入bin log，最后redo log更新为commit。
5. 更新完成。

参考文献

SQL语句在MySQL中的执行过程

高性能MySQL（第4版）