环境准备
-
OceanBase三副本集群：手动部署OB集群
-
BenchmarkSQL
- 下载BenchmarkSQL，下载地址为：
https://github.com/obpilot/benchmarksql-5.0 - 下载后解压缩即可使用：
[root@localhost software]# unzip benchmarksql-5.0-master.zip [root@localhost software]# ls benchmarksql-5.0-master benchmarksql-5.0-master.zip 注： 需要有JAVA运行环境，版本不低于v1.8.0 [root@localhost software]# java -version openjdk version "1.8.0_262" OpenJDK Runtime Environment (build 1.8.0_262-b10) OpenJDK 64-Bit Server VM (build 25.262-b10, mixed mode)
BenchmarkSQL配置
创建测试用户和数据库
连接到obmysql租户，创建测试数据库
[root@localhost ~]# obclient -h192.168.66.4 -uroot@obmysql#obdemo -P2883 -prootPWD123 -C -A oceanbase MySQL [oceanbase]> create database tpcc; Query OK, 1 row affected (0.085 sec) MySQL [oceanbase]> create user tpcc identified by 'tpcc@0215'; Query OK, 0 rows affected (0.216 sec) MySQL [oceanbase]> grant all on *.* to tpcc; Query OK, 0 rows affected (0.094 sec)
配置文件
[root@localhost run]# vi props.ob db=oracle driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver conn=jdbc:oceanbase://127.1:2883/tpcc?useUnicode=true&characterEncoding=utf-8 user=tpcc@obmysql#obdemo password=tpcc@0215 warehouses=5 loadWorkers=5 terminals=50 //To run specified transactions per terminal- runMins must equal zero runTxnsPerTerminal=0 //To run for specified minutes- runTxnsPerTerminal must equal zero runMins=10 //Number of total transactions per minute limitTxnsPerMin=0 //Set to true to run in 4.x compatible mode. Set to false to use the //entire configured database evenly. terminalWarehouseFixed=true //The following five values must add up to 100 newOrderWeight=45 paymentWeight=43 orderStatusWeight=4 deliveryWeight=4 stockLevelWeight=4 // Directory name to create for collecting detailed result data. // Comment this out to suppress. resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS osCollectorScript=./misc/os_collector_linux.py osCollectorInterval=1 注： db指定数据库类型，这里复用Oracle的类型，OceanBase提供自己的驱动文件oceanbase-client-x.x.x.jar，无论是测试MySQL租户还是Oracle租户，都可以使用这个驱动文件， 后面把OceanBase数据库相应的LIBRARY放到对应目录下（lib/）即可。
数据准备
建表
建表脚本通常放在run/sql.common下，运行以下命令建表： [root@localhost run]# sh runSQL.sh props.ob sql.common/tableCreates.sql [root@localhost run]# obclient -h192.168.66.4 -uroot@obmysql#obdemo -P2883 -prootPWD123 -C -A tpcc MySQL [tpcc]> show tables; +------------------+ | Tables_in_tpcc | +------------------+ | bmsql_config | | bmsql_customer | | bmsql_district | | bmsql_history | | bmsql_item | | bmsql_new_order | | bmsql_oorder | | bmsql_order_line | | bmsql_stock | | bmsql_warehouse | +------------------+ 10 rows in set (0.023 sec)
加载数据
***加载数据*** [root@localhost run]# sh runLoader.sh props.ob [root@localhost run]# sh runLoader.sh props.ob Starting BenchmarkSQL LoadData driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver conn=jdbc:oceanbase://127.1:2883/tpcc?useUnicode=true&characterEncoding=utf-8 user=tpcc@obmysql#obdemo password=*********** warehouses=5 loadWorkers=5 fileLocation (not defined) csvNullValue (not defined - using default 'NULL') Worker 000: Loading ITEM Worker 001: Loading Warehouse 1 Worker 002: Loading Warehouse 2 Worker 003: Loading Warehouse 3 Worker 004: Loading Warehouse 4 Worker 000: Loading ITEM done Worker 000: Loading Warehouse 5 Worker 002: Loading Warehouse 2 done Worker 001: Loading Warehouse 1 done Worker 003: Loading Warehouse 3 done Worker 004: Loading Warehouse 4 done Worker 000: Loading Warehouse 5 done 注： 如果事务超时报错，需要调整事务超时参数。 ***检查数据*** MySQL [tpcc]> show tables; +------------------+ | Tables_in_tpcc | +------------------+ | bmsql_config | | bmsql_customer | | bmsql_district | | bmsql_history | | bmsql_item | | bmsql_new_order | | bmsql_oorder | | bmsql_order_line | | bmsql_stock | | bmsql_warehouse | +------------------+ 10 rows in set (0.006 sec) MySQL [tpcc]> select count(*) from bmsql_customer; +----------+ | count(*) | +----------+ | 150000 | +----------+ 1 row in set (2.582 sec)
创建索引
[root@localhost run]# sh runSQL.sh props.ob sql.oceanbase/indexCreates.sql
runSQL.sh: line 1: !/usr/bin/env: No such file or directory
# ------------------------------------------------------------
# Loading SQL file sql.oceanbase/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;
性能测试
集群合并
注：数据初始化确认没有错误，就可以开始跑性能测试，建议在跑性能测试之前先做一次集群合并，将当前大版本的SSTable和MemTable与前一个大版本的全量静态数据进行合并， 使存储层统计信息更准确，生成的执行计划更稳定。 [root@localhost ~]# obclient -h192.168.66.4 -uroot@sys#obdemo -P2883 -prootPWD123 -C -A MySQL [(none)]> use oceanbase; Database changed MySQL [oceanbase]> alter system major freeze; Query OK, 0 rows affected (0.017 sec)
运行测试脚本
[root@localhost run]# sh runSQL.sh props.ob sql.oceanbase/indexCreates.sql
runSQL.sh: line 1: !/usr/bin/env: No such file or directory
# ------------------------------------------------------------
# Loading SQL file sql.oceanbase/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;
[root@localhost run]# sh runBenchmark.sh props.ob
09:49:42,716 [main] INFO jTPCC : Term-00,
09:49:42,720 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
09:49:42,720 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0
09:49:42,720 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
09:49:42,720 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa
09:49:42,720 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier
09:49:42,724 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck
09:49:42,724 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
09:49:42,724 [main] INFO jTPCC : Term-00,
09:49:42,724 [main] INFO jTPCC : Term-00, db=oracle
09:49:42,724 [main] INFO jTPCC : Term-00, driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
09:49:42,725 [main] INFO jTPCC : Term-00, conn=jdbc:oceanbase://127.1:2883/tpcc?useUnicode=true&characterEncoding=utf-8
09:49:42,725 [main] INFO jTPCC : Term-00, user=tpcc@obmysql#obdemo
09:49:42,725 [main] INFO jTPCC : Term-00,
09:49:42,725 [main] INFO jTPCC : Term-00, warehouses=5
09:49:42,725 [main] INFO jTPCC : Term-00, terminals=50
09:49:42,727 [main] INFO jTPCC : Term-00, runMins=10
09:49:42,728 [main] INFO jTPCC : Term-00, limitTxnsPerMin=0
09:49:42,728 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true
09:49:42,728 [main] INFO jTPCC : Term-00,
09:49:42,728 [main] INFO jTPCC : Term-00, newOrderWeight=45
09:49:42,728 [main] INFO jTPCC : Term-00, paymentWeight=43
09:49:42,728 [main] INFO jTPCC : Term-00, orderStatusWeight=4
09:49:42,728 [main] INFO jTPCC : Term-00, deliveryWeight=4
09:49:42,729 [main] INFO jTPCC : Term-00, stockLevelWeight=4
09:49:42,729 [main] INFO jTPCC : Term-00,
09:49:42,729 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
09:49:42,729 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
09:49:42,729 [main] INFO jTPCC : Term-00,
09:49:42,749 [main] INFO jTPCC : Term-00, copied props.ob to my_result_2022-02-17_094942/run.properties
09:49:42,750 [main] INFO jTPCC : Term-00, created my_result_2022-02-17_094942/data/runInfo.csv for runID 10
09:49:42,750 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2022-02-17_094942/data/result.csv
09:49:42,752 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
09:49:42,752 [main] INFO jTPCC : Term-00, osCollectorInterval=1
09:49:42,752 [main] INFO jTPCC : Term-00, osCollectorSSHAddr=null
09:49:42,753 [main] INFO jTPCC : Term-00, osCollectorDevices=null
09:49:42,852 [main] INFO jTPCC : Term-00,
09:49:43,337 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 163
09:49:43,338 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 85
09:49:43,338 [main] INFO jTPCC : Term-00, Term-00, Running Average tpmTOTAL: 2.95 Current tpmTOTAL: 756 Memory Usage: 63Term-00,10:24:23,949 [Thread-34] INFO jTPCC : Term-00, AL: 768 Memory Usage: 63MB / 241MB
10:24:23,949 [Thread-34] INFO jTPCC : Term-00,
10:24:23,950 [Thread-34] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 1.09
10:24:23,950 [Thread-34] INFO jTPCC : Term-00, Measured tpmTOTAL = 2.91
10:24:23,950 [Thread-34] INFO jTPCC : Term-00, Session Start = 2022-02-17 09:49:44
10:24:23,950 [Thread-34] INFO jTPCC : Term-00, Session End = 2022-02-17 10:24:23
10:24:23,950 [Thread-34] INFO jTPCC : Term-00, Transaction Count = 100
[root@localhost run]#
问题解决
- 等待超时报错
加载数据之后运行性能测试脚本，报如下错误：
解决方法：调整事务超时参数，修改为36000000000
[root@localhost ~]# obclient -h127.1 -uroot@obmysql#obdemo -P2883 -prootPWD123 -C -A MySQL [(none)]> show variables like '%timeout%'; +---------------------+------------------+ | Variable_name | Value | +---------------------+------------------+ | connect_timeout | 10 | | interactive_timeout | 28800 | | lock_wait_timeout | 31536000 | | net_read_timeout | 30 | | net_write_timeout | 60 | | ob_pl_block_timeout | 3216672000000000 | | ob_query_timeout | 100000000 | | ob_trx_idle_timeout | 120000000 | | ob_trx_lock_timeout | -1 | | ob_trx_timeout | 10000000000 | | wait_timeout | 28800 | +---------------------+------------------+ 11 rows in set (0.323 sec) MySQL [(none)]> set global ob_query_timeout=36000000000; Query OK, 0 rows affected (0.888 sec) MySQL [(none)]> set global ob_trx_timeout=36000000000; Query OK, 0 rows affected (0.042 sec)
执行计划查看
查询平均执行时间的TOP SQL
MySQL [tpcc]> SELECT/*+ PARALLEL(15)*/avg_exe_usec, svr_ip, svr_port, sql_id, plan_id -> FROM oceanbase.gv$plan_cache_plan_stat -> WHERE tenant_id = 1001 -> ORDER BY avg_exe_usec DESC LIMIT 5; +--------------+--------------+----------+----------------------------------+---------+ | avg_exe_usec | svr_ip | svr_port | sql_id | plan_id | +--------------+--------------+----------+----------------------------------+---------+ | 393657797 | 192.168.66.3 | 2882 | FC3FED8CCB2946DE54F1C5BA3656023C | 280 | | 384623644 | 192.168.66.3 | 2882 | F4585305C4CB9B091C750826A7DEDD13 | 284 | | 286428861 | 192.168.66.3 | 2882 | F4585305C4CB9B091C750826A7DEDD13 | 285 | | 101018092 | 192.168.66.1 | 2882 | F59A700FA168324279B0DBC25E19760F | 187 | | 72276942 | 192.168.66.3 | 2882 | B447DE16B3F42D2409B2A2BE50328E63 | 307 | +--------------+--------------+----------+----------------------------------+---------+ 5 rows in set (0.025 sec)
根据sql_id获取完整sql文本
MySQL [oceanbase]> select query_sql from gv$plan_cache_plan_stat where sql_id='FC3FED8CCB2946DE54F1C5BA3656023C'; +----------------------------------------------------------------------------------------------------+ | query_sql | +----------------------------------------------------------------------------------------------------+ | SELECT d_tax, d_next_o_id FROM bmsql_district WHERE d_w_id = 5 AND d_id = 8 FOR UPDATE | +----------------------------------------------------------------------------------------------------+ 1 row in set (0.031 sec) MySQL [oceanbase]> select query_sql from gv$plan_cache_plan_stat where sql_id='F4585305C4CB9B091C750826A7DEDD13'; +-----------------------------------------------------------------------------------------+ | query_sql | +-----------------------------------------------------------------------------------------+ | UPDATE bmsql_district SET d_ytd = d_ytd + 2784.6 WHERE d_w_id = 3 AND d_id = 6 | | UPDATE bmsql_district SET d_ytd = d_ytd + 4169.17 WHERE d_w_id = 3 AND d_id = 1 | +-----------------------------------------------------------------------------------------+ 2 rows in set (0.025 sec) MySQL [oceanbase]> select query_sql from gv$plan_cache_plan_stat where sql_id='F59A700FA168324279B0DBC25E19760F'; +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | query_sql | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 5 AND s_quantity < 13 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 = 5 AND d_id = 10 ) ) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.019 sec)
查看SQL的解析执行计划以及实际执行计划
***解析执行计划*** MySQL [tpcc]> explain -> SELECT d_tax, d_next_o_id FROM bmsql_district WHERE d_w_id = 5 AND d_id = 8 FOR UPDATE \G *************************** 1. row *************************** Query Plan: ============================================= |ID|OPERATOR |NAME |EST. ROWS|COST| --------------------------------------------- |0 |TABLE SCAN|bmsql_district|2 |91 | ============================================= Outputs & filters: ------------------------------------- 0 - output([bmsql_district.d_tax], [bmsql_district.d_next_o_id]), filter([bmsql_district.d_id = 8], [bmsql_district.d_w_id = 5]), access([bmsql_district.__pk_increment], [bmsql_district.d_w_id], [bmsql_district.d_id], [bmsql_district.d_tax], [bmsql_district.d_next_o_id]), partitions(p0) 1 row in set (0.091 sec) ***实际执行计划*** MySQL [tpcc]> 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.66.3' AND port=2882 AND plan_id=280; +--------------+------------+--------------+----------------+----------------+------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | ip | plan_depth | plan_line_id | operator | name | rows | cost | property | +--------------+------------+--------------+----------------+----------------+------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 192.168.66.3 | 0 | 0 | PHY_TABLE_SCAN | bmsql_district | 2 | 64 | table_rows:50, physical_range_rows:50, logical_range_rows:50, index_back_rows:0, output_rows:1, est_method:local_storage, avaiable_index_name[bmsql_district], estimation info[table_id:1100611139453803, (table_type:1, version:0-1645034410667502-1645034410667502, logical_rc:50, physical_rc:50), (table_type:7, version:1645034400982889-1645034410667502-1645034430400112, logical_rc:0, physical_rc:0), (table_type:5, version:1645034400982889-1645034410667502-1645034430400112, logical_rc:0, physical_rc:0), (table_type:0, version:1645034430400112-1645034430400112-9223372036854775807, logical_rc:0, physical_rc:0)] | +--------------+------------+--------------+----------------+----------------+------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.233 sec)
***解析执行计划*** MySQL [tpcc]> explain -> UPDATE bmsql_district SET d_ytd = d_ytd + 2784.6 WHERE d_w_id = 3 AND d_id = 6 \G *************************** 1. row *************************** Query Plan: ============================================== |ID|OPERATOR |NAME |EST. ROWS|COST| ---------------------------------------------- |0 |UPDATE | |2 |107 | |1 | TABLE SCAN|bmsql_district|2 |106 | ============================================== Outputs & filters: ------------------------------------- 0 - output(nil), filter(nil), table_columns([{bmsql_district: ({bmsql_district: (bmsql_district.__pk_increment, bmsql_district.d_w_id, bmsql_district.d_id, bmsql_district.d_ytd, bmsql_district.d_tax, bmsql_district.d_next_o_id, bmsql_district.d_name, bmsql_district.d_street_1, bmsql_district.d_street_2, bmsql_district.d_city, bmsql_district.d_state, bmsql_district.d_zip)})}]), update([bmsql_district.d_ytd=column_conv(DECIMAL,PS:(12,2),NULL,bmsql_district.d_ytd + 2784.6)]) 1 - output([bmsql_district.__pk_increment], [bmsql_district.d_w_id], [bmsql_district.d_id], [bmsql_district.d_ytd], [bmsql_district.d_tax], [bmsql_district.d_next_o_id], [bmsql_district.d_name], [bmsql_district.d_street_1], [bmsql_district.d_street_2], [bmsql_district.d_city], [bmsql_district.d_state], [bmsql_district.d_zip], [column_conv(DECIMAL,PS:(12,2),NULL,bmsql_district.d_ytd + 2784.6)]), filter([bmsql_district.d_id = 6], [bmsql_district.d_w_id = 3]), access([bmsql_district.d_ytd], [bmsql_district.__pk_increment], [bmsql_district.d_w_id], [bmsql_district.d_id], [bmsql_district.d_tax], [bmsql_district.d_next_o_id], [bmsql_district.d_name], [bmsql_district.d_street_1], [bmsql_district.d_street_2], [bmsql_district.d_city], [bmsql_district.d_state], [bmsql_district.d_zip]), partitions(p0) 1 row in set (0.004 sec) ***实际执行计划*** MySQL [tpcc]> 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.66.3' AND port=2882 AND plan_id=284 \G *************************** 1. row *************************** ip: 192.168.66.3 plan_depth: 0 plan_line_id: 0 operator: PHY_UPDATE name: NULL rows: 2 cost: 73 property: NULL *************************** 2. row *************************** ip: 192.168.66.3 plan_depth: 1 plan_line_id: 1 operator: PHY_TABLE_SCAN name: bmsql_district rows: 2 cost: 72 property: table_rows:50, physical_range_rows:50, logical_range_rows:50, index_back_rows:0, output_rows:1, est_method:local_storage, avaiable_index_name[bmsql_district], estimation info[table_id:1100611139453803, (table_type:1, version:0-1645034410667502-1645034410667502, logical_rc:50, physical_rc:50), (table_type:7, version:1645034400982889-1645034410667502-1645034430400112, logical_rc:0, physical_rc:0), (table_type:5, version:1645034400982889-1645034410667502-1645034430400112, logical_rc:0, physical_rc:0), (table_type:0, version:1645034430400112-1645034430400112-9223372036854775807, logical_rc:0, physical_rc:0)] 2 rows in set (0.008 sec)
***解析执行计划*** MySQL [tpcc]> explain -> SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 5 AND s_quantity < 13 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 = 5 AND d_id = 10 ) ) \G *************************** 1. row *************************** Query Plan: ================================================================ |ID|OPERATOR |NAME |EST. ROWS|COST | ---------------------------------------------------------------- |0 |SCALAR GROUP BY | |1 |1583336| |1 | HASH RIGHT SEMI JOIN | |1132 |1583120| |2 | SUBPLAN SCAN |VIEW1 |1112 |1010963| |3 | NESTED-LOOP JOIN | |1112 |1010809| |4 | PX COORDINATOR | |30009 |1005433| |5 | EXCHANGE OUT DISTR |:EX10000 |30009 |999752 | |6 | TABLE SCAN |bmsql_order_line|30009 |999752 | |7 | MATERIAL | |1 |106 | |8 | PX COORDINATOR | |1 |106 | |9 | EXCHANGE OUT DISTR|:EX20000 |1 |105 | |10| TABLE SCAN |bmsql_district |1 |105 | |11| TABLE SCAN |bmsql_stock |4457 |568806 | ================================================================ Outputs & filters: ------------------------------------- 0 - output([T_FUN_COUNT(*)]), filter(nil), group(nil), agg_func([T_FUN_COUNT(*)]) 1 - output([1]), filter(nil), equal_conds([bmsql_stock.s_i_id = VIEW1.ol_i_id]), other_conds(nil) 2 - output([VIEW1.ol_i_id]), filter(nil), access([VIEW1.ol_i_id]) 3 - output([bmsql_order_line.ol_i_id]), filter(nil), conds([bmsql_order_line.ol_o_id >= bmsql_district.d_next_o_id - 20], [bmsql_order_line.ol_o_id < bmsql_district.d_next_o_id]), nl_params_(nil) 4 - output([bmsql_order_line.ol_o_id], [bmsql_order_line.ol_i_id]), filter(nil) 5 - output([bmsql_order_line.ol_o_id], [bmsql_order_line.ol_i_id]), filter(nil), is_single, dop=1 6 - output([bmsql_order_line.ol_o_id], [bmsql_order_line.ol_i_id]), filter([bmsql_order_line.ol_d_id = 10], [bmsql_order_line.ol_w_id = 5]), access([bmsql_order_line.ol_w_id], [bmsql_order_line.ol_d_id], [bmsql_order_line.ol_o_id], [bmsql_order_line.ol_i_id]), partitions(p0) 7 - output([bmsql_district.d_next_o_id]), filter(nil) 8 - output([bmsql_district.d_next_o_id]), filter(nil) 9 - output([bmsql_district.d_next_o_id]), filter(nil), is_single, dop=1 10 - output([bmsql_district.d_next_o_id]), filter([bmsql_district.d_id = 10], [bmsql_district.d_w_id = 5], [bmsql_district.d_next_o_id > bmsql_district.d_next_o_id - 20]), access([bmsql_district.d_w_id], [bmsql_district.d_id], [bmsql_district.d_next_o_id]), partitions(p0) 11 - output([bmsql_stock.s_i_id]), filter([bmsql_stock.s_quantity < 13], [bmsql_stock.s_w_id = 5]), access([bmsql_stock.s_w_id], [bmsql_stock.s_quantity], [bmsql_stock.s_i_id]), partitions(p0) 1 row in set (0.148 sec) ***实际执行计划*** MySQL [tpcc]> 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.66.1' AND port=2882 AND plan_id=187 \G *************************** 1. row *************************** ip: 192.168.66.1 plan_depth: 0 plan_line_id: 0 operator: PHY_SCALAR_AGGREGATE name: NULL rows: 1 cost: 1582231 property: NULL *************************** 2. row *************************** ip: 192.168.66.1 plan_depth: 1 plan_line_id: 1 operator: PHY_HASH_JOIN name: NULL rows: 1132 cost: 1582015 property: NULL *************************** 3. row *************************** ip: 192.168.66.1 plan_depth: 2 plan_line_id: 2 operator: PHY_SUBPLAN_SCAN name: NULL rows: 1112 cost: 1010332 property: NULL *************************** 4. row *************************** ip: 192.168.66.1 plan_depth: 3 plan_line_id: 3 operator: PHY_NESTED_LOOP_JOIN name: NULL rows: 1112 cost: 1010179 property: NULL *************************** 5. row *************************** ip: 192.168.66.1 plan_depth: 4 plan_line_id: 4 operator: PHY_PX_FIFO_COORD name: NULL rows: 30001 cost: 1004837 property: NULL *************************** 6. row *************************** ip: 192.168.66.1 plan_depth: 5 plan_line_id: 5 operator: PHY_PX_REDUCE_TRANSMIT name: NULL rows: 30001 cost: 999157 property: NULL *************************** 7. row *************************** ip: 192.168.66.1 plan_depth: 6 plan_line_id: 6 operator: PHY_TABLE_SCAN name: bmsql_order_line rows: 30001 cost: 999157 property: table_rows:1500025, physical_range_rows:1500025, logical_range_rows:1500025, index_back_rows:0, output_rows:30000, est_method:local_storage, avaiable_index_name[bmsql_order_line], estimation info[table_id:1100611139453808, (table_type:1, version:0-1645034410667502-1645034410667502, logical_rc:1500025, physical_rc:1500025), (table_type:7, version:1645034400736550-1645034410667502-1645034429952781, logical_rc:0, physical_rc:0), (table_type:5, version:1645034400736550-1645034410667502-1645034429952781, logical_rc:0, physical_rc:0), (table_type:0, version:1645034429952781-1645034429952781-9223372036854775807, logical_rc:0, physical_rc:0)] *************************** 8. row *************************** ip: 192.168.66.1 plan_depth: 4 plan_line_id: 7 operator: PHY_MATERIAL name: NULL rows: 1 cost: 72 property: NULL *************************** 9. row *************************** ip: 192.168.66.1 plan_depth: 5 plan_line_id: 8 operator: PHY_PX_FIFO_COORD name: NULL rows: 1 cost: 72 property: NULL *************************** 10. row *************************** ip: 192.168.66.1 plan_depth: 6 plan_line_id: 9 operator: PHY_PX_REDUCE_TRANSMIT name: NULL rows: 1 cost: 72 property: NULL *************************** 11. row *************************** ip: 192.168.66.1 plan_depth: 7 plan_line_id: 10 operator: PHY_TABLE_SCAN name: bmsql_district rows: 1 cost: 72 property: table_rows:50, physical_range_rows:50, logical_range_rows:50, index_back_rows:0, output_rows:0, est_method:local_storage, avaiable_index_name[bmsql_district], estimation info[table_id:1100611139453803, (table_type:1, version:0-1645034410667502-1645034410667502, logical_rc:50, physical_rc:50), (table_type:7, version:1645034400982889-1645034410667502-1645034430001343, logical_rc:0, physical_rc:0), (table_type:5, version:1645034400982889-1645034410667502-1645034430001343, logical_rc:0, physical_rc:0), (table_type:0, version:1645034430001343-1645034430001343-9223372036854775807, logical_rc:0, physical_rc:0)] *************************** 12. row *************************** ip: 192.168.66.1 plan_depth: 2 plan_line_id: 11 operator: PHY_TABLE_SCAN name: bmsql_stock rows: 4457 cost: 568331 property: table_rows:500000, physical_range_rows:500000, logical_range_rows:500000, index_back_rows:0, output_rows:4456, est_method:local_storage, avaiable_index_name[bmsql_stock], estimation info[table_id:1100611139453810, (table_type:1, version:0-1645034410667502-1645034410667502, logical_rc:500000, physical_rc:500000), (table_type:7, version:1645034400625120-1645034410667502-1645034429851469, logical_rc:0, physical_rc:0), (table_type:5, version:1645034400625120-1645034410667502-1645034429851469, logical_rc:0, physical_rc:0), (table_type:0, version:1645034429851469-1645034429851469-9223372036854775807, logical_rc:0, physical_rc:0)] 12 rows in set (0.040 sec)
最后修改时间：2022-02-17 16:17:05
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
