SQL 调优实战：优化器为什么放着好索引不用

表上明明有合适的索引，执行计划却选了主键扫描，一个定时任务从几毫秒变成十几秒。这类问题靠「加索引」解决不了，要先弄清优化器做选择时依据的假设，以及这个假设在什么数据分布下会失效。

慢查询里最容易处理的是「没有索引」，最难处理的是「有索引但没用上」。本文先复现一个真实线上案例的简化版：一张 300 万行的任务事件表，查询条件都在联合索引里，优化器却选择沿主键扫描了 285 万行。然后从这个案例出发，整理索引失效的几类原因、联合索引的列顺序、深分页的改写和索引的写入代价，以及一套可复用的调优流程。

执行计划各列的含义，见读懂 Explain。

一、先说结论

优化器按「数据均匀分布」估算成本。 ORDER BY id LIMIT n 会让它倾向沿主键顺序扫描，赌前面很快就能凑够 n 行；数据集中在表尾时，这个赌注就输了。
修复方式的效果差距很大。 实测中，强制指定索引把耗时从 569ms 降到约 27ms，按 IN 的取值拆成 UNION ALL 则降到 0.45ms。
FORCE INDEX 是止血手段，不是根治方案。 数据分布变化后，被强制的索引可能反过来变慢；更好的做法是改写查询或调整索引，让优化器自己选对。
联合索引的列顺序比列的选择更重要：等值条件列在前，范围和排序列在后。顺序反了，key_len 看起来一样，实际读取的行数却可能差几倍。
「索引失效」的说法要具体。 函数、隐式类型转换、前导通配符会让索引无法用于定位；低区分度的索引不是失效，而是用了也没有收益。
深分页用游标，不用 offset。 50 万行的表取第 40 万行之后的 20 行，LIMIT offset 读取 40 万行、约 38ms，游标只读 20 行；多列游标要写成展开的形式，行构造器写法 MySQL 不会走范围扫描。
每个索引都有写入代价。 同样写入 20 万行，5 个二级索引比没有二级索引慢一倍多，索引本身的体积是数据的 3 倍多。

二、案例：用了主键反而慢了 3000 多倍

2.1 复现

sql

CREATE TABLE task_event (
  id         BIGINT PRIMARY KEY AUTO_INCREMENT,
  state      TINYINT      NOT NULL,          -- 0 待处理，2 已处理
  event_type VARCHAR(16)  NOT NULL,
  deleted    TINYINT      NOT NULL DEFAULT 0,
  payload    VARCHAR(200) NOT NULL,
  gmt_create DATETIME     NOT NULL,
  KEY idx_state_event_deleted (state, event_type, deleted)
);
-- 300 万行；早期数据都已处理，5 万条待处理数据集中在最近写入的 5% 里（造数脚本见文末配套实验）

定时任务每次取 100 条待处理事件：

sql

SELECT * FROM task_event
WHERE state = 0 AND event_type IN ('PAY', 'REFUND') AND deleted = 0
ORDER BY id LIMIT 100;

MySQL 8.4.11 上的 EXPLAIN ANALYZE（容器限制 2 CPU、2 GB，热缓存，7 次采样的中位数为 569ms）：

text

-> Limit: 100 row(s)  (cost=35 rows=100) (actual time=565..566 rows=100 loops=1)
    -> Filter: ((task_event.deleted = 0) and (task_event.state = 0) and (task_event.event_type in ('PAY','REFUND')))  (cost=35 rows=100) (actual time=565..566 rows=100 loops=1)
        -> Index scan on task_event using PRIMARY  (cost=35 rows=6893) (actual time=0.049..446 rows=2.85e+6 loops=1)

优化器预计沿主键读 6,893 行，实际读了 285 万行。

2.2 优化器为什么这么选

图 1 · 优化器按均匀分布估算，以为沿主键读几千行就能凑够 100 条；实际待处理数据集中在表尾，主键扫描读了 285 万行

优化器面前有两条路：

