MySQL-查询执行流程

MySQL基础架构

以一条查询语句为例，通过查询语句在 MySQL 中的执行流程，了解 MySQL 的基础架构以及各组件模块的基本作用。

select * from user where ID=10；

图片引用自：小林coding：图解MySQL专栏

如图是MySQL的基本架构示意图，MySQL 可以大致分为 Server 层和存储引擎层两大部分。

Server层包括连接器、查询缓存、分析器、优化器、执行器等，涵盖MySQL的大多数核心服务功能，以及所有的内置函数（如日期、时间、数学和加密函数等），所有跨存储引擎的功能都在这一层实现，比如存储过程、触发器、视图等。

存储引擎层负责数据的存储和提取。其架构模式是插件式的，支持 InnoDB、MyISAM、Memory 等多个存储引擎。现在最常用的存储引擎是InnoDB，它从MySQL 5.5.5版本开始成为了默认存储引擎。

正常的建表语句如果不指定默认引擎就是 InnoDB，也可以在建表语句后通过 ENGINE=MyISAM 指定为 MyISAM 或者其他引擎

连接器

连接器：负责跟客户端建立连接、获取权限、维持和管理连接，主要是登陆权限相关；

使用 MySQL 首先要先连接 MySQL 服务，才能执行后续的语句操作。

1、MySQL 采用 TCP 作为服务器和客户端之间的网络通信协议，默认申请的端口是3306，如果服务连接会报错：Can't connect to local MySQL...... ，如果已启动就正常走 TCP 握手建立连接

mysql -h$ip -P$port -u$user -p

2、连接建立后，就跟根据输入的用户名密码校验，如果账户密码有误，就会有如下报错

3、如果账户密码校验通过，就会查询该用户的权限信息并保存，后续的任何操作都会基于连接时读取到的该用户权限

即一个用户成功建立连接后，即使你用管理员账号对这个用户的权限做了修改，也不会影响已经存在连接的权限。修改完成后，只有再新建的连接才会使用新的权限设置。

每当有一个客户端进程连接到服务器进程时，服务器进程都会创建一个线程来专门处理与这个客户端的交互，当该客户端退出时会与服务器断开连接，服务器并不会立即把与该客户端交互的线程销毁掉，而是把它缓存起来，在另一个新的客户端再进行连接时，把这个缓存的线程分配给该新客户端。这样就起到了不频繁创建和销毁线程的效果，从而节省开销。

连接器涉及的常见参数

1、如果连接完成后没有后续操作，连接就处于空闲状态，可以通过 show processlist 命令查看有多少个连接

2、如果客户端长时间没有操作，连接器就会断开连接，具体时间是通过 wait_timeout 参数设置的，默认是28880秒即8小时。对于空闲时间过长被连接器主动断开的连接，客户端并不会收到消息，只有再次发送请求时才会报错提示：ERROR 2013 (HY000): Lost connection to MySQL server during query，如果要继续就要重新建立连接。

3、连接也可以手动断开，通过 kill connection +id 来手动关闭，如图我建立连接后手动关闭了，再次发送请求时提示报错并重连接了

4、MySQL的默认连接数是有限制的，具体连接数由参数 max_connections 控制，可以通过 show variables like 'max_connections' 查看，如果超过连接数就会拒绝新连接并提示 Too many connections

ysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 200   |
+-----------------+-------+
1 row in set (0.00 sec)

5、MySQL连接分为长连接和短连接。长连接是指连接成功后，如果客户端持续有请求，则一直使用同一个连接；短连接则是指每次执行完很少的几次查询就断开连接，下次查询再重新建立一个。

建立连接的过程通常是比较复杂的，所以建议在使用中要尽量减少建立连接的动作，也就是尽量使用长连接。

