001.执行流程

MySQL 执行一条 SQL 查询语句的流程：     MySQL 的架构共分为两层：

• Server 层负责建立连接、分析和执行 SQL，MySQL 大多数的核心功能模块都在这实现
  • 主要包括连接器、查询缓存、解析器、预处理器、优化器、执行器等
  • 另外，所有的内置函数（如日期、时间、数学、加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等）都在 Server 层实现
• 存储引擎层负责数据的存储和提取
  • 支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层
  • 最常用的存储引擎是 InnoDB，从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎
  • 不同的存储引擎支持的索引类型也不相同，比如 InnoDB 支持索引类型是 B+Tree

第一步：连接器 §

连接器的工作：

1. 和客户端经过 TCP 三次握手，建立连接
2. 校验客户端提交的凭证
3. 没有问题则获取该用户的权限，然后保存起来，后续该用户在此连接里的任何操作，都会基于连接开始时读到的权限进行权限逻辑的判断

所以，如果一个用户已经建立了连接，即使管理员中途修改了该用户的权限，也不会影响已经存在连接的权限，只有再新建的连接才会使用新的权限设置

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

show processlist 命令：

上图的显示结果，共有两个用户名为 root 的用户连接了 MySQL 服务，其中 id 为 6 的用户状态为 Sleep，这意味着该用户已经 736 秒没有再执行过任何命令

空闲连接会一直占用着吗？

MySQL 定义了空闲连接的最大空闲时长，由 wait_timeout 参数控制，默认值是 8 小时（28880 秒），如果空闲连接超过了这个时间，连接器就会自动将它断开

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

MySQL 的连接数有限制吗？

MySQL 服务支持的最大连接数由 max_connections 参数控制，默认是 151，超过这个值，系统就会拒绝接下来的连接请求，并报错提示：Too many connections

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

MySQL 的连接也跟 HTTP 一样，有短连接和长连接的概念：

• 短连接：
  • 连接 MySQL 服务（TCP 三次握手）
  • 执行 SQL
  • 断开 MySQL 服务（TCP 四次挥手）
• 长连接：
  • 连接 MySQL 服务（TCP 三次握手）
  • 执行多次 SQL
  • 断开 MySQL 服务（TCP 四次挥手）

使用长连接可以减少建立连接和断开连接的过程，一般是推荐使用长连接

但是，使用长连接可能会占用内存增多，因为 MySQL 使用内存管理连接对象，只有在连接断开时才会释放。如果长连接累计很多，将导致 MySQL 服务占用内存太大

怎么解决长连接占用内存的问题？

• 方法一，定期断开长连接
• 方法二，客户端主动重置连接
  • MySQL 5.7 版本提供 mysql_reset_connection() 接口函数，当客户端执行了一个很大的操作后，可以调用该函数来重置连接，达到释放内存的效果
  • 这个过程不需要重连和重新做权限验证，但是会将连接恢复到刚刚创建完时的状态

第二步：查询缓存 §

连接器的工作完成后，客户端就可以向 MySQL 服务发送 SQL 语句了，MySQL 服务收到 SQL 语句后，就会解析出 SQL 语句的第一个字段，看看是什么类型的语句

如果 SQL 是查询语句（select），就会先去查询缓存（Query Cache），缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句，value 为 SQL 语句查询的结果。如果缓存命中，就直接返回给客户端；否则往下继续执行，等执行完后，存入查询缓存中

看起来，查询缓存还挺有用，但是其实查询缓存挺鸡肋。对于更新比较频繁的表，查询缓存的命中率很低，因为只要一个表有更新操作，那么这个表的查询缓存就会被清空

所以，MySQL 8.0 将查询缓存删掉了

注意：这里说的查询缓存是 server 层的，也就是 MySQL 8.0 移除的是 server 层的查询缓存，并不是 Innodb 存储引擎中的 buffer pool

第三步：解析 SQL §

在正式执行 SQL 语句之前，会先对 SQL 语句做解析，这个工作交由「解析器」来完成

解析器做两件事情：

