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 相关参数名称、语义和语法如下表所示。

说明

• 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 可以精确定位到每一个表，也可以在某处指定任意 Query Block 的行为。TBL_NAME 中的 QB_NAME 用于定位表，在 Hint 中最前面的 QB_NAME 用于定位 Hint 作用于哪一个 query_block。

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

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 语法如下：

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，但是对于 USE 和 FORCE 方式，只会用第一个 INDEX 生成 PATH，即使 SQL 语句中没有该 INDEX 的 filter 而导致全部扫描同时回表（即 OceanBase 数据库当前设计是认为写 Hint 的人比程序更明白那条路径是更好的）。IGNORE 类型会忽略所有指定的 INDEX。USE、FORCE 方式和 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_list 中 table_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) */。