image.png|500

从上图可以看出， MySQL 主要由下面几部分构成：

  • 连接器： 身份认证和权限相关(登录 MySQL 的时候)。
  • 查询缓存： 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。
  • 分析器： 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。
  • 优化器： 按照 MySQL 认为最优的方案去执行。
  • 执行器： 执行语句，然后从存储引擎返回数据。 执行语句之前会先判断是否有权限，如果没有权限的话，就会报错。
  • 插件式存储引擎：主要负责数据的存储和读取，采用的是插件式架构，支持 InnoDB、MyISAM、Memory 等多种存储引擎

一条SQL执行前会经历的过程

为了SQL能够正常执行，首先会先去获取一个数据库连接对象，上篇关于MySQL的架构篇曾聊到过，MySQL连接层中会维护着一个名为「连接池」的玩意儿，但相信大家也都接触过「数据库连接池」这个东西，比如Java中的C3P0、Druid、DBCP....等各类连接池。

那此时在这里可以思考一个问题，为什么数据库自己维护了连接池的情况下，在MySQL客户端中还需要再次维护一个数据库连接池呢？接下来一起聊一聊。

首先会经过《TCP的三次握手过程》，对于这块就不再细聊了，毕竟之前聊过很多次了。当网络连接建立成功后，也就等价于在MySQL中创建了一个客户端会话，然后会发生下图一系列工作：

image.png

  • ①首先会验证客户端的用户名和密码是否正确：
    • 如果用户名不存在或密码错误，则抛出1045的错误码及错误信息。
    • 如果用户名和密码验证通过，则进入第②步。
  • ②判断MySQL连接池中是否存在空闲线程：
    • 存在：直接从连接池中分配一条空闲线程维护当前客户端的连接。
    • 不存在：创建一条新的工作线程（映射内核线程、分配栈空间….）。
  • ③工作线程会先查询MySQL自身的用户权限表，获取当前登录用户的权限信息并授权。

到这里为止，执行SQL前的准备工作就完成了，已经打通了执行SQL的通道，下一步则是准备执行SQL语句，工作线程会等待客户端将SQL传递过来。

一条查询SQL的执行过程

经过连接层的一系列工作后，接着客户端会将要执行的SQL语句通过连接发送过来，然后会进行MySQL服务层进行处理，不过根据用户的操作不同，MySQL执行SQL语句时也会存在些许差异，这里是指读操作和写操作，两者SQL的执行过程并不相同，下面先来看看select语句的执行过程。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- SQL语句
SELECT user_id FROM `zz_user` WHERE user_sex = "男" AND user_name = "竹子④号";

-- 表数据
+---------+--------------+----------+-------------+
| user_id | user_name    | user_sex | user_phone  |
+---------+--------------+----------+-------------+
|       1 | 竹子①号      | 男       | 18888888888 |
|       2 | 竹子②号      | 男       | 13588888888 |
|       3 | 竹子③号      | 男       | 15688888888 |
|       4 | 熊猫①号      | 女       | 13488888888 |
|       5 | 熊猫②号      | 女       | 18588888888 |
|       6 | 竹子④号      | 男       | 17777777777 |
|       7 | 熊猫③号      | 女       | 16666666666 |
+---------+--------------+----------+-------------+

先上个SQL执行的完整流程图，后续再逐步分析每个过程：

image.png|575

  • ①先将SQL发送给SQL接口，SQL接口会对SQL语句进行哈希处理。
  • ②SQL接口在缓存中根据哈希值检索数据，如果缓存中有则直接返回数据。
  • ③缓存中未命中时会将SQL交给解析器，解析器会判断SQL语句是否正确：
    • 错误：抛出1064错误码及相关的语法错误信息。
    • 正确：将SQL语句交给优化器处理，进入第④步。
  • ④优化器根据SQL制定出不同的执行方案，并择选出最优的执行计划。
  • ⑤工作线程根据执行计划，调用存储引擎所提供的API获取数据。
  • ⑥存储引擎根据API调用方的操作，去磁盘中检索数据（索引、表数据….）。
  • ⑦发生磁盘IO后，对于磁盘中符合要求的数据逐条返回给SQL接口。
  • ⑧SQL接口会对所有的结果集进行处理（剔除列、合并数据….）并返回。

