很多时候我们会面临一个这样的问题：有个简单的 SQL，执行计划看起来已经没有优化空间了，但执行速度达不到预期，比在原来的数据库上慢了几倍（比如这是一个从 Oracle 迁移过来的数据库），这个速度是不是慢？
也许可以用一个简单的结论来概括：Oracle 单机的查询性能比 OceanBase 好是预期内的，OB 性能强在分布式高并发场景的读写。但剖开这句话其内里是什么呢？下面将通过一个案例来分享如何判断 SQL 是不是慢。
问题描述
有一个SQL简化后如下，在OB上执行耗时5.2秒，在Oracle上执行只要1秒：
select
count (*)
from
(
select
*
from
"LIFE"."T_LI_COUT_ACTIVITY" "A"
where
("A"."CMP_STATUS" = '06')
and ("A"."SYS_TYPE" = '2')
) A,
"LIFE"."T_LI_COUT_GRP_ACT_NUM" "B"
where
("A"."USER_GRP_ID" = "B"."GROUP_ID")
and ("A"."CMP_TYPE_CD" = "B"."CMP_TYPE_CD")
and ("B"."ENABLE_FLG" = '1');
执行计划解读如下：
- B hash join A
- B表只有336行。A表9000万行，走索引匹配90万行，然后回表过滤后输出 45万行。
从执行计划看，由于驱动表很小，hash join 的代价很低，耗时可以忽略。另外这个架构指定了 Primary Zone，所有 leader副本都在一个节点上，不存在跨节点查询的问题。因此可以初步判断耗时全部是访问 A 表导致的，访问 A 表走了索引，耗时这么长是否符合预期？下面就通过测试来进行验证。
=================================================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
---------------------------------------------------------------------------------
|0 |SCALAR GROUP BY | |1 |3927255|
|1 | HASH JOIN | |70659 |3924559|
|2 | TABLE SCAN |B |168 |132 |
|3 | TABLE LOOKUP |A |450822 |3605421|
|4 | DISTRIBUTED TABLE SCAN|A(IDX_LI_COUT_ACTIVIT_RESE_TEST)|901644 |501040 |
=================================================================================
...
Optimization Info:
-------------------------------------
B:table_rows:336, physical_range_rows:336, logical_range_rows:336, index_back_rows:0, output_rows:168, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[T_LI_COUT_GRP_ACT_NUM], ...
A:table_rows:89156473, physical_range_rows:901644, logical_range_rows:901644, index_back_rows:901644, output_rows:450822, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[IDX_LI_COUT_ACTIVIT_RESE_TEST,T_LI_COUT_ACTIVITY_TEST], ...
测试过程
1. 测试环境
Oracle、OB 都是高配物理机，NVME SSD存储，可以看作硬件配置一样，OB 的架构 leader 副本都集中在一个节点，无远程调用问题，并且测试时都无压力。
2. 测试SQL
--全局索引（一共113个分区）
CREATE INDEX "LIFE"."IDX_LI_COUT_ACTIVIT_RESE_TEST" on "LIFE"."T_LI_COUT_ACTIVITY_TEST" (
"CMP_STATUS",
"RESERVE_BEGIN_DTTM",
"RESERVE_END_DTTM"
) GLOBAL ;
--索引查找SQL
select
count(*)
from
"LIFE"."T_LI_COUT_ACTIVITY_TEST" "A"
where
("A"."CMP_STATUS" = '06');
--回表SQL
select
count(SYS_TYPE)
from
"LIFE"."T_LI_COUT_ACTIVITY_TEST" "A"
where
("A"."CMP_STATUS" = '06');
3. 执行计划解读
主要是介绍如何从执行计划中查看读取的行数、返回的行数、回表的行数，如果知道可以跳过。
- 全局索引索引覆盖的执行计划
A:table_rows:89156473, physical_range_rows:909837, logical_range_rows:909837, index_back_rows:0, output_rows:909837 从9000万行的表里，索引范围扫描 91万行数据，不需要回表
==============================================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
------------------------------------------------------------------------------
|0 |SCALAR GROUP BY | |1 |386655|
|1 | DISTRIBUTED TABLE SCAN|A(IDX_LI_COUT_ACTIVIT_RESE_TEST)|909837 |351930|
==============================================================================
Outputs & filters:
-------------------------------------
0 - output([T_FUN_COUNT(*)(0x7f2e5bf5adc0)]), filter(nil),
group(nil), agg_func([T_FUN_COUNT(*)(0x7f2e5bf5adc0)])
1 - output([remove_const(1)(0x7f2e5bfee3d0)]), filter(nil),
access([A.CMP_STATUS(0x7f2e5bf5a9c0)]), partitions(p0),
is_index_back=false,
range_key([A.CMP_STATUS(0x7f2e5bf5a9c0)], [A.RESERVE_BEGIN_DTTM(0x7f2e5bfd58e0)], [A.RESERVE_END_DTTM(0x7f2e5bfd5bd0)], [A.CRT_DTTM(0x7f2e5bf599f0)], [A.__pk_increment(0x7f2e5bfd3d40)]), range(06,MIN,MIN,MIN,MIN ; 06,MAX,MAX,MAX,MAX),
range_cond([A.CMP_STATUS(0x7f2e5bf5a9c0) = ?(0x7f2e5bf5a2a0)])
Used Hint:
-------------------------------------
/*+
*/
Outline Data:
-------------------------------------
/*+
BEGIN_OUTLINE_DATA
INDEX(@"SEL$1" "LIFE.A"@"SEL$1" "IDX_LI_COUT_ACTIVIT_RESE_TEST")
END_OUTLINE_DATA
*/
Plan Type:
-------------------------------------
LOCAL
Optimization Info:
-------------------------------------
A:table_rows:89156473, physical_range_rows:909837, logical_range_rows:909837, index_back_rows:0, output_rows:909837, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[IDX_LI_COUT_ACTIVIT_RESE_TEST], pruned_index_name[PK_T_LI_COUT_ACTIVITY_TEST,T_LI_COUT_ACTIVITY_TEST],...
- 全局索引回表的执行计划
A:table_rows:89156473, physical_range_rows:909837, logical_range_rows:909837, index_back_rows:909837, output_rows:909837 从9000万行的表里，索引范围扫描 91万行数据，回表了 91万行，输出 91 万行。
================================================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
--------------------------------------------------------------------------------
|0 |SCALAR GROUP BY | |1 |3667405|
|1 | TABLE LOOKUP |A |909837 |3632680|
|2 | DISTRIBUTED TABLE SCAN|A(IDX_LI_COUT_ACTIVIT_RESE_TEST)|909837 |505592 |
================================================================================
Outputs & filters:
-------------------------------------
0 - output([T_FUN_COUNT(A.SYS_TYPE(0x7f25f615b730))(0x7f25f615b020)]), filter(nil),
group(nil), agg_func([T_FUN_COUNT(A.SYS_TYPE(0x7f25f615b730))(0x7f25f615b020)])
1 - output([A.SYS_TYPE(0x7f25f615b730)]), filter(nil),
partitions(p[0-112])
2 - output([A.CRT_DTTM(0x7f25f6159c50)], [A.__pk_increment(0x7f25f61d8700)]), filter(nil),
access([A.CRT_DTTM(0x7f25f6159c50)], [A.__pk_increment(0x7f25f61d8700)]), partitions(p0),
is_index_back=false,
range_key([A.CMP_STATUS(0x7f25f615ac20)], [A.RESERVE_BEGIN_DTTM(0x7f25f61da2a0)], [A.RESERVE_END_DTTM(0x7f25f61da590)], [A.CRT_DTTM(0x7f25f6159c50)], [A.__pk_increment(0x7f25f61d8700)]), range(06,MIN,MIN,MIN,MIN ; 06,MAX,MAX,MAX,MAX),
range_cond([A.CMP_STATUS(0x7f25f615ac20) = ?(0x7f25f615a500)])
Used Hint:
-------------------------------------
/*+
*/
Outline Data:
-------------------------------------
/*+
BEGIN_OUTLINE_DATA
INDEX(@"SEL$1" "LIFE.A"@"SEL$1" "IDX_LI_COUT_ACTIVIT_RESE_TEST")
END_OUTLINE_DATA
*/
Plan Type:
-------------------------------------
LOCAL
Optimization Info:
-------------------------------------
A:table_rows:89156473, physical_range_rows:909837, logical_range_rows:909837, index_back_rows:909837, output_rows:909837, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[IDX_LI_COUT_ACTIVIT_RESE_TEST,T_LI_COUT_ACTIVITY_TEST], pruned_index_name[PK_T_LI_COUT_ACTIVITY_TEST], ...
4. OB测试结果
测试结果：
- 不回表耗时 567ms，这个速度是快的
- 回表后耗时4800ms，比索引范围查找速度慢了8倍
##不回表的 gv$ob_sql_audit 结果，耗时 567 ms
USEC_TO_TIME(REQUEST_TIME): 2023-08-02 13:07:33.480293
SVR_IP: 25.160.151.197
TRACE_ID: YB4219A097C5-0006013C66D7EB1B-0-0
QUERY_SQL: select /*+ test */
count(*)
from
"LIFE"."T_LI_COUT_ACTIVITY_TEST" "A"
where
("A"."CMP_STATUS" = '06');
ELAPSED_TIME: 567807
EXECUTE_TIME: 567730
EVENT: sync rpc
WAIT_CLASS: NETWORK
STATE: WAITED KNOWN TIME
WAIT_TIME_MICRO: 57456
TOTAL_WAIT_TIME_MICRO: 57862
TOTAL_WAITS: 3
RPC_COUNT: 4
MEMSTORE_READ_ROW_COUNT: 0
SSSTORE_READ_ROW_COUNT: 0
REQUEST_MEMORY_USED: 2096128
##回表执行耗时 4.8秒
*************************** 1. row ***************************
USEC_TO_TIME(REQUEST_TIME): 2023-08-02 11:37:01.937757
SVR_IP: 25.160.151.197
TRACE_ID: YB4219A097C5-0006013C6607DA30-0-0
QUERY_SQL: select count(SYS_TYPE)
from
"LIFE"."T_LI_COUT_ACTIVITY_TEST" "A"
where
("A"."CMP_STATUS" = '06');
ELAPSED_TIME: 4818285
EXECUTE_TIME: 4813526
EVENT: sync rpc
WAIT_CLASS: NETWORK
STATE: WAITED KNOWN TIME
WAIT_TIME_MICRO: 46016
TOTAL_WAIT_TIME_MICRO: 3191633
TOTAL_WAITS: 775
RPC_COUNT: 776
MEMSTORE_READ_ROW_COUNT: 0
SSSTORE_READ_ROW_COUNT: 0
REQUEST_MEMORY_USED: 2096128
1 row in set (6.399 sec)
5. Oracle-测试结果
结果：索引覆盖耗时 130毫秒，回表后耗时 900毫秒。
2023-08-02/14:00:29 2023-08-02 14:00:30 LIFE 60wybz006g4b8 0
select count(SYS_TYPE) from "LIFE"."T_LI_COUT_ACTIVITY" "A" where ("A"."CMP_STATUS" = '06')
899695
2023-08-02/14:00:16 2023-08-02 14:02:07 LIFE fkzhud4kfnm05 0
select count(*) from "LIFE"."T_LI_COUT_ACTIVITY" "A" where ("A"."CMP_STATUS" = '06')
133648
6. 结果统计
总结
没有开并行执行的情况下，Oracle 索引范围扫描速度比 OB 快 5-6 倍；如果只看回表的效率，Oracle 和 OB一样都比索引扫描慢 8 倍左右。这就是 Oracle 单机场景查询更快的内在原因，这是底层存储架构决定的，使用 LSM tree 在享受高性能写入的同时查询链路也变长了，当然OB也一直在进行优化，比如 4.3 版本推出的列存功能，范围扫描速度提升非常大。
其实如果不需要回表的话这个 SQL 只要 600 毫秒，即使比 Oracle慢一些还是可以接受的。一旦回表，耗时就增加到 4.8 秒了，这个执行效率在 OB 上是正常的，优化的方向是结合业务看是否能让 SQL 走所有覆盖不进行回表。
