MySql--慢查询

发表于 2022-10-14 更新于 2026-09-14 分类于技术， mysql 置顶精品

阅读次数： 0 本文字数： 2.2k 阅读时长 ≈ 8 分钟

摘要

MySql知识点介绍: 慢查询
本文基于mysql-8.0.30，https://dev.mysql.com/doc/refman/8.0/en/

常见面试题

1.慢SQL问题如何排查？

慢查询

什么是慢查询
慢查询日志，顾名思义，就是查询花费大量时间的日志，是指mysql记录所有执行超过long_query_time参数设定的时间阈值的SQL语句的日志。该日志能为SQL语句的优化带来很好的帮助。默认情况下，慢查询日志是关闭的，要使用慢查询日志功能，首先要开启慢查询日志功能。
开启慢查询

mysql> show VARIABLES like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+

# 开启慢查询日志
mysql> set GLOBAL slow_query_log=1;

# 默认阈值是10秒，超过这个阈值就会记录慢查询日志，可以根据需要进行修改
mysql> show VARIABLES like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

# 如果运行的SQL语句没有使用索引，则MySQL数据库也可以将这条SQL语句记录到慢查询日志文件，默认关闭
mysql> show VARIABLES like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+

# 产生的慢查询日志，可以指定输出的位置，通过参数log_output来控制，可以输出到[TABLE][FILE][FILE,TABLE]，默认FILE，如果指定TABLE，则会记录在mysql.slow_log表中
mysql> show VARIABLES like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+

# 生成的日志文件默认在datadir指定的目录下，也可以自己设置
mysql> show VARIABLES like 'slow_query_log_file';
+---------------------+-------------------------------------------------------------+
| Variable_name       | Value                                                       |
+---------------------+-------------------------------------------------------------+
| slow_query_log_file | /usr/local/soft/mysql8/datas/mysql/ip-10-250-0-214-slow.log |
+---------------------+-------------------------------------------------------------+

慢查询日志格式

“Time: 2021-04-05T07:50:53.243703Z”：查询执行时间
“User@Host: root[root] @ localhost [] Id: 3”：用户名 、用户的IP信息、线程ID号
“Query_time: 0.000495”：执行花费的时长【单位：秒】
“Lock_time: 0.000170”：执行获得锁的时长
“Rows_sent”：获得的结果行数
“Rows_examined”：扫描的数据行数
“SET timestamp”：这SQL执行的具体时间
最后一行：执行的SQL语句

慢查询分析mysqldumpslow

mysqldumpslow -s r -t 10 /usr/local/soft/mysql8/datas/mysql/ip-10-250-0-214-slow.log

# 参数说明：
-s 对结果进行排序，怎么排，根据后面所带的 (c,t,l,r,at,al,ar)，缺省为at
  c:总次数 t:总时间 l:锁的时间 r:获得的结果行数
  at,al,ar :指t,l,r平均数 【例如：at = 总时间/总次数】
-t NUM just show the top n queries：仅显示前n条查询
-g PATTERN grep: only consider stmts that include this string：通过grep来筛选语句。

SET GLOBAL slow_query_log=1 只对当前运行实例生效，重启后丢失，线上应写进 MySql单节点、主从、双主的构建方法。SET GLOBAL long_query_time 只影响之后新建的连接，当前会话还要再 SET SESSION long_query_time=...。默认 10 秒太宽，生产常见是 1~2 秒；log_queries_not_using_indexes 容易把日志打爆，一般保持关闭，用下面的 performance_schema 来抓全表扫描。

慢SQL如何排查

排查就五步：把慢SQL找出来 → 看数字判断慢在哪 → EXPLAIN 看怎么走 → 对症改 → 回归验证。不要一上来就加索引，先分清是 SQL 本身慢、锁等待、还是机器 IO/CPU 已经打满。

1. 把慢SQL找出来

正在发生的、已经发生的、没开慢日志的，三条路。

正在卡住：看当前线程，做法见 MySql--线程管理

SHOW FULL PROCESSLIST;
-- 或
SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO
FROM information_schema.processlist
WHERE COMMAND != 'Sleep' ORDER BY TIME DESC;

Time 很大且 State 长期停在 Sending data / Creating sort index / Waiting for ... lock，这条就是现场。Waiting for ... lock 先去查锁，不要先改 SQL，锁相关见 MySql--锁。

已经发生过：慢查询日志 + mysqldumpslow（见上文）。优先看：
- -s t 总耗时最高：对系统伤害最大
- -s c 次数最多：单次不慢、积少成多
- -s r 扫描/返回行数最多：典型没走好索引
没开慢日志，MySQL 8.0 默认开了 performance_schema，直接问摘要表：

SELECT SCHEMA_NAME,
       DIGEST_TEXT,
       COUNT_STAR,
       ROUND(SUM_TIMER_WAIT/1e12, 3)  AS total_s,
       ROUND(AVG_TIMER_WAIT/1e12, 3)  AS avg_s,
       SUM_ROWS_EXAMINED,
       SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
WHERE SCHEMA_NAME IS NOT NULL
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

装了 sys 库更省事：

1
2
3