但是全部使用长连接后，你可能会发现，有些时候MySQL占用内存涨得特别快，这是因为MySQL在执行过程中临时使用的内存是管理在连接对象里面的。这些资源会在连接断开的时候才释放。所以如果长连接累积下来，可能导致内存占用太大，被系统强行杀掉（OOM），从现象看就是MySQL异常重启了。

怎么解决这个问题呢？你可以考虑以下两种方案。

1. 定期断开长连接。使用一段时间，或者程序里面判断执行过一个占用内存的大查询后，断开连接，之后要查询再重连。
2. 如果你用的是MySQL 5.7或更新版本，可以在每次执行一个比较大的操作后，通过执行 mysql_reset_connection来重新初始化连接资源。这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态。

查询缓存

执行查询语句的时候，会先查询缓存，命中就直接返回（MySQL 8.0 版本后移除）

查询缓存以 key-value 形式保存缓存数据在内存中，key 是查询的语句，value 是查询的结果

如果查询的语句和缓存中的 key 完全一致的话，就会直接返回结果给客户端

弊端

在执行更新操作时，会将查询缓存全部清除。所以频繁更新的表，查询缓存的命中率很低

MySQL 8.0开始查询缓存已经从 Server 层中被移除了，8.0以后的版本查询时不会走查询缓存

解析SQL

没有命中缓存就会进入解析，分析器对查询语句进行词法语法分析，以及判断 SQL 语句是否有语法错误

查询缓存未命中就会进入执行操作，在执行前会对 SQL 语句进行分析

1. 词法分析：根据输入的 SQL 识别系统关键字，构建语法树
2. 语法分析：对构建的语法树分析是否符合语法规则

如果输入的 SQL 中有错误语法，例如 where xxx=1 写成了 wheere xxx=1 就会报错 You have an error in your SQL syntax

优化SQL

根据查询语句来确定最优的执行方案

预处理阶段

预处理阶段主要工作如下：

1. 检查 SQL 中要查询的表或者字段是否存在
2. 将 SELECT * 中的 * 扩充为全部字段

优化阶段

此阶段优化器会确定 SQL 语句的查询方案，它会判断不同查询方案的成本，以选择最优的方式

例如表中存在多个索引时，决定用哪个索引查询；join 连接时决定连接顺序

-- 该 SQL 就会走主键索引
SELECT * FROM user1 WHERE id=1

要想直到具体的查询方案，可以通过 explain 命令对 SQL 进行解释

可以看到 possible_keys 表示该 SQL 语句可能用到的索引，这里只有 PRIMARY，key 是实际使用的索引，用的就是 PRIMARY

具体的索引选择方案在索引相关笔记中记录

执行SQL

执行查询语句，返回记录

分析阶段解析了 SQL 语句、优化阶段指定了查询方案后，就进入正式执行阶段

执行器会根据表的存储引擎定义，调用对应的引擎接口。

SELECT * FROM user1 WHERE id < 10;

以 InnoDB 为例，执行的基本流程：

• 调用 InnoDB 查询接口获取表的第一行数据，判断 id 是否小于10，是则将结果保存到结果集，不是则跳过
• 调用接口读取下一行数据，重复上面的判断逻辑，直到读取到表的最后一行
• 执行器将遍历过程中满足条件的行记录作为结果集返回给客户端

以下执行方式示例，引用自小林coding

通过如下三种查询执行方式，介绍下执行器和存储引擎的交互过程

1. 主键索引查询
2. 全表扫描
3. 索引下推

1、主键索引

以这条 SQL 为例

SELECT * FROM sys_user WHERE id = 1;

查询条件是主键，由于主键是唯一的，且这里按照等值判断，所以类型是 const 常量判断，即用主键索引查询一条记录

