MySQL8-中文参考-三十九-

布客飞龙
119 阅读47分钟

MySQL8 中文参考（三十九）

原文：docs.oracle.com/javase/tutorial/reallybigindex.html

10.8.3 扩展 EXPLAIN 输出格式

原文：dev.mysql.com/doc/refman/8.0/en/explain-extended.html

EXPLAIN语句生成额外（“扩展”）信息，这些信息不是EXPLAIN输出的一部分，但可以通过在EXPLAIN后发出SHOW WARNINGS语句来查看。从 MySQL 8.0.12 开始，扩展信息适用于SELECT、DELETE、INSERT、REPLACE和UPDATE语句。在 8.0.12 之前，扩展信息仅适用于SELECT语句。

SHOW WARNINGS输出中的Message值显示了优化器在SELECT语句中对表和列名进行限定的方式，应用重写和优化规则后的SELECT的样子，以及可能关于优化过程的其他说明。

在EXPLAIN后跟随SHOW WARNINGS语句的扩展信息仅适用于SELECT语句。对于其他可解释的语句（DELETE、INSERT、REPLACE和UPDATE），SHOW WARNINGS显示空结果。

这里是扩展EXPLAIN输出的示例：

mysql> EXPLAIN
       SELECT t1.a, t1.a IN (SELECT t2.a FROM t2) FROM t1\G
*************************** 1\. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 4
     filtered: 100.00
        Extra: Using index
*************************** 2\. row ***************************
           id: 2
  select_type: SUBQUERY
        table: t2
         type: index
possible_keys: a
          key: a
      key_len: 5
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index 2 rows in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1\. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `test`.`t1`.`a` AS `a`,
         <in_optimizer>(`test`.`t1`.`a`,`test`.`t1`.`a` in
         ( <materialize> (/* select#2 */ select `test`.`t2`.`a`
         from `test`.`t2` where 1 having 1 ),
         <primary_index_lookup>(`test`.`t1`.`a` in
         <temporary table> on <auto_key>
         where ((`test`.`t1`.`a` = `materialized-subquery`.`a`))))) AS `t1.a
         IN (SELECT t2.a FROM t2)` from `test`.`t1` 1 row in set (0.00 sec)

因为SHOW WARNINGS显示的语句可能包含特殊标记，提供有关查询重写或优化器操作的信息，所以该语句不一定是有效的 SQL，也不打算执行。输出还可能包含具有提供有关优化器执行的其他非 SQL 解释说明的Message值的行。

下面的列表描述了可以出现在SHOW WARNINGS显示的扩展输出中的特殊标记：

  • <auto_key>

    临时表的自动生成键。

  • <cache>(*expr*)

    表达式（如标量子查询）只执行一次，并且结果值保存在内存中以供后续使用。对于由多个值组成的结果，可能会创建一个临时表，显示为<temporary table>。

  • <exists>(*query fragment*)

    子查询谓词被转换为EXISTS谓词，并且子查询被转换以便与EXISTS谓词一起使用。

  • <in_optimizer>(*query fragment*)

    这是一个内部优化器对象，对用户没有意义。

  • <index_lookup>(*query fragment*)

    使用索引查找来处理查询片段以找到符合条件的行。

  • <if>(*condition*, *expr1*, *expr2*)

    如果条件为真，则评估为*expr1，否则为expr2*。

  • <is_not_null_test>(*expr*)

    用于验证表达式不会评估为NULL的测试。

  • <materialize>(*query fragment*)

    使用子查询实现物化。

  • ``materialized-subquery.*col_name*

    对内部临时表中的*col_name*列的引用，该临时表用于保存子查询的结果。

  • <primary_index_lookup>(*query fragment*)

    使用主键查找来处理查询片段以找到符合条件的行。

  • <ref_null_helper>(*expr*)

    这是一个内部优化器对象，对用户没有意义。

  • /* select#*N* */ *select_stmt*

    SELECT与非扩展的EXPLAIN输出中id值为*N*的行相关联。

  • *outer_tables* semi join (*inner_tables*)

    半连接操作。*inner_tables*显示未被提取的表。参见 Section 10.2.2.1, “Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations”。

  • <temporary table>

    这代表一个内部临时表，用于缓存中间结果。

当某些表是const或system类型时，涉及这些表列的表达式会被优化器提前评估，并且不会显示在语句中。然而，使用FORMAT=JSON时，一些const表访问会显示为使用 const 值的ref访问。

10.8.4 获取命名连接的执行计划信息

原文：dev.mysql.com/doc/refman/8.0/en/explain-for-connection.html

要获取在命名连接中执行的可解释语句的执行计划，请使用此语句：

EXPLAIN [*options*] FOR CONNECTION *connection_id*;

EXPLAIN FOR CONNECTION返回当前用于在给定连接中执行查询的EXPLAIN信息。由于数据（及支持统计数据）的更改，它可能产生与在等效查询文本上运行EXPLAIN不同的结果。这种行为上的差异在诊断更瞬时的性能问题时可能很有用。例如，如果您在一个会话中运行一个需要很长时间才能完成的语句，使用另一个会话中的EXPLAIN FOR CONNECTION可能会提供有关延迟原因的有用信息。

connection_id 是连接标识符，可从INFORMATION_SCHEMA PROCESSLIST表或SHOW PROCESSLIST语句中获取。如果您拥有PROCESS权限，可以指定任何连接的标识符。否则，只能指定自己连接的标识符。在所有情况下，您必须具有足够的权限来解释指定连接上的查询。

如果命名连接未执行语句，则结果为空。否则，只有在命名连接中执行的语句是可解释的情况下，EXPLAIN FOR CONNECTION才适用。这包括SELECT, DELETE, INSERT, REPLACE, 和 UPDATE。（但是，EXPLAIN FOR CONNECTION不适用于准备语句，即使是这些类型的准备语句。）

如果命名连接正在执行可解释语句，则输出与在语句本身上使用EXPLAIN获得的相同。

如果命名连接正在执行不可解释的语句，则会发生错误。例如，您不能命名当前会话的连接标识符，因为EXPLAIN不可解释：

mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|             373 |
+-----------------+
1 row in set (0.00 sec)

mysql> EXPLAIN FOR CONNECTION 373;
ERROR 1889 (HY000): EXPLAIN FOR CONNECTION command is supported
only for SELECT/UPDATE/INSERT/DELETE/REPLACE

Com_explain_other状态变量指示执行的EXPLAIN FOR CONNECTION语句的数量。

10.8.5 估计查询性能

原文：dev.mysql.com/doc/refman/8.0/en/estimating-performance.html

在大多数情况下，您可以通过计算磁盘寻道次数来估计查询性能。对于小表，您通常可以在一个磁盘寻道中找到一行（因为索引可能已缓存）。对于更大的表，您可以估计使用 B 树索引，您需要这么多次寻道才能找到一行：log(*row_count*) / log(*index_block_length* / 3 * 2 / (*index_length* + *data_pointer_length*)) + 1。

在 MySQL 中，索引块通常为 1,024 字节，数据指针通常为四个字节。对于一个具有 500,000 行的表，关键值长度为三个字节（MEDIUMINT - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT")的大小），该公式指示 log(500,000)/log(1024/3*2/(3+4)) + 1 = 4 次寻道。

这个索引需要存储大约 500,000 * 7 * 3/2 = 5.2MB 的空间（假设典型索引缓冲填充比为 2/3），所以你可能已经将大部分索引存储在内存中，因此只需要一两次读取数据来找到行。

然而，对于写入操作，您需要四个寻道请求来找到新索引值的放置位置，并通常需要两次寻道来更新索引并写入行。

