练习内容
请记录并分享下列内容：
-
（必选）使用 BenmarkSQL 运行 TPC-C ，并发数不用很高，5~10 并发即可（根据机器资源）。
-
（必选）分析 TPC-C TOP SQL，并查看 3条 SQL 的 解析执行计划 和 实际执行计划。
-
（可选）使用 OceanBase 的 Outline 对 其中一条 SQL 进行限流（限制并发为 1 ）。
-
（可选）导入 TPC-H schema 和数据，数据量不用太大 100M 即可。查看 TPC-H 5条 SQL 的解析执行计划和实际执行计划。
下载BenmarkSQL并安装
git clone https://github.com/vonwind/benchmarksql-5.0.git
说明：add oceanbase configuration file (run/props.ob) and sql scripts (run/sql.oceanbase/*.sql) .
解压即可
root@cui-work0 ~]# cd benchmarksql-5.0/
[root@cui-work0 benchmarksql-5.0]# ll
total 28
drwxr-xr-x 2 root root 4096 Mar 21 14:28 build
-rwxr-xr-x 1 root root 1130 Mar 21 14:28 build.xml
drwxr-xr-x 2 root root 34 Mar 21 14:28 dist
drwxr-xr-x 3 root root 17 Mar 21 14:28 doc
-rwxr-xr-x 1 root root 6376 Mar 21 14:28 HOW-TO-RUN.txt
drwxr-xr-x 5 root root 111 Mar 21 14:28 lib
-rwxr-xr-x 1 root root 5470 Mar 21 14:28 README.md
drwxr-xr-x 8 root root 4096 Mar 21 14:28 run
drwxr-xr-x 6 root root 67 Mar 21 14:28 src
[root@cui-work0 benchmarksql-5.0]#
下载安装jdk
不是本次实验重点，省略…
创建表
[root@cui-work0 run]#
[root@cui-work0 run]# ./runSQL.sh ob.ob ./sql.oceanbase/mysqltableCreates.sql
# ------------------------------------------------------------
# Loading SQL file ./sql.oceanbase/mysqltableCreates.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),
primary key(w_id)
);
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)
) ;
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)
) ;
create sequence bmsql_hist_id_seq;
name is already used by an existing object
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)
) ;
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)
) ;
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)
) ;
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)
) ;
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)
);
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)
) ;
[root@cui-work0 run]#
准备数据
[root@cui-work0 run]# ./runLoader.sh ob.ob
Starting BenchmarkSQL LoadData
driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
conn=jdbc:oceanbase://127.1:2881/testtest?useUnicode=true&characterEncoding=utf-8
user=testuser@testtenant
password=***********
warehouses=1
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
[root@cui-work0 run]#
运行tpcc测试
[root@cui-work0 run]# ./runBenchmark.sh ob.ob
03:10:01,044 [main] INFO jTPCC : Term-00,
03:10:01,049 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
03:10:01,050 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.0
03:10:01,050 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
03:10:01,050 [main] INFO jTPCC : Term-00, (c) 2003, Raul Barbosa
03:10:01,050 [main] INFO jTPCC : Term-00, (c) 2004-2016, Denis Lussier
03:10:01,054 [main] INFO jTPCC : Term-00, (c) 2016, Jan Wieck
03:10:01,054 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+
03:10:01,054 [main] INFO jTPCC : Term-00,
03:10:01,054 [main] INFO jTPCC : Term-00, db=oracle
03:10:01,055 [main] INFO jTPCC : Term-00, driver=com.alipay.oceanbase.obproxy.mysql.jdbc.Driver
03:10:01,055 [main] INFO jTPCC : Term-00, conn=jdbc:oceanbase://127.1:2881/testtest?useUnicode=true&characterEncoding=utf-8
03:10:01,055 [main] INFO jTPCC : Term-00, user=testuser@testtenant
03:10:01,055 [main] INFO jTPCC : Term-00,
03:10:01,056 [main] INFO jTPCC : Term-00, warehouses=1
03:10:01,056 [main] INFO jTPCC : Term-00, terminals=2
03:10:01,058 [main] INFO jTPCC : Term-00, runMins=1
03:10:01,059 [main] INFO jTPCC : Term-00, limitTxnsPerMin=0
03:10:01,059 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true
03:10:01,059 [main] INFO jTPCC : Term-00,
03:10:01,059 [main] INFO jTPCC : Term-00, newOrderWeight=45
03:10:01,059 [main] INFO jTPCC : Term-00, paymentWeight=43
03:10:01,059 [main] INFO jTPCC : Term-00, orderStatusWeight=4
03:10:01,060 [main] INFO jTPCC : Term-00, deliveryWeight=4
03:10:01,060 [main] INFO jTPCC : Term-00, stockLevelWeight=4
03:10:01,060 [main] INFO jTPCC : Term-00,
03:10:01,060 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
03:10:01,060 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
03:10:01,060 [main] INFO jTPCC : Term-00,
03:10:01,088 [main] INFO jTPCC : Term-00, copied ob.ob to my_result_2023-03-22_031001/run.properties
03:10:01,088 [main] INFO jTPCC : Term-00, created my_result_2023-03-22_031001/data/runInfo.csv for runID 10
03:10:01,088 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2023-03-22_031001/data/result.csv
03:10:01,090 [main] INFO jTPCC : Term-00, osCollectorScript=./misc/os_collector_linux.py
03:10:01,090 [main] INFO jTPCC : Term-00, osCollectorInterval=1
03:10:01,090 [main] INFO jTPCC : Term-00, osCollectorSSHAddr=null
03:10:01,090 [main] INFO jTPCC : Term-00, osCollectorDevices=null
03:10:01,167 [main] INFO jTPCC : Term-00,
03:10:01,560 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 34
03:10:01,560 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 132
03:10:01,560 [main] INFO jTPCC : Term-00,
Term-00, Running Average tpmTOTAL: 757.62 Current tpmTOTAL: 5112 Memory Usage: 51MB / 240MB
03:11:01,634 [Thread-1] INFO jTPCC : Term-00,
03:11:01,635 [Thread-1] INFO jTPCC : Term-00,
03:11:01,635 [Thread-1] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 341.77
03:11:01,635 [Thread-1] INFO jTPCC : Term-00, Measured tpmTOTAL = 758.49
03:11:01,635 [Thread-1] INFO jTPCC : Term-00, Session Start = 2023-03-22 03:10:01
03:11:01,635 [Thread-1] INFO jTPCC : Term-00, Session End = 2023-03-22 03:11:01
03:11:01,635 [Thread-1] INFO jTPCC : Term-00, Transaction Count = 758
[root@cui-work0 run]#
查出占资源前三的sql_id
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$OB_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;
obclient [oceanbase]>
obclient [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$OB_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 |
+----------------------------------+----------+------------------+---------------+-----------+----------+-----------+---------+
| AB92F1B97A8D4DADFD477BB52C65A00B | 19 | 130161 | 130072 | 127.0.0.1 | 2882 | 1002 | 2196 |
| B447DE16B3F42D2409B2A2BE50328E63 | 30 | 8760 | 663 | 127.0.0.1 | 2882 | 1002 | 2194 |
| FC3FED8CCB2946DE54F1C5BA3656023C | 32 | 7548 | 873 | 127.0.0.1 | 2882 | 1002 | 2178 |
+----------------------------------+----------+------------------+---------------+-----------+----------+-----------+---------+
3 rows in set (0.009 sec)
obclient [oceanbase]>
查看第一条sql的执行计划
查出相关sql语句
select distinct query_sql from GV$OB_SQL_AUDIT where sql_id='AB92F1B97A8D4DADFD477BB52C65A00B'\G;
obclient [oceanbase]> select distinct query_sql from GV$OB_SQL_AUDIT where sql_id='AB92F1B97A8D4DADFD477BB52C65A00B' limit 2\G;
*************************** 1. row ***************************
query_sql: SELECT c_id FROM bmsql_customer WHERE c_w_id = 1 AND c_d_id = 9 AND c_last = 'PRIATIONESE' ORDER BY c_first
*************************** 2. row ***************************
query_sql: SELECT c_id FROM bmsql_customer WHERE c_w_id = 1 AND c_d_id = 5 AND c_last = 'ANTIPRIATION' ORDER BY c_first
2 rows in set (0.001 sec)
ERROR:
No query specified
obclient [oceanbase]>
物理执行计划
obclient [oceanbase]> SELECT SVR_IP, plan_depth, plan_line_id,operator,name,rows,cost,property from oceanbase.`GV$OB_PLAN_CACHE_PLAN_EXPLAIN` where tenant_id=1002 AND SVR_IP = '127.0.0.1' AND SVR_PORT=2882 AND plan_id=2196\G;
*************************** 1. row ***************************
SVR_IP: 127.0.0.1
plan_depth: 0
plan_line_id: 0
operator: PHY_SORT
name: NULL
rows: 30
cost: 173
property: NULL
*************************** 2. row ***************************
SVR_IP: 127.0.0.1
plan_depth: 1
plan_line_id: 1
operator: PHY_TABLE_SCAN
name: bmsql_customer
rows: 30
cost: 162
property: table_rows:30000, physical_range_rows:3000, logical_range_rows:3000, index_back_rows:0, output_rows:29, est_method:local_storage, avaiable_index_name[bmsql_customer], estimation info[table_id:500091, (table_type:12, version:-1--1--1, logical_rc:3000, physical_rc:3000)]
2 rows in set (0.001 sec)
ERROR:
No query specified
obclient [oceanbase]>
真实：
解析执行计划
obclient [testtest]> explain extended SELECT c_id FROM bmsql_customer WHERE c_w_id = 1 AND c_d_id = 9 AND c_last = 'PRIATIONESE' ORDER BY c_first \G;
*************************** 1. row ***************************
Query Plan: Plan signature: 447572509837759535
==============================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
----------------------------------------------
|0 |SORT | |30 |177 |
|1 | TABLE SCAN|bmsql_customer|30 |166 |
==============================================
Outputs & filters:
-------------------------------------
0 - output([bmsql_customer.c_id(0x7f2a2f60c680)]), filter(nil), rowset=256, sort_keys([bmsql_customer.c_first(0x7f2a2f60cc70), ASC])
1 - output([bmsql_customer.c_id(0x7f2a2f60c680)], [bmsql_customer.c_first(0x7f2a2f60cc70)]), filter([bmsql_customer.c_last(0x7f2a2f60be10) = 'PRIATIONESE'(0x7f2a2f60b730)]), rowset=256,
access([bmsql_customer.c_id(0x7f2a2f60c680)], [bmsql_customer.c_last(0x7f2a2f60be10)], [bmsql_customer.c_first(0x7f2a2f60cc70)]), partitions(p0),
is_index_back=false, filter_before_indexback[false],
range_key([bmsql_customer.c_w_id(0x7f2a2f609a30)], [bmsql_customer.c_d_id(0x7f2a2f60ac20)], [bmsql_customer.c_id(0x7f2a2f60c680)]), range(1,9,MIN ; 1,9,MAX),
range_cond([bmsql_customer.c_w_id(0x7f2a2f609a30) = 1(0x7f2a2f609350)], [bmsql_customer.c_d_id(0x7f2a2f60ac20) = 9(0x7f2a2f60a540)])
Used Hint:
-------------------------------------
/*+
*/
Qb name trace:
-------------------------------------
stmt_id:0, stmt_type:T_EXPLAIN
stmt_id:1, SEL$1
Outline Data:
-------------------------------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "testtest"."bmsql_customer"@"SEL$1")
OPTIMIZER_FEATURES_ENABLE('4.0.0.0')
END_OUTLINE_DATA
*/
Plan Type:
-------------------------------------
LOCAL
Optimization Info:
-------------------------------------
bmsql_customer:table_rows:30000, physical_range_rows:3058, logical_range_rows:3000, index_back_rows:0, output_rows:29, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[bmsql_customer], estimation info[table_id:500091, (table_type:12, version:-1--1--1, logical_rc:3000, physical_rc:3000), (table_type:0, version:-1--1--1, logical_rc:0, physical_rc:58)]
Parameters:
-------------------------------------
1 row in set (0.010 sec)
ERROR:
No query specified
obclient [testtest]>
查看第二条sql执行计划
物理执行计划
obclient [oceanbase]> SELECT SVR_IP, plan_depth, plan_line_id,operator,name,rows,cost,property from oceanbase.`GV$OB_PLAN_CACHE_PLAN_EXPLAIN` where tenant_id=1002 AND SVR_IP = '127.0.0.1' AND SVR_PORT=2882 AND plan_id=2194\G;
*************************** 1. row ***************************
SVR_IP: 127.0.0.1
plan_depth: 0
plan_line_id: 0
operator: PHY_UPDATE
name: NULL
rows: 1
cost: 34
property: NULL
*************************** 2. row ***************************
SVR_IP: 127.0.0.1
plan_depth: 1
plan_line_id: 1
operator: PHY_TABLE_SCAN
name: bmsql_warehouse
rows: 1
cost: 2
property: table_rows:1, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:default_stat, avaiable_index_name[bmsql_warehouse]
2 rows in set (0.002 sec)
ERROR:
No query specified
obclient [oceanbase]>
真实
解析执行计划
obclient [testtest]> explain extended UPDATE bmsql_warehouse SET w_ytd = w_ytd + 2251.34 WHERE w_id = 1\G;
*************************** 1. row ***************************
Query Plan: Plan signature: 12274392107549736164
==============================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
----------------------------------------------
|0 |UPDATE | |1 |35 |
|1 | TABLE GET|bmsql_warehouse|1 |3 |
==============================================
Outputs & filters:
-------------------------------------
0 - output(nil), filter(nil), table_columns([{bmsql_warehouse: ({bmsql_warehouse: (bmsql_warehouse.w_id(0x7f2a28c10d90), bmsql_warehouse.w_ytd(0x7f2a28c08220), bmsql_warehouse.w_tax(0x7f2a28c11070), bmsql_warehouse.w_name(0x7f2a28c11350), bmsql_warehouse.w_street_1(0x7f2a28c11630), bmsql_warehouse.w_street_2(0x7f2a28c11910), bmsql_warehouse.w_city(0x7f2a28c11bf0), bmsql_warehouse.w_state(0x7f2a28c11ed0), bmsql_warehouse.w_zip(0x7f2a28c121b0))})}]),
update([bmsql_warehouse.w_ytd(0x7f2a28c08220)=column_conv(DECIMAL,PS:(12,2),NULL,bmsql_warehouse.w_ytd(0x7f2a28c08220) + 2251.34(0x7f2a28c08ab0))(0x7f2a28c09250)])
1 - output([bmsql_warehouse.w_id(0x7f2a28c10d90)], [bmsql_warehouse.w_ytd(0x7f2a28c08220)], [bmsql_warehouse.w_tax(0x7f2a28c11070)], [bmsql_warehouse.w_name(0x7f2a28c11350)], [bmsql_warehouse.w_street_1(0x7f2a28c11630)], [bmsql_warehouse.w_street_2(0x7f2a28c11910)], [bmsql_warehouse.w_city(0x7f2a28c11bf0)], [bmsql_warehouse.w_state(0x7f2a28c11ed0)], [bmsql_warehouse.w_zip(0x7f2a28c121b0)]), filter(nil), rowset=256,
access([bmsql_warehouse.w_id(0x7f2a28c10d90)], [bmsql_warehouse.w_ytd(0x7f2a28c08220)], [bmsql_warehouse.w_tax(0x7f2a28c11070)], [bmsql_warehouse.w_name(0x7f2a28c11350)], [bmsql_warehouse.w_street_1(0x7f2a28c11630)], [bmsql_warehouse.w_street_2(0x7f2a28c11910)], [bmsql_warehouse.w_city(0x7f2a28c11bf0)], [bmsql_warehouse.w_state(0x7f2a28c11ed0)], [bmsql_warehouse.w_zip(0x7f2a28c121b0)]), partitions(p0),
is_index_back=false,
range_key([bmsql_warehouse.w_id(0x7f2a28c10d90)]), range[1 ; 1],
range_cond([bmsql_warehouse.w_id(0x7f2a28c10d90) = 1(0x7f2a28c4e590)])
Used Hint:
-------------------------------------
/*+
*/
Qb name trace:
-------------------------------------
stmt_id:0, stmt_type:T_EXPLAIN
stmt_id:1, UPD$1
Outline Data:
-------------------------------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"UPD$1" "testtest"."bmsql_warehouse"@"UPD$1")
OPTIMIZER_FEATURES_ENABLE('4.0.0.0')
END_OUTLINE_DATA
*/
Plan Type:
-------------------------------------
LOCAL
Optimization Info:
-------------------------------------
bmsql_warehouse:table_rows:1, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:default_stat, optimization_method=rule_based, heuristic_rule=unique_index_without_indexback
Parameters:
-------------------------------------
1 row in set (0.008 sec)
ERROR:
No query specified
obclient [testtest]>
查看第三条sql执行计划
物理执行计划
obclient [oceanbase]> SELECT SVR_IP, plan_depth, plan_line_id,operator,name,rows,cost,property from oceanbase.`GV$OB_PLAN_CACHE_PLAN_EXPLAIN` where tenant_id=1002 AND SVR_IP = '127.0.0.1' AND SVR_PORT=2882 AND plan_id=2178\G;
*************************** 1. row ***************************
SVR_IP: 127.0.0.1
plan_depth: 0
plan_line_id: 0
operator: PHY_MATERIAL
name: NULL
rows: 1
cost: 2
property: NULL
*************************** 2. row ***************************
SVR_IP: 127.0.0.1
plan_depth: 1
plan_line_id: 1
operator: PHY_LOCK
name: NULL
rows: 1
cost: 2
property: NULL
*************************** 3. row ***************************
SVR_IP: 127.0.0.1
plan_depth: 2
plan_line_id: 2
operator: PHY_TABLE_SCAN
name: bmsql_district
rows: 1
cost: 2
property: table_rows:10, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:default_stat, avaiable_index_name[bmsql_district]
3 rows in set (0.001 sec)
ERROR:
No query specified
obclient [oceanbase]>
真实：
解析执行计划
obclient [testtest]> explain extended SELECT d_tax, d_next_o_id FROM bmsql_district WHERE d_w_id = 1 AND d_id = 6 FOR UPDATE \G
*************************** 1. row ***************************
Query Plan: Plan signature: 18137904176575130337
==============================================
|ID|OPERATOR |NAME |EST. ROWS|COST|
----------------------------------------------
|0 |MATERIAL | |1 |3 |
|1 | FOR UPDATE| |1 |3 |
|2 | TABLE GET|bmsql_district|1 |3 |
==============================================
Outputs & filters:
-------------------------------------
0 - output([bmsql_district.d_tax(0x7f2a5fe0ae70)], [bmsql_district.d_next_o_id(0x7f2a5fe0b460)]), filter(nil), rowset=256
1 - output([bmsql_district.d_tax(0x7f2a5fe0ae70)], [bmsql_district.d_next_o_id(0x7f2a5fe0b460)]), filter(nil), rowset=256, lock tables(bmsql_district)
2 - output([bmsql_district.d_w_id(0x7f2a5fe09410)], [bmsql_district.d_id(0x7f2a5fe0a600)], [bmsql_district.d_tax(0x7f2a5fe0ae70)], [bmsql_district.d_next_o_id(0x7f2a5fe0b460)]), filter(nil), rowset=256,
access([bmsql_district.d_w_id(0x7f2a5fe09410)], [bmsql_district.d_id(0x7f2a5fe0a600)], [bmsql_district.d_tax(0x7f2a5fe0ae70)], [bmsql_district.d_next_o_id(0x7f2a5fe0b460)]), partitions(p0),
is_index_back=false,
range_key([bmsql_district.d_w_id(0x7f2a5fe09410)], [bmsql_district.d_id(0x7f2a5fe0a600)]), range[1,6 ; 1,6],
range_cond([bmsql_district.d_w_id(0x7f2a5fe09410) = 1(0x7f2a5fe08d30)], [bmsql_district.d_id(0x7f2a5fe0a600) = 6(0x7f2a5fe09f20)])
Used Hint:
-------------------------------------
/*+
*/
Qb name trace:
-------------------------------------
stmt_id:0, stmt_type:T_EXPLAIN
stmt_id:1, SEL$1
Outline Data:
-------------------------------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "testtest"."bmsql_district"@"SEL$1")
OPTIMIZER_FEATURES_ENABLE('4.0.0.0')
END_OUTLINE_DATA
*/
Plan Type:
-------------------------------------
LOCAL
Optimization Info:
-------------------------------------
bmsql_district:table_rows:10, physical_range_rows:1, logical_range_rows:1, index_back_rows:0, output_rows:1, est_method:default_stat, optimization_method=rule_based, heuristic_rule=unique_index_without_indexback
Parameters:
-------------------------------------
1 row in set (0.007 sec)
obclient [testtest]>
总结
- benchmarksql测试时，由于我是在单副本集群测试，所以要将建表语句中的table group去掉
- 原本脚本中有的字段是用的varchar2.而mysql是不支持warchar2的，将其改为varchar
- 三条sql语句的物理执行计划和解析执行计划是相同的