概叙

科普文：软件架构数据库系列之【MySQL解析器和优化器】-CSDN博客

科普文：软件架构数据库系列之【MySQL查询优化器中的优化策略optimizer_switch】-CSDN博客

科普文：软件架构数据库系列之【MySQL的sql_mode参数】-CSDN博客

科普文：软件架构数据库系列之【MySQL执行计划Extra梳理】-CSDN博客

前面说完优化器、以及常见的优化器策略、以及执行计划。今天继续查询优化器的控制。

MySQL :: MySQL 8.0 Reference Manual :: 10.9 Controlling the Query Optimizer

MySQL有一些表级别的Hint，利用这些Hint配合索引、数据倾斜等特点，有时可以大幅度的提高SQL性能，以达到优化目的。

表级优化提示(Table-Level Optimizer Hints)，常用的Hint有4种：

1. BKA, NO_BKA：启用或禁用BKA算法对表进行JOIN。
2. BNL, NO_BNL：启用或禁用BNL算法对表进行JOIN。
3. DERIVED_CONDITION_PUSHDOWN, NO_DERIVED_CONDITION_PUSHDOWN：启用或禁用派生表条件下推(从MySQL 8.0.22版本开始)
4. MERGE, NO_MERGE：启用或禁用视图合并(如视图、子查询、CTE等)。

这里还要说明以下3点：

1. 建议读者先了解下NLJ、BNL、BKA、HASH算法。
2. 派生表条件下推，在其它关系型数据库中早已存在，而MySQl到8.0.22版本才引入，这个优化是常用的技巧之一。
3. 视图合并，MySQL对于复杂些的子查询优化的不是很好，但此方法也是常用的技巧之一，类似于把SQL中的子查询展开成基表访问。
    

从官网可以看到MySQL的 Hints分为：

1. Join-Order Optimizer Hints
2. Table-Level Optimizer Hints
3. Index-Level Optimizer Hints
4. Subquery Optimizer Hints
5. Statement Execution Time Optimizer Hints
6. Variable-Setting Hint Syntax
7. Resource Group Hint Syntax
8. Optimizer Hints for Naming Query Blocks

MySQL :: MySQL 8.0 Reference Manual :: 10.9.3 Optimizer Hints

MySQL :: MySQL 8.0 Reference Manual :: 10.9.4 Index Hints

一、Optimizer Hint 

控制优化器策略的一种方法是设置optimizer_switch系统变量。对该变量的更改将影响所有后续查询的执行;为了对不同的查询产生不同的影响，必须在每个查询之前更改optimizer_switch。

控制优化器的另一种方法是使用优化器提示，这些提示可以在单独的语句中指定。因为优化器提示是针对每条语句应用的，所以它们对语句执行计划提供了比使用optimizer_switch更精细的控制。例如，可以对语句中的一个表启用优化，而对另一个表禁用优化。语句中的提示优先于optimizer_switch标志。

Optimizer hints apply at different scope levels:

• Global: The hint affects the entire statement

• Query block: The hint affects a particular query block within a statement

• Table-level: The hint affects a particular table within a query block

• Index-level: The hint affects a particular index within a table

The following table summarizes the available optimizer hints, the optimizer strategies they affect, and the scope or scopes at which they apply. More details are given later.

Optimizer Hints Available

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

isabling an optimization prevents the optimizer from using it. Enabling an optimization means the optimizer is free to use the strategy if it applies to statement execution, not that the optimizer necessarily uses it.

优化提示适用于不同的范围级别:

1. Global:提示影响整个语句
2. 查询块:提示影响语句中的特定查询块
3. 表级:提示影响查询块中的特定表
4. 索引级:提示影响表中的特定索引

下表总结了可用的优化器提示、它们影响的优化器策略以及它们应用的范围。稍后将提供更多细节：

Hints名	描述	应用范围
BKA, NO_BKA	影响批密钥接入连接处理	Query block, table
BNL, NO_BNL	MySQL 8.0.20之前:影响块嵌套循环连接处理;MySQL 8.0.18及以后版本:也影响哈希连接优化;MySQL 8.0.20及以后版本:只影响哈希连接优化	Query block, table
DERIVED_CONDITION_PUSHDOWN, NO_DERIVED_CONDITION_PUSHDOWN	使用或忽略派生表的派生条件下推优化(MySQL 8.0.22新增)	Query block, table
GROUP_INDEX, NO_GROUP_INDEX	在GROUP BY操作中使用或忽略指定的索引或索引(MySQL 8.0.20中新增)	Index
HASH_JOIN, NO_HASH_JOIN	影响哈希连接优化(仅MySQL 8.0.18	Query block, table
INDEX, NO_INDEX	作为JOIN_INDEX、GROUP_INDEX和ORDER_INDEX的组合，或者作为NO_JOIN_INDEX、NO_GROUP_INDEX和NO_ORDER_INDEX的组合(在MySQL 8.0.20中添加)	Index
INDEX_MERGE, NO_INDEX_MERGE	影响索引合并优化	Table, index
JOIN_FIXED_ORDER	使用FROM子句中指定的表顺序作为连接顺序	Query block
JOIN_INDEX, NO_JOIN_INDEX	使用或忽略任何访问方法的指定索引或索引(在MySQL 8.0.20中添加)	Index
JOIN_ORDER	使用提示中指定的表顺序作为连接顺序	Query block
JOIN_PREFIX	对于连接顺序的第一个表使用提示中指定的表顺序	Query block
JOIN_SUFFIX	对于连接顺序的最后一个表使用提示中指定的表顺序	Query block
MAX_EXECUTION_TIME	限制语句执行时间	Global
MERGE, NO_MERGE	影响将派生表/视图合并到外部查询块	Table
MRR, NO_MRR	影响多段读优化	Table, index
NO_ICP	影响索引条件下推优化	Table, index
NO_RANGE_OPTIMIZATION	影响范围的优化	Table, index
ORDER_INDEX, NO_ORDER_INDEX	使用或忽略指定的索引或索引来排序行(在MySQL 8.0.20中添加)	Index
QB_NAME	将名称分配给查询块	Query block
RESOURCE_GROUP	语句执行时设置资源组	Global
SEMIJOIN, NO_SEMIJOIN	影响semijoin策略;从MySQL 8.0.17开始，这也适用于反连接	Query block
SKIP_SCAN, NO_SKIP_SCAN	影响跳过扫描优化	Table, index
SET_VAR	语句执行时设置变量	Global
SUBQUERY	影响物化、IN-to-EXISTS子查询策略	Query block

禁用优化将阻止优化器使用它。启用优化意味着如果该策略适用于语句执行，则优化器可以自由使用该策略，而不是优化器一定要使用它。

Optimizer Hint Syntax

优化器提示必须在/+… /注释。也就是说，优化器提示使用/…/ c风格的注释语法，在/*注释开始序列后面有一个+字符。

+字符后面允许有空格。

解析器识别SELECT、UPDATE、INSERT、REPLACE和DELETE语句的初始关键字之后的优化器提示注释。

MySQL supports comments in SQL statements as described in Section 11.7, “Comments”. Optimizer hints must be specified within /*+ ... */ comments. That is, optimizer hints use a variant of /* ... */ C-style comment syntax, with a + character following the /* comment opening sequence. Examples:

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

Whitespace is permitted after the + character.

The parser recognizes optimizer hint comments after the initial keyword of SELECT, UPDATE, INSERT, REPLACE, and DELETE statements. Hints are permitted in these contexts:

在以下情况下允许提示:

1. 在查询和数据更改语句的开头:

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

1. 在查询块的开头:

(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) */ ...

当一个提示注释包含多个提示时，可能存在重复和冲突。以下是适用的一般准则。对于特定的提示类型，可以应用其他规则，如提示描述中所示:

1. 重复提示:对于类似/*+ MRR(idx1) MRR(idx1) */这样的提示，MySQL使用第一个提示并发出关于重复提示的警告。
2. 冲突提示:对于像/*+ MRR(idx1) NO_MRR(idx1) */这样的提示，MySQL使用第一个提示，并对第二个冲突提示发出警告。

查询块名称是标识符，并且遵循关于哪些名称有效以及如何引用它们的常规规则。

提示名称、查询块名称和策略名称不区分大小写。对表和索引名的引用遵循通常的标识符区分大小写规则。

1.Join-Order Optimizer Hints 连接顺序优化器Hints

连接顺序提示会影响优化器连接表的顺序。
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]] ...)

语法指的是这些术语:

1. 这些提示名称是允许的:
   1)JOIN_FIXED_ORDER:强制优化器使用表在FROM子句中出现的顺序连接表。这与指定SELECT STRAIGHT_JOIN是一样的。
   2)JOIN_ORDER:指示优化器使用指定的表顺序连接表。这个提示适用于已命名的表。优化器可以将没有按联接顺序命名的表放置在任何位置，包括指定的表之间。
   3)JOIN_PREFIX:指示优化器使用连接执行计划的第一个表的指定表顺序连接表。这个提示适用于已命名的表。优化器将所有其他表放在已命名表之后。
   4)JOIN_SUFFIX:指示优化器使用连接执行计划最后一个表的指定表顺序连接表。这个提示适用于已命名的表。优化器将所有其他表放在命名表之前。

2. tbl_name:语句中使用的表名。命名表的提示适用于它命名的所有表。JOIN_FIXED_ORDER提示不命名表，并应用于查询块的FROM子句中的所有表。
   如果表有别名，则提示必须引用别名，而不是表名。
   提示中的表名不能用模式名限定。

3. query_block_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表的行为。

优化器根据以下原则解析连接顺序提示:

1. 多个Hints实例
   每种类型只应用一个JOIN_PREFIX和JOIN_SUFFIX提示。后面任何相同类型的提示都将被忽略，并发出警告。JOIN_ORDER可以被指定多次。
   例子:

/*+ JOIN_PREFIX(t1) JOIN_PREFIX(t2) */

第二个JOIN_PREFIX提示会被一个警告忽略。

/*+ JOIN_PREFIX(t1) JOIN_SUFFIX(t2) */

这两种提示都适用。没有警告。

/*+ JOIN_ORDER(t1, t2) JOIN_ORDER(t2, t3) */

这两种提示都适用。没有警告。

1. 冲突的Hints
   在某些情况下，提示可能会发生冲突，例如当JOIN_ORDER和JOIN_PREFIX的表订单不可能同时应用时:

SELECT /*+ JOIN_ORDER(t1, t2) JOIN_PREFIX(t2, t1) */ ... FROM t1, t2;

在这种情况下，第一个指定的提示将被应用，后续的冲突提示将被忽略，没有任何警告。不可能应用的有效提示会被无声地忽略，没有任何警告。

1. 忽略的Hints
   如果提示中指定的表具有循环依赖关系，则提示将被忽略。

