一. ORDER BY LIMIT 优化的核心优势：

ORDER BY LIMIT 快不仅仅是因为最终只返回少量结果。真正决定 ORDER BY LIMIT 性能的，往往不是最后返回了几行，而是优化器和执行器能不能借着这个 LIMIT，把大量本来要做的 无效工作提前砍掉。

几类无效工作包括：

• 扫描了大量最终不会进入结果集的数据；
• 对大量候选行做了完整排序；排序数据太多、行太宽，导致内存压力上升，甚至落盘；
• 上游算子明明只需要前几行，却仍然处理了整批数据。

二. ORDER BY ... LIMIT   3 种执行路径:

路径 1：全量排序（最差）

全表/大范围扫描 -> SORT -> LIMIT N 如果既没有可用有序路径，又无法有效做 Top-N，最终就会退化为普通 SORT。这通常意味着：

路径 2：Top-N 排序

全表/大范围扫描 -> TOP-N SORT -> 输出前 N 行 如果下层不能直接提供完整顺序，但查询带 LIMIT，优化器会尽量把 LIMIT 并入排序，形成 TOP-N SORT。Top-N 不是“全部排完再取前 N”，不需要长期保留所有候选行输出阶段可以在达到 topn_cnt_ 后提前停止。

路径 3：利用索引有序性

有序索引扫描 -> LIMIT N 如果访问路径天然有序，比如索引顺序与 ORDER BY 一致，那么优化器会直接复用下层顺序，不再分配 SORT。

三. 判断是否需要排序关键位置：

• 无SORT算子:完全消序
• 有SORT算子：
• 有prefix_pos(1)  部分消序
• 无prefix_pos 不能消序

例1（无prefix_pos 不能消序）

explain EXTENDED_NOADDR    SELECT * from t1_order where c2=10 ORDER   by  c4  limit 10;
|ID|OPERATOR          |NAME               |EST.ROWS|EST.TIME(us)|
-----------------------------------------------------------------
|0 |TOP-N SORT        |                   |1       |5           |
|1 |└─TABLE RANGE SCAN|t1_order(idx_c2_c3)|1       |5           |
=================================================================
Outputs & filters:
-------------------------------------
  0 - output([t1_order.c1], [t1_order.c2], [t1_order.c3], [t1_order.c4]), filter(nil), rowset=16
      sort_keys([t1_order.c4, ASC]), topn(10)
  1 - output([t1_order.c2], [t1_order.c1], [t1_order.c3], [t1_order.c4]), filter(nil), rowset=16
      access([t1_order.__pk_increment], [t1_order.c2], [t1_order.c1], [t1_order.c3], [t1_order.c4]), partitions(p0)
      is_index_back=true, is_global_index=false, 
      range_key([t1_order.c2], [t1_order.c3], [t1_order.__pk_increment]), range(10,MIN,MIN ; 10,MAX,MAX), 
      range_cond([t1_order.c2 = 10])

例2 （有prefix_pos(1)  部分消序）

explain EXTENDED_NOADDR    SELECT * from t1_order where c2=10 ORDER   by  c3,c4  limit 10;
|ID|OPERATOR          |NAME               |EST.ROWS|EST.TIME(us)|
-----------------------------------------------------------------
|0 |TOP-N SORT        |                   |1       |5           |
|1 |└─TABLE RANGE SCAN|t1_order(idx_c2_c3)|1       |5           |
=================================================================
Outputs & filters:
-------------------------------------
  0 - output([t1_order.c1], [t1_order.c2], [t1_order.c3], [t1_order.c4]), filter(nil), rowset=16
      sort_keys([t1_order.c3, ASC], [t1_order.c4, ASC]), topn(10), prefix_pos(1)
  1 - output([t1_order.c2], [t1_order.c1], [t1_order.c3], [t1_order.c4]), filter(nil), rowset=16
      access([t1_order.__pk_increment], [t1_order.c2], [t1_order.c1], [t1_order.c3], [t1_order.c4]), partitions(p0)

例3 （无SORT算子:完全消序）

explain EXTENDED_NOADDR    SELECT * from t1_order where c2=10 ORDER   by  C3  limit 10;
|ID|OPERATOR        |NAME               |EST.ROWS|EST.TIME(us)|
---------------------------------------------------------------
|0 |TABLE RANGE SCAN|t1_order(idx_c2_c3)|1       |5           |
===============================================================
Outputs & filters:
-------------------------------------
  0 - output([t1_order.c1], [t1_order.c2], [t1_order.c3], [t1_order.c4]), filter(nil), rowset=16
      access([t1_order.__pk_increment], [t1_order.c2], [t1_order.c1], [t1_order.c3], [t1_order.c4]), partitions(p0)
      limit(10), offset(nil), is_index_back=true, is_global_index=false,

四. 是否起到 TOP-N 优化呢？

• 是否没有 SORT  如果目标本来是利用索引有序性，没有 SORT 才算真正消序。
• TOP-N SORT  如果不能完全消序，但出现了 TOP-N SORT   说明优化器至少把 LIMIT 合并进排序了。
• 扫描行数是否接近 LIMIT  ，如果  LIMIT 10   ，却扫描几十万行，虽然看上去上优化了但实际收益很差

五. 适用场景：

排行榜 Top-N

SELECT user_id, score
FROM player_score
ORDER BY score DESC
LIMIT 100;

大表分页首页

SELECT id, title, publish_time
FROM article
WHERE tenant_id = 1001
ORDER BY publish_time DESC
LIMIT 50;

大表 JOIN 但最终只取少量记录

SELECT a.*, b.name
FROM big_order a
JOIN user_info b ON a.user_id = b.id
WHERE a.status = 1
ORDER BY a.create_time DESC
LIMIT 20;

先对驱动表做 ORDER BY LIMIT，再 JOIN ，NL  流式处理

六. ORDER BY LIMIT失效或收益差的常见原因

在实际调优中，一个很常见的误区是：只要看见 TOP-N SORT，就觉得这个 SQL 已经优化得不错了。实际上未必。

1. TOP-N SORT 可能只优化了排序，没有优化扫描

如果 LIMIT 10，但底层为了找到这 10 行仍然扫描了几十万行，那么虽然计划看上去“已经做了 Top-N”，但收益仍然可能很有限。 因为这时真正省掉的只是 全量排序的那部分代价，而不是扫描本身的代价。

2. 过滤列和排序列没有形成有效联合索引

比如：

WHERE status = 0
ORDER BY create_time
LIMIT 10

如果只有 create_time 索引，没有 (status, create_time) 联合索引，那么即使可以按 create_time 顺序扫描，也可能需要扫很多行，才能凑出 10 条满足 status = 0 的数据。

这种场景下，顺序是有了，但早停能力并不强，收益自然会打折。

3. 多分区访问时，往往只能做到“分区内有序”

单分区场景下，索引保序更容易直接转化成全局有序；但多分区场景就复杂得多。很多时候每个分区内部可以各自有序，最终仍需要上层做归并。

所以有些时候你虽然没有看到传统意义上的全量排序，但仍然可能存在 merge sort 或 local merge sort 一类的额外工作。

4. 深分页会天然削弱 LIMIT 的收益

像 LIMIT 10 OFFSET 100000 这类 SQL，即使最后仍然只返回 10 行，也不代表代价低。因为前面的 100000 行并不会凭空消失，执行器通常还是得处理它们的顺序问题。这也是为什么很多业务的深分页最终都要转向基于游标或 seek 的分页方式。