優化器提示適用於不同的範圍層級

下表總結了可用的最佳化器提示、它們影響的最佳化器策略，以及它們適用的範圍。 稍後將提供更多詳細資訊。

停用最佳化會阻止最佳化器使用它。 啟用最佳化意味著最佳化器可以自由使用該策略 (如果它適用於語句執行)，而不是最佳化器必須使用它。

MySQL 支援 SQL 語句中的註解，如第 11.7 節，「註解」所述。 最佳化器提示必須在 /*+ ... */ 註解中指定。 也就是說，最佳化器提示使用 /* ... */ C 樣式註解語法的變體，在 /* 註解開啟序列之後加上 + 字元。 範例

/*+ BKA(t1) */
/*+ BNL(t1, t2) */
/*+ NO_RANGE_OPTIMIZATION(t4 PRIMARY) */
/*+ QB_NAME(qb2) */

在 + 字元之後允許空白字元。

剖析器會識別 SELECT、UPDATE、INSERT、REPLACE 和 DELETE 陳述式的初始關鍵字之後的最佳化器提示註解。 在這些情況下允許提示

一個提示註解可以包含多個提示，但一個查詢區塊不能包含多個提示註解。 這是有效的

SELECT /*+ BNL(t1) BKA(t2) */ ...

但這是無效的

SELECT /*+ BNL(t1) */ /* BKA(t2) */ ...

當提示註解包含多個提示時，可能會出現重複和衝突。 適用以下一般準則。 針對特定提示類型，可能適用其他規則，如提示描述中所述。

查詢區塊名稱是識別碼，並遵循關於哪些名稱有效以及如何引用它們的常見規則 (請參閱第 11.2 節，「結構描述物件名稱」)。

提示名稱、查詢區塊名稱和策略名稱不區分大小寫。 對於資料表和索引名稱的參照遵循常見的識別碼區分大小寫規則 (請參閱第 11.2.3 節，「識別碼區分大小寫」)。

聯結順序提示會影響最佳化器聯結資料表的順序。

JOIN_FIXED_ORDER 提示的語法

hint_name([@query_block_name])

其他聯結順序提示的語法

hint_name([@query_block_name] tbl_name [, tbl_name] ...)
hint_name(tbl_name[@query_block_name] [, tbl_name[@query_block_name]] ...)

此語法參照下列術語

範例

SELECT
/*+ JOIN_PREFIX(t2, t5@subq2, t4@subq1)
    JOIN_ORDER(t4@subq1, t3)
    JOIN_SUFFIX(t1) */
COUNT(*) FROM t1 JOIN t2 JOIN t3
           WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
             AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);

提示會控制合併到外部查詢區塊的半聯結資料表的行為。 如果子查詢 subq1 和 subq2 轉換為半聯結，則資料表 t4@subq1 和 t5@subq2 會合併到外部查詢區塊。 在此情況下，外部查詢區塊中指定的提示會控制 t4@subq1、t5@subq2 資料表的行為。

最佳化器會根據下列原則解析聯結順序提示

表格級別的提示會影響

這些提示類型適用於特定表格，或查詢區塊中的所有表格。

表格級別提示的語法

hint_name([@query_block_name] [tbl_name [, tbl_name] ...])
hint_name([tbl_name@query_block_name [, tbl_name@query_block_name] ...])

此語法參照下列術語

範例

SELECT /*+ NO_BKA(t1, t2) */ t1.* FROM t1 INNER JOIN t2 INNER JOIN t3;
SELECT /*+ NO_BNL() BKA(t1) */ t1.* FROM t1 INNER JOIN t2 INNER JOIN t3;
SELECT /*+ NO_MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt;

表格級別提示適用於從先前表格接收記錄的表格，而不是傳送者表格。考慮這個陳述式：

SELECT /*+ BNL(t2) */ FROM t1, t2;

如果優化器選擇先處理 t1，它會透過緩衝來自 t1 的資料列，然後才開始從 t2 讀取，來對 t2 套用區塊巢狀迴圈聯接。如果優化器改為選擇先處理 t2，則提示沒有作用，因為 t2 是傳送者表格。