/*+ JOIN_ORDER(t1, t2) JOIN_PREFIX(t2, t1) */

JOIN_ORDER提示根据t1设置表t2。JOIN_PREFIX提示被忽略，因为表t1不能依赖于t2。被忽略的提示不会显示在扩展的EXPLAIN输出中。

1. 与const表的交互
   MySQL优化器将const表放在连接顺序的第一位，const表的位置不会受到提示的影响。在连接顺序提示中对const表的引用将被忽略，尽管该提示仍然适用。例如，它们是等价的:

JOIN_ORDER(t1, const_tbl, t2)
JOIN_ORDER(t1, t2)

在扩展的EXPLAIN输出中显示的可接受的提示包括指定的const表。

1. 与连接操作类型的交互
   MySQL支持几种类型的连接:LEFT, RIGHT, INNER, CROSS, STRAIGHT_JOIN。与指定联接类型冲突的提示将被忽略，不会发出警告。

SELECT /*+ JOIN_PREFIX(t1, t2) */FROM t2 LEFT JOIN t1;

这里提示中请求的连接顺序与LEFT join所需的顺序发生冲突。提示将被忽略，没有任何警告。

2. Table-Level Optimizer Hints 表级别的优化器Hints

表级提示影响:

1. 使用块嵌套环(BNL)和Batched Key Access(BKA)联合处理算法。
2. 派生表、视图引用或公共表表达式应该合并到外部查询块中，还是使用内部临时表进行物化。
3. 使用派生表条件下推优化。

这些提示类型适用于特定的表，或查询块中的所有表。
表级提示的语法:

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

语法指的是这些术语:

1. 这些提示名称是允许的:
   1)BKA, NO_BKA:启用或禁用指定表的bacthize键访问。
   2)BNL、NO_BNL:启用或禁用指定表的块嵌套循环。在MySQL 8.0.18及以后版本中，这些提示也启用和禁用散列连接优化。
   3)DERIVED_CONDITION_PUSHDOWN, NO_DERIVED_CONDITION_PUSHDOWN:对指定的表启用或禁用派生表条件下推(在MySQL 8.0.22中添加)。
   4)HASH_JOIN, NO_HASH_JOIN:启用或禁用指定表的哈希连接(仅MySQL 8.0.18;在MySQL 8.0.19或更高版本中没有效果)。
   5)MERGE, NO_MERGE:启用指定表、视图引用或通用表表达式的合并;或者禁用合并而使用物化。

2. tbl_name:语句中使用的表名。这个提示适用于它命名的所有表。如果提示没有命名表，它将应用于它出现的查询块的所有表。
   如果表有别名，则提示必须引用别名，而不是表名。
   提示中的表名不能用模式名限定。

3. query_block_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应用Block Nested-Loop连接，在开始从t2读取之前缓冲t1中的行。如果优化器选择先处理t2，则提示无效，因为t2是发送表。

对于MERGE和NO_MERGE提示，这些优先级规则适用:

1. 提示优先于不是技术约束的任何优化器启发式。(如果将提示作为建议提供没有效果，那么优化器就有理由忽略它。)
2. 提示优先于optimizer_switch系统变量的derived_merge标志。
3. 对于视图引用，视图定义中的ALGORITHM={MERGE|TEMPTABLE}子句优先于引用视图的查询中指定的提示。

3. Index-Level Optimizer Hints 索引级别优化器Hints

索引级提示会影响优化器对特定表或索引使用的索引处理策略。这些提示类型会影响使用索引条件下推(ICP)、多范围读取(MRR)、索引合并和范围优化。

索引级提示的语法:

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

1. 这些提示名称是允许的:
   1)GROUP_INDEX、NO_GROUP_INDEX:启用或禁用groupby操作的索引扫描的指定索引或索引。相当于索引提示FORCE index FOR GROUP BY，忽略index FOR GROUP BY。在MySQL 8.0.20及更高版本中可用。
   2)指数,NO_INDEX:充当JOIN_INDEX的结合,GROUP_INDEX, ORDER_INDEX,迫使服务器使用指定的索引或索引所有范围,或NO_JOIN_INDEX的结合,NO_GROUP_INDEX NO_ORDER_INDEX,导致服务器忽略任何和所有的指定索引或索引范围。等同于强制索引，忽略索引。从MySQL 8.0.20开始可用。
   3)INDEX_MERGE、NO_INDEX_MERGE:启用或禁用指定表或索引的索引合并访问方法。
   INDEX_MERGE提示强制优化器使用指定的索引集对指定的表使用Index Merge。如果没有指定索引，优化器将考虑所有可能的索引组合，并选择开销最小的索引。如果索引组合不适用于给定语句，则可能忽略该提示。
   NO_INDEX_MERGE提示禁用包含任何指定索引的索引合并组合。如果提示没有指定索引，则该表不允许索引合并。
   4)JOIN_INDEX, NO_JOIN_INDEX:强制MySQL为任何访问方法使用或忽略指定的索引，如ref、range、index_merge等。相当于FORCE INDEX FOR JOIN，忽略INDEX FOR JOIN。在MySQL 8.0.20及更高版本中可用。
   5)MRR、NO_MRR:使能或禁用指定表或索引的MRR。MRR提示只适用于InnoDB和MyISAM表。
   6)NO_ICP:对指定的表或索引禁用ICP。默认情况下，ICP是一种候选优化策略，因此没有启用它的提示。
   7)NO_RANGE_OPTIMIZATION:禁用指定表或索引的索引范围访问。此提示还禁用了表或索引的索引合并和松散索引扫描。默认情况下，范围访问是一种候选优化策略，因此没有启用它的提示。
   当范围的数量很高并且范围优化需要很多资源时，此提示可能很有用。
   8)ORDER_INDEX, NO_ORDER_INDEX:导致MySQL使用或忽略指定的索引或索引来排序行。相当于FORCE INDEX FOR ORDER BY，忽略INDEX FOR ORDER BY。从MySQL 8.0.20开始可用。
   9)SKIP_SCAN、NO_SKIP_SCAN:启用或禁用指定表或索引的Skip Scan访问方法。有关此访问方法的信息，请参见跳过扫描范围访问方法。这些提示从MySQL 8.0.13开始提供。
   SKIP_SCAN提示强制优化器使用指定的索引集对指定的表使用Skip Scan。如果没有指定索引，优化器将考虑所有可能的索引并选择开销最小的索引。如果索引不适用于给定语句，则提示可能被忽略。
   NO_SKIP_SCAN提示禁用指定索引的跳过扫描。如果提示没有指定索引，则该表不允许跳过扫描。

2. tbl_name:提示应用的表。

3. index_name:命名表中索引的名称。该提示适用于它命名的所有索引。如果提示没有命名索引，它将应用于表中的所有索引。
   要引用主键，请使用名称primary。要查看表的索引名称，请使用SHOW index。

4. query_block_name:提示应用的查询块。如果提示不包含前导@query_block_name，则提示适用于出现该提示的查询块。对于tbl_name@query_block_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;

下面的示例使用了Index Merge提示，但是其他索引级别的提示遵循相同的原则，即忽略提示和优化器提示相对于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优化提示，这些优先规则适用:

1. 如果指定了一个优化器提示并适用，它将优先于optimizer_switch系统变量的Index merge相关标志。

SET optimizer_switch='index_merge_intersection=off';
SELECT /*+ INDEX_MERGE(t1 i_b, i_c) */ * FROM t1
WHERE b = 1 AND c = 2 AND d = 3;

提示优先于optimizer_switch。在本例中，索引合并用于(i_b, i_c)。

SET optimizer_switch='index_merge_intersection=on';
SELECT /*+ INDEX_MERGE(t1 i_b) */ * FROM t1
WHERE b = 1 AND c = 2 AND d = 3;

提示只指定了一个索引，因此不适用，并应用optimizer_switch标志(on)。如果优化器认为索引合并具有成本效率，就使用它。

SET optimizer_switch='index_merge_intersection=off';
SELECT /*+ INDEX_MERGE(t1 i_b) */ * FROM t1
WHERE b = 1 AND c = 2 AND d = 3;

该提示只指定了一个索引，因此不适用，并应用optimizer_switch标志(off)。没有使用索引合并

1. 索引级优化器提示GROUP_INDEX、INDEX、JOIN_INDEX和ORDER_INDEX都优先于等效的FORCE INDEX提示;也就是说，它们导致FORCE INDEX提示被忽略。同样，NO_GROUP_INDEX、NO_INDEX、NO_JOIN_INDEX和NO_ORDER_INDEX提示它们都优先于任何IGNORE INDEX等价项，这也导致它们被忽略。

索引级优化器提示GROUP_INDEX、NO_GROUP_INDEX、INDEX、NO_INDEX、JOIN_INDEX、NO_JOIN_INDEX、ORDER_INDEX和NO_ORDER_INDEX提示都优先于所有其他优化器提示，包括其他索引级优化器提示。任何其他优化器提示只应用于这些优化器允许的索引。

GROUP_INDEX、INDEX、JOIN_INDEX和ORDER_INDEX提示都等同于FORCE INDEX而不是USE INDEX。这是因为使用一个或多个这样的提示意味着只有在无法使用其中一个命名索引查找表中的行时才会使用表扫描。要使MySQL使用与给定的use index实例相同的索引或索引集，您可以使用NO_INDEX、NO_JOIN_INDEX、NO_GROUP_INDEX、NO_ORDER_INDEX或它们的某种组合。

为了复制USE INDEX查询SELECT a,c FROM t1 USE INDEX FOR ORDER BY (i_a) ORDER BY a的效果，你可以使用NO_ORDER_INDEX优化提示覆盖表上的所有索引，除了需要的索引，如下所示:

SELECT /*+ NO_ORDER_INDEX(t1 i_b,i_c) */ a,c
    FROM t1
    ORDER BY a;

尝试将表的NO_ORDER_INDEX与USE INDEX for ORDER BY结合在一起是不能做到这一点的，因为NO_ORDER_BY会导致USE INDEX被忽略，如下所示:

mysql> EXPLAIN SELECT /*+ NO_ORDER_INDEX(t1) */ a,c FROM t1
    ->     USE INDEX FOR ORDER BY (i_a) ORDER BY a\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 256
     filtered: 100.00
        Extra: Using filesort

USE INDEX、FORCE INDEX和IGNORE INDEX索引提示比INDEX_MERGE和NO_INDEX_MERGE优化提示具有更高的优先级。

/*+ INDEX_MERGE(t1 i_a, i_b, i_c) */ ... IGNORE INDEX i_a

IGNORE INDEX优先于INDEX_MERGE，因此索引i_a被排除在索引合并的可能范围之外。

/*+ NO_INDEX_MERGE(t1 i_a, i_b) */ ... FORCE INDEX i_a, i_b