前面的讨论并不意味着您的应用性能会随着 log N 而缓慢下降。只要一切都被操作系统或 MySQL 服务器缓存，随着表变得更大，事情只会变得稍微慢一点。当数据变得太大无法缓存时，事情开始变得慢得多，直到您的应用程序仅受磁盘寻道的限制（这些寻道按 log N 增加）。为了避免这种情况，随着数据增长，增加关键缓存大小。对于MyISAM表，关键缓存大小由key_buffer_size系统变量控制。请参阅第 7.1.1 节，“配置服务器”。

10.9 控制查询优化器

原文：dev.mysql.com/doc/refman/8.0/en/controlling-optimizer.html

10.9.1 控制查询计划评估

10.9.2 可切换的优化

10.9.3 优化器提示

10.9.4 索引提示

10.9.5 优化器成本模型

10.9.6 优化器统计信息

MySQL 通过影响查询计划如何评估的系统变量、可切换的优化、优化器和索引提示以及优化器成本模型提供优化器控制。

服务器在column_statistics数据字典表中维护关于列值的直方图统计信息（参见第 10.9.6 节，“优化器统计信息”）。像其他数据字典表一样，用户无法直接访问此表。相反，您可以通过查询INFORMATION_SCHEMA.COLUMN_STATISTICS来获取直方图信息，该表实现为数据字典表上的视图。您还可以使用ANALYZE TABLE语句执行直方图管理。

10.9.1 控制查询计划评估

原文：dev.mysql.com/doc/refman/8.0/en/controlling-query-plan-evaluation.html

查询优化器的任务是找到执行 SQL 查询的最佳计划。因为“好”和“坏”计划之间的性能差异可能是数量级的（即秒与小时甚至天），大多数查询优化器，包括 MySQL 的优化器，在所有可能的查询评估计划中执行更多或更少的详尽搜索以找到最佳计划。对于连接查询，MySQL 优化器研究的可能计划数量随查询中引用的表的数量呈指数增长。对于少量表（通常少于 7 到 10 个），这不是问题。然而，当提交较大的查询时，查询优化所花费的时间很容易成为服务器性能的主要瓶颈。

一种更灵活的查询优化方法使用户能够控制优化器在寻找最佳查询评估计划时的详尽程度。一般的想法是，优化器研究的计划越少，编译查询所花费的时间就越少。另一方面，因为优化器跳过了一些计划，它可能会错过找到最佳计划的机会。

优化器关于评估计划数量的行为可以通过两个系统变量进行控制：

  • optimizer_prune_level 变量告诉优化器根据每个表访问的行数的估计跳过某些计划。我们的经验表明，这种“有根据的猜测”很少会错过最佳计划，并且可能显著减少查询编译时间。这就是为什么默认情况下此选项为开启状态（optimizer_prune_level=1）。然而，如果您认为优化器错过了更好的查询计划，可以关闭此选项（optimizer_prune_level=0），但这样做可能会导致查询编译时间更长。请注意，即使使用了这种启发式方法，优化器仍然会探索大致指数数量的计划。

  • optimizer_search_depth变量告诉优化器应该查看每个不完整计划的“未来”多远，以评估是否应进一步扩展。较小的optimizer_search_depth值可能导致查询编译时间减少数个数量级。例如，具有 12、13 或更多表的查询如果optimizer_search_depth接近查询中的表数，可能需要几小时甚至几天才能编译。同时，如果使用optimizer_search_depth等于 3 或 4 进行编译，优化器可能在不到一分钟内为相同的查询编译完成。如果您不确定optimizer_search_depth的合理值是多少，可以将此变量设置为 0，告诉优化器自动确定该值。

10.9.2 可切换的优化

原文：dev.mysql.com/doc/refman/8.0/en/switchable-optimizations.html

optimizer_switch系统变量可控制优化器行为。其值是一组标志，每个标志的值为on或off，表示相应的优化器行为是否启用或禁用。此变量具有全局和会话值，并且可以在运行时更改。全局默认值可以在服务器启动时设置。

要查看当前设置的优化器标志集，请选择变量值：

mysql> SELECT @@optimizer_switch\G
*************************** 1\. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
                    index_merge_sort_union=on,index_merge_intersection=on,
                    engine_condition_pushdown=on,index_condition_pushdown=on,
                    mrr=on,mrr_cost_based=on,block_nested_loop=on,
                    batched_key_access=off,materialization=on,semijoin=on,
                    loosescan=on,firstmatch=on,duplicateweedout=on,
                    subquery_materialization_cost_based=on,
                    use_index_extensions=on,condition_fanout_filter=on,
                    derived_merge=on,use_invisible_indexes=off,skip_scan=on,
                    hash_join=on,subquery_to_derived=off,
                    prefer_ordering_index=on,hypergraph_optimizer=off,
                    derived_condition_pushdown=on 1 row in set (0.00 sec)

要更改optimizer_switch的值，请分配一个由一个或多个命令的逗号分隔列表组成的值：

SET [GLOBAL|SESSION] optimizer_switch='*command*[,*command*]...';

每个*command*值应具有以下表中所示的形式之一。

命令语法含义
default将每个优化重置为其默认值
*opt_name*=default将指定的优化设置为其默认值
*opt_name*=off禁用指定的优化
*opt_name*=on启用指定的优化

命令值的顺序无关紧要，尽管如果存在default命令，则首先执行该命令。将*opt_name标志设置为default会将其设置为on或off中的默认值。在值中多次指定任何给定的opt_name*是不允许的，并会导致错误。值中的任何错误都会导致分配失败并显示错误，使optimizer_switch的值保持不变。

