codecamp

OceanBase Optimizer Hint

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

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

HINT 语法

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

{DELETE|INSERT|SELECT|UPDATE|REPLACE} /*+ [hint_text] [hin_text]... */
*<span data-type="background" style="background-color: rgb(191, 191, 191);"></span>*
注意 
如果使用 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)

设置指定表在作为右表的时候使用 NESTED LOOP 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 的 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。如下例所示,由于 t1 在 query block 2,同时无法改写提升到 query block 1,所以 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)

    如下例所示,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)
  • 如果指定 table 行为,但在本 query block 中没有找到该 table,或者发生冲突,那么 HINT 无效。

    对于没有找到 table 的 case 可以参考规则 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 中指定的 table 如果找不到,忽略该 table,其他的指定依然生效;如果优化器不能生成指定的联接方法,就会选择其他方法,HINT 无效。

  • 联接顺序的 HINT 中如果存在 table 无法找到,则该 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 语法虽然支持指定多个,但是对于 USE 和 FORCE,只会用第一个 INDEX 生成 PATH,即使 SQL 语句中没有该 INDEX 的 filter 而导致全部扫描同时回表(即 OceanBase 数据库当前设计是认为写 HINT 的人比程序更明白那条路径是更好的)。IGNORE 类型会忽略所有指定的 INDEX。USE、 FORCE 和 Oracle HINT 方式实际是一样的,该方式的 INDEX 不存在或者处于 invalid 状态,则 HINT 无效。对于 IGNORE 方式,如果将包括主表 (primary) 在内的所有 INDEX 忽略,则 HINT 无效。

FULL HINT

FULL HINT 的语法是用于指定表使用主表扫描,语法如下:

/*+ FULL(table_name)*/

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

ORDERED HINT

ORDERED HINT 可以指定按照 from 后面的表的顺序作为联接顺序,语法如下:

/*+ ORDERED*/

如果指定该 HINT 后发生改写,那么就按照改写后的 stmt 中 from items 的顺序联接,因为改写时候 sub_query 会在 from items 中对应位置填放新的 table item。

LEADING HINT

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

使用 merge-join 将 use_merge 指定的表作为右表。

注意 
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

指定表作为右表在联接的时候使用 NESTED LOOP JOIN 算法,语法如下:

/*+ USE_NL(table_name_list) */

Use_hash

指定表作为右表在联接的时候使用 HASH JOIN 算法,语法如下:

/*+ USE_HASH(table_name_list) */

Parallel

指定语句级别的并发度。当该 HINT 指定时,会忽略系统变量 ob_stmt_parallel_degree 的设置。语法如下:

/*+ PARALLEL(4) */

OceanBase 联接顺序
OceanBase 计划绑定
温馨提示
下载编程狮App,免费阅读超1000+编程语言教程
取消
确定
目录

OceanBase 控制台指南

OceanBase ODC 使用指南

OceanBase Web 版 ODC

OceanBase 客户端版 ODC

OceanBase Connector/J 开发者指南

OceanBase 什么是OceanBase Connector/J

OceanBase SQL 参考(MySQL 模式)

OceanBase SQL 参考(Oracle 模式)

OceanBase 基本元素

OceanBase 数据库对象

OceanBase 函数

OceanBase 单行函数

OceanBase 返回数字的字符串函数

OceanBase 通用比较函数

OceanBase 编码解码函数

OceanBase SQL 调优指南

OceanBase 相关协议

关闭

MIP.setData({ 'pageTheme' : getCookie('pageTheme') || {'day':true, 'night':false}, 'pageFontSize' : getCookie('pageFontSize') || 20 }); MIP.watch('pageTheme', function(newValue){ setCookie('pageTheme', JSON.stringify(newValue)) }); MIP.watch('pageFontSize', function(newValue){ setCookie('pageFontSize', newValue) }); function setCookie(name, value){ var days = 1; var exp = new Date(); exp.setTime(exp.getTime() + days*24*60*60*1000); document.cookie = name + '=' + value + ';expires=' + exp.toUTCString(); } function getCookie(name){ var reg = new RegExp('(^| )' + name + '=([^;]*)(;|$)'); return document.cookie.match(reg) ? JSON.parse(document.cookie.match(reg)[2]) : null; }