由于FORCE Index, i_a, i_b不允许索引合并，但是优化器被迫使用i_a或i_b来进行范围或ref访问。没有冲突;这两种提示都适用。

1. 如果IGNORE INDEX提示命名多个索引，这些索引对于索引合并是不可用的。

2. FORCE INDEX和USE INDEX提示只对已命名的索引进行索引合并。

SELECT /*+ INDEX_MERGE(t1 i_a, i_b, i_c) */ a FROM t1
FORCE INDEX (i_a, i_b) WHERE c = 'h' AND a = 2 AND b = 'b';

索引合并交集访问算法用于(i_a, i_b)。如果将FORCE INDEX更改为USE INDEX，也同样适用。

4. Subquery Optimizer Hints子查询相关优化器的Hints

子查询提示影响是否使用半连接转换以及允许哪些半连接策略，以及在不使用半连接时，是否使用子查询具体化或IN-to-EXISTS转换。

影响半连接策略的提示语法:

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

语法指的是这些术语:

1. 这些提示名称是允许的:
   1)SEMIJOIN、NO_SEMIJOIN:启用或禁用命名的SEMIJOIN策略。

1. strategy:启用或禁用的半连接策略。这些策略名称是允许的:DUPSWEEDOUT, FIRSTMATCH, LOOSESCAN, MATERIALIZATION。
   对于SEMIJOIN提示，如果没有指定策略，则根据optimizer_switch系统变量启用的策略尽可能使用SEMIJOIN。如果指定了策略但不适用于语句，则使用DUPSWEEDOUT。
   对于NO_SEMIJOIN提示，如果没有指定策略，则不使用semijoin。如果指定的策略排除了语句中所有适用的策略，则使用DUPSWEEDOUT。

如果一个子查询嵌套在另一个子查询中，并且两者合并为外部查询的半连接，则最内层查询的任何半连接策略规范都会被忽略。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-to-EXISTS转换的提示语法:

SUBQUERY([@query_block_name] strategy)

提示名称总是SUBQUERY。

对于子查询提示，这些策略值是允许的: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，则提示适用于出现该提示的查询块。要为查询块分配名称，请参见命名查询块的优化提示。

如果一个提示注释包含多个子查询提示，则使用第一个。如果有其他类似的提示，它们会产生警告。其他类型的后续提示将被默默地忽略。

5. Statement Execution Time Optimizer Hints语句执行时间优化器Hints

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提示如下所示:

1. 对于具有多个SELECT关键字的语句，例如union或带有子查询的语句，MAX_EXECUTION_TIME应用于整个语句，必须出现在第一个SELECT之后。
2. 它适用于只读SELECT语句。非只读语句是那些调用了附带修改数据的存储函数的语句。
3. 它不适用于存储程序中的SELECT语句，会被忽略。

6. Variable-Setting Hint Syntax可变设定Hints语法

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　Hints的语法:

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一起使用。单个系统变量描述表明每个变量是否可提示。您还可以在运行时通过尝试将系统变量与SET_VAR一起使用来检查系统变量。如果变量不能提示，则会出现警告:

mysql> SELECT /*+ SET_VAR(collation_server = 'utf8') */ 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提示，以避免潜在的安全问题。

7. Resource Group Hint Syntax 资源组 Hint 语法

RESOURCE_GROUP优化器提示用于资源组管理。此提示将执行语句的线程临时分配给指定的资源组(在语句执行期间)。它需要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语句，该语句将线程非临时地分配给资源组。

8. Optimizer Hints for Naming Query Blocks命名查询块的优化器Hints

表级、索引级和子查询优化器提示允许将特定的查询块命名为其参数语法的一部分。要创建这些名称，使用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 ...)) ...

由此产生的效果如下:

1. MRR(@qb1 t1)适用于查询块qb1中的表t1。
2. BKA(@qb2)适用于查询块qb2。
3. NO_MRR(@qb3 t1 idx1, id2)适用于查询块qb3中表t1中的索引idx1和idx2。

查询块名称是标识符，并且遵循关于哪些名称有效以及如何引用它们的常规规则。例如，包含空格的查询块名称必须用引号括起来，可以使用反引号:

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") */ ...) ...

二、索引Hints

索引提示为优化器提供关于在查询处理期间如何选择索引的信息。索引和优化器提示可以单独使用，也可以同时使用。

MySQL :: MySQL 8.0 Reference Manual :: 10.9.4 Index Hints

索引提示只适用于SELECT和UPDATE语句。

