📌 今日关键词：慢查询诊断、排障方法论、性能优化、实战案例

大家好呀！我是 数据库小学妹 👋

前几天接到一个任务：“小学妹，这个报表查询太慢了，每次都要10秒，用户投诉好几次了，你帮忙看看怎么优化。”

我信心满满地跑去执行 SQL，结果……真的卡住了！10 秒，整整 10 秒，屏幕转圈转得我心态都崩了 😅

后来跑了十几轮优化，终于把查询时间从10 秒降到 0.1 秒！今天把压箱底的"5 步排障法"分享出来，建议先收藏再看！

---

一、5 步排障法框架

遇到慢查询，很多人第一反应就是"加索引"。但我踩过的坑告诉你：盲目加索引 = 乱花钱，还可能帮倒忙！

正确姿势是这样的：

📋 5 步排障法
1️⃣ 发现：找到慢查询在哪里
2️⃣ 收集：收集诊断信息
3️⃣ 分析：定位根因
4️⃣ 优化：制定方案并实施
5️⃣ 验证：确认效果

---

二、第一步：发现慢查询

1. 开启慢查询日志

MySQL 默认不记录慢查询，得先打开开关：

SHOW VARIABLES LIKE 'slow_query_log%';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

生产环境阈值建议设 1-2 秒，太低日志太多；开发环境可以设低些方便调试。

2. 查看慢查询日志

用 mysqldumpslow 工具快速找出最慢的查询：

mysqldumpslow -s t -t 10 /var/log/mysql/slow.log
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

输出示例：

Count: 158  Time=3.25s  Lock=0.00s  Rows=10000  SELECT * FROM orders WHERE create_time...

这条 SQL 平均执行 3.25 秒，被执行了 158 次！优先优化它。

3. 神器推荐：pt-query-digest

装了这个工具，分析更高效：

pt-query-digest --type=slowlog /var/log/mysql/slow.log | head -50

这个工具按执行时间排序，给出优化建议，发现查询模式问题。

工具安装：yum install percona-toolkit

---

三、第二步：收集诊断信息

找到目标 SQL 后，先诊断，别急着优化！

1. EXPLAIN 分析（深入篇）

之前学过 EXPLAIN 基础，今天说点高级的：

EXPLAIN FORMAT=JSON 
SELECT o.*, u.name 
FROM orders o 
JOIN users u ON o.user_id = u.id 
WHERE o.create_time > '2026-01-01';

JSON 格式能看到 cost_info、used_columns、attached_condition 这些详细信息。

MySQL 8.0+ 还可以用 EXPLAIN ANALYZE 实际执行并获取真实统计信息。

2. 查看当前正在跑的查询

查询正在卡住？用这个看看它在干什么：

SHOW FULL PROCESSLIST;

• Time：执行了多少秒
• State：当前状态（Sending data、Creating tmp table）
• Info：正在执行的 SQL

Time 很大的查询，说明已经卡很久了！

3. 查看系统状态

看看数据库当前的"健康状况"：

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW GLOBAL STATUS LIKE 'Created_tmp%';
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';

指标	含义	正常值
Innodb_buffer_pool_read_requests	缓存命中次数	越高越好
Created_tmp_tables	临时表创建次数	越低越好
Slow_queries	慢查询数量	越低越好

---

四、第三步：分析根因

收集完信息，开始找问题。最常见的 4 类慢查询根因：

根因 1：全表扫描

看 EXPLAIN 的 type 字段：

EXPLAIN SELECT * FROM orders WHERE order_no = 'A12345';

输出：

type: ALL（全表扫描，500万行一条一条查！）
key: NULL（没用到索引）

解决方案：给 order_no 字段加索引

CREATE INDEX idx_order_no ON orders(order_no);

根因 2：索引失效

明明有索引，为啥不用？

场景 A：索引列用了函数

-- 索引失效
SELECT * FROM orders WHERE YEAR(create_time) = 2026;

-- 改用范围查询
SELECT * FROM orders WHERE create_time >= '2026-01-01' AND create_time < '2027-01-01';

场景 B：隐式类型转换

-- order_no 是 VARCHAR，查询用了数字
SELECT * FROM orders WHERE order_no = 12345;

