参考官方文档：

https://dev.mysql.com/doc/refman/5.7/en/controlling-optimizer.html

MySQL通过影响查询计划如何评估，可切换优化，优化器和索引提示以及优化器成本模型的系统变量提供优化器控制。

8.9.1 控制查询计划评估

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

用于查询优化的更灵活的方法，让用户能够控制优化器在搜索最优查询评估计划时的详尽程度。 总体思路是，优化程序调查的计划越少，编译查询花费的时间就越少。 另一方面，因为优化器跳过了一些计划，所以可能会错过找到最佳计划。

可以使用两个系统变量来控制优化程序相对于其计算的计划数量的行为：

• optimizer_prune_level变量告诉优化器根据每个表访问的行数的估计值跳过某些计划。 我们的经验表明，这种“ educated guess”很少会错过最佳计划，并且可能大大减少查询编译时间。 这就是为什么默认情况下该选项开启（optimizer_prune_level = 1）的原因。 但是，如果您认为优化程序错过了更好的查询计划，则可能会关闭此选项（optimizer_prune_level = 0），因为查询编译可能需要更长的时间。 请注意，即使使用这种启发式，优化器仍然会探索大致指数级的计划。
• optimizer_search_depth变量说明优化程序应该看看每个不完整计划的“未来”有多远，以评估它是否应该进一步扩展。 optimizer_search_depth的较小值可能导致查询编译时间缩短了几个数量级。 例如，如果optimizer_search_depth接近查询中的表的数量，那么具有12,13或更多表的查询可能很容易需要几个小时甚至几天来编译。 同时，如果使用optimizer_search_depth编译等于3或4，优化程序可以在不到一分钟的时间内编译相同的查询。 如果您不确定optimizer_search_depth的合理值，则可以将此变量设置为0，以通知优化器自动确定该值。

8.9.2 控制 Switchable Optimizations

optimizer_switch系统变量启用对优化器行为的控制。 它的值是一组标志，其中每个标志都具有打开或关闭的值，以指示相应的优化器行为是启用还是禁用。 该变量具有全局和会话值，并且可以在运行时更改。 全局默认值可以在服务器启动时设置。

要查看当前的优化器标志集，请选择变量值：

要更改optimizer_switch的值，请指定一个由逗号分隔的一个或多个命令列表组成的值：

SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';

值中的命令顺序无关紧要，但默认命令如果存在则首先执行。 将opt_name标志设置为default会将其设置为on或off的默认值。 不允许在该值中多次指定任何给定的opt_name，会导致错误。 值中的任何错误都会导致赋值失败并返回错误，并保持optimizer_switch的值不变。

batched_key_access设置为on时是有影响的，mrr标志也必须打开。 目前，MRR的成本估算过于悲观。 因此，对于使用BKA来说，关闭mrr_cost_based也是必要的。

semijoin，firstmatch，loosescan，duplicateweedout和materialization标志可以控制半连接和子查询实现策略。 semijoin标志控制是否使用半连接。 如果它设置为on，则firstmatch和loosescan标志可以更好地控制允许的半连接策略。 materialization 标志控制是否使用子查询materialization 。 如果半连接和materialization 都处于打开状态，则半连接也在适用的情况下使用materialization 。 这些标志默认打开。

如果禁用了duplicateweedout半连接策略，则不会使用它，除非所有其他适用的策略也被禁用。

subquery_materialization_cost_based标志可以控制子查询实现和IN-to-EXISTS子查询转换之间的选择。 如果该标志处于打开状态（缺省状态），则优化程序可在子查询实现和IN-to-EXISTS子查询转换之间执行基于开销的选择，只要使用任一方法即可。 如果标志关闭，优化器会选择IN - > EXISTS子查询转换的子查询实现。

derived_merge标志控制优化器是否尝试合并派生表并将引用视为外部查询块，假定没有其他规则会阻止合并; 例如，视图的ALGORITHM指令优先于derived_merge设置。 默认情况下，该标志启用以启用合并。

当您将值赋给optimizer_switch时，未提及的标志会保留其当前值。 这样可以在单个语句中启用或禁用特定的优化器行为，而不会影响其他行为。 该语句不依赖于其他优化器标志存在以及它们的值是什么。 假设启用了所有索引合并优化：

如果服务器对某些查询使用索引合并联合或索引合并排序联合访问方法，并且您希望检查优化程序在没有它们的情况下是否会更好地执行，请设置变量值，如下所示：

SET optimizer_switch='index_merge_union=off,index_merge_sort_union=off';