索引提示在表名之后指定。引用单个表的语法(包括索引提示)如下所示:

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|FORCE} {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

index_list:
    index_name [, index_name] ...

USE INDEX (index_list)提示告诉MySQL只使用其中一个命名索引来查找表中的行。另一种语法IGNORE INDEX (index_list)告诉MySQL不要使用某些特定的索引。如果EXPLAIN显示MySQL在可能的索引列表中使用了错误的索引，那么这些提示是有用的。

FORCE INDEX提示的作用类似于USE INDEX (index_list)，只是假设表扫描的开销非常大。换句话说，只有在无法使用某个命名索引查找表中的行时，才会使用表扫描。

每个提示都需要索引名，而不是列名。要引用主键，请使用名称primary。要查看表的索引名称，可以使用SHOW index语句或INFORMATION_SCHEMA。统计数据表。

Index Hint是向优化器提供有关在查询处理期间如何选择索引。这里单存的索引选择上，不更改优化器策略。合理的索引可以加快数据检索操作，常用的索引Hint简单方式，使用USE（参考使用），IGNORE（忽略），FORCE（强制）三种方式。

• 但其语法上看，应该更精准的使用 JOIN，ORDER，GROUP 等场景。

index_hint:
    USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | {IGNORE|FORCE} {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

• 索引Hint应用于SELECT和UPDATE语句。还可以用于多表DELETE语句，但不能用于单表DELETE。
• 索引hint需要的是索引名，而不是列名，主键使用PRIMARY，其他的可以通过SHOW index语句或Information Schema STATISTICS查看。

因为MySQL是索引组织表，索引优化是SQL语句常用的主要必要手段。普遍情况下执行计划都是能合理选择最优的索引，进行检索。但也有例外。

在官方Note提示后期就会弃掉，融合到Optimizer Hints里。要是在代码中使用索引Hint就要注意了。

说明：
USE INDEX、FORCE INDEX和IGNORE INDEX将在MySQL的未来版本中被弃用，并在之后的某个时间被完全删除。因为从MySQL 8.0.20开始，服务器支持索引级优化器提示JOIN_INDEX、GROUP_INDEX、ORDER_INDEX和INDEX，它们等价于并打算取代FORCE INDEX索引提示，以及NO_JOIN_INDEX、NO_GROUP_INDEX、NO_ORDER_INDEX和NO_INDEX优化器提示，它们等价于并打算取代IGNORE INDEX索引提示。

index_name值不需要是完整的索引名称。它可以是索引名的明确前缀。如果前缀有二义性，就会发生错误。

例子:

SELECT * FROM table1 USE INDEX (col1_index,col2_index)
  WHERE col1=1 AND col2=2 AND col3=3;

SELECT * FROM table1 IGNORE INDEX (col3_index)
  WHERE col1=1 AND col2=2 AND col3=3;

索引提示的语法具有以下特征:

1. 在USE INDEX中省略index_list在语法上是有效的，这意味着“不使用索引”。在FORCE INDEX或IGNORE INDEX中省略index_list是语法错误。

2. 可以通过向提示添加FOR子句来指定索引提示的范围。这为查询处理的各个阶段提供了对执行计划的优化器选择的更细粒度控制。当MySQL决定如何在表中查找行以及如何处理连接时，要只影响索引，使用FOR JOIN。若要影响用于对行进行排序或分组的索引使用，请使用for ORDER BY或for GROUP BY。

3. 你可以指定多个索引提示:

SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX FOR ORDER BY (i2) ORDER BY a;

在几个提示中(甚至在同一个提示中)命名同一个索引不是错误:

SELECT * FROM t1 USE INDEX (i1) USE INDEX (i1,i1);

但是，对于同一个表混合使用INDEX和FORCE INDEX是错误的:

SELECT * FROM t1 USE INDEX FOR JOIN (i1) FORCE INDEX FOR JOIN (i2);

如果索引提示不包含FOR子句，则该提示的作用域将应用于语句的所有部分。例如，这个提示:

IGNORE INDEX (i1)

等价于以下提示的组合:

IGNORE INDEX FOR JOIN (i1)
IGNORE INDEX FOR ORDER BY (i1)
IGNORE INDEX FOR GROUP BY (i1)

在MySQL 5.0中，没有FOR子句的提示作用域只适用于行检索。要使服务器在没有FOR子句时使用这种旧的行为，请在服务器启动时启用旧的系统变量。注意在复制设置中启用这个变量。对于基于语句的二进制日志记录，使用源和副本的不同模式可能会导致复制错误。

在处理索引提示时，它们将按类型(USE、FORCE、IGNORE)和范围(FOR JOIN、FOR ORDER by、FOR GROUP by)收集在一个列表中。例如:

SELECT * FROM t1
  USE INDEX () IGNORE INDEX (i2) USE INDEX (i1) USE INDEX (i2);

等同于:

SELECT * FROM t1
   USE INDEX (i1,i2) IGNORE INDEX (i2);

然后索引提示按以下顺序应用于每个作用域:

1. {USE|FORCE}如果存在，则应用索引。(如果不是，则使用优化器确定的索引集。)
2. IGNORE INDEX应用于上一步的结果。例如，下面两个查询是等价的:

SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX (i2) USE INDEX (i2);

SELECT * FROM t1 USE INDEX (i1);

对于FULLTEXT搜索，索引提示的作用如下:

1. 对于自然语言模式的搜索，索引提示会被静默地忽略。例如，IGNORE INDEX(i1)被忽略，没有任何警告，索引仍然被使用。

2. 对于布尔模式搜索，带有For ORDER BY或For GROUP BY的索引提示将被静默忽略。使用FOR JOIN或不使用FOR修饰符的索引提示是被尊重的。与用于非fulltext搜索的提示不同，提示用于查询执行的所有阶段(查找行和检索、分组和排序)。即使提示是非fulltext索引，也是如此。

例如，下面两个查询是等价的:

SELECT * FROM t
  USE INDEX (index1)
  IGNORE INDEX (index1) FOR ORDER BY
  IGNORE INDEX (index1) FOR GROUP BY
  WHERE ... IN BOOLEAN MODE ... ;

SELECT * FROM t
  USE INDEX (index1)
  WHERE ... IN BOOLEAN MODE ... ;

三、控制查询计划评估

查询优化器的任务是找到执行SQL查询的最佳计划。因为“好”和“坏”计划之间的性能差异可能是数量级的(即秒、小时甚至是天)，大多数查询优化器，包括MySQL，在所有可能的查询评估计划中执行或多或少详尽的搜索，以寻找最优计划。对于连接查询，MySQL优化器调查的可能计划的数量会随着查询中引用的表的数量呈指数级增长。对于少量的表(通常少于7到10个)，这不是问题。然而，当提交较大的查询时，花费在查询优化上的时间很容易成为服务器性能的主要瓶颈。

一种更灵活的查询优化方法使用户能够控制优化器在搜索最优查询评估计划时的穷尽程度。一般的想法是，优化器调查的计划越少，它在编译查询上花费的时间就越少。另一方面，因为优化器跳过一些计划，它可能会错过找到一个最优计划。

优化器的行为与它评估的计划数量有关，可以使用两个系统变量来控制:

1. optimizer_prune_level变量告诉优化器根据对每个表访问的行数的估计跳过某些计划。我们的经验表明，这种“有根据的猜测”很少会错过最佳计划，并且可以显著减少查询编译时间。这就是为什么这个选项在默认情况下是开启的(optimizer_prune_level=1)。但是，如果您认为优化器遗漏了一个更好的查询计划，那么可以关闭这个选项(optimizer_prune_level=0)，这样做的风险是，查询编译可能花费更长的时间。请注意，即使使用了这种启发式方法，优化器仍然在探索大致呈指数级数量的计划。
2. optimizer_search_depth变量告诉优化器应该查看每个不完整计划的“未来”，以评估它是否应该进一步扩展。较小的optimizer_search_depth值可能会导致较小的查询编译时间。例如，如果optimizer_search_depth接近查询中表的数量，那么包含12、13或更多表的查询可能很容易需要数小时甚至数天的编译。同时，如果在optimizer_search_depth等于3或4的情况下编译，那么对于同一个查询，优化器可能只需要不到一分钟的时间。如果您不确定optimizer_search_depth的合理值是多少，可以将该变量设置为0，以告诉优化器自动确定该值。

四、内核实现

前面讲到，Hints 其实是一套干预机制，它匹配被干预对象，施以动作来影响优化器行为。

内核实现分为三个部分：统一的 Hint 语法支持、Hint 的内部组织形式和对优化器的影响方式。

语法支持和组织形式，也称为新一代 Hint 基础架构，新开发的 Hint 只需要按照约定在其中增加声明和校验机制。但影响方式则是因 Hint 相关的优化器行为而异的，简单的只需要查一下 hint 参数来决定是否启用一段代码分支（例如 MERGE ），而复杂的就需要修改优化器数据结构，像 JOIN_ORDER 就要根据参数来建立表依赖关系，并修改相应的运行期数据结构内容。

1.统一的 Hint 语法支持

语法支持分为两部分，即在客户端的专用注释类型和在服务端语法解析，都在 WL#8016 设计范围里。

客户端其实没有做语法解析，只是在 client/http://mysql.cc 的 add_line() 函数里，将新一代 hints 的注释转发到服务端。顺便说一句，虽然在 8.0.20 里已经支持了以系统化命名机制来引用查询块，但在客户端代码里却未做相应的处理，所以，这个还是未公开行为。

在服务端解析代码设计上，为了尽量避免修改 main parser (sql_yacc.yy) ， WL#8016 选择了共用 token 空间，但独立的 Hint parser 和 lexer 的方式。只在遇到特定的 token 才切换到 Hint parser 消费掉所有新一代 hints 注释 (consume_optimizer_hints) ，产生的 hints 列表 (PT_hint_list) 则返回给 main parser 。只有 5 种子句支持 hint ，即 SELECT INSERT DELETE UPDATE REPLACE 。

相关源代码文件：

sql/lex.h                    // symbol
sql/gen_lex_token.{h,cc}     // token
sql/sql_lex_hints.{h,cc}     // hint lexer
sql/sql_hints.yy             // hint parser
sql/parse_tree_hints.{h,cc}  // PT_hint_list, PT_hint, PT_{qb,table,key}_level_hint, PT_hint_sys_var, ...
sql/sql_lex.cc               // consume_optimizer_hints()

2.Hint 的内部组织形式

Hints 会在 parse 后的 contextualization 阶段注册到一个称为 hints tree 的四层树状结构中。每个 PT_hint 子类都需要提供相应的 contextualize() 实现，它主要作用是检查 hint 的合法性和相互是否有冲突，然后转成 hints tree 表达形式。这些在 WL#8017 的设计范围里。

Hints tree 的节点类型是 Opt_hints ，四个层次分别是语句、查询块、表和索引，相应的子类是 Opt_hints_global, Opt_hints_qb, Opt_hints_table, Opt_hints_key 。也就是说，每个被干预对象，都是 hints tree 的一个节点。然后在优化过程中的每个决策点，优化器都会到这个 hints tree (lex->opt_hints_global) 查找匹配的 hint ，并采取相应的动作，而查找结果还会缓存在被干预对象中，例如 SELECT_LEX::opt_hints_qb 和 TABLE_LIST::opt_hints_table 。如下图所示：

下面是 Opt_hints 结构。每个节点都有一个 hints_map ，用于表示每个类型的 hint 是否指定以及开关状态。可以看到，MySQL Hints 目前最多支持 64 种。

class Opt_hints_map {
  Bitmap<64> hints;
  Bitmap<64> hints_specified;
};
class Opt_hints {
  const LEX_CSTRING *name;  // 用于匹配的名字
  Opt_hints *parent;
  Mem_root_array<Opt_hints *> child_array;
  Opt_hints_map hints_map;  // 每个 Hint 是否指定，及其开关状态
  // ...
};

而每个层级都可以有相应的额外信息，例如，语句级 hint 记录全局设定，查询块级 hint 会有相应的变形和表序决策，表级 hint 则有索引选择的决策。索引级 hint 没有额外信息，因为索引上的 hint 都是开关类型的。

class Opt_hints_global : public Opt_hints {
  PT_hint_max_execution_time *max_exec_time;
  Sys_var_hint *sys_var_hint;
};
class Opt_hints_qb : public Opt_hints {
  uint select_number;
  PT_qb_level_hint *subquery_hint, *semijoin_hint;
  Mem_root_array<PT_qb_level_hint *> join_order_hints;
  //...
};
class Opt_hints_table : public Opt_hints {
  Glob_index_key_hint index;
  Compound_key_hint index_merge;
  Compound_key_hint skip_scan;
  // ...
};

3.对优化器行为的影响方式

不同的 Hint 对优化器的干预方式是不同的（详见附录）。大体上，可以分为开关型、枚举型和复杂 Hint 三类。

4.开关型

​开关型影响方式就是直接启用特定的代码路径。大部分 hint 都是开关型的。下面是开关型查找函数。查找时会考虑两级继承逻辑（称为 Applicable Scopes ，详见社区文档 ），不过，从上级对象继承干预方式的情况是几乎是没有的。Index merge 因为涉及多个索引，在处理上会特别一些。

hint_table_state()  // 表级 hint 状态
hint_key_state()    // 索引级 hint 状态
compound_hint_key_enabled()  // 主要用于检测 index merge 涉及的索引是否被禁掉
idx_merge_hint_state()  // 用于表访问路径是否强制为 index merge

举例来说， 视图合并的决策点是在 SELECT_LEX::merge_derived() 函数里，在这里根据该引用位置（占位表）所匹配的 MERGE hint ，来决定启用相关代码路径：

SELECT_LEX::resolve_placeholder_tables
  SELECT_LEX::merge_derived
    hint_table_state  // 是否启用视图合并

5.枚举型

枚举型相对于开关型，主要是支持多种状态。例如 semijoin 有两个决策点，在第一个决策点会调用 Opt_hints_qb::semijoin_enabled() 决定是否启用 semijion，在第二个决策点会查采用什么 semijoin 策略，调用 Opt_hints_qb::sj_enabled_strategies() 获得具体 semijoin 策略（ FIRSTMATCH, LOOSESCAN 或 DUPSWEEDOUT ），然后设置到 NESTED_JOIN 运行时结构中。

SELECT_LEX::resolve_subquery
  SELECT_LEX::semijoin_enabled
    Opt_hints_qb::semijoin_enabled  // 是否启用 semijoin

JOIN::optimize
  JOIN::make_join_plan
    SELECT_LEX::update_semijoin_strategies
      Opt_hints_qb::sj_enabled_strategies  // 获取具体的 semijoin 策略，更新 NESTED_JOIN

6.复杂型

其他 Hint 处理会相对复杂一点，不过，处理逻辑都包装在对应的函数中了。

干预连接顺序的 Hint 会在决策点调用 Opt_hints_qb::apply_join_order_hints() ，根据 hint 参数设置连接表的依赖关系，即修改 JOIN_TAB::dependent 表依赖位图，增加额外的依赖关系（表的相对顺序）。基于代价优化阶段产生的表序，会遵守设定的依赖关系。

JOIN::optimize
  JOIN::make_join_plan
    Opt_hints_qb::apply_join_order_hints
      set_join_hint_deps  // 修改表依赖位图 JOIN_TAB::dependent ，增加额外的相对顺序关系
    Optimize_table_order::choose_table_order()  // 基于代价确定表序时，遵守已设定相对顺序

干预索引选择的 Hint 会在决策点调用 Opt_hints_qb::adjust_table_hints() 和 Opt_hints_table::update_index_hint_maps() 修改 TABLE 结构里的候选索引位图。在优化过程中，候选索引位图决定了哪些索引是可用的。

SELECT_LEX::setup_tables
  Opt_hints_qb::adjust_table_hints  // 查找索引干预决策
  Opt_hints_table::update_index_hint_maps  // 修改候选索引位图 TABLE::keys_in_use_for_query 等

五、使用Hints的注意事项

虽然Hints可以帮助你更好地控制查询优化器的行为，但在使用时需要注意以下几点：

1 避免过度依赖Hints
Hints应作为优化手段的补充，而不是唯一手段。过度依赖Hints可能会导致查询性能的不稳定，甚至在数据库版本升级或数据分布变化后出现性能问题。因此，建议优先通过索引优化、查询重写等方式提升性能，只有在必要时才使用Hints。

2 定期评估Hints的效果
由于数据和数据库环境可能随时间变化，原本有效的Hints可能会失效。因此，建议定期评估和调整Hints，以确保其持续有效。

3 理解Hints的影响范围
不同类型的Hints对查询优化器的影响范围不同。例如，INDEX Hint只影响索引选择，而JOIN Hint则影响表连接方式。在使用Hints前，需充分理解其影响范围和潜在副作用。

六、实践中的Hints应用场景

1 优化复杂查询
在某些复杂查询中，优化器可能无法选择最优的执行计划。此时，可以使用Hints引导优化器选择更优的执行路径。例如，在涉及多个表连接的查询中，可以使用JOIN Hint优化连接顺序。

2 解决性能瓶颈
当查询在某些情况下出现性能瓶颈时，可以使用Hints进行针对性优化。例如，在高并发场景下，可以使用MAX_EXECUTION_TIME Hint限制查询的最大执行时间，避免长时间占用资源。

3 规避已知问题
在某些版本的MySQL中，优化器可能存在已知问题，导致某些查询性能较差。此时，可以使用Hints规避这些问题。例如，通过NO_INDEX Hint避免使用已知问题的索引。

七、示例：使用Hints优化查询

示例一：查询优化

1 场景描述
假设我们有一个电商系统，包含用户表（users）和订单表（orders）。我们需要查询所有下过订单的用户及其订单详情，并对查询进行优化。

2 初始查询
初始查询如下：

SELECT u.id, u.name, o.id, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
AND o.amount > 100;

3 使用Hints优化查询
根据实际情况，我们发现优化器没有选择最佳的索引和连接顺序。我们可以通过添加Hints进行优化：

SELECT /*+ INDEX(u idx_status) JOIN_TYPE(STRAIGHT_JOIN) */
    u.id, u.name, o.id, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
AND o.amount > 100;

在优化后的查询中：

1. 使用INDEX(u idx_status) Hint强制优化器选择users表上的idx_status索引。
2. 使用JOIN_TYPE(STRAIGHT_JOIN) Hint强制优化器按照查询中表的顺序连接users表和orders表。

示例二：表级优化

准备数据

drop table if exists t1;
CREATE TABLE t1 (
  id int NOT NULL AUTO_INCREMENT,
  name varchar(20) DEFAULT NULL,
  age int DEFAULT NULL,
  PRIMARY KEY (id),
  KEY ix_age (age)
) ENGINE=InnoDB;
insert into t1(id, name, age) values(1, 'name1', 10);
insert into t1(id, name, age) values(2, 'name2', 10);
insert into t1(id, name, age) values(3, 'name3', 20);
insert into t1(id, name, age) values(4, 'name4', 20);
insert into t1(id, name, age) values(5, 'name5', 30);
insert into t1(id, name, age) values(6, 'name6', 30);
insert into t1(id, name, age) values(7, 'name3', 40);
insert into t1(id, name, age) values(8, 'name4', 40);
insert into t1(id, name, age) values(9, 'name5', 50);
insert into t1(id, name, age) values(10, 'name6', 50);

drop table if exists t2;
create table t2 like t1;
insert into t2 select * from t1;

mysql> select * from t1;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | name1 |   10 |
|  2 | name2 |   10 |
|  3 | name3 |   20 |
|  4 | name4 |   20 |
|  5 | name5 |   30 |
|  6 | name6 |   30 |
|  7 | name3 |   40 |
|  8 | name4 |   40 |
|  9 | name5 |   50 |
| 10 | name6 |   50 |
+----+-------+------+
10 rows in set (0.01 sec)

-- 查看optimizer_switch变量
mysql> show variables like 'optimizer_switch';
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name    | Value                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
--

1. BKA, NO_BKA：启用或禁用BKA算法对表进行JOIN.

-- 启用BKA，从Extra列可看出被驱动表走了BKA算法
mysql> explain
    -> select /*+ BKA(a, b) */ a.*
    -> from t1 a
    -> inner join t2 b
    -> on a.age=cast(b.age as char)
    -> ;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                                               |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------------------------------------+
|  1 | SIMPLE      | b     | NULL       | index | NULL          | ix_age | 5       | NULL |   10 |   100.00 | Using index                                         |
|  1 | SIMPLE      | a     | NULL       | ref   | ix_age        | ix_age | 5       | func |    2 |   100.00 | Using where; Using join buffer (Batched Key Access) |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------------------------------------+
2 rows in set, 1 warning (0.02 sec)

mysql> explain analyze
    -> select /*+ BKA(a, b) */ a.*
    -> from t1 a
    -> inner join t2 b
    -> on a.age=cast(b.age as char)
    -> ;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Batched key access inner join  (actual time=19.460..27.760 rows=20 loops=1)
    -> Batch input rows
        -> Covering index scan on b using ix_age  (cost=1.25 rows=10) (actual time=0.269..1.335 rows=10 loops=1)
    -> Filter: (cast(a.age as double) = cast(cast(b.age as char charset utf8mb4) as double))  (actual time=17.501..24.526 rows=20 loops=1)
        -> Multi-range index lookup on a using ix_age (age=cast(b.age as char charset utf8mb4))  (actual time=17.173..22.687 rows=20 loops=1)
 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

-- 启用BKA，但当优化器判断有更低成本的执行计划时，选择最低成本执行计划。
mysql> explain
    -> select /*+ BKA(a, b) */ a.*
    -> from t1 a
    -> inner join t2 b
    -> on a.age=b.age
    -> ;
+----+-------------+-------+------------+------+---------------+--------+---------+------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref        | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+--------+---------+------------+------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | ALL  | ix_age        | NULL   | NULL    | NULL       |   10 |   100.00 | Using where |
|  1 | SIMPLE      | b     | NULL       | ref  | ix_age        | ix_age | 5       | modb.a.age |    2 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+--------+---------+------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

-- 禁用BKA，由于被驱动表索引失效，从Extra看出执行计划走了NLJ算法，性能很差
mysql> explain
    -> select /*+ NO_BKA(a, b) */ a.*
    -> from t1 a
    -> inner join t2 b
    -> on a.age=cast(b.age as char)
    -> ;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | b     | NULL       | index | NULL          | ix_age | 5       | NULL |   10 |   100.00 | Using index           |
|  1 | SIMPLE      | a     | NULL       | ref   | ix_age        | ix_age | 5       | func |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
2 rows in set, 1 warning (0.00 sec)

mysql> explain analyze
    -> select /*+ NO_BKA(a, b) */ a.*
    -> from t1 a
    -> inner join t2 b
    -> on a.age=cast(b.age as char)
    -> ;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join  (cost=8.25 rows=20) (actual time=1.039..12.472 rows=20 loops=1)
    -> Covering index scan on b using ix_age  (cost=1.25 rows=10) (actual time=0.248..1.295 rows=10 loops=1)
    -> Index lookup on a using ix_age (age=cast(b.age as char charset utf8mb4)), with index condition: (cast(a.age as double) = cast(cast(b.age as char charset utf8mb4) as double))  (cost=0.52 rows=2) (actual time=0.659..0.847 rows=2 loops=10)
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

注意事项：

BKA提示相当于set optimizer_switch=‘batched_key_access=on’; 默认为关闭。
BKA提示开启时，优化器会考虑评估此算法的成本，但最终会选择最低成本的执行计划。
BKA、NO_BAK提示优先级高于optimizer_switch变量。

2. BNL, NO_BNL：启用或禁用BNL算法对表进行JOIN。

-- mysql5.7版本,由于没有hash算法,当被驱动表没有合适索引时,使用BNL算法
mysql> explain
    -> select /*+ BNL(a, b) */ a.*
    -> from t1 a
    -> inner join t2 b
    -> on a.name=b.name
    -> ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL                                               |
|  1 | SIMPLE      | b     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

-- mysql5.7版本,当使用NO_BNL提示时,禁用BNL算法,只能使用NLJ嵌套循环
mysql> explain
    -> select /*+ NO_BNL(a, b) */ a.*
    -> from t1 a
    -> inner join t2 b
    -> on a.name=b.name
    -> ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL        |
|  1 | SIMPLE      | b     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

-- mysql8.0版本,由于hash比BNL算法成本更低,故优化器选择hash算法
mysql> explain
    -> select /*+ BNL(a, b) */ a.*
    -> from t1 a
    -> inner join t2 b
    -> on a.name=b.name
    -> ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL                                       |
|  1 | SIMPLE      | b     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    10.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.01 sec)

mysql> explain analyze
    -> select /*+ BNL(a, b) */ a.*
    -> from t1 a
    -> inner join t2 b
    -> on a.name=b.name
    -> ;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                  |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Inner hash join (b.`name` = a.`name`)  (cost=11.50 rows=10) (actual time=3.589..4.820 rows=18 loops=1)
    -> Table scan on b  (cost=0.04 rows=10) (actual time=0.448..0.826 rows=10 loops=1)
    -> Hash
        -> Table scan on a  (cost=1.25 rows=10) (actual time=0.446..0.842 rows=10 loops=1)
 |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

-- mysql8.0版本,当使用NO_BNL提示时,禁用hash算法,同时也禁用BNL算法
mysql> explain
    -> select /*+ NO_BNL(a, b) */ a.*
    -> from t1 a
    -> inner join t2 b
    -> on a.name=b.name
    -> ;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | NULL        |
|  1 | SIMPLE      | b     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

mysql> explain analyze
    -> select /*+ NO_BNL(a, b) */ a.*
    -> from t1 a
    -> inner join t2 b
    -> on a.name=b.name
    -> ;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                             |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Nested loop inner join  (cost=13.75 rows=10) (actual time=1.216..16.920 rows=18 loops=1)
    -> Table scan on a  (cost=1.25 rows=10) (actual time=0.302..1.372 rows=10 loops=1)
    -> Filter: (b.`name` = a.`name`)  (cost=0.26 rows=1) (actual time=0.642..1.291 rows=2 loops=10)
        -> Table scan on b  (cost=0.26 rows=10) (actual time=0.278..0.772 rows=10 loops=10)
 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)