-- 类型匹配
SELECT * FROM orders WHERE order_no = '12345';

场景 C：最左前缀不匹配

-- 联合索引是 (a, b, c)，查询跳过了 a
SELECT * FROM table WHERE b = 1 AND c = 2;

-- 包含最左前缀
SELECT * FROM table WHERE a = 1 AND b = 1;

根因 3：关联效率低

多表 JOIN 容易出问题：

EXPLAIN SELECT o.*, u.name 
FROM orders o 
JOIN users u ON o.user_id = u.id 
WHERE o.create_time > '2026-01-01';

如果出现 type = ALL、rows 很大、Using filesort，说明有问题。

优化方案：给关联字段加索引，用小表驱动大表，避免 SELECT *。

根因 4：分页深度偏移

这个坑很多人踩过！

-- 超级慢（第 10000 页，每页 10 条）
SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 99990;

-- 改用游标分页
SELECT * FROM orders WHERE id > 99990 ORDER BY id LIMIT 10;

OFFSET 会让 MySQL 先跳过 99990 行，再取 10 行。跳过的行越多越慢。游标分页直接定位，不跳过。

---

四、第四步：优化实施

根据分析结果选择对应方案：

方案 1：加索引

CREATE INDEX idx_field ON table_name(field);
CREATE INDEX idx_a_b ON table_name(a, b, c);
SHOW INDEX FROM table_name;

索引不是越多越好，每个索引都会影响写操作性能。优先给 WHERE、JOIN、ORDER BY 的字段加索引，定期清理不用的索引。

方案 2：重写 SQL

换个写法，效果天差地别：

-- 子查询效率低
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE status = 'paid');

-- 改用 JOIN
SELECT u.* FROM users u 
INNER JOIN orders o ON u.id = o.user_id 
WHERE o.status = 'paid';

方案 3：拆分大查询

海量数据需要"分而治之"：

-- 按月份拆分
SELECT * FROM logs WHERE created_at BETWEEN '2025-01-01' AND '2025-01-31';
SELECT * FROM logs WHERE created_at BETWEEN '2025-02-01' AND '2025-02-28';

---

五、第五步：验证效果

优化完了一定要验证！别凭感觉。

1. 重新执行 EXPLAIN

EXPLAIN SELECT * FROM orders WHERE order_no = 'A12345';

优化后应该是：type = ref 或 eq_ref（不再是 ALL），key = 索引名（不再是 NULL），rows = 几十或几百（不再是几十万）。

2. 实际执行时间对比

SET @start = NOW();
SELECT * FROM orders WHERE order_no = 'A12345';
SELECT TIMESTAMPDIFF(MICROSECOND, @start, NOW()) / 1000000 AS '耗时(秒)';

我的真实案例：

阶段	执行时间	优化手段
优化前	10.35 秒	无
第 1 轮	3.20 秒	加索引
第 2 轮	0.85 秒	避免 SELECT *
第 3 轮	0.12 秒	覆盖索引
最终	0.08 秒

3. 持续监控

SHOW GLOBAL STATUS LIKE 'Slow_queries';

用 Prometheus + Grafana 配置告警，长期跟踪。

---

六、常见调优场景速查表

场景	表现	解决方案
分页慢	OFFSET 很大	改用游标分页
JOIN 慢	多表关联	确保关联字段有索引，小表驱动大表
排序慢	Using filesort	给 ORDER BY 字段加索引
计数慢	COUNT(*) 慢	用近似值或缓存
插入慢	单条插入	改用批量插入

---

七、学习心得

今天的内容总结成三句话：

1️⃣ 先发现，再诊断，别盲目加索引！ 用慢查询日志 + pt-query-digest 定位问题 SQL

2️⃣ EXPLAIN + PROCESSLIST + STATUS，三招走天下 组合使用能看到完整信息

3️⃣ 优化后必须验证 用 EXPLAIN 和实际执行时间对比，确保真的生效

遇到慢查询千万别慌，按这个流程来，你也能把 10 秒变成 0.1 秒！

---

👋 我是 数据库小学妹，一个用 设计师思维 学数据库的转行人。我们一起，把复杂的技术变得简单有趣！💕

---

本文基于 MySQL 8.0 编写，不同版本略有差异，建议先在测试环境验证。