祥哥的技术博客

主题

📅 2025年8月10日 🏷️ MySQL ⏱️ 12 分钟阅读 mysqlslow-sqlexplainindexperformance

慢 SQL 优化方法论：从 EXPLAIN 到索引设计 ​

本文是 MySQL 系统学习系列的 L3 实战篇。前置：索引的本质：B+ 树、页与查找过程。 学完可以配合面试题食用：EXPLAIN 执行计划、慢 SQL 优化与索引失效场景、大偏移量分页优化

先解决一个问题：怎么发现慢 SQL ​

线上数据库不会主动告诉你哪条 SQL 慢，得先让 MySQL 自己记下来。打开慢查询日志只需要三个参数：

sql
-- 查看当前配置
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time%';

-- 开启慢日志，阈值设为 1 秒
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;

两个细节容易被忽略。一是 long_query_time 的默认值是 10 秒，很多系统一直用默认值，等于没开；线上建议压到 0.1 甚至 0.05，慢日志量不大时宁低勿高。二是默认不记录全表扫描的 SQL，log_queries_not_using_indexes = ON 可以把"没用索引的查询"也抓出来，哪怕它跑得快——今天的快查询，数据涨了就是明天的慢查询。

日志抓到的是一条条原始记录，光看没用，要聚合。pt-query-digest（Percona Toolkit）按指纹（query fingerprint，把字面值归一化后的 SQL 模板）分组统计，输出谁的总耗时最高、平均扫描行数多大：

bash
pt-query-digest /var/log/mysql/slow.log | head -30
# 输出按 total r/95% ETA 指纹分组排序，Rank 1 的就是首要优化对象

优先修什么？看两个数的乘积：执行次数 × 单次耗时。一条 50ms 但每天跑一千万次的 SQL，比一条 5 秒但每天跑 3 次的 SQL 更值得先处理。

EXPLAIN：读懂执行计划再动手 ​

拿到慢 SQL，第一步不是加索引，是 EXPLAIN 它。EXPLAIN 的输出字段很多，抓住三个层次就够了。

第一层看 type（访问类型，从好到差）：

text
system > const > eq_ref > ref > range > index > ALL

写 SQL 的底线是别出 ALL（全表扫描）。index 看起来比 ALL 好一点，实际是"扫描整棵二级索引树"，行数没少多少，叫覆盖索引时才有意义。range 是索引范围扫描，绝大多数优化后的落点都在这。一个经验阈值：单表数据量过 10 万还敢跑 ALL 或 index 的，EXPLAIN 阶段就该打回。

第二层看 key_len（实际用到的索引长度）：

这一列能算出联合索引用了几列。规则是：INT 不带 NULL 占 4 字节；VARCHAR(n) 在 utf8mb4 下占 4n+2 字节，可空再 +1。比如 idx(a_int, b_varchar32) 完整命中时 key_len = 4 + (4×32+2) = 134；如果 EXPLAIN 显示 4，说明只有 a 走了索引，b 是在索引上顺带过滤的——该建的联合索引没建对顺序。

第三层看 Extra（附加信息，有几个高危值）：

  • Using filesort：排序没走上索引，需要额外排序缓冲区
  • Using temporary：建了临时表，GROUP BY / DISTINCT 没命中索引时的典型信号
  • Using index：覆盖索引，免回表，好信号
  • Using index condition：ICP 生效，下推到存储引擎层过滤，也是好信号

常见误读提醒：rows 是优化器基于统计信息的估算值，不是精确值，统计信息过期时能差一个数量级，别拿它当真相，拿它当方向。另外 type=ref 不一定比 range 慢——访问类型排的是"结构性好坏"，具体快慢还要看扫描行数与回表次数。

索引失效的场景，本质只有一种 ​

"索引失效八大场景"这类清单可以背，但更省脑子的办法是理解共性：B+ 树靠索引列的原始值有序排列来二分，任何让优化器"无法对列的原始值做范围界定"的写法，都会让它放弃这棵树。