注意事项：

• BNL提示相当于set optimizer_switch=‘block_nested_loop=on’; 默认为开启
• BNL提示开启时，优化器会考虑评估此算法的成本，但最终会选择最低成本的执行计划。mysql8.0增加了HASH算法，它的成本一般比BNL算法成本更低。
• BNL、NO_BNL提示优先级高于optimizer_switch变量。
• BNL、NO_BNL提示可以启用/禁用HASH JOIN。
   

3. DERIVED_CONDITION_PUSHDOWN, NO_DERIVED_CONDITION_PUSHDOWN：启用或禁用派生表条件下推

-- 派生表条件下推默认开启,会考虑把子查询外层的条件推入到子查询内部
mysql> explain 
    -> SELECT /*+ DERIVED_CONDITION_PUSHDOWN() */ * FROM 
    -> (SELECT age, count(*) cnt FROM t1 GROUP BY age) AS dt 
    -> WHERE age > 50;
+----+-------------+------------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+------------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL   | NULL    | NULL |    2 |   100.00 | NULL                     |
|  2 | DERIVED     | t1         | NULL       | range | ix_age        | ix_age | 5       | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+------------+------------+-------+---------------+--------+---------+------+------+----------+--------------------------+
2 rows in set, 1 warning (0.02 sec)

-- 从warning信息可以看出，SQL进行了改写
mysql> show warnings;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select /*+ DERIVED_CONDITION_PUSHDOWN(@`select#1`) */ `dt`.`age` AS `age`,`dt`.`cnt` AS `cnt` from (/* select#2 */ select `modb`.`t1`.`age` AS `age`,count(0) AS `cnt` from `modb`.`t1` where (`modb`.`t1`.`age` > 50) group by `modb`.`t1`.`age`) `dt` |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

