MySQL基础架构（三）一条查询语句的一生

目录

1 执行流程概述

2 连接器

3 分析器

4 优化器

5 执行器

6 存储引擎

1 执行流程概述

前面我们提到过MySQL的结构可以大致分为MySQL server层和存储引擎层。一条SQL查询语句会的请求当然也会经过server层和存储引擎层。

MySQL数据库执行一条查询语句的过程涉及多个阶段。以下是一条查询语句在MySQL中的大致执行过程：

• 连接器:
• 当客户端尝试与MySQL服务器建立连接时，服务器首先验证连接请求是否来自允许的主机，并检查提供的用户名和密码是否有效。
• 连接成功建立后由连接器管理这些连接。
• 分析器:
• MySQL使用SQL解析器进行词法分析，将查询语句分解为多个令牌或词素。
• 语法分析器然后根据这些词素生成一个所称的“解析树”。
• 优化器:
• 这是查询处理中最重要的部分。查询优化器的任务是找到执行查询的最有效方式，尤其是对于涉及复杂连接、子查询和其他高级功能的查询。
• 优化器会考虑各种因素，如可用的索引、表的统计信息（例如行数、数据分布等）和查询的结构。
• 它可能会重写查询或更改查询的执行顺序来找到最佳的执行路径。
• 执行器:
• 根据查询优化器确定的计划，MySQL现在开始实际执行查询。
• 执行器调用存储引擎提供的接口读写数据。

2 连接器

客户端工具通过连接器与服务端建立连接。常用客户端如下：

//JDBC 创建连接
Connection conn = DriverManager.getConnection(url, user, password);


#MySQL Client 创建连接
mysql -h$ip -P$port -u$user -p$pwd
mysql -u$user -p$pwd -S $socket

如果密码不对，会收到错误如下所示：

ERROR 1045 (28000): Access denied for user 'xxx'@'xxx' (using password: YES)

连接成功后，可以在MySQL服务端看到当前连接：

mysql> show processlist;
+----+------+-----------------+------+---------+---------+----------+------------------+
| Id | User | Host            | db   | Command | Time    | State    | Info             |
+----+------+-----------------+------+---------+---------+----------+------------------+
|  6 | root | localhost:52466 | test | Sleep   | 1196053 |          | NULL             |
|  7 | root | localhost:41184 | NULL | Sleep   |      25 |          | NULL             |
|  8 | root | localhost:52564 | NULL | Query   |       0 | starting | show processlist |
+----+------+-----------------+------+---------+---------+----------+------------------+

这里需要注意的是，连接如果在长时间不使用后会自动断开，这时候再发送sql请求就会报错。所以在使用长会话生产环境，我们通常建议设置“连接保活机制”，如果一个连接暂时不用，那么也每5分钟发送一个简单sql如select 1;保持连接的活动状态。当然也可以每次不用的时候手工关闭connection，每次使用的时候再新建connection，但是这样会对性能由损耗。

3 分析器

对于一条SQL，MySQL 需要判断SQL语法是否正确，以及SQL要做什么，因此需要对 SQL 语句做解析。分析器会对SQL做如下分析：

1. 词法分析，生成语法分析需要的token。
2. 语法分析，生成语法树。

如果语法分析失败，会报错如下：

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 'xxx' at line 1

如果sql出现语法错误，重点关注'xxx'内容。

4 优化器

数据库查询优化器的主要目的是为给定的查询找到最有效的执行计划。

比如下面的SQL就有不止一种执行方式:

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

• 执行方式1：先从表 t1 里面取出 c=10 的记录的 ID 值，再根据 ID 值关联到表 t2，再判断 t2 里面 d 的值是否等于 20。
• 执行方式2：先从表 t2 里面取出 d=20 的记录的 ID 值，再根据 ID 值关联到 t1，再判断 t1 里面 c 的值是否等于 10。

两种执行方式耗时差别可能非常大，以需要优化器来帮助我们选择合适的执行方式。

大体上，优化器可以分为两大类：基于规则的优化器（Rule-Based Optimizer, RBO）和基于成本的优化器（Cost-Based Optimizer, CBO）。

• 基于规则的优化器 (RBO)：它根据一组预定义的规则来选择执行计划。这些规则是基于通用的数据库设计和查询原则。
• 基于成本的优化器 (CBO)：CBO使用统计信息（例如表的大小、数据分布、索引的选择性等）来估计执行不同查询计划的“成本”。CBO的目标是找到具有最低成本的执行计划。

MySQL使用的优化器是CBO，在生成执行计划时各种操作的成本，取成本最低的操作作为最终的执行计划。

MySQL关于成本的计算是一个单独的话题，后面有机会再讲解。

为了生成成本最低的执行计划，优化器需要做到如下两点：

• 若查询只涉及到单表，计算走不同索引和全表扫描的代价，找出成本最低的方案。
• 若查询是多表联查，选择最优的表连接顺序，同时为驱动表和被驱动表选择成本最低的访问方法。

使用explain查看优化器生成的执行计划：

对于执行计划的详细介绍以及优化在后面性能优化课程中会讲解。

5 执行器

MySQL根据执行计划给出的指令逐步执行。在根据执行计划逐步执行的过程中，有大量的操作需要通过调用存储引擎实现的接口来完成。

一个执行器执行全表扫描流程示例：

1. 调用存储引擎接口取这个表的第一行，判断是否满足条件，如果不是则跳过，如果是则将这行存在结果集中。
2. 调用引擎接口取“下一行”，重复相同的判断逻辑，直到取到这个表的最后一行。
3. 执行器将上述遍历过程中所有满足条件的行组成的记录集作为结果集返回给客户端。

对于有索引的表，调用接口会稍有变化，不过大致类似。

6 存储引擎

当执行器与InnoDB存储引擎交互时，InnoDB有其特定的内部机制来处理请求，如下：

1. 请求解释: 当InnoDB收到来自执行器的请求时，首先会解释该请求的性质。这可能是一个全表扫描、索引查找或基于主键的查找。
2. Buffer Pool查找: 在检索数据之前，InnoDB首先在其Buffer Pool中查找该数据。Buffer Pool是InnoDB的中心缓存，用于存储表数据和索引数据。它帮助减少对物理磁盘的访问，从而加速查询性能。

• 如果所需的数据页已在Buffer Pool中，则直接从那里读取数据。
• 如果数据页不在Buffer Pool中，则需要从磁盘中读取。

1. 磁盘读取: 如果必要，InnoDB会从磁盘中读取所需的数据页。然后，这些数据页通常会被加载到Buffer Pool中，以便将来的请求可以更快地访问它们。
2. 数据返回: 一旦得到了所需的数据，InnoDB将其返回给执行器。

最后我们来看一下InnoDB的架构：