10bet网址
MySQL 8.0参考手册
相关的文档10bet官方网站 本手册下载 本手册节选

8.9.3优化器提示

控制优化器策略的一种方法是设置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可用的优化器提示

提示名称 描述 适用的范围
BKANO_BKA 影响批处理键访问连接处理 查询块,表
BNLNO_BNL 在MySQL 8.0.20之前:影响块嵌套循环连接处理;MySQL 8.0.18及以上版本:也会影响哈希连接优化;MySQL 8.0.20及以上版本:只影响哈希连接优化 查询块,表
DERIVED_CONDITION_PUSHDOWNNO_DERIVED_CONDITION_PUSHDOWN 使用或忽略物化派生表的派生条件下推优化(在MySQL 8.0.22中添加) 查询块,表
GROUP_INDEXNO_GROUP_INDEX 中索引扫描使用或忽略指定的索引集团操作(MySQL 8.0.20新增) 指数
HASH_JOINNO_HASH_JOIN 影响哈希连接优化(MySQL仅8.0.18) 查询块,表
指数NO_INDEX 作为…的组合JOIN_INDEXGROUP_INDEX,ORDER_INDEX的组合NO_JOIN_INDEXNO_GROUP_INDEX,NO_ORDER_INDEX(MySQL 8.0.20新增) 指数
INDEX_MERGENO_INDEX_MERGE 影响索引合并优化 表,索引
JOIN_FIXED_ORDER 中指定的表顺序联营令条款 查询块
JOIN_INDEXNO_JOIN_INDEX 使用或忽略任何访问方法的指定索引(在MySQL 8.0.20中添加) 指数
JOIN_ORDER 使用hint中指定的表顺序作为连接顺序 查询块
JOIN_PREFIX 对于联接顺序的第一个表,使用hint中指定的表顺序 查询块
JOIN_SUFFIX 对于联接顺序的最后一个表,使用hint中指定的表顺序 查询块
MAX_EXECUTION_TIME 限制语句执行时间 全球
合并NO_MERGE 影响将派生表/视图合并到外部查询块 表格
MRRNO_MRR 影响多范围读优化 表,索引
NO_ICP 影响索引条件下推优化 表,索引
NO_RANGE_OPTIMIZATION 影响范围的优化 表,索引
ORDER_INDEXNO_ORDER_INDEX 使用或忽略指定的索引来排序行(在MySQL 8.0.20中添加) 指数
QB_NAME 为查询块分配名称 查询块
RESOURCE_GROUP 在执行语句时设置资源组 全球
SEMIJOINNO_SEMIJOIN 影响semijoin策略;从MySQL 8.0.17开始,这也适用于反连接 查询块
SKIP_SCANNO_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_nametbl_name[,tbl_name]…)hint_nametbl_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@subq1t5@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;

    方法所需的顺序与提示中请求的连接顺序之间会发生冲突左连接.这种暗示没有任何警告就被忽略了。

表级优化器提示

表级提示影响:

这些提示类型应用于查询块中的特定表或所有表。

表级提示的语法:

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

语法指的是这些术语:

  • hint_name:这些提示名称是允许的:

    • BKANO_BKA:对指定表启用或禁用bacched key访问。

    • BNLNO_BNL:启用或禁用指定表的块嵌套循环。在MySQL 8.0.18和更高版本中,这些提示还启用和禁用哈希连接优化。

      请注意

      在MySQL 8.0.20和后续版本中删除了块嵌套循环优化,但是在启用和禁用散列连接时仍然支持这些提示。

    • DERIVED_CONDITION_PUSHDOWNNO_DERIVED_CONDITION_PUSHDOWN:启用或禁用对指定表使用派生表条件下推(在MySQL 8.0.22中添加)。有关更多信息,请参见第8.2.2.5节“推导条件下推优化”

    • HASH_JOINNO_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是发送方表。

合并而且NO_MERGE提示,这些优先级规则适用:

  • 提示优先于不是技术约束的任何优化器启发式。(如果将提示作为建议提供没有效果,优化器就有理由忽略它。)

  • 提示优先于derived_merge国旗的optimizer_switch系统变量。

  • 对于视图引用,一个合并算法={|可诱惑的}子句优先于引用视图的查询中指定的提示。

索引级别的优化器提示

索引级提示会影响优化器针对特定表或索引使用的索引处理策略。这些提示类型影响索引条件下压(ICP)、多范围读取(MRR)、索引合并和范围优化的使用(参见第8.2.1节“优化SELECT语句”).

索引级提示的语法:

hint_name([@query_block_nametbl_nameindex_name[,index_name]…])hint_nametbl_name@query_block_nameindex_name[,index_name]…])