對於 MERGE 和 NO_MERGE 提示，以下優先順序規則適用：

索引級別的提示會影響優化器針對特定表格或索引使用的索引處理策略。這些提示類型會影響索引條件下推 (ICP)、多範圍讀取 (MRR)、索引合併和範圍優化（請參閱第 10.2.1 節，〈優化 SELECT 陳述式〉）。

索引級別提示的語法

hint_name([@query_block_name] tbl_name [index_name [, index_name] ...])
hint_name(tbl_name@query_block_name [index_name [, index_name] ...])

此語法參照下列術語

範例

SELECT /*+ INDEX_MERGE(t1 f3, PRIMARY) */ f2 FROM t1
  WHERE f1 = 'o' AND f2 = f3 AND f3 <= 4;
SELECT /*+ MRR(t1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
  FROM t3 WHERE f1 > 30 AND f1 < 33;
INSERT INTO t3(f1, f2, f3)
  (SELECT /*+ NO_ICP(t2) */ t2.f1, t2.f2, t2.f3 FROM t1,t2
   WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1
   AND t1.f2 AND t2.f2 + 1 >= t1.f1 + 1);
SELECT /*+ SKIP_SCAN(t1 PRIMARY) */ f1, f2
  FROM t1 WHERE f2 > 40;

以下範例使用索引合併提示，但其他索引層級提示遵循相同的原則，關於提示忽略以及最佳化工具提示相對於 optimizer_switch 系統變數或索引提示的優先順序。

假設表格 t1 有欄位 a、b、c 和 d；且在 a、b 和 c 上存在名為 i_a、i_b 和 i_c 的索引

SELECT /*+ INDEX_MERGE(t1 i_a, i_b, i_c)*/ * FROM t1
  WHERE a = 1 AND b = 2 AND c = 3 AND d = 4;

在此情況下，索引合併用於 (i_a, i_b, i_c)。

SELECT /*+ INDEX_MERGE(t1 i_a, i_b, i_c)*/ * FROM t1
  WHERE b = 1 AND c = 2 AND d = 3;

在此情況下，索引合併用於 (i_b, i_c)。

/*+ INDEX_MERGE(t1 i_a, i_b) NO_INDEX_MERGE(t1 i_b) */

由於有針對相同表格的先前提示，因此 NO_INDEX_MERGE 會被忽略。

/*+ NO_INDEX_MERGE(t1 i_a, i_b) INDEX_MERGE(t1 i_b) */

由於有針對相同表格的先前提示，因此 INDEX_MERGE 會被忽略。

對於 INDEX_MERGE 和 NO_INDEX_MERGE 最佳化工具提示，這些優先順序規則適用

子查詢提示會影響是否使用半聯結轉換，以及允許的半聯結策略，並且在不使用半聯結時，是否使用子查詢具體化或 IN 至 EXISTS 轉換。有關這些最佳化的詳細資訊，請參閱 第 10.2.2 節，「最佳化子查詢、衍生表格、檢視參考和通用表格運算式」。

影響半聯結策略的提示語法

hint_name([@query_block_name] [strategy [, strategy] ...])

此語法參照下列術語

如果一個子查詢巢狀在另一個子查詢內，且兩者都合併到外部查詢的半聯結中，則會忽略對最內層查詢的半聯結策略的任何指定。SEMIJOIN 和 NO_SEMIJOIN 提示仍可用於啟用或停用此類巢狀子查詢的半聯結轉換。

如果停用了 DUPSWEEDOUT，則有時最佳化工具可能會產生遠非最佳的查詢計畫。發生這種情況的原因是在貪婪搜尋期間進行的啟發式修剪，可以透過設定 optimizer_prune_level=0 來避免。

範例

SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) */ * FROM t2
  WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2
  WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);

影響是否使用子查詢具體化或 IN 至 EXISTS 轉換的提示語法

SUBQUERY([@query_block_name] strategy)

提示名稱始終為 SUBQUERY。

對於 SUBQUERY 提示，允許使用這些 strategy 值：INTOEXISTS、MATERIALIZATION。

範例