8.9.3 优化器hint

控制优化器策略的一种方法是设置optimizer_switch系统变量。 对此变量的更改影响所有后续查询的执行; 要影响一个查询与另一个查询不同，需要在每个查询之前更改optimizer_switch。

--上述讲的

在5.7.7，另一种控制优化器的方式是使用优化器提示，它可以在单个语句中指定。 因为优化器提示基于每个语句应用，所以它们比使用optimizer_switch可以实现更好地控制语句执行计划。 例如，您可以在语句中为一个表启用优化，并禁用针对其他表的优化。 语句中的提示优先于optimizer_switch标志。

例子：

SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1 FROM t3 WHERE f1 > 30 AND f1 < 33;

SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...;

SELECT /*+ NO_ICP(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...;

SELECT /*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */ * FROM t1 ...;

EXPLAIN SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE ...;

注意：

默认情况下，mysql客户端从发送到服务器的SQL语句（包括优化器提示）中删除注释，直到MySQL 5.7.7更改为将优化器提示传递给服务器。 要确保如果您使用旧版本的mysql客户端与理解优化器提示的服务器版本不会剥离优化器提示，请使用--comments选项调用mysql。

此处介绍的优化器提示与第8.9.4节“索引提示”中介绍的索引提示有所不同。 优化器和索引提示可以单独使用或一起使用。

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

• 全局：提示影响整个陈述

• 查询块：提示影响语句中的特定查询块

• 表级别：提示影响查询块中的特定表

• 索引级别：提示影响表中的特定索引

下表总结了可用的优化器提示，它们影响的优化器策略以及它们所应用的范围。 更多细节在后面给出。

可用的优化器hint

禁用一个优化可防止优化器使用它。 启用一个优化意味着如果优化程序适用于语句执行，则可以自由使用策略，而不是优化程序必须使用它。（启用一个，并不代表优化器一定会使用，优化器会自己选择）

优化器 Hint 语法

如第9.6节“注释语法”中所述，MySQL支持SQL语句中的注释。 优化器提示必须在/ * + ... * /注释中指定。 也就是说，优化器提示使用/ * ... * / C风格注释语法的变体，并在/ *注释打开序列后面加上+字符。 例子：

/*+ BKA(t1) */

/*+ BNL(t1, t2) */

/*+ NO_RANGE_OPTIMIZATION(t4 PRIMARY) */

/*+ QB_NAME(qb2) */

+字符后允许使用空格

解析器识别SELECT，UPDATE，INSERT，REPLACE和DELETE语句的初始关键字后的优化器提示注释。 在这些情况下允许提示：

• 在查询和数据更改语句的开始处：

SELECT /*+ ... */ ...

INSERT /*+ ... */ ...

REPLACE /*+ ... */ ...

UPDATE /*+ ... */ ...

DELETE /*+ ... */ ...

• 在查询块的开始处

(SELECT /*+ ... */ ... )

(SELECT ... ) UNION (SELECT /*+ ... */ ... )

(SELECT /*+ ... */ ... ) UNION (SELECT /*+ ... */ ... )

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

INSERT ... SELECT /*+ ... */ ...

• 在由EXPLAIN开头的暗示性陈述中。 例如：

EXPLAIN SELECT /*+ ... */ ...

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

其含义是您可以使用EXPLAIN来查看优化器提示如何影响执行计划。 在EXPLAIN之后立即使用SHOW WARNINGS来查看提示是如何使用的。 由以下SHOW WARNINGS显示的扩展EXPLAIN输出指示使用了哪些提示。 不显示忽略的hint。

提示注释可能包含多个提示，但查询块不能包含多个提示注释。 这些事有效的：

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

但是下面是无效的

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

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

• 重复提示：对于诸如/ * + MRR（idx1）MRR（idx1）* /等提示，MySQL使用第一个提示并发出关于重复提示的警告。
• 冲突提示：对于诸如/ * + MRR（idx1）NO_MRR（idx1）* /等提示，MySQL使用第一个提示并发出关于第二个冲突提示的警告。

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

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

表-级别 优化hint

表级提示影响使用块嵌套循环（BNL）和批处理密钥访问（BKA）连接处理算法（请参见第8.2.1.11节“块嵌套循环和批处理键访问连接”）。 这些提示类型适用于特定表或查询块中的所有表。

表级提示的语法：

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

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

语法引用了这些术语：

• hint_name：这些提示名称是允许的：
• BKA, NO_BKA:为指定的表启用或禁用BKA。
• BNL, NO_BNL: 启用或禁用指定表的BNL。

