人为控制优化器行为 - 优化器HINT - 《OceanBase v1.4 官方教程》

HINT是一种机制，通过HINT可以使优化器生成某种特定的计划。一般情况下，优化器会为用户查询选择最佳的执行计划，不需要用户使用HINT指定，但在某些场景下，优化器生成的执行计划可能不满足用户的要求，这时就需要用户使用HINT来显式指定生成某种执行计划。

HINT语法

{DELETE|INSERT|SELECT|UPDATE|REPLACE} /*+ [hint_text] [hin_text]... */

HINT从语法上看是一种特殊的SQL注释, 所不同的是在注释的左标记后（’/*‘符号）增加了一个“+”。既然是注释，如果Server端无法识别SQL语句中的HINT，优化器会选择忽略用户HINT而使用默认的计划生成逻辑。另外，需要指明，HINT只影响优化器生成计划的逻辑，而不影响SQL语句的语义。

*<span data-type="background" style="background-color: rgb(191, 191, 191);">注意：如果使用mysql的c客户端执行带HINT的SQL语句，需要使用-c选项登陆， 否则MySQL客户端会将HINT作为注释从用户SQL中去除，导致系统无法收到用户HINT。</span>*

Hints列表

Hint Name	Hint Text	Hint 语义
NO_REWRITE	NO_REWRITE	禁止SQL改写
READ_CONSISTENCY	READ_CONSISTENCY (WEAK[STRONG	FROZEN])
INDEX_HINT	INDEX_HINT ([qb_name] TBL_NAME INDEX_NAME)	设置表索引
QUERY_TIMEOUT	QUERY_TIMEOUT(INTNUM)	设置超时时间
LOG_LEVEL	LOG_LEVEL([‘]log_level[‘])	设置日志级别，当设置模块级别语句时候，加’’
LEADING	LEADING([qb_name] TBL_NAME_LIST)	设置连接顺序
ORDERED	ORDERED	设置按照SQL中的顺序连接
FULL	FULL([qb_name] TBL_NAME)	设置表访问路径为主表等价于INDEX(TBL_NAME PRIMARY)
USE_PLAN_CACHE	USE_PLAN_CACHE(NONE[DEFAULT])	设置是否使用PLAN CACHE，NONE不适用PLAN CAHCE,DEFAULT按照其他变量设置
ACTIVATE_BURIED_POINT	ACTIVATE_BURIED_POINT(INTNUM, [FIX_MOD	BEFORE_MODE
INTNUM, [INTNUM	-INTNUM])	调试用，触发内部设定的错误点
USE_MERGE	USE_MERGE([qb_name] TBL_NAME_LIST)	设置指定表在作为右表的时候使用MERGE JOIN
USE_HASH	USE_HASH([qb_name] TBL_NAME_LIST)	设置指定表在作为右表的时候使用HASH JOIN
NO_USE_HASH	NO_USE_HASH([qb_name] TBL_NAME_LIST)	设置指定表在作为右表的时候不使用HASH JOIN
USE_NL	USE_NL([qb_name] TBL_NAME_LIST)	设置指定表在作为右表的时候使用NestLoop JOIN
USE_BNL	USE_BNL([qb_name] TBL_NAME_LIST)	设置指定表在作为右表的时候使用NestLoop Block JOIN
USE_HASH_AGGREGATION	USE_HASH_AGGREGATION([qb_name])	设置aggregate方法使用HASH AGGREGATE，例如HASH GROUP BY，HASH DISTINCT
NO_USE_HASH_AGGREGATION	NO_USE_HASH_AGGREGATION([qb_name])	设置aggregate方法不使用HASH AGGREGATE，使用MERGE GROUP BY，MERGE DISTINCT
USE_LATE_MATERIALIZATION	USE_LATE_MATERIALIZATION	设置使用晚期物化
NO_USE_LATE_MATERIALIZATION	NO_USE_LATE_MATERIALIZATION	设置不使用晚期物化
TRACE_LOG	TRACE_LOG	设置收集trace记录用于show trace展示
QB_NAME	QB_NAME( NAME )	设置query block的name
PARALLEL	PARALLEL(INTNUM)	设置分布式执行并行度
TOPK	TOPK(PRECISION MINIMUM_ROWS)	设置模糊查询的精度和最小行数。其中PRECSION为整型，取值范围【0， 100】，表示模糊查询的行数百分比；MINIMUM_ROWS为最小返回行数

说明

说明：TBL_NAME语法：[db_name.]relation_name [qb_name]

QB_NAME介绍

在DML语句中，每一个query_block都会有一个QB_NAME(query block name)，可以用户指定，也可以系统自动生成。在用户没有用Hint指定的QB_NAME的时候，系统会按照SEL$1、SEL$2，UPD$1，DEL$1方式从左到右（实际也是resolver解析顺序）依次生成。

有了QB_NAME，可以精确的定位每一个table，也可以在一处地方指定任意query block的行为。在TBL_NAME中的QB_NAME用于定位table，在hint中最前面的qb_name用于定位hint作用于哪一个query_block。

如下示例，按照默认规则，会为SEL$1中的t选择t_c1路径，为SEL$2中的t选择PRIMARY（主表）访问。

create table t(c1 int, c2 int, key t_c1(c1));
explain select * 
        from t , (select * from t where c2 = 1) ta 
        where t.c1 = 1\G
*************************** 1. row ***************************
Query Plan: ============================================================
|ID|OPERATOR                        |NAME   |EST. ROWS|COST|
------------------------------------------------------------
|0 |NESTED-LOOP INNER JOIN CARTESIAN|       |1        |1895|
|1 | TABLE SCAN                     |t(t_c1)|1        |472 |
|2 | TABLE SCAN                     |t      |1        |1397|
============================================================
Outputs & filters:
-------------------------------------
  0 - output([t.c1], [t.c2], [t.c1], [t.c2]), filter(nil),
      conds(nil), nl_params_(nil)
  1 - output([t.c1], [t.c2]), filter(nil),
      access([t.c1], [t.c2]), partitions(p0)
  2 - output([t.c2], [t.c1]), filter([t.c2 = 1]),
      access([t.c2], [t.c1]), partitions(p0)

上面的SQL如果通过HINT来指定SEL$1的t走主表，SEL$2的走索引，颠倒一下。

说明

注意：这里因为改写后，SEL$2被提升到SEL$1所以这里不用指定HINT作用的query block。

explain select/*+INDEX(t@SEL$1 PRIMARY) INDEX(t@SEL$2 t_c1)*/ * 
        from t , (select * from t where c2 = 1) ta 
        where t.c1 = 1\G