SELECT id, a IN (SELECT /*+ SUBQUERY(MATERIALIZATION) */ a FROM t1) FROM t2;
SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ SUBQUERY(INTOEXISTS) */ a FROM t1);

對於半聯結（semijoin）和 SUBQUERY 提示，開頭的 @query_block_name 指定提示所套用的查詢區塊。如果提示不包含開頭的 @query_block_name，則提示會套用至它所在的查詢區塊。若要為查詢區塊指定名稱，請參閱查詢區塊命名最佳化器提示。

如果提示註解包含多個子查詢提示，則會使用第一個。如果有其他同類型的後續提示，則會產生警告。其他類型的後續提示會被靜默忽略。

MAX_EXECUTION_TIME 提示僅允許用於 SELECT 陳述式。它會限制陳述式執行時間上限 N（以毫秒為單位的逾時值），超過此時間上限，伺服器會終止陳述式。

MAX_EXECUTION_TIME(N)

以下範例設定逾時時間為 1 秒（1000 毫秒）

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t1 INNER JOIN t2 WHERE ...

MAX_EXECUTION_TIME(N) 提示會設定陳述式執行逾時時間為 N 毫秒。如果此選項不存在或 N 為 0，則會套用由 max_execution_time 系統變數所建立的陳述式逾時時間。

MAX_EXECUTION_TIME 提示的適用情況如下：

SET_VAR 提示會暫時設定系統變數的工作階段值（在單一陳述式的持續時間內）。範例：

SELECT /*+ SET_VAR(sort_buffer_size = 16M) */ name FROM people ORDER BY name;
INSERT /*+ SET_VAR(foreign_key_checks=OFF) */ INTO t2 VALUES(2);
SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=off') */ 1;

SET_VAR 提示的語法：

SET_VAR(var_name = value)

var_name 會命名具有工作階段值的系統變數（儘管並非所有此類變數都可以命名，如稍後說明）。value 是要指派給變數的值；該值必須是純量。

SET_VAR 會進行暫時性的變數變更，如下列陳述式所示：

mysql> SELECT @@unique_checks;
+-----------------+
| @@unique_checks |
+-----------------+
|               1 |
+-----------------+
mysql> SELECT /*+ SET_VAR(unique_checks=OFF) */ @@unique_checks;
+-----------------+
| @@unique_checks |
+-----------------+
|               0 |
+-----------------+
mysql> SELECT @@unique_checks;
+-----------------+
| @@unique_checks |
+-----------------+
|               1 |
+-----------------+

使用 SET_VAR，不需要儲存和還原變數值。這可讓您以單一陳述式取代多個陳述式。請考慮以下陳述式序列：

SET @saved_val = @@SESSION.var_name;
SET @@SESSION.var_name = value;
SELECT ...
SET @@SESSION.var_name = @saved_val;

此序列可以由以下單一陳述式取代：

SELECT /*+ SET_VAR(var_name = value) ...

獨立的 SET 陳述式允許使用以下任何語法來命名工作階段變數：

SET SESSION var_name = value;
SET @@SESSION.var_name = value;
SET @@.var_name = value;

由於 SET_VAR 提示僅套用至工作階段變數，因此工作階段範圍是隱含的，而 SESSION、@@SESSION. 和 @@ 都不需要也不允許使用。包含明確的工作階段指標語法會導致 SET_VAR 提示被忽略並產生警告。

並非所有工作階段變數都允許與 SET_VAR 一起使用。個別系統變數的說明會指出每個變數是否可提示；請參閱第 7.1.8 節「伺服器系統變數」。您也可以在執行階段嘗試將系統變數與 SET_VAR 一起使用，來檢查該變數。如果變數不可提示，則會發生警告。

mysql> SELECT /*+ SET_VAR(collation_server = 'utf8mb4') */ 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 4537
Message: Variable 'collation_server' cannot be set using SET_VAR hint.

SET_VAR 語法僅允許設定單一變數，但可以提供多個提示來設定多個變數。

SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=off')
           SET_VAR(max_heap_table_size = 1G) */ 1;

如果同一個陳述式中出現多個具有相同變數名稱的提示，則會套用第一個提示，而其他提示會被忽略並產生警告。