• 词法分析：将输入的 SQL 语句字符串识别成 Token 序列，包含关键字和非关键字
• 语法分析：根据词法分析的结果，语法解析器会根据语法规则，判断输入的这个 SQL 语句是否满足语法，如果没问题就会构建出 SQL 语法树，这样方便后续模块获取 SQL 类型、表名、字段名、 where 条件等

解析器阶段：只会检查 SQL 语句的语法错误，不会检查表不存在或字段不存在

第四步：执行 SQL §

经过解析器后，就要执行 SQL 语句的流程了，主要可以分为下面这三个阶段：

• prepare（预处理）阶段
• optimize（优化）阶段
• execute（执行）阶段

预处理器 §

• 检查 SQL 查询语句中的表或者字段是否存在
• 将 select * 中的 * 扩展为表上的所有列

优化器 §

负责将 SQL 查询语句的执行方案确定下来，比如在表中有多个索引，优化器会基于查询成本的考虑，决定选择使用哪个索引

要想知道优化器选择了哪个索引，可以查看执行计划，其中的 key 表示执行过程使用了哪个索引

比如下图的 key 为 PRIMARY 就是使用了主键索引：

如果查询语句的执行计划里的 key 为 null，说明没有使用索引，那就会全表扫描（type = ALL），这种查询扫描的方式是效率最低档次的：

再比如 product 表有主键索引 id 和普通索引 name，执行这条查询语句：

select id from product where id > 1 and name like 'i%';

这条查询语句既可以使用主键索引，也可以使用普通索引（二级索引），但是执行的效率会不同。这时，就需要优化器来决定使用哪个索引

很显然这条查询语句是覆盖索引，直接在二级索引就能查找到结果（因为二级索引 B+Tree 的叶子节点的数据存储的是主键值），就没必要在主键索引查找了，因为查询主键索引的 B+Tree 的成本会比查询二级索引的 B+ 的成本大，优化器基于查询成本的考虑，会选择查询代价小的普通索引

在下图中执行计划，执行过程中使用了普通索引 name，Exta 为 Using index，表明使用了覆盖索引优化：

执行器 §

经历完优化器后，就确定了执行方案，真正开始执行语句，执行过程中，执行器就会和存储引擎交互了，交互是以记录为单位的

接下来，用三种方式执行，理解一下执行器和存储引擎的交互过程：

• 主键索引查询
• 全表扫描
• 索引下推

主键索引查询 §

select * from product where id = 1;

查询条件用到了主键索引，而且是等值查询，同时主键 id 是唯一，不会有 id 相同的记录，所以优化器决定选用访问类型为 const 进行查询，也就是使用主键索引查询一条记录

执行器与存储引擎的执行流程：

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

全表扫描 §

select * from product where name = 'iphone';

查询条件没有用到索引，所以优化器决定选用访问类型为 ALL 进行查询，也就是全表扫描的方式查询

执行器与存储引擎的执行流程：

• 执行器第一次查询，调用 read_first_record 函数指针指向的函数，因为优化器选择的访问类型为 all，这个函数指针被指向为 InnoDB 引擎全扫描的接口，让存储引擎读取表中的第一条记录
• 执行器会判断记录是否符合查询条件，如果不是则跳过；如果是则将记录发给客户端（是的没错，Server 层每从存储引擎读到一条记录就会发送回客户端，之所以客户端是直接显示所有记录，是因为客户端是等查询语句查询完成后，才会显示出所有的记录）
• 执行器查询的过程是一个循环，所以还会再查，调用 read_record 函数指针指向的函数，因为优化器选择的访问类型为 all，read_record 函数指针指向的还是 InnoDB 引擎全扫描的接口，所以接着向存储引擎层要求继续读刚才那条记录的下一条记录，存储引擎把下一条记录取出后就将其返回给执行器，执行器继续判断条件，不符合查询条件即跳过该记录，否则发送到客户端
• 一直重复循环，直到存储引擎把表中的所有记录读完，然后向执行器返回读取完毕的信息（信号）
• 执行器收到存储引擎报告的查询完毕信息，退出循环，结束查询