方案	做法	优化器的估算
A. 走 `idx_state_event_deleted`	取出全部匹配行（估算约 4.2 万，实际 24,541），再按 `id` 排序取前 100	必须读完所有匹配行，还要排序
B. 沿主键顺序扫描	按 `id` 从小到大读，边读边过滤，凑够 100 条就停	匹配行占比约 1.45%，平均每 69 行出现一条，读 6,893 行即可

方案 B 的估算建立在「匹配行均匀分布在整张表里」这个假设上。现实中，待处理数据几乎总是集中在最近写入的部分，也就是主键的尾部。

同一条查询换成已处理数据（state = 2，分布在表头）时，主键扫描只读了 204 行、耗时 0.077ms，这正是优化器期望的情况：

text

-> Index scan on task_event using PRIMARY  (cost=5.06 rows=146) (actual time=0.011..0.0455 rows=204 loops=1)

所以这不是优化器的 bug，而是它的成本模型不了解数据的业务含义。

为什么等值条件就不会出问题？把 IN 换成单个值 event_type = 'PAY'，优化器直接选择了二级索引，耗时 0.16ms。原因是 InnoDB 的二级索引叶子节点按「索引列 + 主键」排序，三列都是等值时，匹配的记录天然按 id 有序，ORDER BY id 不需要额外排序。IN 有两个取值，两段结果合起来就不再按 id 有序了。

2.3 五种修复方式实测

方式	写法	读取行数	耗时（中位数）
原始查询	—	2,850,620	569ms
强制索引	`FORCE INDEX (idx_state_event_deleted)`	24,541 + 排序	27.5ms
关闭排序索引偏好	`/+ SET_VAR(optimizer_switch = 'prefer_ordering_index=off') /`	24,541 + 排序	27.5ms
让排序无法利用主键	`ORDER BY id + 0`	24,541 + 排序	27.7ms
按取值拆成 UNION ALL	见下方	约 200	0.45ms

读取行数来自 Handler_read% 计数，耗时是 7 次 EXPLAIN ANALYZE 的中位数；五种写法返回的 100 个 id 完全相同。还有一个容易忽略的细节：三种「走索引再排序」的写法读取行数只有原查询的 0.9%，缓冲池页请求却是原查询的 2 倍（98,232 对 49,171），因为每条匹配都要回聚簇索引取整行，而主键扫描在一个页里连续读多行。它们仍然快 20 倍，说明热缓存下扫描和过滤 285 万行才是主要成本。

sql

(SELECT * FROM task_event WHERE state = 0 AND event_type = 'PAY'    AND deleted = 0 ORDER BY id LIMIT 100)
UNION ALL
(SELECT * FROM task_event WHERE state = 0 AND event_type = 'REFUND' AND deleted = 0 ORDER BY id LIMIT 100)
ORDER BY id LIMIT 100;

每个子查询都是等值条件，可以直接按索引顺序取前 100 条；两段合并后只需要对 200 行排序。

怎么选：

UNION ALL 拆分：IN 的取值少且固定时效果最好，代价是 SQL 变长。
ORDER BY id + 0：改动最小，不依赖索引名，但对后来维护的人不直观，要写注释。
prefer_ordering_index（MySQL 8.0.21 起）：可以用优化器提示 /*+ SET_VAR(optimizer_switch='prefer_ordering_index=off') */ 只作用于这一条语句，不要在全局关闭。
FORCE INDEX：索引被改名或删除时语句会直接报错，数据分布变化后也可能变慢，适合作为应急手段。

2.4 换个思路：别用 LIMIT 扫描任务表

这类「定时捞待处理数据」的查询，还可以从设计上避开问题：

用上一批最大的 id 作为游标（WHERE id > ? ... ORDER BY id LIMIT 100），每次从上次结束的位置开始；
已处理的数据定期归档出主表，让表里只剩待处理和近期数据，见千万级大表怎么清理数据。

三、联合索引的列顺序