SELECT /*+ SET_VAR(max_heap_table_size = 1G)
           SET_VAR(max_heap_table_size = 3G) */ 1;

在此情況下，第二個提示會被忽略，並產生衝突的警告。

如果沒有系統變數具有指定的名稱或變數值不正確，則會忽略 SET_VAR 提示，並產生警告。

SELECT /*+ SET_VAR(max_size = 1G) */ 1;
SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=yes') */ 1;

對於第一個陳述式，沒有 max_size 變數。對於第二個陳述式，mrr_cost_based 採用 on 或 off 的值，因此嘗試將其設定為 yes 是不正確的。在每種情況下，提示都會被忽略並產生警告。

SET_VAR 提示僅允許在陳述式層級使用。如果在子查詢中使用，則會忽略該提示並產生警告。

複本會忽略複寫陳述式中的 SET_VAR 提示，以避免潛在的安全性問題。

RESOURCE_GROUP 最佳化器提示用於資源群組管理（請參閱第 7.1.16 節「資源群組」）。此提示會將執行陳述式的執行緒暫時指派給已命名的資源群組（在陳述式的持續時間內）。它需要 RESOURCE_GROUP_ADMIN 或 RESOURCE_GROUP_USER 權限。

範例

SELECT /*+ RESOURCE_GROUP(USR_default) */ name FROM people ORDER BY name;
INSERT /*+ RESOURCE_GROUP(Batch) */ INTO t2 VALUES(2);

RESOURCE_GROUP 提示的語法：

RESOURCE_GROUP(group_name)

group_name 指示應將執行緒指派到的資源群組，以執行陳述式。如果該群組不存在，則會發生警告且該提示會被忽略。

RESOURCE_GROUP 提示必須出現在初始陳述式關鍵字（SELECT、INSERT、REPLACE、UPDATE 或 DELETE）之後。

RESOURCE_GROUP 的替代方案是 SET RESOURCE GROUP 陳述式，其會將執行緒非暫時性地指派給資源群組。請參閱第 15.7.2.4 節「SET RESOURCE GROUP 陳述式」。

資料表層級、索引層級和子查詢最佳化器提示允許將特定的查詢區塊命名為其引數語法的一部分。若要建立這些名稱，請使用 QB_NAME 提示，其會將名稱指派給提示所在的查詢區塊。

QB_NAME(name)

使用 QB_NAME 提示可以明確清楚地指出其他提示套用至哪個查詢區塊。它們也允許在單一提示註解中指定所有非查詢區塊名稱提示，以便更容易理解複雜的陳述式。請考慮以下陳述式：

SELECT ...
  FROM (SELECT ...
  FROM (SELECT ... FROM ...)) ...

QB_NAME 提示會將名稱指派給陳述式中的查詢區塊。

SELECT /*+ QB_NAME(qb1) */ ...
  FROM (SELECT /*+ QB_NAME(qb2) */ ...
  FROM (SELECT /*+ QB_NAME(qb3) */ ... FROM ...)) ...

然後其他提示可以使用這些名稱來參照適當的查詢區塊。

SELECT /*+ QB_NAME(qb1) MRR(@qb1 t1) BKA(@qb2) NO_MRR(@qb3t1 idx1, id2) */ ...
  FROM (SELECT /*+ QB_NAME(qb2) */ ...
  FROM (SELECT /*+ QB_NAME(qb3) */ ... FROM ...)) ...

產生的效果如下：

查詢區塊名稱是識別碼，並遵循有關有效名稱以及如何將其加上引號的通常規則（請參閱第 11.2 節「結構描述物件名稱」）。例如，包含空格的查詢區塊名稱必須加上引號，這可以使用反引號來完成。

SELECT /*+ BKA(@`my hint name`) */ ...
  FROM (SELECT /*+ QB_NAME(`my hint name`) */ ...) ...

如果啟用 ANSI_QUOTES SQL 模式，則也可以在雙引號內將查詢區塊名稱加上引號。

SELECT /*+ BKA(@"my hint name") */ ...
  FROM (SELECT /*+ QB_NAME("my hint name") */ ...) ...