控制优化器策略的一种方法是设置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…SELECT /*+ MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt;INSERT /*+ SET_VAR(foreign_key_checks=OFF) */ INTO t2 VALUES(2);
本文描述的优化器提示与中描述的索引提示不同第8.9.4节“索引提示”.优化器和索引提示可以单独使用,也可以一起使用。
优化器提示应用于不同的作用域级别:
全局:提示影响整个语句
查询块:该提示影响语句中的特定查询块
表级:该提示影响查询块中的特定表
索引级:该提示影响表中的特定索引
下表总结了可用的优化器提示、它们影响的优化器策略以及它们应用的范围。稍后将给出更多细节。
表8.2可用的优化器提示
提示名称 | 描述 | 适用的范围 |
---|---|---|
BKA ,NO_BKA |
影响批处理键访问连接处理 | 查询块,表 |
BNL ,NO_BNL |
在MySQL 8.0.20之前:影响块嵌套循环连接处理;MySQL 8.0.18及以上版本:也会影响哈希连接优化;MySQL 8.0.20及以上版本:只影响哈希连接优化 | 查询块,表 |
DERIVED_CONDITION_PUSHDOWN ,NO_DERIVED_CONDITION_PUSHDOWN |
使用或忽略物化派生表的派生条件下推优化(在MySQL 8.0.22中添加) | 查询块,表 |
GROUP_INDEX ,NO_GROUP_INDEX |
中索引扫描使用或忽略指定的索引集团 操作(MySQL 8.0.20新增) |
指数 |
HASH_JOIN ,NO_HASH_JOIN |
影响哈希连接优化(MySQL仅8.0.18) | 查询块,表 |
指数 ,NO_INDEX |
作为…的组合JOIN_INDEX ,GROUP_INDEX ,ORDER_INDEX 的组合NO_JOIN_INDEX ,NO_GROUP_INDEX ,NO_ORDER_INDEX (MySQL 8.0.20新增) |
指数 |
INDEX_MERGE ,NO_INDEX_MERGE |
影响索引合并优化 | 表,索引 |
JOIN_FIXED_ORDER |
中指定的表顺序从 联营令条款 |
查询块 |
JOIN_INDEX ,NO_JOIN_INDEX |
使用或忽略任何访问方法的指定索引(在MySQL 8.0.20中添加) | 指数 |
JOIN_ORDER |
使用hint中指定的表顺序作为连接顺序 | 查询块 |
JOIN_PREFIX |
对于联接顺序的第一个表,使用hint中指定的表顺序 | 查询块 |
JOIN_SUFFIX |
对于联接顺序的最后一个表,使用hint中指定的表顺序 | 查询块 |
MAX_EXECUTION_TIME |
限制语句执行时间 | 全球 |
合并 ,NO_MERGE |
影响将派生表/视图合并到外部查询块 | 表格 |
MRR ,NO_MRR |
影响多范围读优化 | 表,索引 |
NO_ICP |
影响索引条件下推优化 | 表,索引 |
NO_RANGE_OPTIMIZATION |
影响范围的优化 | 表,索引 |
ORDER_INDEX ,NO_ORDER_INDEX |
使用或忽略指定的索引来排序行(在MySQL 8.0.20中添加) | 指数 |
QB_NAME |
为查询块分配名称 | 查询块 |
RESOURCE_GROUP |
在执行语句时设置资源组 | 全球 |
SEMIJOIN ,NO_SEMIJOIN |
影响semijoin策略;从MySQL 8.0.17开始,这也适用于反连接 | 查询块 |
SKIP_SCAN ,NO_SKIP_SCAN |
影响跳过扫描优化 | 表,索引 |
SET_VAR |
在语句执行时设置变量 | 全球 |
子查询 |
影响实体化,在 - - - - - - - - -存在 子查询策划 |
查询块 |
禁用优化将阻止优化器使用它。启用优化意味着优化器可以自由地使用策略(如果它应用于语句执行),而不是优化器必须使用它。
MySQL支持SQL语句中的注释9.7节,“评论”.中必须指定优化器提示/ * +……* /
评论。即,优化器提示使用的变体/ *……* /
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节“标识符区分大小写”).
连接顺序提示会影响优化器连接表的顺序。
语法的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
:强制优化器使用表在从
条款。这与指定是一样的选择STRAIGHT_JOIN
.JOIN_ORDER
:指示优化器使用指定的表顺序连接表。该提示适用于指定的表。优化器可以将未命名的表放在连接顺序的任何位置,包括在指定的表之间。JOIN_PREFIX
:指示优化器使用联接执行计划的第一个表的指定表顺序联接表。该提示适用于指定的表。优化器将所有其他表放在命名表之后。JOIN_SUFFIX
:指示优化器对联接执行计划的最后几个表使用指定的表顺序联接表。该提示适用于指定的表。优化器将所有其他表放在命名表之前。
tbl_name
:语句中使用的表的名称。一个命名表的提示应用于它所命名的所有表。的JOIN_FIXED_ORDER
中的所有表都不命名从
子句的表达式。如果表有别名,提示必须引用别名,而不是表名。
提示中的表名不能用模式名限定。
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
表。
优化器根据以下原则解决连接顺序提示:
多个提示实例
只有一个
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) */…从t1, t2;
在这种情况下,应用第一个指定的提示,并忽略后续冲突的提示,不发出警告。一个不可能应用的有效提示被无声地忽略,没有任何警告。
忽略提示
如果提示中指定的表具有循环依赖项,则忽略该提示。
例子:
/*+ JOIN_ORDER(t1, t2) JOIN_PREFIX(t2, t1) */
的
JOIN_ORDER
提示设置表t2
依赖t1
.的JOIN_PREFIX
由于表原因,提示被忽略t1
不能依赖t2
.被忽略的提示不会在扩展中显示解释
输出。互动
常量
表MySQL优化器放置
常量
表的第一个连接顺序,和a的位置常量
表不受提示影响。引用常量
连接顺序提示中的表将被忽略,尽管提示仍然适用。例如,它们是等价的:JOIN_ORDER (t1,const_tbl, t2) JOIN_ORDER(t1, t2)
已接受的提示显示在扩展
解释
输出包括常量
表。与连接操作类型的交互
MySQL支持几种类型的连接:
左
,正确的
,内心的
,交叉
,STRAIGHT_JOIN
.与指定连接类型冲突的提示将被忽略,且不发出警告。例子:
SELECT /*+ JOIN_PREFIX(t1, t2) */FROM t2 LEFT JOIN t1;
方法所需的顺序与提示中请求的连接顺序之间会发生冲突
左连接
.这种暗示没有任何警告就被忽略了。
表级提示影响:
使用块嵌套循环(BNL)和批处理密钥访问(BKA)连接处理算法(参见第8.2.1.12节“块嵌套循环和批处理键访问连接”).
无论是派生表、视图引用还是公共表表达式,都应该合并到外部查询块中,或者使用内部临时表具体化。
使用派生表条件下推优化(在MySQL 8.0.22中添加)。看到第8.2.2.5节“推导条件下推优化”.
这些提示类型应用于查询块中的特定表或所有表。
表级提示的语法:
hint_name([@query_block_name] [tbl_name[,tbl_name]…])hint_name([tbl_name@query_block_name[,tbl_name@query_block_name]…])
语法指的是这些术语:
hint_name
:这些提示名称是允许的:BNL
,NO_BNL
:启用或禁用指定表的块嵌套循环。在MySQL 8.0.18和更高版本中,这些提示还启用和禁用哈希连接优化。请注意在MySQL 8.0.20和后续版本中删除了块嵌套循环优化,但是在启用和禁用散列连接时仍然支持这些提示。
DERIVED_CONDITION_PUSHDOWN
,NO_DERIVED_CONDITION_PUSHDOWN
:启用或禁用对指定表使用派生表条件下推(在MySQL 8.0.22中添加)。有关更多信息,请参见第8.2.2.5节“推导条件下推优化”.HASH_JOIN
,NO_HASH_JOIN
对指定的表启用或禁用哈希连接(MySQL 8.0.18;在MySQL 8.0.19或更高版本中无效)。合并
,NO_MERGE
:对指定的表、视图引用或公共表表达式启用合并;或者禁用合并并使用物化代替。
请注意要使用块嵌套循环或批处理键访问提示为外部连接的任何内部表启用连接缓冲,必须为外部连接的所有内部表启用连接缓冲。
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 /*+ NO_MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt;
表级提示应用于从前一个表接收记录的表,而不是发送者表。考虑一下这句话:
SELECT /*+ BNL(t2) */ FROM t1, t2;
如果优化器选择处理t1
首先,它将块嵌套循环连接应用到t2
通过缓冲行t1
在开始阅读之前t2
.如果优化器选择处理t2
首先,暗示没有效果,因为t2
是发送方表。
提示优先于不是技术约束的任何优化器启发式。(如果将提示作为建议提供没有效果,优化器就有理由忽略它。)
提示优先于
derived_merge
国旗的optimizer_switch
系统变量。对于视图引用,一个
合并算法={|可诱惑的}
子句优先于引用视图的查询中指定的提示。
索引级提示会影响优化器针对特定表或索引使用的索引处理策略。这些提示类型影响索引条件下压(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
:这些提示名称是允许的:GROUP_INDEX
,NO_GROUP_INDEX
:启用或禁用用于索引扫描的指定索引集团
操作。相当于索引提示力指数组由
,忽略组by的索引
.在MySQL 8.0.20及更高版本中可用。指数
,NO_INDEX
:作为…的组合JOIN_INDEX
,GROUP_INDEX
,ORDER_INDEX
的组合,强制服务器对任意和所有作用域使用指定的索引或多个索引NO_JOIN_INDEX
,NO_GROUP_INDEX
,NO_ORDER_INDEX
,这会导致服务器忽略任何和所有作用域的指定索引或多个索引。相当于力指数
,忽略指数
.从MySQL 8.0.20开始可用。INDEX_MERGE
,NO_INDEX_MERGE
:对指定的表或索引启用或禁用索引合并访问方法。有关此访问方法的信息,请参见第8.2.1.3节“索引合并优化”.这些提示适用于所有三种索引合并算法。的
INDEX_MERGE
hint强制优化器使用指定的索引集对指定的表使用索引合并。如果没有指定索引,优化器将考虑所有可能的索引组合,并选择代价最小的一个。如果索引组合不适用于给定语句,则可以忽略该提示。的
NO_INDEX_MERGE
hint禁用涉及任何指定索引的索引合并组合。如果提示没有指定索引,则该表不允许索引合并。JOIN_INDEX
,NO_JOIN_INDEX
:强制MySQL使用或忽略任何访问方法的指定索引,例如裁判
,范围
,index_merge
,等等。相当于连接的力索引
,忽略连接索引
.在MySQL 8.0.20及更高版本中可用。MRR
,NO_MRR
:对指定表或索引启用或禁用MRR。MRR提示仅适用于InnoDB
而且MyISAM
表。有关此访问方法的信息,请参见第8.2.1.11节“多范围读优化”.NO_ICP
:关闭指定表或索引的ICP。默认情况下,ICP是一个候选优化策略,因此没有启用它的提示。有关此访问方法的信息,请参见第8.2.1.6节“索引条件下推优化”.NO_RANGE_OPTIMIZATION
:对指定的表或索引禁用索引范围访问。此提示还禁用表或索引的索引合并和松散索引扫描。默认情况下,范围访问是一个候选优化策略,因此没有启用它的提示。当范围的数量可能很高,并且范围优化需要很多资源时,这个提示可能很有用。
ORDER_INDEX
,NO_ORDER_INDEX
:使MySQL使用或忽略指定的索引进行行排序。相当于命令的力索引
,忽略order by的索引
.从MySQL 8.0.20开始可用。SKIP_SCAN
,NO_SKIP_SCAN
:对指定表或索引启用或禁用Skip Scan访问方式。有关此访问方法的信息,请参见跳过扫描范围访问方法.这些提示在MySQL 8.0.13中可用。的
SKIP_SCAN
hint强制优化器使用指定的索引集对指定的表使用跳过扫描。如果没有指定索引,优化器将考虑所有可能的索引并选择代价最小的索引。如果索引不适用于给定的语句,则可以忽略该提示。的
NO_SKIP_SCAN
hint禁止对指定的索引进行跳过扫描。如果提示没有指定索引,则不允许对表进行跳过扫描。
tbl_name
:应用提示的表。index_name
:命名表中索引的名称。该提示应用于它命名的所有索引。如果提示没有命名索引,则应用于表中的所有索引。要引用主键,请使用名称
主要的
.要查看表的索引名,请使用显示指数
.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和t2。f2 t1之间。f1和t1。f2和t2。F2 + 1 >= t1。f1 + 1);SELECT /*+ SKIP_SCAN(t1 PRIMARY) */ f1, f2 FROM t1 WHERE f2 > 40;
下面的示例使用索引合并提示,但其他索引级提示遵循相同的原则,包括提示忽略和优化器提示的优先级optimizer_switch
系统变量或索引提示。
假设表t1
已经列一个
,b
,c
,d
;索引命名为i_a
,i_b
,i_c
存在于一个
,b
,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_b i_a 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
优化器提示,这些优先级规则适用:
类的索引合并相关标志的优先级
optimizer_switch
系统变量。设置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)
在这种情况下。设置optimizer_switch = ' index_merge_intersection = ';SELECT /*+ INDEX_MERGE(t1 i_b) */ * FROM t1 WHERE b = 1 AND c = 2 AND d = 3;
提示只指定了一个索引,因此不适用
optimizer_switch
标志(在
)适用。如果优化器评估索引合并具有成本效益,则使用索引合并。设置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
标志(从
)适用。没有使用索引合并。索引级优化器提示
GROUP_INDEX
,指数
,JOIN_INDEX
,ORDER_INDEX
所有的都优先于同等的力指数
提示;也就是说,它们引起力指数
可以忽略的暗示。同样,NO_GROUP_INDEX
,NO_INDEX
,NO_JOIN_INDEX
,NO_ORDER_INDEX
所有的暗示都比任何暗示都重要忽略指数
等价物,也导致它们被忽略。索引级优化器提示
GROUP_INDEX
,NO_GROUP_INDEX
,指数
,NO_INDEX
,JOIN_INDEX
,NO_JOIN_INDEX
,ORDER_INDEX
,NO_ORDER_INDEX
所有提示都优先于所有其他优化器提示,包括其他索引级优化器提示。任何其他优化器提示只应用于它们所允许的索引。的
GROUP_INDEX
,指数
,JOIN_INDEX
,ORDER_INDEX
暗示都相当于力指数
而不是使用索引
.这是因为使用一个或多个提示意味着只有在无法使用其中一个命名索引查找表中的行时才使用表扫描。使MySQL使用与给定实例相同的索引或索引集使用索引
,你可以使用NO_INDEX
,NO_JOIN_INDEX
,NO_GROUP_INDEX
,NO_ORDER_INDEX
,或者两者的某种组合。为了复制这种效果
使用索引
已在查询中使用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
为整个表配上使用索引的顺序
不工作做这个,因为NO_ORDER_BY
原因使用索引
被忽略,如下所示:mysql >解释选择/ * + NO_ORDER_INDEX (t1) * / a, c从t1 - >使用索引顺序(i_a)秩序\ G *************************** 1。row *************************** id: 1 select_type: SIMPLE table: t1 partitions: NULL type: ALL possible le_keys: NULL key: NULL key_len: NULL ref: NULL rows: 256 filtered: 100.00 Extra: Using filesort
的
使用索引
,力指数
,忽略指数
索引提示的优先级高于INDEX_MERGE
而且NO_INDEX_MERGE
优化器提示。/*+ INDEX_MERGE(t1 i_a, i_b, i_c) */…忽略指数i_a
忽略指数
优先于INDEX_MERGE
,所以指数i_a
从索引合并的可能范围中排除。/*+ NO_INDEX_MERGE(t1 i_a, i_b) */…FORCE INDEX i_a, i_b
不允许索引合并
i_a, i_b
因为力指数
,但优化器被迫使用其中之一i_a
或i_b
为范围
或裁判
访问。没有冲突;这两个提示都适用。如果一个
忽略指数
提示命名多个索引,这些索引不能用于索引合并。的
力指数
而且使用索引
提示使只有指定的索引可用于索引合并。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)
.同样,如果力指数
更改为使用索引
.
子查询提示会影响是否使用半连接转换和允许哪些半连接策略,以及在不使用半连接时,是否使用子查询物化或在
- - - - - - - - -存在
转换。有关这些优化的更多信息,请参见第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。一个在(选择/*+子查询(INTOEXISTS)* /一个 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
应用于整个语句,并且必须出现在第一个语句之后选择
.它适用于只读
选择
语句。非只读语句是调用作为副作用修改数据的存储函数的语句。它不适用于
选择
语句,并被忽略。
的SET_VAR
提示临时设置系统变量的会话值(在一条语句的持续时间内)。例子:
SELECT /*+ SET_VAR(sort_buffer_size = 16M) */ name FROM peopleINSERT /*+ SET_VAR(foreign_key_checks=OFF) */ INTO t2 VALUES(2);SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=off') */ 1;
语法的SET_VAR
提示:
SET_VAR (var_name=价值)
var_name
命名一个具有会话值的系统变量(尽管不是所有这样的变量都可以命名,后面会解释)。价值
是要赋给变量的值;必须为标量。
SET_VAR
对临时变量进行更改,如以下语句所示:
mysql >选择@@unique_checks;+-----------------+ | @@ unique_checks | +-----------------+ | 1 | +-----------------+ mysql >选择/ * + SET_VAR (unique_checks =) * / @@unique_checks;+-----------------+ | @@ unique_checks | +-----------------+ | 0 | +-----------------+ mysql >选择@@unique_checks;+-----------------+ | @@ unique_checks | +-----------------+ | 1 | +-----------------+
与SET_VAR
,不需要保存和恢复变量值。这使您可以用一条语句替换多条语句。考虑下面的语句序列:
SET @saved_val = @@SESSION。var_name;设置@@SESSION。var_name=价值;选择……设置@@SESSION。var_name= @saved_val;
这个序列可以用下面这条语句替换:
选择/ * + SET_VAR (var_name=价值)…
独立的集
语句允许以下任何一种语法来命名会话变量:
设置会话var_name=价值;设置@@SESSION。var_name=价值;设置@@。var_name=价值;
因为SET_VAR
Hint只应用于会话变量,会话作用域是隐式的,并且会话
,@@SESSION。
,@@
既不需要也不允许。包含显式会话指示符语法将导致SET_VAR
提示被忽略,并发出警告。
并不是所有的会话变量都允许使用SET_VAR
.单个系统变量的描述表明每个变量是否隐含;看到第5.1.8节,“服务器系统变量”.您还可以在运行时通过尝试使用它来检查系统变量SET_VAR
.如果变量是不可提示的,则会出现警告:
mysql> SELECT /*+ SET_VAR(collation_server = 'utf8') */ 1;+——+ | 1 | +---+ | 1 | +——+ 1行,1警告(0.00秒)mysql >显示警告\ G *************************** 1。行***************************级别:警告代码:4537消息:变量collation_server不能使用SET_VAR提示设置。
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
需要的值在
或从
,所以尝试设置它是的
是不正确的。在每种情况下,提示都会被忽略并发出警告。
的SET_VAR
只允许在语句级别使用Hint。如果在子查询中使用,则忽略提示并发出警告。
副本忽略SET_VAR
复制语句中的提示,以避免潜在的安全问题。
的RESOURCE_GROUP
优化器提示用于资源组管理(参见第5.1.16节,“资源组”).该提示将执行语句的线程临时分配给指定的资源组(在语句执行期间)。它要求RESOURCE_GROUP_ADMIN
或RESOURCE_GROUP_USER
特权。
例子:
SELECT /*+ RESOURCE_GROUP(USR_default) */ name FROM peopleINSERT /*+ RESOURCE_GROUP(Batch) */ INTO t2 VALUES(2);
语法的RESOURCE_GROUP
提示:
RESOURCE_GROUP (group_name)
group_name
指示在语句执行期间应将线程分配到的资源组。如果组不存在,则会出现警告并忽略提示。
的RESOURCE_GROUP
提示必须出现在初始语句关键字(选择
,插入
,取代
,更新
,或删除
).
另一种选择RESOURCE_GROUP
是设置资源组
语句,该语句非临时地将线程分配给资源组。看到第13.7.2.4节“SET RESOURCE GROUP Statement”.
表级、索引级和子查询优化器提示允许将特定查询块命名为其参数语法的一部分。要创建这些名称,请使用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(“我暗示的名字 ") */ ...) ...