--- title: 如何绑定有子查询的 SQL 的执行计划-OceanBase数据库使用指南 description: 了解OceanBase数据库在实际应用中关于 如何绑定有子查询的 SQL 的执行计划相关的常见问题和使用技巧，帮助您快速解决 如何绑定有子查询的 SQL 的执行计划的难题。 --- 切换语言 - 中文站 - 简体中文 - International - English - 日本站 - 日本語 划线反馈 # 如何绑定有子查询的 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 ```shell MySQL [test]> create table test1(a int,b int,key `b_idx`(b)); Query OK, 0 rows affected (0.66 sec) ``` ```shell MySQL [test]> create table test2(a int,b int,key `b_idx`(b)); Query OK, 0 rows affected (0.16 sec) ``` ## 含子查询的示例 SQL ```shell 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 执行计划信息（原始执行计划） ```shell 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); ``` 输出结果如下： ```shell ============================================================ |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 如下。 ```shell Used Hint: ------------------------------------- /*+ FULL("test2") */ ``` 子查询其中一个 test2 走全表扫描，如下。 ```shell 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); ``` 输出结果如下： ```shell ============================================================ |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 直接加到最前面，走不到想要的执行计划，例如。 1. Used Hint。 ```shell ------------------------------------- /*+ FULL("test2") */ ``` 2. 创建 Outline。 ```shell create outline outline_test2 on "EA19E97132FCC08DCD96AF3D894A69F0" using hint /*+FULL("test2")*/; ``` 3. 展示本租户的执行计划 Outline 信息。 ```shell select outline_name,sql_id,outline_content from oceanbase.DBA_OB_OUTLINES where outline_name = 'outline_test2'; ``` 4. 查看执行的 SQL 实际使用上了绑定的执行计划。 ```shell 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; ``` 输出结果如下： ```shell *************************** 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 版本。 ## 解决方法 1. Outline 使用 SQL_ID 绑定执行计划，详细内容参见：[计划绑定](https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001431842)。 2. EXPLAIN EXTENDED 绑定了 Hint 的 SQL，查看执行计划的的 OUTLINE_DATA 信息，使用示例的 Hint 直接绑定，示例。 ```shell | 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。 ```shell 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 */; ``` 3. 查看执行的 SQL 实际使用上了绑定的执行计划。 ```shell -- 绑定的子查询执行计划生效。 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; ``` 输出结果如下： ```shell *************************** 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) ``` 上一篇 [OceanBase 数据库报 timeout 或者直接 hang 住，报错 4012](https://www.oceanbase.com/knowledge-base/oceanbase-database-1000000000089878) 下一篇 [跨节点访问数据错误，错误代码 1235](https://www.oceanbase.com/knowledge-base/oceanbase-database-20000001060) ![有帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*y6ocSqN8cqsAAAAAAAAAAAAAARQnAQ)![无帮助](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*BG9IQJyLHF8AAAAAAAAAAAAAARQnAQ)![反馈](https://gw.alipayobjects.com/mdn/ob_asset/afts/img/A*eTWdQKCRKHwAAAAAAAAAAAAAARQnAQ)[AI](https://www.oceanbase.com/obi) 咨询热线