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

HINT 语法

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

  1. {DELETE|INSERT|SELECT|UPDATE|REPLACE} /*+ [hint_text] [hin_text]... */
  1. *<span data-type="background" style="background-color: rgb(191, 191, 191);">注意:如果使用mysqlc客户端执行带HINTSQL语句,需要使用-c选项登陆, 否则MySQL客户端会将HINT作为注释从用户SQL中去除,导致系统无法收到用户HINT。</span>*

HINT 列表

HINT NameHINT TextHINT 语义
NO_REWRITENO_REWRITE禁止 SQL 改写
READ_CONSISTENCYREAD_CONSISTENCY (WEAK[STRONGFROZEN])

读一致性设置(弱/强)

INDEX_HINT

/+ INDEX(table_name index_name) /

设置表索引
QUERY_TIMEOUTQUERY_TIMEOUT(INTNUM)设置超时时间
LOG_LEVELLOG_LEVEL([‘]log_level[‘])设置日志级别,当设置模块级别语句时候,以第一个单引号(‘)作为开始,第二个单引号(‘)作为结束;比如,‘DEBUG’
LEADINGLEADING([qb_name] TBL_NAME_LIST)设置连接顺序
ORDEREDORDERED设置按照 SQL 中的顺序连接
FULLFULL([qb_name] TBL_NAME)设置表访问路径为主表等价于INDEX(TBL_NAME PRIMARY)
USE_PLAN_CACHEUSE_PLAN_CACHE(NONE[DEFAULT])设置是否使用 PLAN CACHE。NONE 表示不适用 PLAN ;CAHCE,DEFAULT 表示按照其他变量设置。
ACTIVATE_BURIED_POINTACTIVATE_BURIED_POINT(INTNUM, [FIX_MODBEFORE_MODE
INTNUM, [INTNUM-INTNUM])调试用,触发内部设定的错误点
USE_MERGEUSE_MERGE([qb_name] TBL_NAME_LIST)设置指定表在作为右表的时候使用MERGE JOIN
USE_HASHUSE_HASH([qb_name] TBL_NAME_LIST)设置指定表在作为右表的时候使用HASH JOIN
NO_USE_HASHNO_USE_HASH([qb_name] TBL_NAME_LIST)设置指定表在作为右表的时候不使用 HASH JOIN
USE_NLUSE_NL([qb_name] TBL_NAME_LIST)设置指定表在作为右表的时候使用NestLoop JOIN
USE_BNLUSE_BNL([qb_name] TBL_NAME_LIST)设置指定表在作为右表的时候使用NestLoop Block JOIN
USE_HASH_AGGREGATIONUSE_HASH_AGGREGATION([qb_name])设置 aggregate 方法使用 HASH AGGREGATE,例如 HASH GROUP BY,HASH DISTINCT
NO_USE_HASH_AGGREGATIONNO_USE_HASH_AGGREGATION([qb_name])设置 aggregate 方法不使用 HASH AGGREGATE,使用 MERGE GROUP BY,MERGE DISTINCT
USE_LATE_MATERIALIZATIONUSE_LATE_MATERIALIZATION设置使用晚期物化
NO_USE_LATE_MATERIALIZATIONNO_USE_LATE_MATERIALIZATION设置不使用晚期物化
TRACE_LOGTRACE_LOG设置收集 trace 记录用于 show trace 展示
QB_NAMEQB_NAME( NAME )设置 query block 的 name
PARALLELPARALLEL(INTNUM)设置分布式执行并行度
TOPKTOPK(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 的走索引,则反之。

  1. create table t(c1 int, c2 int, key t_c1(c1));
  2. explain select *
  3. from t , (select * from t where c2 = 1) ta
  4. where t.c1 = 1\G
  5. *************************** 1. row ***************************
  6. Query Plan: ============================================================
  7. |ID|OPERATOR |NAME |EST. ROWS|COST|
  8. ------------------------------------------------------------
  9. |0 |NESTED-LOOP INNER JOIN CARTESIAN| |1 |1895|
  10. |1 | TABLE SCAN |t(t_c1)|1 |472 |
  11. |2 | TABLE SCAN |t |1 |1397|
  12. ============================================================
  13. Outputs & filters:
  14. -------------------------------------
  15. 0 - output([t.c1], [t.c2], [t.c1], [t.c2]), filter(nil),
  16. conds(nil), nl_params_(nil)
  17. 1 - output([t.c1], [t.c2]), filter(nil),
  18. access([t.c1], [t.c2]), partitions(p0)
  19. 2 - output([t.c2], [t.c1]), filter([t.c2 = 1]),
  20. access([t.c2], [t.c1]), partitions(p0)

注意:

改写后,SEL$2 被提升到 SEL$1 所以这里不用指定 HINT 作用的 query block。

  1. explain select/*+INDEX(t@SEL$1 PRIMARY) INDEX(t@SEL$2 t_c1)*/ *
  2. from t , (select * from t where c2 = 1) ta
  3. where t.c1 = 1\G
  4. *************************** 1. row ***************************
  5. Query Plan: =============================================================
  6. |ID|OPERATOR |NAME |EST. ROWS|COST |
  7. -------------------------------------------------------------
  8. |0 |NESTED-LOOP INNER JOIN CARTESIAN| |1 |16166|
  9. |1 | TABLE SCAN |t |1 |1397 |
  10. |2 | TABLE SCAN |t(t_c1)|1 |14743|
  11. =============================================================
  12. Outputs & filters:
  13. -------------------------------------
  14. 0 - output([t.c1], [t.c2], [t.c1], [t.c2]), filter(nil),
  15. conds(nil), nl_params_(nil)
  16. 1 - output([t.c1], [t.c2]), filter([t.c1 = 1]),
  17. access([t.c1], [t.c2]), partitions(p0)
  18. 2 - output([t.c2], [t.c1]), filter([t.c2 = 1]),
  19. access([t.c2], [t.c1]), partitions(p0)

此例中 SQL 也可以写成如下方式:

  1. 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
  2. <==>
  3. 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
  4. <==>
  5. 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 来学习。

  1. explain extended select *
  2. from t , (select * from t where c2 = 1) ta
  3. where t.c1 = 1\G
  4. *************************** 1. row ***************************
  5. Query Plan: ============================================================
  6. |ID|OPERATOR |NAME |EST. ROWS|COST|
  7. ------------------------------------------------------------
  8. |0 |NESTED-LOOP INNER JOIN CARTESIAN| |1 |1895|
  9. |1 | TABLE SCAN |t(t_c1)|1 |472 |
  10. |2 | TABLE SCAN |t |1 |1397|
  11. ============================================================
  12. Used Hint:
  13. -------------------------------------
  14. /*+
  15. */
  16. Outline Data:
  17. -------------------------------------
  18. /*+
  19. BEGIN_OUTLINE_DATA
  20. USE_NL(@"SEL$1" "test.t"@"SEL$2")
  21. LEADING(@"SEL$1" "test.t"@"SEL$1" "test.t"@"SEL$2")
  22. INDEX(@"SEL$1" "test.t"@"SEL$1" "t_c1")
  23. FULL(@"SEL$2" "test.t"@"SEL$2")
  24. END_OUTLINE_DATA
  25. */

HINT 一般规则

  • 对于没有指定 query block 的 HINT 表示作用在本 query block。
    如下例所示,由于 t1 在query block 2,同时无法改写提升到 query block 1,所以 HINT 无法生效。
  1. explain select/*+index(t1 t1_c2)*/ *
  2. from t, (select * from t1 group by c1) ta
  3. where t.c1 = 1\G
  4. *************************** 1. row ***************************
  5. Query Plan: ============================================================
  6. |ID|OPERATOR |NAME |EST. ROWS|COST|
  7. ------------------------------------------------------------
  8. |0 |NESTED-LOOP INNER JOIN CARTESIAN| |666 |5906|
  9. |1 | TABLE SCAN |t(t_c1)|1 |472 |
  10. |2 | SUBPLAN SCAN |ta |666 |5120|
  11. |3 | HASH GROUP BY | |666 |4454|
  12. |4 | TABLE SCAN |t1 |1000 |1397|
  13. ============================================================

如下例所示,SQL 可以发生改写,t1 提升到 SEL$1,则 HINT 生效。

  1. explain select/*+index(t1 t1_c2)*/ *
  2. from t, (select * from t1) ta
  3. where t.c1 = 1\G
  4. *************************** 1. row ***************************
  5. Query Plan: ===============================================================
  6. |ID|OPERATOR |NAME |EST. ROWS|COST |
  7. ---------------------------------------------------------------
  8. |0 |NESTED-LOOP INNER JOIN CARTESIAN| |1000 |15674|
  9. |1 | TABLE SCAN |t(t_c1) |1 |472 |
  10. |2 | TABLE SCAN |t1(t1_c2)|1000 |14743|
  11. ===============================================================
  • 如果指定 table 行为,但在本 query block 中没有找到该 table,或者冲突,那么 HINT 无效。

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

  1. explain extended select/*+index(t primary)*/ *
  2. from t , (select * from t where c1 = 1) ta
  3. where t.c1 = 1\G
  4. *************************** 1. row ***************************
  5. Query Plan: ============================================================
  6. |ID|OPERATOR |NAME |EST. ROWS|COST|
  7. ------------------------------------------------------------
  8. |0 |NESTED-LOOP INNER JOIN CARTESIAN| |1 |970 |
  9. |1 | TABLE SCAN |t(t_c1)|1 |472 |
  10. |2 | TABLE SCAN |t(t_c1)|1 |472 |
  11. ============================================================
  12. Used Hint:
  13. -------------------------------------
  14. /*+
  15. */
  • 连接方法 hint 中指定的 table 如果找不到,忽略该 table,其他的指定依然生效;如果优化器不能生成指定的连接方法,就会选择其他方法,HINT 无效。
  • 连接顺序 HINT 如果存在 table 无法找到,则该 HINT完全失效。

主要 HINT 行为细节

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

index

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

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

MySQL 语法如下:

  1. tbl_name [[AS] alias] [index_hint_list]
  2. index_hint_list:
  3. index_hint [, index_hint] ...
  4. index_hint:
  5. USE {INDEX|KEY}
  6. [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  7. | IGNORE {INDEX|KEY}
  8. [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
  9. | FORCE {INDEX|KEY}
  10. [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
  11. index_list:
  12. index_name [, index_name] ...

Oracle 语法中 一个 table 只能指定一个 index,MySQL 语法中可以指定多个。但是 OB 中 MySQL 语法虽然支持指定多个,但是对于 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 语法如下:

  1. db_name . relation_name
  2. relation_name
  3. .relation_name

在 table_name_list 中 table_name 语法如下:

  1. db_name . relation_name
  2. relation_name

table_name_list 语法如下:

  1. table_name
  2. table_name_list table_name
  3. 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 的区别

OB 与 MySQL,Oracle,除了 HINT 理念不一致外,其他区别如下:

  • MySQL 5.6 版本的 index hint, 如果 index 不存在会报错; MySQL 会在后面版本改成不报错的方式,OB 使用的 MySQL 修改成不报错;
  • Oracle leading hint 出现不存在的表时, HINT 是否生效需要做推算,其中部分情况有效,或者部分情况全部无效,导致行为出现不确定性。