上述是一个简单的流程概述，一般情况下查询SQL的执行都会经过这些步骤，下面再将每一步拆开详细聊一聊。

SQL接口会干的工作

当客户端将SQL发送过来之后，SQL紧接着会交给SQL接口处理，首先会对SQL做哈希处理，也就是根据SQL语句计算出一个哈希值，然后去「查询缓存」中比对，如果缓存中存在相同的哈希值，则代表着之前缓存过相同SQL语句的结果，那此时则直接从缓存中获取结果并响应给客户端。

在这里，如果没有从缓存中查询到数据，紧接着会将SQL语句交给解析器去处理。

SQL接口除开对SQL进行上述的处理外，后续还会负责处理结果集（稍后分析）。

解析器中会干的工作

解析器收到SQL后，会开始检测SQL是否正确，也就是做词法分析、语义分析等工作，在这一步，解析器会根据SQL语言的语法规则，判断客户端传递的SQL语句是否合规，如果不合规就会返回1064错误码及错误信息：

ERROR 1064 (42000): You have an error in your SQL syntax; check....

但如果SQL语句没有问题，此时就会对SQL语句进行关键字分析，也就是根据SQL中的SELECT、UPDATE、DELETE等关键字，先判断SQL语句的操作类型，是读操作还是写操作，然后再根据FROM关键字来确定本次SQL语句要操作的是哪张表，也会根据WHERE关键字后面的内容，确定本次SQL的一些结果筛选条件…..。

总之，经过关键字分析后，一条SQL语句要干的具体工作就会被解析出来。

解析了SQL语句中的关键字之后，解析器会根据分析出的关键字信息，生成对应的语法树，然后交给优化器处理。

在这一步也就相当于Java中的.java源代码变为.class字节码的过程，目的就是将SQL语句翻译成数据库可以看懂的指令。

优化器中会干的工作

经过解析器的工作后会得到一个SQL语法树，也就是知道了客户端的SQL大体要干什么事情了，接着优化器会对于这条SQL，给出一个最优的执行方案，也就是告诉工作线程怎么执行效率最高、最节省资源以及时间。

优化器最开始会根据语法树制定出多个执行计划，然后从多个执行计划中选择出一个最好的计划，交给工作线程去执行，但这里究竟是如何选择最优执行计划的，相信大家也比较好奇，那此时我们结合前面给出的案例分析一下。

1
SELECT user_id FROM `zz_user` WHERE user_sex = "男" AND user_name = "竹子④号";

先来看看，对于这条SQL而言，总共有几种执行方案呢？答案是两种。

  • ①先从表中将所有user_sex="男"的数据查出来，再从结果中获取user_name="竹子④号"的数据。
  • ②先从表中寻找user_name="竹子④号"的数据，再从结果中获得user_sex="男"的数据。

再结合前面给出的表数据，暂且分析一下上述两种执行计划哪个更好呢？

1
2
3
4
5
6
7
8
9
10
11
12
+---------+--------------+----------+-------------+
| user_id | user_name    | user_sex | user_phone  |
+---------+--------------+----------+-------------+
|       1 | 竹子①号      | 男       | 18888888888 |
|       2 | 竹子②号      | 男       | 13588888888 |
|       3 | 竹子③号      | 男       | 15688888888 |
|       4 | 熊猫①号      | 女       | 13488888888 |
|       5 | 熊猫②号      | 女       | 18588888888 |
|       6 | 竹子④号      | 男       | 17777777777 |
|       7 | 熊猫③号      | 女       | 16666666666 |
+---------+--------------+----------+-------------+

如果按照第①种方案执行，此时会先得到四条user_sex="男"的数据，然后再从四条数据中查找user_name="竹子④号"的数据。

如果按照第②中方案执行，此时会直接得到一条user_name="竹子④号"的数据，然后再判断一下user_sex是否为”男”，是则直接返回，否则返回空。

相较于两种执行方案的过程，前者需要扫一次全表，然后再对结果集逐条判断。而第二种方案扫一次全表后，只需要再判断一次就可以了，很明显可以感知出：第②种执行计划是最优的，因此优化器会给出第②种执行计划。

