划线反馈

分区表场景下 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 和计划如下。

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;

执行计划输出结果如下。

========================================================================================
|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 分区表优场景。

策略就是消除 top-n 算子，走上流式计划，例如 ORDER BY t1.id DESC LIMIT 让 t1 表当做 nl join 的左表能够利用 t1 表的序消除掉 order by 继承左表的序。
因为是左表是分区表，需要加全局索引保证 order by 继承左表的序。
遇到分区表随机路由的问题，用全局索引表路由需要开启 proxy 参数。
```
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)。

===============================================================================================
|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 刷新异常

Truncate Table 的性能问题

OceanBase 数据库