OceanBase Optimizer Hint

一般情況下，優(yōu)化器會(huì)為用戶查詢選擇最佳的執(zhí)行計(jì)劃，不需要用戶使用 HINT 指定，但在某些場(chǎng)景下，優(yōu)化器生成的執(zhí)行計(jì)劃可能不滿足用戶的要求，這時(shí)就需要用戶使用 HINT 來(lái)指定生成某種執(zhí)行計(jì)劃。

HINT 語(yǔ)法

HINT 從語(yǔ)法上看是一種特殊的 SQL 注釋，所不同的是在注釋的左標(biāo)記后（'/*' 符號(hào)）增加了一個(gè)“+”。 既然是注釋，如果服務(wù)器端無(wú)法識(shí)別 SQL 語(yǔ)句中的 HINT，優(yōu)化器會(huì)選擇忽略用戶 HINT 而使用默認(rèn)的計(jì)劃生成邏輯結(jié)構(gòu)。另外，HINT 只影響優(yōu)化器生成計(jì)劃的邏輯結(jié)構(gòu)，而不影響 SQL 語(yǔ)句的語(yǔ)義。

{DELETE|INSERT|SELECT|UPDATE|REPLACE} /*+ [hint_text] [hin_text]... */
*<span data-type="background" style="background-color: rgb(191, 191, 191);"></span>*

HINT 參數(shù)

HINT 相關(guān)參數(shù)名稱、語(yǔ)義和語(yǔ)法如下表：

QB_NAME 介紹

在 DML 語(yǔ)句中，每一個(gè) query_block 都會(huì)有一個(gè) QB_NAME(query block name)，可以用戶指定，也可以系統(tǒng)自動(dòng)生成。在用戶沒(méi)有用 HINT 指定的 QB_NAME 的時(shí)候，系統(tǒng)會(huì)按照 SEL$1、SEL$2，UPD$1，DEL$1 方式從左到右（實(shí)際也是 Resolver 的解析順序）依次生成。

通過(guò) QB_NAME 可以精確定位每一個(gè) table，也可以在一處地方指定任意 query block 的行為。在 TBL_NAME 中的 QB_NAME 用于定位 table，在 HINT 中最前面的 qb_name 用于定位 HINT 作用于哪一個(gè) query_block。

如下例所示，按照默認(rèn)規(guī)則，會(huì)為 SEL$1 中的 t 選擇 t_c1 路徑，為 SEL$2 中的 t 選擇 PRIMARY（主表）訪問(wèn)。如果 SQL 通過(guò) HINT 來(lái)指定 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)

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；

對(duì)于 HINT 可以通過(guò) EXPLAIN EXTENDED 查看 Outline Data 來(lái)學(xué)習(xí)。

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 一般規(guī)則

• 對(duì)于沒(méi)有指定 query block 的 HINT 代表作用在本 query block。如下例所示，由于 t1 在 query block 2，同時(shí)無(wú)法改寫提升到 query block 1，所以 HINT 無(wú)法生效。

  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 可以發(fā)生改寫，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 中沒(méi)有找到該 table，或者發(fā)生沖突，那么 HINT 無(wú)效。

  對(duì)于沒(méi)有找到 table 的 case 可以參考規(guī)則 1 中的第一個(gè)示例。以下示例為同時(shí)找到兩個(gè)沖突的情況：

  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:
  -------------------------------------
    /*+
    */

• 聯(lián)接方法的 HINT 中指定的 table 如果找不到，忽略該 table，其他的指定依然生效；如果優(yōu)化器不能生成指定的聯(lián)接方法，就會(huì)選擇其他方法，HINT 無(wú)效。

• 聯(lián)接順序的 HINT 中如果存在 table 無(wú)法找到，則該 HINT 完全失效。

HINT 主要語(yǔ)法

與其他數(shù)據(jù)庫(kù)的行為相比，OceanBase 數(shù)據(jù)庫(kù)優(yōu)化器是動(dòng)態(tài)規(guī)劃的，已經(jīng)考慮了所有可能的最優(yōu)路徑，HINT 主要作用是指定優(yōu)化器的行為，并按照 HINT 執(zhí)行。

INDEX HINT

INDEX HINT 的語(yǔ)法同時(shí)支持 MySQL 和 Oracle 方式。

• INDEX HINT 的 Oracle 語(yǔ)法如下：

obclient> SELECT/*+INDEX(table_name index_name) */ * FROM table_name;