*************************** 1. row ***************************
Query Plan: =============================================================
|ID|OPERATOR                        |NAME   |EST. ROWS|COST |
-------------------------------------------------------------
|0 |NESTED-LOOP INNER JOIN CARTESIAN|       |1        |16166|
|1 | TABLE SCAN                     |t      |1        |1397 |
|2 | TABLE SCAN                     |t(t_c1)|1        |14743|
=============================================================
Outputs & filters:
-------------------------------------
  0 - output([t.c1], [t.c2], [t.c1], [t.c2]), filter(nil),
      conds(nil), nl_params_(nil)
  1 - output([t.c1], [t.c2]), filter([t.c1 = 1]),
      access([t.c1], [t.c2]), partitions(p0)
  2 - output([t.c2], [t.c1]), filter([t.c2 = 1]),
      access([t.c2], [t.c1]), partitions(p0)

上面的SQL也可以写成如下方式：

select/*+INDEX(t@SEL$1 PRIMARY) INDEX(@SEL$2 t@SEL$2 t_c1)*/ * from t , (select * from t where c2 = 1) ta where t.c1 = 1\G
<==>
select/*+INDEX(t@SEL$1 PRIMARY)*/ * from t , (select/*+index(t@SEL$2 t_c1)*/ * from t where c2 = 1) ta where t.c1 = 1\G
<==>
select/*+INDEX(@SEL$1 t@SEL$1 PRIMARY) INDEX(@SEL$2 t@SEL$2 t_c1)*/ * from t , (select * from t where c2 = 1) ta where t.c1 = 1\G

