ORDER BY 配合 LIMIT 触发的索引选择陷阱原创

版本说明

本文基于 MySQL 8.0 InnoDB。文中执行计划为按该场景构造的结构示意，用于说明 type / key / Extra 三个字段的判读方法，非某次具体压测的原始抓取；行数与耗时会随你的数据分布浮动，请以自己环境的 EXPLAIN ANALYZE 为准。

# ORDER BY 配合 LIMIT 触发的索引选择陷阱

同一条 SQL，只把 WHERE 里的日期换一天，一次 0.01 秒返回，另一次要 13.10 秒—— 而且两次的返回结果都是 0 条。

没有锁等待，没有资源争抢，表结构和索引一个字没动。慢的那次，EXPLAIN 里 rows 只估了 10 行，看上去比快的那次还"便宜"。

这不是玄学，是 ORDER BY + LIMIT 组合下优化器的一种典型误判：它会为了省掉排序，主动放弃过滤性最好的索引。它以为自己抄了条近路，而当过滤条件恰好命中 0 行时，这条近路会变成全表最长的那条弯路。

# 1. 两个索引，优化器只能选一个

先看这类查询的通用形状——一个按时间过滤、按另一个时间排序、再取前 N 条的分页查询：

SELECT id, created_at, updated_at, status, biz_type
FROM t_record
WHERE created_at >= '2026-08-01 00:00:00'
  AND created_at <  '2026-08-02 00:00:00'
  AND status   = 1
  AND biz_type = 2
ORDER BY updated_at DESC
LIMIT 10;

1
2
3
4
5
6
7
8

对应的表结构（与业务无关的抽象模型，下文所有实验都基于它）：

