博主PS：優化器提示的作用就是你可以提示優化器使用什么優化策略。當然優化器只是被提示了，而不是必須按你的提示做出操作，它可以執行或者拒絕你的提示。所以它叫優化器提示，而不是優化器配置。

控制優化器策略的一種方法是設置優化器切換系統變量（見“可切換優化”）

對此變量的更改會影響所有后續查詢的執行；

為了以不同的方式影響一個查詢，有必要在每個查詢之前更改optimizer_switch。

控制優化器的另一種方法是使用優化器提示，這些提示可以在單獨的語句中指定。

因為優化器提示是以每條語句為基礎應用的，所以它們提供了比使用optimizer_switch更精細的語句執行計劃控制。

例如，您可以對語句中的一個表啟用優化，而對另一個表禁用優化。語句中的提示優先于優化器開關標志。

SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1FROM t3 WHERE f1 > 30 AND f1 < 33;
SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ NO_ICP(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */ * FROM t1 ...;
EXPLAIN SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE ...;
SELECT /*+ MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt;
INSERT /*+ SET_VAR(foreign_key_checks=OFF) */ INTO t2 VALUES(2);

這里描述的優化器提示不同于“索引提示”中描述的索引提示。

優化器提示和索引提示可以單獨使用，也可以一起使用。

---

1.Optimizer提示概述

優化器提示適用于不同的作用域級別：

全局：提示影響整個語句

查詢塊：提示影響語句中的特定查詢塊

表級別：提示影響查詢塊中的特定表

索引級別：提示影響表中的特定索引

下表總結了可用的優化器提示、它們影響的優化器策略以及它們應用的范圍。更多細節將在后面給出。

Hint Name	Description	Applicable Scopes
BKA, NO_BKA	Affects Batched Key Access join processing	Query block, table
BNL, NO_BNL	Prior to MySQL 8.0.20: affects Block Nested-Loop join processing; MySQL 8.0.18 and later: also affects hash join optimization; MySQL 8.0.20 and later: affects hash join optimization only	Query block, table
DERIVED_CONDITION_PUSHDOWN, NO_DERIVED_CONDITION_PUSHDOWN	Use or ignore the derived condition pushdown optimization for materialized derived tables (Added in MySQL 8.0.22)	Query block, table
GROUP_INDEX, NO_GROUP_INDEX	Use or ignore the specified index or indexes for index scans in GROUP BY operations (Added in MySQL 8.0.20)	Index
HASH_JOIN, NO_HASH_JOIN	Affects Hash Join optimization (MySQL 8.0.18 only	Query block, table
INDEX, NO_INDEX	Acts as the combination of JOIN_INDEX, GROUP_INDEX, and ORDER_INDEX, or as the combination of NO_JOIN_INDEX, NO_GROUP_INDEX, and NO_ORDER_INDEX (Added in MySQL 8.0.20)	Index
INDEX_MERGE, NO_INDEX_MERGE	Affects Index Merge optimization	Table, index
JOIN_FIXED_ORDER	Use table order specified in FROM clause for join order	Query block
JOIN_INDEX, NO_JOIN_INDEX	Use or ignore the specified index or indexes for any access method (Added in MySQL 8.0.20)	Index
JOIN_ORDER	Use table order specified in hint for join order	Query block
JOIN_PREFIX	Use table order specified in hint for first tables of join order	Query block
JOIN_SUFFIX	Use table order specified in hint for last tables of join order	Query block
MAX_EXECUTION_TIME	Limits statement execution time	Global
MERGE, NO_MERGE	Affects derived table/view merging into outer query block	Table
MRR, NO_MRR	Affects Multi-Range Read optimization	Table, index
NO_ICP	Affects Index Condition Pushdown optimization	Table, index
NO_RANGE_OPTIMIZATION	Affects range optimization	Table, index
ORDER_INDEX, NO_ORDER_INDEX	Use or ignore the specified index or indexes for sorting rows (Added in MySQL 8.0.20)	Index
QB_NAME	Assigns name to query block	Query block
RESOURCE_GROUP	Set resource group during statement execution	Global
SEMIJOIN, NO_SEMIJOIN	Affects semijoin strategies; beginning with MySQL 8.0.17, this also applies to antijoins	Query block
SKIP_SCAN, NO_SKIP_SCAN	Affects Skip Scan optimization	Table, index
SET_VAR	Set variable during statement execution	Global
SUBQUERY	Affects materialization, IN-to-EXISTS subquery strategies	Query block

禁用優化會阻止優化器使用它。啟用優化意味著如果策略應用于語句執行，優化器可以自由使用該策略，而不是優化器必須使用它。

2.Optimizer提示語法

MySQL支持SQL語句中的注釋，如“注釋”所述。優化器提示必須在/**+…*/中指定評論。

也就是說，優化器提示使用/*…*/的變體C風格的注釋語法，在/*注釋開頭序列后面有一個+字符。示例：

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

+字符后面允許有空格。

解析器識別SELECT、UPDATE、INSERT、REPLACE和DELETE語句的初始關鍵字之后的優化器提示注釋。在以下情況下允許提示：

在查詢和數據更改語句的開頭

SELECT /*+ ... */ ...
INSERT /*+ ... */ ...
REPLACE /*+ ... */ ...
UPDATE /*+ ... */ ...
DELETE /*+ ... */ ...

在查詢塊的開頭

(SELECT /*+ ... */ ... )
(SELECT ... ) UNION (SELECT /*+ ... */ ... )
(SELECT /*+ ... */ ... ) UNION (SELECT /*+ ... */ ... )
UPDATE ... WHERE x IN (SELECT /*+ ... */ ...)
INSERT ... SELECT /*+ ... */ ...

在以EXPLAIN開頭的暗示語句中。例如

EXPLAIN SELECT /*+ ... */ ...
EXPLAIN UPDATE ... WHERE x IN (SELECT /*+ ... */ ...)

這意味著您可以使用EXPLAIN來查看優化器提示如何影響執行計劃。在EXPLAIN之后立即使用SHOW WARNINGS查看提示的使用方式。

以下SHOW WARNINGS顯示的擴展EXPLAIN輸出指示使用了哪些提示。不顯示忽略的提示。

---

?

提示注釋可以包含多個提示，但查詢塊不能包含多個暗示注釋。這是有效的：

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

但這是無效的：

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

當提示注釋包含多個提示時，可能存在重復和沖突。以下通用指南適用。對于特定的提示類型，可以應用附加規則，如提示描述中所示。

重復提示：對于/*+MRR（idx1）MRR（idx1）*/，MySQL使用第一個提示并發出關于重復提示的警告。

沖突提示：對于/*+ MRR(idx1) NO_MRR(idx1) */，MySQL使用第一個提示，并發出關于第二個沖突提示的警告。

查詢塊名稱是標識符，并遵循關于哪些名稱是有效的以及如何引用它們的常見規則

（請參閱“模式對象名稱”）。

提示名稱、查詢塊名稱和策略名稱不區分大小寫。表和索引名稱的引用遵循通常的標識符大小寫敏感度規則（見第“標識符大小寫敏感性”）。

3.聯接順序優化器提示

未完待續。。。

4.表級優化器提示

5.索引級別優化器提示

6.子查詢優化器提示

7.語句執行時間優化器提示

8.變量設置提示語法

9.資源組提示語法

10.優化器命名查詢塊的提示