【MySQL精通之路】查询优化器的使用(8)-优化器提示_mysql查询优化器提示

博主PS：优化器提示的作用就是你可以提示优化器使用什么优化策略。当然优化器只是被提示了，而不是必须按你的提示做出操作，它可以执行或者拒绝你的提示。所以它叫优化器提示，而不是优化器配置。

控制优化器策略的一种方法是设置优化器切换系统变量（见“可切换优化”）

对此变量的更改会影响所有后续查询的执行；

为了以不同的方式影响一个查询，有必要在每个查询之前更改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);

这里描述的优化器提示不同于“索引提示”中描述的索引提示。

优化器提示和索引提示可以单独使用，也可以一起使用。

---

1.Optimizer提示概述

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

全局：提示影响整个语句

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

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

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

下表总结了可用的优化器提示、它们影响的优化器策略以及它们应用的范围。更多细节将在后面给出。

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

2.Optimizer提示语法

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 x IN (SELECT /*+ ... */ ...)
INSERT ... SELECT /*+ ... */ ...

在以EXPLAIN开头的暗示语句中。例如

EXPLAIN SELECT /*+ ... */ ...
EXPLAIN UPDATE ... WHERE x IN (SELECT /*+ ... */ ...)

这意味着您可以使用EXPLAIN来查看优化器提示如何影响执行计划。在EXPLAIN之后立即使用SHOW WARNINGS查看提示的使用方式。

以下SHOW WARNINGS显示的扩展EXPLAIN输出指示使用了哪些提示。不显示忽略的提示。

---

 

提示注释可以包含多个提示，但查询块不能包含多个暗示注释。这是有效的：

SELECT /*+ BNL(t1) BKA(t2) */ ...

但这是无效的：

SELECT /*+ BNL(t1) */ /* BKA(t2) */ ...

当提示注释包含多个提示时，可能存在重复和冲突。以下通用指南适用。对于特定的提示类型，可以应用附加规则，如提示描述中所示。

重复提示：对于/*+MRR（idx1）MRR（idx1）*/，MySQL使用第一个提示并发出关于重复提示的警告。

冲突提示：对于/*+ MRR(idx1) NO_MRR(idx1) */，MySQL使用第一个提示，并发出关于第二个冲突提示的警告。

查询块名称是标识符，并遵循关于哪些名称是有效的以及如何引用它们的常见规则

（请参阅“模式对象名称”）。

提示名称、查询块名称和策略名称不区分大小写。表和索引名称的引用遵循通常的标识符大小写敏感度规则（见第“标识符大小写敏感性”）。

3.联接顺序优化器提示

未完待续。。。

4.表级优化器提示

5.索引级别优化器提示

6.子查询优化器提示

7.语句执行时间优化器提示

8.变量设置提示语法

9.资源组提示语法

10.优化器命名查询块的提示