排序 limit 场景性能优化 - OceanBase 数据库 V4.2.1

排序 limit 场景性能优化

更新时间：2026-04-16 00:11:36

包含 order by limit 的查询性能优化首先期望获得流式执行计划，减少不必要数据的读取和处理。当阻塞算子无法避免或出现其他开销较大的场景时，可以利用 limit 返回少量行数的特点，减少执行时不必要的较大开销进行优化。

消除排序

对包含 order by limit 的简单单表查询，可以通过创建联合索引，在使用索引进行快速扫描的同时消除排序，实现一个流式计划，避免大量扫描满足过滤条件的行。

Q3_1_1:
create table t1(c1 int, c2 int, c3 int, c4 int, pk int primary key);
create index idx on t1(c1, c2, c3);
select * from t1 where c1 = 1 and c3 > 0 order by c2 limit 10;

查询 Q3_1_1 能够利用 c1 = 1 等值条件抽取 query range 进行索引快速扫描，由于 c1 上的等值条件，使用 idx 索引扫描也直接获得了按 c2 有序的结果集，在扫描得到 10 行数据后能够提前结束计划的执行。

需要注意的是，查询 Q3_1_1 中有一个 c3 > 0 谓词，如果以 (c1, c3, c2) 为顺序创建索引，可以直接使用索引抽取 query range 实现 c3 > 0 的过滤，但是此时无法获得按 c2 有序的结果集，当 c3 > 0 有很强的过滤性时，消除排序获得流式计划减少数据扫描基本没有带来优化，使用 idx(c1, c2, c3) 反而使得计划无法充分利用 c3 > 0 的过滤性。

减少扫描/计算开销

对于大宽表或包含 lob 等数据类型表的扫描，当查询中包含 order by 和 limit 时，优化器将使用晚期物化的优化策略能够减少大宽表大量数据的扫描。

对于下方查询 Q3_2_1，过滤条件及排序列均在索引 idx 上，先通过索引 idx 扫描排序列、过滤条件列及主键，通过排序后得到最终需要返回的行，再通过主键进行主表扫描返回所有列。对于包含 order by 和 limit 的复杂查询，也可以按照相同的策略对查询进行改写。

Q3_2_2 中通过 no_use_late_materialization 禁止了晚期物化优化，此时需要通过索引回表读取表中所有满足过滤条件的匹配行。

Q3_2_1:
create table t1(c1 int, c2 int, c3 int, c4 int, pk int primary key);
create index idx on t1(c1, c2, c3);
select /*+index(t idx) */ * from t1 t where c1 > 0 order by c2 limit 10;
==============================================
|ID|OPERATOR        |NAME    |EST. ROWS|COST |
----------------------------------------------
|0 |NESTED-LOOP JOIN|        |10       |14855|
|1 | TOP-N SORT     |        |10       |14842|
|2 |  TABLE SCAN    |t(idx)  |93114    |11972|
|3 | TABLE GET      |t1_alias|1        |2    |
==============================================

Outputs & filters:
-------------------------------------
  0 - output([t.c1], [t.c2], [t1_alias.c3], [t1_alias.c4], [t.pk]), filter(nil), rowset=256,
      conds(nil), nl_params_([t.pk])
  1 - output([t.pk], [t.c1], [t.c2]), filter(nil), rowset=256, sort_keys([t.c2, ASC]), topn(10)
  2 - output([t.pk], [t.c1], [t.c2]), filter(nil), rowset=256,
      access([t.pk], [t.c1], [t.c2]), partitions(p0)
  3 - output([t1_alias.c3], [t1_alias.c4]), filter(nil), rowset=256,
      access([t1_alias.c3], [t1_alias.c4]), partitions(p0)

Q3_2_2:
select /*+index(t idx) no_use_late_materialization*/ * from t1 t
where c1 > 0 order by c2 limit 10;
========================================
|ID|OPERATOR   |NAME  |EST. ROWS|COST  |
----------------------------------------
|0 |TOP-N SORT |      |10       |265257|
|1 | TABLE SCAN|t(idx)|93114    |262387|
========================================

Outputs & filters:
-------------------------------------
  0 - output([t.c1], [t.c2], [t.c3], [t.c4], [t.pk]), filter(nil), rowset=256, sort_keys([t.c2, ASC]), topn(10)
  1 - output([t.pk], [t.c1], [t.c2], [t.c3], [t.c4]), filter(nil), rowset=256,
      access([t.pk], [t.c1], [t.c2], [t.c3], [t.c4]), partitions(p0)