Mysql 之执行计划

背景

开发过程经常会遇到接口响应慢的问题，用户也会反馈页面卡顿，响应太慢，导致用户体验差，这个时候作为开发就要赶紧排查了，可能是程序处理的问题、也可能是并发量大导致排队问题、也可能是 sql 查询性能导致等；大多数时候 sql 查询缓慢是主要原因，下面我们聊下排查 sql 慢的思路。

技术简介

说到 sql 就要先了解下 sql 的架构图:

**客户端：**这里指连接 MySQL 各种形式，如.Net 中使用的 ADO 连接、Java 使用 JDBC 连接等，MySQL 是客户端和服务器模式，前提先建立连接，才能传输数据，处理相关逻辑。

**业务逻辑：**在 MySQL 内部有很多模块组成，分别处理相关业务逻辑。

**连接管理：**负责连接认证、连接数判断、连接池处理等业务逻辑处理。

**查询缓存：**当一个 SQL 进来时，如果开启查询缓存功能，MySQL 会优先去查询缓存,平时我们开发都会选择不开启。

**解析器：**对于一个 sql 语句,MySql 根据语法规则需要对其进行解析，并生成一个内部能识别的解析树。

**优化器：**负责对解析器得到的解析树进行优化，MySQL 会根据内部算法找到一个 MySQL 认为最优的执行计划，后续就按照这个执行计划执行。

**执行器：**得到执行计划之后，就会找到对应的存储引擎,根据执行计划给出的指令依次执行。

**存储引擎：**数据的存储和提取最后是靠存储引擎,平时我们使用最多的就是支持事务的 InnoDb。

**物理文件：**数据存储的最终位置,即磁盘上,协同存储引擎对数据进行读写操作。

实现与效果

通过上面的逻辑我们知道一个 sql 发送到服务端时候要经过优化器，而使用 explain 关键字可以模拟优化器执行 sql 查询语句，从而知道 MySQL 是如何处理 sql 的即 sql 的执行计划。

概要描述：

**id: **选择标识符

select_type: 表示查询的类型

table: 输出结果集的表

**partitions: **匹配的分区

type: 表示表的连接类型

**possible_keys: **表示查询时，可能使用的索引

key: 表示实际使用的索引

key_len: 索引字段的长度

**ref: **列与索引的比较

**rows: **扫描出的行数(估算的行数)

filtered: 按表条件过滤的行百分比

**Extra: **执行情况的描述和说明

这里面重点需要关注 type, key, Extra

Type

开发中比较常见的几个值，性能从好到坏

const->eq_ref->ref->range->index->all

const：表示通过索引一次就找到数据，用于 primary key 或者 unique 索引，很快就能找到对应的数据,如图：

eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常用于主键或唯一索引扫描。

Ref：非唯一索引扫描，返回匹配的所有行。

两者区别匹配出多条结果时候用到的是否是唯一索引,如图：

range：使用一个索引检索指定范围的行，一般在 where 语句中会出现 between、<、>、in 等范围查询，如图：

index：全索引扫描，只遍历索引树，如图：

ALL：全表扫描，找到匹配行。与 index 比较，ALL 需要扫描磁盘数据，index 值需要遍历索引树，如图：

Key

实际使用到的索引，如果为 NULL 代表没有使用到索引；这也是平时小伙伴判断是否用上索引的关键

Extra

一般常见会出现下面的信息：

Using index：表示查询语句中用到了覆盖索引，不访问表的数据行，查询效率比较好，如图：

如果用 SELECT *进行查询，就不会有 Using index。

Using filesort：代表 MySQL 会使用一个外部索引对数据进行排序(文件排序)，而不是使用表内索引。这种情况在 SQL 查询需要避免，最好不要在 Extra 中出现此类型，如图：

通常会是使用 ORDER BY 语句导致，上图中使用无索引的字段进行排序会出现。

Using temporary：产生临时表保存中间结果，这种 SQL 是不允许的，遇见数据量大的场景，就会特别慢，如图：

这种类型常常因为 ORDER BY 和 GROUP BY 导致，所以在进行数据排序和分组查询时，要注意是否加了索引以及索引是否生效。

《总结》

上面只是列出了一些重要的点，大家可以通过重要信息看出 sql 是否需要优化，从而定位问题。另外大家如果不好定位哪条 sql 比较慢的话，可以打开 mysql 的慢查询来定位到。

更多学习资料戳下方！！！

https://qrcode.ceba.ceshiren.com/link?name=article&project_id=qrcode&from=infoQ&timestamp=1662366626&author=xueqi