Optimizer Hint

Hint 机制可以使优化器生成某种特定的计划。

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

Hint 语法

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

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

注意

如果使用 MySQL 的 C 客户端执行带 Hint 的 SQL 语句,需要使用 -c 选项登录,否则 MySQL 客户端会将 Hint 作为注释从用户 SQL 语句中去除,导致系统无法收到用户 Hint。

Hint 参数

Hint 相关参数名称、语义和语法如下表所示。

名称

语法

语义

NO_REWRITE

NO_REWRITE

禁止 SQL 改写。

READ_CONSISTENCY

READ_CONSISTENCY (WEAK[STRONGFROZEN])

读一致性设置(弱/强)。

INDEX_HINT

/*+ INDEX(table_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])

设置是否使用计划缓存:

  • NONE:表示不使用计划缓存。

  • 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)

设置指定表在作为右表时使用 Nested Loop Join。

USE_BNL

USE_BNL([qb_name] TBL_NAME_LIST)

设置指定表在作为右表时使用 Block Nested Loop Join

USE_HASH_AGGREGATION

USE_HASH_AGGREGATION([qb_name])

设置聚合算法为 Hash。例如 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$1SEL$2UPD$1DEL$1 方式从左到右(实际也是 Resolver 的解析顺序)依次生成。

通过 QB_NAME 可以精确定位到每一个表,也可以在某处指定任意 Query Block 的行为。TBL_NAME 中的 QB_NAME 用于定位表,在 Hint 中最前面的 QB_NAME 用于定位 Hint 作用于哪一个 query_block

如下例所示,按照默认规则,会为 SEL$1 中的 t 表选择 t_c1 路径,为 SEL$2 中的 t 表选择主表(Primary)访问。如果 SQL 通过 Hint 来指定 SEL$1t 表走主表,则 SEL$2t 表走索引。

obclient>CREATE TABLE t(c1 INT, c2 INT, KEY t_c1(c1));
Query OK, 0 rows affected (0.31 sec)

obclient>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。

obclient>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 也可以写成如下方式:

obclient>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;
<==>
obclient>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;
<==>
obclient>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 来查看它的所有信息。

obclient>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。

    示例 1:由于 t1 表在 Query Block2,并且无法通过改写提升到 Query Block1,所以 Hint 无法生效。

    obclient>CREATE TABLE t1(c1 INT, c2 INT, INDEX t1_c1(c1), INDEX
     t1_c2(c2));
    Query OK, 0 rows affected (0.31 sec)
    
    obclient>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|
    ============================================================
    Outputs & filters:
    -------------------------------------
      0 - output([t.c1], [t.c2], [ta.c1], [ta.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([ta.c1], [ta.c2]), filter(nil),
          access([ta.c1], [ta.c2])
      4 - output([t1.c1], [t1.c2]), filter(nil),
          group([t1.c1]), agg_func(nil)
      5 - output([t1.c1], [t1.c2]), filter(nil),
          access([t1.c1], [t1.c2]), partitions(p0)

    示例 2:SQL 可以通过改写将 t1 表提升到 SEL$1,则 Hint 生效。

    obclient>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|
    ===============================================================
    Outputs & filters:
    -------------------------------------
      0 - output([t.c1], [t.c2], [t1.c1], [t1.c2]), filter(nil),
          conds(nil), nl_params_(nil)
      1 - output([t1.c1], [t1.c2]), filter(nil),
          access([t1.c1], [t1.c2]), partitions(p0)
      2 - 
    output([t.c1], [t.c2]), filter(nil),
          access([t.c1], [t.c2]), partitions(p0)
  • 如果指定表行为,但在本 Query Block 中没有找到该表,或者发生冲突,那么 Hint 无效。

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

    obclient>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 |
    ============================================================
    Outputs & filters:
    -------------------------------------
      0 - output([t.c1(0x7f7b7cdd3e60)], [t.c2(0x7f7b7cdd40f0)], [t.c1(0x7f7b7cdd2bd0)], [t.c2(0x7f7b7cdd2e60)]), filter(nil),
          conds(nil), nl_params_(nil), inner_get=false, self_join=false, batch_join=false
      1 - output([t.c1(0x7f7b7cdd3e60)], [t.c2(0x7f7b7cdd40f0)]), filter(nil),
          access([t.c1(0x7f7b7cdd3e60)], [t.c2(0x7f7b7cdd40f0)]), partitions(p0),
          is_index_back=true,
          range_key([t.c1(0x7f7b7cdd3e60)], [t.__pk_increment(0x7f7b7cde86e0)]), range(1,MIN ; 1,MAX),
          range_cond([t.c1(0x7f7b7cdd3e60) = 1(0x7f7b7cdd3800)])
      2 - 
    output([t.c1(0x7f7b7cdd2bd0)], [t.c2(0x7f7b7cdd2e60)]), filter(nil),
          access([t.c1(0x7f7b7cdd2bd0)], [t.c2(0x7f7b7cdd2e60)]), partitions(p0),
          is_index_back=true,
          range_key([t.c1(0x7f7b7cdd2bd0)], [t.__pk_increment(0x7f7b7cdf41b0)]), range(1,MIN ; 1,MAX),
          range_cond([t.c1(0x7f7b7cdd2bd0) = 1(0x7f7b7cdd2570)])
    
    Used Hint:
    -------------------------------------
      /*+
      */
  • 联接方法的 Hint 中指定的表如果找不到,则忽略该表,其他的指定依然生效;如果优化器不能生成指定的联接方法,就会选择其他方法,Hint 无效。

  • 联接顺序的 Hint 中如果存在表无法找到的情况,则该 Hint 完全失效。