注意：

要使用BNL或BKA提示为外连接的任何inner表启用连接缓冲，必须为外连接的所有inner表启用连接缓冲。

• tbl_name：声明中使用的表的名称。 该提示适用于它命名的所有表。 如果提示不命名表，它将应用于它发生的查询块的所有表。
• 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 /*+ BNL(t2) */ FROM t1, t2;

如果优化器选择首先处理t1，则在开始从t2读取数据之前，通过缓冲来自t1的行来将块Nested-Loop连接应用于t2。 如果优化器选择首先处理t2，则提示不起作用，因为t2是发送者表。

索引级别 优化 hints

索引级提示会影响优化程序对特定表或索引使用的索引处理策略。 这些提示类型影响使用索引条件下推（ICP），多范围读取（MRR）和范围优化（请参见第8.2.1节“优化SELECT语句”）。

索引-级别 hints 语法：

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

语法引用了这些术语：

• hint_name：这些提示名称是允许的：
• MRR，NO_MRR：启用或禁用指定表或索引的MRR。 MRR提示仅适用于InnoDB和MyISAM表。
• NO_ICP:禁用指定表或索引的ICP。 默认情况下，ICP是一个候选优化策略，所以没有启用它的提示。
• NO_RANGE_OPTIMIZATION：禁止指定表或索引的索引范围访问。 此提示还会禁用索引合并和索引扫描以查找表或索引。 默认情况下，范围访问是一个候选优化策略，所以没有启用它的提示。

当范围数可能很高并且范围优化需要许多资源时，这个提示可能是有用的。

• tbl_name：提示适用的表
• index_name：指定表中索引的名称。 该提示适用于它命名的所有索引。 如果提示不命名索引，则它适用于表中的所有索引。
• 要引用主键，请使用名称PRIMARY。 要查看表的索引名称，请使用SHOW INDEX。
• query_block_name：提示适用的查询块。 如果提示不包含前导@query_block_name，则该提示将应用于其发生的查询块。 对于tbl_name @ query_block_name语法，该提示适用于指定查询块中的指定表。

例子：

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);

子查询 优化hints

子查询提示（在MySQL 5.7.8中添加）会影响是否使用半连接转换以及哪些半连接策略允许，以及在不使用半连接时是否使用子查询实现或IN-EXISTS转换。

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

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

语法引用了这些术语：

• hint_name：这些提示名称是允许的：
• SEMIJOIN，NO_SEMIJOIN：启用或禁用被命名的半连接策略
• strategy: 启用或禁用的半联接策略。 这些策略名称是允许的：DUPSWEEDOUT，FIRSTMATCH，LOOSESCAN，MATERIALIZATION。

对于SEMIJOIN提示，如果没有策略被命名，则根据optimizer_switch系统变量启用的策略尽可能使用半连接。 如果策略被命名但不适用于该语句，则使用DUPSWEEDOUT。

对于NO_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-EXISTS转换的提示语法：

SUBQUERY([@query_block_name] strategy)

hint 的名称总是SUBQUERY

对于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);

对于半连接和SUBQUERY提示，一个领先的@query_block_name指定提示适用的查询块。 如果提示不包含前导@query_block_name，则该提示将应用于其发生的查询块。

如果提示注释包含多个子查询提示，则使用第一个。 如果还有其他类型的提示，则会发出警告。 其他类型的提示被默默忽略。

语句执行时间优化器hint

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系统变量建立的语句超时。（在MySQL 5.7.8之前，这个变量被命名为 max_statement_time。）

MAX_EXECUTION_TIME() 提示适用如下：

• 对于具有多个SELECT关键字的语句（如联合或具有子查询的语句），MAX_EXECUTION_TIME()适用于整个语句，并且必须出现在第一个SELECT之后。
• 它适用于只读的SELECT语句。 不是只读的语句是那些调用一个存储函数，将数据修改为副作用的语句。
• 它不适用于存储程序中的SELECT语句，并且被忽略。

命名查询块的优化器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 ...)) ...

其结果如下所示：

• MRR（@ qb1 t1）适用于查询块qb1中的表t1。

• BKA（@ qb2）适用于查询块qb2。

• NO_MRR（@ qb3 t1 idx1，id2）适用于查询块qb3中的表t1中的索引idx1和idx2。

查询块名称是标识符，并遵循关于哪些名称有效以及如何引用它们的通用规则（请参见第9.2节“模式对象名称”）。 例如，包含空格的查询块名称必须用引号引起来，这可以使用反引号来完成：

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

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

