【MySQL】关于 SQL 语句执行分析的二三事

一、为什么要发生这件事

确实， 平时我们增删改查写的好好的， 各种业务代码，各种小接口写的不亦乐乎，正常是没时间干这个的，但是但是，这不是还有点技术追求嘛，假如我们平时测一个小接口，发现它的响应速度太慢了，当然这可能是有很多原因的，比如下面我能想到的这几种：

🌰 1. 换个操作系统部署试试，可能是在当前这个操作系统比较慢（硬件问题）
🌰 2. 考虑下是不是自己的代码写的太烂了，看看能不能优化一下
🌰 3. 可能是当前语句请求数据库的语句执行过慢了，出现了慢查询

下面是万能的 GPT 给我们的答案(太官方了，不是嘛，小傻 GPT …) ：

假设，我们已经部署到了其他机器上，检查了代码，也确定了不是网络问题等等，那么下面我们就该分析一下我们的 SQL 了，本文就将介绍一下如何分析一条 SQL 语句 。

二、 MySQL 如何查看慢查询语句

1. 修改配置文件，开启慢查询日志

– 找到 MySQL 配置文件 my.cnf (linux) 或者 my.ini(window)，在文件中添加以下配置

slow_query_log=1
   slow_query_log_file=/var/log/mysql/mysql-slow.log # 慢查询日志存放位置
   long_query_time=1 # 查询时间超过多长的作为慢查询被记录下来

– 重启 MySQL 服务。

– 打开 MySQL 命令行客户端，输入以下命令：

show variables like '%slow_query_log%';

– 确认慢查询日志已经开启，并查看日志文件的路径。

– 通过命令行或其他工具打开指定的日志文件，就可以看到所有的慢查询语句了。

2. mysql dump slow

使用 MySQL 自带的工具来对慢查询日志进行分析和统计，例如使用以下命令查看最慢的 10 条查询：

mysql dump slow -s t -t 10 /var/log/mysql/mysql-slow.log

三、拿到 SQL 后如何分析 —— explain

1. 稍稍说一点点原理

略略略，我不讨厌原理，但是原理真的很讨厌 ， 所以就稍稍说一点点和 explain 有点关系的原理 。

首先，一条 SQL 来给 MySQL 执行了，MySQL 客户端先会连接到 MySQL 服务器，然后进行语法分析，看看是不是符合规则，不符合规则直接返回了，就是那个我们常见的错误 。

然后呢，假如没有出现问题 ， 这条 SQL 就被交给 MySQL 查询优化器了，优化器会根据表的索引、表的大小、查询条件等因素来选择最优的执行方案 。然后就是熟悉的执行过程，返回结果，最后断开连接 。

通过上面，我们就能 get 到了决定我们的 SQL 如何执行的就是我们的查询优化器， 所以 explain 就是用来分析这个过程 ， 其模拟了 MySQL 优化器是如何执行这个 SQL 语句的。

2. 进入正题 —— explain

以下面的 SQL 为例子，使用 explain 进行分析下面的 SQL 语句 （这里就假设我这个 SQL 是一条查询百万级数据的慢查询 SQL ， 内心 OS： 连 100 条都没有， 还百万…） ：

explain
(
    select *
    from hcm_hr_employee
    join hcm_abs_account
    on hcm_hr_employee.id = hcm_abs_account.employee_id
)

执行上面的分析语句后，输出下面的分析结果，我们可以看到结果有很多字段。虽然现在看起来乱七八糟的，但是通过我们下面的学习，我们就能看懂这个分析结果了 。

1. id

ps： 这个字段不咋重要，随便看看就行了
对一条 SQL 语句，虽然我们看上去是一条，但是其可以含有一些子查询，一些 join 连接，一些 union 组合，所以 MySQL 是将其拆成多个 SQL 执行的， 这些 SQL 的编号为 id。id 相同情况下，执行顺序自上向下。id 不同情况下，执行顺序为 id 值越大，优先级越高，先执行 。

2. select_type

这个字段指示了我们这条 SQL 的查询类型，含有简单查询、含复杂子查询、含 UNION 等类型，对分析慢查询也不咋重要 。

