SQL 调优与 EXPLAIN

一句话：调优不是「加索引」，而是让优化器用更少的页和更少的行完成同样的结果；EXPLAIN 看它打算怎么做，EXPLAIN ANALYZE 看它实际做了什么。

适用版本：MySQL 8.0。EXPLAIN ANALYZE 从 8.0.18 起可用；EXPLAIN FORMAT=TREE 从 8.0.16 起可用。8.4 的输出格式以官方文档为准。

为什么需要

一条 SQL 变慢，可能是没走索引、走了错误的索引、回表太多、排序落盘、锁等待、或者返回的数据量本身就大。只看「加个索引」会漏掉后面几类，也会加上不必要甚至有害的索引。

调优要先定位瓶颈在哪一层：扫描行数、回表次数、排序/临时表、锁等待、网络传输。EXPLAIN 给的是优化器的估算，EXPLAIN ANALYZE 给的是实际执行统计，两者要对照看。

核心机制

索引的存储结构（B+ 树、聚簇/二级索引、回表、最左前缀）见 05-索引与B+树；本篇只讲优化器如何选计划，以及慢查询如何定位和改写。

优化器怎么选

优化器按成本模型比较候选计划，成本主要由「需要读多少页」估算。它依赖统计信息（索引基数、数据分布），统计信息过期就会选错。ANALYZE TABLE 会重新采样；FORCE INDEX 只是让它在候选里偏向某个索引，不是让 SQL 变快。

EXPLAIN 的关键列

列	看什么	判断
type	访问方式	const > eq_ref > ref > range > index > ALL；ALL 是全表扫描
key	实际使用的索引	NULL = 没用索引
key_len	用了索引的多少字节	联合索引只用了前几列时会偏短
rows	预估扫描行数	与实际返回行数差距大，说明统计信息或条件选择性有问题
filtered	预估过滤后剩余比例	低 filtered + 大 rows 往往意味着大量无效扫描
Extra	额外动作	Using index 覆盖索引；Using index condition 索引下推；Using filesort 额外排序；Using temporary 临时表

type=index 不是「走了索引所以没问题」：它是全索引扫描，扫描量可能和全表差不多，只是不用回表。

EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT id, amount
FROM t_order
WHERE user_id = 42 AND status = 1
ORDER BY created_at DESC
LIMIT 20;

输出是树形，每个节点带 actual time=... rows=... loops=...。重点看：

• actual rows 与 rows 估算差多少；差一个数量级，先更新统计信息。
• 哪个节点耗时最多；耗时在扫描、回表还是排序。
• loops 很大时，单次时间要乘以循环次数。

索引失效的真实条件

根因只有一个：优化器无法利用索引的有序性，或判断用索引的成本更高。

写法	为什么可能不走索引
WHERE DATE(created_at) = '2026-01-01'	对索引列做函数运算，索引里存的是原值
WHERE phone = 13800138000（phone 是 VARCHAR）	隐式类型转换，等价于对列做函数
WHERE name LIKE '%abc'	前导通配符无法定位起点
WHERE a = 1 OR b = 2（a、b 分属不同索引）	可能要合并多个索引或全表扫描，优化器按成本选
WHERE status != 1	低区分度 + 不等于，可能选择全表
两表字符集不同	连接时在被驱动表列上做转换

注意两个常见误判：

• WHERE id = '1000'（id 是 INT）通常不失效：字符串被转成数字，列本身没变。
• 函数导致「不能用于快速定位」不等于「完全不用索引」：可能退化成全索引扫描（type=index），key 仍有值。

一次慢查询的完整改写

-- 原始：深分页，扫描 100020 行再丢弃前 100000 行
SELECT * FROM t_order ORDER BY id LIMIT 100000, 20;

-- 改写 1：延迟关联，先取主键再回表
SELECT o.*
FROM t_order o
JOIN (SELECT id FROM t_order ORDER BY id LIMIT 100000, 20) AS t
  ON o.id = t.id;

-- 改写 2：游标分页，适合有自增主键且能记住上一页的位置
SELECT * FROM t_order WHERE id > 100000 ORDER BY id LIMIT 20;

实战场景

一、统计信息过期，优化器换了个更差的索引

现象：一条跑了两年没问题的查询在某个凌晨突然从 60ms 变成 9s。表结构、SQL、参数都没变，唯一变化是前一天做了一次大批量历史数据回填（新增 800 万行）。EXPLAIN 显示 key 从 idx_user_status 变成 idx_created_at，rows 估算 12 而 EXPLAIN ANALYZE 的 actual rows 是 21 万。

排查：优化器按成本模型比较候选计划，成本依赖统计信息。