经过上述案例的讲解后，大家应该能够对优化器的工作进一步理解。但上述案例仅是为了帮助大家理解，实际的SQL优化过程会更加复杂，例如多表join查询时，怎么查更合适？单表复杂SQL查询时，有多条索引可以走，走哪条速度最快….，因此一条SQL的最优执行计划，需要结合多方面的优化策略来生成，例如MySQL优化器的一些优化准则如下：

  • ❶多条件查询时，重排条件先后顺序，将效率更好的字段条件放在前面。
  • ❷当表中存在多个索引时，选择效率最高的索引作为本次查询的目标索引。
  • ❸使用分页Limit关键字时，查询到对应的数据条数后终止扫表。
  • ❹多表join联查时，对查询表的顺序重新定义，同样以效率为准。
  • ❺对于SQL中使用函数时，如count()、max()、min()...，根据情况选择最优方案。
  • max()函数：走B+树最右侧的节点查询（大的在右，小的在左）。
  • min()函数：走B+树最左侧的节点查询。
  • count()函数：如果是MyISAM引擎，直接获取引擎统计的总行数。
  • ......
  • ❻对于group by分组排序，会先查询所有数据后再统一排序，而不是一开始就排序。
  • ❼......

总之，根据SQL不同，优化器也会基于不同的优化准则选择出最佳的执行计划。但需要牢记的一点是：**MySQL虽然有优化器，但对于效率影响最大的还是SQL本身，因此编写出一条优秀的SQL，才是提升效率的最大要素**。

存储引擎中会干的工作

**经过优化器后，会得到一个最优的执行计划，紧接着工作线程会根据最优计划，去依次调用存储引擎提供的API**，在上篇文章中提到过，存储引擎主要就是负责在磁盘读写数据的，不同的存储引擎，存储在本地磁盘中的数据结构也并不相同，但这些底层实现并不需要MySQL的上层服务关心，因为上层服务只需要负责调用对应的API即可，存储引擎的API功能都是相同的。

工作线程根据执行计划调用存储引擎的API查询指定的表，最终也就是会发生磁盘IO，从磁盘中检索数据，当然，检索的数据有可能是磁盘中的索引文件，也有可能是磁盘中的表数据文件，这点要根据执行计划来决定，我们只需要记住，经过这一步之后总能够得到执行结果即可。

但有个小细节，还记得最开始创建数据库连接时，对登录用户的授权步骤嘛？当工作线程去尝试查询某张表时，会首先判断一下线程自身维护的客户端连接，其登录的用户是否具备这张表的操作权限，如果不具备则会直接返回权限不足的错误信息。

不过存储引擎从磁盘中检索出目标数据后，并不会将所有数据全部得到后再返回，而是会逐条返回给SQL接口，然后会由SQL接口完成最后的数据聚合工作，对于这点稍后会详细分析。

下来再来看看写入SQL的执行过程，因为读取和写入操作之间，也会存在些许差异。

一条写入SQL的执行过程

假设此时要执行下述这一条写入类型的SQL语句（还是基于之前的表数据）：

1
UPDATE `zz_user` SET user_sex = "女" WHERE user_id = 6;

image.png
从上图来看，相较于查询SQL，写操作的SQL执行流程明显会更复杂一些，这里也先简单总结一下每一步流程，然后再详细分析一下其中一些与查询SQL中不同的步骤：

  • ①先将SQL发送给SQL接口，SQL接口会对SQL语句进行哈希处理。
  • ②在缓存中根据哈希值检索数据，如果缓存中有则将对应表的所有缓存全部删除。
  • ③经过缓存后会将SQL交给解析器，解析器会判断SQL语句是否正确：
    • 错误：抛出1064错误码及相关的语法错误信息。
    • 正确：将SQL语句交给优化器处理，进入第④步。
  • ④优化器根据SQL制定出不同的执行方案，并择选出最优的执行计划。
  • ⑤在执行开始之前，先记录一下undo-log日志和redo-log(prepare状态)日志。
  • ⑥在缓冲区中查找是否存在当前要操作的行记录或表数据（内存中）：
    • 存在：
      • ⑦直接对缓冲区中的数据进行写操作。
      • ⑧然后利用Checkpoint机制刷写到磁盘。
    • 不存在：
      • ⑦根据执行计划，调用存储引擎的API。
      • ⑧发生磁盘IO，对磁盘中的数据做写操作。
  • ⑨写操作完成后并且事务提交后，记录bin-log日志，同时将redo-log日志中的记录改为commit状态。
  • ⑩将SQL执行耗时及操作成功的结果返回给SQL接口，再由SQL接口返回给客户端。

