mysql 优化器提示_mysql8 参考手册--优化器提示

控制优化程序策略的一种方法是设置 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 /+ 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.9.4节“索引提示”中所述的索引提示不同。优化程序和索引提示可以单独使用，也可以一起使用。

优化程序提示概述

优化程序提示语法

联合订单优化器提示

表级优化器提示

索引级优化器提示

子查询优化器提示

语句执行时间优化器提示

变量设置提示语法

资源组提示语法

用于命名查询块的优化器提示

优化程序提示概述

优化器提示适用于不同的作用域级别：

全局：提示会影响整个语句

查询块：提示会影响语句中的特定查询块

表级别：提示会影响查询块中的特定表

索引级别：提示会影响表中的特定索引

下表总结了可用的优化器提示，它们影响的优化器策略以及它们适用的范围。稍后给出更多细节。

表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 限制语句执行时间 全球

MERGE， NO_MERGE 影响派生表/视图合并到外部查询块中 表

MRR， NO_MRR 影响多范围读取优化 表，索引

NO_ICP 影响索引条件下推式优化 表，索引

NO_RANGE_OPTIMIZATION 影响范围优化 表，索引

QB_NAME 为查询块分配名称 查询块

RESOURCE_GROUP 在语句执行期间设置资源组 全球

SEMIJOIN， NO_SEMIJOIN 影响半联接策略；从MySQL 8.0.17开始，这也适用于抗联接 查询块

SKIP_SCAN， NO_SKIP_SCAN 影响跳过扫描优化 表，索引

SET_VAR 在语句执行期间设置变量 全球

SUBQUERY 影响物化， IN至- EXISTS 子查询配置的对策探讨 查询块

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

优化程序提示语法

MySQL支持SQL语句中的注释，如 第9.6节“注释语法”中所述。优化器提示必须在/+ .../注释中指定。也就是说，优化程序提示使用/.../ 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 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使用第一个提示，并发出有关第二个冲突提示的警告。

查询块名称是标识符，并遵循有关哪些名称有效以及如何对其进行引用的常规规则(请参见 第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：强制优化器使用表在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);

提示控制合并到外部查询块的半联接表的行为。如果将子查询 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 t2 LEFT JOIN t1;

在此，提示中的请求加入顺序与所需的顺序之间发生冲突LEFT JOIN。该提示将被忽略，而不会发出警告。

表级优化器提示

表级提示影响：

使用块嵌套循环(BNL)和批处理密钥访问(BKA)联接处理算法(请参见 第8.2.1.12节“块嵌套环和批处理密钥访问联接”)。

是否应将派生表，视图引用或公共表表达式合并到外部查询块中，或使用内部临时表实例化。

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

表级提示的语法：

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_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

对于MERGE和 NO_MERGE提示，这些优先级规则适用：

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

提示优先于系统变量的 derived_merge标志 optimizer_switch。

对于视图引用，ALGORITHM={MERGE|TEMPTABLE}视图定义中的 子句优先于查询中引用该视图的查询中指定的提示。

索引级优化器提示

索引级别的提示会影响优化器针对特定表或索引使用的索引处理策略。这些提示类型影响索引条件下推(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：允许这些提示名称：

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

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

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

MRR， NO_MRR：启用或禁用指定表或索引的MRR。MRR提示仅适用于InnoDB和 MyISAM表。有关此访问方法的信息，请参见 第8.2.1.11节“多范围读取优化”。

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

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

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

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_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中的列 a，b， c，和d; 和索引命名i_a，i_b以及i_c存在于a， 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_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_MERGE和 NO_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)适用。不使用索引合并。

的USE INDEX，FORCE INDEX和IGNORE INDEX索引提示具有比更高的优先级 INDEX_MERGE和 NO_INDEX_MERGE优化提示。

/+ INDEX_MERGE(t1 i_a, i_b, i_c)/ ... IGNORE INDEX i_a

IGNORE INDEX优先于 INDEX_MERGE，因此索引 i_a从索引合并的可能范围中排除。

/+ NO_INDEX_MERGE(t1 i_a, i_b)/ ... FORCE INDEX i_a, i_b

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

如果IGNORE INDEX提示命名多个索引，则这些索引不可用于索引合并。

在FORCE INDEX与USE INDEX只有指定索引可用于索引合并提示做。

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)。如果FORCE INDEX更改为，也是如此 USE INDEX。

子查询优化器提示

子查询提示会影响是否使用半联接转换以及允许使用哪些半联接策略，以及在不使用半联接时，是否使用子查询实现或 IN-to- EXISTS 转换。有关这些优化的更多信息，请参见第8.2.2节“优化子查询，派生表，视图引用和公用表表达式”。

影响半联接策略的提示语法：

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 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);

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

SUBQUERY([@query_block_name] strategy)

提示名称始终为 SUBQUERY。

对于SUBQUERY提示，可以使用以下 strategy值： INTOEXISTS， MATERIALIZATION。

例子：

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);

对于半联接和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。单独的系统变量描述指示每个变量是否暗示。请参见第5.1.8节“服务器系统变量”。您也可以在运行时通过尝试将其与一起使用来检查系统变量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 people ORDER BY name;

INSERT /+ RESOURCE_GROUP(Batch)/ INTO t2 VALUES(2);

RESOURCE_GROUP 提示的 语法：

RESOURCE_GROUP(group_name)

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

该RESOURCE_GROUP提示必须出现在最初的声明关键字后(SELECT，INSERT， REPLACE，UPDATE，或 DELETE)。

语句 的替代方法 RESOURCE_GROUP是该 SET RESOURCE GROUP语句，该语句将线程临时分配给资源组。请参见 第13.7.2.4节“ 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

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

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