• 执行器第一次查询，会调用 read_first_record 函数指针指向的函数，因为优化器选择的访问类型为 const，这个函数指针被指向为 InnoDB 引擎索引查询的接口，把条件 id = 1 交给存储引擎，让存储引擎定位符合条件的第一条记录；
• 存储引擎通过 B+ 树定位到 id=1 的第一条记录，不存在该记录就向执行器报错提示找不到，存在就返回该条记录；
• 执行器获取记录后判断记录是否符合查询条件，如果符合则发送给客户端，如果不符合则跳过；
• 执行器查询的过程是一个 while 循环，所以还会再查一次，但是这次因为不是第一次查询了，所以会调用 read_record 函数指针指向的函数，因为优化器选择的访问类型为 const，这个函数指针被指向为一个永远返回 - 1 的函数，所以当调用该函数的时候，执行器就退出循环，也就是结束查询了

2、全表扫描

SELECT * FROM sys_user WHERE email = 'xxx@qq.com';

由于 email 字段没有索引，所以优化阶段的查询方案是全表扫描，对应 TYPE=ALL，此时执行器和存储引擎的交互流程如下：

• 执行器第一次查询，会调用 read_first_record 函数指针指向的函数，因为优化器选择的访问类型为 all，这个函数指针被指向为 InnoDB 引擎全扫描的接口，让存储引擎读取表中的第一条记录；

• 执行器会判断读到的这条记录的 email=xxx@qq.com 是否成立，不是就跳过，是就返回给客户端

  Server 层每从存储引擎读到一条记录就会发送给客户端，之所以客户端显示的时候是直接显示所有记录的，是因为客户端是等查询语句查询完成后，才会显示出所有的记录

• 执行器查询的过程是一个 while 循环，所以还会再查一次，会调用 read_record 函数指针指向的函数，因为优化器选择的访问类型为 ALL，read_record 函数指针指向的还是 InnoDB 引擎全扫描的接口，所以接着向存储引擎层要求继续读刚才那条记录的下一条记录，存储引擎把下一条记录取出后就将其返回给执行器（Server层），执行器继续判断条件，不符合查询条件即跳过该记录，否则发送到客户端；

• 一直重复上述过程，直到存储引擎把表中的所有记录读完，然后向执行器（Server层） 返回了读取完毕的信息；

• 执行器收到存储引擎报告的查询完毕的信息，退出循环，停止查询。

3、索引下推

索引下推是 MySQL5.6 之后推出的优化策略。索引下推能够减少二级索引查询时的回表操作，提高查询效率

回表查询：由于二级索引的 B+ 树叶子节点存储的主键值，索引在查到符合条件的记录时，会读取叶子节点中的记录的主键值，再次查询聚簇索引的 B+ 树来拿到完整的记录

SELECT * FROM sys_user WHERE age > 18 AND role = 2;

以这条 SQL 为例，假设建立了 (age,role) 的联合索引，由于联合索引在遇到 >、<、%xxx 时会停止匹配，即只有 age 字段参与了索引查询，后面的 role 字段没有用到索引

联合索引不是有序的，或者说是局部有序的，因为构建 B+ 树时只能根据一个值来。(a,b,c) 构建排列时按照 a、b、c 的顺序来

以 (age,role) 的联合索引为例，从 B+ 树角度去看，在全局范围内只有 age 是有序排列的，age=1 时可能有 role=2、role=5 两条记录，age=2时可能有 role=1、role=2、role=3 三条记录，即 role 的有序是针对具体的某个 age 局部存在的

• 加入查询条件是 age=18 AND role=2，那么 B+ 树先定位到 age=18 的第一条记录这儿，可能有10条，再定位到 role=2 的第一条记录这儿，此时联合索引的两个字段都参与了索引查询
• 当查询条件为 age>18 时，必须要把联合索引的 B+ 树中从 age>18 开始的第一条到最后一条记录全找出来，相当于后面的 role=2 压根没起作用，所以它并没有用到索引

无索引下推时