图 2 · 同一条查询，(status, created_at) 在引擎里只检查 9,223 条索引记录；(created_at, status) 先按时间范围检查 36,899 条再过滤，两者 key_len 都是 71，EXPLAIN ANALYZE 的行数也相同

一个 B+ 树索引只能按列顺序依次缩小扫描范围。遇到范围条件（>、<、BETWEEN、LIKE 'abc%'）之后，后面的列只能在已经圈定的范围内逐条过滤，不能再缩小范围。

上图的实测有一个容易误判的细节：两种列顺序下，EXPLAIN 显示的 key_len 都是 71（VARCHAR(16) 的 66 加 DATETIME 的 5），看起来都「用满了索引」；EXPLAIN ANALYZE 的实际行数也都是 9,222，Handler_read_next 也一样。

原因是索引条件下推（ICP）：(created_at, status) 先按时间范围定位，status 的过滤在存储引擎里完成，只有满足条件的行才交给 Server 层，所以 Server 层看到的行数相同。真正的差别在引擎内部，要用 InnoDB 的 ICP 计数器才能看到：

sql

SET GLOBAL innodb_monitor_enable = 'module_icp';
SET GLOBAL innodb_monitor_reset = 'module_icp';
-- 执行查询后
SELECT name, count_reset FROM information_schema.innodb_metrics WHERE subsystem = 'icp';

索引	引擎检查的索引记录（`icp_attempts`）	被过滤掉的（`icp_no_match`）	中位耗时
`(status, created_at)`	9,223	0	7.1ms
`(created_at, status)`	36,899	27,677	9.0ms

在 10 万行、全部缓存在内存的表上，四倍的索引记录只多了约 2ms；表更大、数据不在内存里时，差距会随扫描的页数放大。

设计联合索引时，按这个顺序排列列：

等值条件的列（=、IN 取值很少时也可以视为等值），区分度高的放前面；
排序列（ORDER BY），和等值列配合时可以免去排序；
范围条件的列，放在最后；
如果需要覆盖索引，把只在 SELECT 中出现的列追加到末尾。

四、索引用不上的几类原因

以下结果都在 10 万行的 orders 表上实测（索引 idx_customer_created (customer_id, created_at)、idx_phone (phone)、idx_status (status)）。

4.1 无法用于定位

写法	实测 `type`	原因	改写
`WHERE customer_id + 1 = 43`	`ALL`	索引存的是原值，不是计算结果	`WHERE customer_id = 42`
`WHERE DATE(created_at) = '2026-06-01'`	`ALL`	同上	改成左闭右开的范围条件
`WHERE phone = 13800000042`（`phone` 是字符串）	`ALL`	字符串与数字比较时逐行转换成数字	参数用字符串类型
`WHERE phone LIKE '%0000042'`	`ALL`	前导通配符无法利用索引的有序性	反转字符串建索引，或交给全文检索
`WHERE phone LIKE '1380000004%'`	`range`	前缀匹配可以用索引	—

前导通配符有一个例外：只查询索引里有的列时，SELECT id, phone ... LIKE '%0000042' 的 type 是 index，会扫描整棵 idx_phone 索引而不是整张表。索引比表小，但依然是全量扫描。

4.2 OR 条件：两边都有索引才行

sql

EXPLAIN SELECT * FROM orders WHERE customer_id = 42 OR phone = '13800000042';
-- type=index_merge  Extra=Using sort_union(idx_customer_created,idx_phone)

EXPLAIN SELECT * FROM orders WHERE customer_id = 42 OR amount = 10.99;
-- type=ALL  （amount 没有索引）

OR 两边都有可用索引时，MySQL 会做索引合并（index merge），分别扫描两个索引再去重。任意一边没有索引，就只能全表扫描。这时可以给缺失的一边补索引，或者改写成 UNION。

4.3 不等于：通常不值得走索引

WHERE customer_id != 42 实测是全表扫描，优化器估计会返回约一半的行。!= 本身可以转换成两段范围，但匹配行太多时，读索引再回表不如直接扫表。