索引下推 §

索引下推时 MySQL 5.6 推出的查询优化策略，能够减少二级索引在查询时的回表操作，提高查询的效率，因为它将 Server 层部分负责的事情，交给存储引擎层去处理了

select * from t_user where age > 20 and reward = 100000;

其中 age 和 reward 是联合索引，联合索引当遇到范围查询（>、< 等）就会停止匹配，==也就是 age 字段能用到联合索引，但 reward 字段无法利用到索引==（详见）

不使用索引下推（MySQL 5.6 之前的版本）时，执行器与存储引擎的执行流程：

• 执行器首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，也就是定位到 age > 20 的第一条记录
• 存储引擎根据二级索引的 B+Tree 快速定位到这条记录后，获取主键值，然后==进行回表操作==，将完整的记录返回给执行器
• 执行器再判断该记录 reward = 100000，如果成立则将其发送给客户端；否则跳过该记录
• 接着，继续向存储引擎索要下一条记录，存储引擎在二级索引定位到记录后，获取主键值，然后回表操作，将完整的记录返回执行器
• 如此往复，直到存储引擎把表中的所有记录读完

可以看到，没有索引下推的时候，每查询到一条二级索引记录，都要进行回表操作，将记录返回给执行器，接着执行器再判断其他条件

而使用索引下推后，判断记录 reward = 100000 的工作交给了存储引擎层，过程如下 ：

• 执行器首先调用存储引擎的接口定位到满足查询条件的第一条二级索引记录，也就是定位到 age > 20 的第一条记录
• 存储引擎定位到二级索引后，先不执行回表操作，而是先判断一下该索引中包含的列的条件（reward = 100000）是否成立。如果不成立，则直接跳过该二级索引；如果成立，则执行回表操作，将记录返回给执行器
• 执行器再判断其他的查询条件（本次查询没有其他条件）是否成立，如果成立则将其发送给客户端；否则跳过该记录，然后向存储引擎索要下一条记录
• 如此往复，直到存储引擎把表中的所有记录读完

可以看到，使用索引下推后，虽然 reward 列无法使用到联合索引，但是因为它包含在联合索引（age, reward）中，所以直接在存储引擎过滤出满足 reward = 100000 的记录后，才执行回表操作获取整个记录，相比于没有使用索引下推，节省了很多回表操作

执行计划里的 Exta 部分显示 Using index condition，说明使用了索引下推：

参考

执行计划 §

在查询语句最前面加个 explain 命令，就会输出这条 SQL 语句的执行计划

• possible_keys：可能用到的索引
• key：实际用的索引，NULL 说明没有使用索引
• key_len：使用索引的长度
• rows：扫描的数据行数
• type：数据扫描类型，常见扫描类型的执行效率从低到高的顺序为：
  • ALL（全表扫描）
  • index（全索引扫描）：对索引表进行全扫描
  • range（索引范围扫描）：一般在 where 子句中使用 < 、>、in、between 等关键词
    • 从这一级别开始，索引的作用会越来越明显，因此需要尽量让 SQL 查询可以使用到 range 这一级别及以上的扫描类型
  • ref（非唯一索引扫描）：用了非唯一索引或唯一索引的非唯一性前缀，返回数据可能是多条
    • 即使使用索引快速查找到了第一条数据，仍然不能停止，需要继续扫描
  • eq_ref（唯一索引扫描）：使用主键或唯一索引时产生的访问方式，通常使用在多表联查中
  • const（结果只有一条的主键或唯一索引扫描）
• extra：额外说明
  • Using filesort：使用了文件排序
    • 当查询语句中包含 group by 操作，而且无法利用索引完成排序操作时，不得不选择相应的排序算法进行排序，甚至可能会通过文件排序。效率很低，要避免
  • Using temporary：使了用临时表保存中间结果
    • 在对查询结果排序时使用临时表，常见于排序 order by 和分组查询 group by。效率低，要避免
  • Using index：使用了覆盖索引，避免了回表操作，效率不错
  • Using index condition：使用了索引下推