--- title: 分区表场景下 order by limit 查询优化-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于分区表场景下 order by limit 查询优化相关的常见问题和使用技巧，帮助您快速解决分区表场景下 order by limit 查询优化的难题。 --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 划线反馈 # 分区表场景下 order by limit 查询优化 更新时间：2026-07-24 10:01 适用版本： V4.0.x、V4.1.x、V4.2.x 内容类型：Troubleshoot ## 问题现象 两张分区表 join 并且按照 t1 表 id order by limit 在 OceanBase 数据库上执行耗时 10s，耗时比较长，SQL 和计划如下。 ```shell SELECT `t1`.`id`, `t1`.`created_at` AS `assigned_at`, `t1`.`rls`, `t2`.`uid` FROM `user_label` AS `t1`, `urbs_user` AS `t2` WHERE ( (`t1`.`label_id` = 1) AND (`t1`.`id` <= 9223372036854775807) AND (`t1`.`user_id` = `t2`.`id`) ) ORDER BY `t1`.`id` DESC LIMIT 21; ``` 执行计划输出结果如下。 ```shell ======================================================================================== |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| ---------------------------------------------------------------------------------------- |0 |LIMIT | |21 |55580673 | |1 |└─PX COORDINATOR MERGE SORT | |21 |55580673 | |2 | └─EXCHANGE OUT DISTR |:EX10000 |21 |55580653 | |3 | └─TOP-N SORT | |21 |55580606 | |4 | └─NESTED-LOOP JOIN | |191816 |55572951 | |5 | ├─PX PARTITION ITERATOR | |198677 |30334 | |6 | │ └─TABLE RANGE SCAN |t1(idx_lid_uid_ca_rls)|198677 |30334 | |7 | └─DISTRIBUTED TABLE RANGE SCAN|t2 |1 |279 | ======================================================================================== Outputs & filters: ------------------------------------- 0 - output([t1.id(0x7f13a5c26c10)], [t1.created_at(0x7f13a5c28ab0)], [t1.rls(0x7f13a5c290b0)], [t2.uid(0x7f13a5c249c0)]), filter(nil), rowset=256 limit(21), offset(nil) 1 - output([t1.id(0x7f13a5c26c10)], [t1.created_at(0x7f13a5c28ab0)], [t1.rls(0x7f13a5c290b0)], [t2.uid(0x7f13a5c249c0)]), filter(nil), rowset=256 sort_keys([t1.id(0x7f13a5c26c10), DESC]) 2 - output([t1.id(0x7f13a5c26c10)], [t1.created_at(0x7f13a5c28ab0)], [t1.rls(0x7f13a5c290b0)], [t2.uid(0x7f13a5c249c0)]), filter(nil), rowset=256 dop=1 3 - output([t1.id(0x7f13a5c26c10)], [t1.created_at(0x7f13a5c28ab0)], [t1.rls(0x7f13a5c290b0)], [t2.uid(0x7f13a5c249c0)]), filter(nil), rowset=256 sort_keys([t1.id(0x7f13a5c26c10), DESC]), topn(21) 4 - output([t1.id(0x7f13a5c26c10)], [t1.created_at(0x7f13a5c28ab0)], [t1.rls(0x7f13a5c290b0)], [t2.uid(0x7f13a5c249c0)]), filter(nil), rowset=256 conds(nil), nl_params_([t1.user_id(0x7f13a5c22e90)(:0)]), use_batch=false 5 - output([t1.id(0x7f13a5c26c10)], [t1.user_id(0x7f13a5c22e90)], [t1.created_at(0x7f13a5c28ab0)], [t1.rls(0x7f13a5c290b0)]), filter(nil), rowset=256 force partition granule 6 - output([t1.id(0x7f13a5c26c10)], [t1.user_id(0x7f13a5c22e90)], [t1.created_at(0x7f13a5c28ab0)], [t1.rls(0x7f13a5c290b0)]), filter([t1.id(0x7f13a5c26c10) <= 9223372036854775807(0x7f13a5c264c0)]), rowset=256 access([t1.id(0x7f13a5c26c10)], [t1.user_id(0x7f13a5c22e90)], [t1.created_at(0x7f13a5c28ab0)], [t1.rls(0x7f13a5c290b0)]), partitions(p[0-15]) is_index_back=false, is_global_index=false, filter_before_indexback[false], range_key([t1.label_id(0x7f13a5c259b0)], [t1.user_id(0x7f13a5c22e90)], [t1.created_at(0x7f13a5c28ab0)], [t1.rls(0x7f13a5c290b0)], [t1.id(0x7f13a5c26c10)]), range(1,MIN,MIN,MIN,MIN ; 1,MAX,MAX,MAX,MAX), range_cond([t1.label_id(0x7f13a5c259b0) = 1(0x7f13a5c25260)]) 7 - output([t2.uid(0x7f13a5c249c0)]), filter(nil), rowset=256 access([t2.uid(0x7f13a5c249c0)]), partitions(p[0-15]) is_index_back=false, is_global_index=false, range_key([t2.id(0x7f13a5c27f00)], [t2.uid(0x7f13a5c249c0)]), range(MIN ; MAX), range_cond([:0 = t2.id(0x7f13a5c27f00)(0x7f13a5cfccc0)]) Used Hint: ------------------------------------- /*+ */ Qb name trace: ------------------------------------- stmt_id:0, stmt_type:T_EXPLAIN stmt_id:1, SEL$1 Outline Data: ------------------------------------- /*+ BEGIN_OUTLINE_DATA LEADING(@"SEL$1" ("urbs"."t1"@"SEL$1" "urbs"."t2"@"SEL$1")) USE_NL(@"SEL$1" "urbs"."t2"@"SEL$1") PQ_DISTRIBUTE(@"SEL$1" "urbs"."t2"@"SEL$1" NONE ALL) INDEX(@"SEL$1" "t1"@"SEL$1" "idx_lid_uid_ca_rls") FULL(@"SEL$1" "t2"@"SEL$1") USE_DAS(@"SEL$1" "t2"@"SEL$1") OPTIMIZER_FEATURES_ENABLE('4.2.1.8') END_OUTLINE_DATA */ Optimization Info: ------------------------------------- t1: table_rows:198679 physical_range_rows:198677 logical_range_rows:198677 index_back_rows:0 output_rows:198677 table_dop:1 dop_method:Table DOP avaiable_index_name:[uk_user_label_user_id_label_id, idx_user_label_label_id, idx_lid_uid_ca_rls, idx_g_id_lid_uid_ca_rls, user_label] pruned_index_name:[uk_user_label_user_id_label_id, idx_g_id_lid_uid_ca_rls] stats version:1730435905198673 dynamic sampling level:0 t2: table_rows:62438278 physical_range_rows:1 logical_range_rows:1 index_back_rows:0 output_rows:1 table_dop:1 dop_method:DAS DOP avaiable_index_name:[uk_user_uid, idx_user_active_at, urbs_user] pruned_index_name:[uk_user_uid, idx_user_active_at] stats version:1730435900013086 dynamic sampling level:0 Plan Type: DISTRIBUTED Note: Degree of Parallelisim is 1 because of table property ``` ## 关键信息 order by limit 分区表优场景。 1. 策略就是消除 top-n 算子，走上流式计划，例如 ORDER BY `t1`.`id` DESC LIMIT 让 t1 表当做 nl join 的左表能够利用 t1 表的序消除掉 order by 继承左表的序。 2. 因为是左表是分区表，需要加全局索引保证 order by 继承左表的序。 3. 遇到分区表随机路由的问题，用全局索引表路由需要开启 proxy 参数。 ```shell enable_index_route = true 和 enable_reroute = true ``` ## 问题原因 order by limit 分区表场景的语句优化，优化方向就是消除 TOP-N SORT算子是一个阻塞算子，具体就是能够利用 t1 表的序消除掉 order by 继承左表的序，整个计划就能走上流式的计划，不用扫描全部数据。这个 case 不同的点是因为是分区表，建 local 的索引，走 nl join 没法消除 TOP-N SORT 算子加全局索引 `create index idx_id_uid_lid_uid_ca_rls on user_label(`id`,`user_id`,`label_id`,`created_at`,`rls`)`。 ```shell =============================================================================================== |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| ----------------------------------------------------------------------------------------------- |0 |LIMIT | |21 |6088 | |1 |└─NESTED-LOOP JOIN | |21 |6088 | |2 | ├─DISTRIBUTED TABLE RANGE SCAN|t1(idx_g_id_uid_lid_ca_rls,Reverse)|22 |8 | |3 | └─DISTRIBUTED TABLE RANGE SCAN|t2 |1 |279 | =============================================================================================== Outputs & filters: ------------------------------------- 0 - output([t1.id(0x7f1554626c10)], [t1.created_at(0x7f1554628ab0)], [t1.rls(0x7f15546290b0)], [t2.uid(0x7f15546249c0)]), filter(nil), rowset=256 limit(21), offset(nil) 1 - output([t1.id(0x7f1554626c10)], [t1.created_at(0x7f1554628ab0)], [t1.rls(0x7f15546290b0)], [t2.uid(0x7f15546249c0)]), filter(nil), rowset=256 conds(nil), nl_params_([t1.user_id(0x7f1554622e90)(:0)]), use_batch=false 2 - output([t1.id(0x7f1554626c10)], [t1.user_id(0x7f1554622e90)], [t1.created_at(0x7f1554628ab0)], [t1.rls(0x7f15546290b0)]), filter([t1.label_id(0x7f15546259b0) = 1(0x7f1554625260)]), rowset=256 access([t1.id(0x7f1554626c10)], [t1.user_id(0x7f1554622e90)], [t1.label_id(0x7f15546259b0)], [t1.created_at(0x7f1554628ab0)], [t1.rls(0x7f15546290b0)]), partitions(p0) is_index_back=false, is_global_index=true, filter_before_indexback[false], range_key([t1.id(0x7f1554626c10)], [t1.user_id(0x7f1554622e90)], [t1.label_id(0x7f15546259b0)], [t1.created_at(0x7f1554628ab0)], [t1.rls(0x7f15546290b0)]), range(NULL,MAX,MAX,MAX,MAX ; 9223372036854775807,MAX,1,MAX,MAX), range_cond([t1.id(0x7f1554626c10) <= 9223372036854775807(0x7f15546264c0)]) 3 - output([t2.uid(0x7f15546249c0)]), filter(nil), rowset=256 access([t2.uid(0x7f15546249c0)]), partitions(p[0-15]) is_index_back=false, is_global_index=false, range_key([t2.id(0x7f1554627f00)], [t2.uid(0x7f15546249c0)]), range(MIN ; MAX), range_cond([:0 = t2.id(0x7f1554627f00)(0x7f155475c620)]) 复现： CREATE TABLE `t1` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), `user_id` bigint(20) NOT NULL, `label_id` bigint(20) NOT NULL, `rls` bigint(20) NOT NULL DEFAULT '0', PRIMARY KEY (`id`, `user_id`), UNIQUE KEY `uk_user_label_user_id_label_id` (`user_id`, `label_id`) BLOCK_SIZE 16384 LOCAL, KEY `idx_user_label_label_id` (`label_id`) BLOCK_SIZE 16384 LOCAL, KEY `idx_lid_uid_ca_rls` (`label_id`, `user_id`, `created_at`, `rls`) BLOCK_SIZE 16384 LOCAL ) partition by key(`user_id`) (partition `p0`, partition `p1`, partition `p2`, partition `p3`, partition `p4`, partition `p5`, partition `p6`, partition `p7`, partition `p8`, partition `p9`, partition `p10`, partition `p11`, partition `p12`, partition `p13`, partition `p14`, partition `p15`); CREATE TABLE `t2` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `created_at` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3), `active_at` bigint(20) NOT NULL DEFAULT '0', `uid` varchar(63) COLLATE utf8mb4_bin NOT NULL, `labels` varchar(8190) COLLATE utf8mb4_bin NOT NULL DEFAULT '', PRIMARY KEY (`id`, `uid`), UNIQUE KEY `uk_user_uid` (`uid`) BLOCK_SIZE 16384 LOCAL, KEY `idx_user_active_at` (`active_at`) BLOCK_SIZE 16384 LOCAL ) partition by key(`uid`) (partition `p0`, partition `p1`, partition `p2`, partition `p3`, partition `p4`, partition `p5`, partition `p6`, partition `p7`, partition `p8`, partition `p9`, partition `p10`, partition `p11`, partition `p12`, partition `p13`, partition `p14`, partition `p15`) SELECT /*+ use_das(t1) -- px 计划会导致走不上流式计划 use_das(t2) LEADING(@"SEL$1" ("test"."t1"@"SEL$1" "test"."t2"@"SEL$1")) USE_NL(@"SEL$1" "test"."t2"@"SEL$1") INDEX(@"SEL$1" "t1"@"SEL$1" "idx_id_uid_lid_uid_ca_rls_global") */ `t1`.`id`, `t1`.`created_at` AS `assigned_at`, `t1`.`rls`, `t2`.`uid` FROM `user_label` AS `t1`, `urbs_user` AS `t2` WHERE ( (`t1`.`label_id` = 1) AND (`t1`.`id` <= 9223372036854775807) AND (`t1`.`user_id` = `t2`.`id`) ) ORDER BY `t1`.`id` DESC LIMIT 21 +-----------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +-----------------------------------------------------------------------------------------------------------------------------------------------------+ | ======================================================================================================== | | |ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)| | | -------------------------------------------------------------------------------------------------------- | | |0 |LIMIT | |1 |284 | | | |1 |└─NESTED-LOOP JOIN | |1 |284 | | | |2 | ├─DISTRIBUTED TABLE RANGE SCAN|t1(idx_id_uid_lid_uid_ca_rls_global,Reverse)|1 |4 | | | |3 | └─DISTRIBUTED TABLE RANGE SCAN|t2 |1 |279 | | | ======================================================================================================== | | Outputs & filters: | | ------------------------------------- | | 0 - output([t1.id], [t1.created_at], [t1.rls], [t2.uid]), filter(nil), rowset=16 | | limit(21), offset(nil) | | 1 - output([t1.id], [t1.created_at], [t1.rls], [t2.uid]), filter(nil), rowset=16 | | conds(nil), nl_params_([t1.user_id(:0)]), use_batch=false | | 2 - output([t1.id], [t1.user_id], [t1.created_at], [t1.rls]), filter([t1.label_id = 1]), rowset=16 | | access([t1.id], [t1.user_id], [t1.label_id], [t1.created_at], [t1.rls]), partitions(p0) | | is_index_back=false, is_global_index=true, filter_before_indexback[false], | | range_key([t1.id], [t1.user_id], [t1.label_id], [t1.created_at], [t1.rls]), range(NULL,MAX,MAX,MAX,MAX ; 9223372036854775807,MAX,1,MAX,MAX), | | range_cond([t1.id <= 9223372036854775807]) | | 3 - output([t2.uid]), filter(nil), rowset=16 | | access([t2.uid]), partitions(p[0-15]) | | is_index_back=false, is_global_index=false, | | range_key([t2.id], [t2.uid]), range(MIN ; MAX), | | range_cond([:0 = t2.id]) | +-----------------------------------------------------------------------------------------------------------------------------------------------------+ 24 rows in set (0.094 sec) ``` ## 问题的风险及影响 order by limit 分区表优场景 SQL 执行执行慢。 ## 影响租户 影响 OceanBase 数据库中的 Oracle 租户和 MySQL 租户，对于 SYS 租户无影响。 ## 适用版本 OceanBase 数据库 V4.x 版本。 ## 解决方法及规避方式 order by limit 分区表优场景，ORDER BY `t1`.`id` DESC LIMIT 让 t1 表当做 nl join 的左表，需要 t1 表加全局索引保证 order by 继承左表的序。 上一篇 [创建和已有内部表同名的临时表，会导致 schema 刷新异常](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000002396681) 下一篇 [Truncate Table 的性能问题](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000514283) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线