SELECT * FROM sys.statement_analysis ORDER BY total_latency DESC LIMIT 10;
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;
SELECT * FROM sys.statements_with_sorting LIMIT 10;

2. 先看数字，判断慢在哪

慢日志里三个数字最有用，先定性再动手：

现象	慢在哪	下一步
`Lock_time` 接近 `Query_time`	在等锁，SQL 本身不一定差	MySql--锁、MySql--线程管理
`Rows_examined` 远大于 `Rows_sent`	扫了很多行才筛出几行，索引/条件有问题	`EXPLAIN`，再看 MySql索引
`Rows_examined` 不大，但 `Query_time` 很大	单行很重，或结果集太大、磁盘 IO、网络	少查列、分页、看 Buffer Pool
次数极多、单次耗时一般	热点 SQL 被循环调用	先改调用次数，再改 SQL

经验值：Rows_examined / Rows_sent 越大越冤。返回 10 行却扫了 100 万行，优先查索引，不要先加机器。

3. EXPLAIN 看优化器怎么走

字段含义见 MySql执行计划。排查时先盯这几列：

type：从好到差大致是 system > const > eq_ref > ref > range > index > ALL。ALL 就是全表扫描，单表数据量大时基本就是慢 SQL。
key 为 NULL：没用上索引。对照 possible_keys，有候选却没用，多半是条件写法和索引不匹配。
rows：优化器估的扫描行数，越大越慢。这是估算，和慢日志里真实的 Rows_examined 对一下。
Extra：
- Using filesort / Using temporary：排序或分组没走索引
- Using where：存储引擎取出后再用 where 过滤，往往扫描偏多
- Using index：覆盖索引，通常是好事
- Using index condition：索引条件下推，比回表后再过滤好

MySQL 8.0.18 起可以用 EXPLAIN ANALYZE，跑一遍真实执行，给出每一步实际耗时和实际行数，比普通 EXPLAIN 更接近现场：

1	EXPLAIN ANALYZE SELECT * FROM employees WHERE name = 'LiLei' AND age = 22;

改 SQL 或加索引之后，用 SHOW WARNINGS 看优化器重写后的语句，避免自己以为改对了、优化器其实没按你想的走。

4. 对症改

SQL / 索引问题（最常见）
- where / join / order by / group by 列上没有合适索引，或违反最左前缀。联合索引、覆盖索引、前缀索引见 MySql索引。
- 索引列包了函数、做了运算、隐式类型转换（'123' 和 123、字符集不一致），索引失效，表现就是 possible_keys 有值但 key 为 NULL，或 type=ALL。常见场景和改法见 MySql索引。
- SELECT * 导致无法覆盖索引，必须回表；只查需要的列。
- 深分页 LIMIT 100000, 20：先按索引把主键翻出来再回表，不要直接大 offset。
- 统计信息不准，优化器选错索引：ANALYZE TABLE t; 更新索引基数。
锁等待
- Lock_time 高、State 带 lock、SHOW ENGINE INNODB STATUS 里有 LOCK WAIT。先找长事务、没提交的 for update，再考虑是不是没走索引把行锁升级成了表锁，见 MySql--锁。
执行环境
- Buffer Pool 命中率低，热数据全在磁盘上，见 MySql--InnoDB Buffer Pool。
- 单次返回结果集过大，时间花在 Sending data 和网络上，加分页、少选列。
- 服务器 CPU / 磁盘已经打满，所有 SQL 一起变慢：这不是单条 SQL 的问题，先看机器和连接数。

5. 回归验证

同一条 SQL 再 EXPLAIN / EXPLAIN ANALYZE，确认 type、key、rows 变好了。
用生产可比的数据量看耗时，不要只在几十行的测试表上宣布优化成功。
观察慢日志和 events_statements_summary_by_digest 里这条 digest 的 COUNT_STAR、AVG_TIMER_WAIT 是否下降。
加索引后用 SHOW INDEX 看 Cardinality，并盯一下写入是否变慢——索引不是免费的，原则见 MySql索引。

慢SQL问题如何排查？

先分清三种慢：SQL 执行慢（扫描行数多）、等锁慢（Lock_time 高）、环境慢（IO/CPU/网络/结果集）。搞混了就会给一条在等锁的 SQL 乱加索引。
找 SQL：现场用 SHOW PROCESSLIST，历史用慢查询日志 + mysqldumpslow，没开慢日志用 performance_schema.events_statements_summary_by_digest 或 sys.statement_analysis。
定性：Rows_examined >> Rows_sent 查索引；Lock_time 接近总耗时查锁；次数多单次不慢查调用方。
分析：EXPLAIN 看 type / key / rows / Extra，8.0 用 EXPLAIN ANALYZE 看真实耗时。type=ALL、key=NULL、Using filesort、Using temporary 是最常见的红灯。
处理：条件与索引对齐（最左前缀、避免函数和隐式转换）、能覆盖就覆盖、深分页改写、ANALYZE TABLE 刷新统计信息；等锁就收短事务；环境问题看 Buffer Pool 和结果集大小。
验证：同样数据量复测，digest 统计下降才算完。线上务必把慢日志写进配置并定期扫，不要只靠临时 SET GLOBAL。