优化器提示
控制优化程序策略的一种方法是设置optimizer_switch
系统变量(请参见“可切换的优化”)。对该变量的更改会影响所有后续查询的执行;为了使一个查询与另一个查询有不同的影响,必须optimizer_switch
在每个查询之前进行更改。
控制优化器的另一种方法是使用优化器提示,该提示可以在各个语句中指定。由于优化器提示是基于每个语句应用的,因此与使用相比,它们可以更好地控制语句执行计划optimizer_switch
。例如,可以在语句中为一个表启用优化,而为另一表禁用优化。语句中的提示优先于optimizer_switch
标志。
例子:
SELECT /* + NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1FROM t3WHERE f1 > 30 AND f1 < 33;SELECT /* + BKA(t1) NO_BKA(t2) */ *FROM t1INNER JOIN t2WHERE ...;SELECT /* + NO_ICP(t1, t2) */ *FROM t1INNER JOIN t2WHERE ...;SELECT /* + SEMIJOIN(FIRSTMATCH, LOOSESCAN) */ *FROM t1 ...;EXPLAIN SELECT /* + NO_ICP(t1) */ *FROM t1WHERE ...;SELECT /* + MERGE(dt) */ *FROM (SELECT *FROM t1)AS dt;INSERT /* + SET_VAR(foreign_key_checks=OFF) */INTO t2VALUES (2);
本文所述的优化程序提示与“索引提示”中所述的索引提示不同。优化程序和索引提示可以单独使用,也可以一起使用。
- 优化程序提示概述
- 优化程序提示语法
- 联合订单优化器提示
- 表级优化器提示
- 索引级优化器提示
- 子查询优化器提示
- 语句执行时间优化器提示
- 变量设置提示语法
- 资源组提示语法
- 用于命名查询块的优化器提示
优化程序提示概述
优化器提示适用于不同的作用域级别:
- 全局:提示会影响整个语句
- 查询块:提示会影响语句中的特定查询块
- 表级别:提示会影响查询块中的特定表
- 索引级别:提示会影响表中的特定索引
下表总结了可用的优化器提示,它们影响的优化器策略以及它们适用的范围。稍后给出更多细节。
表8.2可用的优化程序提示
提示名称 | 描述 | 适用范围 |
---|---|---|
BKA ,NO_BKA | 影响批量密钥访问联接处理 | 查询块,表 |
BNL ,NO_BNL | 影响块嵌套循环连接处理 | 查询块,表 |
HASH_JOIN ,NO_HASH_JOIN | 影响哈希联接优化 | 查询块,表 |
INDEX_MERGE ,NO_INDEX_MERGE | 影响索引合并优化 | 表,索引 |
JOIN_FIXED_ORDER | 使用FROM 子句中指定的表顺序作为连接顺序 | 查询块 |
JOIN_ORDER | 使用提示中指定的表顺序作为连接顺序 | 查询块 |
JOIN_PREFIX | 将提示中指定的表顺序用于连接顺序的第一个表 | 查询块 |
JOIN_SUFFIX | 将提示中指定的表顺序用于联接顺序的最后一个表 | 查询块 |
MAX_EXECUTION_TIME | 限制语句执行时间 | Global |
MERGE ,NO_MERGE | 影响派生表/视图合并到外部查询块中 | 表 |
MRR ,NO_MRR | 影响多范围读取优化 | 表,索引 |
NO_ICP | 影响索引条件下推式优化 | 表,索引 |
NO_RANGE_OPTIMIZATION | 影响范围优化 | 表,索引 |
QB_NAME | 为查询块分配名称 | 查询块 |
RESOURCE_GROUP | 在语句执行期间设置资源组 | Global |
SEMIJOIN ,NO_SEMIJOIN | 影响半联接策略;从MySQL 8.0.17开始,这也适用于抗联接 | 查询块 |
SKIP_SCAN ,NO_SKIP_SCAN | 影响跳过扫描优化 | 表,索引 |
SET_VAR | 在语句执行期间设置变量 | Global |
SUBQUERY | 影响物化,IN 至-EXISTS 子查询配置的对策探讨 | 查询块 |
禁用优化会阻止优化器使用它。启用优化意味着,如果优化器适用于语句执行,则它可以自由使用该策略,而不是优化器必然会使用它。
优化程序提示语法
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 xIN (SELECT /* + ... */ ...)INSERT ...SELECT /* + ... */ ...在以开头的暗示性声明中
EXPLAIN
。例如:EXPLAIN SELECT /* + ... */ ...EXPLAIN UPDATE ...WHERE xIN (SELECT /* + ... */ ...)这意味着您可以
EXPLAIN
用来参见优化器提示如何影响执行计划。SHOW WARNINGS
之后EXPLAIN
,请立即使用以参见如何使用提示。EXPLAIN
以下SHOW WARNINGS
显示的扩展输出指示使用了哪些提示。不显示忽略的提示。
提示注释可以包含多个提示,但是查询块不能包含多个提示注释。这是有效的:
SELECT /* + BNL(t1) BKA(t2) */ ...
但这是无效的:
SELECT /* + BNL(t1) */ /* BKA(t2) */ ...
当提示注释包含多个提示时,存在重复和冲突的可能性。以下一般准则适用。对于特定的提示类型,可能会应用其他规则,如提示说明中所述。
- 重复提示:对于诸如的提示
/* + MRR(idx1)MRR(idx1)*/
,MySQL使用第一个提示并发出有关重复提示的警告。 - 冲突的提示:对于诸如的提示
/* + MRR(idx1)NO_MRR(idx1)*/
,MySQL使用第一个提示,并发出有关第二个冲突提示的警告。
查询块名称是标识符,并遵循有关哪些名称有效以及如何对其进行引用的常规规则(请参见“模式对象名称”)。
提示名称,查询块名称和策略名称不区分大小写。对表和索引名称的引用遵循通常的标识符区分大小写规则(请参见“标识符区分大小写”)。
联合订单优化器提示
联接顺序提示会影响优化器联接表的顺序。
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]] ...)
语法涉及以下术语:
hint_name
:允许这些提示名称:JOIN_FIXED_ORDER
:强制优化器使用表在FROM
子句中出现的顺序联接表。这与指定相同SELECT STRAIGHT_JOIN
。JOIN_ORDER
:指示优化器使用指定的表顺序联接表。提示适用于命名表。优化器可以将未命名的表放在连接顺序中的任何位置,包括指定表之间。JOIN_PREFIX
:指示优化器使用指定的表顺序为联接执行计划的第一个表联接表。提示适用于命名表。优化器将所有其他表放在命名表之后。JOIN_SUFFIX
:指示优化器使用指定的表顺序为联接执行计划的最后一个表联接表。提示适用于命名表。优化器将所有其他表放在命名表之前。
tbl_name
:语句中使用的表的名称。命名表的提示适用于它命名的所有表。该JOIN_FIXED_ORDER
提示名称没有表,并适用于所有的表FROM
在其发生的查询块的条款。如果表具有别名,则提示必须引用别名,而不是表名称。
提示中的表名不能用架构名称限定。
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 t1JOIN t2JOIN t3WHERE t1.f1IN (SELECT /* + QB_NAME(subq1) */ f1FROM t4) AND t2.f1IN (SELECT /* + QB_NAME(subq2) */ f1FROM t5);
提示控制合并到外部查询块的半联接表的行为。如果将子查询subq1
和subq2
转换为半联接,则表t4@subq1
和t5@subq2
将合并到外部查询块。在这种情况下,提示在外部查询块控制指定的行为t4@subq1
,t5@subq2
表。
优化器根据以下原则来解析连接顺序提示:
多个提示实例
每种类型仅应用一个
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) */
两种提示均适用。没有警告发生。
冲突的提示
在某些情况下,提示可能会发生冲突,例如何时
JOIN_ORDER
和JOIN_PREFIX
具有无法同时应用的表顺序:SELECT /* + JOIN_ORDER(t1, t2) JOIN_PREFIX(t2, t1) */ ...FROM t1, t2;在这种情况下,将应用第一个指定的提示,随后的冲突提示将被忽略,而不会发出警告。不会应用的有效提示将被静默忽略,而不会发出警告。
忽略提示
如果提示中指定的表具有循环依赖性,则忽略该提示。
例:
/* + JOIN_ORDER(t1, t2) JOIN_PREFIX(t2, t1) */
该
JOIN_ORDER
提示套表t2
依赖t1
。该JOIN_PREFIX
提示将被忽略,因为表t1
不能依赖t2
。忽略的提示不会显示在扩展EXPLAIN
输出中。与
const
表格的互动MySQL优化器将
const
表按连接顺序放在首位,并且const
表的位置不受提示的影响。const
尽管提示仍然适用,但忽略了对连接顺序提示中的表的引用。例如,这些是等效的:JOIN_ORDER(t1, const_tbl, t2) JOIN_ORDER(t1, t2)
扩展
EXPLAIN
输出中显示的可接受提示包括const
指定的表。与联接操作类型的交互
MySQL支持多种类型的连接:
LEFT
,RIGHT
,INNER
,CROSS
,STRAIGHT_JOIN
。与指定的联接类型冲突的提示将被忽略,而不会发出警告。例:
SELECT /* + JOIN_PREFIX(t1, t2) */FROM t2LEFT JOIN t1;在此,提示中的请求加入顺序与所需的顺序之间发生冲突
LEFT JOIN
。该提示将被忽略,而不会发出警告。
表级优化器提示
表级提示影响:
- 使用块嵌套循环(BNL)和批处理密钥访问(BKA)联接处理算法(请参见“块嵌套环和批处理密钥访问联接”)。
- 是否应将派生表,视图引用或公共表表达式合并到外部查询块中,或使用内部临时表实例化。
这些提示类型适用于特定表或查询块中的所有表。
表级提示的语法:
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。HASH_JOIN
,NO_HASH_JOIN
:对指定的表启用或禁用哈希联接(仅适用于MySQL 8.0.18;在MySQL 8.0.19或更高版本中无效)。MERGE
,NO_MERGE
:为指定的表,视图引用或公共表表达式启用合并;或禁用合并并改用实现。
注意
若要使用BNL或BKA提示为外部联接的任何内部表启用联接缓冲,必须为外部联接的所有内部表启用联接缓冲。
tbl_name
:语句中使用的表的名称。提示适用于它命名的所有表。如果提示未命名表,则该提示将应用于出现该查询的查询块的所有表。如果表具有别名,则提示必须引用别名,而不是表名称。
提示中的表名不能用架构名称限定。
query_block_name
:提示适用于的查询块。如果提示中不包含前导,则该提示适用于出现该查询的查询块。对于语法,提示适用于命名查询块中的命名表。要将名称分配给查询块,请参阅命名查询块的优化器提示。@query_block_name
tbl_name@query_block_name
例子:
SELECT /* + NO_BKA(t1, t2) */ t1.*FROM t1INNER JOIN t2INNER JOIN t3;SELECT /* + NO_BNL() BKA(t1) */ t1.*FROM t1INNER JOIN t2INNER JOIN t3;SELECT /* + NO_MERGE(dt) */ *FROM (SELECT *FROM t1)AS dt;
表级提示适用于从先前的表而非发送方表接收记录的表。考虑以下语句:
SELECT /* + BNL(t2) */FROM t1, t2;
如果优化器选择先处理t1
,它将在开始读取之前t2
对行进行缓冲,从而对块嵌套循环联接应用。如果优化程序选择先处理,则提示无效,因为它是发送方表。t1
t2
t2
t2
对于MERGE
和NO_MERGE
提示,这些优先级规则适用:
- 提示优先于不是技术约束的任何优化器启发式方法。(如果提供提示作为建议没有效果,则优化器有理由忽略它。)
- 提示优先于系统变量的
derived_merge
标志optimizer_switch
。 - 对于视图引用,
ALGORITHM={MERGE|TEMPTABLE}
视图定义中的子句优先于查询中引用该视图的查询中指定的提示。
索引级优化器提示
索引级别的提示会影响优化器针对特定表或索引使用的索引处理策略。这些提示类型会影响索引条件下推(ICP),多范围读取(MRR),索引合并和范围优化的使用(请参见“优化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
:允许这些提示名称:GROUP_INDEX
,NO_GROUP_INDEX
:启用或禁用指定的索引以进行GROUP BY
操作的索引扫描。相当于索引提示FORCE INDEX FOR GROUP BY
,IGNORE INDEX FOR GROUP BY
。在MySQL 8.0.20及更高版本中可用。INDEX
,NO_INDEX
:作为的组合JOIN_INDEX
,GROUP_INDEX
以及ORDER_INDEX
,迫使服务器以用于任何指定索引或索引和所有范围,或为一体的组合NO_JOIN_INDEX
,NO_GROUP_INDEX
和NO_ORDER_INDEX
,这使得服务器忽略任何指定索引或索引和所有范围。相当于FORCE INDEX
,IGNORE INDEX
。从MySQL 8.0.20开始可用。INDEX_MERGE
,NO_INDEX_MERGE
:启用或禁用指定表或索引的索引合并访问方法。有关此访问方法的信息,请参见“优化索引”。这些提示适用于所有三种索引合并算法。该
INDEX_MERGE
提示会强制优化器使用指定索引集对指定表使用索引合并。如果未指定索引,则优化器将考虑所有可能的索引组合并选择最便宜的索引组合。如果索引组合不适用于给定的语句,则可以忽略该提示。该
NO_INDEX_MERGE
提示将禁用涉及任何指定索引的索引合并组合。如果提示未指定索引,则表不允许索引合并。JOIN_INDEX
,NO_JOIN_INDEX
:强制MySQL使用或忽略指定的索引或索引为任何存取方法,诸如ref
,range
,index_merge
,等。相当于FORCE INDEX FOR JOIN
,IGNORE INDEX FOR JOIN
。在MySQL 8.0.20及更高版本中可用。MRR
,NO_MRR
:启用或禁用指定表或索引的MRR。MRR提示仅适用于InnoDB
和MyISAM
表。有关此访问方法的信息,请参见“多范围读取优化”。NO_ICP
:对指定的表或索引禁用ICP。默认情况下,ICP是一种候选优化策略,因此没有启用它的提示。有关此访问方法的信息,请参见“索引条件下推优化”。NO_RANGE_OPTIMIZATION
:禁用指定表或索引的索引范围访问。此提示还禁用了表或索引的索引合并和松散索引扫描。默认情况下,范围访问是一种候选优化策略,因此没有启用它的提示。当范围数可能很高并且范围优化将需要许多资源时,此提示可能很有用。
ORDER_INDEX
,NO_ORDER_INDEX
:使MySQL使用或忽略指定的一个或多个用于对行进行排序的索引。相当于FORCE INDEX FOR ORDER BY
,IGNORE INDEX FOR ORDER BY
。从MySQL 8.0.20开始可用。SKIP_SCAN
,NO_SKIP_SCAN
:为指定的表或索引启用或禁用“跳过扫描”访问方法。有关此访问方法的信息,请参见跳过扫描范围访问方法。这些提示自MySQL 8.0.13起可用。该
SKIP_SCAN
提示会强制优化器使用指定索引集对指定表使用“跳过扫描”。如果未指定索引,则优化器将考虑所有可能的索引并选择最便宜的索引。如果索引不适用于给定的语句,则可以忽略该提示。该
NO_SKIP_SCAN
提示禁用指定索引的跳过扫描。如果提示未指定索引,则不允许对该表进行跳过扫描。
tbl_name
:提示适用的表格。index_name
:命名表中索引的名称。提示适用于它命名的所有索引。如果提示未命名索引,则它将应用于表中的所有索引。要引用主键,请使用名称
PRIMARY
。要参见表的索引名称,请使用SHOW INDEX
。query_block_name
:提示适用于的查询块。如果提示中不包含前导,则该提示适用于出现该查询的查询块。对于语法,提示适用于命名查询块中的命名表。要将名称分配给查询块,请参阅命名查询块的优化器提示。@query_block_name
tbl_name@query_block_name
例子:
SELECT /* + INDEX_MERGE(t1 f3, PRIMARY) */ f2FROM t1WHERE f1 = 'o' AND f2 = f3 AND f3 <= 4;SELECT /* + MRR(t1) */ *FROM t1WHERE f2 <= 3 AND 3 <= f3;SELECT /* + NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1FROM t3WHERE f1 > 30 AND f1 < 33;INSERT INTO t3(f1, f2, f3) (SELECT /* + NO_ICP(t2) */ t2.f1, t2.f2, t2.f3FROM t1,t2WHERE 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, f2FROM t1WHERE f2 > 40;
以下示例使用索引合并提示,但是其他索引级别的提示遵循与忽略提示和优化程序提示相对于optimizer_switch
系统变量或索引提示的优先级相同的原理。
假设表t1
中的列,b
,c
,和d
;和索引命名i_
,i_b
以及i_c
存在于,b
和c
分别为:
SELECT /* + INDEX_MERGE(t1 i_a, i_b, i_c)*/ *FROM t1WHERE 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 t1WHERE 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
优化程序提示,这些优先级规则适用:
如果指定了优化器提示并且该提示适用,那么它优先于
optimizer_switch
系统变量的与索引合并相关的标志。SET optimizer_switch='index_merge_intersection=off';SELECT /* + INDEX_MERGE(t1 i_b, i_c) */ *FROM t1WHERE 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 t1WHERE 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 t1WHERE b = 1 AND c = 2 AND d = 3;该提示仅指定一个索引,因此不适用,并且
optimizer_switch
标志(off
)适用。不使用索引合并。索引级优化标记
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
可以使用NO_ORDER_INDEX
优化器提示来覆盖表上的所有索引,除了所需的索引外,如下所示:SELECT /* + NO_ORDER_INDEX(t1 i_b,i_c) */ ,cFROM t1ORDER BY ;尝试将
NO_ORDER_INDEX
表作为一个整体进行合并USE INDEX FOR ORDER BY
无法执行此操作,因为NO_ORDER_BY
会导致USE INDEX
忽略,如下所示:mysql>
EXPLAIN SELECT /* + NO_ORDER_INDEX(t1) */ ,cFROM t1 ->USE INDEX FOR ORDER BY (i_ )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 Extr : Using filesortUSE INDEX
,FORCE INDEX
和IGNORE INDEX
索引提示具有比更高的优先级INDEX_MERGE
和NO_INDEX_MERGE
优化提示。/* + INDEX_MERGE(t1 i_a, i_b, i_c) */ ...
IGNORE INDEX i_IGNORE INDEX
优先于INDEX_MERGE
,因此索引i_
从索引合并的可能范围中排除。/* + NO_INDEX_MERGE(t1 i_a, i_b) */ ...
FORCE INDEX i_ , i_bi_a, i_b
由于不允许索引合并FORCE INDEX
,但是优化器被迫使用i_
或i_b
用于range
或ref
访问。没有冲突。两种提示均适用。- 如果
IGNORE INDEX
提示命名多个索引,则这些索引不可用于索引合并。 在
FORCE INDEX
与USE INDEX
只有指定索引可用于索引合并提示做。SELECT /* + INDEX_MERGE(t1 i_a, i_b, i_c) */ aFROM t1FORCE INDEX (i_ , i_b)WHERE c = 'h' AND a = 2 AND b = 'b';索引合并交集访问算法用于
(i_a, i_b)
。如果FORCE INDEX
更改为,也是如此USE INDEX
。
子查询优化器提示
子查询提示会影响是否使用半联接转换以及允许使用哪些半联接策略,以及在不使用半联接时,是否使用子查询实现或IN
-to-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 t2WHERE t2.IN (SELECT /* + QB_NAME(subq1) */ aFROM t3);SELECT /* + SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ *FROM t2WHERE t2.IN (SELECT /* + QB_NAME(subq1) */ aFROM t3);
影响是否使用子查询实现或IN
-to-EXISTS
转换的提示的语法:
SUBQUERY([@query_block_name] strategy)
提示名称始终为SUBQUERY
。
对于SUBQUERY
提示,可以使用以下strategy
值:INTOEXISTS
,MATERIALIZATION
。
例子:
SELECT id, aIN (SELECT /* + SUBQUERY(MATERIALIZATION) */ aFROM t1)FROM t2;SELECT *FROM t2WHERE t2.IN (SELECT /* + SUBQUERY(INTOEXISTS) */ aFROM t1);
对于半联接和SUBQUERY
提示,前导指定提示适用于的查询块。如果提示中不包含前导,则该提示适用于出现该查询的查询块。要将名称分配给查询块,请参阅命名查询块的优化器提示。@query_block_name
@query_block_name
如果提示注释包含多个子查询提示,则使用第一个。如果还有其他以下类型的提示,则会产生警告。其他类型的以下提示将被忽略。
语句执行时间优化器提示
该MAX_EXECUTION_TIME
提示仅适用于SELECT
语句。它N
为服务器终止该语句之前允许执行该语句的时间设置了一个限制(超时值(以毫秒为单位)):
MAX_EXECUTION_TIME(N)
超时为1秒(1000毫秒)的示例:
SELECT /* + MAX_EXECUTION_TIME(1000) */ *FROM t1INNER JOIN t2WHERE ...
该提示设置的语句执行超时毫秒。如果此选项不存在或为0,则应用系统变量建立的语句超时。MAX_EXECUTION_TIME(N)
N
N
max_execution_time
该MAX_EXECUTION_TIME
提示适用于以下情况:
- 对于具有多个
SELECT
关键字的语句,例如并集或带有子查询的语句,MAX_EXECUTION_TIME
将应用于整个语句,并且必须出现在第一个之后SELECT
。 - 它适用于只读
SELECT
语句。非只读的语句是那些调用存储函数修改数据的副作用的语句。 - 它不适用于
SELECT
存储程序中的语句,将被忽略。
变量设置提示语法
所述SET_VAR
提示暂时设定的系统变量的会话值(用于单个语句的持续时间)。例子:
SELECT /* + SET_VAR(sort_buffer_size = 16M) */name FROM peopleORDER BY name ;INSERT /* + SET_VAR(foreign_key_checks=OFF) */INTO t2VALUES (2);SELECT /* + SET_VAR(optimizer_switch = 'mrr_cost_based=off') */ 1;
SET_VAR
提示的语法:
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_flag
采用on
或的值off
,因此尝试将其设置yes
为不正确。在每种情况下,该提示都会被警告忽略。
SET_VAR
仅在语句级别允许使用提示。如果在子查询中使用,则提示将被警告忽略。
从服务器忽略SET_VAR
复制语句中的提示,以避免潜在的安全问题。
资源组提示语法
该RESOURCE_GROUP
优化程序提示用于资源组管理(见第8.12.5,“资源组”)。此提示将执行语句的线程临时分配给命名资源组(在语句持续时间内)。它需要RESOURCE_GROUP_ADMIN
或RESOURCE_GROUP_USER
特权。
例子:
SELECT /* + RESOURCE_GROUP(USR_default) */name FROM peopleORDER BY name ;INSERT /* + RESOURCE_GROUP(Batch) */INTO t2VALUES (2);
RESOURCE_GROUP
提示的语法:
RESOURCE_GROUP(group_name)
group_name
指示在语句执行期间应将线程分配给的资源组。如果该组不存在,则会发生警告并忽略提示。
该RESOURCE_GROUP
提示必须出现在最初的声明关键字后(SELECT
,INSERT
,REPLACE
,UPDATE
,或DELETE
)。
语句的替代方法RESOURCE_GROUP
是该SET RESOURCE GROUP
语句,该语句将线程临时分配给资源组。请参见“ SET RESOURCE GROUP语句”。
用于命名查询块的优化器提示
表级,索引级和子查询优化器提示允许将特定查询块命名为其参数语法的一部分。要创建这些名称,请使用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)
适用t1
于查询块中的表qb1
。BKA(@qb2)
适用于查询块qb2
。NO_MRR(@qb3 t1 idx1, id2)
适用于索引idx1
和查询块idx2
中的表。t1
qb3
查询块名称是标识符,并遵循有关哪些名称有效以及如何对其进行引用的常规规则(请参见“模式对象名称”)。例如,必须引用包含空格的查询块名称,这可以使用反引号来完成:
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") */ ...) ...