问题现象
两张分区表 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.idDESC 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 继承左表的序。