对于hint的学习可以通过explain extended查看Outline Data来学习。

explain extended select * 
                 from t , (select * from t where c2 = 1) ta 
                 where t.c1 = 1\G
*************************** 1. row ***************************
Query Plan: ============================================================
|ID|OPERATOR                        |NAME   |EST. ROWS|COST|
------------------------------------------------------------
|0 |NESTED-LOOP INNER JOIN CARTESIAN|       |1        |1895|
|1 | TABLE SCAN                     |t(t_c1)|1        |472 |
|2 | TABLE SCAN                     |t      |1        |1397|
============================================================
Used Hint:
-------------------------------------
  /*+
  */
Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      USE_NL(@"SEL$1" "test.t"@"SEL$2")
      LEADING(@"SEL$1" "test.t"@"SEL$1" "test.t"@"SEL$2")
      INDEX(@"SEL$1" "test.t"@"SEL$1" "t_c1")
      FULL(@"SEL$2" "test.t"@"SEL$2")
      END_OUTLINE_DATA
  */

Hints一般规则

对于没有指定query block的hint代表作用在本query block。由于t1在query block 2，同时无法改写提升到query block 1，所以hint无法生效。

explain select/*+index(t1 t1_c2)*/ * 
        from t, (select * from t1  group by c1) ta 
        where t.c1 = 1\G
*************************** 1. row ***************************
Query Plan: ============================================================
|ID|OPERATOR                        |NAME   |EST. ROWS|COST|
------------------------------------------------------------
|0 |NESTED-LOOP INNER JOIN CARTESIAN|       |666      |5906|
|1 | TABLE SCAN                     |t(t_c1)|1        |472 |
|2 | SUBPLAN SCAN                   |ta     |666      |5120|
|3 |  HASH GROUP BY                 |       |666      |4454|
|4 |   TABLE SCAN                   |t1     |1000     |1397|
============================================================

如下SQL可以发生改写，t1提升到SEL$1，则hint生效。


explain select/*+index(t1 t1_c2)*/ * 
        from t, (select * from t1) ta
        where t.c1 = 1\G
*************************** 1. row ***************************
Query Plan: ===============================================================
|ID|OPERATOR                        |NAME     |EST. ROWS|COST |
---------------------------------------------------------------
|0 |NESTED-LOOP INNER JOIN CARTESIAN|         |1000     |15674|
|1 | TABLE SCAN                     |t(t_c1)  |1        |472  |
|2 | TABLE SCAN                     |t1(t1_c2)|1000     |14743|
===============================================================

如果指定table行为，但在本query block中没有找到该table，或者冲突，那么hint无效。

对于没有找到table的case可以参考规则1中的第一个case。以下示例为同时找到两个冲突的情况：

explain extended select/*+index(t primary)*/ * 
                 from t , (select * from t where c1 = 1) ta 
                 where t.c1 = 1\G
*************************** 1. row ***************************
Query Plan: ============================================================
|ID|OPERATOR                        |NAME   |EST. ROWS|COST|
------------------------------------------------------------
|0 |NESTED-LOOP INNER JOIN CARTESIAN|       |1        |970 |
|1 | TABLE SCAN                     |t(t_c1)|1        |472 |
|2 | TABLE SCAN                     |t(t_c1)|1        |472 |
============================================================
Used Hint:
-------------------------------------
  /*+
  */

连接方法Hint中指定的table如果找不到，忽略该table，其他的指定依然生效；如果优化器不能生成指定的连接方法，就会选择其他方法，HINT无效
连接顺序Hint如果存在table无法找到，则该hint完全失效

主要Hint行为细节介绍

