MySQL数据库响应缓慢如何排查？慢查询、索引与执行计划分析

业务群里最常被 @ 的一句话是"网站好卡"。这种反馈没有任何诊断价值，因为它可能来自前端、网络、应用线程池，也可能真的来自数据库。数据库侧变慢有三个可观测的共同特征：CPU 或 IO 打满、连接数上涨、慢查询日志突增。本文给出一条从"是不是数据库慢"到"具体哪条 SQL 慢、为什么慢、怎么改"的完整排查链路，包含可直接执行的命令、执行计划逐列解读和一套索引优化的判断标准。

一、先分层：确认慢在数据库，而不是别处

在没有证据之前不要急着重启数据库。先回答三个问题：慢是全站还是单接口？慢是持续性还是周期性？慢的 SQL 有没有共性（某个表、某个时间段）？

1.1 三条命令锁定瓶颈方向

# 1) 负载与 CPU：load average 持续 > CPU 逻辑核数，说明已经排队
uptime
top -c -b -n 1 | head -20

# 2) IO：%util 接近 100%、await > 10ms（SSD 场景），说明磁盘是瓶颈
iostat -xm 2 3
sar -d 2 3

# 3) MySQL 内部：当前正在执行的语句
mysql -uroot -p -e "SHOW FULL PROCESSLIST\G"

• load 高 + CPU sys 高 + IO 正常：可能是大量并发、上下文切换、临时表排序。
• CPU user 高 + 少量连接长期 running：典型全表扫描或大结果集排序。
• IO util 高 + await 大：扫描行数太多、buffer pool 命中率低，或者 redo / binlog 刷盘压力大。
• 都正常但 SQL 不走：锁等待，用 SHOW ENGINE INNODB STATUS\G 看 LATEST DETECTED DEADLOCK 与 TRANSACTIONS 段。

1.2 用 buffer pool 命中率快速判断 IO 是否吃紧

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';

命中率 = 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests。OLTP 场景应长期保持在 99% 以上；低于 98% 通常意味着热数据放不进内存，或者 SQL 扫描范围过大把冷数据搅进来了。当 Innodb_buffer_pool_wait_free 大于 0 时，说明 InnoDB 找不到空闲页、必须等脏页刷完，此时 IO 必然成为瓶颈。

二、打开慢查询日志：让证据自己跳出来

慢查询日志（slow log）默认是关闭的，long_query_time 默认值为 10 秒——这在 Web 场景几乎抓不到东西。线上建议设为 1 秒甚至 0.5 秒。

# 在线开启（重启失效）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL min_examined_row_limit = 100;
SET GLOBAL log_slow_admin_statements = 'ON';
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

# MySQL 8.0 推荐持久化写法
SET PERSIST slow_query_log = 'ON';
SET PERSIST long_query_time = 1;

配置文件写法（永久生效）：

[mysqld]
slow_query_log              = ON
slow_query_log_file         = /var/lib/mysql/slow.log
long_query_time             = 1
min_examined_row_limit      = 100
log_queries_not_using_indexes = OFF      # 长期开启会淹没日志，只在排查窗口临时开
log_slow_admin_statements   = ON
log_throttle_queries_not_using_indexes = 10

关于 log_queries_not_using_indexes：开启后会把所有未走索引的语句记下来，对优化很有价值，但在表多、扫描频繁的系统里日志量会爆炸，建议只在排查窗口临时打开，并配合 log_throttle_queries_not_using_indexes 限流。min_examined_row_limit = 100 可以过滤掉"扫描 3 行但恰好超时"的噪声。

2.1 两种分析方式

# 方式一：mysqldump 自带的 mysqldumpslow，无需安装
mysqldumpslow -s t -t 20 /var/lib/mysql/slow.log
mysqldumpslow -s c -t 20 -g 'SELECT' /var/lib/mysql/slow.log
# -s t 按总耗时排序；-s c 按出现次数排序；-s at 按平均耗时；-g 做正则过滤