• INDEX HINT 的 MySQL 語(yǔ)法如下：

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 語(yǔ)法中一個(gè)表只能指定一個(gè) INDEX，MySQL 語(yǔ)法可以指定多個(gè)。但是 OceanBase 數(shù)據(jù)庫(kù)中 MySQL 語(yǔ)法雖然支持指定多個(gè)，但是對(duì)于 USE 和 FORCE，只會(huì)用第一個(gè) INDEX 生成 PATH，即使 SQL 語(yǔ)句中沒(méi)有該 INDEX 的 filter 而導(dǎo)致全部掃描同時(shí)回表（即 OceanBase 數(shù)據(jù)庫(kù)當(dāng)前設(shè)計(jì)是認(rèn)為寫 HINT 的人比程序更明白那條路徑是更好的）。IGNORE 類型會(huì)忽略所有指定的 INDEX。USE、 FORCE 和 Oracle HINT 方式實(shí)際是一樣的，該方式的 INDEX 不存在或者處于 invalid 狀態(tài)，則 HINT 無(wú)效。對(duì)于 IGNORE 方式，如果將包括主表 (primary) 在內(nèi)的所有 INDEX 忽略，則 HINT 無(wú)效。

FULL HINT

FULL HINT 的語(yǔ)法是用于指定表使用主表掃描，語(yǔ)法如下：

/*+ FULL(table_name)*/

FULL HINT 用于指定表選擇主表掃描等價(jià)于 INDEX HINT /*+ INDEX(table_name PRIMARY)*/。

ORDERED HINT

ORDERED HINT 可以指定按照 from 后面的表的順序作為聯(lián)接順序，語(yǔ)法如下：

/*+ ORDERED*/

如果指定該 HINT 后發(fā)生改寫，那么就按照改寫后的 stmt 中 from items 的順序聯(lián)接，因?yàn)楦膶憰r(shí)候 sub_query 會(huì)在 from items 中對(duì)應(yīng)位置填放新的 table item。

LEADING HINT

LEADING HINT 可以指定表的聯(lián)接順序，語(yǔ)法如下：

/*+ LEADING(table_name_list)*/

table_name_list 中 table_name 比較特殊，其他 table_name 語(yǔ)法如下：

db_name . relation_name

relation_name

.relation_name

在 table_name_list 中 table_name 語(yǔ)法如下：

db_name . relation_name

relation_name

table_name_list 語(yǔ)法如下：

table_name
table_name_list table_name
table_name_list, table_name

LEADING HINT 為確保按照用戶指定的順序聯(lián)接檢查比較嚴(yán)格，如果發(fā)現(xiàn) HINT 指定的 table_name 不存在，LEADING HINT 失效；如果發(fā)現(xiàn) HINT 中存在重復(fù) table，LEADING HINT 失效。如果在 optimizer 聯(lián)接期間，按 table_id 無(wú)法在 from items 中找到對(duì)應(yīng)的，即可能發(fā)生改寫，那么該 table 及后面的 table 指定的 JOIN 序失效，前面的依然有效。

Use_merge

可以指定表在 JOIN 時(shí)候使用 merge-join 算法，語(yǔ)法為：/*+ USE_MERGE(table_name_list) */

使用 merge-join 將 use_merge 指定的表作為右表。

關(guān)于 merge-join 是否認(rèn)為 A merge-join B 等效于 B merge-join A 當(dāng)前并沒(méi)有最后結(jié)論。按照代價(jià)模型，merge-join 計(jì)算代價(jià)時(shí)是區(qū)分左右表的。同時(shí)考慮到區(qū)分左右表可以增加 HINT 靈活性，當(dāng)前 merge-join 區(qū)分左右表，即 use_merge 僅對(duì)表作為右表的時(shí)候生效。

Use_nl

指定表作為右表在聯(lián)接的時(shí)候使用 NESTED LOOP JOIN 算法，語(yǔ)法如下：

/*+ USE_NL(table_name_list) */

Use_hash

指定表作為右表在聯(lián)接的時(shí)候使用 HASH JOIN 算法，語(yǔ)法如下：

/*+ USE_HASH(table_name_list) */

Parallel

指定語(yǔ)句級(jí)別的并發(fā)度。當(dāng)該 HINT 指定時(shí)，會(huì)忽略系統(tǒng)變量 ob_stmt_parallel_degree 的設(shè)置。語(yǔ)法如下：

/*+ PARALLEL(4) */