以下列表描述了按优化策略分组的允许的*opt_name*标志名称：

  • 批量键访问标志

    • batched_key_access（默认为off）

      控制使用 BKA 连接算法。

    当设置为on时，batched_key_access要产生任何效果，mrr标志也必须为on。目前，对于 MRR 的成本估算过于悲观。因此，还需要将mrr_cost_based设置为off才能使用 BKA。

    有关更多信息，请参见第 10.2.1.12 节，“块嵌套循环和批量键访问连接”。

  • 块嵌套循环标志

    • block_nested_loop（默认为on）

      控制使用 BNL 连接算法。在 MySQL 8.0.18 及更高版本中，这也控制使用哈希连接，就像BNL和NO_BNL优化提示一样。在 MySQL 8.0.20 及更高版本中，MySQL 服务器中移除了块嵌套循环支持，此标志仅控制使用哈希连接，就像引用的优化提示一样。

    欲了解更多信息，请参阅 Section 10.2.1.12，“块嵌套循环和批量键访问连接”。

  • 条件过滤标志

    • condition_fanout_filter（默认on）

      控制条件过滤的使用。

    欲了解更多信息，请参阅 Section 10.2.1.13，“条件过滤”。

  • 派生条件下推标志

    • derived_condition_pushdown（默认on）

      控制派生条件下推。

    欲了解更多信息，请参阅 Section 10.2.2.5，“派生条件下推优化”

  • 派生表合并标志

    • derived_merge（默认on）

      控制将派生表和视图合并到外部查询块中。

    derived_merge标志控制优化器是否尝试将派生表、视图引用和公共表达式合并到外部查询块中，假设没有其他规则阻止合并；例如，视图的ALGORITHM指令优先于derived_merge设置。默认情况下，该标志为on以启用合并。

    欲了解更多信息，请参阅 Section 10.2.2.4，“优化派生表、视图引用和公共表达式的合并或材料化”。

  • 引擎条件下推标志

    • engine_condition_pushdown（默认on）

      控制引擎条件下推。

    欲了解更多信息，请参阅 Section 10.2.1.5，“引擎条件下推优化”。

  • 哈希连接标志

    • hash_join（默认on）

      仅在 MySQL 8.0.18 中控制哈希连接，对任何后续版本均无效。在 MySQL 8.0.19 及更高版本中，要控制哈希连接的使用，请使用block_nested_loop标志。

    更多信息，请参见第 10.2.1.4 节，“哈希连接优化”。

  • 索引条件下推标志

    • index_condition_pushdown（默认为on）

      控制索引条件下推。

    更多信息，请参见第 10.2.1.6 节，“索引条件下推优化”��

  • 索引扩展标志

    • use_index_extensions（默认为on）

      控制使用索引扩展。

    更多信息，请参见第 10.3.10 节，“使用索引扩展”。

  • 索引合并标志

    • index_merge（默认为on）

      控制所有索引合并优化。

    • index_merge_intersection（默认为on）

      控制索引合并交集访问优化。

    • index_merge_sort_union（默认为on）

      控制索引合并排序-联合访问优化。

    • index_merge_union（默认为on）

      控制索引合并联合访问优化。

    更多信息，请参见第 10.2.1.3 节，“索引合并优化”。

  • 索引可见性标志

    • use_invisible_indexes（默认为off）

      控制使用不可见索引。

    更多信息，请参见第 10.3.12 节，“不可见索引”。

  • 限制优化标志

    • prefer_ordering_index（默认为on）

      控制是否在查询具有带有ORDER BY或GROUP BY和LIMIT子句的情况下，优化器尝试使用有序索引而不是无序索引、文件排序或其他一些优化。只要优化器确定使用它可以加快查询的执行速度，此优化就会默认执行。

      由于进行此决定的算法无法处理每种可能的情况（部分原因是假设数据分布总是或多或少均匀的），因此存在这种优化可能不可取的情况。在 MySQL 8.0.21 之前，不可能禁用此优化，但在 MySQL 8.0.21 及更高版本中，虽然它仍然是默认行为，但可以通过将prefer_ordering_index标志设置为off来禁用它。

    有关更多信息和示例，请参见第 10.2.1.19 节，“LIMIT 查询优化”。

  • 多范围读取标志

    • mrr（默认on）

      控制多范围读取策略。

    • mrr_cost_based（默认on）

      如果mrr=on，则控制基于成本的 MRR 的使用。

    更多信息，请参见第 10.2.1.11 节，“多范围读取优化”。

  • 半连接标志

    • duplicateweedout（默认on）

      控制半连接 Duplicate Weedout 策略。

    • firstmatch（默认on）

      控制半连接 FirstMatch 策略。

    • loosescan（默认on）

      控制半连接松散扫描策略（不要与GROUP BY的松散索引扫描混淆）。

    • semijoin（默认on）

      控制所有半连接策略。

      在 MySQL 8.0.17 及更高版本中，这也适用于反连接优化。

    semijoin、firstmatch、loosescan和duplicateweedout标志可控制半连接策略。semijoin标志控制是否使用半连接。如果设置为on，则firstmatch和loosescan标志可更精细地控制允许的半连接策略。

    如果禁用了duplicateweedout半连接策略，则除非所有其他适用策略也被禁用，否则不会使用。

    如果semijoin和materialization都为on，则半连接也在适用的情况下使用材料化。这些标志默认为on。

    更多信息，请参见第 10.2.2.1 节，“使用半连接转换优化 IN 和 EXISTS 子查询谓词”。

  • 跳过扫描标志

    • skip_scan（默认on）

      控制跳过扫描访问方法的使用。

    更多信息，请参见跳过扫描范围访问方法。

  • 子查询材料化标志

    • materialization（默认on）

      控制材料化（包括半连接材料化）。

    • subquery_materialization_cost_based（默认on）

      使用基于成本的物化选择。

    materialization标志控制是否使用子查询物化。如果semijoin和materialization都为on，半连接也在适用的情况下使用物化。这些标志默认为on。

    subquery_materialization_cost_based标志允许控制子查询物化和IN到EXISTS子查询转换之间的选择。如果标志为on（默认值），优化器在子查询物化和IN到EXISTS子查询转换之间执行基于成本的选择，如果可以使用任一方法。如果标志为off，优化器选择子查询物化而不是IN到EXISTS子查询转换。

    更多信息，请参见 Section 10.2.2, “Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions”。

  • 子查询转换标志

    • subquery_to_derived（默认为off）

      从 MySQL 8.0.21 开始，优化器在许多情况下能够将SELECT、WHERE、JOIN或HAVING子句中的标量子查询转换为派生表上的左外连接。（根据派生表的可空性，有时可以进一步简化为内连接。）这可以用于满足以下条件的子查询：

      • 子查询不使用任何非确定性函数，如RAND()。

      • 子查询不是可以重写为MIN()或MAX()的ANY或ALL子查询。

      • 父查询不设置用户变量，因为重写它可能会影响执行顺序，如果变量在同一查询中被访问多次，可能会导致意外结果。

      • 子查询不应该是相关的，也就是说，它不应该引用外部查询中的列，或包含在外部查询中评估的聚合。

      在 MySQL 8.0.22 之前，子查询不能包含GROUP BY子句。

      这种优化也可以应用于作为IN、NOT IN、EXISTS或NOT EXISTS参数的表子查询，不包含GROUP BY。

      该标志的默认值为off，因为在大多数情况下，启用此优化并不会产生明显的性能改进（在许多情况下甚至可能使查询运行更慢），但您可以通过将subquery_to_derived标志设置为on来启用该优化。它主要用于测试。

      示例，使用标量子查询：

      d
mysql> CREATE TABLE t1(a INT);

mysql> CREATE TABLE t2(a INT);

mysql> INSERT INTO t1 VALUES ROW(1), ROW(2), ROW(3), ROW(4);

mysql> INSERT INTO t2 VALUES ROW(1), ROW(2);

mysql> SELECT * FROM t1
 ->     WHERE t1.a > (SELECT COUNT(a) FROM t2);
+------+
| a    |
+------+
|    3 |
|    4 |
+------+

mysql> SELECT @@optimizer_switch LIKE '%subquery_to_derived=off%';
+-----------------------------------------------------+
| @@optimizer_switch LIKE '%subquery_to_derived=off%' |
+-----------------------------------------------------+
|                                                   1 |
+-----------------------------------------------------+

mysql> EXPLAIN SELECT * FROM t1 WHERE t1.a > (SELECT COUNT(a) FROM t2)\G
*************************** 1\. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 33.33
        Extra: Using where
*************************** 2\. row ***************************
           id: 2
  select_type: SUBQUERY
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: NULL 
mysql> SET @@optimizer_switch='subquery_to_derived=on';

mysql> SELECT @@optimizer_switch LIKE '%subquery_to_derived=off%';
+-----------------------------------------------------+
| @@optimizer_switch LIKE '%subquery_to_derived=off%' |
+-----------------------------------------------------+
|                                                   0 |
+-----------------------------------------------------+

mysql> SELECT @@optimizer_switch LIKE '%subquery_to_derived=on%';
+----------------------------------------------------+
| @@optimizer_switch LIKE '%subquery_to_derived=on%' |
+----------------------------------------------------+
|                                                  1 |
+----------------------------------------------------+

mysql> EXPLAIN SELECT * FROM t1 WHERE t1.a > (SELECT COUNT(a) FROM t2)\G
*************************** 1\. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2\. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 33.33
        Extra: Using where; Using join buffer (hash join)