# 方式二：Percona Toolkit 的 pt-query-digest（推荐，输出带 Rows_examined 分布）
pt-query-digest /var/lib/mysql/slow.log > /tmp/slow_report.txt
pt-query-digest --limit 10 --filter '$event->{Rows_examined} > 1000' /var/lib/mysql/slow.log
head -40 /tmp/slow_report.txt

pt-query-digest 的报告里最关键的三列是：Response time（总耗时占比）、Calls（执行次数）、Rows examined（平均扫描行数）。优化收益 = 总耗时占比，所以优先看排第一的那条；但也要注意"Calls 巨大但单次很快"的语句，累积成本同样可观。

2.2 不想开日志时用 performance_schema

PERFORMANCE_SCHEMA 默认开启，能把语句按指纹（digest）聚合，粒度到毫秒，且不需要重启：

SELECT schema_name, digest_text, count_star AS cnt,
       ROUND(avg_timer_wait/1000000000000, 3) AS avg_sec,
       ROUND(max_timer_wait/1000000000000, 3) AS max_sec,
       sum_rows_examined, sum_rows_sent, sum_created_tmp_disk_tables
FROM performance_schema.events_statements_summary_by_digest
WHERE schema_name IS NOT NULL AND digest_text NOT LIKE 'SHOW%'
ORDER BY sum_timer_wait DESC LIMIT 10\G

注意 timer 的单位是皮秒，除以 10 的 12 次方才是秒。sum_rows_examined 远大于 sum_rows_sent 的语句，几乎一定是访问路径有问题（扫描多、返回少）。另外还可直接用 sys schema 的现成视图：

SELECT * FROM sys.statements_with_full_table_scans LIMIT 10\G
SELECT * FROM sys.statements_with_sorting LIMIT 10\G
SELECT * FROM sys.statements_with_temp_tables LIMIT 10\G
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile LIMIT 10\G

三、EXPLAIN 逐列怎么看

拿到可疑 SQL 后，用 EXPLAIN 问优化器"你打算怎么执行"，再用 EXPLAIN ANALYZE（MySQL 8.0.18 起支持）问"实际执行成了什么样子"。

EXPLAIN SELECT o.id, o.amount, u.nickname
FROM orders o JOIN users u ON o.user_id = u.id
WHERE o.status = 1 AND o.created_at >= '2025-01-01'
ORDER BY o.created_at DESC LIMIT 20\G

EXPLAIN FORMAT=JSON SELECT ...\G    -- 输出含 cost 估算、索引选择原因
EXPLAIN ANALYZE SELECT ...\G        -- MySQL 8.0.18+，输出实际耗时与实际行数

3.1 key_len 的计算

key_len 能告诉你在联合索引里到底用到了几列：

• INT NOT NULL 占 4 字节，可为 NULL 时 +1 字节。
• BIGINT NOT NULL 占 8 字节。
• DATETIME（MySQL 5.6 之后）占 5 字节。
• VARCHAR(n) 在 utf8mb4 下每字符最多 4 字节，另加 2 字节长度前缀，可为 NULL 时再加 1 字节。例如 VARCHAR(64) utf8mb4 NOT NULL 的 key_len 为 64×4+2 = 258。

举例：索引 idx_status_created(status, created_at)，其中 status 为 TINYINT NOT NULL（1 字节）、created_at 为 DATETIME NOT NULL（MySQL 5.6 之后 5 字节）。若 key_len = 6，说明两列都被用到，ORDER BY created_at 可以直接顺着索引扫描、不需要额外排序；若 key_len 只有 1，说明只用到了 status，created_at 的排序无法借助索引完成，几乎必然出现 Using filesort。注意若列允许为 NULL，索引还要额外占 1 字节做 NULL 标志位。

3.2 Extra 里的几个关键词

