一、前言
在我的上一篇文章：玩转oceanbase之OB集群与Mysql集群性能对比测试 里面，我对oceanbase集群和mysql集群的TPS和QPS进行了对比测试。
本文基于相同的环境，我再继续深入测试一下两种数据库在多表查询和DML更新操作上的差异，并分析产生的原因。
之前在使用sysbench时，脚本自动在sbtest库中生成了15张表（sbtest1~sbtest15），每张表有10万条数据。我编写了以下语句来测试多表查询和对表进行更新的执行能力：
mysql集群上执行：
SELECT s1.id,s2.c,s1.pad FROM sbtest1 s1,sbtest2 s2 WHERE s1.id=s2.id and s2.id in (select id from sbtest9 s9 where s9.k=s2.k) ORDER BY s2.c;
update sbtest1 s1 join sbtest2 s2 on s1.id=s2.id and s2.id in (select id from sbtest9 s9 where s9.k=s2.k) set s1.pad=s2.pad;
OB集群上执行：
SELECT s1.id,s2.c,s1.pad FROM sbtest1 s1,sbtest2 s2 WHERE s1.id=s2.id and s2.id in (select id from sbtest9 s9 where s9.k=s2.k) ORDER BY s2.c; --这些表的主副本在同一台observer上
SELECT s1.id,s2.c,s1.pad FROM sbtest3 s1,sbtest5 s2 WHERE s1.id=s2.id and s2.id in (select id from sbtest11 s9 where s9.k=s2.k) ORDER BY s2.c; --这些表的主副本位于不同observer上
update sbtest1 s1 join sbtest2 s2 on s1.id=s2.id and s2.id in (select id from sbtest9 s9 where s9.k=s2.k) set s1.pad=s2.pad;
update sbtest3 s3 join sbtest5 s5 on s3.id=s5.id and s5.id in (select id from sbtest11 s11 where s11.k=s5.k) set s3.pad=s5.pad;
二、实验测试
2.1 在mysql集群上执行测试
mysql> explain SELECT s1.id,s2.c,s1.pad FROM sbtest1 s1,sbtest2 s2 WHERE s1.id=s2.id and s2.id in (select id from sbtest9 s9 where s9.k=s2.k) ORDER BY s2.c;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+-------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+-------+----------+---------------------------------+
| 1 | SIMPLE | s1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 98794 | 100.00 | Using temporary; Using filesort |
| 1 | SIMPLE | s9 | NULL | eq_ref | PRIMARY,k_9 | PRIMARY | 4 | sbtest.s1.id | 1 | 100.00 | NULL |
| 1 | SIMPLE | s2 | NULL | eq_ref | PRIMARY,k_2 | PRIMARY | 4 | sbtest.s1.id | 1 | 5.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+-------+----------+---------------------------------+
在mysql集群中执行查询:
mysql> SELECT s1.id,s2.c,s1.pad FROM sbtest1 s1,sbtest2 s2 WHERE s1.id=s2.id and s2.id in (select id from sbtest9 s9 where s9.k=s2.k) ORDER BY s2.c;
......
75 rows in set (0.37 sec)
在mysql集群中执行更新：
mysql> explain update sbtest1 s1 join sbtest2 s2 on s1.id=s2.id and s2.id in (select id from sbtest9 s9 where s9.k=s2.k) set s1.pad=s2.pad;
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+-------+----------+-------------+
| 1 | UPDATE | s1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 98794 | 100.00 | NULL |
| 1 | SIMPLE | s9 | NULL | eq_ref | PRIMARY,k_9 | PRIMARY | 4 | sbtest.s1.id | 1 | 100.00 | NULL |
| 1 | SIMPLE | s2 | NULL | eq_ref | PRIMARY,k_2 | PRIMARY | 4 | sbtest.s1.id | 1 | 5.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+--------------+-------+----------+-------------+
mysql> update sbtest1 s1 join sbtest2 s2 on s1.id=s2.id and s2.id in (select id from sbtest9 s9 where s9.k=s2.k) set s1.pad=s2.pad;
......
Query OK, 75 rows affected (0.75 sec)
Rows matched: 75 Changed: 75 Warnings: 0
2.2 在OB集群上进行测试
查看表的主副本分布情况：
MySQL [oceanbase]> select c.tenant_name,
-> b.table_name,
-> a.zone,
-> case when a.role=1 then 'leader' when a.role=2 then 'follower' else null end role,
-> concat(a.svr_ip,':',a.svr_port) server
-> from __all_tenant_meta_table a,gv$table b,gv$tenant c
-> where a.tenant_id=b.tenant_id and a.table_id=b.table_id and a.tenant_id=c.tenant_id and a.role=1
-> order by server;
+-------------+------------+-------+--------+--------------------+
| tenant_name | table_name | zone | role | server |
+-------------+------------+-------+--------+--------------------+
| obmysql | sbtest8 | zone1 | leader | 192.168.18.28:2900 |
| obmysql | sbtest5 | zone1 | leader | 192.168.18.28:2900 |
| obmysql | sbtest15 | zone1 | leader | 192.168.18.28:2900 |
| obmysql | sbtest13 | zone1 | leader | 192.168.18.28:2900 |
| obmysql | sbtest14 | zone1 | leader | 192.168.18.28:2900 |
| obmysql | sbtest11 | zone2 | leader | 192.168.18.28:2901 |
| obmysql | sbtest10 | zone2 | leader | 192.168.18.28:2901 |
| obmysql | sbtest4 | zone2 | leader | 192.168.18.28:2901 |
| obmysql | sbtest12 | zone2 | leader | 192.168.18.28:2901 |
| obmysql | sbtest7 | zone2 | leader | 192.168.18.28:2901 |
| obmysql | sbtest2 | zone3 | leader | 192.168.18.28:2902 |
| obmysql | sbtest6 | zone3 | leader | 192.168.18.28:2902 |
| obmysql | sbtest9 | zone3 | leader | 192.168.18.28:2902 |
| obmysql | sbtest3 | zone3 | leader | 192.168.18.28:2902 |
| obmysql | sbtest1 | zone3 | leader | 192.168.18.28:2902 |
+-------------+------------+-------+--------+--------------------+
15 rows in set (0.034 sec)
先选取主副本分布在同一个zone上的表（sbtest1，sbtest2，sbtest9）做select查询：
MySQL [sbtest]> explain extended SELECT s1.id,s2.c,s1.pad FROM sbtest1 s1,sbtest2 s2 WHERE s1.id=s2.id and s2.id in (select id from sbtest9 s9 where s9.k=s2.k) ORDER BY s2.c;
| =========================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
-----------------------------------------
|0 |SORT | |98011 |1686785|
|1 | MERGE JOIN | |98011 |370298 |
|2 | MERGE JOIN | |98010 |221969 |
|3 | TABLE SCAN|s2 |100000 |65116 |
|4 | TABLE SCAN|s9 |100000 |62915 |
|5 | TABLE SCAN |s1 |100000 |62872 |
=========================================
Outputs & filters:
-------------------------------------
0 - output([s1.id(0x2b921074fa30)], [s2.c(0x2b92107b8f80)], [s1.pad(0x2b92107b94a0)]), filter(nil), sort_keys([s2.c(0x2b92107b8f80), ASC])
1 - output([s1.id(0x2b921074fa30)], [s2.c(0x2b92107b8f80)], [s1.pad(0x2b92107b94a0)]), filter(nil),
equal_conds([s1.id(0x2b921074fa30) = s2.id(0x2b921074fcc0)(0x2b921074f3b0)]), other_conds(nil),
merge_directions([ASC])
2 - output([s2.c(0x2b92107b8f80)], [s2.id(0x2b921074fcc0)]), filter(nil),
equal_conds([s2.id(0x2b921074fcc0) = s9.id(0x2b92107b8650)(0x2b92107bb260)], [s9.k(0x2b921077cea0) = s2.k(0x2b92107b8130)(0x2b92107baa60)]), other_conds(nil),
merge_directions([ASC], [ASC])
3 - output([s2.id(0x2b921074fcc0)], [s2.k(0x2b92107b8130)], [s2.c(0x2b92107b8f80)]), filter(nil),
access([s2.id(0x2b921074fcc0)], [s2.k(0x2b92107b8130)], [s2.c(0x2b92107b8f80)]), partitions(p0),
is_index_back=false,
range_key([s2.id(0x2b921074fcc0)]), range(MIN ; MAX)always true
4 - output([s9.k(0x2b921077cea0)], [s9.id(0x2b92107b8650)]), filter(nil),
access([s9.k(0x2b921077cea0)], [s9.id(0x2b92107b8650)]), partitions(p0),
is_index_back=false,
range_key([s9.id(0x2b92107b8650)]), range(MIN ; MAX)always true
5 - output([s1.id(0x2b921074fa30)], [s1.pad(0x2b92107b94a0)]), filter(nil),
access([s1.id(0x2b921074fa30)], [s1.pad(0x2b92107b94a0)]), partitions(p0),
is_index_back=false,
range_key([s1.id(0x2b921074fa30)]), range(MIN ; MAX)always true
OB中执行查询耗时：
MySQL [sbtest]> SELECT s1.id,s2.c,s1.pad FROM sbtest1 s1,sbtest2 s2 WHERE s1.id=s2.id and s2.id in (select id from sbtest9 s9 where s9.k=s2.k) ORDER BY s2.c;
......
71 rows in set (1.434 sec)
再选取主副本分布在不同zone上的表（sbtest3，sbtest5，sbtest11）做select查询：
MySQL [sbtest]> explain extended SELECT s1.id,s2.c,s1.pad FROM sbtest3 s1,sbtest5 s2 WHERE s1.id=s2.id and s2.id in (select id from sbtest11 s9 where s9.k=s2.k) ORDER BY s2.c;
| ======================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
------------------------------------------------------
|0 |SORT | |97956 |1775753|
|1 | MERGE JOIN | |97956 |460041 |
|2 | MERGE JOIN | |97956 |311798 |
|3 | PX COORDINATOR | |100000 |145569 |
|4 | EXCHANGE OUT DISTR|:EX10000|100000 |65108 |
|5 | TABLE SCAN |s2 |100000 |65108 |
|6 | PX COORDINATOR | |99944 |72336 |
|7 | EXCHANGE OUT DISTR|:EX20000|99944 |62875 |
|8 | TABLE SCAN |s9 |99944 |62875 |
|9 | TABLE SCAN |s1 |100000 |62826 |
======================================================
Outputs & filters:
-------------------------------------
0 - output([s1.id(0x2b921074fa30)], [s2.c(0x2b92107e3160)], [s1.pad(0x2b92107e3680)]), filter(nil), sort_keys([s2.c(0x2b92107e3160), ASC])
1 - output([s1.id(0x2b921074fa30)], [s2.c(0x2b92107e3160)], [s1.pad(0x2b92107e3680)]), filter(nil),
equal_conds([s1.id(0x2b921074fa30) = s2.id(0x2b921074fcc0)(0x2b921074f3b0)]), other_conds(nil),
merge_directions([ASC])
2 - output([s2.c(0x2b92107e3160)], [s2.id(0x2b921074fcc0)]), filter(nil),
equal_conds([s2.id(0x2b921074fcc0) = s9.id(0x2b92107e2830)(0x2b92108c2d40)], [s9.k(0x2b92107df880) = s2.k(0x2b92107e2310)(0x2b92108c2540)]), other_conds(nil),
merge_directions([ASC], [ASC])
3 - output([s2.id(0x2b921074fcc0)], [s2.k(0x2b92107e2310)], [s2.c(0x2b92107e3160)]), filter(nil)
4 - output([s2.id(0x2b921074fcc0)], [s2.k(0x2b92107e2310)], [s2.c(0x2b92107e3160)]), filter(nil), is_single, dop=1
5 - output([s2.id(0x2b921074fcc0)], [s2.k(0x2b92107e2310)], [s2.c(0x2b92107e3160)]), filter(nil),
access([s2.id(0x2b921074fcc0)], [s2.k(0x2b92107e2310)], [s2.c(0x2b92107e3160)]), partitions(p0),
is_index_back=false,
range_key([s2.id(0x2b921074fcc0)]), range(MIN ; MAX)always true
6 - output([s9.k(0x2b92107df880)], [s9.id(0x2b92107e2830)]), filter(nil)
7 - output([s9.k(0x2b92107df880)], [s9.id(0x2b92107e2830)]), filter(nil), is_single, dop=1
8 - output([s9.k(0x2b92107df880)], [s9.id(0x2b92107e2830)]), filter(nil),
access([s9.k(0x2b92107df880)], [s9.id(0x2b92107e2830)]), partitions(p0),
is_index_back=false,
range_key([s9.id(0x2b92107e2830)]), range(MIN ; MAX)always true
9 - output([s1.id(0x2b921074fa30)], [s1.pad(0x2b92107e3680)]), filter(nil),
access([s1.id(0x2b921074fa30)], [s1.pad(0x2b92107e3680)]), partitions(p0),
is_index_back=false,
range_key([s1.id(0x2b921074fa30)]), range(MIN ; MAX)always true
直接查询耗时：
MySQL [sbtest]> SELECT s1.id,s2.c,s1.pad FROM sbtest3 s1,sbtest5 s2 WHERE s1.id=s2.id and s2.id in (select id from sbtest11 s9 where s9.k=s2.k) ORDER BY s2.c;
......
57 rows in set (0.521 sec)
查看OB实际执行计划：
MySQL [oceanbase]> SELECT /*+ read_consistency(weak) ob_querytimeout(100000000) */ substr(usec_to_time(request_time),1,19) request_time_, s.svr_ip, s.svr_port,s.client_ip, s.sid,s.tenant_id, s.tenant_name, s.user_name, s.db_name, s.query_sql, s.plan_id, s.plan_type, s.affected_rows, s.return_rows, s.ret_code, s.event, s.elapsed_time, s.queue_time, s.execute_time
-> FROM oceanbase.gv$sql_audit s
-> WHERE query_sql like 'SELECT s1.id,s2.c,s1.pad%'
-> ORDER BY request_time DESC
-> LIMIT 2 \G
*************************** 1. row ***************************
request_time_: 2022-04-14 11:21:44
svr_ip: 192.168.18.28
svr_port: 2902
client_ip: 192.168.18.28
sid: 3222021342
tenant_id: 1001
tenant_name: obmysql
user_name: root
db_name: sbtest
query_sql: SELECT s1.id,s2.c,s1.pad FROM sbtest3 s1,sbtest5 s2 WHERE s1.id=s2.id and s2.id in (select id from sbtest11 s9 where s9.k=s2.k) ORDER BY s2.c
plan_id: 113
plan_type: 3
affected_rows: 0
return_rows: 57
ret_code: 0
event: db file data read
elapsed_time: 520392
queue_time: 72
execute_time: 510535
*************************** 2. row ***************************
request_time_: 2022-04-14 10:49:20
svr_ip: 192.168.18.28
svr_port: 2902
client_ip: 192.168.18.28
sid: 3222021342
tenant_id: 1001
tenant_name: obmysql
user_name: root
db_name: sbtest
query_sql: SELECT s1.id,s2.c,s1.pad FROM sbtest1 s1,sbtest2 s2 WHERE s1.id=s2.id and s2.id in (select id from sbtest9 s9 where s9.k=s2.k) ORDER BY s2.c
plan_id: 107
plan_type: 1
affected_rows: 0
return_rows: 71
ret_code: 0
event: db file data read
elapsed_time: 1415638
queue_time: 34
execute_time: 1405864
查看本地事务的执行计划：
MySQL [oceanbase]> SELECT ip, plan_depth, plan_line_id,operator,name,rows,cost,property
-> from oceanbase.`gv$plan_cache_plan_explain`
-> WHERE tenant_id=1001 AND ip = '192.168.18.28' AND port=2902 AND plan_id=107;
| ip | plan_depth | plan_line_id | operator | name | rows | cost | property
-----------------------------------------------------------------------------------------------------+
| 192.168.18.28 | 0 | 0 | PHY_SORT | NULL | 98011 | 1686784 | NULL |
| 192.168.18.28 | 1 | 1 | PHY_MERGE_JOIN | NULL | 98011 | 370297 | NULL |
| 192.168.18.28 | 2 | 2 | PHY_MERGE_JOIN | NULL | 98010 | 221968 | NULL
cost=1686784,与实际执行总时长相近。
查看分布式事务的执行计划：
MySQL [oceanbase]> SELECT ip, plan_depth, plan_line_id,operator,name,rows,cost,property
-> from oceanbase.`gv$plan_cache_plan_explain`
-> WHERE tenant_id=1001 AND ip = '192.168.18.28' AND port=2902 AND plan_id=113;
| ip | plan_depth | plan_line_id | operator | name | rows | cost | property
-------------------------------------------------------------------------------------------------------------+
| 192.168.18.28 | 0 | 0 | PHY_SORT | NULL | 97956 | 1775752 | NULL |
| 192.168.18.28 | 1 | 1 | PHY_MERGE_JOIN | NULL | 97956 | 460040 | NULL |
| 192.168.18.28 | 2 | 2 | PHY_MERGE_JOIN | NULL | 97956 | 311797 | NULL |
| 192.168.18.28 | 3 | 3 | PHY_PX_FIFO_COORD | NULL | 100000 | 145568 | NULL |
| 192.168.18.28 | 4 | 4 | PHY_PX_REDUCE_TRANSMIT | NULL | 100000 | 65107 | NULL
cost=1775752，实际执行总时长为0.521 sec，差距较大。
为什么会这样呢？为什么本地事务比分布式事务的用时还长呢？
下面，我再详细地查询并对比下这两条sql执行各阶段的用时，看下具体差异在哪：
MySQL [oceanbase]> select * from gv$sql_audit where TENANT_NAME='obmysql' and query_sql like 'SELECT s1.id,s2.c,s1.pad%' limit 2\G
*************************** 1. row ***************************
SVR_IP: 192.168.18.28
SVR_PORT: 2902
REQUEST_ID: 2
SQL_EXEC_ID: 817976
TRACE_ID: YB56C0A8121C-0005DC9433D69BE0
SID: 3222021342
CLIENT_IP: 192.168.18.28
CLIENT_PORT: 62196
TENANT_ID: 1001
TENANT_NAME: obmysql
EFFECTIVE_TENANT_ID: 1001
USER_ID: 1100611139403777
USER_NAME: root
USER_GROUP: 0
USER_CLIENT_IP: 192.168.18.28
DB_ID: 1100611139404827
DB_NAME: sbtest
SQL_ID: E4577EB2B21CB01AEE53C93A38EEFE3F
QUERY_SQL: SELECT s1.id,s2.c,s1.pad FROM sbtest1 s1,sbtest2 s2 WHERE s1.id=s2.id and s2.id in (select id from sbtest9 s9 where s9.k=s2.k) ORDER BY s2.c
PLAN_ID: 107
AFFECTED_ROWS: 0
RETURN_ROWS: 71
PARTITION_CNT: 3
RET_CODE: 0
QC_ID: 0
DFO_ID: 0
SQC_ID: 0
WORKER_ID: 0
EVENT: db file data read
P1TEXT: fd
P1: 86016
P2TEXT: offset
P2: 0
P3TEXT: size
P3: 0
LEVEL: 0
WAIT_CLASS_ID: 108
WAIT_CLASS#: 8
WAIT_CLASS: USER_IO
STATE: WAITED KNOWN TIME
WAIT_TIME_MICRO: 35947
TOTAL_WAIT_TIME_MICRO: 888192
TOTAL_WAITS: 126
RPC_COUNT: 1
PLAN_TYPE: 1
IS_INNER_SQL: 0
IS_EXECUTOR_RPC: 0
IS_HIT_PLAN: 0
REQUEST_TIME: 1649904560613658
ELAPSED_TIME: 1415638
NET_TIME: 0
NET_WAIT_TIME: 0
QUEUE_TIME: 34
DECODE_TIME: 1
GET_PLAN_TIME: 9700
EXECUTE_TIME: 1405864
APPLICATION_WAIT_TIME: 0
CONCURRENCY_WAIT_TIME: 0
USER_IO_WAIT_TIME: 888093
SCHEDULE_TIME: 0
ROW_CACHE_HIT: 0
BLOOM_FILTER_CACHE_HIT: 0
BLOCK_CACHE_HIT: 0
BLOCK_INDEX_CACHE_HIT: 0
DISK_READS: 617
RETRY_CNT: 0
TABLE_SCAN: 1
CONSISTENCY_LEVEL: 3
MEMSTORE_READ_ROW_COUNT: 4944
SSSTORE_READ_ROW_COUNT: 299403
REQUEST_MEMORY_USED: 65536
EXPECTED_WORKER_COUNT: 0
USED_WORKER_COUNT: 0
SCHED_INFO:
FUSE_ROW_CACHE_HIT: 0
PS_STMT_ID: 0
TRANSACTION_HASH: 0
REQUEST_TYPE: 2
IS_BATCHED_MULTI_STMT: 0
OB_TRACE_INFO: client_ip=192.168.18.28
PLAN_HASH: 16641128910069783824
LOCK_FOR_READ_TIME: 0
WAIT_TRX_MIGRATE_TIME: 0
*************************** 2. row ***************************
SVR_IP: 192.168.18.28
SVR_PORT: 2902
REQUEST_ID: 12
SQL_EXEC_ID: 853538
TRACE_ID: YB56C0A8121C-0005DC9433D69BEA
SID: 3222021342
CLIENT_IP: 192.168.18.28
CLIENT_PORT: 62196
TENANT_ID: 1001
TENANT_NAME: obmysql
EFFECTIVE_TENANT_ID: 1001
USER_ID: 1100611139403777
USER_NAME: root
USER_GROUP: 0
USER_CLIENT_IP: 192.168.18.28
DB_ID: 1100611139404827
DB_NAME: sbtest
SQL_ID: 09C4FA5F64938ADAD6C7E2F21430391B
QUERY_SQL: SELECT s1.id,s2.c,s1.pad FROM sbtest3 s1,sbtest5 s2 WHERE s1.id=s2.id and s2.id in (select id from sbtest11 s9 where s9.k=s2.k) ORDER BY s2.c
PLAN_ID: 113
AFFECTED_ROWS: 0
RETURN_ROWS: 57
PARTITION_CNT: 3
RET_CODE: 0
QC_ID: 0
DFO_ID: 0
SQC_ID: 0
WORKER_ID: 0
EVENT: db file data read
P1TEXT: fd
P1: 45056
P2TEXT: offset
P2: 0
P3TEXT: size
P3: 0
LEVEL: 0
WAIT_CLASS_ID: 108
WAIT_CLASS#: 8
WAIT_CLASS: USER_IO
STATE: WAITED KNOWN TIME
WAIT_TIME_MICRO: 13841
TOTAL_WAIT_TIME_MICRO: 239186
TOTAL_WAITS: 208
RPC_COUNT: 162
PLAN_TYPE: 3
IS_INNER_SQL: 0
IS_EXECUTOR_RPC: 0
IS_HIT_PLAN: 0
REQUEST_TIME: 1649906504210446
ELAPSED_TIME: 520392
NET_TIME: 0
NET_WAIT_TIME: 0
QUEUE_TIME: 72
DECODE_TIME: 1
GET_PLAN_TIME: 9735
EXECUTE_TIME: 510535
APPLICATION_WAIT_TIME: 0
CONCURRENCY_WAIT_TIME: 24537
USER_IO_WAIT_TIME: 214649
SCHEDULE_TIME: 0
ROW_CACHE_HIT: 0
BLOOM_FILTER_CACHE_HIT: 0
BLOCK_CACHE_HIT: 0
BLOCK_INDEX_CACHE_HIT: 0
DISK_READS: 202
RETRY_CNT: 0
TABLE_SCAN: 1
CONSISTENCY_LEVEL: 3
MEMSTORE_READ_ROW_COUNT: 1576
SSSTORE_READ_ROW_COUNT: 97551
REQUEST_MEMORY_USED: 65536
EXPECTED_WORKER_COUNT: 0
USED_WORKER_COUNT: 0
SCHED_INFO: J10000T00000:1649906504231524,472429,472438;J20000T00000:1649906504235680,434386,434396;
FUSE_ROW_CACHE_HIT: 0
PS_STMT_ID: 0
TRANSACTION_HASH: 0
REQUEST_TYPE: 2
IS_BATCHED_MULTI_STMT: 0
OB_TRACE_INFO: client_ip=192.168.18.28
PLAN_HASH: 1510720345016188034
LOCK_FOR_READ_TIME: 0
WAIT_TRX_MIGRATE_TIME: 0
2 rows in set (0.036 sec)
结论：主要原因还是在IO上面，分布式系统的优势也从这里体现了出来，在查询时，IO收益得到了成倍增强。
OB上测试本地事务更新：
MySQL [sbtest]> explain update sbtest1 s1 join sbtest2 s2 on s1.id=s2.id and s2.id in (select id from sbtest9 s9 where s9.k=s2.k) set s1.pad=s2.pad;
| ========================================
|ID|OPERATOR |NAME|EST. ROWS|COST |
----------------------------------------
|0 |UPDATE | |98011 |472792|
|1 | MERGE JOIN | |98011 |374782|
|2 | MERGE JOIN | |98010 |221969|
|3 | TABLE SCAN|s2 |100000 |65116 |
|4 | TABLE SCAN|s9 |100000 |62915 |
|5 | TABLE SCAN |s1 |100000 |67356 |
========================================
Outputs & filters:
-------------------------------------
0 - output(nil), filter(nil), table_columns([{sbtest1: ({sbtest1: (s1.id, s1.k, s1.c, s1.pad)})}]),
update([s1.pad=column_conv(CHAR,utf8mb4_general_ci,length:60,NOT NULL,cast(inner_trim(2, ?, cast(s2.pad, VARCHAR(1048576))), CHAR(1048576)))])
1 - output([s1.id], [s1.k], [s1.c], [s1.pad], [column_conv(CHAR,utf8mb4_general_ci,length:60,NOT NULL,cast(inner_trim(2, ?, cast(s2.pad, VARCHAR(1048576))), CHAR(1048576)))]), filter(nil),
equal_conds([s1.id = s2.id]), other_conds(nil)
2 - output([s2.pad], [s2.id]), filter(nil),
equal_conds([s2.id = s9.id], [s9.k = s2.k]), other_conds(nil)
3 - output([s2.id], [s2.k], [s2.pad]), filter(nil),
access([s2.id], [s2.k], [s2.pad]), partitions(p0)
4 - output([s9.k], [s9.id]), filter(nil),
access([s9.k], [s9.id]), partitions(p0)
5 - output([s1.id], [s1.pad], [s1.k], [s1.c]), filter(nil),
access([s1.id], [s1.pad], [s1.k], [s1.c]), partitions(p0)
实际执行：
MySQL [sbtest]> update sbtest1 s1 join sbtest2 s2 on s1.id=s2.id and s2.id in (select id from sbtest9 s9 where s9.k=s2.k) set s1.pad=s2.pad;
Query OK, 71 rows affected (0.552 sec)
OB上测试分布式事务更新：
MySQL [sbtest]> explain update sbtest3 s3 join sbtest5 s5 on s3.id=s5.id and s5.id in (select id from sbtest11 s11 where s11.k=s5.k) set s3.pad=s5.pad;
| =====================================================
|ID|OPERATOR |NAME |EST. ROWS|COST |
-----------------------------------------------------
|0 |UPDATE | |97956 |526980|
|1 | MERGE JOIN | |97956 |429024|
|2 | MERGE JOIN | |97956 |276300|
|3 | PX COORDINATOR | |100000 |110071|
|4 | EXCHANGE OUT DISTR|:EX10000|100000 |65108 |
|5 | TABLE SCAN |s5 |100000 |65108 |
|6 | PX COORDINATOR | |99944 |72336 |
|7 | EXCHANGE OUT DISTR|:EX20000|99944 |62875 |
|8 | TABLE SCAN |s11 |99944 |62875 |
|9 | TABLE SCAN |s3 |100000 |67307 |
=====================================================
Outputs & filters:
-------------------------------------
0 - output(nil), filter(nil), table_columns([{sbtest3: ({sbtest3: (s3.id, s3.k, s3.c, s3.pad)})}]),
update([s3.pad=column_conv(CHAR,utf8mb4_general_ci,length:60,NOT NULL,cast(inner_trim(2, ?, cast(s5.pad, VARCHAR(1048576))), CHAR(1048576)))])
1 - output([s3.id], [s3.k], [s3.c], [s3.pad], [column_conv(CHAR,utf8mb4_general_ci,length:60,NOT NULL,cast(inner_trim(2, ?, cast(s5.pad, VARCHAR(1048576))), CHAR(1048576)))]), filter(nil),
equal_conds([s3.id = s5.id]), other_conds(nil)
2 - output([s5.pad], [s5.id]), filter(nil),
equal_conds([s5.id = s11.id], [s11.k = s5.k]), other_conds(nil)
3 - output([s5.id], [s5.k], [s5.pad]), filter(nil)
4 - output([s5.id], [s5.k], [s5.pad]), filter(nil), is_single, dop=1
5 - output([s5.id], [s5.k], [s5.pad]), filter(nil),
access([s5.id], [s5.k], [s5.pad]), partitions(p0)
6 - output([s11.k], [s11.id]), filter(nil)
7 - output([s11.k], [s11.id]), filter(nil), is_single, dop=1
8 - output([s11.k], [s11.id]), filter(nil),
access([s11.k], [s11.id]), partitions(p0)
9 - output([s3.id], [s3.pad], [s3.k], [s3.c]), filter(nil),
access([s3.id], [s3.pad], [s3.k], [s3.c]), partitions(p0)
实际执行：
update sbtest3 s3 join sbtest5 s5 on s3.id=s5.id and s5.id in (select id from sbtest11 s11 where s11.k=s5.k) set s3.pad=s5.pad;
......
Query OK, 57 rows affected (0.529 sec)
结论：在我的实验环境中看起来这两个差距不大。按道理之前本地事务的查询用时较长，更新时应该用时更长才对。查看下原因:
MySQL [oceanbase]> select * from gv$sql_audit where TENANT_NAME='obmysql' and query_sql like 'update sbtest1 s1 join sbtest2%' limit 2\G
*************************** 1. row ***************************
SVR_IP: 192.168.18.28
SVR_PORT: 2902
REQUEST_ID: 34
SQL_EXEC_ID: 886275
TRACE_ID: YB56C0A8121C-0005DC9433D69BFB
SID: 3222034189
CLIENT_IP: 192.168.18.28
CLIENT_PORT: 38430
TENANT_ID: 1001
TENANT_NAME: obmysql
EFFECTIVE_TENANT_ID: 1001
USER_ID: 1100611139403777
USER_NAME: root
USER_GROUP: 0
USER_CLIENT_IP: 192.168.18.28
DB_ID: 1100611139404827
DB_NAME: sbtest
SQL_ID: 449E020FEE9CA3270E0E733439D777B0
QUERY_SQL: update sbtest1 s1 join sbtest2 s2 on s1.id=s2.id and s2.id in (select id from sbtest9 s9 where s9.k=s2.k) set s1.pad=s2.pad
PLAN_ID: 128
AFFECTED_ROWS: 71
RETURN_ROWS: 0
PARTITION_CNT: 3
RET_CODE: 0
QC_ID: 0
DFO_ID: 0
SQC_ID: 0
WORKER_ID: 0
EVENT: system internal wait
P1TEXT:
P1: 0
P2TEXT:
P2: 0
P3TEXT:
P3: 0
LEVEL: 0
WAIT_CLASS_ID: 100
WAIT_CLASS#: 0
WAIT_CLASS: OTHER
STATE: MAX_WAIT TIME ZERO
WAIT_TIME_MICRO: 0
TOTAL_WAIT_TIME_MICRO: 0
TOTAL_WAITS: 0
RPC_COUNT: 2
PLAN_TYPE: 1
IS_INNER_SQL: 0
IS_EXECUTOR_RPC: 0
IS_HIT_PLAN: 0
REQUEST_TIME: 1649912926736905
ELAPSED_TIME: 504239
NET_TIME: 0
NET_WAIT_TIME: 0
QUEUE_TIME: 40
DECODE_TIME: 1
GET_PLAN_TIME: 17293
EXECUTE_TIME: 486858
APPLICATION_WAIT_TIME: 0
CONCURRENCY_WAIT_TIME: 0
USER_IO_WAIT_TIME: 0
SCHEDULE_TIME: 0
ROW_CACHE_HIT: 0
BLOOM_FILTER_CACHE_HIT: 0
BLOCK_CACHE_HIT: 4223
BLOCK_INDEX_CACHE_HIT: 30
DISK_READS: 0
RETRY_CNT: 0
TABLE_SCAN: 1
CONSISTENCY_LEVEL: 3
MEMSTORE_READ_ROW_COUNT: 4944
SSSTORE_READ_ROW_COUNT: 299403
REQUEST_MEMORY_USED: 393216
EXPECTED_WORKER_COUNT: 0
USED_WORKER_COUNT: 0
SCHED_INFO:
FUSE_ROW_CACHE_HIT: 0
PS_STMT_ID: 0
TRANSACTION_HASH: 2006120236626507883
REQUEST_TYPE: 2
IS_BATCHED_MULTI_STMT: 0
OB_TRACE_INFO: client_ip=192.168.18.28
PLAN_HASH: 15223555702903365219
LOCK_FOR_READ_TIME: 0
WAIT_TRX_MIGRATE_TIME: 0
1 row in set (0.038 sec)
其中BLOCK_CACHE_HIT: 4223 说明有块缓存命中，次数为4223次，这大大减少了IO压力，因此速度得到了大幅度的提高。
补充测试：OB缓存命中后的本地事务查询
MySQL [sbtest]> SELECT s1.id,s2.c,s1.pad FROM sbtest3 s1,sbtest5 s2 WHERE s1.id=s2.id and s2.id in (select id from sbtest11 s9 where s9.k=s2.k) ORDER BY s2.c;
......
57 rows in set (0.282 sec)
三、总结：
通过在mysql集群和oceanbase集群中分别执行相同的DML语句，我得出的结论是：
1）在并发量很小时，mysql的查询速度优于oceanbase，mysql的更新速度低于oceanbase。
多表查询：mysql:0.37秒，OB最低0.52秒
更新：mysql:0.75秒, OB最低0.52秒
2）由于该实验的集群节点都是建在同一台主机上，主机的磁盘IO对测试影响很大。但虽然影响很大，仍然证明了oceanbase分布式架构对IO的提升是很明显的。
3）该实验在补充测试时利用到了OB的内存+SSL table特性，让本来需要执行1.434秒的查询降低至0.282秒，这在实际生产中的用处是很大的。
最后修改时间：2022-04-14 18:36:44
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