如果启用了ANSI_QUOTES SQL模式，则也可以在双引号内引用查询块名称：

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

8.9.4 索引 hint

索引提示为优化程序提供有关如何在查询处理期间选择索引的信息。 这里描述的索引提示与第8.9.2节“优化器提示”中描述的优化器提示不同。 索引和优化器提示可以单独使用或一起使用。

索引提示是根据表名指定的。 （有关在SELECT语句中指定表的一般语法，请参见第13.2.9.2节“JOIN语法”。）引用单个表（包括索引提示）的语法如下所示：

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

| 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.STATISTICS表。

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;

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

• 对于USE INDEX，这意味着“不使用索引”，它在语法上是有效的。忽略FORCE INDEX或IGNORE INDEX的index_list是语法错误。
• 您可以通过向提示添加一个FOR子句来指定索引提示的范围。 这提供了对查询处理的各个阶段的执行计划的优化器选择的更细粒度的控制。 要仅影响MySQL决定如何在表中查找行以及如何处理连接时使用的索引，请使用FOR JOIN。 要影响用于排序或分组行的索引使用情况，请使用FOR ORDER BY或FOR GROUP BY。
• 你可以指定多个索引hint：

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);

但是，将USE 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，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搜索，索引提示的工作如下：

• 对于自然语言模式搜索，索引提示将被忽略。 例如，IGNORE INDEX（i1）在没有警告的情况下被忽略，索引仍然被使用。
• 对于布尔模式搜索，使用FOR ORDER BY或FOR GROUP BY的索引提示将被忽略。 带有FOR JOIN或无FOR修饰符的索引提示将得到遵守。 与提示如何应用于non-FULLTEXT 搜索相比，该提示用于查询执行的所有阶段（查找行和检索，分组和排序）。 即使给出non-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 ... ;

8.9.5 优化器COST模型

为了生成执行计划，优化器使用基于对查询执行期间发生的各种操作的成本进行估计的成本模型。 优化器具有一组可编辑的默认“成本常量”，可用于制定有关执行计划的决策。

从MySQL 5.7.5开始， 化器还具有执行计划构建过程中使用的成本估算数据库。 这些估算值存储在mysql系统数据库的server_cost和engine_cost表中，并且可以随时配置。 这些表的目的是使它能够轻松调整优化器在尝试到达查询执行计划时使用的成本估计。

Cost 模型生成操作

可配置的优化器成本模型如下所示：

• 服务器在启动时将成本模型表读入内存，并在运行时使用内存中的值。 表中指定的任何非NULL成本估算优先于相应的已编译默认成本常数。 任何NULL评估都会向优化器指示使用编译的默认值。
• 在运行时，服务器可能重新读取成本表。 当动态加载存储引擎或执行FLUSH OPTIMIZER_COSTS语句时会发生这种情况。
• 成本表使服务器管理员能够通过更改表中的条目来轻松调整成本估算。 通过将条目的成本设置为NULL，还可以很容易地恢复到默认值。 优化器使用内存中的成本值，因此对表的更改应该在FLUSH OPTIMIZER_COSTS之后生效。
• 当客户端会话开始时，当前内存中的成本估算适用于整个会话，直到它结束。 特别是，如果服务器重新读取成本表格，则任何更改的估算值仅适用于随后开始的会话。 现有会话不受影响。
• 成本表特定于给定的服务器实例。 服务器不会将成本表更改复制到复制从服务器。

Cost 模型数据库

优化器成本模型数据库包含mysql系统数据库中的两个表，其中包含查询执行期间发生的操作的成本估算信息：

• server_cost：一般服务器操作的优化器成本估算
• engine_cost：针对特定存储引擎的操作的优化器成本估算

server_cost表包含这些列：

• cost_name

成本模型中使用的成本估算的名称。 名称不区分大小写。 如果服务器在读取此表时未识别成本名称，则会向错误日志写入警告。

• cost_vales

成本估算值。 如果该值不是NULL，则服务器将其用作成本。 否则，它使用默认估计值（编译值）。 DBA可以通过更新此列来更改成本估算。 如果服务器在读取此表时发现成本值无效（非正确），则会向错误日志写入警告。

要覆盖默认成本估算（对于指定NULL的条目），请将成本设置为非NULL值。 要恢复为默认值，请将该值设置为NULL。 然后执行FLUSH OPTIMIZER_COSTS来通知服务器重新读取成本表。

• last_update

最后一行更新的时间。

• comment