整个写SQL的执行过程，前面的一些步骤与查SQL执行的过程没太大差异，唯一一点不同的在于缓存那里，原本查询时是从缓存中尝试获取数据。而写操作时，由于要对表数据发生更改，因此如果在缓存中发现了要操作的表存在缓存，则需要将整个表的所有缓存清空，确保缓存的强一致性。

OK~，除开上述这点区别外，另外多出了唯一性判断、一个缓冲区写入，以及几个写入日志的步骤，接下来一起来聊聊这些。

唯一性判断主要是针对插入、修改语句来说的，因为如果表中的某个字段建立了唯一约束或唯一索引后，当插入/修改一条数据时，就会先检测一下目前插入/修改的值，是否与表中的唯一字段存在冲突，如果表中已经存在相同的值，则会直接抛出异常，反之会继续执行。

一条SQL执行完成后是如何返回的？

一条「读SQL」或「写SQL」执行完成后，由于SQL操作的属性不同，两者之间也会存在差异性，

读类型的SQL返回

前面聊到过，MySQL执行一条查询SQL时，数据是逐条返回的模式，因为如果等待所有数据全部查出来之后再一次性返回，必然会导致撑满内存。

不过这里的返回，并不是指返回客户端，而是指返回SQL接口，因为从磁盘中检索出目标数据时，一般还需要对这些数据进行再次处理，举个例子理解一下。

1
SELECT user_id FROM `zz_user` WHERE user_sex = "男" AND user_name = "竹子④号";

还是之前那条查询SQL，这条SQL要求返回的结果字段仅有一个user_id，但在磁盘中检索数据时，会直接将这个字段单独查询出来吗？并不是的，而是会将整条行数据全部查询出来，如下：

1
2
3
4
5
6
+---------+--------------+----------+-------------+
| user_id | user_name    | user_sex | user_phone  |
+---------+--------------+----------+-------------+
|    6    |   竹子④号    |    男    | 17777777777 |
+---------+--------------+----------+-------------+

从行记录中筛选出最终所需的结果字段，这个工作是在SQL接口中完成的，也包括多表联查时，数据的合并工作，同样也是在SQL接口完成，其他SQL亦是同理。

还有一点需要牢记：就算没有查询到数据，也会将执行状态、执行耗时这些信息返回给SQL接口，然后由SQL接口向客户端返回NULL。

不过当查询到数据后，在正式向客户端返回之前，还会顺手将结果集放入到缓存中。

写类型的SQL返回

写SQL执行的过程会比读SQL复杂，但写SQL的结果返回却很简单，写类型的操作执行完成之后，仅会返回执行状态、受影响的行数以及执行耗时，比如：

1
UPDATE `zz_user` SET user_sex = "女" WHERE user_id = 6;

这条SQL执行成功后，会返回Query OK, 1 row affected (0.00 sec)这组结果，最终返回给客户端的则只有「受影响的行数」，如果写SQL执行成功，这个值一般都会大于0，反之则会等于0。

执行结果是如何返回给客户端的？

对于这个问题的答案其实很简单，由于执行当前SQL的工作线程，本身也维护着一个数据库连接，这个数据库连接实际上也维持着客户端的网络连接，如下：

image.png|575

当结果集处理好了之后，直接通过Host中记录的地址，将结果集封装成TCP数据报，然后返回即可。

数据返回给客户端之后，除非客户端主动输入exit等退出连接的命令，否则连接不会立马断开。

如果要断开客户端连接时，又会经过TCP四次挥手的过程。

不过就算与客户端断开了连接，MySQL中创建的线程并不会销毁，而是会放入到MySQL的连接池中，等待其他客户端复用当前连接。一般情况下，一条线程在八小时内未被复用，才会触发MySQL的销毁工作。