• Server 层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，即 age>18 的第一条记录；
• 存储引擎根据二级索引的 B+ 树快速定位到这条记录后，获取主键值，然后进行回表操作，将完整的记录返回给 Server 层；
• Server 层在判断该记录 role=2 是否成立，成立则将其发送给客户端，否则跳过该记录；
• 接着继续向存储引擎索要下一条记录，重复上面的操作直到读完所有的记录；

即：没有索引下推时，没查询一条二级索引记录，都需要一次回表查询，然后 Server 层再判断该记录的 role=2 是否成立

有索引下推时

• Server 层首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，即 age>18 的第一条记录；
• 存储引擎根据二级索引的 B+ 树快速定位到这条记录后，先不进行回表操作，而是先判断索引包含的列 role 值是否等于2，条件不成立则直接跳过该索引记录，成立则执行回表操作，将记录返回给 Server；
• Server 层再去判断其他的查询条件（本 SQL 只有联合索引的查询条件），成立则将其发送给客户端，否则跳过该记录；
• 接着继续向存储引擎索要下一条记录，重复上面的操作直到读完所有的记录；

即：有索引下推时，虽然 role 列还是没有用到索引，但是由于其本身在联合索引中，所以会直接在存储引擎层过滤出符合 role=2 的条件，再去做回表操作，相比直接每条记录都回表，节省了很多的操作

使用 EXPLAIN 查看执行计划时，如果 Extra=Using index condition 表示使用了索引下推

MySQL存储引擎

MySQL 支持多种存储引擎，可以通过 show engines 指令查看支持的引擎

如图所示，当前版本（8.0.15）MySQL 的默认引擎是 InnoDB，并且只有 InnoDB 是支持事务的（Transactions）

在 MySQL 5.5.5之前 MyISAM 是默认存储引擎，之后都是 InnoDB

如果想要深入了解每个存储引擎以及它们之间的区别，可以阅读 MySQL 官方文档

• InnoDB 存储引擎详细介绍：https://dev.mysql.com/doc/refman/8.0/en/innodb-storage-engine.html
• 其他存储引擎详细介绍：https://dev.mysql.com/doc/refman/8.0/en/storage-engines.html

MyISAM

MyISAM 是基于 ISAM 的存储引擎，数据以紧密格式存储、拥有较高的插入和查询速度、不支持事务、不支持崩溃后的安全恢复、修复操作很慢、提供了全文索引、压缩表、空间数据（GIS）等特性；

使用 MyISAM 引擎创建数据库，将生产 3 个文件。文件的名字以表的名字开始，扩展名指出文件类型

• .frm：存储表结构定义
• .MYD(MYData)：存储表数据
• .MYI（MYIndex)：存储索引数据

InnoDB

InnoDB 提供了具有提交回滚、崩溃的安全回复等机制。

实现了四个标准的隔离级别，默认级别是可重复读(REPEATABLE READ)。在可重复读隔离级别下，通过多版本并发控制(MVCC)+ 间隙锁(Next-Key Locking)防止幻影读；主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升；内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等；支持真正的在线热备份

MyISAM 和 InnoDB

MySQL5.5 之前默认的存储引擎是 MyISAM，之后改为了 InnoDB

• 事务：MyISAM 不支持事务；InnoDB 提供事务支持，实现了 SQL 标准定义的四个隔离级别，默认是可重复读
• 并发：MyISAM 只支持表级锁、InnoDB 支持行级锁（上图的 row-level locking）和表级锁，默认行级锁
• 索引：MyISAM 和 InnoDB 都是 B+ 树索引，但是 MyISAM 仅保存记录所在页的指针
• 外键：MyISAM 不支持外键、InnoDB 支持外键
• 备份：MyISAM 不支持在线热备份、InnoDB 支持
• 恢复：MyISAM 崩溃后数据损毁的概率比 InnoDB高很多，InnoDB 支持数据崩溃恢复，通过 redo log 操作

参考

• 极客时间：MySQL 45讲
• MySQL常见面试题总结
• 小林coding的图解MySQL专栏
• MySQL - 存储引擎