-- 从最后一行 using ix_age over (50 < age) 可看出，条件已推入到子查询内部
mysql> explain analyze
    -> SELECT * FROM 
    -> (SELECT age, count(*) cnt FROM t1 GROUP BY age) AS dt 
    -> WHERE age > 50;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Table scan on dt  (cost=3.17..3.17 rows=1) (actual time=0.793..0.793 rows=0 loops=1)
    -> Materialize  (cost=0.66..0.66 rows=1) (actual time=0.610..0.610 rows=0 loops=1)
        -> Group aggregate: count(0)  (cost=0.56 rows=1) (actual time=0.451..0.451 rows=0 loops=1)
            -> Filter: (t1.age > 50)  (cost=0.46 rows=1) (actual time=0.351..0.351 rows=0 loops=1)
                -> Covering index range scan on t1 using ix_age over (50 < age)  (cost=0.46 rows=1) (actual time=0.251..0.251 rows=0 loops=1)
 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

-- 禁止派生表条件下推,可以看出,子查询先物化,然后在外层过滤age条件
mysql> explain 
    -> SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN() */ * FROM 
    -> (SELECT age, count(*) cnt FROM t1 GROUP BY age) AS dt 
    -> WHERE age > 50;
+----+-------------+------------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL   | NULL    | NULL |   10 |    33.33 | Using where |
|  2 | DERIVED     | t1         | NULL       | index | ix_age        | ix_age | 5       | NULL |   10 |   100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+--------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.01 sec)

-- 从第一行 Filter: (dt.age > 50) 可以看出，子查询先物化，最后才过滤条件
mysql> explain analyze
    -> SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN() */ * FROM 
    -> (SELECT age, count(*) cnt FROM t1 GROUP BY age) AS dt 
    -> WHERE age > 50;
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (dt.age > 50)  (cost=3.36..3.62 rows=3) (actual time=3.762..3.762 rows=0 loops=1)
    -> Table scan on dt  (cost=3.51..5.88 rows=10) (actual time=2.896..3.510 rows=5 loops=1)
        -> Materialize  (cost=3.25..3.25 rows=10) (actual time=2.706..2.706 rows=5 loops=1)
            -> Group aggregate: count(0)  (cost=2.25 rows=10) (actual time=0.735..2.097 rows=5 loops=1)
                -> Covering index scan on t1 using ix_age  (cost=1.25 rows=10) (actual time=0.487..1.241 rows=10 loops=1)
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

注意事项：

• DERIVED_CONDITION_PUSHDOWN提示相当于set optimizer_switch=‘derived_condition_pushdown=on’; 默认为开启
• DERIVED_CONDITION_PUSHDOWN提示开启时，优化器会考虑评估此算法的成本，但最终会选择最低成本的执行计划。
• DERIVED_CONDITION_PUSHDOWN、NO_DERIVED_CONDITION_PUSHDOWN提示优先级高于optimizer_switch变量。
• 派生表条件内推是常用的优化技巧，对子查询优化有较大的好处。
   

4. MERGE, NO_MERGE：启用或禁用视图合并(如视图、子查询、CTE等)

-- MERGE默认开启
mysql> explain SELECT /*+ MERGE(dt) */ * FROM (SELECT * FROM t1 where name='name1') AS dt;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

-- 从warning信息可以看出,优化器对SQL进行了改写,去掉了子查询
mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                    |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select /*+ MERGE(`dt`@`select#1`) */ `modb`.`t1`.`id` AS `id`,`modb`.`t1`.`name` AS `name`,`modb`.`t1`.`age` AS `age` from `modb`.`t1` where (`modb`.`t1`.`name` = 'name1') |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

-- NO_MERGE提示,可以看出,子查询被物化
mysql> explain SELECT /*+ NO_MERGE(dt) */ * FROM (SELECT * FROM t1 where name='name1') AS dt;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL        |
|  2 | DERIVED     | t1         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |    10.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

注意事项：

1. MERGE提示相当于set optimizer_switch=‘derived_merge=on’; 默认为开启
2. MERGE提示开启时，优化器会考虑评估此合并子查询的成本，但最终会选择最低成本的执行计划。
3. MERGE、NO_MERGE提示优先级高于optimizer_switch变量。
4. 对于NO_MERGE提示，会产生物化临时表。
5. 视图合并是常用的优化技巧，对子查询优化有较大的好处。
6. 对于视图引用，视图定义中的ALGORITHM={MERGE|TEMPTABLE}子句优先于引用该视图的查询中指定的提示。
    

示例三：索引级优化

索引级别优化提示，会影响优化器对索引的使用策略，有多种Hint：

1. GROUP_INDEX, NO_GROUP_INDEX
2. JOIN_INDEX, NO_JOIN_INDEX
3. ORDER_INDEX, NO_ORDER_INDEX
4. INDEX, NO_INDEX
5. INDEX_MERGE, NO_INDEX_MERGE
6. MRR, NO_MRR
7. NO_ICP
8. NO_RANGE_OPTIMIZATION
9. SKIP_SCAN, NO_SKIP_SCAN

准备数据

CREATE TABLE t1 (
  id int NOT NULL AUTO_INCREMENT,
  name varchar(20) DEFAULT NULL,
  age int DEFAULT NULL,
  PRIMARY KEY (id),
  KEY ix_name (name),
  KEY ix_age (age),
  KEY ix_name_age(name, age)
) ENGINE=InnoDB;
create table t2 like t1;
create table t3 like t1;

insert into t1(id, name, age) values(1, 'name1', 10);
insert into t2(id, name, age) values(1, 'name1', 10);
insert into t2(id, name, age) values(2, 'name2', 20);
insert into t3(id, name, age) values(1, 'name1', 10);
insert into t3(id, name, age) values(2, 'name2', 20);
insert into t3(id, name, age) values(3, 'name3', 30);

mysql> select * from t1;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | name1 |   10 |
+----+-------+------+
1 row in set (0.00 sec)

mysql> select * from t2;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | name1 |   10 |
|  2 | name2 |   20 |
+----+-------+------+
2 rows in set (0.00 sec)

mysql> select * from t3;
+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | name1 |   10 |
|  2 | name2 |   20 |
|  3 | name3 |   30 |
+----+-------+------+
3 rows in set (0.00 sec)

1. GROUP_INDEX, NO_GROUP_INDEX：为GROUP BY操作的索引扫描启用或禁用指定的索引。相当于5.7版本中的：FORCE INDEX FOR GROUP BY, IGNORE INDEX FOR GROUP BY。举例如下：

-- 默认选择ix_name索引进行GROUP BY
mysql> explain select  name,count(*) from t1 group by name;
+----+-------------+-------+------------+-------+---------------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys       | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | ix_name,ix_name_age | ix_name | 83      | NULL |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

-- 提示选择ix_name_age索引进行GROUP BY
mysql> explain select /*+ GROUP_INDEX(t1 ix_name_age) */ name,count(*) from t1 group by name;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | ix_name_age   | ix_name_age | 88      | NULL |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

-- 提示选择ix_age索引进行GROUP BY，由于此索引无法用于GROUP BY，故走了全表扫
mysql> explain select /*+ GROUP_INDEX(t1 ix_age) */ name,count(*) from t1 group by name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra           |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using temporary |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)

-- 提示禁用ix_name索引进行GROUP BY
mysql> explain select /*+ NO_GROUP_INDEX(t1 ix_name) */ name,count(*) from t1 group by name;
+----+-------------+-------+------------+-------+---------------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys       | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | ix_name,ix_name_age | ix_name_age | 88      | NULL |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

-- 提示禁用ix_name, ix_name_age索引进行GROUP BY, 当禁用所有可用索引时, 优化器,自行选择一个可用索引或全表扫描
mysql> explain select /*+ NO_GROUP_INDEX(t1 ix_name, ix_name_age) */ name,count(*) from t1 group by name;
+----+-------------+-------+------------+-------+---------------------+---------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type  | possible_keys       | key     | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+-------+---------------------+---------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | ix_name,ix_name_age | ix_name | 83      | NULL |    1 |   100.00 | Using index; Using temporary |
+----+-------------+-------+------------+-------+---------------------+---------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

-- 提示禁用ix_name索引进行GROUP BY
mysql> explain select /*+ NO_GROUP_INDEX(t1 ix_name) */ name,count(*) from t1 group by name;
+----+-------------+-------+------------+-------+---------------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys       | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | index | ix_name,ix_name_age | ix_name_age | 88      | NULL |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

-- 提示禁用ix_name, ix_name_age索引进行GROUP BY, 当禁用所有可用索引时, 优化器自行决定使用哪个索引(注：即使使用了索引，也不会利用索引的有序性，而是产生了临时表)
mysql> explain select /*+ NO_GROUP_INDEX(t1 ix_name, ix_name_age) */ name,count(*) from t1 group by name;
+----+-------------+-------+------------+-------+---------------------+---------+---------+------+------+----------+------------------------------+
| id | select_type | table | partitions | type  | possible_keys       | key     | key_len | ref  | rows | filtered | Extra                        |
+----+-------------+-------+------------+-------+---------------------+---------+---------+------+------+----------+------------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | ix_name,ix_name_age | ix_name | 83      | NULL |    1 |   100.00 | Using index; Using temporary |
+----+-------------+-------+------------+-------+---------------------+---------+---------+------+------+----------+------------------------------+
1 row in set, 1 warning (0.00 sec)

-- 禁用所有可用索引后，优化器使用ix_name索引的行为(虽然使用了ix_name索引，但无法利用此索引的有序性进行GROUP BY):
explain analyze select /*+ NO_GROUP_INDEX(t1 ix_name, ix_name_age) */ name,count(*) from t1 group by name;
/*
-> Table scan on <temporary>  (actual time=3.118..3.193 rows=1 loops=1)
    -> Aggregate using temporary table  (actual time=2.966..2.966 rows=1 loops=1)
        -> Covering index scan on t1 using ix_name  (cost=0.35 rows=1) (actual time=0.262..0.391 rows=1 loops=1)
*/

-- 优化器使用ix_name索引的行为(用到了ix_name索引的有序性进行GROUP BY):
explain analyze select name,count(*) from t1 group by name;
/*
-> Group aggregate: count(0)  (cost=0.45 rows=1) (actual time=0.602..0.625 rows=1 loops=1)
    -> Covering index scan on t1 using ix_name  (cost=0.35 rows=1) (actual time=0.230..0.471 rows=1 loops=1)
*/

注意事项：

