【MySQL | 第五篇】 MySQL 性能分析：如何查询慢 SQL
一、前言
在面试和项目开发中，慢 SQL 是比较常见的问题。
如果一条 SQL 执行时间过长，轻则导致接口响应变慢，重则导致请求超时。要是大量慢 SQL 同时出现，还可能把数据库连接池占满，最终影响整个系统的正常访问。
今天我们就来回答并深入学习这个问题：怎么把慢 SQL 找出来。
二、查看 SQL 执行频次
SQL 执行频次，指的是某类 SQL 语句在数据库中被执行的次数。
比如在一个系统中，select执行得特别多，而insert、update相对较少，那么我们在做性能优化时，就可以优先关注查询语句。因为这类 SQL 执行频率高，优化之后的收益也更明显。
在 MySQL 中，可以通过下面的命令查看常见 SQL 的执行频次：
showglobalstatuslike'Com_______';这里的global表示查看全局统计信息(要注意global重启后失效)，如果只想查看当前会话的统计信息，也可以写成：
showsessionstatuslike'Com_______';Com_______中有 7 个下划线。这里的下划线是like语句中的通配符，用来匹配Com_select、Com_insert、Com_update这类状态变量。
常见字段如下：
– 事务相关
com_begin： 执行 begin 操作的次数 com_commit： 执行 commit 操作的次数 com_rollback： 执行 rollback 操作的次数
– dml 操作（最常用） com_select： 执行 select 操作的次数 com_insert： 执行 insert 操作的次数 com_update： 执行 update 操作的次数 com_delete： 执行
delete 操作的次数
– ddl 操作 com_create_table： 执行 create table 的次数 com_drop_table： 执行 drop table 的次数 com_alter_table： 执行 alter table 的次数
– 表维护 com_repair： 执行 repair table 的次数 com_optimize： 执行 optimize table 的次数
– 权限操作
com_grant： 执行 grant 的次数
com_revoke： 执行 revoke 的次数
– show 相关
com_show_databases： 执行 show databases 的次数
com_show_tables： 执行 show tables 的次数
com_show_status： 执行 show status 的次数
com_show_variables： 执行 show variables 的次数
com_show_processlist： 执行 show processlist 的次数
– binlog
com_binlog： 执行 binlog 操作的次数
– 其他
com_change_db： 执行 use database 的次数
com_kill： 执行 kill 的次数
例如，我们一次查看所有常用的包括提交删除插入事务,查询,更新的频率
通过这一步，我们可以先大概判断系统的 SQL 访问特点：到底是查询多，还是写入多，后续优化时也会更有方向。
三、开启慢查询日志
如果说执行频次是帮我们判断“哪类 SQL 执行得多”，那么慢查询日志就是帮我们找到“哪些 SQL 执行得慢”。
MySQL 的慢查询日志会记录执行时间超过指定阈值的 SQL 语句。这个阈值由long_query_time参数控制，单位是秒，默认值通常是 10 秒。
1. 查看慢查询日志是否开启
先查看慢查询日志的开关状态（ON为开启，OFF为关闭）：
showvariableslike'slow_query_log';2. 临时开启慢查询日志
可以通过下面的命令临时开启慢查询日志：
setglobalslow_query_log='on';也可以写成：
setglobalslow_query_log=1;需要注意的是，这种方式是运行时修改，MySQL 重启之后可能会失效。如果希望长期生效，需要写到 MySQL 配置文件中。
3. 设置慢查询时间阈值
查看当前慢查询时间阈值：
showvariableslike'long_query_time';例如我们希望 SQL 执行时间超过 2 秒就被记录下来，可以设置：
setgloballong_query_time=2;如果只想在当前会话中测试，也可以使用：
setsessionlong_query_time=2;这里要注意一点：slow_query_log是控制慢查询日志是否开启，而long_query_time是控制超过多少秒才算慢查询。两个参数的作用不一样。
4. 查看慢查询日志文件位置
这个时候可能会有一个问题：慢查询日志开启之后，生成的日志文件在哪里？
更直接的方式是查看slow_query_log_file：
showvariableslike'slow_query_log_file';如果想查看 MySQL 的数据目录，也可以使用：
select@@datadir;查询结果就是 MySQL 的数据目录地址。慢查询日志文件一般会在这个目录下，文件名中通常会带有slow.log。
5. 测试慢查询日志是否生效
为了测试慢查询日志是否真的生效，可以执行一条耗时 SQL：
selectsleep(3);如果我们把long_query_time设置成了 2 秒，那么这条 SQL 执行 3 秒，就会被记录到慢查询日志中。
然后找到后缀为slow.log的文件，打开之后就可以看到慢查询记录。
慢查询日志中比较重要的信息一般包括：
| 字段 | 含义 |
|---|---|
Query_time | SQL 实际执行耗时 |
Lock_time | 等待锁的时间 |
Rows_sent | 返回给客户端的行数 |
Rows_examined | 扫描过的行数 |
| SQL 语句 | 具体被记录下来的慢 SQL |
其中我们最需要关注的是Query_time和Rows_examined。
如果Query_time很长，说明 SQL 执行时间确实比较久。如果Rows_examined很大，说明 MySQL 为了得到结果扫描了大量数据，这种情况通常就需要继续用explain分析执行计划。
四、使用 show profile 查看 SQL 耗时
慢查询日志可以帮助我们找到慢 SQL，但如果我们想继续看一条 SQL 在执行过程中具体耗时在哪里，可以使用show profile。
先查看 profiling 是否开启：
select@@profiling;如果结果为0，说明默认是关闭的。可以通过下面的命令开启：
setprofiling=1;然后执行需要分析的 SQL，再查看 SQL 执行记录：
showprofiles;如果想查看某一条 SQL 的详细耗时阶段，可以继续执行：
showprofileforquery1;这里的1是show profiles结果中的Query_ID。
show profile更适合学习阶段观察 SQL 执行过程。在实际项目或生产环境中，更推荐结合慢查询日志、explain、监控工具以及 Performance Schema 一起分析。
五、使用 explain 查看执行计划
找到慢 SQL 之后，下一步就要分析它为什么慢。
这时最常用的工具就是explain。它可以告诉我们 MySQL 准备怎么执行这条 SQL，比如有没有走索引、预计扫描多少行、访问类型是什么等。
基本语法如下：
explainselect字段列表from表名where条件;例如：
explainselect*fromempwhereusername='Tom';1. explain 的输出格式
在 MySQL 8.0 中，explain支持多种输出格式，比如传统表格格式、树形格式和 JSON 格式。
如果你的环境中看到的是树形结果，类似下面这样：
explainformat=treeselect字段列表from表名where条件;树形格式更适合看执行步骤之间的层级关系。
如果想看到我们平时更常见的表格字段，可以使用传统格式：
explainformat=traditionalselect字段列表from表名where条件;也可以使用 JSON 格式查看更完整的信息：
explainformat=jsonselect字段列表from表名where条件;对于初学阶段来说，先掌握format=traditional的表格字段就够用了。
2. explain 常见字段说明
explain format=traditional 的结果中，常见字段如下：
| 字段 | 含义 | 重点怎么看 |
|---|---|---|
id | 查询中select的编号 | id相同通常从上往下看，id不同一般值越大越先执行 |
select_type | 查询类型 | 常见有SIMPLE、PRIMARY、SUBQUERY、UNION等 |
table | 当前访问的表 | 表示这一行执行计划分析的是哪张表 |
partitions | 匹配到的分区 | 没有使用分区表时通常为NULL |
type | 访问类型 | 非常重要，用来判断访问表的方式好不好 |
possible_keys | 可能使用的索引 | 优化器认为这条 SQL 可能用到哪些索引 |
key | 实际使用的索引 | 如果为NULL，说明没有使用索引 |
key_len | 使用索引的长度 | 表示 MySQL 实际使用了索引中的多少字节 |
ref | 索引比较对象 | 表示索引列和哪个列或常量进行比较 |
rows | 预计扫描行数 | 估算值，越大通常说明扫描数据越多 |
filtered | 条件过滤百分比 | 要结合rows一起看，不能单独判断好坏 |
Extra | 额外信息 | 会显示Using where、Using index、Using filesort等补充信息 |
3. type 字段怎么看
在explain中，type是非常关键的字段，它表示 MySQL 访问表的方式。
常见访问类型可以简单按下面顺序理解：
system > const > eq_ref > ref > range > index > ALL一般来说，越靠左性能越好，越靠右性能越差。
| type | 含义 | 说明 |
|---|---|---|
system | 系统表或只有一行数据的表 | 这种情况比较少见 |
const | 通过主键或唯一索引精确匹配一行 | 性能很好 |
eq_ref | 多表连接时，通过主键或唯一索引匹配唯一一行 | 常见于关联查询 |
ref | 使用普通索引进行查询 | 可能匹配多行 |
range | 使用索引进行范围查询 | 例如>、<、BETWEEN、IN |
index | 扫描整个索引树 | 比全表扫描好一些，但仍然可能扫描很多数据 |
ALL | 全表扫描 | 如果数据量大，就需要重点关注 |
这里还可能看到NULL。NULL一般表示查询不需要访问表，比如直接执行：
explainselect1;这种情况比较特殊，不需要放到常规的性能优劣顺序里理解。
4. Extra 字段怎么看
Extra字段是执行计划中的补充信息，也很值得关注。
| Extra 信息 | 含义 | 是否需要关注 |
|---|---|---|
Using where | MySQL 在存储引擎取出数据后，还需要根据where条件过滤 | 正常情况，结合rows一起看 |
Using index | 使用了覆盖索引，不需要回表查询 | 通常是比较好的情况 |
Using temporary | 使用了临时表 | 需要关注，常见于group by、order by |
Using filesort | MySQL 需要额外排序 | 需要关注，可能和索引设计有关 |
其中Using temporary和Using filesort在数据量比较大时要重点分析，因为它们可能会带来额外的内存或磁盘开销。
5. 分析 explain 时的思路
看explain时，不需要一上来就把所有字段背下来，可以先抓住几个重点：
看
key是否为NULL如果
key是NULL，说明这条 SQL 没有使用索引，需要继续分析查询条件、索引设计以及字段类型是否匹配。看
type是否为ALL如果
type是ALL，说明 MySQL 做了全表扫描。小表问题不大，但如果是大表，就很可能是慢 SQL 的原因。看
rows是否过大rows表示 MySQL 预计要扫描多少行。它是估算值，不一定完全准确，但可以用来判断扫描范围是否过大。看
Extra中是否出现Using temporary或Using filesort如果出现这两个信息，说明 SQL 可能发生了临时表或额外排序，需要重点检查
order by、group by以及相关索引。看
possible_keys和key是否一致possible_keys表示可能使用的索引，key表示最终实际使用的索引。如果possible_keys有值，但key是NULL，说明优化器最终没有选择索引，这时就要继续分析条件写法、数据量、索引区分度等问题。
六、总结
这一篇主要学习了如何查询慢 SQL，而不是直接进入 SQL 优化。
整体流程可以总结为：
- 先用show status查看 SQL 执行频次，判断系统中哪类 SQL 最常见
- 再开启慢查询日志，通过 slow_query_log 和 long_query_time找到真正执行慢的 SQL
- 如果想看单条 SQL 的耗时阶段，可以使用
show profile - 最后使用
explain查看执行计划，重点关注type、key、rows和Extra
今天我们把慢SQL查询学习完毕,下篇我们将会讲到索引与SQL优化,这基本是相关联的也是面试的重点内容