类型	含义
SIMPLE	简单的select 查询，即 SQL 语句中不包含子查询语句 或者 UNION。
PRIMARY	查询中包含复杂的子查询部分，最外层查询语句被标记为 PRIMARY
SUBQUERY	在 select 或者 where 列表中包含了子查询
DERIVED	在 from 列表中包含的子查询会被标记为DERIVED(衍生表)
UNION	如果第二个 select 出现在 union 之后，则被标记位 UNION
UNION RESULT	从 union 后的表获取结果的 select

3. table

该行数据关于哪张表（更不重要了，为了完整性，写一下）

4. type *

数据访问类型，即我们这个数据是如何访问的，这个字段非常重要，我们可以通过这个字段判断我们当前的 SQL 语句是否出现了索引失效。数据访问类型性能由好到差为 system ， const ， eq_ref ， ref ，range ， index 和 ALL 。对 system 一听这个名字就知道用不到了，其他的多多少少都会出现，下面是其介绍：

1. system ： 平时业务中不会出现
2. const ：通过索引一次查到数据，该类型主要用于比较primary key 或者unique 索引等值查询，因为只匹配一行数据 （InnoDB 进行二分查找，快速定位到该行数据位置，然后直接返回），所以很快。
3. eq_ref ：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或者唯一索引扫描。
4. ref ：非唯一索引扫描，返回匹配某个单独值得所有行，本质上是一种索引访问，它返回所有匹配某个单独值的行，就是说它可能会找到多条符合条件的数据，所以他是查找与扫描的混合体。
5. range ：只检索给定范围的行，使用一个索引来选出行。一般在你的WHERE 语句中出现between 、< 、> 、in 等查询，这种给定范围扫描比全表扫描要好。因为他只需要开始于索引的某一点，而结束于另一点，不用扫描全表索引。
6. index ：Full Index Scan 扫描遍历索引树（扫描全表的索引，从索引中获取数据）。
7. ALL ：全表扫描 ，从磁盘中获取数据 ，百万级别的数据ALL类型的数据尽量优化。

5. possible_keys

显示可能应用在这张表的索引，一个或者多个。查询涉及的字段若存在该索引，该索引就将被列出，但不一定被查询实际使用。 这是由 MySQL 自己决定的，其在某个字段唯一性差， 频繁更新的字段， 索引性能低于全表扫描（回表太多了）等时，就会选择不用索引 。

6. keys

实际使用到的索引。如果为NULL，则没有使用索引。

7. ken_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。

8. ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。

9. rows

每张表有多少行被优化器查询，根据表统计信息及索引选用的情况，大致估算出的找到所需记录需要读取的行数。

10. Extra

一些重要的扩展信息

1. Using filesort(文件排序)：无法按照既定的索引的顺序进行读取
2. Using temporary：Mysql使用了临时表保存中间结果，常见于排序 order by 和分组查询 group by。
3. Using index ：表示相应的select 操作使用了覆盖索引，避免了回表查询的出现，性能较好 。

• 如果同时出现Using where ，表明索引被用来执行索引键值的查找。
• 如果没有同时出现using where 表明索引用来读取数据而非执行查找动作。

4. Using where
5. Using join buffer ：表示当前 SQL 使用了连接缓存。
6. impossible where ：where 字句总是false ，MySQL 无法获取数据行。
7. select tables optimized away
8. distinct

四、举个栗子🌰

1. or 操作一定会导致索引失效吗 ，导致全表扫描吗？ —— index_merge

很多网上的文章都说， or 操作会导致索引失效，但是我实际运行一下 explain 分析，发现数据访问类型变为了 index_merge ，而不是完全的失效， 索引合并表示对两个独立的索引过滤之后，再将结果合并在一起，当然这样也是非常慢的，优化方式为 ： 对使用到的索引建立联合索引，再进行查询 。

2. 有索引的时候一定走索引吗 ？

来吧，展示

就算是全是按照索引字段进行筛选，也可能不走索引，因为如果按照二级索引查出的记录太多，要对这里面每条记录去聚簇索引里做一次回表，回表次数太多了，还不如全表扫描效率高，这种情况下的优化依旧是建立一个联合索引，或者使用覆盖索引，从而避免回表 。