• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • 优化器提示

    控制优化程序策略的一种方法是设置optimizer_switch系统变量(请参见“可切换的优化”)。对该变量的更改会影响所有后续查询的执行;为了使一个查询与另一个查询有不同的影响,必须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 /*	+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */ * FROM t1 ...;
    EXPLAIN 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.2可用的优化程序提示

    提示名称描述适用范围
    BKANO_BKA影响批量密钥访问联接处理查询块,表
    BNLNO_BNL影响块嵌套循环连接处理查询块,表
    HASH_JOINNO_HASH_JOIN影响哈希联接优化查询块,表
    INDEX_MERGENO_INDEX_MERGE影响索引合并优化表,索引
    JOIN_FIXED_ORDER使用FROM子句中指定的表顺序作为连接顺序查询块
    JOIN_ORDER使用提示中指定的表顺序作为连接顺序查询块
    JOIN_PREFIX将提示中指定的表顺序用于连接顺序的第一个表查询块
    JOIN_SUFFIX将提示中指定的表顺序用于联接顺序的最后一个表查询块
    MAX_EXECUTION_TIME限制语句执行时间Global
    MERGENO_MERGE影响派生表/视图合并到外部查询块中
    MRRNO_MRR影响多范围读取优化表,索引
    NO_ICP影响索引条件下推式优化表,索引
    NO_RANGE_OPTIMIZATION影响范围优化表,索引
    QB_NAME为查询块分配名称查询块
    RESOURCE_GROUP在语句执行期间设置资源组Global
    SEMIJOINNO_SEMIJOIN影响半联接策略;从MySQL 8.0.17开始,这也适用于抗联接查询块
    SKIP_SCANNO_SKIP_SCAN影响跳过扫描优化表,索引
    SET_VAR在语句执行期间设置变量Global
    SUBQUERY影响物化,IN至-EXISTS子查询配置的对策探讨查询块

    禁用优化会阻止优化器使用它。启用优化意味着,如果优化器适用于语句执行,则它可以自由使用该策略,而不是优化器必然会使用它。

    优化程序提示语法

    MySQL支持SQL语句中的注释,如“注释语法”中所述。优化器提示必须在/* +...*/注释中指定。也就是说,优化程序提示使用/*...*/ C样式注释语法的变体,并 +/*注释打开序列之后添加一个字符。例子:

    /*	+ BKA(t1) */
    /*	+ BNL(t1, t2) */
    /*	+ NO_RANGE_OPTIMIZATION(t4 PRIMARY) */
    /*	+ QB_NAME(qb2) */
    

    +字符后允许有空格。

    解析器承认优化的初始关键字后暗示的意见SELECTUPDATEINSERTREPLACE,和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用来参见优化器提示如何影响执行计划。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_nametbl_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);
    

    提示控制合并到外部查询块的半联接表的行为。如果将子查询subq1subq2转换为半联接,则表t4@subq1t5@subq2将合并到外部查询块。在这种情况下,提示在外部查询块控制指定的行为t4@subq1t5@subq2表。

    优化器根据以下原则来解析连接顺序提示:

    • 多个提示实例

      每种类型仅应用一个JOIN_PREFIXJOIN_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_ORDERJOIN_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支持多种类型的连接:LEFTRIGHTINNERCROSSSTRAIGHT_JOIN。与指定的联接类型冲突的提示将被忽略,而不会发出警告。

      例:

      SELECT /*	+ JOIN_PREFIX(t1, t2) */FROM t2 LEFT 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:允许这些提示名称:

      • BKANO_BKA:为指定的表启用或禁用BKA。
      • BNLNO_BNL:为指定的表启用或禁用BNL。
      • HASH_JOINNO_HASH_JOIN:对指定的表启用或禁用哈希联接(仅适用于MySQL 8.0.18;在MySQL 8.0.19或更高版本中无效)。
      • MERGENO_MERGE:为指定的表,视图引用或公共表表达式启用合并;或禁用合并并改用实现。
      注意

      若要使用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 /*	+ NO_MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt;
    

    表级提示适用于从先前的表而非发送方表接收记录的表。考虑以下语句:

    SELECT /*	+ BNL(t2) */ FROM t1, t2;
    

    如果优化器选择先处理t1,它将在开始读取之前t2对行进行缓冲,从而对块嵌套循环联接应用。如果优化程序选择先处理,则提示无效,因为它是发送方表。t1t2t2t2

    对于MERGENO_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_INDEXNO_GROUP_INDEX:启用或禁用指定的索引以进行GROUP BY操作的索引扫描。相当于索引提示FORCE INDEX FOR GROUP BYIGNORE INDEX FOR GROUP BY。在MySQL 8.0.20及更高版本中可用。
      • INDEXNO_INDEX:作为的组合JOIN_INDEXGROUP_INDEX以及ORDER_INDEX,迫使服务器以用于任何指定索引或索引和所有范围,或为一体的组合NO_JOIN_INDEXNO_GROUP_INDEXNO_ORDER_INDEX,这使得服务器忽略任何指定索引或索引和所有范围。相当于FORCE INDEXIGNORE INDEX。从MySQL 8.0.20开始可用。
      • INDEX_MERGENO_INDEX_MERGE:启用或禁用指定表或索引的索引合并访问方法。有关此访问方法的信息,请参见“优化索引”。这些提示适用于所有三种索引合并算法。

        INDEX_MERGE提示会强制优化器使用指定索引集对指定表使用索引合并。如果未指定索引,则优化器将考虑所有可能的索引组合并选择最便宜的索引组合。如果索引组合不适用于给定的语句,则可以忽略该提示。

        NO_INDEX_MERGE提示将禁用涉及任何指定索引的索引合并组合。如果提示未指定索引,则表不允许索引合并。

      • JOIN_INDEXNO_JOIN_INDEX:强制MySQL使用或忽略指定的索引或索引为任何存取方法,诸如refrangeindex_merge,等。相当于FORCE INDEX FOR JOINIGNORE INDEX FOR JOIN。在MySQL 8.0.20及更高版本中可用。
      • MRRNO_MRR:启用或禁用指定表或索引的MRR。MRR提示仅适用于InnoDBMyISAM表。有关此访问方法的信息,请参见“多范围读取优化”。
      • NO_ICP:对指定的表或索引禁用ICP。默认情况下,ICP是一种候选优化策略,因此没有启用它的提示。有关此访问方法的信息,请参见“索引条件下推优化”。
      • NO_RANGE_OPTIMIZATION:禁用指定表或索引的索引范围访问。此提示还禁用了表或索引的索引合并和松散索引扫描。默认情况下,范围访问是一种候选优化策略,因此没有启用它的提示。

        当范围数可能很高并且范围优化将需要许多资源时,此提示可能很有用。

      • ORDER_INDEXNO_ORDER_INDEX:使MySQL使用或忽略指定的一个或多个用于对行进行排序的索引。相当于FORCE INDEX FOR ORDER BYIGNORE INDEX FOR ORDER BY。从MySQL 8.0.20开始可用。
      • SKIP_SCANNO_SKIP_SCAN:为指定的表或索引启用或禁用“跳过扫描”访问方法。有关此访问方法的信息,请参见跳过扫描范围访问方法。这些提示自MySQL 8.0.13起可用。

        SKIP_SCAN提示会强制优化器使用指定索引集对指定表使用“跳过扫描”。如果未指定索引,则优化器将考虑所有可能的索引并选择最便宜的索引。如果索引不适用于给定的语句,则可以忽略该提示。

        NO_SKIP_SCAN提示禁用指定索引的跳过扫描。如果提示未指定索引,则不允许对该表进行跳过扫描。

    • tbl_name:提示适用的表格。
    • index_name:命名表中索引的名称。提示适用于它命名的所有索引。如果提示未命名索引,则它将应用于表中的所有索引。

      要引用主键,请使用名称PRIMARY。要参见表的索引名称,请使用SHOW INDEX

    • query_block_name:提示适用于的查询块。如果提示中不包含前导,则该提示适用于出现该查询的查询块。对于语法,提示适用于命名查询块中的命名表。要将名称分配给查询块,请参阅命名查询块的优化器提示。@query_block_nametbl_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 AND t2.f2 BETWEEN t1.f1
       AND t1.f2 AND t2.f2 	+ 1 >= t1.f1 	+ 1);
    SELECT /*	+ SKIP_SCAN(t1 PRIMARY) */ f1, f2
      FROM t1 WHERE f2 > 40;
    

    以下示例使用索引合并提示,但是其他索引级别的提示遵循与忽略提示和优化程序提示相对于optimizer_switch系统变量或索引提示的优先级相同的原理。

    假设表t1中的列,bc,和d;和索引命名i_ i_b以及i_c存在于,bc分别为:

    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_a, i_b, 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_MERGENO_INDEX_MERGE优化程序提示,这些优先级规则适用:

    • 如果指定了优化器提示并且该提示适用,那么它优先于optimizer_switch系统变量的与索引合并相关的标志。

      SET 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)在这种情况下,使用索引合并。

      SET optimizer_switch='index_merge_intersection=on';
      SELECT /*	+ INDEX_MERGE(t1 i_b) */ * FROM t1
      WHERE 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 t1
      WHERE b = 1 AND c = 2 AND d = 3;
      

      该提示仅指定一个索引,因此不适用,并且optimizer_switch标志(off)适用。不使用索引合并。

    • 索引级优化标记GROUP_INDEXINDEXJOIN_INDEX,和ORDER_INDEX所有优先于等效FORCE INDEX的提示;也就是说,它们导致FORCE INDEX提示被忽略。同样,NO_GROUP_INDEXNO_INDEXNO_JOIN_INDEX,和NO_ORDER_INDEX提示都优先于任何IGNORE INDEX等价物,也使他们被忽略。

      该指数级的优化程序提示GROUP_INDEXNO_GROUP_INDEXINDEXNO_INDEXJOIN_INDEXNO_JOIN_INDEXORDER_INDEX,并NO_ORDER_INDEX提示所有优先于所有其他的优化器提示,包括其他指数级的优化提示。任何其他优化器提示仅应用于这些允许的索引。

      GROUP_INDEXINDEXJOIN_INDEX,和ORDER_INDEX提示都相当于FORCE INDEX而不是USE INDEX。这是因为使用这些提示中的一个或多个提示意味着仅当无法使用命名索引之一在表中查找行时才使用表扫描。为了使MySQL使用索引的相同指标或设定为具有给定的情况下USE INDEX,你可以使用NO_INDEXNO_JOIN_INDEXNO_GROUP_INDEXNO_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) */  ,c
          FROM t1
          ORDER BY  ;
      

      尝试将NO_ORDER_INDEX表作为一个整体进行合并USE INDEX FOR ORDER BY无法执行此操作,因为NO_ORDER_BY会导致USE INDEX忽略,如下所示:

      mysql> EXPLAIN SELECT /*	+ NO_ORDER_INDEX(t1) */  ,c FROM 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 filesort
      
    • USE INDEXFORCE INDEXIGNORE INDEX索引提示具有比更高的优先级INDEX_MERGENO_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_b
      

      i_a, i_b由于不允许索引合并FORCE INDEX,但是优化器被迫使用i_ i_b用于rangeref访问。没有冲突。两种提示均适用。

    • 如果IGNORE INDEX提示命名多个索引,则这些索引不可用于索引合并。
    • FORCE INDEXUSE INDEX只有指定索引可用于索引合并提示做。

      SELECT /*	+ INDEX_MERGE(t1 i_a, i_b, i_c) */ a FROM t1
      FORCE 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:允许这些提示名称:

      • SEMIJOINNO_SEMIJOIN:启用或禁用命名的半连接策略。
    • strategy:启用或禁用的半连接策略。这些策略名允许:DUPSWEEDOUTFIRSTMATCHLOOSESCANMATERIALIZATION

      对于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.  IN (SELECT /*	+ QB_NAME(subq1) */ a FROM t3);
    SELECT /*	+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2
      WHERE t2.  IN (SELECT /*	+ QB_NAME(subq1) */ a FROM t3);
    

    影响是否使用子查询实现或IN-to-EXISTS转换的提示的语法:

    SUBQUERY([@query_block_name] strategy)
    

    提示名称始终为SUBQUERY

    对于SUBQUERY提示,可以使用以下strategy值:INTOEXISTSMATERIALIZATION

    例子:

    SELECT id, a IN (SELECT /*	+ SUBQUERY(MATERIALIZATION) */ a FROM t1) FROM t2;
    SELECT * FROM t2 WHERE t2.  IN (SELECT /*	+ SUBQUERY(INTOEXISTS) */ a FROM 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 t1 INNER JOIN t2 WHERE ...
    

    该提示设置的语句执行超时毫秒。如果此选项不存在或为0,则应用系统变量建立的语句超时。MAX_EXECUTION_TIME(N)NNmax_execution_time

    MAX_EXECUTION_TIME提示适用于以下情况:

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

    变量设置提示语法

    所述SET_VAR提示暂时设定的系统变量的会话值(用于单个语句的持续时间)。例子:

    SELECT /*	+ SET_VAR(sort_buffer_size = 16M) */ name FROM people ORDER BY name;
    INSERT /*	+ 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 = 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_ADMINRESOURCE_GROUP_USER特权。

    例子:

    SELECT /*	+ RESOURCE_GROUP(USR_default) */ name FROM people ORDER BY name;
    INSERT /*	+ RESOURCE_GROUP(Batch) */ INTO t2 VALUES(2);
    

    RESOURCE_GROUP提示的语法:

    RESOURCE_GROUP(group_name)
    

    group_name指示在语句执行期间应将线程分配给的资源组。如果该组不存在,则会发生警告并忽略提示。

    RESOURCE_GROUP提示必须出现在最初的声明关键字后(SELECTINSERTREPLACEUPDATE,或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中的表。t1qb3

    查询块名称是标识符,并遵循有关哪些名称有效以及如何对其进行引用的常规规则(请参见“模式对象名称”)。例如,必须引用包含空格的查询块名称,这可以使用反引号来完成:

    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") */ ...) ...
    

    上篇:可切换的优化

    下篇:索引提示