作者简介：陆星，瑞蓝创数据库工程师
原创内容未经授权不得随意使用，转载请联系小编并注明来源
一. 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 的分页方式。
END
瑞蓝创 OceanBase OBCP V4 精英训练营
点击下方图片立即了解详情
▼ 点击「阅读原文」，了解更多产品技术文章