定位层	取证据	判定
估算与实际差距	EXPLAIN 的 rows vs EXPLAIN ANALYZE 的 actual rows	差一个数量级 → 先怀疑统计信息
选错索引	possible_keys 与 key 不一致；对比历史执行计划	命中即换计划导致劣化
统计新鲜度	information_schema.INNODB_TABLESTATS 的 last_update、STATS_PERSISTENT	大批量写入后未及时采样
排序与临时表	Extra 出现 Using filesort/Using temporary	索引变了，排序也丢了
loops 放大	EXPLAIN ANALYZE 的 loops	单次时间要乘以循环次数，容易低估
参数嗅探	同一语句不同参数的实际选择性	某个值让索引不再划算

ANALYZE TABLE t_order;                      -- 重新采样索引基数，秒级、只短暂加元数据锁
SHOW STATUS LIKE 'Handler_read%';           -- 辅助判断扫描形态

对症做法：

1. 大批量回填之后立即 ANALYZE TABLE，把统计刷新写进变更流程的最后一步；
   innodb_stats_persistent=ON 让统计跨重启保留，
   innodb_stats_auto_recalc=ON 会在变化超过 10% 时自动重算（默认值以版本为准）。
2. FORCE INDEX 只能作为止痛：它让优化器偏向某个索引，不让 SQL 本身变快，
   数据分布再变一次就失效；用之前先确认 SQL 形态是否可优化。
3. 真正稳的做法是把“排序 + 定位”需要的列放进同一个联合索引：
   (user_id, status, created_at) 能同时命中等值前缀与 ORDER BY created_at。
4. 给核心 SQL 建立“执行计划基线”：把 EXPLAIN 输出留档，
   变更前后做 diff，计划漂移立刻可见。

-- 让 ORDER BY 走索引顺序，避免 filesort
EXPLAIN ANALYZE
SELECT id, amount FROM t_order
 WHERE user_id = 42 AND status = 1
 ORDER BY created_at DESC LIMIT 20;
-- 期望：key=idx_user_status_created，Extra 中没有 Using filesort

复盘要点：判据是“关键 SQL 的 actual rows / rows 比值接近 1”，比值偏离说明统计或选择性有问题；把 Top 20 SQL 的执行计划 diff 纳入每日巡检，比等接口超时再排查提前得多。

二、LIMIT 100000, 20：翻页越深，DB 越忙

现象：运营后台的订单列表“第一页 30ms，第 200 页 4.6s”，DB CPU 在高峰被这一条 SQL 占掉三成。审计日志显示 rows_examined 稳定是 100020——扫描后丢弃前 100000 行。

排查：深分页的成本与偏移量成正比，而不是与页大小成正比。

定位层	取证据	判定
扫描量	EXPLAIN ANALYZE 的 rows 与 Handler_read_next	与 offset 同量级即确认丢弃式分页
排序代价	Extra 是否 Using filesort；Sort_rows 状态计数	排序在无索引支持时落临时表
回表次数	SELECT * 让每行都回聚簇索引	覆盖索引 + 延迟关联能砍掉大部分
并发来源	是否有爬虫/脚本按页遍历全量	深页流量常来自批量导出，不该走在线接口
产品形态	用户是否真的需要跳到第 N 页	决定用游标分页还是导出异步化

对症做法：

-- 改写 1：延迟关联，先在索引上取主键，再回表 20 行
SELECT o.*
  FROM t_order o
  JOIN (SELECT id FROM t_order ORDER BY id LIMIT 100000, 20) AS t ON o.id = t.id;

-- 改写 2：游标分页（推荐），成本与页大小相关、与深度无关
SELECT * FROM t_order WHERE id > 100000 ORDER BY id LIMIT 20;
-- 排序键非唯一时，用 (created_at, id) 组合游标，保证翻页确定：
-- WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 20

-- 改写 3：全量遍历走异步导出，不在在线接口里翻页
--        按主键区间切片扫描，每次 5000 行，结果写对象存储供下载

1. 游标分页要求“上一页的位置信息”能带回来：前端返回 lastId/lastSortValue，
   下一页请求带上它，而不是带 pageNo。
2. UI 层面收掉“跳转到最后一页”“页码直达”这类需求：
   它们天然要求 count(*) + 深 offset，是成本来源而不是功能。
3. count(*) 单独治理：大表用近似值（按分片统计缓存）或限制条件必须带索引前缀。

复盘要点：判据是“rows_examined / rows_sent 比值”——在线查询应接近 1，超过 50 就属于丢弃式扫描（阈值经验值，需校准）。这条比值可以直接从慢日志与 performance_schema.events_statements_summary_by_digest 里按 SQL 指纹统计，是发现深分页最省事的口径。

三、phone = 13800138000 把索引变成全表扫描

现象：一个查用户的接口偶发 2s，SHOW PROCESSLIST 里能看到它在 Sending data。表上 idx_phone 明明存在。最终原因是 Java 侧把手机号当数字传，SQL 变成 WHERE phone = 13800138000，而 phone 是 VARCHAR(20)。

排查：根因只有一个——优化器无法利用索引的有序性，或判断用索引成本更高。