Hint 主要语法

与其他数据库的行为相比,OceanBase 数据库优化器是动态规划的,已经考虑了所有可能的最优路径,Hint 主要作用是指定优化器的行为,并按照 Hint 执行。

INDEX Hint

INDEX Hint 的语法同时支持 MySQL 和 Oracle 模式。

  • INDEX Hint 的 Oracle 语法如下:

obclient>SELECT/*+INDEX(table_name index_name) */ * FROM table_name;
  • INDEX Hint 的 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 语法中一个表只能指定一个 INDEX,而 MySQL 语法可以指定多个。但是 OceanBase 数据库中 MySQL 语法虽然支持指定多个 INDEX,但是对于 USEFORCE 方式,只会用第一个 INDEX 生成 PATH,即使 SQL 语句中没有该 INDEXfilter 而导致全部扫描同时回表(即 OceanBase 数据库当前设计是认为写 Hint 的人比程序更明白那条路径是更好的)。IGNORE 类型会忽略所有指定的 INDEXUSEFORCE 方式和 Oracle Hint 方式实际是一样的,如果该方式的 INDEX 不存在或者处于 invalid 状态,则 Hint 无效。对于 IGNORE 方式,如果将包括主表在内的所有 INDEX 忽略,则 Hint 无效。

FULL Hint

FULL Hint 的语法是用于指定表使用主表扫描,语法为 /*+ FULL(table_name)*/

FULL Hint 用于指定表选择主表扫描等价于 INDEX Hint /*+ INDEX(table_name PRIMARY)*/

ORDERED Hint

ORDERED Hint 可以指定按照 FROM 后面的表顺序作为联接顺序,语法为 /*+ ORDERED*/

如果指定该 Hint 后发生 SQL 改写,那么就按照改写后的 stmt 中 From Items 的顺序联接,因为改写时候 sub_query 会在 From Items 中对应位置填放新的 Table Item。

LEADING Hint

LEADING Hint 可以指定表的联接顺序,语法为 /*+ LEADING(table_name_list)*/

其中,table_name_list 语法如下:

table_name
table_name_list table_name
table_name_list, table_name

table_name_listtable_name 比较特殊,语法如下:

db_name. relation_name
relation_name

其他 table_name 语法如下:

db_name. relation_name
relation_name
.relation_name

为确保按照用户指定的顺序进行联接,LEADING Hint 的检查比较严格,如果发现 Hint 指定的 table_name 不存在,LEADING Hint 失效;如果发现 Hint 中存在重复表,LEADING Hint 失效。如果在 Optimizer 联接期间,按 table_id 无法在 From Items 中找到对应的表,则可能发生改写,那么该表及后面的表指定的 JOIN 顺序失效,该表前面的 JOIN 顺序依然有效。

USE_MERGE

USE_MERGE 可以指定表在联接时候使用 Merge Join 算法,将所指定的表作为右表。语法为 /*+ USE_MERGE(table_name_list) */

注意

OceanBase 数据库中 Merge Join 必须有等值条件的 join-condition,因此无等值条件的两个表联接,use_merge 会无效。

关于 Merge Join 算法是否认为 A Merge Join B 等效于 B Merge Join A, 当前并没有最后结论。按照代价模型,Merge Join 计算代价时是区分左右表的,这也增加了 Hint 的灵活性,所以当前 Merge Join 区分左右表,即 use_merge 仅对表作为右表的时候生效。

USE_NL

USE_NL 指定表作为右表,在联接的时候使用 Nested Loop Join 算法,语法为:/*+ USE_NL(table_name_list) */

USE_HASH

USE_HASH 指定表作为右表,在联接的时候使用 Hash Join 算法,语法为:/*+ USE_HASH(table_name_list) */

PARALLEL

PARALLEL 指定语句级别的并发度。当指定该 Hint时,会忽略系统变量 ob_stmt_parallel_degree 的设置。语法为:/*+ PARALLEL(4) */