*************************** 3\. row ***************************
           id: 2
  select_type: DERIVED
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: NULL

      从第二个EXPLAIN语句后立即执行SHOW WARNINGS可以看出，在启用优化的情况下，查询SELECT * FROM t1 WHERE t1.a > (SELECT COUNT(a) FROM t2)被重写成类似于这里显示的形式：

      SELECT t1.a FROM t1
    JOIN  ( SELECT COUNT(t2.a) AS c FROM t2 ) AS d
            WHERE t1.a > d.c;

      示例，使用带有IN (*子查询*)的查询：

      mysql> DROP TABLE IF EXISTS t1, t2;

mysql> CREATE TABLE t1 (a INT, b INT);
mysql> CREATE TABLE t2 (a INT, b INT);

mysql> INSERT INTO t1 VALUES ROW(1,10), ROW(2,20), ROW(3,30);
mysql> INSERT INTO t2
 ->    VALUES ROW(1,10), ROW(2,20), ROW(3,30), ROW(1,110), ROW(2,120), ROW(3,130);

mysql> SELECT * FROM t1
 ->     WHERE   t1.b < 0
 ->             OR
 ->             t1.a IN (SELECT t2.a + 1 FROM t2);
+------+------+
| a    | b    |
+------+------+
|    2 |   20 |
|    3 |   30 |
+------+------+

mysql> SET @@optimizer_switch="subquery_to_derived=off";

mysql> EXPLAIN SELECT * FROM t1
 ->             WHERE   t1.b < 0
 ->                     OR
 ->                     t1.a IN (SELECT t2.a + 1 FROM t2)\G
*************************** 1\. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where
*************************** 2\. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using where 
mysql> SET @@optimizer_switch="subquery_to_derived=on";

mysql> EXPLAIN SELECT * FROM t1
 ->             WHERE   t1.b < 0
 ->                     OR
 ->                     t1.a IN (SELECT t2.a + 1 FROM t2)\G
*************************** 1\. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: NULL
*************************** 2\. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ref
possible_keys: <auto_key0>
          key: <auto_key0>
      key_len: 9
          ref: std2.t1.a
         rows: 2
     filtered: 100.00
        Extra: Using where; Using index
*************************** 3\. row ***************************
           id: 2
  select_type: DERIVED
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using temporary

      在这个查询上执行EXPLAIN后，检查并简化SHOW WARNINGS的结果显示，当启用subquery_to_derived标志时，SELECT * FROM t1 WHERE t1.b < 0 OR t1.a IN (SELECT t2.a + 1 FROM t2)被重写成类似于这里显示的形式：

      SELECT a, b FROM t1
    LEFT JOIN (SELECT DISTINCT a + 1 AS e FROM t2) d
    ON t1.a = d.e
    WHERE   t1.b < 0
            OR
            d.e IS NOT NULL;

      示例，使用带有EXISTS (*子查询*)的查询，并与前一个示例中相同的表和数据：

      mysql> SELECT * FROM t1
 ->     WHERE   t1.b < 0
 ->             OR
 ->             EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1);
+------+------+
| a    | b    |
+------+------+
|    1 |   10 |
|    2 |   20 |
+------+------+

mysql> SET @@optimizer_switch="subquery_to_derived=off";

mysql> EXPLAIN SELECT * FROM t1
 ->             WHERE   t1.b < 0
 ->                     OR
 ->                     EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1)\G
*************************** 1\. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where
*************************** 2\. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 16.67
        Extra: Using where 
mysql> SET @@optimizer_switch="subquery_to_derived=on";

mysql> EXPLAIN SELECT * FROM t1
 ->             WHERE   t1.b < 0
 ->                     OR
 ->                     EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1)\G
*************************** 1\. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: NULL
*************************** 2\. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using where; Using join buffer (hash join)
*************************** 3\. row ***************************
           id: 2
  select_type: DERIVED
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using temporary

      如果我们在subquery_to_derived已启用的情况下，在查询SELECT * FROM t1 WHERE t1.b < 0 OR EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1)上运行EXPLAIN后执行SHOW WARNINGS并简化结果的第二行，我们会看到它被重写成类似于这里显示的形式：

      SELECT a, b FROM t1
LEFT JOIN (SELECT DISTINCT 1 AS e1, t2.a AS e2 FROM t2) d
ON t1.a + 1 = d.e2
WHERE   t1.b < 0
        OR
        d.e1 IS NOT NULL;

      欲了解更多信息，请参阅 Section 10.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization”，以及 Section 10.2.1.19, “LIMIT Query Optimization”，和 Section 10.2.2.1, “Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations”。

当您为optimizer_switch分配一个值时，未提及的标志保持其当前值。这使得可以在单个语句中启用或禁用特定的优化器行为，而不影响其他行为。该语句不依赖于其他优化器标志的存在及其值是什么。假设所有索引合并优化都已启用：

mysql> SELECT @@optimizer_switch\G
*************************** 1\. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
                    index_merge_sort_union=on,index_merge_intersection=on,
                    engine_condition_pushdown=on,index_condition_pushdown=on,
                    mrr=on,mrr_cost_based=on,block_nested_loop=on,
                    batched_key_access=off,materialization=on,semijoin=on,
                    loosescan=on, firstmatch=on,
                    subquery_materialization_cost_based=on,
                    use_index_extensions=on,condition_fanout_filter=on,
                    derived_merge=on,use_invisible_indexes=off,skip_scan=on,
                    hash_join=on,subquery_to_derived=off,
                    prefer_ordering_index=on

如果服务器对某些查询使用了索引合并联合或索引合并排序-联合访问方法，并且您想检查优化器在没有它们的情况下是否可以表现更好，请像这样设置变量值：

mysql> SET optimizer_switch='index_merge_union=off,index_merge_sort_union=off';

mysql> SELECT @@optimizer_switch\G
*************************** 1\. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=off,
                    index_merge_sort_union=off,index_merge_intersection=on,
                    engine_condition_pushdown=on,index_condition_pushdown=on,
                    mrr=on,mrr_cost_based=on,block_nested_loop=on,
                    batched_key_access=off,materialization=on,semijoin=on,
                    loosescan=on, firstmatch=on,
                    subquery_materialization_cost_based=on,
                    use_index_extensions=on,condition_fanout_filter=on,
                    derived_merge=on,use_invisible_indexes=off,skip_scan=on,
                    hash_join=on,subquery_to_derived=off,
                    prefer_ordering_index=on

10.9.3 优化提示

原文：dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html

控制优化器策略的一种方法是设置optimizer_switch系统变量（参见第 10.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);

此处描述的优化提示与第 10.9.4 节，“索引提示”中描述的索引提示不同。优化提示和索引提示可以单独使用或一起使用。

  • 优化提示概述

  • 优化提示语法

  • 连接顺序优化提示

  • 表级别优化提示

  • 索引级别优化提示

  • 子查询优化提示

  • 语句执行时间优化提示

  • 变量设置提示语法

  • 资源组提示语法

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

优化提示概述

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

  • 全局: 提示影响整个语句

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

  • 表级别: 提示影响查询块内的特定表

  • 索引级别: 提示影响表内的特定索引

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

表 10.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使用或忽略指定的索引或索引进行GROUP BY操作中的索引扫描（MySQL 8.0.20 中添加）索引
HASH_JOIN, NO_HASH_JOIN影响哈希连接优化（仅适用于 MySQL 8.0.18）查询块，表
INDEX, 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使用 FROM 子句中指定的表顺序作为连接顺序查询块
JOIN_INDEX, NO_JOIN_INDEX使用或忽略指定的索引或索引以供任何访问方法使用（MySQL 8.0.20 中添加）索引
JOIN_ORDER使用提示中指定的表顺序作为连接顺序查询块
JOIN_PREFIX使用提示中指定的表顺序作为连接顺序的第一个表查询块
JOIN_SUFFIX使用提示中指定的表顺序作为连接顺序的最后表查询块
MAX_EXECUTION_TIME限制语句执行时间全局
MERGE, 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影响半连接策略；从 MySQL 8.0.17 开始，这也适用于反连接查询块
SKIP_SCAN, NO_SKIP_SCAN影响跳过扫描优化表，索引
SET_VAR在语句执行期间设置变量全局
SUBQUERY影响物化，IN-to-EXISTS子查询策略查询块
提示名称描述适用范围

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