语法指的是这些术语:

  • hint_name:这些提示名称是允许的:

    • GROUP_INDEXNO_GROUP_INDEX:启用或禁用用于索引扫描的指定索引集团操作。相当于索引提示力指数组由忽略组by的索引.在MySQL 8.0.20及更高版本中可用。

    • 指数NO_INDEX:作为…的组合JOIN_INDEXGROUP_INDEX,ORDER_INDEX的组合,强制服务器对任意和所有作用域使用指定的索引或多个索引NO_JOIN_INDEXNO_GROUP_INDEX,NO_ORDER_INDEX,这会导致服务器忽略任何和所有作用域的指定索引或多个索引。相当于力指数忽略指数.从MySQL 8.0.20开始可用。

    • INDEX_MERGENO_INDEX_MERGE:对指定的表或索引启用或禁用索引合并访问方法。有关此访问方法的信息,请参见第8.2.1.3节“索引合并优化”.这些提示适用于所有三种索引合并算法。

      INDEX_MERGEhint强制优化器使用指定的索引集对指定的表使用索引合并。如果没有指定索引,优化器将考虑所有可能的索引组合,并选择代价最小的一个。如果索引组合不适用于给定语句,则可以忽略该提示。

      NO_INDEX_MERGEhint禁用涉及任何指定索引的索引合并组合。如果提示没有指定索引,则该表不允许索引合并。

    • JOIN_INDEXNO_JOIN_INDEX:强制MySQL使用或忽略任何访问方法的指定索引,例如裁判范围index_merge,等等。相当于连接的力索引忽略连接索引.在MySQL 8.0.20及更高版本中可用。

    • MRRNO_MRR:对指定表或索引启用或禁用MRR。MRR提示仅适用于InnoDB而且MyISAM表。有关此访问方法的信息,请参见第8.2.1.11节“多范围读优化”

    • NO_ICP:关闭指定表或索引的ICP。默认情况下,ICP是一个候选优化策略,因此没有启用它的提示。有关此访问方法的信息,请参见第8.2.1.6节“索引条件下推优化”

    • NO_RANGE_OPTIMIZATION:对指定的表或索引禁用索引范围访问。此提示还禁用表或索引的索引合并和松散索引扫描。默认情况下,范围访问是一个候选优化策略,因此没有启用它的提示。

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

    • ORDER_INDEXNO_ORDER_INDEX:使MySQL使用或忽略指定的索引进行行排序。相当于命令的力索引忽略order by的索引.从MySQL 8.0.20开始可用。

    • SKIP_SCANNO_SKIP_SCAN:对指定表或索引启用或禁用Skip Scan访问方式。有关此访问方法的信息,请参见跳过扫描范围访问方法.这些提示在MySQL 8.0.13中可用。

      SKIP_SCANhint强制优化器使用指定的索引集对指定的表使用跳过扫描。如果没有指定索引,优化器将考虑所有可能的索引并选择代价最小的索引。如果索引不适用于给定的语句,则可以忽略该提示。

      NO_SKIP_SCANhint禁止对指定的索引进行跳过扫描。如果提示没有指定索引,则不允许对表进行跳过扫描。

  • 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已经列一个bc,d;索引命名为i_ai_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_INDEXNO_INDEXNO_JOIN_INDEX,NO_ORDER_INDEX所有的暗示都比任何暗示都重要忽略指数等价物,也导致它们被忽略。

    索引级优化器提示GROUP_INDEXNO_GROUP_INDEX指数NO_INDEXJOIN_INDEXNO_JOIN_INDEXORDER_INDEX,NO_ORDER_INDEX所有提示都优先于所有其他优化器提示,包括其他索引级优化器提示。任何其他优化器提示只应用于它们所允许的索引。

    GROUP_INDEX指数JOIN_INDEX,ORDER_INDEX暗示都相当于力指数而不是使用索引.这是因为使用一个或多个提示意味着只有在无法使用其中一个命名索引查找表中的行时才使用表扫描。使MySQL使用与给定实例相同的索引或索引集使用索引,你可以使用NO_INDEXNO_JOIN_INDEXNO_GROUP_INDEXNO_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_ai_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:这些提示名称是允许的:

  • 策略:需要启用或禁用的半连接策略。这些策略名称是允许的:DUPSWEEDOUTFIRSTMATCHLOOSESCAN物质化

    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_VARHint只应用于会话变量,会话作用域是隐式的,并且会话@@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_ADMINRESOURCE_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) */…从  ...)) ...

其结果如下:

查询块名称是标识符,并遵循关于哪些名称有效以及如何引用它们的通常规则(参见第9.2节“模式对象名称”).例如,包含空格的查询块名称必须加引号,这可以使用反勾:

SELECT /*+ BKA(@ '我的提示名')*/…从(选择/ * + QB_NAME(“我暗示的名字 `) */ ...) ...

如果ANSI_QUOTES启用SQL模式,也可以在双引号内引用查询块名称:

SELECT /*+ BKA(@"我的提示名")*/…从(选择/ * + QB_NAME(“我暗示的名字 ") */ ...) ...