作者简介：张霁：数据库架构师。
查看 OceanBase 执行计划
SQL 审计设置
查看 SQL 审计开关
[admin@ocp ~]$ mysql -h10.144.2.106 -ubenchmark@tpcc -P2883 -pbenchmark -c -A oceanbase ERROR 1045 (42000): Access denied for user 'benchmark'@'xxx.xxx.xxx.xxx' (using password: YES) [admin@ocp ~]$ mysql -h10.144.2.106 -ubenchmarksql@tpcc -P2883 -pbenchmarksql -c -A oceanbase Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 35 Server version: 5.6.25 OceanBase 3.1.2 (r10000392021123010-d4ace121deae5b81d8f0b40afbc4c02705b7fc1d) (Built Dec 30 2021 02:47:29) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MySQL [oceanbase]> show variables like 'ob_enable_sql_audit'; +---------------------+-------+ | Variable_name | Value | +---------------------+-------+ | ob_enable_sql_audit | ON | +---------------------+-------+ 1 row in set (0.05 sec)
开启SQL审计
在租户里开启或关闭 SQL 审计功能:
MySQL [oceanbase]> set global ob_enable_sql_audit = on; Query OK, 0 rows affected (0.01 sec)
执行低压力Benchmark测试
测试方案
- 使用 OBD 部署OceanBase 数据库集群。TPC-C 单独部署在一台机器上, 作为客户端的压力机器。
- OceanBase 集群规模为 2：2：2。部署成功后，新建执行 TPC-C 测试的租户及用户：租户tpcc，用户benchmarksql。将租户的 primary_zone 设置为 RANDOM。RANDOM 表示新建表分区的 Leader 随机到这 6 台机器。
测试规格
warehouses=2000 loadWorkers=100 terminals=20 runMins=10 newOrderWeight=45 paymentWeight=43 orderStatusWeight=4 deliveryWeight=4 stockLevelWeight=4
安装 Benchmark SQL
按照以下步骤安装 Benchmark SQL：
下载 Benchmark SQL。
本次使用的是开源社区进行适配了mysql的benchmarksql。所以不包含修改源代码适配过程。
解压 Benchmark SQL。
unzip ./benchmarksql-5.0.zip
适配 OceanBase
这里测试实验的是已经适配了 mysql 数据库的 benchmarksql.
修改建表语句
修改benchmarksql/run/sql.mysql/tableCreates.sql
create table bmsql_config ( cfg_name varchar(30) primary key, cfg_value varchar(50) ); -- drop tablegroup tpcc_group; create tablegroup tpcc_group partition by hash partitions 128; create table bmsql_warehouse ( w_id integer not null, w_ytd decimal(12,2), w_tax decimal(4,4), w_name varchar(10), w_street_1 varchar(20), w_street_2 varchar(20), w_city varchar(20), w_state char(2), w_zip char(9), primary key(w_id) )tablegroup='tpcc_group' partition by hash(w_id) partitions 128; create table bmsql_district ( d_w_id integer not null, d_id integer not null, d_ytd decimal(12,2), d_tax decimal(4,4), d_next_o_id integer, d_name varchar(10), d_street_1 varchar(20), d_street_2 varchar(20), d_city varchar(20), d_state char(2), d_zip char(9), PRIMARY KEY (d_w_id, d_id) )tablegroup='tpcc_group' partition by hash(d_w_id) partitions 128; create table bmsql_customer ( c_w_id integer not null, c_d_id integer not null, c_id integer not null, c_discount decimal(4,4), c_credit char(2), c_last varchar(16), c_first varchar(16), c_credit_lim decimal(12,2), c_balance decimal(12,2), c_ytd_payment decimal(12,2), c_payment_cnt integer, c_delivery_cnt integer, c_street_1 varchar(20), c_street_2 varchar(20), c_city varchar(20), c_state char(2), c_zip char(9), c_phone char(16), c_since timestamp, c_middle char(2), c_data varchar(500), PRIMARY KEY (c_w_id, c_d_id, c_id) )tablegroup='tpcc_group' partition by hash(c_w_id) partitions 128; create table bmsql_history ( hist_id integer, h_c_id integer, h_c_d_id integer, h_c_w_id integer, h_d_id integer, h_w_id integer, h_date timestamp, h_amount decimal(6,2), h_data varchar(24) )tablegroup='tpcc_group' partition by hash(h_w_id) partitions 128; create table bmsql_new_order ( no_w_id integer not null , no_d_id integer not null, no_o_id integer not null, PRIMARY KEY (no_w_id, no_d_id, no_o_id) )tablegroup='tpcc_group' partition by hash(no_w_id) partitions 128; create table bmsql_oorder ( o_w_id integer not null, o_d_id integer not null, o_id integer not null, o_c_id integer, o_carrier_id integer, o_ol_cnt integer, o_all_local integer, o_entry_d timestamp, PRIMARY KEY (o_w_id, o_d_id, o_id) )tablegroup='tpcc_group' partition by hash(o_w_id) partitions 128; create table bmsql_order_line ( ol_w_id integer not null, ol_d_id integer not null, ol_o_id integer not null, ol_number integer not null, ol_i_id integer not null, ol_delivery_d timestamp, ol_amount decimal(6,2), ol_supply_w_id integer, ol_quantity integer, ol_dist_info char(24), PRIMARY KEY (ol_w_id, ol_d_id, ol_o_id, ol_number) )tablegroup='tpcc_group' partition by hash(ol_w_id) partitions 128; create table bmsql_item ( i_id integer not null, i_name varchar(24), i_price decimal(5,2), i_data varchar(50), i_im_id integer, PRIMARY KEY (i_id) ) duplicate_scope='cluster'; create table bmsql_stock ( s_w_id integer not null, s_i_id integer not null, s_quantity integer, s_ytd integer, s_order_cnt integer, s_remote_cnt integer, s_data varchar(50), s_dist_01 char(24), s_dist_02 char(24), s_dist_03 char(24), s_dist_04 char(24), s_dist_05 char(24), s_dist_06 char(24), s_dist_07 char(24), s_dist_08 char(24), s_dist_09 char(24), s_dist_10 char(24), PRIMARY KEY (s_w_id, s_i_id) )tablegroup='tpcc_group' use_bloom_filter=true partition by hash(s_w_id) partitions 128;
修改索引创建语句
修改 benchmarksql/run/sql.mysql/indexCreates.sql
create index bmsql_customer_idx1 on bmsql_customer (c_w_id, c_d_id, c_last, c_first) local; create index bmsql_oorder_idx1 on bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id) local;
修改删除语句
修改benchmarksql/run/sql.mysql/tableDrops.sql drop table bmsql_config; drop table bmsql_new_order; drop table bmsql_order_line; drop table bmsql_oorder; drop table bmsql_history; drop table bmsql_customer; drop table bmsql_stock; drop table bmsql_item; drop table bmsql_district; drop table bmsql_warehouse; purge recyclebin; -- tpcc_group drop tablegroup tpcc_group
导数执行
运行以下命令，初始化环境：
benchmarksql/run 目录下执行
./runDatabaseDestroy.sh prop.ob
运行以下命令，创建表并导入数据：
benchmarksql/run 目录下执行
./runDatabaseBuild.sh prop.ob
TPC-C 测试执行
在 benchmarksql/run 目录下执行。
按照以下命令进行 TPC-C 测试：
./runBenchmark.sh prop.ob
执行结果
[root@ocp run]# ./runBenchmark.sh props.ob 05:29:15,617 [main] INFO jTPCC : Term-00, 05:29:15,619 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+ 05:29:15,619 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0 05:29:15,619 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+ 05:29:15,619 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa 05:29:15,620 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier 05:29:15,621 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck 05:29:15,621 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+ 05:29:15,621 [main] INFO jTPCC : Term-00, 05:29:15,621 [main] INFO jTPCC : Term-00, db=mysql 05:29:15,622 [main] INFO jTPCC : Term-00, driver=com.mysql.jdbc.Driver 05:29:15,622 [main] INFO jTPCC : Term-00, conn=jdbc:mysql:loadbalance://10.144.2.106:2883,10.144.2.107:2883,10.144.2.108:2883,10.144.2.109:2883,10.144.2.110:2883,10.144.2.111:2883/benchmark?rewriteBatchedStatements=true&allowMultiQueries=true&useLocalSessionState=true&useUnicode=true&characterEncoding=utf-8&socketTimeout=3000000&useCursorFetch=true&cachePrepStmts=true&prepStmtCacheSqlLimit=10240&prepStmtCacheSize=100000&useServerPrepStmts=true 05:29:15,622 [main] INFO jTPCC : Term-00, user=benchmarksql@tpcc 05:29:15,622 [main] INFO jTPCC : Term-00, 05:29:15,622 [main] INFO jTPCC : Term-00, warehouses=2000 05:29:15,622 [main] INFO jTPCC : Term-00, terminals=5 05:29:15,623 [main] INFO jTPCC : Term-00, runMins=10 05:29:15,623 [main] INFO jTPCC : Term-00, limitTxnsPerMin=0 05:29:15,623 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true 05:29:15,624 [main] INFO jTPCC : Term-00, 05:29:15,624 [main] INFO jTPCC : Term-00, newOrderWeight=45 05:29:15,624 [main] INFO jTPCC : Term-00, paymentWeight=43 05:29:15,624 [main] INFO jTPCC : Term-00, orderStatusWeight=4 05:29:15,624 [main] INFO jTPCC : Term-00, deliveryWeight=4 05:29:15,624 [main] INFO jTPCC : Term-00, stockLevelWeight=4 05:29:15,624 [main] INFO jTPCC : Term-00, 05:29:15,624 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS 05:29:15,624 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py 05:29:15,624 [main] INFO jTPCC : Term-00, 05:29:15,649 [main] INFO jTPCC : Term-00, copied props.ob to my_result_2022-02-25_052915/run.properties 05:29:15,649 [main] INFO jTPCC : Term-00, created my_result_2022-02-25_052915/data/runInfo.csv for runID 1587 05:29:15,649 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2022-02-25_052915/data/result.csv 05:29:15,650 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py 05:29:15,650 [main] INFO jTPCC : Term-00, osCollectorInterval=1 05:29:15,650 [main] INFO jTPCC : Term-00, osCollectorSSHAddr=null 05:29:15,650 [main] INFO jTPCC : Term-00, osCollectorDevices=net_em1 blk_nvme0n1 05:29:15,703 [main] INFO jTPCC : Term-00, Traceback (most recent call last): File "<stdin>", line 299, in <module> File "<stdin>", line 90, in main File "<stdin>", line 269, in initNetDevice IOError: [Errno 2] No such file or directory: '/sys/class/net/em1/statistics/rx_packets' 05:29:15,756 [Thread-0] ERROR OSCollector$CollectData : OSCollector, unexpected EOF while reading from external helper process 05:29:16,198 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 208 05:29:16,198 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 118 05:29:16,198 [main] INFO jTPCC : Term-00, Term-00, Running Average tpmTOTAL: 32033.04 Current tpmTOTAL: 2109240 Memory Usage: 112MB / 1442MB 05:39:16,861 [Thread-2] INFO jTPCC : Term-00, 05:39:16,862 [Thread-2] INFO jTPCC : Term-00, 05:39:16,862 [Thread-2] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 14479.55 05:39:16,862 [Thread-2] INFO jTPCC : Term-00, Measured tpmTOTAL = 32030.29 05:39:16,862 [Thread-2] INFO jTPCC : Term-00, Session Start = 2022-02-25 05:29:16 05:39:16,862 [Thread-2] INFO jTPCC : Term-00, Session End = 2022-02-25 05:39:16 05:39:16,862 [Thread-2] INFO jTPCC : Term-00, Transaction Count = 320334
分析 SQL
分析 TPC-C TOP SQL，并查看3条 SQL 的解析执行计划和实际执行计划
查看TOP 10的 SQL
MySQL [oceanbase]> SELECT/*+ PARALLEL(15)*/ SQL_ID, COUNT(*) AS QPS, AVG(t1.elapsed_time) RT -> FROM oceanbase.gv$sql_audit t1 -> WHERE tenant_id = 1001 -> GROUP BY t1.sql_id ORDER BY RT DESC LIMIT 10; +----------------------------------+------+--------------+ | SQL_ID | QPS | RT | +----------------------------------+------+--------------+ | 1C8BA8E2A6D2F4900B237CDB9B276371 | 1 | 1778149.0000 | | 7DC10E3547C663FE0E9350A0DD23C052 | 1 | 1583977.0000 | | 234A5447A85B4DB0110569C117986CB0 | 1 | 1541224.0000 | | 59E2137ECD5DDEE462DED984FEDDA585 | 1 | 1487768.0000 | | 283AC0A3A8A43DD1A2ECFBE07E4A99BC | 1 | 1474334.0000 | | C3AA55B8ACDED0282166B052AFD51F9C | 1 | 1458817.0000 | | D383E549D344CBC46BFF590690187D19 | 1 | 1455760.0000 | | 3F9993A8A1FC23C40B9980B45F713D42 | 1 | 1439289.0000 | | D5A377BF7288EBCF6DDC7988E77A4380 | 1 | 1438694.0000 | | D881F7AE87CD18A69A9329D6096C601B | 1 | 1428771.0000 | +----------------------------------+------+--------------+ 10 rows in set (0.85 sec)
查询 TOP SQL
MySQL [oceanbase]> SELECT sql_id, count(*), round(avg(elapsed_time)) avg_elapsed_time, round(avg(execute_time)) avg_exec_time FROM gv$sql_audit s WHERE 1=1 and user_name='benchmarksql' and request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 30 MINUTE) ) GROUP BY sql_id order by avg_elapsed_time desc limit 10; +----------------------------------+----------+------------------+---------------+ | sql_id | count(*) | avg_elapsed_time | avg_exec_time | +----------------------------------+----------+------------------+---------------+ | 3E76C52BE13F7D48388D2476D9596C7F | 1 | 843471 | 766414 | | E0D27337BEF665886EB694E0F68A6A4B | 1 | 43027 | 3116 | | 79C127C79275FCB830CB02CBCE28948F | 1 | 36203 | 3803 | | 98081DF291BAB86943C93DE34D37083D | 2 | 31827 | 6557 | | 9C20469C2AD80467EB8EBD79AFF2EE0F | 3 | 30049 | 15127 | | E3750B4E0E13AB3254D5D93E2DD6F6EA | 1 | 23293 | 3141 | | FC49A0648E312569A29359AC00C38B4E | 1 | 21869 | 3137 | | C9CECD6B36750DEEFFB586465A4A8B1C | 1 | 21641 | 2494 | | CFF3129A7C53F9DCA11DD32585BBDC27 | 1 | 21180 | 2489 | | 4E0FBC5C7AFD78A1F7FB8A1D9FBAADF5 | 2 | 20927 | 2931 | +----------------------------------+----------+------------------+---------------+
对 elapsed 时间最长的前10条 sql 进行分析
查询获取实际执行计划需要的信息
MySQL [oceanbase]> SELECT sql_id, count(*), round(avg(elapsed_time)) avg_elapsed_time, round(avg(execute_time)) avg_exec_time, s.svr_ip, s.svr_port, s.tenant_id, s.plan_id FROM gv$sql_audit s WHERE 1=1 and request_time >= time_to_usec(DATE_SUB(current_timestamp, INTERVAL 30 MINUTE) ) GROUP BY sql_id order by avg_elapsed_time desc limit 10; +----------------------------------+----------+------------------+---------------+--------------+----------+-----------+---------+ | sql_id | count(*) | avg_elapsed_time | avg_exec_time | svr_ip | svr_port | tenant_id | plan_id | +----------------------------------+----------+------------------+---------------+--------------+----------+-----------+---------+ | 5E8C83BFB1A6FF16645493E272039D84 | 1 | 1114249 | 1103850 | 10.144.2.107 | 2882 | 1001 | 94 | | BBD45D535E7E601AB57DF2F8FA752692 | 1 | 915440 | 907599 | 10.144.2.107 | 2882 | 1001 | 95 | | 3E76C52BE13F7D48388D2476D9596C7F | 1 | 843471 | 766414 | 10.144.2.107 | 2882 | 1001 | 90 | | DE87906DB54423565A43CEAA58618D72 | 1 | 344768 | 336313 | 10.144.2.107 | 2882 | 1001 | 96 | | E0D27337BEF665886EB694E0F68A6A4B | 1 | 43027 | 3116 | 10.144.2.107 | 2882 | 1001 | 64 | | 79C127C79275FCB830CB02CBCE28948F | 1 | 36203 | 3803 | 10.144.2.110 | 2882 | 1001 | 13565 | | 98081DF291BAB86943C93DE34D37083D | 2 | 31827 | 6557 | 10.144.2.109 | 2882 | 1001 | 81 | | 9C20469C2AD80467EB8EBD79AFF2EE0F | 3 | 30049 | 15127 | 10.144.2.107 | 2882 | 1001 | 61 | | E3750B4E0E13AB3254D5D93E2DD6F6EA | 1 | 23293 | 3141 | 10.144.2.109 | 2882 | 1001 | 89 | | FC49A0648E312569A29359AC00C38B4E | 1 | 21869 | 3137 | 10.144.2.107 | 2882 | 1001 | 73 | +----------------------------------+----------+------------------+---------------+--------------+----------+-----------+---------+ 10 rows in set (0.98 sec)
查看执行计划
获取第一条 sql 的文本
前4条都是查询审计日志的 SQL 忽略。从第5条开始，查询看到 plan_id 是94，sql_id 是E0D27337BEF665886EB694E0F68A6A4B
MySQL [oceanbase]> select distinct query_sql from gv$sql_audit where sql_id='E0D27337BEF665886EB694E0F68A6A4B' \G; *************************** 1. row *************************** query_sql: SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 202 AND s_quantity < 19 AND s_i_id IN ( SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = 202 AND d_id = 8 ) ) tmp 1 row in set (0.18 sec)
查看实际执行计划
运行下面 sql 查询这条语句的实际执行计划
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 = '10.144.2.107' AND port=2882 AND plan_id=94; +--------------+------------+--------------+------------------------------+-------------------------+------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ip | plan_depth | plan_line_id | operator | name | rows | cost | property | +--------------+------------+--------------+------------------------------+-------------------------+------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 10.144.2.107 | 0 | 0 | PHY_LIMIT | NULL | 10 | 2422 | NULL | | 10.144.2.107 | 1 | 1 | PHY_PX_MERGE_SORT_COORD | NULL | 10 | 2421 | NULL | | 10.144.2.107 | 2 | 2 | PHY_PX_REDUCE_TRANSMIT | NULL | 10 | 2410 | NULL | | 10.144.2.107 | 3 | 3 | PHY_LIMIT | NULL | 10 | 2410 | NULL | | 10.144.2.107 | 4 | 4 | PHY_SORT | NULL | 10 | 2409 | NULL | | 10.144.2.107 | 5 | 5 | PHY_HASH_GROUP_BY | NULL | 64 | 2212 | NULL | | 10.144.2.107 | 6 | 6 | PHY_PX_FIFO_RECEIVE | NULL | 100 | 2107 | NULL | | 10.144.2.107 | 7 | 7 | PHY_PX_DIST_TRANSMIT | NULL | 100 | 2000 | NULL | | 10.144.2.107 | 8 | 8 | PHY_GRANULE_ITERATOR | NULL | 100 | 2000 | NULL | | 10.144.2.107 | 9 | 9 | PHY_TABLE_SCAN | __all_virtual_sql_audit | 100 | 2000 | table_rows:600000, physical_range_rows:100, logical_range_rows:100, index_back_rows:0, output_rows:100, est_method:basic_stat, avaiable_index_name[__all_virtual_sql_audit], pruned_index_name[all_virtual_sql_audit_i1] | +--------------+------------+--------------+------------------------------+-------------------------+------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 10 rows in set (0.03 sec)
分析执行计划
MySQL [benchmark]> explain SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 202 AND s_quantity < 19 AND s_i_id IN ( SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = 202 AND d_id = 8 ) ) tmp; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ============================================================ |ID|OPERATOR |NAME |EST. ROWS|COST | ------------------------------------------------------------ |0 |EXCHANGE IN REMOTE | |1 |70176| |1 | EXCHANGE OUT REMOTE | |1 |70176| |2 | SCALAR GROUP BY | |1 |70176| |3 | NESTED-LOOP JOIN | |2531 |69693| |4 | SUBPLAN SCAN |VIEW2 |1804 |4202 | |5 | HASH DISTINCT | |1804 |3953 | |6 | NESTED-LOOP JOIN| |1822 |2064 | |7 | TABLE GET |bmsql_district |1 |53 | |8 | TABLE SCAN |bmsql_order_line|5465 |2498 | |9 | TABLE GET |bmsql_stock |1 |37 | ============================================================ Outputs & filters: ------------------------------------- 0 - output([T_FUN_COUNT(*)]), filter(nil) 1 - output([T_FUN_COUNT(*)]), filter(nil) 2 - output([T_FUN_COUNT(*)]), filter(nil), group(nil), agg_func([T_FUN_COUNT(*)]) 3 - output([1]), filter(nil), conds(nil), nl_params_([VIEW2.VIEW1.ol_i_id]) 4 - output([VIEW2.VIEW1.ol_i_id]), filter(nil), access([VIEW2.VIEW1.ol_i_id]) 5 - output([bmsql_order_line.ol_i_id]), filter(nil), distinct([bmsql_order_line.ol_i_id]) 6 - output([bmsql_order_line.ol_i_id]), filter(nil), conds(nil), nl_params_([bmsql_district.d_next_o_id - 20], [bmsql_district.d_next_o_id]) 7 - output([bmsql_district.d_next_o_id]), filter([bmsql_district.d_next_o_id > bmsql_district.d_next_o_id - 20]), access([bmsql_district.d_next_o_id]), partitions(p74) 8 - output([bmsql_order_line.ol_i_id]), filter(nil), access([bmsql_order_line.ol_i_id]), partitions(p74) 9 - output([1]), filter([bmsql_stock.s_quantity < 19]), access([bmsql_stock.s_quantity]), partitions(p74) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.04 sec)
获取第二条 sql 的文本
前4条都是查询审计日志的 SQL 忽略。从第7条开始，查询看到 plan_id 是61，sql_id 是9C20469C2AD80467EB8EBD79AFF2EE0F
MySQL [oceanbase]> select distinct query_sql from gv$sql_audit where sql_id='98081DF291BAB86943C93DE34D37083D ' \G; *************************** 1. row *************************** query_sql: SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 818 AND s_quantity < 16 AND s_i_id IN ( SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = 818 AND d_id = 5 ) ) tmp 1 row in set (0.45 sec)
查看实际执行计划
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 = '10.144.2.107' AND port=2882 AND plan_id=61; +--------------+------------+--------------+----------------------------+------------------+------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ip | plan_depth | plan_line_id | operator | name | rows | cost | property | +--------------+------------+--------------+----------------------------+------------------+------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 10.144.2.107 | 0 | 0 | PHY_DIRECT_RECEIVE | NULL | 1 | 42834 | NULL | | 10.144.2.107 | 1 | 1 | PHY_DIRECT_TRANSMIT | NULL | 1 | 42833 | NULL | | 10.144.2.107 | 2 | 2 | PHY_SCALAR_AGGREGATE | NULL | 1 | 42833 | NULL | | 10.144.2.107 | 3 | 3 | PHY_NESTED_LOOP_JOIN | NULL | 1630 | 42522 | NULL | | 10.144.2.107 | 4 | 4 | PHY_SUBPLAN_SCAN | NULL | 1104 | 2449 | NULL | | 10.144.2.107 | 5 | 5 | PHY_HASH_DISTINCT | NULL | 1104 | 2297 | NULL | | 10.144.2.107 | 6 | 6 | PHY_NESTED_LOOP_JOIN | NULL | 1112 | 1142 | NULL | | 10.144.2.107 | 7 | 7 | PHY_TABLE_SCAN | bmsql_district | 1 | 52 | table_rows:160, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:0, est_method:local_storage, avaiable_index_name[bmsql_district] | | 10.144.2.107 | 7 | 8 | PHY_TABLE_SCAN | bmsql_order_line | 3334 | 1113 | table_rows:5578531, physical_range_rows:3334, logical_range_rows:3333, index_back_rows:0, output_rows:3333, est_method:local_storage, avaiable_index_name[bmsql_order_line], estimation info[table_id:1100611139453897, (table_type:1, version:0-1645725610481717-1645725610481717, logical_rc:29989, physical_rc:29989), (table_type:7, version:1645725600395743-1645725610481717-1645725630203780, logical_rc:0, physical_rc:0), (table_type:5, version:1645725600395743-1645725610481717-1645725630203780, logical_rc:0, physical_rc:0), (table_type:0, version:1645725630203780-1645725630203780-9223372036854775807, logical_rc:9, physical_rc:17)] | | 10.144.2.107 | 4 | 9 | PHY_TABLE_SCAN | bmsql_stock | 1 | 36 | table_rows:1600000, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:0, est_method:local_storage, avaiable_index_name[bmsql_stock], estimation info[table_id:1100611139453899, (table_type:1, version:0-1645725610481717-1645725610481717, logical_rc:1600000, physical_rc:1600000), (table_type:7, version:1645725600395743-1645725610481717-1645725630002982, logical_rc:0, physical_rc:0), (table_type:5, version:1645725600395743-1645725610481717-1645725630002982, logical_rc:0, physical_rc:0), (table_type:0, version:1645725630002982-1645725630002982-9223372036854775807, logical_rc:0, physical_rc:153)] | +--------------+------------+--------------+----------------------------+------------------+------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 10 rows in set (0.00 sec)
分析执行计划
MySQL [benchmark]> explain SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 818 AND s_quantity < 16 AND s_i_id IN ( SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = 818 AND d_id = 5 ) ) tmp; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ========================================================== |ID|OPERATOR |NAME |EST. ROWS|COST | ---------------------------------------------------------- |0 |SCALAR GROUP BY | |1 |77893| |1 | NESTED-LOOP JOIN | |2519 |77412| |2 | SUBPLAN SCAN |VIEW2 |2005 |4640 | |3 | HASH DISTINCT | |2005 |4363 | |4 | NESTED-LOOP JOIN| |2030 |2262 | |5 | TABLE GET |bmsql_district |1 |53 | |6 | TABLE SCAN |bmsql_order_line|6088 |2688 | |7 | TABLE GET |bmsql_stock |1 |37 | ========================================================== Outputs & filters: ------------------------------------- 0 - output([T_FUN_COUNT(*)]), filter(nil), group(nil), agg_func([T_FUN_COUNT(*)]) 1 - output([1]), filter(nil), conds(nil), nl_params_([VIEW2.VIEW1.ol_i_id]) 2 - output([VIEW2.VIEW1.ol_i_id]), filter(nil), access([VIEW2.VIEW1.ol_i_id]) 3 - output([bmsql_order_line.ol_i_id]), filter(nil), distinct([bmsql_order_line.ol_i_id]) 4 - output([bmsql_order_line.ol_i_id]), filter(nil), conds(nil), nl_params_([bmsql_district.d_next_o_id - 20], [bmsql_district.d_next_o_id]) 5 - output([bmsql_district.d_next_o_id]), filter([bmsql_district.d_next_o_id > bmsql_district.d_next_o_id - 20]), access([bmsql_district.d_next_o_id]), partitions(p50) 6 - output([bmsql_order_line.ol_i_id]), filter(nil), access([bmsql_order_line.ol_i_id]), partitions(p50) 7 - output([1]), filter([bmsql_stock.s_quantity < 16]), access([bmsql_stock.s_quantity]), partitions(p50) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.02 sec)
获取第三条SQL文本
前4条都是查询审计日志的SQL忽略。第10条，查询看到plan_id是73，sql_id是 FC49A0648E312569A29359AC00C38B4E
MySQL [oceanbase]> select distinct query_sql from gv$sql_audit where sql_id='FC49A0648E312569A29359AC00C38B4E ' \G; *************************** 1. row *************************** query_sql: SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 818 AND s_quantity < 18 AND s_i_id IN ( SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = 818 AND d_id = 5 ) ) tmp 1 row in set (0.37 sec)
分析实际执行计划
MySQL [benchmark]> explain SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 818 AND s_quantity < 18 AND s_i_id IN ( SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = 818 AND d_id = 5 ) ) tmp; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Query Plan | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ============================================================ |ID|OPERATOR |NAME |EST. ROWS|COST | ------------------------------------------------------------ |0 |EXCHANGE IN REMOTE | |1 |77951| |1 | EXCHANGE OUT REMOTE | |1 |77951| |2 | SCALAR GROUP BY | |1 |77951| |3 | NESTED-LOOP JOIN | |2686 |77438| |4 | SUBPLAN SCAN |VIEW2 |2005 |4640 | |5 | HASH DISTINCT | |2005 |4363 | |6 | NESTED-LOOP JOIN| |2030 |2262 | |7 | TABLE GET |bmsql_district |1 |53 | |8 | TABLE SCAN |bmsql_order_line|6088 |2688 | |9 | TABLE GET |bmsql_stock |1 |37 | ============================================================ Outputs & filters: ------------------------------------- 0 - output([T_FUN_COUNT(*)]), filter(nil) 1 - output([T_FUN_COUNT(*)]), filter(nil) 2 - output([T_FUN_COUNT(*)]), filter(nil), group(nil), agg_func([T_FUN_COUNT(*)]) 3 - output([1]), filter(nil), conds(nil), nl_params_([VIEW2.VIEW1.ol_i_id]) 4 - output([VIEW2.VIEW1.ol_i_id]), filter(nil), access([VIEW2.VIEW1.ol_i_id]) 5 - output([bmsql_order_line.ol_i_id]), filter(nil), distinct([bmsql_order_line.ol_i_id]) 6 - output([bmsql_order_line.ol_i_id]), filter(nil), conds(nil), nl_params_([bmsql_district.d_next_o_id - 20], [bmsql_district.d_next_o_id]) 7 - output([bmsql_district.d_next_o_id]), filter([bmsql_district.d_next_o_id > bmsql_district.d_next_o_id - 20]), access([bmsql_district.d_next_o_id]), partitions(p50) 8 - output([bmsql_order_line.ol_i_id]), filter(nil), access([bmsql_order_line.ol_i_id]), partitions(p50) 9 - output([1]), filter([bmsql_stock.s_quantity < 18]), access([bmsql_stock.s_quantity]), partitions(p50) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.02 sec)
————————————————
附录：
练习题：
实践练习一（必选）：OceanBase Docker 体验
实践练习二（必选）：手动部署 OceanBase 集群
实践练习三（可选）：使用OBD 部署一个 三副本OceanBase 集群
实践练习四（必选）：迁移 MySQL 数据到 OceanBase 集群
实践练习五（可选）：对 OceanBase 做性能测试
实践练习六（必选）：查看 OceanBase 执行计划
还没交作业的小伙伴要抓紧啦！
可以免费带走 OBCP 考试券喔~~
方法一：完成四道必选练习
方法二：任意一道练习题 ➕ 结业考试超过80分
已经有很多同学抢先答题了，
加入钉钉群（群号3582 5151），和大家一起学习、交流～～
进群二维码：
