首批通过分布式安全可靠测评，为关键业务系统打造
划线反馈
如何绑定有子查询的 SQL 的执行计划
更新时间：2024-11-12 08:36
分享
适用版本：V4.0.x、V4.1.x、V4.2.x内容类型：How-to
问题现象
当一条 SQL 中有子查询的时候，并且该子查询需要绑定 Hint 的情况下，如果用 Outline 根据 SQL_ID 绑定执行计划不知如何写 Hint 或者是绑定执行计划的时候，发现 Hint 没有生效。
PS：如果根据 SQL_TEXT 绑定 SQL 只能固定参数，不像 SQL_ID 绑定可以针对模版化的一类 SQL，都使用相同的执行计划。
示例表 test1，test2
MySQL [test]> create table test1(a int,b int,key `b_idx`(b));
Query OK, 0 rows affected (0.66 sec)
MySQL [test]> create table test2(a int,b int,key `b_idx`(b));
Query OK, 0 rows affected (0.16 sec)
含子查询的示例 SQL
obclient [test]> select * from test1
where test1.a in(
select a from test2 where test2.b = 1
union all
select a from test2 where test2.b = 2
);
Empty set (0.037 sec)
解释 SQL 执行计划信息（原始执行计划）
explain select * from test1 where test1.a in(select a from test2 where test2.b = 1 union all select a from test2 where test2.b = 2);
输出结果如下：
============================================================
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
------------------------------------------------------------
|0 |SUBPLAN FILTER | |1 |16 |
|1 |├─TABLE FULL SCAN |test1 |1 |3 |
|2 |└─UNION ALL | |2 |14 |
|3 | ├─TABLE RANGE SCAN|test2(b_idx)|1 |7 |
|4 | └─TABLE RANGE SCAN|test2(b_idx)|1 |7 |
============================================================
Outputs & filters:
-------------------------------------
0 - output([test1.a(0x7f57d0a7b000)], [test1.b(0x7f57d0a7b690)]), filter(nil), rowset=16
exec_params_(nil), onetime_exprs_([subquery(1)(0x7f57d0a20f50)(:0)]), init_plan_idxs_(nil), use_batch=false
1 - output([test1.a(0x7f57d0a7b000)], [test1.b(0x7f57d0a7b690)]), filter([test1.a(0x7f57d0a7b000) = :0(0x7f57abc59ec0)]), rowset=16
access([test1.a(0x7f57d0a7b000)], [test1.b(0x7f57d0a7b690)]), partitions(p0)
is_index_back=false, is_global_index=false, filter_before_indexback[false],
range_key([test1.__pk_increment(0x7f57d0a7cee0)]), range(MIN ; MAX)always true
2 - output([UNION([1])(0x7f57d0a7aad0)]), filter(nil), rowset=16
3 - output([test2.a(0x7f57d0a66090)]), filter(nil), rowset=16
access([test2.__pk_increment(0x7f57d0a7c0e0)], [test2.a(0x7f57d0a66090)]), partitions(p0)
is_index_back=true, is_global_index=false,
range_key([test2.b(0x7f57d0a65590)], [test2.__pk_increment(0x7f57d0a7c0e0)]), range(1,MIN ; 1,MAX),
range_cond([test2.b(0x7f57d0a65590) = 1(0x7f57d0a64cd0)])
4 - output([test2.a(0x7f57d0a7a5d0)]), filter(nil), rowset=16
access([test2.__pk_increment(0x7f57d0a7c720)], [test2.a(0x7f57d0a7a5d0)]), partitions(p0)
is_index_back=true, is_global_index=false,
range_key([test2.b(0x7f57d0a79ad0)], [test2.__pk_increment(0x7f57d0a7c720)]), range(2,MIN ; 2,MAX),
range_cond([test2.b(0x7f57d0a79ad0) = 2(0x7f57d0a79210)])
解释 SQL 执行计划信息（子查询其中一个 test2 走全表扫描）
如果这时候想要子查询里面的其中一个 test2 表走全表扫描，另一个 test2 表仍走索引的话，如果 Hint 直接加到子查询这里，绑定 Outline 时就不知道该怎么写 Hint 了，只能看到 Used Hint 如下。
Used Hint:
-------------------------------------
/*+
FULL("test2")
*/
子查询其中一个 test2 走全表扫描，如下。
MySQL [test]> explain extended select * from test1 where test1.a in(select a from test2 where test2.b = 1 union all select /*+FULL(test2)*/ a from test2 where test2.b = 2);
输出结果如下：
============================================================
|ID|OPERATOR |NAME |EST.ROWS|EST.TIME(us)|
------------------------------------------------------------
|0 |SUBPLAN FILTER | |1 |12 |
|1 |├─TABLE FULL SCAN |test1 |1 |3 |
|2 |└─UNION ALL | |2 |9 |
|3 | ├─TABLE RANGE SCAN|test2(b_idx)|1 |7 |
|4 | └─TABLE FULL SCAN |test2 |1 |3 |
============================================================
Outputs & filters:
-------------------------------------
0 - output([test1.a(0x7f57e647b080)], [test1.b(0x7f57e647b710)]), filter(nil), rowset=16
exec_params_(nil), onetime_exprs_([subquery(1)(0x7f57e64212e0)(:0)]), init_plan_idxs_(nil), use_batch=false
1 - output([test1.a(0x7f57e647b080)], [test1.b(0x7f57e647b710)]), filter([test1.a(0x7f57e647b080) = :0(0x7f57cce54e10)]), rowset=16
access([test1.a(0x7f57e647b080)], [test1.b(0x7f57e647b710)]), partitions(p0)
is_index_back=false, is_global_index=false, filter_before_indexback[false],
range_key([test1.__pk_increment(0x7f57e647cf60)]), range(MIN ; MAX)always true
2 - output([UNION([1])(0x7f57e647ab50)]), filter(nil), rowset=16
3 - output([test2.a(0x7f57e6466090)]), filter(nil), rowset=16
access([test2.__pk_increment(0x7f57e647c160)], [test2.a(0x7f57e6466090)]), partitions(p0)
is_index_back=true, is_global_index=false,
range_key([test2.b(0x7f57e6465590)], [test2.__pk_increment(0x7f57e647c160)]), range(1,MIN ; 1,MAX),
range_cond([test2.b(0x7f57e6465590) = 1(0x7f57e6464cd0)])
4 - output([test2.a(0x7f57e647a650)]), filter([test2.b(0x7f57e6479b50) = 2(0x7f57e6479290)]), rowset=16
access([test2.b(0x7f57e6479b50)], [test2.a(0x7f57e647a650)]), partitions(p0)
is_index_back=false, is_global_index=false, filter_before_indexback[false],
range_key([test2.__pk_increment(0x7f57e647c7a0)]), range(MIN ; MAX)always true
Used Hint:
-------------------------------------
/*+
FULL("test2")
*/
Qb name trace:
-------------------------------------
stmt_id:0, stmt_type:T_EXPLAIN
stmt_id:1, SEL$1
stmt_id:2, SET$1
stmt_id:3, SEL$2
stmt_id:4, SEL$3
Outline Data:
-------------------------------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "test"."test1"@"SEL$1")
INDEX(@"SEL$2" "test"."test2"@"SEL$2" "b_idx")
FULL(@"SEL$3" "test"."test2"@"SEL$3")
OPTIMIZER_FEATURES_ENABLE('4.3.3.0')
END_OUTLINE_DATA
*/
Optimization Info:
-------------------------------------
test1:
table_rows:1
physical_range_rows:1
logical_range_rows:1
index_back_rows:0
output_rows:1
table_dop:1
dop_method:Table DOP
avaiable_index_name:[b_idx, test1]
pruned_index_name:[b_idx]
stats info:[version=0, is_locked=0, is_expired=0]
dynamic sampling level:0
estimation method:[DEFAULT, STORAGE]
test2:
table_rows:1
physical_range_rows:1
logical_range_rows:1
index_back_rows:1
output_rows:1
table_dop:1
dop_method:Table DOP
avaiable_index_name:[b_idx, test2]
unstable_index_name:[test2]
stats info:[version=0, is_locked=0, is_expired=0]
dynamic sampling level:0
estimation method:[DEFAULT, STORAGE]
test2:
table_rows:1
physical_range_rows:1
logical_range_rows:1
index_back_rows:0
output_rows:1
table_dop:1
dop_method:Table DOP
avaiable_index_name:[b_idx, test2]
pruned_index_name:[b_idx]
stats info:[version=0, is_locked=0, is_expired=0]
dynamic sampling level:0
estimation method:[DEFAULT, STORAGE]
Plan Type:
LOCAL
Note:
Degree of Parallelisim is 1 because of table property
绑定的执行计划
这时如果直接使用 Used Hint 里面的信息来绑定执行计划的话，就会把 Hint 直接加到最前面，走不到想要的执行计划，例如。
Used Hint。
------------------------------------- /*+ FULL("test2") */创建 Outline。
create outline outline_test2 on "EA19E97132FCC08DCD96AF3D894A69F0" using hint /*+FULL("test2")*/;展示本租户的执行计划 Outline 信息。
select outline_name,sql_id,outline_content from oceanbase.DBA_OB_OUTLINES where outline_name = 'outline_test2';查看执行的 SQL 实际使用上了绑定的执行计划。
MySQL [test]> select SQL_ID,PLAN_ID,QUERY_SQL,OUTLINE_ID,OUTLINE_DATA from oceanbase.gv$ob_plan_cache_plan_stat where sql_id='EA19E97132FCC08DCD96AF3D894A69F0'\G;输出结果如下：
*************************** 1. row *************************** SQL_ID: EA19E97132FCC08DCD96AF3D894A69F0 PLAN_ID: 18085 QUERY_SQL: select * from test1 where test1.a in(select a from test2 where test2.b = 1 union all select a from test2 where test2.b = 2) OUTLINE_ID: 500124 OUTLINE_DATA: /*+BEGIN_OUTLINE_DATA FULL(@"SEL$1" "test"."test1"@"SEL$1") INDEX(@"SEL$2" "test"."test2"@"SEL$2" "b_idx") INDEX(@"SEL$3" "test"."test2"@"SEL$3" "b_idx") OPTIMIZER_FEATURES_ENABLE('4.3.3.0') END_OUTLINE_DATA*/ 1 row in set (0.01 sec)可以看到，这时候的执行计划没有按照预期的走，子查询的 2 个 test2 表都是走的索引扫描。
适用版本
OceanBase 数据库 V4.x 版本。
解决方法
Outline 使用 SQL_ID 绑定执行计划，详细内容参见：计划绑定。
EXPLAIN EXTENDED 绑定了 Hint 的 SQL，查看执行计划的的 OUTLINE_DATA 信息，使用示例的 Hint 直接绑定，示例。
| Outline Data: | | ------------------------------------- | | /*+ | | BEGIN_OUTLINE_DATA | | FULL(@"SEL$1" "test"."test1"@"SEL$1") | | INDEX(@"SEL$2" "test"."test2"@"SEL$2" "b_idx") | | FULL(@"SEL$3" "test"."test2"@"SEL$3") | | OPTIMIZER_FEATURES_ENABLE('4.3.3.0') | | END_OUTLINE_DATA | | */创建 Outline。
CREATE OUTLINE outline_test2 on "EA19E97132FCC08DCD96AF3D894A69F0" using hint /*+ BEGIN_OUTLINE_DATA INDEX(@"SEL$2" "test"."test2"@"SEL$2" "b_idx") FULL(@"SEL$3" "test"."test2"@"SEL$3") END_OUTLINE_DATA */;查看执行的 SQL 实际使用上了绑定的执行计划。
-- 绑定的子查询执行计划生效。 MySQL [test]> select SQL_ID,PLAN_ID,QUERY_SQL,OUTLINE_ID,OUTLINE_DATA from oceanbase.gv$ob_plan_cache_plan_stat where sql_id='EA19E97132FCC08DCD96AF3D894A69F0'\G;输出结果如下：
*************************** 1. row *************************** SQL_ID: EA19E97132FCC08DCD96AF3D894A69F0 PLAN_ID: 17878 QUERY_SQL: select * from test1 where test1.a in(select a from test2 where test2.b = 1 union all select a from test2 where test2.b = 2) OUTLINE_ID: 500123 OUTLINE_DATA: /*+BEGIN_OUTLINE_DATA FULL(@"SEL$1" "test"."test1"@"SEL$1") INDEX(@"SEL$2" "test"."test2"@"SEL$2" "b_idx") FULL(@"SEL$3" "test"."test2"@"SEL$3") OPTIMIZER_FEATURES_ENABLE('4.3.3.0') END_OUTLINE_DATA*/ 1 row in set (0.01 sec)