4.4 低区分度：用了也没收益

status 只有 4 种取值，每种占 25%：

text

走 idx_status：   Index lookup on orders using idx_status (status='PAID')   actual time=0.472..15.6 rows=25000
忽略索引全表扫描：Table scan on orders → Filter                             actual time=0.097..19.8 rows=25000

7 次采样的中位数分别是 15.1ms 和 19.8ms，处在同一个量级。低区分度的列单独建索引通常没有意义，但作为联合索引的前导列、配合其他列使用时，依然可能很有价值，比如第三节的 (status, created_at)。

五、深分页：让下一页从上一页结束的位置开始

LIMIT offset, n 的语义是「跳过 offset 行，再取 n 行」，跳过的行也要一行一行读出来再丢掉。50 万行的订单表，按主键在不同深度取 20 行：

位置	`ORDER BY id LIMIT offset, 20`	`WHERE id > ? ORDER BY id LIMIT 20`
第 0 行之后	读取 21 行，0.18ms	读取 20 行，0.14ms
第 1 万行之后	读取 10,021 行，1.12ms	读取 20 行，0.20ms
第 10 万行之后	读取 100,021 行，9.64ms	读取 20 行，0.17ms
第 40 万行之后	读取 400,021 行，38.16ms	读取 20 行，0.17ms

游标分页（keyset pagination）记住上一页最后一行的排序值，下一页直接从那里开始定位，读取的行数与翻到第几页无关。两种写法在每个深度返回的结果完全相同。代价是只能「下一页」「上一页」，不能直接跳到第 N 页；需要跳页的场景（例如后台管理），通常可以限制最大页数，或者改为按时间、按条件筛选。

5.1 多列游标要写成展开的形式

按时间排序时，时间可能重复，游标要用 (created_at, id) 两列，并建立对应的联合索引。直觉的写法是行构造器比较，实测它不会被用作索引范围：

sql

-- 行构造器：从索引末尾反向扫描再逐行过滤，越往后翻读得越多
WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 20

-- 展开写法：索引范围扫描，每页读取的行数恒定
WHERE created_at < ? OR (created_at = ? AND id < ?) ORDER BY created_at DESC, id DESC LIMIT 20

倒序翻 50 页，两种写法都取到 1000 行且没有重复；行构造器写法总共读取 25,550 行，第 50 页读取 1,001 行，展开写法总共读取 1,050 行，第 50 页读取 21 行。MySQL 手册的「Row Constructor Expression Optimization」一节给出了同样的改写建议。上线前用 EXPLAIN ANALYZE 确认执行计划里出现的是 index range scan，而不是 index scan 加 Filter。

六、索引不是免费的

每个二级索引都是一棵独立的 B+ 树，写入一行就要在每棵树里各插入一条记录，还要写对应的 Redo 和 Binlog。同样写入 20 万行（每 2000 行一批），3 轮中位数：

二级索引数量	写入耗时	二级索引大小（数据 11.5 MB）
0	1,172ms	0
2	1,560ms	16.0 MB
5	2,638ms	39.6 MB

新增索引之前要算两笔账：它让哪些查询变快、快多少；它让每次写入慢多少、占多少内存和磁盘。重复的索引（例如同时有 (a) 和 (a, b)）、多年没有被查询用到的索引，都应该定期清理，sys.schema_unused_indexes 视图可以帮助找到后者。批量导入时先写数据、后建索引的做法，见百万行数据导入。

七、一套可复用的调优流程

定位：从慢查询日志（long_query_time）或 APM 中拿到真实 SQL 和参数，确认它是真正的瓶颈，而不是被锁等待或连接池排队拖慢。
看计划：EXPLAIN 看优化器的选择，重点是 type、key、rows、Extra。
看事实：在隔离的测试环境或 replica 上 EXPLAIN ANALYZE，找实际读取行数最多的一层，对比估算与实际。
找原因：
- 估算与实际偏差大：统计信息或数据分布问题，考虑 ANALYZE TABLE、直方图，或改写查询绕开优化器的假设；
- 读得多、留得少：索引不匹配条件，调整联合索引列顺序；
- 回表次数多：考虑覆盖索引；
- 有额外排序或临时表：让排序列进入索引。
验证：用同样的参数、同样的数据量再执行一次，对比读取行数和耗时；上线后观察慢查询日志。
防回退：把关键查询的执行计划纳入发布前检查，数据量增长到新的量级时重新验证。

