目录
1 使用 BenmarkSQL 运行 TPC-C
1.1 下载BenmarkSQL
git clone https://github.com/obpilot/benchmarksql-5.0.git
1.2 编辑props.ob配置文件
这个配置文件在/root/benchmarksql-5.0/run/目录下，编辑后的内容如下：
db=oracle driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver conn=jdbc:oceanbase://127.1:2883/tpccdb?useUnicode=true&characterEncoding=utf-8 user=tpcc@my_tenant#obcluster password=123456 warehouses=2 loadWorkers=1 terminals=5 //To run specified transactions per terminal- runMins must equal zero runTxnsPerTerminal=0 //To run for specified minutes- runTxnsPerTerminal must equal zero runMins=5 //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 [root@localhost run]# pwd /root/benchmarksql-5.0/run
1.3 运行创建表语句
[root@localhost run]# sh runSQL.sh props.ob sql.common/tableCreates.sql
这里出了点小问题，这个脚本报找不到funcs.sh文件，应该是环境变量的问题，不过通过更改脚本中funcs.sh路径为绝对路径也可以解决这个问题：
编辑一下runSQL.sh文件，更改内容如下：
#!/usr/bin/env bash # ---- # Check command line usage # ---- if [ $# -ne 2 ] ; then echo "usage: $(basename $0) PROPS_FILE SQL_FILE" >&2 exit 2 fi # ---- # Load common functions # ---- source /root/benchmarksql-5.0/run/funcs.sh $1
再运行创建表的命令，表创建正常了，下面的脚本都需要做这样的更改
[root@localhost run]# sh runSQL.sh props.ob sql.common/tableCreates.sql # ------------------------------------------------------------ # Loading SQL file sql.common/tableCreates.sql # ------------------------------------------------------------ create table bmsql_config ( cfg_name varchar(30) primary key, cfg_value varchar(50) ); 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) --------- ---------
1.4 数据装载
装载前先优化一下测试租户的设置，避免大事务超时，先登陆到租户
obclient -h127.0.0.1 -P 2883 -u root@my_tenant -p -A -c
设置以下租户全局变量：
set global ob_timestamp_service='GTS' ; set global autocommit=ON; set global ob_query_timeout=36000000000; set global ob_trx_timeout=36000000000; set global max_allowed_packet=67108864; set global ob_sql_work_area_percentage=100; set global parallel_max_servers=800; set global parallel_servers_target=800;
运行脚本，装载数据，输出显示装载成功，由于是笔记本电脑，仓库数选的小一些
[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/tpccdb?useUnicode=true&characterEncoding=utf-8 user=tpcc@my_tenant#obcluster password=*********** warehouses=2 loadWorkers=1 fileLocation (not defined) csvNullValue (not defined - using default 'NULL') Worker 000: Loading ITEM Worker 000: Loading ITEM done Worker 000: Loading Warehouse 1 Worker 000: Loading Warehouse 1 done Worker 000: Loading Warehouse 2 Worker 000: Loading Warehouse 2 done
1.5 登陆到数据库，创建两个索引
MySQL [(none)]> use tpccdb Database changed MySQL [tpccdb]> create index bmsql_customer_idx1 -> on bmsql_customer (c_w_id, c_d_id, c_last, c_first) local; Query OK, 0 rows affected (1.506 sec) MySQL [tpccdb]> create index bmsql_oorder_idx1 -> on bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id) local; Query OK, 0 rows affected (1.181 sec)
1.6 运行TPCC测试
[root@localhost run]# sh runBenchmark.sh props.ob 20:20:46,632 [main] INFO jTPCC : Term-00, 20:20:46,636 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+ 20:20:46,636 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0 20:20:46,636 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+ 20:20:46,636 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa 20:20:46,637 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier 20:20:46,639 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck 20:20:46,639 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+ 20:20:46,639 [main] INFO jTPCC : Term-00, 20:20:46,639 [main] INFO jTPCC : Term-00, db=oracle 20:20:46,639 [main] INFO jTPCC : Term-00, driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver 20:20:46,640 [main] INFO jTPCC : Term-00, conn=jdbc:oceanbase://127.1:2883/tpccdb?useUnicode=true&characterEncoding=ut f-8 20:20:46,640 [main] INFO jTPCC : Term-00, user=tpcc@my_tenant#obcluster 20:20:46,640 [main] INFO jTPCC : Term-00, 20:20:46,640 [main] INFO jTPCC : Term-00, warehouses=2 20:20:46,640 [main] INFO jTPCC : Term-00, terminals=5 20:20:46,645 [main] INFO jTPCC : Term-00, runMins=5 20:20:46,645 [main] INFO jTPCC : Term-00, limitTxnsPerMin=0 20:20:46,645 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true 20:20:46,645 [main] INFO jTPCC : Term-00, 20:20:46,645 [main] INFO jTPCC : Term-00, newOrderWeight=45 20:20:46,645 [main] INFO jTPCC : Term-00, paymentWeight=43 20:20:46,645 [main] INFO jTPCC : Term-00, orderStatusWeight=4 20:20:46,645 [main] INFO jTPCC : Term-00, deliveryWeight=4 20:20:46,645 [main] INFO jTPCC : Term-00, stockLevelWeight=4 20:20:46,645 [main] INFO jTPCC : Term-00, 20:20:46,646 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS 20:20:46,646 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py 20:20:46,646 [main] INFO jTPCC : Term-00, 20:20:46,713 [main] INFO jTPCC : Term-00, copied props.ob to my_result_2022-01-26_202046/run.properties 20:20:46,722 [main] INFO jTPCC : Term-00, created my_result_2022-01-26_202046/data/runInfo.csv for runID 7 20:20:46,724 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2022-01-26_202046/data/result.c sv 20:20:46,725 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py 20:20:46,726 [main] INFO jTPCC : Term-00, osCollectorInterval=1 20:20:46,726 [main] INFO jTPCC : Term-00, osCollectorSSHAddr=null 20:20:46,726 [main] INFO jTPCC : Term-00, osCollectorDevices=null 20:20:46,905 [main] INFO jTPCC : Term-00, 20:20:47,417 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 14 20:20:47,417 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 90 20:20:47,417 [main] INFO jTPCC : Term-00, Term-00, Running Average tpmTOTAL: 5.81 Curren20:26:59,279 [Thread-3] INFO jTPCC : Term-00, 186MB 20:26:59,279 [Thread-3] INFO jTPCC : Term-00, 20:26:59,279 [Thread-3] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 3.39 20:26:59,280 [Thread-3] INFO jTPCC : Term-00, Measured tpmTOTAL = 5.81 20:26:59,280 [Thread-3] INFO jTPCC : Term-00, Session Start = 2022-01-26 20:20:47 20:26:59,280 [Thread-3] INFO jTPCC : Term-00, Session End = 2022-01-26 20:26:59 20:26:59,280 [Thread-3] INFO jTPCC : Term-00, Transaction Count = 35
毕竟是笔记本电脑，内存仅有16G， tpmC才3.39， 五分钟一共运行了35个事务，指标低了点。
2 TPC-C TOP SQL分析
2.1 查询TOP sql
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='tpcc' 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 | +----------------------------------+----------+------------------+---------------+ | 7229213613983BC5FDA15AD11EC70D01 | 2 | 4489936 | 4283007 | | F59A700FA168324279B0DBC25E19760F | 1 | 4356147 | 4162021 | | 5984364296F35BE1B71CD5622426385A | 1 | 2518614 | 2462524 | | 482BA7822AE7BE644CEBEB55213E7284 | 1 | 1977823 | 1973265 | | EC66B09D06D688727D0F999BFCFF5348 | 1 | 1857203 | 1851199 | | E1F2BDA1D7391B757859ED3704E5AFB7 | 1 | 1825043 | 1821800 | | | 1172 | 1454010 | 1183139 | | E86A0CA8BE3F21A2FBC9F1F9855075A1 | 1 | 1306776 | 1173832 | | A460265EC2F0763A15DD27CE9E4E2200 | 1 | 835242 | 590782 | | F0EFFFCD85E71C241661E66EEA047C58 | 2 | 707358 | 678918 | +----------------------------------+----------+------------------+---------------+ 10 rows in set (0.119 sec)
2.2 对elapsed时间最长的前三条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 3; +----------------------------------+----------+------------------+---------------+-----------+----------+-----------+---------+ | sql_id | count(*) | avg_elapsed_time | avg_exec_time | svr_ip | svr_port | tenant_id | plan_id | +----------------------------------+----------+------------------+---------------+-----------+----------+-----------+---------+ | 7229213613983BC5FDA15AD11EC70D01 | 2 | 4489936 | 4283007 | 127.0.0.1 | 2882 | 1001 | 40 | | F59A700FA168324279B0DBC25E19760F | 1 | 4356147 | 4162021 | 127.0.0.1 | 2882 | 1001 | 42 | | 5984364296F35BE1B71CD5622426385A | 1 | 2518614 | 2462524 | 127.0.0.1 | 2882 | 1001 | 27 | +----------------------------------+----------+------------------+---------------+-----------+----------+-----------+---------+ 3 rows in set (0.088 sec)
获取第一条sql的文本
select distinct query_sql from gv$sql_audit where sql_id='7229213613983BC5FDA15AD11EC70D01'; +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | query_sql | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | SELECT s_quantity, s_data, s_dist_01, s_dist_02, s_dist_03, s_dist_04,s_dist_05, s_dist_06, s_dist_07, s_dist_08,s_dist_09, s_dist_10 FROM bmsql_stock WHERE s_w_id = 1 AND s_i_id = 97744 FOR UPDATE | | SELECT s_quantity, s_data, s_dist_01, s_dist_02, s_dist_03, s_dist_04,s_dist_05, s_dist_06, s_dist_07, s_dist_08, s_dist_09, s_dist_10 FROM bmsql_stock WHERE s_w_id = 2 AND s_i_id = 10652 FOR UPDATE | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.068 sec)
第一个sql_id 有两个不同的文本，这两个文本的不同仅仅是传入参数不同，可以共享执行计划，这个计划的plan_id 为40，运行下面sql查询这条语句的实际执行计划
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 = '127.0.0.1' AND port=2882 AND plan_id=40;
查询结果为空集，看来这条语句的实际执行计划已经被从缓存里刷出去了，切换到tpcc db运行以下这条语句后，再进行查询，得到下面的执行计划
*************************** 1. row *************************** ip: 127.0.0.1 plan_depth: 0 plan_line_id: 0 operator: PHY_TABLE_SCAN name: bmsql_stock rows: 9 cost: 248249 property: table_rows:86530, physical_range_rows:200159, logical_range_rows:86530, index_back_rows:0, output_rows:8, est_method:local_storage, avaiable_index_name[bmsql_stock], estimation info[table_id:1100611139453798, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:7, version:1-1643245354224215-1643245354224215, logical_rc:0, physical_rc:96654), (table_type:0, version:1643245354224215-1643245354224215-9223372036854775807, logical_rc:86530, physical_rc:103505)] 1 row in set (0.013 sec) ERROR: No query specified
这条语句的解释执行计划如下：
*************************** 1. row *************************** Query Plan: ============================================ |ID|OPERATOR |NAME |EST. ROWS|COST | -------------------------------------------- |0 |TABLE SCAN|bmsql_stock|9 |248250| ============================================ Outputs & filters: ------------------------------------- 0 - output([bmsql_stock.s_quantity], [bmsql_stock.s_data], [bmsql_stock.s_dist_01], [bmsql_stock.s_dist_02], [bmsql_stock.s_dist_03], [bmsql_stock.s_dist_04], [bmsql_stock.s_dist_05], [bmsql_stock.s_dist_06], [bmsql_stock.s_dist_07], [bmsql_stock.s_dist_08], [bmsql_stock.s_dist_09], [bmsql_stock.s_dist_10]), filter([bmsql_stock.s_w_id = 2], [bmsql_stock.s_i_id = 10652]), access([bmsql_stock.s_w_id], [bmsql_stock.s_i_id], [bmsql_stock.s_quantity], [bmsql_stock.s_data], [bmsql_stock.s_dist_01], [bmsql_stock.s_dist_02], [bmsql_stock.s_dist_03], [bmsql_stock.s_dist_04], [bmsql_stock.s_dist_05], [bmsql_stock.s_dist_06], [bmsql_stock.s_dist_07], [bmsql_stock.s_dist_08], [bmsql_stock.s_dist_09], [bmsql_stock.s_dist_10]), partitions(p0) 1 row in set (0.016 sec) ERROR: No query specified
可以看到，这条语句实际的执行计划和解释执行计划是相同的，都做了全表扫描，成本显示也相同。
获取第二条语句的文本
MySQL [oceanbase]> select query_sql from gv$sql_audit 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 = 2 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 = 2 AND d_id = 8 ) ) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.069 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 = '127.0.0.1' AND port=2882 AND plan_id=42\G; *************************** 1. row *************************** ip: 127.0.0.1 plan_depth: 0 plan_line_id: 0 operator: PHY_SCALAR_AGGREGATE name: NULL rows: 1 cost: 811804 property: NULL *************************** 2. row *************************** ip: 127.0.0.1 plan_depth: 1 plan_line_id: 1 operator: PHY_HASH_JOIN name: NULL rows: 2 cost: 811804 property: NULL *************************** 3. row *************************** ip: 127.0.0.1 plan_depth: 2 plan_line_id: 2 operator: PHY_SUBPLAN_SCAN name: NULL rows: 1 cost: 612151 property: NULL *************************** 4. row *************************** ip: 127.0.0.1 plan_depth: 3 plan_line_id: 3 operator: PHY_NESTED_LOOP_JOIN name: NULL rows: 1 cost: 612151 property: NULL *************************** 5. row *************************** ip: 127.0.0.1 plan_depth: 4 plan_line_id: 4 operator: PHY_TABLE_SCAN name: bmsql_order_line rows: 40 cost: 612101 property: table_rows:404006, physical_range_rows:600330, logical_range_rows:404006, index_back_rows:0, output_rows:39, est_method:local_storage, avaiable_index_name[bmsql_order_line], estimation info[table_id:1100611139453797, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:7, version:1-1643245354224215-1643245354224215, logical_rc:0, physical_rc:173262), (table_type:0, version:1643245354224215-1643245354224215-9223372036854775807, logical_rc:404006, physical_rc:427068)] *************************** 6. row *************************** ip: 127.0.0.1 plan_depth: 4 plan_line_id: 5 operator: PHY_MATERIAL name: NULL rows: 1 cost: 50 property: NULL *************************** 7. row *************************** ip: 127.0.0.1 plan_depth: 5 plan_line_id: 6 operator: PHY_TABLE_SCAN name: bmsql_district rows: 1 cost: 50 property: table_rows:20, physical_range_rows:20, logical_range_rows:20, index_back_rows:0, output_rows:0, est_method:local_storage, avaiable_index_name[bmsql_district], estimation info[table_id:1100611139453792, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:7, version:1-1643245354224215-1643245354224215, logical_rc:0, physical_rc:0), (table_type:0, version:1643245354224215-1643245354224215-9223372036854775807, logical_rc:20, physical_rc:20)] *************************** 8. row *************************** ip: 127.0.0.1 plan_depth: 2 plan_line_id: 7 operator: PHY_TABLE_SCAN name: bmsql_stock rows: 91 cost: 199621 property: table_rows:91148, physical_range_rows:200114, logical_range_rows:91148, index_back_rows:0, output_rows:90, est_method:local_storage, avaiable_index_name[bmsql_stock], estimation info[table_id:1100611139453798, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:7, version:1-1643245354224215-1643245354224215, logical_rc:0, physical_rc:96654), (table_type:0, version:1643245354224215-1643245354224215-9223372036854775807, logical_rc:91148, physical_rc:103460)] 8 rows in set (0.005 sec)
解释执行计划
explain SELECT count(*) AS low_stock FROM (SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = 2 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 = 2 AND d_id = 8))\G; MySQL [tpccdb]> explain SELECT count(*) AS low_stock FROM (SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock -> WHERE s_w_id = 2 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 = 2 AND d_id = 8))\G; *************************** 1. row *************************** Query Plan: ============================================================ |ID|OPERATOR |NAME |EST. ROWS|COST | ------------------------------------------------------------ |0 |SCALAR GROUP BY | |1 |812132| |1 | HASH RIGHT SEMI JOIN| |2 |812131| |2 | SUBPLAN SCAN |VIEW1 |1 |612420| |3 | NESTED-LOOP JOIN | |1 |612419| |4 | TABLE SCAN |bmsql_order_line|37 |612369| |5 | MATERIAL | |1 |51 | |6 | TABLE SCAN |bmsql_district |1 |51 | |7 | TABLE SCAN |bmsql_stock |86 |199683| ============================================================ 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([bmsql_order_line.ol_w_id = 2], [bmsql_order_line.ol_d_id = 8]), 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) 5 - output([bmsql_district.d_next_o_id]), filter(nil) 6 - output([bmsql_district.d_next_o_id]), filter([bmsql_district.d_w_id = 2], [bmsql_district.d_id = 8], [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) 7 - output([bmsql_stock.s_i_id]), filter([bmsql_stock.s_w_id = 2], [bmsql_stock.s_quantity < 13]), access([bmsql_stock.s_w_id], [bmsql_stock.s_quantity], [bmsql_stock.s_i_id]), partitions(p0) 1 row in set (0.016 sec) ERROR: No query specified
这条语句的解释执行计划和实际执行计划也是相同的。
获取第三条语句的文本
MySQL [oceanbase]> select query_sql from gv$sql_audit where sql_id='5984364296F35BE1B71CD5622426385A'; +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | query_sql | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | SELECT ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_delivery_d FROM bmsql_order_line WHERE ol_w_id = 2 AND ol_d_id = 4 AND ol_o_id = 1182 ORDER BY ol_w_id, ol_d_id, ol_o_id, ol_number | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.060 sec)
实际执行计划
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 = '127.0.0.1' AND port=2882 AND plan_id=27; *************************** 1. row *************************** ip: 127.0.0.1 plan_depth: 0 plan_line_id: 0 operator: PHY_SORT name: NULL rows: 1 cost: 778838 property: NULL *************************** 2. row *************************** ip: 127.0.0.1 plan_depth: 1 plan_line_id: 1 operator: PHY_TABLE_SCAN name: bmsql_order_line rows: 1 cost: 778837 property: table_rows:404006, physical_range_rows:600330, logical_range_rows:404006, index_back_rows:0, output_rows:0, est_method:local_storage, avaiable_index_name[bmsql_order_line], estimation info[table_id:1100611139453797, (table_type:1, version:0-1-1, logical_rc:0, physical_rc:0), (table_type:7, version:1-1643245354224215-1643245354224215, logical_rc:0, physical_rc:173262), (table_type:0, version:1643245354224215-1643245354224215-9223372036854775807, logical_rc:404006, physical_rc:427068)] 2 rows in set (0.014 sec)
解释执行计划
explain SELECT ol_i_id, ol_supply_w_id, ol_quantity, ol_amount, ol_delivery_d FROM bmsql_order_line WHERE ol_w_id = 2 AND ol_d_id = 4 AND ol_o_id = 1182 ORDER BY ol_w_id, ol_d_id, ol_o_id, ol_number\G; MySQL [tpccdb]> explain SELECT ol_i_id, ol_supply_w_id, ol_quantity, -> ol_amount, ol_delivery_d -> FROM bmsql_order_line WHERE ol_w_id = 2 AND ol_d_id = 4 AND ol_o_id = 1182 -> ORDER BY ol_w_id, ol_d_id, ol_o_id, ol_number\G; *************************** 1. row *************************** Query Plan: ================================================== |ID|OPERATOR |NAME |EST. ROWS|COST | -------------------------------------------------- |0 |SORT | |1 |779182| |1 | TABLE SCAN|bmsql_order_line|1 |779181| ================================================== Outputs & filters: ------------------------------------- 0 - output([bmsql_order_line.ol_i_id], [bmsql_order_line.ol_supply_w_id], [bmsql_order_line.ol_quantity], [bmsql_order_line.ol_amount], [bmsql_order_line.ol_delivery_d]), filter(nil), sort_keys([bmsql_order_line.ol_number, ASC]) 1 - output([bmsql_order_line.ol_i_id], [bmsql_order_line.ol_supply_w_id], [bmsql_order_line.ol_quantity], [bmsql_order_line.ol_amount], [bmsql_order_line.ol_delivery_d], [bmsql_order_line.ol_number]), filter([bmsql_order_line.ol_w_id = 2], [bmsql_order_line.ol_d_id = 4], [bmsql_order_line.ol_o_id = 1182]), 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], [bmsql_order_line.ol_supply_w_id], [bmsql_order_line.ol_quantity], [bmsql_order_line.ol_amount], [bmsql_order_line.ol_delivery_d], [bmsql_order_line.ol_number]), partitions(p0) 1 row in set (0.007 sec) ERROR: No query specified
这条语句的解释执行计划和实际执行计划也是相同的，都是全表扫描之后进行排序。