与成本估算相关的描述性评论。 DBA可以使用此列来提供有关成本估算行为何存储特定值的信息。

server_cost表的主键是cost_name列，因此无法为任何成本估算创建多个条目。

服务器识别server_cost表的这些cost_name值：

• disk_temptable_create_cost (default 40.0), disk_temptable_row_cost (default 1.0)

存储在基于磁盘的存储引擎（InnoDB或MyISAM）中内部创建的临时表的成本估算。 增加这些值会增加使用内部临时表的成本估计值，并使优化程序偏好使用较少的查询计划。

与相应的内存参数（memory_temptable_create_cost，memory_temptable_row_cost）的缺省值相比，这些磁盘参数的默认值较大反映了处理基于磁盘的表的较大成本。

• key_compare_cost (default 0.1)

比较记录密钥的成本。 增加此值会导致查询计划比较许多密钥变得更加昂贵。 例如，与避免使用索引进行排序的查询计划相比， filesort的查询计划变得相对昂贵。

• memory_temptable_create_cost (default 2.0), memory_temptable_row_cost (default 0.2)

MEMORY存储引擎中存储的内部创建的临时表的成本估算。 增加这些值会增加使用内部临时表的成本估计值，并使优化程序偏好使用较少的查询计划。

与相应磁盘参数的默认值（disk_temptable_create_cost，disk_temptable_row_cost）相比，这些内存参数的较小默认值反映了处理基于内存的表的较低成本。

• row_evaluate_cost (default 0.2)

评估记录条件的成本。 与查询更少行的查询计划相比，增加此值会导致查询计划检查许多行变得更加昂贵。 例如，与读取较少行的范围扫描相比，表扫描变得相对昂贵。

engine_cost表包含这些列：

• engine_name

此成本估算适用的存储引擎的名称。 名称不区分大小写。 如果该值是缺省值，则它适用于没有自己的命名条目的所有存储引擎。 如果服务器在读取此表时未识别引擎名称，则会向错误日志写入警告。

• device_type

此成本估算适用的设备类型。 该列旨在为不同的存储设备类型指定不同的成本估算，例如硬盘驱动器与固态驱动器。 目前，该信息未被使用，0是唯一允许的值。

• cost_name

与server_cost表中的相同。

• cost_value

与server_cost表中的相同。

• last_update

与server_cost表中的相同。

• comment

与server_cost表中的相同。

engine_cost表的主键是包含（cost_name，engine_name，device_type）列的元组，因此无法为这些列中的任何值组合创建多个条目。

服务器识别engine_cost表的这些cost_name值：

• io_block_read_cost (default 1.0)

从磁盘读取索引或数据块的成本。 与增加此值的查询计划相比，读取许多磁盘块的查询计划与读取更少磁盘块的查询计划相比变得更加昂贵。 例如，与读取较少块的范围扫描相比，表扫描变得相对昂贵。

• memory_block_read_cost (default 1.0)

与io_block_read_cost类似，但表示从内存数据库缓冲区中读取索引或数据块的开销。

如果io_block_read_cost和memory_block_read_cost值不同，则执行计划可能会在相同查询的两次运行之间发生变化。 假设内存访问的成本低于磁盘访问的成本。 在这种情况下，在数据读入缓冲池之前的服务器启动时，您可能会得到与查询运行后不同的计划，因为这样数据将存储在内存中。

对cost 模型数据库进行改变

对于希望从其默认值更改成本模型参数的DBA，请尝试将值加倍或减半并测量结果。

对io_block_read_cost和memory_block_read_cost参数的更改很可能会产生有价值的结果。 这些参数值使数据访问方法的成本模型能够考虑从不同来源读取信息的成本; 即从磁盘读取信息与读取存储器缓冲区中已有信息的成本。 例如，在所有其他条件相同的情况下，将io_block_read_cost设置为大于memory_block_read_cost的值会使优化程序更喜欢查询计划，该计划将已存储在内存中的信息读取到必须从磁盘读取的计划中。

此示例显示如何更改io_block_read_cost的默认值：

UPDATE mysql.engine_cost SET cost_value = 2.0 WHERE cost_name = 'io_block_read_cost';

FLUSH OPTIMIZER_COSTS;

本示例说明如何仅为InnoDB存储引擎更改io_block_read_cost的值：

INSERT INTO mysql.engine_cost VALUES ('InnoDB', 0, 'io_block_read_cost', 3.0, CURRENT_TIMESTAMP, 'Using a slower disk for InnoDB');

FLUSH OPTIMIZER_COSTS;