博主PS：优化器提示的作用就是你可以提示优化器使用什么优化策略。当然优化器只是被提示了，而不是必须按你的提示做出操作，它可以执行或者拒绝你的提示。所以它叫优化器提示，而不是优化器配置。
控制优化器策略的一种方法是设置优化器切换系统变量（见“可切换优化”）
对此变量的更改会影响所有后续查询的执行；
为了以不同的方式影响一个查询，有必要在每个查询之前更改optimizer_switch。
控制优化器的另一种方法是使用优化器提示，这些提示可以在单独的语句中指定。
因为优化器提示是以每条语句为基础应用的，所以它们提供了比使用optimizer_switch更精细的语句执行计划控制。
例如，您可以对语句中的一个表启用优化，而对另一个表禁用优化。语句中的提示优先于优化器开关标志。
SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
FROM 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.优化器命名查询块的提示