优化器提示语法

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

查询块名称是标识符，遵循关于哪些名称有效以及如何引用它们的通常规则（参见第 11.2 节，“模式对象名称”）。

提示名称、查询块名称和策略名称不区分大小写。表和索引名称的引用遵循通常的标识符大小写敏感性规则（参见第 11.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_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) */ ... 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）连接处理算法（参见第 10.2.1.12 节，“块嵌套循环和批量键访问连接”）。

  • 衍生表、视图引用或公共表达式是否应合并到外部查询块中，或者使用内部临时表进行材料化。

  • 使用衍生表条件下推优化（在 MySQL 8.0.22 中添加）。请参见第 10.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：允许使用这些提示名称：

    • BKA, NO_BKA：启用或禁用指定表的批量键访问。

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

      注意

      MySQL 8.0.20 及更高版本中移除了块嵌套循环优化，但仍支持BNL和NO_BNL以启用和禁用哈希连接。

    • DERIVED_CONDITION_PUSHDOWN, NO_DERIVED_CONDITION_PUSHDOWN：启用或禁用指定表的衍生表条件下推（在 MySQL 8.0.22 中添加）。有关更多信息，请参见第 10.2.2.5 节，“衍生条件下推优化”。

    • HASH_JOIN, NO_HASH_JOIN：仅在 MySQL 8.0.18 中启用或禁用指定表的哈希连接。在 MySQL 8.0.19 或更高版本中，这些提示无效，应改用BNL或NO_BNL。

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

对于MERGE和NO_MERGE提示，适用以下优先规则：

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

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

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

索引级优化器提示

索引级提示影响优化器为特定表或索引使用哪些索引处理策略。这些提示类型影响索引条件下推（ICP）、多范围读取（MRR）、索引合并和范围优化的使用（参见第 10.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：为GROUP BY操作启用或禁用指定索引或索引的索引扫描。等同于索引提示FORCE INDEX FOR GROUP BY, IGNORE INDEX FOR GROUP BY。从 MySQL 8.0.20 及更高版本提供。

    • INDEX, NO_INDEX：作为JOIN_INDEX, GROUP_INDEX, 和 ORDER_INDEX的组合，强制服务器使用指定的索引或索引来处理任何范围，或作为NO_JOIN_INDEX, NO_GROUP_INDEX, 和 NO_ORDER_INDEX的组合，导致服务器忽略指定的索引或索引用于任何范围。等同于FORCE INDEX, IGNORE INDEX。从 MySQL 8.0.20 开始提供。

    • INDEX_MERGE, NO_INDEX_MERGE：启用或禁用指定表或索引的索引合并访问方法。有关此访问方法的信息，请参见 Section 10.2.1.3, “Index Merge Optimization”。这些提示适用于所有三种索引合并算法。

      INDEX_MERGE提示强制优化器使用指定表的指定索引集进行索引合并。如果未指定索引，则优化器会考虑所有可能的索引组合并选择最经济的一个。如果索引组合不适用于给定的语句，则可能会忽略此提示。

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

    • JOIN_INDEX, NO_JOIN_INDEX: 强制 MySQL 使用或忽略指定的索引或索引，用于任何访问方法，如ref、range、index_merge等。相当于FORCE INDEX FOR JOIN、IGNORE INDEX FOR JOIN。从 MySQL 8.0.20 版本开始提供。

    • MRR, NO_MRR: 启用或禁用指定表或索引的 MRR。MRR 提示仅适用于InnoDB和MyISAM表。有关此访问方法的信息，请参见 Section 10.2.1.11, “Multi-Range Read Optimization”。

    • NO_ICP: 禁用指定表或索引的 ICP。默认情况下，ICP 是一种候选优化策略，因此没有启用它的提示。有关此访问方法的信息，请参见 Section 10.2.1.6, “Index Condition Pushdown Optimization”。

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

      当范围数量可能很高且范围优化需要大量资源时，此提示可能会有用。

    • ORDER_INDEX, NO_ORDER_INDEX: 导致 MySQL 使用或忽略指定的索引或索引来对行进行排序。相当于FORCE INDEX FOR ORDER BY、IGNORE INDEX FOR ORDER BY。从 MySQL 8.0.20 版本开始提供。

    • 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_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 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；并且在a、b和c上分别存在名为i_a、i_b和i_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）适用。不使用索引合并。

  • 索引级别的优化提示 GROUP_INDEX、INDEX、JOIN_INDEX 和 ORDER_INDEX 都优先于等效的 FORCE INDEX 提示；也就是说，它们导致 FORCE INDEX 提示被忽略。同样，NO_GROUP_INDEX、NO_INDEX、NO_JOIN_INDEX 和 NO_ORDER_INDEX 提示都优先于任何 IGNORE INDEX 等效项，也导致它们被忽略。

    索引级别的优化提示 GROUP_INDEX、NO_GROUP_INDEX、INDEX、NO_INDEX、JOIN_INDEX、NO_JOIN_INDEX、ORDER_INDEX 和 NO_ORDER_INDEX 提示都优先于所有其他优化提示，包括其他索引级别的优化提示。任何其他优化提示仅适用于这些提示允许的索引。

    GROUP_INDEX、INDEX、JOIN_INDEX 和 ORDER_INDEX 提示都等同于 FORCE INDEX，而不是 USE INDEX。这是因为使用这些提示中的一个或多个意味着只有在无法使用命名的索引来查找表中的行时才会使用表扫描。要使 MySQL 使用与给定的 USE INDEX 实例相同的索引或索引集，可以使用 NO_INDEX、NO_JOIN_INDEX、NO_GROUP_INDEX、NO_ORDER_INDEX 或这些提示的某种组合。

    要复制查询 SELECT a,c FROM t1 USE INDEX FOR ORDER BY (i_a) ORDER BY a 中 USE INDEX 的效果，可以使用 NO_ORDER_INDEX 优化提示来覆盖表上除所需索引之外的所有索引，如下所示：

    SELECT /*+ NO_ORDER_INDEX(t1 i_b,i_c) */ a,c
    FROM t1
    ORDER BY a;

    尝试将整个表的 NO_ORDER_INDEX 与 USE INDEX FOR ORDER BY 结合起来不起作用，因为 NO_ORDER_BY 导致 USE INDEX 被忽略，如下所示：

    mysql> EXPLAIN SELECT /*+ NO_ORDER_INDEX(t1) */ a,c FROM t1
 ->     USE INDEX FOR ORDER BY (i_a) 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
        Extra: Using filesort

  • 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

    由于 FORCE INDEX，i_a, i_b 不允许进行索引合并，但是优化器被强制使用 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到EXISTS转换。有关这些优化的更多信息，请参见第 10.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到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 ...

MAX_EXECUTION_TIME(*N*)提示设置了*N毫秒的语句执行超时。如果此选项不存在或N*为 0，则由max_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 一起使用。各个系统变量描述指示每个变量是否可提示；请参阅 第 7.1.8 节，“服务器系统变量”。您还可以通过尝试在 SET_VAR 中使用它来在运行时检查系统变量。如果变量不可提示，则会发出警告：

mysql> SELECT /*+ SET_VAR(collation_server = 'utf8mb4') */ 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_based 可以取值 on 或 off，因此尝试将其设置为 yes 是不正确的。在每种情况下，提示都会被忽略并显示警告。