CREATE TABLE t_record (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  created_at  DATETIME        NOT NULL,
  updated_at  DATETIME        NOT NULL,
  status      TINYINT         NOT NULL DEFAULT 0,
  biz_type    TINYINT         NOT NULL DEFAULT 0,
  payload     VARCHAR(255)    NOT NULL DEFAULT '',
  PRIMARY KEY (id),
  KEY idx_created_at (created_at),
  KEY idx_updated_at (updated_at)
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4;

1
2
3
4
5
6
7
8
9
10
11

关键在于：WHERE 用的列和 ORDER BY 用的列，分别落在两个不同的单列索引上，而且没有任何一个索引能同时满足两者。 于是优化器面前只有两条路：

路线	走哪个索引	好处	代价
A：先过滤	`idx_created_at`	过滤性强，扫描量小	拿到的行是乱序的，必须额外排序（`filesort`）
B：先排序	`idx_updated_at`	索引天然有序，省掉排序	无法用索引过滤 `created_at`，只能逐行回表判断

LIMIT 10 让优化器倒向了 B。它的想法是：既然只要 10 条，那顺着 idx_updated_at 倒着摸，摸够 10 条就收工——听起来根本扫不了几行，还白赚一个免排序。

打个比方：你要在一条街上找 10 家还在营业的店。B 路线相当于沿街一家家推门看，看够 10 家就回家。只要这条街上开着的店够多，走几十米就搞定了，比先去查营业名录再挨个找要快得多。

但这条捷径有个没写出来的前提：这条街上真的有 10 家在营业。

# 2. 数据空洞：这条街一家店都没开

现在把前提抽掉。假设 2026-08-01 这天一条数据都没有（业务还没开始、数据延迟入库、或者干脆就是个未来日期）——相当于整条街全部关门。走 B 路线会发生什么：

从全表 updated_at 最大的那头开始，倒序扫描整个 idx_updated_at
每拿到一个索引条目，回表取出整行，判断 created_at 是否落在目标区间、 status 和 biz_type 是否匹配
全部不匹配，LIMIT 10 的计数器永远停在 0
没有任何提前退出的机会，只能一路扫到索引末尾
最终宣布："确实是 0 条"——代价是几乎整张表被倒着扫了一遍，外加百万次回表

回到街上那个比方：你推遍了整条街的每一扇门，才确认今天真的一家都没开。 "看够 10 家就回家"这个省事的规矩，在一家都开不出来的时候，一次也没能生效。

而 A 路线在同样的空洞日只需要：翻开 idx_created_at，定位到 2026-08-01，发现区间为空，立刻返回——相当于先查一眼营业名录，发现今天全体歇业，掉头就走。连排序都不用做——0 行没什么可排的。

一边是常数时间，一边是全表扫描 + 全表回表。1300 倍的差距就是这么来的。

反直觉的地方

LIMIT 通常被当作性能优化手段——"我只要 10 条，能有多慢？"。但 LIMIT 只在 能提前凑够数时才省事。凑不够的时候，它不但不省，反而会诱导优化器选一条 没有提前退出可能的执行路径，把小查询变成全表扫描。

返回行数少 ≠ 扫描行数少。 这两个数字在这个场景里可以差六个数量级。

# 3. 复现与判读

# 准备：造一张带"空洞日"的表

灌 100 万行，时间集中在 2026-06-01 ~ 2026-07-31，刻意跳过 2026-08-01：

SET SESSION cte_max_recursion_depth = 1000000;

INSERT INTO t_record (created_at, updated_at, status, biz_type, payload)
WITH RECURSIVE seq(n) AS (
  SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < 1000000
)
SELECT
  DATE_ADD('2026-06-01', INTERVAL FLOOR(RAND() * 61) DAY),
  DATE_ADD('2026-06-01', INTERVAL n SECOND),
  FLOOR(RAND() * 3),
  FLOOR(RAND() * 3),
  REPEAT('x', 200)
FROM seq;

ANALYZE TABLE t_record;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 执行：对比有数据日与空洞日

-- 有数据的一天
EXPLAIN SELECT id FROM t_record
WHERE created_at >= '2026-07-01' AND created_at < '2026-07-02'
  AND status = 1 AND biz_type = 2
ORDER BY updated_at DESC LIMIT 10;

-- 空洞日
EXPLAIN SELECT id FROM t_record
WHERE created_at >= '2026-08-01' AND created_at < '2026-08-02'
  AND status = 1 AND biz_type = 2
ORDER BY updated_at DESC LIMIT 10;

1
2
3
4
5
6
7
8
9
10
11

# 验证：`Extra` 字段是唯一可信的告警灯

健康的计划（走过滤索引）大致长这样：

type: range
key:  idx_created_at
rows: 16384
Extra: Using index condition; Using where; Using filesort

1
2
3
4

踩坑的计划长这样：

type: index
key:  idx_updated_at
rows: 10
Extra: Using where; Backward index scan

1
2
3
4

判读要点，按可信度排序：

Extra 出现 Backward index scan（或正序的 Using index）而 key 是排序列的索引 ——最强信号。说明优化器已经切换到"先排序后过滤"，指望着提前凑够数就收工。
type 从 range 退化成 index —— index 是全索引扫描，不是范围定位。在有明确范围条件的查询里看到 index，基本就是出事了。
rows 变小反而更危险 —— 上面踩坑那份 rows: 10，看起来比 16384 好一个数量级。这个 10 不是"要扫 10 行"，而是"我打算扫到 10 行就停"。它是意图，不是成本。单看 rows 挑执行计划，会挑中最慢的那个。

想看真实扫描量而不是估算值，用 EXPLAIN ANALYZE——它给的是实际执行后的 actual rows 和 actual time，空洞日那次会诚实地告诉你 actual rows = 1000000。

# 4. 四种解法，按推荐顺序

# 解法一：建覆盖两端的联合索引（首选）

问题的根在于"没有一个索引同时满足过滤和排序"。那就造一个：

ALTER TABLE t_record ADD KEY idx_created_updated (created_at, updated_at);

症状：优化器在过滤索引和排序索引之间反复横跳。
原因：两个需求分属两个索引，必然牺牲一个。
解药：联合索引把两个需求合并。等值条件在前、排序列在后时，MySQL 可以用前缀定位范围、用后缀维持有序，一次扫描同时满足过滤和排序。

注意前缀顺序的限制

created_at 在本例是范围条件（>= / <）。范围列之后的索引列无法再用于消除排序，所以 (created_at, updated_at) 只能省掉过滤代价，filesort 仍会保留——但排序对象已经缩小到当天那几行，代价可以忽略。

若把查询改成按天等值匹配（例如额外维护一个 stat_date DATE 列），则 (stat_date, updated_at) 可以做到过滤和排序全走索引，Extra 干净到只剩 Using index condition。低基数的 status / biz_type 也可以视选择性酌情加进来。

# 解法二：`FORCE INDEX` 强制走过滤索引（应急）

SELECT id, created_at, updated_at, status, biz_type
FROM t_record FORCE INDEX (idx_created_at)
WHERE created_at >= '2026-08-01' AND created_at < '2026-08-02'
  AND status = 1 AND biz_type = 2
ORDER BY updated_at DESC
LIMIT 10;

1
2
3
4
5
6

症状：线上正在被慢查询打爆，来不及加索引。
原因：优化器的成本模型在这个数据分布下算错账。
解药：FORCE INDEX 直接剥夺它的选择权，无论查哪天都走范围定位。

代价要认清：索引提示是硬编码的技术债。它绑死了表名与索引名，日后索引改名或重建会让 SQL 直接报错；数据分布变化后，它也可能从"救命"变成"拖累"。当应急止血手段用，随后补上解法一，别让它在代码里长住。

# 解法三：延迟关联，把回表次数压到 N 次

SELECT r.id, r.created_at, r.updated_at, r.status, r.biz_type
FROM t_record r
JOIN (
  SELECT id FROM t_record
  WHERE created_at >= '2026-08-01' AND created_at < '2026-08-02'
    AND status = 1 AND biz_type = 2
  ORDER BY updated_at DESC
  LIMIT 10
) t ON t.id = r.id
ORDER BY r.updated_at DESC;

1
2
3
4
5
6
7
8
9
10

症状：行宽很大（大 TEXT / 多列），回表本身就是主要开销。
原因：外层每扫一行都要拉回完整行数据。
解药：内层只在索引上跑出 10 个主键，外层只回表 10 次。

注意这不能单独解决本文的问题——内层子查询同样可能选错索引。它是减少回表放大的配套手段，需要和解法一或解法二叠加使用。

# 解法四：刷新统计信息（先做，但别指望它兜底）

ANALYZE TABLE t_record;

症状：索引选择时好时坏，重启或大批量写入后突然劣化。
原因：InnoDB 的索引统计是采样估算，大批量写入后可能严重失真。
解药：ANALYZE TABLE 重新采样。代价很低（只更新统计信息，不重建表，区别于 OPTIMIZE TABLE），值得作为排查第一步。

但要清醒：本文的场景里，统计信息再准也救不了。优化器不知道 2026-08-01 是个空洞——直方图能刻画已有数据的分布，却无法预知一个区间外的日期返回 0 行。 ANALYZE 能修复的是"估算偏差"，修不了"捷径没有出口"。

# 5. 可复用要点

WHERE 列与 ORDER BY 列不在同一个索引上时，LIMIT 是风险放大器，不是优化器。 这是索引选择失误的头号高发区。设计分页查询时，第一件事是确认两者能否收进同一个索引。
判读执行计划要看 Extra 和 type，别看 rows。 Backward index scan + 排序列索引 = 优化器在抄近路；type: index 出现在有范围条件的查询里 = 全索引扫描。而 rows 在 LIMIT 查询里表达的是意图不是成本， 越小可能越危险。
用返回 0 行的边界条件测你的分页接口。 常规测试都拿有数据的日期跑，恰好绕开了这个坑。空结果集是最坏情况—— 它让 LIMIT 永远无法提前退出。未来日期、刚上线的业务、被清理过的历史区间，都是天然的测试用例。
索引提示是止血带，不是治疗方案。 FORCE INDEX 用来救火，联合索引用来治本。前者绑死索引名、随数据分布腐化，不该长期留在代码里。
先 ANALYZE，但别指望它。 统计信息刷新成本低、值得作为排查第一步，却修不了"区间内无数据"这类优化器根本无从预知的场景。

# 6. 延伸阅读

通论篇：MySQL为什么有时候会选错索引 ——本文是其中 ORDER BY + LIMIT 这一特定触发器的深入拆解。
成本工具：optimize table 和 analyze table 的区别
想追到优化器的决策现场，打开 optimizer_trace，重点看 range_scan_alternatives（此时选择往往还是对的）与其后的 clause_processing 阶段——ORDER BY 优化正是在后者把 access_type 从 range 降级为 index_scan 的。

# 7. Agent 可直接解析的元数据块

{
  "_meta": {
    "doc_version": "2026-08-07",
    "article_id": "mysql-33-orderby-limit-index-trap",
    "profile_context": "any",
    "estimated_setup_time": "20min"
  },
  "symptom_signature": {
    "description": "同一 SQL 换个日期慢 1000 倍，两次都返回 0 行",
    "explain_red_flags": [
      "Extra 含 'Backward index scan' 且 key 是 ORDER BY 列的索引",
      "type 从 range 退化为 index",
      "rows 异常小且约等于 LIMIT 值"
    ]
  },
  "quick_start": {
    "step_1": "EXPLAIN <慢查询>; 检查 key 是否为 ORDER BY 列的索引",
    "step_2": "EXPLAIN ANALYZE <慢查询>; 对比 actual rows 与 estimated rows",
    "step_3": "ANALYZE TABLE <表名>; 重新采样统计信息后复测",
    "step_4": "ALTER TABLE <表名> ADD KEY idx_filter_sort (<过滤列>, <排序列>);",
    "step_5": "应急场景改用 FORCE INDEX (<过滤列索引>) 先止血"
  },
  "safety_rules": [
    "生产环境加索引前先评估表大小与 DDL 方式（pt-online-schema-change / gh-ost）",
    "FORCE INDEX 仅作应急，须登记为技术债并在补索引后移除",
    "不要用 OPTIMIZE TABLE 代替 ANALYZE TABLE，前者等价整表重建",
    "任何 EXPLAIN 结论都以本地实测为准，不要照搬文中示意输出"
  ],
  "verification": {
    "check_1": "EXPLAIN 输出中 key 应为过滤列索引，Extra 不再含 Backward index scan",
    "check_2": "对空洞日期（无数据的日期）执行查询，耗时应与有数据日期同量级",
    "check_3": "EXPLAIN ANALYZE 的 actual rows 应远小于表总行数"
  }
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

AI Agent 使用指南：

当用户说"同一条 SQL 有时快有时慢" → 执行 quick_start.step_1、step_2，按 symptom_signature.explain_red_flags 逐条比对
当用户说"分页查询突然变慢" → 确认 WHERE 列与 ORDER BY 列是否分属不同索引，命中则走 step_4
当用户说"要紧急止血" → 执行 step_5，随后必须补 step_4 并移除索引提示

#索引优化 #查询性能 #执行计划 #优化器

上次更新: 8/7/2026

← MySQL 的 Left join、Right join 和 Inner join 的区别 Redis 运维知识地图：从单机到 Cluster 排障→