MySQL 5.7版本说明
控制优化器策略的一种方法是设置optimizer_switch系统变量(见第8.9.2节“可切换优化”)．对该变量的更改会影响所有后续查询的执行;为了使一个查询与另一个查询产生不同的影响，需要进行更改optimizer_switch在每一个。
控制优化器的另一种方法是使用优化器提示，这可以在单个语句中指定。因为优化器提示是针对每个语句应用的，所以它们对语句执行计划提供了比使用优化器更好的控制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 /*+ semi - join (FIRSTMATCH, LOOSESCAN) */ * FROM t1…;解释SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE…
请注意
的mysql客户端在默认情况下从发送到服务器的SQL语句中删除注释(包括优化器提示)，直到MySQL 5.7.7才更改为向服务器传递优化器提示。的较旧版本，以确保优化器提示没有被剥离mysql客户端使用一个理解优化器提示的服务器版本，调用mysql与——评论选择。
本文描述的优化器提示与中描述的索引提示不同第8.9.4节“索引提示”．优化器和索引提示可以单独使用，也可以一起使用。
优化器提示应用于不同的作用域级别:
全局:提示影响整个语句
查询块:该提示影响语句中的特定查询块
表级:该提示影响查询块中的特定表
索引级:该提示影响表中的特定索引
下表总结了可用的优化器提示、它们影响的优化器策略以及它们应用的范围。稍后将给出更多细节。
表8.2可用的优化器提示
| 提示名称 | 描述 | 适用的范围 |
|---|---|---|
BKA，NO_BKA |
影响批处理键访问连接处理 | 查询块,表 |
BNL，NO_BNL |
影响块嵌套循环连接处理 | 查询块,表 |
MAX_EXECUTION_TIME |
限制语句执行时间 | 全球 |
MRR，NO_MRR |
影响多范围读优化 | 表,索引 |
NO_ICP |
影响索引条件下推优化 | 表,索引 |
NO_RANGE_OPTIMIZATION |
影响范围的优化 | 表,索引 |
QB_NAME |
为查询块分配名称 | 查询块 |
SEMIJOIN，NO_SEMIJOIN |
semijoin策略 | 查询块 |
子查询 |
影响实体化,在- - - - - - - - -存在子查询策划 |
查询块 |
禁用优化将阻止优化器使用它。启用优化意味着优化器可以自由地使用策略(如果它应用于语句执行)，而不是优化器必须使用它。
MySQL支持SQL语句中的注释9.6节,“评论”．中必须指定优化器提示/ * +……* /评论。即，优化器提示使用的变体/ *……* /c风格的注释语法，使用+字符后／*评论开放序列。例子:
/ * + BKA (t1) * / / * + BNL (t1, t2) * / / * + NO_RANGE_OPTIMIZATION (t4主)* / / * + QB_NAME (qb2) * /的后面允许空格+的性格。
解析器识别初始关键字后的优化器提示注释选择，更新，插入，取代,删除语句。在以下情况下可以使用提示:
在查询和数据更改语句的开头:
选择/ * +…* /…插入/ * +……* /…替换/ * +……* /…更新/ * +……* /…删除/ * +……* /…在查询块的开始:
(选择/ * +…* /…)(选择…)Union (select /*+…)* /…)(选择/ * +…* /…)Union (select /*+…)* /…)更新…… WHERE x IN (SELECT /*+ ... */ ...) INSERT ... SELECT /*+ ... */ ...在以…开头的暗示的陈述中
解释．例如:解释select /*+…* /…解释更新……WHERE x IN (SELECT /*+…)* /…)意思是你可以用
解释查看优化器提示如何影响执行计划。使用显示警告后立即解释了解如何使用提示。扩展的解释下面显示的输出显示警告指示使用了哪些提示。忽略的提示不显示。
一个提示注释可以包含多个提示，但一个查询块不能包含多个提示注释。这是有效的:
SELECT /*+ BNL(t1) BKA(t2) */…但这是无效的:
SELECT /*+ BNL(t1) */ /* BKA(t2) */…当一个提示注释包含多个提示时，存在重复和冲突的可能性。以下是通用的指导方针。对于特定的提示类型，可以应用附加规则，如提示说明中所示。
重复提示:用于提示，例如
/*+ MRR(idx1) MRR(idx1) */， MySQL使用第一个提示，并发出关于重复提示的警告。矛盾的暗示:为了暗示，例如
/*+ MRR(idx1) NO_MRR(idx1) */， MySQL使用第一个提示，并就第二个提示冲突发出警告。
查询块名称是标识符，并遵循关于哪些名称有效以及如何引用它们的通常规则(参见第9.2节“模式对象名称”)．
提示名称、查询块名称和策略名称不区分大小写。对表名和索引名的引用遵循通常的标识符区分大小写规则(参见第9.2.3节“标识符区分大小写”)．
表级提示影响块嵌套循环(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:这些提示名称是允许的:请注意要使用BNL或BKA提示为外部连接的任何内部表启用连接缓冲，必须为外部连接的所有内部表启用连接缓冲。
tbl_name:语句中使用的表的名称。该提示应用于它命名的所有表。如果提示没有命名任何表，那么它将应用于发生该提示的查询块中的所有表。如果表有别名，提示必须引用别名，而不是表名。
提示中的表名不能用模式名限定。
query_block_name:提示的查询块。如果提示中没有前导@，该提示应用于它出现的查询块。为query_block_nametbl_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在开始阅读之前t2．如果优化器选择处理t2首先，暗示没有效果，因为t2是发送方表。
索引级提示会影响优化器针对特定表或索引使用的索引处理策略。这些提示类型影响索引条件下压(ICP)、多范围读取(MRR)和范围优化的使用(参见第8.2.1节“优化SELECT语句”)．
索引级提示的语法:
hint_name([@query_block_name］tbl_name(index_name[，index_name]…])hint_name（tbl_name@query_block_name(index_name[，index_name]…])语法指的是这些术语:
hint_name:这些提示名称是允许的:NO_ICP:关闭指定表或索引的ICP。默认情况下，ICP是一个候选优化策略，因此没有启用它的提示。NO_RANGE_OPTIMIZATION:对指定的表或索引禁用索引范围访问。此提示还禁用表或索引的索引合并和松散索引扫描。默认情况下，范围访问是一个候选优化策略，因此没有启用它的提示。当范围的数量可能很高，并且范围优化需要很多资源时，这个提示可能很有用。
tbl_name:应用提示的表。index_name:命名表中索引的名称。该提示应用于它命名的所有索引。如果提示没有命名索引，则应用于表中的所有索引。要引用主键，请使用名称
主要的．要查看表的索引名，请使用显示指数．query_block_name:提示的查询块。如果提示中没有前导@，该提示应用于它出现的查询块。为query_block_nametbl_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和t2。f2 t1之间。f1和t1。f2和t2。F2 + 1 >= t1。f1 + 1);子查询提示会影响是否使用半连接转换和允许哪些半连接策略，以及在不使用半连接时，是否使用子查询物化或在- - - - - - - - -存在转换。有关这些优化的更多信息，请参见第8.2.2节“优化子查询、派生表和视图引用”．
影响半连接策略的提示语法:
hint_name([@query_block_name] [策略[，策略]…])语法指的是这些术语:
hint_name:这些提示名称是允许的:SEMIJOIN，NO_SEMIJOIN:启用或禁用命名半连接策略。
策略:需要启用或禁用的半连接策略。这些策略名称是允许的:DUPSWEEDOUT，FIRSTMATCH，LOOSESCAN，物质化．为
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);影响是否使用子查询物化或的提示语法在- - - - - - - - -存在转换:
子查询([@query_block_name］策略）提示名称总是子查询．
为子查询提示,这些策略值是允许的:INTOEXISTS，物质化．
例子:
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和子查询提示,@指定提示应用到的查询块。如果提示中没有前导query_block_name@，该提示应用于它出现的查询块。要为查询块分配名称，请参见为查询块命名的优化提示．query_block_name
如果提示注释包含多个子查询提示，则使用第一个子查询提示。如果有其他类似的提示，则会产生警告。其他类型的提示会被忽略。
的MAX_EXECUTION_TIME只允许提示选择语句。它设定了一个限制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提示适用如下:
对于具有多个语句的语句
选择关键字，例如联合或带有子查询的语句，MAX_EXECUTION_TIME应用于整个语句，并且必须出现在第一个语句之后选择．它适用于只读
选择语句。非只读语句是调用作为副作用修改数据的存储函数的语句。它不适用于
选择语句，并被忽略。
表级、索引级和子查询优化器提示允许将特定查询块命名为其参数语法的一部分。要创建这些名称，请使用QB_NAME提示，为查询块分配一个名称:
QB_NAME (的名字）QB_NAME提示可以用来明确地显示查询块所应用的其他提示。它们还允许在单个提示注释中指定所有非查询块名称提示，以便更容易理解复杂语句。考虑下面的陈述:
选择……从(选择…从(选择…从 ...)) ...QB_NAME提示为语句中的查询块分配名称:
SELECT /*+ QB_NAME(qb1) */…FROM (SELECT /*+ QB_NAME(qb2) */…FROM (SELECT /*+ QB_NAME(qb3) */…从 ...)) ...然后其他提示可以使用这些名称来引用适当的查询块:
SELECT /*+ QB_NAME(qb1) MRR(@qb1 t1) BKA(@qb2) NO_MRR(@qb3t1 idx1, id2) */…FROM (SELECT /*+ QB_NAME(qb2) */…FROM (SELECT /*+ QB_NAME(qb3) */…从 ...)) ...其结果如下:
MRR (@qb1 t1)适用于表t1在查询块qb1．BKA (@qb2)适用于查询块qb2．NO_MRR(@qb3 t1 idx1, id2)适用于索引idx1而且idx2在表t1在查询块qb3．
查询块名称是标识符，并遵循关于哪些名称有效以及如何引用它们的通常规则(参见第9.2节“模式对象名称”)．例如，包含空格的查询块名称必须加引号，这可以使用反勾:
SELECT /*+ BKA(@ '我的提示名')*/…从(选择/ * + QB_NAME(“我暗示的名字 `) */ ...) ...如果ANSI_QUOTES启用SQL模式，也可以在双引号内引用查询块名称:
SELECT /*+ BKA(@"我的提示名")*/…从(选择/ * + QB_NAME(“我暗示的名字 ") */ ...) ...