写法	是否失效	判定依据
WHERE phone = 13800138000（列是 VARCHAR）	失效	隐式类型转换等价于对列做函数；EXPLAIN key=NULL、type=ALL
WHERE id = '1000'（列是 INT）	通常不失效	字符串被转成数字，列本身没变
WHERE DATE(created_at)='2026-01-01'	失效	函数包裹索引列
WHERE name LIKE '%abc'	失效	前导通配符无法定位起点
两表字符集/排序规则不同做 JOIN	被驱动表失效	连接时在被驱动列上做转换
status != 1 且区分度低	可能不走	优化器按成本选全表

函数导致“不能用于快速定位”不等于“完全不用索引”：可能退化成全索引扫描（type=index），此时 key 仍有值，容易误判成“走了索引没问题”。

对症做法：

-- 1. 列与值类型一致：字符串列传字符串
SELECT id FROM t_user WHERE phone = '13800138000';

-- 2. 时间等值改成区间，保留索引有序性
SELECT id FROM t_order
 WHERE created_at >= '2026-01-01 00:00:00' AND created_at < '2026-01-02 00:00:00';

-- 3. 跨系统 JOIN 先统一字符集与排序规则，避免隐式转换
ALTER TABLE t_user CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

4. 接入层防呆：ORM 参数绑定按列类型声明（String 就传 String），
   禁止用 Long 承载手机号/身份证号这类“数字形态的字符串”。
5. 上线前把这类写法挡在 SQL 审核：
   EXPLAIN 结果里出现 type=ALL 或 key=NULL 且表行数超过阈值（例如 10 万）即拒绝发布。
6. 需要模糊搜索时交给搜索引擎或全文索引，不要指望 B+ 树支持 %abc。

复盘要点：判据是“生产慢 SQL 中因类型转换/函数包裹导致的索引失效占比为 0”，靠 SQL 审核平台的 EXPLAIN 门禁保证。另外提醒一个反向误判：type=index 不是“走了索引所以没问题”，它是全索引扫描，扫描量可能和全表差不多，只是不用回表——看到 key 有值就下结论会漏掉真问题。

最小上手

开启慢查询并分析（MySQL 8.0）：

SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;          -- 默认 10 秒
SET GLOBAL log_queries_not_using_indexes = ON;  -- 仅在排查期短开，日志会很多
SHOW VARIABLES LIKE 'slow_query_log_file';

命令行：

mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log   # 按总耗时 Top 10
# 或
pt-query-digest /var/lib/mysql/slow.log

总耗时 = 单次耗时 × 调用次数，优先处理总耗时最高的，而不是单次最慢的。

生产动作

排障路径固定为四步：

1. 定位：慢日志 / performance_schema.events_statements_summary_by_digest 找 SQL。
2. 看计划：EXPLAIN 看 type/key/rows/Extra，必要时 EXPLAIN ANALYZE。
3. 改：补索引、改联合索引顺序、去掉列上的函数、改参数类型、用覆盖索引、改分页方式。
4. 验证：上线前用真实数据量跑 EXPLAIN ANALYZE，对比扫描行数和耗时；不要只看测试库的小表。

常见处置：

• type=ALL 且 rows 大 → 补索引或缩小 WHERE 范围。
• Using filesort → 让 ORDER BY 的列顺序和索引一致，或减少排序数据量。
• Using temporary → 简化 GROUP BY、补复合索引，或把聚合移到汇总表。
• 回表太多 → 覆盖索引；但索引列太多会增大写放大，要权衡。
• 统计信息过期 → ANALYZE TABLE t;，再复测。
• 锁等待导致的「慢」→ 查 sys.innodb_lock_waits，不要误判为索引问题。

边界

• EXPLAIN 是估算：rows 不是实际值；小表上估算偏差尤其大，必须用真实数据验证。
• FORCE INDEX 是最后手段：它把优化器的判断权拿走，数据分布变化后可能更慢。
• 不是所有慢查询都能靠索引解决：返回百万行、复杂聚合、跨库 JOIN，应该改架构（汇总表、ES、数仓）。
• 索引有写代价：每个索引都会拖慢写入并占空间，调优要算总账。
• 不要在生产常开 log_queries_not_using_indexes：日志量和 IO 开销都很大。

讲清楚

SQL 调优的目标是让优化器用更少的页完成同样的结果，不是机械地加索引。先用慢日志按总耗时排序，找到值得改的 SQL；再用 EXPLAIN 看访问方式、实际索引、预估行数和额外动作，用 EXPLAIN ANALYZE 看实际行数和耗时。索引失效的根因是破坏了索引的有序性：列上做函数、隐式类型转换、前导通配符；但「失效」不等于完全不用索引，可能只是退化成全索引扫描。改完之后要用真实数据量复测，并考虑索引带来的写放大；如果瓶颈是锁等待、返回数据量或聚合，加索引解决不了。