GROUP_INDEX：Hint使用指定的一个或多个索引进行GROUP BY操作，如果Hint的索引无法作用于GROUP BY操作，则全表扫描。
NO_GROUP_INDEX：Hint禁用指定的一个或多个索引进行GROUP BY操作，如果禁用掉了所有可用索引，优化器自行选择使用索引或全表扫，这儿要注意，即使使用了合适的索引，也不会用到索引的有序性进行GROUP BY操作。

2. JOIN_INDEX, NO_JOIN_INDEX：强制MySQL使用或忽略指定索引进行表之间的JOIN。相当于5.7版本中的：FORCE INDEX FOR JOIN, IGNORE INDEX FOR JOIN。举例如下：

-- 两表JOIN时,优化默认选择合适的索引
mysql> explain select a.name from t1 a inner join t2 b on a.name=b.name;
+----+-------------+-------+------------+-------+---------------------+---------+---------+-------------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys       | key     | key_len | ref         | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------------+---------+---------+-------------+------+----------+--------------------------+
|  1 | SIMPLE      | a     | NULL       | index | ix_name,ix_name_age | ix_name | 83      | NULL        |    1 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | b     | NULL       | ref   | ix_name,ix_name_age | ix_name | 83      | modb.a.name |    1 |   100.00 | Using index              |
+----+-------------+-------+------------+-------+---------------------+---------+---------+-------------+------+----------+--------------------------+
2 rows in set, 1 warning (0.01 sec)

-- 两表JOIN时,Hint使用指定的索引进行JOIN
mysql> explain select /*+ JOIN_INDEX(a ix_name_age) JOIN_INDEX(b ix_name_age) */ a.name from t1 a inner join t2 b on a.name=b.name;
+----+-------------+-------+------------+-------+---------------+-------------+---------+-------------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref         | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-------------+---------+-------------+------+----------+--------------------------+
|  1 | SIMPLE      | a     | NULL       | index | ix_name_age   | ix_name_age | 88      | NULL        |    1 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | b     | NULL       | ref   | ix_name_age   | ix_name_age | 83      | modb.a.name |    1 |   100.00 | Using index              |
+----+-------------+-------+------------+-------+---------------+-------------+---------+-------------+------+----------+--------------------------+
2 rows in set, 1 warning (0.00 sec)

-- 两表JOIN时,Hint忽略使用指定的索引进行JOIN,结果导致了hash join
mysql> explain select /*+ NO_JOIN_INDEX(b ix_name, ix_name_age) */ a.name from t1 a inner join t2 b on a.name=b.name;
+----+-------------+-------+------------+-------+---------------------+---------+---------+------+------+----------+---------------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys       | key     | key_len | ref  | rows | filtered | Extra                                                   |
+----+-------------+-------+------------+-------+---------------------+---------+---------+------+------+----------+---------------------------------------------------------+
|  1 | SIMPLE      | a     | NULL       | index | ix_name,ix_name_age | ix_name | 83      | NULL |    1 |   100.00 | Using index                                             |
|  1 | SIMPLE      | b     | NULL       | index | NULL                | ix_name | 83      | NULL |    2 |    50.00 | Using where; Using index; Using join buffer (hash join) |
+----+-------------+-------+------------+-------+---------------------+---------+---------+------+------+----------+---------------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

注意事项：

JOIN_INDEX：指定索引进行JOIN，如果索引不适用，则走全表扫描。
NO_JOIN_INDEX：忽略指定的索引进行JOIN。相当于在这些索引不存在的情况，由优化器决定如何JOIN。

3. ORDER_INDEX, NO_ORDER_INDEX：强制MySQL使用或忽略指定索引进行排序。相当于5.7版本中的：FORCE INDEX FOR ORDER, IGNORE INDEX FOR ORDER。举例如下：
 

-- 默认排序
mysql> explain select a.age from t1 a order by a.name;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | index | NULL          | ix_name_age | 88      | NULL |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

-- 使用指定的索引进行排序(需要回表)
mysql> explain select /*+ ORDER_INDEX(a ix_name) */ a.age from t1 a order by a.name;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
|  1 | SIMPLE      | a     | NULL       | index | NULL          | ix_name | 83      | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.01 sec)

-- 当指定的索引不适合排序时，全表扫描并filesort
mysql> explain select /*+ ORDER_INDEX(a ix_age) */ a.age from t1 a order by a.name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

-- 忽略使用指定的索引排序，再由优化器决定最优的执行计划(注意：还是用到了ix_name_age覆盖索引扫描和filesort)。
mysql> explain select /*+ NO_ORDER_INDEX(a ix_name_age) */ a.age from t1 a order by a.name;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | a     | NULL       | index | NULL          | ix_name_age | 88      | NULL |    1 |   100.00 | Using index; Using filesort |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

注意事项：

ORDER_INDEX：指定索引来排序，如果索引不适合排序，则走全表扫描并filesort。
NO_ORDER_INDEX：忽略指定的索引进行排序。仅是忽略指定的索引用于排序(忽略的索引还可能被使用，但不会用于排序)，再由优化器生成最优的执行计划。

4. INDEX, NO_INDEX：相当于[NO]_GROUP_INDEX、[NO]_JOIN_INDEX、[NO]_ORDER_INDEX的组合, 可以在GROUP、JOIN、ORDER操作上强制使用或忽略索引。相当于5.7版本中的：FORCE INDEX, IGNORE INDEX。举例如下：
 

-- 默认使用索引ix_name_age扫描并排序
mysql> explain select a.age from t1 a order by a.name;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | index | NULL          | ix_name_age | 88      | NULL |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

-- 强制走索引ix_name(隐含了回表)
mysql> explain select /*+ INDEX(a ix_name) */ a.age from t1 a order by a.name;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
|  1 | SIMPLE      | a     | NULL       | index | NULL          | ix_name | 83      | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

-- 强制的索引不适用时，生成全表扫描的执行计划
mysql> explain select /*+ INDEX(a ix_age) */ a.age from t1 a order by a.name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

-- 忽略掉指定的索引后，由优化器生成最优执行计划
mysql> explain select /*+ NO_INDEX(a ix_name, ix_name_age) */ a.age from t1 a order by a.name;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | a     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.01 sec)

注意事项：

这是我们最常用的索引Hint方式，/*+ */语法避免了索引不存在时SQL报错。
INDEX：指定索引进行GROUP、JOIN、ORDER，如果索引不适用，则走全表扫描。
NO_INDEX：忽略指定的索引进行GROUP、JOIN、ORDER操作后，由优化器生成最优执行计划。

5. INDEX_MERGE, NO_INDEX_MERGE：指定或忽略多个索引进行合并优化。举例如下：

-- 索引合并交叉访问算法(Index Merge Intersection Access Algorithm)
mysql> explain select /*+ INDEX_MERGE(a ix_name,ix_age) */ id from t3 a where name = 'name1' and age = 30;
+----+-------------+-------+------------+-------------+----------------------------+----------------+---------+------+------+----------+-----------------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys              | key            | key_len | ref  | rows | filtered | Extra                                                     |
+----+-------------+-------+------------+-------------+----------------------------+----------------+---------+------+------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | a     | NULL       | index_merge | ix_name,ix_age,ix_name_age | ix_name,ix_age | 83,5    | NULL |    1 |   100.00 | Using intersect(ix_name,ix_age); Using where; Using index |
+----+-------------+-------+------------+-------------+----------------------------+----------------+---------+------+------+----------+-----------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

-- 索引合并联合访问算法(Index Merge Union Access Algorithm)
mysql> explain select /*+ INDEX_MERGE(a ix_name,ix_age) */ id from t3 a where name='name1' or age=30;
+----+-------------+-------+------------+-------------+----------------------------+----------------+---------+------+------+----------+------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys              | key            | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+-------+------------+-------------+----------------------------+----------------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | a     | NULL       | index_merge | ix_name,ix_age,ix_name_age | ix_name,ix_age | 83,5    | NULL |    2 |   100.00 | Using union(ix_name,ix_age); Using where |
+----+-------------+-------+------------+-------------+----------------------------+----------------+---------+------+------+----------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)

-- 索引合并排序联合访问算法(Index Merge Sort-Union Access Algorithm)
mysql> explain select /*+ INDEX_MERGE(a ix_name,ix_age) */ id from t3 a where name < 'name2' or age < 20;
+----+-------------+-------+------------+-------------+----------------------------+----------------+---------+------+------+----------+-----------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys              | key            | key_len | ref  | rows | filtered | Extra                                         |
+----+-------------+-------+------------+-------------+----------------------------+----------------+---------+------+------+----------+-----------------------------------------------+
|  1 | SIMPLE      | a     | NULL       | index_merge | ix_name,ix_age,ix_name_age | ix_name,ix_age | 83,5    | NULL |    2 |   100.00 | Using sort_union(ix_name,ix_age); Using where |
+----+-------------+-------+------------+-------------+----------------------------+----------------+---------+------+------+----------+-----------------------------------------------+
1 row in set, 1 warning (0.01 sec)

-- 忽略指定的索引合并后，由优化器生成最优执行计划
mysql> explain select /*+ NO_INDEX_MERGE(a ix_name,ix_age) */ id from t3 a where name = 'name1' and age = 30;
+----+-------------+-------+------------+------+----------------------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys              | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+----------------------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | ref  | ix_name,ix_age,ix_name_age | ix_name | 83      | const |    1 |    50.00 | Using where |
+----+-------------+-------+------------+------+----------------------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

-- 查看对应的开关
mysql> show variables like 'optimizer_switch';
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name    | Value                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,hypergraph_optimizer=off,derived_condition_pushdown=on |
+------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

-- INDEX_MERGE提示未指定任何索引，则优化器自动使用a表所有可能的索引组合
mysql> explain select /*+ INDEX_MERGE(a) */ id from t3 a where name < 'name2' or age < 20;
+----+-------------+-------+------------+-------------+----------------------------+----------------+---------+------+------+----------+-----------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys              | key            | key_len | ref  | rows | filtered | Extra                                         |
+----+-------------+-------+------------+-------------+----------------------------+----------------+---------+------+------+----------+-----------------------------------------------+
|  1 | SIMPLE      | a     | NULL       | index_merge | ix_name,ix_age,ix_name_age | ix_name,ix_age | 83,5    | NULL |    2 |   100.00 | Using sort_union(ix_name,ix_age); Using where |
+----+-------------+-------+------------+-------------+----------------------------+----------------+---------+------+------+----------+-----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

-- NO_INDEX_MERGE提示未指定任何索引，则表不允许索引合并。
mysql> explain select /*+ NO_INDEX_MERGE(a) */ id from t3 a where name < 'name2' or age < 20;
+----+-------------+-------+------------+-------+----------------------------+-------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys              | key         | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+----------------------------+-------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | a     | NULL       | index | ix_name,ix_age,ix_name_age | ix_name_age | 88      | NULL |    2 |    75.00 | Using where; Using index |
+----+-------------+-------+------------+-------+----------------------------+-------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

