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

刘婉晴

已于 2023-04-23 17:11:10 修改

阅读量1k

点赞数

分类专栏： MySQL 文章标签： mysql sql 数据库

于 2023-04-23 16:13:15 首次发布

本文链接：https://blog.csdn.net/liuwanqing233333/article/details/130293290

版权

MySQL 专栏收录该内容

16 篇文章 1 订阅

订阅专栏

一、为什么要发生这件事

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

🌰 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 一听这个名字就知道用不到了，其他的多多少少都会出现，下面是其介绍：

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