逐个场景对号入座：

  • 对索引列用函数或运算：WHERE YEAR(create_time) = 2025。索引存的是 create_time 原值，按 YEAR() 的结果并不可有序，只能全扫。改写成 create_time >= '2025-01-01' AND create_time < '2026-01-01' 就能用上 range。
  • 隐式类型转换：phone 是 VARCHAR，WHERE phone = 13800138000 传了数字。MySQL 的规则是把字符串列转成数字再比，等价于对列套了 CAST 函数，回到上一条。反过来（INT 列传字符串 '123'）不失效，因为转换发生在常量侧。
  • 前导模糊：LIKE '%abc'。B+ 树按左边的值排，左边是通配符就没法定位起点。LIKE 'abc%' 可以走 range。
  • OR 混合了无索引列：WHERE a = 1 OR b = 2，b 没索引，则 a 的索引也用不上（用了还得回表再查 b 的部分，优化器直接放弃）。要么给 b 也建索引，要么改写成 UNION。
  • 联合索引不满足最左前缀：索引 (a,b,c)，查询只给了 b 和 c。树是先按 a 排再按 b 排，a 缺失则 b、c 的有序性无从谈起。
  • NOT IN / != / NOT LIKE：否定条件给出的是"补集"，补集在 B+ 树上不连续，通常只能全扫（并非绝对，优化器有 index dive 统计兜底，但大多数场景如此）。

清单还能列更长，但归类后就是一句话：保证条件能转化为对索引列原始值的连续区间。

深分页：LIMIT 1000000, 20 为什么慢 ​

LIMIT 1000000, 20 的执行过程是：按索引顺序取出前 1000020 行（含回表），丢掉前 100 万行，返回 20 行。慢在"取了再扔"，不是慢在返回。

两种解法，原理不同：

延迟关联。先用覆盖索引把目标 20 行的主键找出来，再回表取整行：

sql
-- 优化前：回表 100 万次
SELECT * FROM orders ORDER BY id LIMIT 1000000, 20;

-- 优化后：只在索引上滑过 100 万行，回表只有 20 次
SELECT o.* FROM orders o
JOIN (SELECT id FROM orders ORDER BY id LIMIT 1000000, 20) t
ON o.id = t.id;

子查询只扫主键索引（二级索引都免了），把最贵的回表从 100 万次压到 20 次。代价是仍然要遍历 100 万个索引项，offset 越深越慢，只是常数因子小了很多。

游标法（seek method）。如果翻页场景允许"记住上一页最后一条的排序值"，就不用 offset：

sql
-- 上一页最后一条 id = 1003520，直接从它之后取
SELECT * FROM orders WHERE id > 1003520 ORDER BY id LIMIT 20;

这条走的是 range 定位 + 顺序读 20 行，offset 再深也是常数耗时。代价是交互模式受限：不能跳页，只能连续向后翻（或按时间线无限下拉），且排序键要唯一（不唯一就拼上主键）。

选型：管理后台要跳页，用延迟关联；C 端信息流天然连续翻页，用游标法，一步到位。

动手实操：一条慢 SQL 的完整优化过程 ​

背景：orders 表 3000 万行，有一条投诉"偶尔要 8 秒"的 SQL。

sql
-- 原始 SQL：按买家查最近订单，按时间倒序取前 20
SELECT * FROM orders
WHERE buyer_id = 88123 AND status IN (2, 3)
ORDER BY create_time DESC
LIMIT 20;

第一步，EXPLAIN 看现状：

sql
EXPLAIN SELECT * FROM orders
WHERE buyer_id = 88123 AND status IN (2, 3)
ORDER BY create_time DESC LIMIT 20;
text
type: ALL
key: NULL
rows: 29174231
Extra: Using where; Using filesort

全表扫描 2900 万行，还要额外排序。买家平均只有几百单，却扫了全表——典型的"等值条件没有索引"。

第二步，设计索引。条件是 buyer_id 等值 + status 离散集合 + create_time 排序。按"等值列在前、范围/排序列在后"的原则：