Hint是为了告诉优化器考虑hint中的方式，其他数据库的行为更像贪心算法，不会考虑全部可能的路径最优，hint的指定的方式就是为了告诉数据库加入到它的考虑范围。OB优化器更像是动态规划，已经考虑了所有可能，因此hint告诉数据库加入到考虑范围就没有什么意义。基于这种情况，OB的hint更多是告诉优化器按照指定行为做，除非指定行为无法完成。

index

Index hint的语法同时支持MySQL和Oracle方式，语法如下：Oracle syntax:

select /*+ index(table_name index_name) */ * from table_name;

MySQL syntax:

tbl_name [[AS] alias] [index_hint_list]
index_hint_list:
index_hint [, index_hint] ...
index_hint:
USE {INDEX|KEY}
  [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | IGNORE {INDEX|KEY}
  [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
  | FORCE {INDEX|KEY}
  [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
index_list:
index_name [, index_name] ...

Oracle syntax一个table只能指定一个index，MySQL syntax可以指定多个。但是OB中MySQL syntax虽然支持指定多个，但是对于USE, FORCE只会用第一个Index生成PATH，即使SQL语句中没有该Index的filter而导致全部扫描同时回表。即OB当前设计是认为写hint的人比程序更明白那条路径是更好的。IGNORE类型会忽略所有指定的index。USE、 FORCE和Oracle hint方式，实际是一样的，该方式Index不存在或者处于invalid状态，hint无效。IGNORE方式，如果将包括主表(‘primary’)在内的所有Index忽略，则hint无效。

full

Full hint的语法是用于指定表使用主表扫描，语法如下：/*+ full(table_name)*/Full hint用于指定表选择主表扫描等价于index hint /*+ index(table_name primary)*/。

ordered

Ordered hint可以指定按照from后面的表的顺序作为连接顺序，语法：/*+ ordered*/如果指定该hint后发生改写，那么就按照改写后的stmt中from items的顺序连接，因为改写时候sub_query会在from items中对应位置填放新的table item。

leading

leading hint可以指定表的连接顺序，语法:/*+ leading(table_name_list)*/table_name_list中table_name比较特殊，其他地方table_name语法为

db_name . relation_name
relation_name
.relation_name

在table_name_list中table_name语法为

db_name . relation_name
relation_name

table_name_list语法为

table_name
table_name_list table_name
table_name_list, table_name

leading hint为确保按照用户指定的顺序连接检查比较严格，如果发现hint指定的table_name不存在，leading hint失效；如果发现hint中存在重复table，leading hint失效。如果在optimizer连接期间，按table id无法在from items中找到对应的，即可能发生改写，那么该table及后面的table指定的join序失效，前面的依然有效。

use_merge

可以指定表在join时候使用MERGE-JOIN算法，语法为/*+ use_merge(table_name_list) */use_merge指定表作为右表时候使用MERGE-JOIN。注：OB中MERGE-JOIN必须有等值条件的join-condition，因此无等值条件的两个表join，use_merge会无效。MERGE-JOIN是否认为A MERGE-JOIN B等效于B MERGE-JOIN A当前并没有最后结论。按照代价模型，MERGE-JOIN计算代价时是区分左右表的。同时考虑到区分左右表可以增加hint灵活性，当前merge-join区分左右表，即use_merge仅对表作为右表的时候生效。测试Oracle也是作为右表时候生效。

use_nl

指定表作为右表在join的时候使用NL-JOIN算法，语法：/*+ use_nl(table_name_list) */

use_hash

指定表作为右表在join的时候使用HASH-JOIN算法，语法：/*+ use_hash(table_name_list) */

parallel

指定语句级别的并发度。当该hint指定时，会忽略系统变量ob_stmt_parallel_degree的设置。/*+ parallel(4) */

OB与MySQL，Oracle不一致的地方

除了在’OceanBase部分基础行为’中说的，hint理念不一致外，其他细节有：

MySQL 5.6版本 index hint如果Index不存在会报错；这一点MySQL会在后面也改成不报错的方式，集团使用的MySQL修改成不报错。
Oracle leading hint，出现不存在的表时候hint是否生效会做推算，导致行为不确定，部分情况有效，部分情况全部无效。