SET_VAR 提示仅在语句级别允许。如果在子查询中使用，提示将被忽略并显示警告。

复制品会忽略复制语句中的 SET_VAR 提示，以避免安全问题的潜在风险。

资源组提示语法

RESOURCE_GROUP 优化提示用于资源组管理（参见第 7.1.16 节，“资源组”）。此提示将执行语句的线程暂时分配给命名的资源组（在语句执行期间）。它需要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 语句，它将线程非暂时地分配给资源组。参见第 15.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) 适用于查询块 qb1 中的表 t1。

  • BKA(@qb2) 适用于查询块 qb2。

  • NO_MRR(@qb3 t1 idx1, id2) 适用于查询块 qb3 中表 t1 中的索引 idx1 和 idx2。

查询块名称是标识符，并遵循关于名称有效性和如何引用它们的通常规则（参见第 11.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") */ ...) ...

10.9.4 索引提示

原文：dev.mysql.com/doc/refman/8.0/en/index-hints.html

索引提示为优化器提供有关在查询处理期间如何选择索引的信息。这里描述的索引提示与第 10.9.3 节“优化器提示”中描述的优化器提示不同。索引提示和优化器提示可以单独或一起使用。

索引提示适用于SELECT和UPDATE语句。它们还适用于多表DELETE语句，但不适用于单表DELETE，如本节后面所示。

索引提示在表名后指定。（有关在SELECT语句中指定表的一般语法，请参见第 15.2.13.2 节“JOIN 子句”。）指定单个表的语法，包括索引提示，如下所示：

*tbl_name* [[AS] *alias*] [*index_hint_list*]

*index_hint_list*:
    *index_hint* [*index_hint*] ...

