MySQL ORDER BY id LIMIT 走错主键索引实测：LIMIT 1 要 290ms、LIMIT 2000 只要 32ms，建了 (shop_id, id) 也不用
在 MySQL 8.0.46 测试库 olimit_lab（自造 orders 200 万行）上实测 prefer_ordering_index：一个「已迁走」的大店 4 万单全在老 id 段，WHERE shop_id=? ORDER BY id DESC LIMIT 20 被优化器改成主键倒序扫，读 180 万行、279–337ms；LIMIT 2000 反而换回二级索引只要 31–35ms。全局关开关会把在营大店从 0.3ms 拖到 519–906ms；新建 (shop_id, id) 索引后优化器照样走主键，用 INDEX hint 钉住才降到 0.4–0.9ms。附只读排查 SQL、演示脚本与 CSV。
MySQL 8.0.46 实测（测试库、自造 200 万行，2026-11-01）：同一条 SELECT … WHERE shop_id=? ORDER BY id DESC LIMIT 20，对「4 万单全在老 id 段」的店，优化器估计主键倒序扫 532 行就能凑满 20 行，实际倒着读了 1,800,099 行、279–337ms；LIMIT 2000 时改走 idx_shop_created 只要 31–35ms。我的结论：这类慢查询按语句修——建 (过滤列, id) 索引并用 INDEX hint 钉住（三类店都降到 0.4–0.9ms），不要全局关 prefer_ordering_index（在营大店从 0.3ms 变成 519–906ms），也别以为建了索引优化器就会用（不加 hint 仍 280–286ms）。
适用对象
- 结论先说：MySQL 8.0.46 上，WHERE a=? ORDER BY id LIMIT n 在数据倾斜时会被改成主键倒序扫；本次 LIMIT 1 约 288–314ms，LIMIT 2000 只要 31–35ms——LIMIT 越小越容易中招。
- 起因是官方文档写明的假设：低 LIMIT 下优化器按「数据分布大致均匀」估算多久能凑够 n 行；4 万单全在老 id 段的店，估 532 行、实际倒读 1,800,099 行。
- 全局 prefer_ordering_index=off 会误伤：迁走大店从 280ms 降到 28–30ms，但在营大店从 0.23–0.30ms 升到 519–906ms（约 2000 倍）。
- 新建 KEY(shop_id, id) 后，不加 hint 优化器仍走主键（280–286ms）；/*+ INDEX(orders idx_shop_id) */ 钉住后三类店都只读 20 行、0.4–0.9ms。
- 监控容易漏：这条语句 SUM_NO_INDEX_USED=0，sys.statements_with_full_table_scans 不列它；9 次调用平均 94.7ms 把 282.6ms 的最大值摊薄了——要按 rows_examined/rows_sent 和 max 查。
测试库怎么造：200 万订单、一个「迁走的大店」
orders 表有自增主键 id、shop_id、amount、created_at 等列，只有一个二级索引 KEY idx_shop_created(shop_id, created_at)——这是很常见的「按店查最近订单」索引。数据按 id 递增插入，created_at 从 2025-01-01 起每 15 秒一单，所以 id 越大时间越新。
关键是 shop 9002：它模拟一家曾经很大、后来不再下单的店，4 万单全部落在 id ≤ 199996（全表最前 10%）。在营大店 shop 1 的 57.8 万单均匀分布在全表；普通店 777 只有 1000 单。
本节判断
- 实例：MySQL 8.0.46，innodb_buffer_pool_size=1GiB，optimizer_switch 全默认（prefer_ordering_index=on）。
- 我先试过一个只有 2000 单的老店（shop 9001），优化器选的是 ref + filesort，3 次里两次 3.7–4.2ms、一次 19.3ms，没中招；要「量大且集中在老 id 段」才会触发。
| 店 | 订单数 | id 范围 | 模拟的业务 |
|---|---|---|---|
| shop 1 | 578,000 | 2 – 2,000,000 | 在营大店，最新订单在表尾 |
| shop 777 | 1,000 | 1,225 – 1,999,225 | 普通店，订单均匀分布 |
| shop 9002 | 40,000 | 1 – 199,996 | 已迁走的大店，全在老 id 段 |
| 全表 | 2,000,000 | 1 – 2,000,000 | PRIMARY 211.8MB，idx_shop_created 89.7MB |
同一条 SQL：在营大店 0.3ms，迁走大店 280ms
查询是 SELECT id, amount, created_at FROM orders WHERE shop_id = ? ORDER BY id DESC LIMIT 20。对 shop 9002，EXPLAIN 显示 type=index、key=PRIMARY、rows=532、filtered=3.76、Extra 里是 Using where; Backward index scan——优化器的算盘是：shop 9002 占全表约 2%，那从表尾倒着读大约 500 多行就能凑够 20 行，还省掉一次排序。
实际情况是表尾 180 万行里一单都没有。EXPLAIN ANALYZE 显示扫描了 1.8e+6 行，Handler_read_prev=1,800,099，3 次耗时 279.0–336.6ms。同一条 SQL 换成 shop 1，倒读 68 行就凑满，0.23–0.30ms；这就是「换个参数又很快」的来源。
| 店 | 计划 | 估计行 | 实际读取 | 耗时 |
|---|---|---|---|---|
| shop 9002 | PRIMARY 倒序扫 | 532 | 1,800,099（read_prev） | 279.0–336.6ms |
| shop 1 | PRIMARY 倒序扫 | 48 | 68（read_prev） | 0.23–0.30ms |
| shop 777 | idx_shop_created + filesort | 1000 | 1000（read_next） | 1.6–2.9ms |
LIMIT 1 比 LIMIT 2000 慢 9 倍
我把 shop 9002 的 LIMIT 从 1 扫到 2000：LIMIT 1、20、100、500、1000、1500 全部是主键倒序扫，耗时都在 271–314ms；到 LIMIT 2000，优化器算出倒着读要读太多行，才换回 idx_shop_created（读 4 万行再排序），31–35ms。
这和很多人的直觉相反——「只要一条」应该最快。原因就在官方文档那句话：low-limit 优化假设数据分布大致均匀，LIMIT 越小，优化器越相信「很快就能撞到」。所以「取最新一条」的接口（ORDER BY id DESC LIMIT 1）恰恰最危险。
| LIMIT | 计划 | 耗时（3 次） |
|---|---|---|
| 1 | PRIMARY 倒序扫，read_prev 1,800,004 | 288.3–314.3ms |
| 20 | PRIMARY 倒序扫，read_prev 1,800,099 | 279.0–336.6ms |
| 1500 | PRIMARY 倒序扫，read_prev 1,807,499 | 271.0–275.6ms |
| 2000 | idx_shop_created + filesort，read_next 40,000 | 30.7–35.1ms |
全局关开关、FORCE INDEX、加索引、钉索引、改写排序：实测对比
我把常见的几种修法在三类店上各跑 3 次。最容易被采纳的是 SET GLOBAL optimizer_switch='prefer_ordering_index=off'——它确实把 shop 9002 从 280ms 降到 27.5–29.6ms，但同时把 shop 1 从 0.3ms 拖到 518.6–905.7ms：没了倒序扫，优化器只能把 57.8 万单读出来再排序。一个参数救一家、伤一家。
第二个坑是「建了对的索引也不用」。我在线加了 KEY idx_shop_id(shop_id, id)（ALGORITHM=INPLACE, LOCK=NONE，1505ms，38.6MB），再 ANALYZE。shop 9002 的计划纹丝不动，仍是主键倒序扫，280.1–285.6ms；shop 777 也仍选 idx_shop_created + filesort。只有用 /*+ INDEX(orders idx_shop_id) */（或 FORCE INDEX）钉住后，三类店都变成「在 (shop_id, id) 上倒读 20 行」，0.37–0.9ms。
本节判断
- FORCE INDEX(idx_shop_created) 和 ORDER BY id+0（让排序无法用主键）对 shop 9002 都是 26–28ms，效果等同关开关，同样会拖慢 shop 1。
- 「改写排序」不建新索引，直接复用 idx_shop_created 倒读，但前提是 created_at 与 id 严格同向单调；有补录、改时间、时钟回拨的表不能这么改，否则「最新 20 条」会错。
- 只把 hint 写成 SET_VAR(optimizer_switch='prefer_ordering_index=off') 的语句级版本，在新索引存在时仍选 idx_shop_created + filesort（shop 1 约 630ms），不是正确答案。
| 修法 | shop 9002 | shop 1 | shop 777 | 我的评价 |
|---|---|---|---|---|
| 默认 | 279–337ms | 0.23–0.30ms | 1.6–2.9ms | 倾斜店中招 |
| prefer_ordering_index=off | 27.5–29.6ms | 518.6–905.7ms | 1.6–2.7ms | 别全局关 |
| 新建 (shop_id, id)，不加 hint | 280.1–285.6ms | 0.3–0.5ms | 1.5–2.6ms | 优化器不选 |
| 新建 (shop_id, id) + INDEX hint | 0.37–0.9ms | 0.52–0.9ms | 0.47–0.6ms | 推荐 |
| 改写 ORDER BY created_at DESC, id DESC | 4.0–5.5ms | 8.3–11.3ms | 0.5–1.0ms | 有前提的备选 |
为什么我主张「按语句钉计划」，而不是调参数或等统计信息
这次我考虑过五个切入角度：LIMIT 越小越慢、别全局关开关、建对索引不等于用上、监控抓不到、改按时间排序。最终我给的判断是：这类慢查询应该按语句修——建 (过滤列, id) 索引，并在这条 SQL 上用 INDEX hint 钉住。
理由有三。第一，问题根源是优化器的均匀分布假设，而业务数据天然不均匀：迁走的店、停用的商品类型、归档过的批次，都会集中在老 id 段；ANALYZE 把统计算得再准，也改不了「假设均匀」这件事。第二，全局开关是实例级的，会同时改变所有走对了的语句，本次实测在营大店变慢约 2000 倍。第三，只建索引不钉住，优化器的成本比较里新索引依然输给「倒序扫 532 行」的估算——你得把选择权收回来。
代价也要说清楚：hint 写死在 SQL 里，索引改名或删除时要同步改代码（我在 8.0.46 上试过：FORCE INDEX 指向不存在的索引直接报 ERROR 1176；/*+ INDEX() */ 指向不存在的索引只给 Warning 3128 然后被忽略，查询会悄悄退回原计划），所以要把它登记进索引变更清单。我不建议对所有 ORDER BY … LIMIT 都加 hint，只钉被排查确认中招的那几条。
怎么在自己库里找到它：别按「没用索引」筛
我让同一指纹在三类店上各跑 3 次，然后查 performance_schema.events_statements_summary_by_digest：9 次调用平均 94.7ms、最大 282.6ms，rows_sent 180、rows_examined 5,403,567，扫描/返回比约 30020；SUM_NO_INDEX_USED=0。sys.statements_with_full_table_scans 视图也不列这条语句——因为它确实「用了索引」（主键），只是用法不对。
所以排查条件要换成：DIGEST_TEXT 带 ORDER BY 和 LIMIT、rows_examined 超过 rows_sent 的 1000 倍，并且按 MAX_TIMER_WAIT 排序而不是平均值。下载区的只读 SQL 就是这么写的，全部是 SELECT；拿到可疑语句后，在从库上 EXPLAIN FORMAT=TREE，看到「Index scan on orders using PRIMARY (reverse)」加上 Limit，就是本文的场景。
本节判断
- 再确认一下过滤值是否集中在老 id 段：SELECT MIN(id), MAX(id), COUNT(*) FROM orders WHERE shop_id = ?;
- 慢日志的 long_query_time 若设 1s，这条 280ms 的语句根本进不了慢日志；体检时我会同时看 digest 的 max 和 examined/sent。
- EXPLAIN ANALYZE 会真的执行一次查询，线上要在从库或低峰做。
觉得有用？转给正在做这件事的同事或朋友
参考依据
以下来源用于确认市场趋势、政策背景和术语边界；具体落地方案仍以客户的数据范围、权限和交付目标为准。
常见问题
为什么 ORDER BY id LIMIT 20 不走 shop_id 上的索引，反而扫主键？
MySQL 有 low-limit 排序优化（optimizer_switch 的 prefer_ordering_index，默认 on）：它估算按主键顺序读多少行能凑够 LIMIT，并假设数据分布大致均匀。过滤值集中在老 id 段时估算会严重偏小，本次估 532 行、实际倒读 1,800,099 行。
把 prefer_ordering_index 全局关掉可以吗？
不建议。本次实测关掉后迁走大店从约 280ms 降到 28–30ms，但在营大店从 0.23–0.30ms 升到 519–906ms。开关是实例级的，会影响所有本来选对计划的语句；要关也只在具体语句上用 SET_VAR hint，并且先实测。
我已经建了 (shop_id, id) 索引，为什么 EXPLAIN 还是 PRIMARY？
本次实测也是这样：新建 idx_shop_id 并 ANALYZE 后，不加 hint 的 shop 9002 仍走主键倒序扫（280–286ms）。需要 /*+ INDEX(orders idx_shop_id) */ 或 FORCE INDEX 钉住，钉住后只读 20 行、0.4–0.9ms。
怎么判断我的库里有没有这类慢查询？
不要按 SUM_NO_INDEX_USED 或「全表扫描」筛，这条语句两者都是 0。按 events_statements_summary_by_digest 里 DIGEST_TEXT 含 ORDER BY…LIMIT、rows_examined 远大于 rows_sent 筛，并看 MAX_TIMER_WAIT；本次平均 94.7ms 掩盖了 282.6ms 的最大值。
这些数字能代表我的生产库吗？
不能直接代表。这是 MySQL 8.0.46 测试库、自造 200 万行、热缓存下的结果，用来说明机制和量级；冷缓存、更大的表会更慢。生产以你库上的 EXPLAIN ANALYZE（在从库执行）为准。
下一步
推荐动作
你提供 events_statements_summary_by_digest 的只读导出和可疑语句的 EXPLAIN（可脱敏），我们标出哪些 ORDER BY … LIMIT 在倾斜数据上走错了索引、该钉哪个索引；生产变更由你方授权执行。