優化器提示在不同的範圍層級套用

下表總結了可用的優化器提示、它們影響的優化器策略，以及它們套用的範圍或範圍。稍後會提供更多詳細資料。

停用最佳化會防止最佳化工具使用它。啟用最佳化表示如果該策略適用於語句執行，則最佳化工具可以自由使用該策略，而不是表示最佳化工具一定會使用它。

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，則會將區塊巢狀迴圈聯結套用到 t2，方法是在開始從 t2 讀取之前緩衝來自 t1 的列。如果最佳化工具改為選擇先處理 t2，則提示無效，因為 t2 是傳送者資料表。

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

索引層級提示會影響最佳化工具用於特定資料表或索引的索引處理策略。這些提示類型會影響索引條件下推 (Index Condition Pushdown, ICP)、多範圍讀取 (Multi-Range Read, 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") */ ...) ...