八、常见误区

「有索引就会用」：优化器按成本选择，数据分布不符合它的假设时，会放弃更好的索引。
「FORCE INDEX 一劳永逸」：数据分布和索引结构都会变化，强制索引是把今天的判断写死在代码里。
「key_len 相同就说明索引用得一样好」：实测两种列顺序 key_len 相同、EXPLAIN ANALYZE 的行数也相同，引擎检查的索引记录却相差 4 倍。
「区分度低的列不能建索引」：单独建通常没用，放在联合索引前导位置可能很有用。
「调数据库参数是调优的重点」：大多数慢查询的原因在 SQL 和索引，参数调整通常是最后一步。
「分页加个索引就不慢了」：LIMIT offset 仍然要读出并丢掉前面所有行，深分页要改成游标。
「索引多多益善」：每个索引都会让写入变慢、占用内存，实测 5 个二级索引让批量写入慢了一倍多。

小结

优化器不了解数据的业务含义，它只按统计信息和均匀分布的假设估算成本。ORDER BY id LIMIT 叠加数据倾斜，是最常见的「放着好索引不用」的场景。遇到这类问题，先用 EXPLAIN ANALYZE 确认实际读取行数，再优先通过改写查询和调整索引让优化器自然选对，把 FORCE INDEX 留作应急。

表的数据量增长到什么程度需要拆分，见单表多大该拆分。

配套实验

codesphere-labs/storage/order-by-limit-index-choice：300 万行确定性造数、五种写法与两条对照查询的完整执行计划、Handler 计数与 7 次采样（验证记录）
codesphere-labs/storage/mysql-explain-plans：第三、四节的联合索引列顺序（含 ICP 计数器）与索引用不上的几类原因（验证记录）
codesphere-labs/storage/pagination-and-index-cost：第五、六节的深分页与游标分页、两种多列游标写法、二级索引数量对批量写入的影响（验证记录）

每个实验都可以用 make verify 一条命令复现，链接固定在对应的实验仓库版本。

参考资料

SQL 调优实战：优化器为什么放着好索引不用 ​

一、先说结论 ​

二、案例：用了主键反而慢了 3000 多倍 ​

2.1 复现 ​

2.2 优化器为什么这么选 ​

2.3 五种修复方式实测 ​

2.4 换个思路：别用 LIMIT 扫描任务表 ​

三、联合索引的列顺序 ​

四、索引用不上的几类原因 ​

4.1 无法用于定位 ​

4.2 OR 条件：两边都有索引才行 ​

4.3 不等于：通常不值得走索引 ​

4.4 低区分度：用了也没收益 ​

五、深分页：让下一页从上一页结束的位置开始 ​

5.1 多列游标要写成展开的形式 ​

六、索引不是免费的 ​

七、一套可复用的调优流程 ​

八、常见误区 ​

小结 ​

SQL 调优实战：优化器为什么放着好索引不用

一、先说结论

二、案例：用了主键反而慢了 3000 多倍

2.1 复现

2.2 优化器为什么这么选

2.3 五种修复方式实测

2.4 换个思路：别用 LIMIT 扫描任务表

三、联合索引的列顺序

四、索引用不上的几类原因

4.1 无法用于定位

4.2 OR 条件：两边都有索引才行

4.3 不等于：通常不值得走索引

4.4 低区分度：用了也没收益

五、深分页：让下一页从上一页结束的位置开始

5.1 多列游标要写成展开的形式

六、索引不是免费的

七、一套可复用的调优流程

八、常见误区

小结