sql
ALTER TABLE orders ADD INDEX idx_buyer_status_ct(buyer_id, status, create_time);

第三步，对比验证：

sql
EXPLAIN SELECT * FROM orders
WHERE buyer_id = 88123 AND status IN (2, 3)
ORDER BY create_time DESC LIMIT 20;
text
type: range
key: idx_buyer_status_ct
key_len: 12            -- buyer_id(BIGINT 8) + status(INT 4)，create_time 参与排序下推
rows: 640
Extra: Using index condition; Backward index scan
sql
-- 实测：8.2s -> 0.003s
SELECT * FROM orders WHERE buyer_id = 88123 AND status IN (2, 3)
  ORDER BY create_time DESC LIMIT 20;

rows 从 2900 万降到 640，Using filesort 消失（排序直接用索引的逆序扫描，Backward index scan），实测从 8.2 秒降到 3 毫秒。注意这里 status 放在 create_time 前面有个前提：IN 列表的多个等值区间在索引上各自内部仍按 create_time 有序，所以 ORDER BY 能吃上索引；如果把范围条件（如 create_time > x）放在排序列前面，排序就吃不上了，这是设计联合索引时最容易踩的顺序坑。

索引设计 checklist ​

最后把方法论收进一张单子，建索引前过一遍：

  • 区分度优先：COUNT(DISTINCT col) / COUNT(*) 接近 1 的列才配单独做索引前导；性别、状态这类两三个取值的列，单独建索引几乎没用，只配进联合索引
  • 等值在前，范围在后：联合索引把等值条件列放左边，范围和排序列放右边
  • 长字符串用前缀索引：INDEX(col(20)) 能省空间，但代价是失去覆盖索引能力（前缀截断后无法直接从索引判断结果，还是要回表），且不能用于 ORDER BY
  • 控制单表索引数量：一般不超过 5~6 个。每个索引都是一棵要维护的 B+ 树，写入放大、优化器选择成本都会涨
  • 删掉冗余索引：有了 (a,b) 就不必再留 (a)；建新索引前先查 SHOW INDEX，别让索引悄悄堆成山
  • 改写 SQL 优先于堆索引：函数改区间、隐式转换改字面量类型、OR 改 UNION，这些是零成本优化，先做

常见误区与小结 ​

  • "EXPLAIN rows 很大就一定慢"——rows 是估算，方向对了就行，最终以实测为准
  • "type=ALL 就必须加索引"——结果集本来就占大半张表的查询（如报表扫描），全表扫反而比走索引+百万次回表快，加索引是负优化
  • "索引失效八大场景"当口诀背——不如记住共性：条件必须能转化为索引列原始值的连续区间，函数、隐式转换、前导模糊、最左缺失全是这一个原理的不同马甲
  • "深分页加索引就好了"——LIMIT 1000000,20 的问题不是没索引，是取了再扔；延迟关联只降常数，游标法才降量级
  • "ORDER BY 随便写，反正有 filesort 兜底"——filesort 在内存不够时会落盘，大表深排序是隐形杀手，排序键要尽量设计进联合索引

小结：慢 SQL 优化是一条固定的流水线——慢日志 + pt-query-digest 定位，EXPLAIN 的 type/key_len/Extra 三层定位病因，按"区间可界定"原理改写或建索引，最后实测对比。这套流程加上前一篇的 B+ 树原理，MySQL 查询优化的主线就串完整了。下一篇讲 InnoDB 页、区与文件物理布局，从逻辑结构沉到物理存储。

参考 ​

参考：MySQL 8.0 Reference Manual — EXPLAIN Output Format（https://dev.mysql.com/doc/refman/8.0/en/explain-output.html）；《高性能 MySQL》第 5、6 章；Percona Toolkit pt-query-digest 文档（https://docs.percona.com/percona-toolkit/pt-query-digest.html）

手撕 → 框架 → 生产化，一步步把 AI Agent 工程化搞透。
粤ICP备2026104257号-1

Copyright © 2026 祥哥