注意事项：

• 只适合合并单个表中的索引扫描，产生交集、并集或交集的并集。
• 在EXPLAIN输出中，索引合并方法在type列中显示为index_merge。key列包含使用的索引列表，key_len包含这些索引的最长键部分列表
• 索引合并访问有3种算法，它们显示在EXPLAIN输出的Extra字段中：Using intersect、Using union、Using sort_union
• 此Hint和3种算法分别对应optimizer_switch中的：index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on
• INDEX_MERGE：强制优化器使用指定的索引集进行索引合并，如果没有指定任何索引，优化器将考虑所有可能的索引组合，如果索引合并不适用于SQL，则忽略此提示
• NO_INDEX_MERGE：忽略包含任何指定索引的索引合并组合。如果提示指定没有索引，则表不允许索引合并。

6. MRR, NO_MRR：为指定的表或索引启用或禁用MRR。举例如下：

-- 默认的执行计划
mysql> explain select * from t3 where age between 1 and 3;
+----+-------------+-------+------------+-------+--------------------+-------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys      | key         | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+--------------------+-------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t3    | NULL       | index | ix_age,ix_name_age | ix_name_age | 88      | NULL |    2 |    50.00 | Using where; Using index |
+----+-------------+-------+------------+-------+--------------------+-------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

-- 使用MRR算法的执行计划(强制使用ix_age索引，以便走MRR算法)
mysql> explain select /*+ MRR(t3) INDEX(t3 ix_age) */ * from t3 where age between 1 and 3;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+----------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                            |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+----------------------------------+
|  1 | SIMPLE      | t3    | NULL       | range | ix_age        | ix_age | 5       | NULL |    1 |   100.00 | Using index condition; Using MRR |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+----------------------------------+
1 row in set, 1 warning (0.00 sec)

-- 不使用MRR算法的执行计划(强制使用ix_age索引，以便走MRR算法)
mysql> explain select /*+ NO_MRR(t3) INDEX(t3 ix_age) */ * from t3 where age between 1 and 3;
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t3    | NULL       | range | ix_age        | ix_age | 5       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

注意事项：

1. MRR提示只适用于InnoDB和MyISAM表
2. 当使用MRR时，EXPLAIN输出中的Extra列显示: Using MRR
3. 想启用MRR，可在optimizer_switch变量中设置：mrr=on(默认on)和mrr_cost_based=off(默认on根据成本估算是否使用MRR算法，off的话不估算成本)。或使用MRR提示。
4. 对于MRR，存储引擎使用read_rnd_buffer_size变量值为其缓冲区分配内存

7. NO_ICP：禁用表或索引的索引下推。举例如下：

-- 添加一个字段，避免走覆盖索引时，不使用ICP
mysql> alter table t3 add column sex tinyint;
Query OK, 0 rows affected (0.29 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- 使用索引ix_name_age，引导走ICP
mysql> explain select /*+ INDEX(a ix_name_age) */ * from t3 a where a.name > 'name1' and age > 1;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | a     | NULL       | range | ix_name_age   | ix_name_age | 83      | NULL |    2 |    33.33 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

-- 走覆盖索引时，不使用ICP
mysql> explain select /*+ INDEX(a ix_name_age) */ id,name,age from t3 a where a.name > 'name1' and age > 1;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | a     | NULL       | range | ix_name_age   | ix_name_age | 83      | NULL |    2 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

-- 禁用ICP
mysql>  explain select /*+ INDEX(a ix_name_age) NO_ICP(a) */ * from t3 a where a.name > 'name1' and age > 1;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | range | ix_name_age   | ix_name_age | 83      | NULL |    2 |    33.33 | Using where |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

注意事项：

默认情况下，ICP是候选优化策略，因此没有启用它的提示。也就是说，ICP总是好的
走覆盖索引时不使用ICP
其它关系型数据库（如Oracle等），总会使用类似"ICP"方法

8. NO_RANGE_OPTIMIZATION：禁用指定表或索引的索引范围访问，此提示还禁用表或索引的索引合并和松散索引扫描。举例如下：

-- 使用索引范围优化的执行计划(默认)
mysql> explain SELECT age FROM t3 WHERE age > 1 AND age < 3;
+----+-------------+-------+------------+-------+--------------------+--------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys      | key    | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+--------------------+--------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t3    | NULL       | range | ix_age,ix_name_age | ix_age | 5       | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+--------------------+--------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)

-- 强制不使用索引范围优化生成的执行计划(注意：不再根据条件范围进行扫描，而是全索引扫描后使用了filtered)
mysql> explain SELECT /*+ NO_RANGE_OPTIMIZATION(t3 ix_age) */ age FROM t3 WHERE age > 1 AND age < 3;
+----+-------------+-------+------------+-------+--------------------+--------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys      | key    | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+--------------------+--------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t3    | NULL       | index | ix_age,ix_name_age | ix_age | 5       | NULL |    3 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+--------------------+--------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)

注意事项：

默认情况下，范围访问是一个候选优化策略，因此没有启用它的提示。
但当范围的数量可能很高并且范围优化需要很多资源时，这个提示可能很有用。

9. SKIP_SCAN, NO_SKIP_SCAN：为指定的表或索引启用或禁用跳扫。举例如下：

-- 默认生成的执行计划
mysql> EXPLAIN SELECT name,age FROM t3 WHERE age > 20;
+----+-------------+-------+------------+-------+--------------------+-------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys      | key         | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+--------------------+-------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t3    | NULL       | index | ix_age,ix_name_age | ix_name_age | 88      | NULL |    3 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+--------------------+-------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

-- 强制使用索引ix_name_age跳扫产生的执行计划(因为是跳扫，最终的filtered为100.00)
mysql> EXPLAIN SELECT /*+ SKIP_SCAN(t3 ix_name_age) */ name,age FROM t3 WHERE age > 20;
+----+-------------+-------+------------+-------+--------------------+-------------+---------+------+------+----------+----------------------------------------+
| id | select_type | table | partitions | type  | possible_keys      | key         | key_len | ref  | rows | filtered | Extra                                  |
+----+-------------+-------+------------+-------+--------------------+-------------+---------+------+------+----------+----------------------------------------+
|  1 | SIMPLE      | t3    | NULL       | range | ix_age,ix_name_age | ix_name_age | 88      | NULL |    3 |   100.00 | Using where; Using index for skip scan |
+----+-------------+-------+------------+-------+--------------------+-------------+---------+------+------+----------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)

-- 强制使用索引ix_name_age且不跳扫产生的执行计划
mysql> EXPLAIN SELECT /*+ NO_SKIP_SCAN(t3 ix_name_age) */ name,age FROM t3 WHERE age > 20;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t3    | NULL       | index | ix_age        | ix_name_age | 88      | NULL |    3 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)

-- 不指定索引，让优化器自行选择合适索引去跳扫
mysql> EXPLAIN SELECT /*+ SKIP_SCAN(t3) */ name,age FROM t3 WHERE age > 20;
+----+-------------+-------+------------+-------+--------------------+-------------+---------+------+------+----------+----------------------------------------+
| id | select_type | table | partitions | type  | possible_keys      | key         | key_len | ref  | rows | filtered | Extra                                  |
+----+-------------+-------+------------+-------+--------------------+-------------+---------+------+------+----------+----------------------------------------+
|  1 | SIMPLE      | t3    | NULL       | range | ix_age,ix_name_age | ix_name_age | 88      | NULL |    3 |   100.00 | Using where; Using index for skip scan |
+----+-------------+-------+------------+-------+--------------------+-------------+---------+------+------+----------+----------------------------------------+
1 row in set, 1 warning (0.01 sec)

-- 不指定索引，对此表禁用跳扫
mysql> EXPLAIN SELECT /*+ NO_SKIP_SCAN(t3) */ name,age FROM t3 WHERE age > 20;
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t3    | NULL       | index | ix_age        | ix_name_age | 88      | NULL |    3 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

注意事项：

默认情况下，范围访问是一个候选优化策略，因此没有启用它的提示。
SKIP_SCAN提示强制优化器使用指定的索引集对指定的表使用跳过扫描。如果没有指定索引，优化器将考虑所有可能的索引，并选择代价最小的索引。如果索引不适用于给定语句，则可以忽略提示
NO_SKIP_SCAN提示对指定的索引禁用跳过扫描。如果提示指定没有索引，则不允许对表进行跳过扫描。

10. 其它一些Hint规则和注意事项：

如果指定了优化器提示并且是适用的，它优先于optimizer_switch系统变量
索引级优化器提示GROUP_INDEX、NO_GROUP_INDEX、INDEX、NO_INDEX、JOIN_INDEX、NO_JOIN_INDEX、ORDER_INDEX和NO_ORDER_INDEX都优先于所有其他优化器提示，包括其他索引级优化器提示。任何其他优化器提示仅应用于这些索引所允许的索引。
使用多个提示时，注意提示之间的优化级，当提示有冲突时，优化器会根据优先级处理。
如果explain时，提示有问题，可通过show warnings;查看原因。
 

八、总结

MySQL Hints提供了一种灵活的方式，让开发者能够手动控制查询优化器的行为，从而实现特定的性能优化目标。

在使用Hints时，需要充分理解其语法和作用范围，并结合实际需求进行合理配置。

同时，Hints应作为优化手段的补充，而不是唯一手段，建议优先通过索引优化、查询重写等方式提升性能。

通过合理使用Hints，我们可以更好地控制查询优化器的选择，提升MySQL查询的整体性能。

优化器Hints与optimizer_switch的区别

1. optimizer_switch：这是一个系统变量，通过它可以开启或关闭某些优化器的特性或策略。改变这个变量会影响所有后续的查询执行。因此，如果你需要对不同的查询应用不同的优化策略，你需要在每个查询之前更改optimizer_switch，这在实际操作中可能会很不方便。
2. 优化器Hints：与optimizer_switch不同，优化器Hints允许你在单个SQL语句中指定优化策略。这种方法提供了更精细的控制，因为你可以针对每个查询或查询中的特定表指定不同的优化策略。此外，语句中的Hints会覆盖optimizer_switch的设置。

使用Hints的注意事项

1. 谨慎使用：过度或不当地使用Hints可能会导致性能下降，因为它们可能会覆盖优化器的智能决策。
2. 测试和验证：在应用Hints之前和之后，都要对查询性能进行彻底的测试，以确保它们确实带来了预期的提升。
3. 版本兼容性：不是所有的MySQL版本都支持所有的Hints，因此在使用前要检查你的MySQL版本是否支持所需的Hints。
4. 可维护性：在SQL查询中嵌入Hints可能会降低代码的可读性和可维护性。确保团队成员都了解并同意使用这些Hints。
5. 监控和调优：即使使用了Hints，也应该定期监控查询性能，并根据需要进行调整。