*index_hint*:
    USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([*index_list*])
  | {IGNORE|FORCE} {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (*index_list*)

*index_list*:
    *index_name* [, *index_name*] ...

USE INDEX (*index_list*)提示告诉 MySQL 仅使用命名索引之一来查找表中的行。另一种语法IGNORE INDEX (*index_list*)告诉 MySQL 不使用某些特定的索引。如果EXPLAIN显示 MySQL 正在使用列表中错误的索引，则这些提示���有用。

FORCE INDEX提示类似于USE INDEX (*index_list*)，但额外假定表扫描是非常昂贵的。换句话说，只有在无法使用命名索引之一来查找表中的行时，才会使用表扫描。

注意

截至 MySQL 8.0.20，服务器支持索引级别的优化器提示JOIN_INDEX、GROUP_INDEX、ORDER_INDEX和INDEX，它们相当于并打算取代FORCE INDEX索引提示，以及NO_JOIN_INDEX、NO_GROUP_INDEX、NO_ORDER_INDEX和NO_INDEX优化器提示，它们相当于并打算取代IGNORE INDEX索引提示。因此，您应该期望USE INDEX、FORCE INDEX和IGNORE INDEX在将来的 MySQL 版本中被弃用，并在此后的某个时间被完全移除。

这些索引级优化器提示支持单表和多表DELETE语句。

更多信息，请参见 Index-Level Optimizer Hints。

每个提示都需要索引名称，而不是列名称。要引用主键，请使用名称PRIMARY。要查看表的索引名称，请使用SHOW INDEX语句或 Information Schema STATISTICS表。

*index_name*值不必是完整的索引名称。它可以是索引名称的明确前缀。如果前缀不明确，则会出现错误。

示例：

SELECT * FROM table1 USE INDEX (col1_index,col2_index)
  WHERE col1=1 AND col2=2 AND col3=3;

SELECT * FROM table1 IGNORE INDEX (col3_index)
  WHERE col1=1 AND col2=2 AND col3=3;

索引提示的语法具有以下特点：

  • 对于USE INDEX，省略*index_list在语法上是有效的，这意味着“不使用索引”。对于FORCE INDEX或IGNORE INDEX省略index_list*是语法错误。

  • 通过在提示中添加FOR子句，您可以指定索引提示的范围。这可以更精细地控制优化器在查询处理的各个阶段选择执行计划。要仅影响 MySQL 在决定如何在表中查找行以及如何处理连接时使用的索引，请使用FOR JOIN。要影响用于对行进行排序或分组的索引使用，请使用FOR ORDER BY或FOR GROUP BY。

  • 您可以指定多个索引提示：

    SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX FOR ORDER BY (i2) ORDER BY a;

    在几个提示中命名相同的索引不是错误的（即使在同一个提示中）：

    SELECT * FROM t1 USE INDEX (i1) USE INDEX (i1,i1);

    但是，对于同一表混合使用USE INDEX和FORCE INDEX是错误的：

    SELECT * FROM t1 USE INDEX FOR JOIN (i1) FORCE INDEX FOR JOIN (i2);

如果索引提示不包含FOR子句，则提示的范围是应用于语句的所有部分。例如，这个提示：

IGNORE INDEX (i1)

等同于以下提示的组合：

IGNORE INDEX FOR JOIN (i1)
IGNORE INDEX FOR ORDER BY (i1)
IGNORE INDEX FOR GROUP BY (i1)

在 MySQL 5.0 中，没有FOR子句的提示范围仅适用于行检索。要在没有FOR子句的情况下使服务器使用这种较旧的行为，请在服务器启动时启用old系统变量。在复制设置中启用此变量时要小心。使用基于语句的二进制日志记录，源和副本之间具有不同模式可能会导致复制错误。

处理索引提示时，它们按类型（USE，FORCE，IGNORE）和范围（FOR JOIN，FOR ORDER BY，FOR GROUP BY）被收集到单个列表中。例如：

SELECT * FROM t1
  USE INDEX () IGNORE INDEX (i2) USE INDEX (i1) USE INDEX (i2);

等同于：

SELECT * FROM t1
   USE INDEX (i1,i2) IGNORE INDEX (i2);

然后，索引提示按以下顺序应用于每个范围：

  1. 如果存在，则应用{USE|FORCE} INDEX。（如果不存在，则使用优化器确定的索引集。）

  2. IGNORE INDEX应用于上一步的结果。例如，以下两个查询是等效的：

    SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX (i2) USE INDEX (i2);

SELECT * FROM t1 USE INDEX (i1);

对于FULLTEXT搜索，索引提示的工作方式如下：

  • 对于自然语言模式搜索，索引提示会被静默忽略。例如，IGNORE INDEX(i1)会被忽略而不会有警告，索引仍然会被使用。

  • 对于布尔模式搜索，带有FOR ORDER BY或FOR GROUP BY的索引提示会被静默忽略。带有FOR JOIN或没有FOR修饰符的索引提示会被应用。与非FULLTEXT搜索的提示应用方式相反，该提示用于查询执行的所有阶段（查找行和检索、分组和排序）。即使为非FULLTEXT索引提供提示，也是如此。

    例如，以下两个查询是等效的：

    SELECT * FROM t
  USE INDEX (index1)
  IGNORE INDEX FOR ORDER BY (index1)
  IGNORE INDEX FOR GROUP BY (index1)
  WHERE ... IN BOOLEAN MODE ... ;

SELECT * FROM t
  USE INDEX (index1)
  WHERE ... IN BOOLEAN MODE ... ;

索引提示适用于DELETE语句，但仅当您使用多表DELETE语法时，如下所示：

mysql> EXPLAIN DELETE FROM t1 USE INDEX(col2) 
 -> WHERE col1 BETWEEN 1 AND 100 AND COL2 BETWEEN 1 AND 100\G
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'use
index(col2) where col1 between 1 and 100 and col2 between 1 and 100' at line 1 
mysql> EXPLAIN DELETE t1.* FROM t1 USE INDEX(col2) 
 -> WHERE col1 BETWEEN 1 AND 100 AND COL2 BETWEEN 1 AND 100\G
*************************** 1\. row ***************************
           id: 1
  select_type: DELETE
        table: t1
   partitions: NULL
         type: range
possible_keys: col2
          key: col2
      key_len: 5
          ref: NULL
         rows: 72
     filtered: 11.11
        Extra: Using where 1 row in set, 1 warning (0.00 sec)

10.9.5 优化器成本模型

原文：dev.mysql.com/doc/refman/8.0/en/cost-model.html

为生成执行计划，优化器使用基于查询执行过程中各种操作成本的估算的成本模型。优化器具有一组编译默认的“成本常量”可供其在决定执行计划时使用。

优化器还具有成本估算数据库，用于执行计划构建过程中使用。这些估算存储在mysql系统数据库的server_cost和engine_cost表中，并且可以随时进行配置。这些表的目的是使得优化器在尝试生成查询执行计划时能够轻松调整使用的成本估算。

  • 成本模型一般操作

  • 成本模型数据库

  • 对成本模型数据库进行更改

成本模型一般操作

可配置的优化器成本模型工作方式如下：

  • 服务器在启动时将成本模型表读入内存，并在运行时使用内存中的值。表中指定的任何非NULL成本估算优先于相应的编译默认成本常量。任何NULL估算指示优化器使用编译默认值。

  • 在运行时，服务器可能会重新读取成本表。当动态加载存储引擎或执行FLUSH OPTIMIZER_COSTS语句时会发生这种情况。

  • 成本表使服务器管理员可以通过更改表中的条目轻松调整成本估算。通过将条目的成本设置为NULL可以轻松恢复默认值。优化器使用内存中的成本值，因此更改表后应跟随FLUSH OPTIMIZER_COSTS以生效。

  • 当客户端会话开始时，内存中的成本估算值将贯穿整个会话直至结束。特别是，如果服务器重新读取成本表，则任何更改的估算仅适用于随后启动的会话。现有会话不受影响。

  • 成本表特定于给定的服务器实例。服务器不会将成本表更改复制到副本。

成本模型数据库

优化器成本模型数据库包括mysql系统数据库中的两个表，这些表包含查询执行过程中发生的操作的成本估算信息：

  • server_cost：一般服务器操作的优化器成本估算

  • engine_cost：特定存储引擎操作的优化器成本估算

server_cost表包含以下列：

  • cost_name

    用于成本模型的成本估算名称。名称不区分大小写。如果服务器在读取此表时无法识别成本名称，则会将警告写入错误日志。

  • cost_value

    成本估算值。如果值为非NULL，服务器将其用作成本。否则，它使用默认估算值（编译值）。DBA 可以通过更新此列来更改成本估算。如果服务器在读取此表时发现成本值无效（非正数），则会将警告写入错误日志。

    要覆盖默认成本估算（对于指定NULL的条目），请将成本设置为非NULL值。要恢复默认值，请将值设置为NULL。然后执行FLUSH OPTIMIZER_COSTS以告诉服务器重新读取成本表。

  • last_update

    最后一次行更新的时间。

  • comment

    与成本估算相关联的描述性注释。DBA 可以使用此列提供有关为什么成本估算行存储特定值的信息。

  • default_value

    成本估算的默认（编译内）值。此列是只读生成的列，即使关联的成本估算更改，它也保留其值。对于在运行时添加到表中的行，此列的值为NULL。

server_cost表的主键是cost_name列，因此不可能为任何成本估算创建多个条目。

服务器识别server_cost表的这些cost_name值：

  • disk_temptable_create_cost，disk_temptable_row_cost

    存储在基于磁盘的存储引擎（InnoDB或MyISAM）中的内部创建临时表的成本估算。增加这些值会增加使用内部临时表的成本估算，并使优化器更倾向于使用较少的查询计划。有关此类表的信息，请参见 Section 10.4.4, “MySQL 中的内部临时表使用”。

    与相应内存参数（memory_temptable_create_cost，memory_temptable_row_cost）的默认值相比，这些磁盘参数的默认值较大，反映了处理基于磁盘的表的更高成本。

  • key_compare_cost

    比较记录键的成本。增加此值会导致比较许多键的查询计划变得更昂贵。例如，执行filesort的查询计划相对于通过使用索引避免排序的查询计划变得更昂贵。

  • memory_temptable_create_cost，memory_temptable_row_cost

    存储在MEMORY存储引擎中的内部创建临时表的成本估算。增加这些值会增加使用内部临时表的成本估算，并使优化器更倾向于使用较少的查询计划。有关这些表的信息，请参见 Section 10.4.4, “MySQL 中的内部临时表使用”。

    与相应磁盘参数的默认值（disk_temptable_create_cost，disk_temptable_row_cost）相比，这些内存参数的默认值较小，反映了处理基于内存的表的较低成本。

  • row_evaluate_cost

    评估记录条件的成本。增加这个值会导致查询计划检查许多行变得比检查较少行的查询计划更昂贵。例如，与读取较少行的范围扫描相比，表扫描变得相对更昂贵。

engine_cost表包含以下列：

  • engine_name

    适用于此成本估算的存储引擎的名称。名称不区分大小写。如果值为default，则适用于所有没有自己命名条目的存储引擎。如果服务器在读取此表时不识别引擎名称，则会将警告写入错误日志。

  • device_type

    适用于此成本估算的设备类型。该列用于指定不同存储设备类型（例如硬盘驱动器与固态驱动器）的不同成本估算。目前，此信息未被使用，0 是唯一允许的值。

  • cost_name

    与server_cost表中相同。

  • cost_value

    与server_cost表中相同。

  • last_update

    与server_cost表中相同。

  • 注释

    与server_cost表中相同。

  • default_value

    成本估算的默认（编译内）值。此列是一个只读生成的列，即使相关的成本估算发生变化，它也会保留其值。对于在运行时添加到表中的行，此列的值为NULL，但有一个例外，即如果该行具有与原始行之一相同的cost_name值，则default_value列的值与该行相同。

engine_cost表的主键是由(cost_name, engine_name, device_type)列组成的元组，因此不可能为这些列中的任何值组合创建多个条目。

服务器识别engine_cost表中的这些cost_name值：

  • io_block_read_cost

    从磁盘读取索引或数据块的成本。增加这个值会导致读取许多磁盘块的查询计划变得比读取较少磁盘块的查询计划更昂贵。例如，与读取较少块的范围扫描相比，表扫描变得相对更昂贵。

  • memory_block_read_cost

    类似于io_block_read_cost，但表示从内存数据库缓冲区读取索引或数据块的成本。

如果io_block_read_cost和memory_block_read_cost的值不同，同一查询的两次运行之间可能会导致执行计划的变化。假设内存访问的成本低于磁盘访问的成本。在服务器启动时，数据尚未读入缓冲池之前，可能会得到不同的计划，而在查询运行后，数据已经在内存中。

更改成本模型数据库

对于希望从默认值更改成本模型参数的数据库管理员，尝试将值加倍或减半，并测量效果。

更改io_block_read_cost和memory_block_read_cost参数最有可能产生有价值的结果。这些参数值使得数据访问方法的成本模型能够考虑从不同来源读取信息的成本；也就是说，从磁盘读取信息的成本与从内存缓冲区中读取信息的成本。例如，其他条件相同的情况下，将io_block_read_cost设置为大于memory_block_read_cost的值会导致优化器更倾向于选择已经保存在内存中的信息而不是需要从磁盘读取的查询计划。

这个示例展示了如何更改io_block_read_cost的默认值：

UPDATE mysql.engine_cost
  SET cost_value = 2.0
  WHERE cost_name = 'io_block_read_cost';
FLUSH OPTIMIZER_COSTS;

这个示例展示了如何仅为InnoDB存储引擎更改io_block_read_cost的值：

INSERT INTO mysql.engine_cost
  VALUES ('InnoDB', 0, 'io_block_read_cost', 3.0,
  CURRENT_TIMESTAMP, 'Using a slower disk for InnoDB');
FLUSH OPTIMIZER_COSTS;

10.9.6 优化器统计

原文：dev.mysql.com/doc/refman/8.0/en/optimizer-statistics.html

column_statistics数据字典表存储关于列值的直方图统计信息，供优化器在构建查询执行计划时使用。要执行直方图管理，请使用ANALYZE TABLE语句。

column_statistics表具有以下特征：

  • 该表包含所有数据类型的列的统计信息，除了几何类型（空间数据）和JSON。

  • 该表是持久的，因此每次服务器启动时不需要创建列统计信息。

  • 服务器对表执行更新；用户不执行。

column_statistics表不直接可被用户访问，因为它是数据字典的一部分。直方图信息可通过INFORMATION_SCHEMA.COLUMN_STATISTICS获得，它被实现为数据字典表上的视图。COLUMN_STATISTICS包含以下列：

  • SCHEMA_NAME、TABLE_NAME、COLUMN_NAME：适用于统计的模式、表和列的名称。

  • HISTOGRAM：描述以直方图形式存储的列统计信息的JSON值。

列直方图包含存储在列中值范围的部分的桶。直方图是JSON对象，以允许在列统计的表示中灵活性。这是一个示例直方图对象：

{
  "buckets": [
    [
      1,
      0.3333333333333333
    ],
    [
      2,
      0.6666666666666666
    ],
    [
      3,
      1
    ]
  ],
  "null-values": 0,
  "last-updated": "2017-03-24 13:32:40.000000",
  "sampling-rate": 1,
  "histogram-type": "singleton",
  "number-of-buckets-specified": 128,
  "data-type": "int",
  "collation-id": 8
}

直方图对象具有以下键：

  • buckets：直方图桶。桶结构取决于直方图类型。

    对于singleton直方图，桶包含两个值：

    • 值 1：桶的值。类型取决于列数据类型。

    • 值 2：表示该值的累积频率的双精度值。例如，.25 和.75 表示列中值的 25%和 75%小于或等于桶值。

    对于equi-height直方图，桶包含四个值：

    • 值 1、2：桶的下限和上限值（包括）。类型取决于列数据类型。

    • 值 3：表示该值的累积频率的双精度值。例如，.25 和.75 表示列中值的 25%和 75%小于或等于桶的上限值。

    • 值 4：从桶下限值到上限值的范围内的不同值的数量。

  • null-values：介于 0.0 和 1.0 之间的数字，表示列值中为 SQL NULL值的比例。如果为 0，则列不包含NULL值。

  • last-updated：生成直方图的时间，以*YYYY-MM-DD hh:mm:ss.uuuuuu*格式的 UTC 值。

  • sampling-rate: 介于 0.0 和 1.0 之间的数字，表示用于创建直方图的数据比例。值为 1 表示读取了所有数据（无抽样）。

  • histogram-type: 直方图类型：

    • singleton: 一个桶代表列中的一个单个值。当列中不同数值的数量小于或等于ANALYZE TABLE语句生成直方图时指定的桶数时，将创建此直方图类型。

    • equi-height: 一个桶代表一系列数值。当列中不同数值的数量大于ANALYZE TABLE语句生成直方图时指定的桶数时，将创建此直方图类型。

  • number-of-buckets-specified: ANALYZE TABLE语句生成直方图时指定的桶数。

  • data-type: 此直方图包含的数据类型。在将直方图从持久存储读取和解析到内存时需要。其值为int、uint（无符号整数）、double、decimal、datetime或string（包括字符和二进制字符串）之一。

  • collation-id: 直方图数据的排序规则 ID。当data-type值为string时，这个值大多有意义。这些值对应于信息模式COLLATIONS表中的ID列值。

要从直方图对象中提取特定值，可以使用JSON操作。例如：

mysql> SELECT
         TABLE_NAME, COLUMN_NAME,
         HISTOGRAM->>'$."data-type"' AS 'data-type',
         JSON_LENGTH(HISTOGRAM->>'$."buckets"') AS 'bucket-count'
       FROM INFORMATION_SCHEMA.COLUMN_STATISTICS;
+-----------------+-------------+-----------+--------------+
| TABLE_NAME      | COLUMN_NAME | data-type | bucket-count |
+-----------------+-------------+-----------+--------------+
| country         | Population  | int       |          226 |
| city            | Population  | int       |         1024 |
| countrylanguage | Language    | string    |          457 |
+-----------------+-------------+-----------+--------------+

优化器使用直方图统计信息（如果适用）来处理收集了统计信息的任何数据类型的列。优化器应用直方图统计信息来根据列值与常量值的比较的选择性（过滤效果）确定行估计。以下形式的谓词符合直方图使用的条件：

*col_name* = *constant*
*col_name* <> *constant*
*col_name* != *constant*
*col_name* > *constant*
*col_name* < *constant*
*col_name* >= *constant*
*col_name* <= *constant*
*col_name* IS NULL
*col_name* IS NOT NULL
*col_name* BETWEEN *constant* AND *constant*
*col_name* NOT BETWEEN *constant* AND *constant*
*col_name* IN (*constant*[, *constant*] ...)
*col_name* NOT IN (*constant*[, *constant*] ...)

例如，以下语句包含符合直方图使用条件的谓词：

SELECT * FROM orders WHERE amount BETWEEN 100.0 AND 300.0;
SELECT * FROM tbl WHERE col1 = 15 AND col2 > 100;

对常量值的比较要求包括常量函数，如ABS()和FLOOR()：

SELECT * FROM tbl WHERE col1 < ABS(-34);

直方图统计信息主要适用于非索引列。为适用直方图统计信息的列添加索引也可能帮助优化器进行行估计。权衡如下：

  • 当表数据被修改时，索引必须更新。

  • 直方图仅在需要时创建或更新，因此在修改表数据时不会增加额外开销。另一方面，当表发生修改时，统计信息会逐渐变得过时，直到下次更新为止。

优化器更喜欢使用范围优化器的行估计值，而不是直方图统计数据。如果优化器确定范围优化器适用，则不使用直方图统计数据。

对于已建立索引的列，可以通过索引潜入来获取相等比较的行估计值（参见 Section 10.2.1.2, “Range Optimization”）。在这种情况下，直方图统计数据未必有用，因为索引潜入可以提供更好的估计值。

在某些情况下，使用直方图统计数据可能不会改善查询执行（例如，如果统计数据已过时）。要检查是否是这种情况，请使用ANALYZE TABLE重新生成直方图统计数据，然后再次运行查询。

或者，要禁用直方图统计数据，可以使用ANALYZE TABLE来删除它们。另一种禁用直方图统计数据的方法是关闭optimizer_switch系统变量的condition_fanout_filter标志（尽管这可能会禁用其他优化）：

SET optimizer_switch='condition_fanout_filter=off';

如果使用直方图统计数据，可以通过EXPLAIN查看结果。考虑以下查询，其中col1列没有可用的索引：

SELECT * FROM t1 WHERE col1 < 24;

如果直方图统计数据表明t1中有 57%的行满足col1 < 24的条件，即使没有索引，也可以进行过滤，并且EXPLAIN中的filtered列显示 57.00。

avatar
文章
阅读
粉丝