慢 SQL 分析与索引失效调优实战

1. 为什么慢 SQL 是性能黑洞

一句话总结： 一条被 1 万请求触发的慢 SQL，其危害等价于 1 万次全表扫描——定位并消灭它是数据库性能治理的第一步。

慢 SQL 之所以危险，不在于某次查询慢，而在于它的放大效应。绝大多数慢查询都具有「低频 SQL × 高频率调用」的组合：单次执行 3 秒的查询看似可接受，但当它被 100 QPS 的接口反复调用时，数据库线程池迅速被打满，活跃连接飙升，最终拖垮整个实例。

优化收益的量级对比：
  全表扫描 (1M 行)  →  约 300ms
  加索引查询        →  约 1-5ms
  命中覆盖索引      →  约 0.3ms

建一个正确的索引，常比堆更多硬件便宜 100 倍。
数据库性能优化的第一课：不是加机器，是消灭慢 SQL。

一个健康的数据库，慢查询应接近零。调优的目标不是"这条 SQL 跑得快一点"，而是让整条链路的 Latency P99 稳定可控。

2. 慢查询日志：找到猎物的第一步

2.1 开启慢日志

慢日志是命中问题的探照灯。没开启或不合理设置，等于蒙着眼调优。

-- MySQL 动态开启（无需重启，但重启后失效，需配合 my.cnf）
SET GLOBAL long_query_time = 1;          -- 阈值：>1 秒记为慢
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- 永久生效：写入 my.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1   -- 记录所有未走索引的查询
min_examined_row_limit = 100         -- 扫描超过 100 行才记录，过滤噪声

生产环境 long_query_time 建议先设置 2s 采集一周，再逐步下调到 1s。不要一上来就 0.1s，否则慢日志会瞬间被淹没。

2.2 用 mysqldumpslow / pt-query-digest 聚合

逐个看慢日志不现实，关键是聚合：

# 按执行次数/耗时聚合统计
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log   # 按平均耗时排前 10

# pt-query-digest 更强大：报告每类 SQL 的执行次数、总耗时、平均扫描行数
pt-query-digest /var/log/mysql/slow.log > digest.txt

聚合报告会告诉你哪几类查询贡献了 80% 的慢查询时长（典型的长尾效应）。调优收益最大的总是那几个头部问题，而不是均匀用力。

3. EXPLAIN 深度解读：看懂执行计划

拿到慢日志后，下一步是用 EXPLAIN 分析其执行计划。

3.1 EXPLAIN 关键列速查

EXPLAIN 不是玄学，每一列都对应优化器的一次决策。

+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | users | ref  | idx_email     | idx_email | 102 | const |   1 |  100.00  | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+----------+-------+

关键列的含义与最优取值：

type=ALL（全表扫描）是最常见的元凶，其次是 type=range 但扫描范围过大。一个健康的查询应至少达到 range 以上。

3.2 一份看不懂会踩坑的执行计划

EXPLAIN SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.city = 'Beijing'
  AND o.status = 'completed';

+------+-------------+-------+----------+---------+-------------------+-------
| type | key         | rows  | Extra
+------+-------------+-------+----------+-------------------+------+
| ref  | idx_city    | 5000  |          |
| ALL  | NULL        | 200000| Using where; Using temporary; Using filesort
+------+-------------+-------+----------+-------------------+------+

orders 表是驱动方向（在这里是第二行），type=ALL + Using filesort + Using temporary 三条红灯同时亮起——排序和分组压到了磁盘临时表。这类查询在 orders.status 上建组合索引即可显著缓解。读执行计划时，重点不是看它"用没用索引"，而是看哪一侧是短板。

4. 12 种索引失效场景（调优必查清单）

索引不总是生效。以下 12 种场景是慢 SQL 最常见的根因，逐条核对能覆盖绝大多数问题。

核心原则： 紧贴函数、类型转换、运算、前导 %、违反最左前缀，都是优化器"无法使用 B+ 树有序性"的场景。B+ 树索引依赖列值的原生有序性，一旦引入变换，有序性即被破坏。

5. 统计信息与直方图：优化器"看不见"的本质

有时候 SQL 写法没问题、索引也建了，可优化器还是选了全表扫描。这往往是统计信息陈旧或选择性估错。

-- 手动生成/更新统计信息
ANALYZE TABLE orders;                -- 触发统计信息更新
ANALYZE TABLE orders UPDATE HISTOGRAM ON status, category;  -- MySQL 8.0+ 建直方图

-- 查看当前统计
SHOW STATS META;                     -- (TiDB/部分 MySQL 兼容)
SELECT * FROM mysql.innodb_table_stats;

优化器基于 rows × filtering 估算代价。当数据分布严重倾斜（例如 99% 是 ‘ok’，1% 是 ‘failed’），而统计看不到直方图时，优化器会误以为 status='failed' 选择性很高，从而放弃索引走全表扫。给倾斜列建直方图常能立竿见影。

6. 调优闭环：一个真实案例

一句话： 慢 SQL 治理不是一次性的，而是一套「定位 → 分析 → 修复 → 回归」的闭环。

[定位] 慢日志 → [归因] pt-query-digest
   ↓
[分析] EXPLAIN + 统计信息
   ↓
[修复] 索引 / 改写 SQL / 分库分表 / 缓存
   ↓
[回归] 对比优化前后 EXPLAIN 的 type/rows + 压测 P99

案例：订单详情页 500ms → 8ms

症状： orders 表 800w 行，SELECT * FROM orders WHERE user_id=? ORDER BY id DESC LIMIT 10 平均耗时 500ms。

分析：

• EXPLAIN 显示 type=ALL，rows=800w → 全表扫描后排序。
• 原因：虽然建了 idx_user(user_id)，但 ORDER BY id 需要回表排序，优化器觉得回表代价高。

修复：

-- 组合索引 (user_id, id)：把排序字段并进来，走覆盖索引，fprintf=100%
ALTER TABLE orders ADD INDEX idx_user_created(user_id, id);

-- 结果：type=range，rows=2，耗时 2ms，命中覆盖索引无回表

效果对比：

调优的胜利往往来自消除一次"全表扫描"，而不是把索引类型从 ref 换成 const。面关系型数据，绝大多数查询难题都可以被「合适的组合索引 + 正确的最左前缀 + 覆盖索引」解决。

7. 慢 SQL 调优避坑清单

8. 总结

慢 SQL 调优是一门「工程方法 > 灵光」的技术。核心闭环包括：

1. 定位：合理开启慢日志 + 用 pt-query-digest 聚合归因，找准头部高频慢查询。
2. 分析：用 EXPLAIN 读 type/rows/key/Extra，逐条对照 12 种索引失效场景。
3. 修复：优先加组合索引、改 SQL 消除隐式转换与函数，必要时建直方图纠正统计信息。
4. 回归：优化前后对比执行计划与耗时，形成可复现的优化基线。

真正的高手，是把「每次全表扫描」都可能变成「一次覆盖索引命中」。调优恒久，且来自对 B+ 树有序性与优化器决策模型的深刻理解——而不是堆机器、堆参数。

延伸阅读

• 数据库查询优化与执行计划
• B+ 树索引原理
• 数据库索引设计与优化（索引失效场景详解）