• Using index：覆盖索引，数据全部从索引拿到，不回表，理想状态。
• Using index condition：索引条件下推（ICP），把过滤下推到存储引擎，减少回表次数，好现象。
• Using where：在 Server 层再次过滤，正常现象。
• Using filesort：无法利用索引顺序，做了额外排序。小结果集无所谓，大结果集是性能杀手。
• Using temporary：创建了临时表（GROUP BY、DISTINCT、UNION、子查询物化常见）。若同时出现 Using filesort 且落到磁盘（Created_tmp_disk_tables 上涨），性能会断崖式下降。
• Using join buffer：被驱动表没有可用索引，走 Block Nested Loop 或 Hash Join（MySQL 8.0.18 起支持 hash join），说明缺索引。

四、索引为什么没生效：七个高频原因

还有一点容易被忽略：SELECT *。它不但多传了网络字节，还会让覆盖索引失效，逼迫 InnoDB 回表。把 SELECT 列表收敛到真正需要的字段，往往比加索引更快见效。深分页也是典型大户：LIMIT 100000, 20 需要先扫描并丢弃十万行，改成延迟关联或游标翻页效果立竿见影：

-- 原始慢 query
SELECT id, title FROM articles ORDER BY created_at DESC LIMIT 100000, 20;

-- 改法一：延迟关联（先走覆盖索引拿主键）
SELECT a.id, a.title FROM articles a
JOIN (SELECT id FROM articles ORDER BY created_at DESC LIMIT 100000, 20) t USING (id);

-- 改法二：游标分页（推荐，要求有序且有稳定唯一键）
SELECT id, title FROM articles WHERE id < last_id ORDER BY id DESC LIMIT 20;

关于索引数量：加索引不是免费的。每个二级索引都会参与写入、占用 buffer pool、增加 redo。可以用 sys schema 找出冗余和从未被使用的索引：

SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;
SHOW INDEX FROM orders;
ANALYZE TABLE orders;

五、一套可复用的排查流程

1. 确认慢的范围：单接口还是全站，持续性还是周期性，先看 uptime / iostat / top 判断 CPU、IO、锁的方向。
2. 看 MySQL 内部状态：Innodb_buffer_pool_reads 命中率、Threads_running、SHOW ENGINE INNODB STATUS 是否有锁等待。
3. 开慢查询日志（long_query_time 设为 1 秒），或用 performance_schema / sys schema 直接聚合。
4. 用 pt-query-digest 或 mysqldumpslow 排序，优先处理"响应时间占比第一"的语句。
5. 对目标 SQL 跑 EXPLAIN 和 EXPLAIN ANALYZE，重点看 type、key、rows 与 Extra。
6. 比对"预估 rows"与"实际 rows"，差距大就先 ANALYZE TABLE 刷新统计信息。
7. 按第四节的表单逐项自查：类型、函数、最左前缀、OR、深分页。
8. 设计补丁：加/改索引、改写 SQL、收敛 SELECT 字段、改分页方式。
9. 在从库或压测环境验证，对比 EXPLAIN 的 type 与 rows 变化，以及 Handler_read_% 状态的变化。
10. 灰度上线后观测 24 小时，确认慢日志里该语句消失、整体 QPS/RT 曲线回落到正常水位。

小结

MySQL 响应缓慢的排查诀窍是"不要猜，要度量"。第一步用系统工具和状态变量确定瓶颈在 CPU、IO 还是锁；第二步用慢查询日志或 performance_schema 把模糊的"慢"变成一条具体的 SQL；第三步用 EXPLAIN 的执行计划（type、key、key_len、rows、Extra）解释为什么慢，用 EXPLAIN ANALYZE 验证优化器的判断是否靠谱；最后用索引重建、SQL 改写、分页方式调整去收敛扫描行数。日常避坑：不要一看到慢就调大连接数和 buffer pool；不要在 SQL 语句里对索引列套函数或做隐式类型转换；不要为了让某条 SQL 走特定索引而堆一大堆冗余索引；改写完之后一定要回到慢日志复核，而不是看一次 EXPLAIN 就收工。