建站
咨詢
售后
建站咨詢
新聞中心
HINT 機制可以使優(yōu)化器生成某種特定的計劃。
公司主營業(yè)務：做網(wǎng)站、成都網(wǎng)站制作、移動網(wǎng)站開發(fā)等業(yè)務。幫助企業(yè)客戶真正實現(xiàn)互聯(lián)網(wǎng)宣傳，提高企業(yè)的競爭能力。創(chuàng)新互聯(lián)是一支青春激揚、勤奮敬業(yè)、活力青春激揚、勤奮敬業(yè)、活力澎湃、和諧高效的團隊。公司秉承以“開放、自由、嚴謹、自律”為核心的企業(yè)文化，感謝他們對我們的高要求，感謝他們從不同領域給我們帶來的挑戰(zhàn)，讓我們激情的團隊有機會用頭腦與智慧不斷的給客戶帶來驚喜。創(chuàng)新互聯(lián)推出綏棱免費做網(wǎng)站回饋大家。
一般情況下，優(yōu)化器會為用戶查詢選擇最佳的執(zhí)行計劃，不需要用戶使用 HINT 指定，但在某些場景下，優(yōu)化器生成的執(zhí)行計劃可能不滿足用戶的要求，這時就需要用戶使用 HINT 來指定生成某種執(zhí)行計劃。
HINT 語法
HINT 從語法上看是一種特殊的 SQL 注釋，所不同的是在注釋的左標記后（'/*' 符號）增加了一個“+”。 既然是注釋，如果服務器端無法識別 SQL 語句中的 HINT，優(yōu)化器會選擇忽略用戶 HINT 而使用默認的計劃生成邏輯結構。另外，HINT 只影響優(yōu)化器生成計劃的邏輯結構，而不影響 SQL 語句的語義。
{DELETE|INSERT|SELECT|UPDATE|REPLACE} /*+ [hint_text] [hin_text]... */
**注意
如果使用 MySQL 的 C 客戶端執(zhí)行帶 HINT 的 SQL 語句，需要使用 -c 選項登陸，否則 MySQL 客戶端會將 HINT 作為注釋從用戶 SQL 中去除，導致系統(tǒng)無法收到用戶 HINT。
HINT 參數(shù)
HINT 相關參數(shù)名稱、語義和語法如下表：
|
名稱 |
語法 |
語義 |
|---|---|---|
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) |
設置聯(lián)接順序。 |
ORDERED |
ORDERED |
設置按照 SQL 中的順序進行聯(lián)接。 |
FULL |
FULL([qb_name] TBL_NAME) |
設置表訪問路徑為主表等價于 INDEX(TBL_NAME PRIMARY)。 |
USE_PLAN_CACHE |
USE_PLAN_CACHE(NONE[DEFAULT]) |
設置是否使用計劃緩存：
|
ACTIVATE_BURIED_POINT |
ACTIVATE_BURIED_POINT(INTNUM, [FIX_MOD | BEFORE_MODE], INTNUM, [INTNUM | -INTNUM]) |
調試用，觸發(fā)內部設定的錯誤點。 |
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) |
設置分布式執(zhí)行并行度。 |
TOPK |
TOPK(PRECISION MINIMUM_ROWS) |
設置模糊查詢的精度和最小行數(shù)。其中 PRECSION 為整型，取值范圍[0， 100]，表示模糊查詢的行數(shù)百分比；MINIMUM_ROWS 為最小返回行數(shù)。 |
說明
- qb_name 語法是:
@NAME- TBL_NAME 語法是:
[db_name.]relation_name [qb_name]
QB_NAME 介紹
在 DML 語句中，每一個 query_block 都會有一個 QB_NAME(query block name)，可以用戶指定，也可以系統(tǒng)自動生成。在用戶沒有用 HINT 指定的 QB_NAME 的時候，系統(tǒng)會按照 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。
如下例所示，按照默認規(guī)則，會為 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 一般規(guī)則
-
對于沒有指定 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 可以發(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 中沒有找到該 table，或者發(fā)生沖突，那么 HINT 無效。
對于沒有找到 table 的 case 可以參考規(guī)則 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: ------------------------------------- /*+ */
-
聯(lián)接方法的 HINT 中指定的 table 如果找不到，忽略該 table，其他的指定依然生效；如果優(yōu)化器不能生成指定的聯(lián)接方法，就會選擇其他方法，HINT 無效。
-
聯(lián)接順序的 HINT 中如果存在 table 無法找到，則該 HINT 完全失效。
HINT 主要語法
與其他數(shù)據(jù)庫的行為相比，OceanBase 數(shù)據(jù)庫優(yōu)化器是動態(tài)規(guī)劃的，已經(jīng)考慮了所有可能的最優(yōu)路徑，HINT 主要作用是指定優(yōu)化器的行為，并按照 HINT 執(zhí)行。
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 數(shù)據(jù)庫中 MySQL 語法雖然支持指定多個，但是對于 USE 和 FORCE，只會用第一個 INDEX 生成 PATH，即使 SQL 語句中沒有該 INDEX 的 filter 而導致全部掃描同時回表（即 OceanBase 數(shù)據(jù)庫當前設計是認為寫 HINT 的人比程序更明白那條路徑是更好的）。IGNORE 類型會忽略所有指定的 INDEX。USE、 FORCE 和 Oracle HINT 方式實際是一樣的，該方式的 INDEX 不存在或者處于 invalid 狀態(tài)，則 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 后面的表的順序作為聯(lián)接順序，語法如下：
/*+ ORDERED*/
如果指定該 HINT 后發(fā)生改寫，那么就按照改寫后的 stmt 中 from items 的順序聯(lián)接，因為改寫時候 sub_query 會在 from items 中對應位置填放新的 table item。
LEADING HINT
LEADING HINT 可以指定表的聯(lián)接順序，語法如下：
/*+ 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_nametable_name_list 語法如下：
table_name
table_name_list table_name
table_name_list, table_nameLEADING HINT 為確保按照用戶指定的順序聯(lián)接檢查比較嚴格，如果發(fā)現(xiàn) HINT 指定的 table_name 不存在，LEADING HINT 失效；如果發(fā)現(xiàn) HINT 中存在重復 table，LEADING HINT 失效。如果在 optimizer 聯(lián)接期間，按 table_id 無法在 from items 中找到對應的，即可能發(fā)生改寫，那么該 table 及后面的 table 指定的 JOIN 序失效，前面的依然有效。
Use_merge
可以指定表在 JOIN 時候使用 merge-join 算法，語法為：/*+ USE_MERGE(table_name_list) */
使用 merge-join 將 use_merge 指定的表作為右表。
注意
OceanBase 數(shù)據(jù)庫中 merge-join 必須有等值條件的 join-condition，因此無等值條件的兩個表聯(lián)接，use_merge 會無效。
關于 merge-join 是否認為 A merge-join B 等效于 B merge-join A 當前并沒有最后結論。按照代價模型，merge-join 計算代價時是區(qū)分左右表的。同時考慮到區(qū)分左右表可以增加 HINT 靈活性，當前 merge-join 區(qū)分左右表，即 use_merge 僅對表作為右表的時候生效。
Use_nl
指定表作為右表在聯(lián)接的時候使用 NESTED LOOP JOIN 算法，語法如下：
/*+ USE_NL(table_name_list) */
Use_hash
指定表作為右表在聯(lián)接的時候使用 HASH JOIN 算法，語法如下：
/*+ USE_HASH(table_name_list) */
Parallel
指定語句級別的并發(fā)度。當該 HINT 指定時，會忽略系統(tǒng)變量 ob_stmt_parallel_degree 的設置。語法如下：
/*+ PARALLEL(4) */
新聞名稱：創(chuàng)新互聯(lián)OceanBase教程：OceanBaseOptimizerHint
文章分享：http://www.5511xx.com/article/cdcijdd.html
