管理执行计划 - 优化器 HINT - 《OceanBase v2.2.30 官方教程》

HINT 语法
- HINT 列表
QB_NAME 介绍
HINT 一般规则
主要 HINT 行为细节
OceanBase 数据库与 MySQL，Oracle 的区别

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

HINT 语法

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

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

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

HINT 列表

HINT 名称	HINT 文本	HINT 语义
NO_REWRITE	NO_REWRITE	禁止 SQL 改写
READ_CONSISTENCY	READ_CONSISTENCY (WEAK[STRONGFROZEN])	读一致性设置（弱/强）
INDEX_HINT	INDEX_HINT ([qb_name] TBL_NAME INDEX_NAME)	设置表索引
QUERY_TIMEOUT	QUERY_TIMEOUT(INTNUM)	设置超时时间
LOG_LEVEL	LOG_LEVEL([‘]log_level[‘])	设置日志级别，当设置模块级别语句时候，以第一个单引号(‘)作为开始，第二个单引号(‘)作为结束；比如，‘DEBUG’
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 的名称
PARALLEL	PARALLEL(INTNUM)	设置分布式执行并行度
TOPK	TOPK(PRECISION MINIMUM_ROWS)	设置模糊查询的精度和最小行数。其中 PRECSION 为整型，取值范围 [0， 100]，表示模糊查询的行数百分比；MINIMUM_ROWS 为最小返回行数。

说明

QB_NAME 语法是: @NAME
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（主表）访问。如果 SQL 通过 HINT 来指定 SEL$1 的 t 走主表，SEL$2 的走索引，则反之。

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)

注意

这里因为改写后，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
  */

HINT 一般规则

对于没有指定 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 的场景可以参考规则 1 中的第一个示例。以下为同时找到两个冲突的情况的示例：

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 的指定的方式就是为了告诉数据库加入到它的考虑范围。OceanBase 数据库优化器更像是动态规划，已经考虑了所有可能，因此 HINT 告诉数据库加入到考虑范围就没有什么意义。基于这种情况，OceanBase 数据库的 HINT 更多是告诉优化器按照指定行为做，除非指定行为无法完成。

INDEX

INDEX HINT 的语法同时支持 MySQL 和 Oracle 方式。

Oracle 语法如下：

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

MySQL 语法如下：

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 语法中一个 table 只能指定一个 index，MySQL 语法中可以指定多个。但是 OceanBase 数据库中 MySQL 语法虽然支持指定多个，但是对于 USE, FORCE 只会用第一个 index 生成 path，即使 SQL 语句中没有该 index 的 filter 而导致全部扫描同时回表。即 OceanBase 数据库当前设计是认为写 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 失效。如果在优化器连接期间，按 table id 无法在 from items 中找到对应的，即可能发生改写，那么该 table 及后面的 table 指定的 JOIN 失效，前面的依然有效。

use_merge

可以指定表在 JOIN 时候使用 MERGE-JOIN 算法，语法为：

/*+ use_merge(table_name_list) */

use_merge 指定表作为右表时候使用 MERGE-JOIN。

注意

OceanBase 数据库中 MERGE-JOIN 必须有等值条件的 join-condition，因此无等值条件的两个表 JOIN，use_merge 会无效。

MERGE-JOIN 是否认为 A MERGE-JOIN B 等效于 B MERGE-JOIN A 当前并没有最后结论。按照代价模型，MERGE-JOIN 计算代价时是区分左右表的。同时考虑到区分左右表可以增加 hint 灵活性，当前 MERGE-JOIN 区分左右表，即 use_merge 仅对表作为右表的时候生效。

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) */

OceanBase 数据库与 MySQL，Oracle 的区别

OceanBase 数据库与 MySQL，Oracle，除了 HINT 理念不一致外，其他区别如下：

MySQL 5.6 版本的 INDEX HINT，如果 INDEX 不存在会报错； MySQL 会在后面版本改成不报错的方式，OceanBase 数据库使用的 MySQL 修改成不报错。
Oracle LEADING HINT 出现不存在的表时， HINT 是否生效需要做推算，其中部分情况有效，或者部分情况全部无效，导致行为出现不确定性。