MySQL8 中文参考（三十七）

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

原文：dev.mysql.com/doc/refman/8.0/en/function-optimization.html

10.2.1.20 函数调用优化

MySQL 函数在内部被标记为确定性或非确定性。如果给定其参数的固定值，函数在不同调用中可以返回不同结果，则该函数是非确定性的。非确定性函数的示例：RAND()，UUID()。

如果一个函数被标记为非确定性，在WHERE子句中对其进行引用会对每一行（从一个表中选择）或每一行组合（从多表连接中选择）进行评估。

MySQL 还根据参数类型确定何时评估函数，参数是表列还是常量值。将表列作为参数的确定性函数必须在该列更改值时进行评估。

非确定性函数可能会影响查询性能。例如，某些优化可能不可用，或者可能需要更多的锁定。以下讨论使用RAND()，但也适用于其他非确定性函数。

假设表t有以下定义：

CREATE TABLE t (id INT NOT NULL PRIMARY KEY, col_a VARCHAR(100));

考虑以下两个查询：

SELECT * FROM t WHERE id = POW(1,2);
SELECT * FROM t WHERE id = FLOOR(1 + RAND() * 49);

由于对主键的等式比较，这两个查询似乎都使用主键查找，但这只对第一个查询是真的：

• 第一个查询始终最多生成一行，因为具有常量参数的POW()是一个常量值，并用于索引查找。

• 第二个查询包含一个使用非确定性函数RAND()的表达式，该函数在查询中不是常量，而是实际上对表t的每一行都有一个新值。因此，查询读取表的每一行，为每一行评估谓词，并输出所有主键与随机值匹配的行。这可能是零行、一行或多行，取决于id列值和RAND()序列中的值。

非确定性的影响不仅限于SELECT语句。此UPDATE语句使用非确定性函数选择要修改的行：

UPDATE t SET col_a = *some_expr* WHERE id = FLOOR(1 + RAND() * 49);

大概意图是更新与表达式匹配的主键最多一行。但是，根据id列值和RAND()序列中的值，可能会更新零行、一行或多行。

刚才描述的行为对性能和复制有影响：

• 因为非确定性函数不会产生常量值，优化器无法使用可能适用的策略，如索引查找。结果可能是表扫描。

• InnoDB可能会升级为范围键锁，而不是为一个匹配行获取单行锁。

• 不确定性地执行的更新对复制是不安全的。

困难源于RAND()函数对表中每一行都进行一次评估。为避免多次函数评估，可以使用以下技术之一：

• 将包含非确定性函数的表达式移动到单独的语句中，并将值保存在变量中。在原始语句中，用对变量的引用替换表达式，优化器可以将其视为常量值：

  SET @keyval = FLOOR(1 + RAND() * 49);
  UPDATE t SET col_a = *some_expr* WHERE id = @keyval;
  

• 将随机值分配给派生表中的变量。这种技术会导致变量在比较WHERE子句中使用之前被分配一个值，仅一次：

  UPDATE /*+ NO_MERGE(dt) */ t, (SELECT FLOOR(1 + RAND() * 49) AS r) AS dt
  SET col_a = *some_expr* WHERE id = dt.r;
  

如前所述，在WHERE子句中的非确定性表达式可能会阻止优化并导致表扫描。然而，如果其他表达式是确定性的，可能可以部分优化WHERE子句。例如：

SELECT * FROM t WHERE partial_key=5 AND some_column=RAND();

如果优化器可以使用partial_key来减少选择的行集，那么RAND()的执行次数会减少，从而减少非确定性对优化的影响。

原文：dev.mysql.com/doc/refman/8.0/en/window-function-optimization.html

10.2.1.21 窗口函数优化

窗口函数会影响优化器考虑的策略：

• 如果子查询具有窗口函数，则禁用对子查询的派生表合并。子查询总是被实体化。

• 半连接不适用于窗口函数优化，因为半连接适用于WHERE和JOIN ... ON中的子查询，这些子查询不能包含窗口函数。

• 优化器按顺序处理具有相同排序要求的多个窗口，因此对于第一个窗口之后的窗口，可以跳过排序。

• 优化器不会尝试合并可以在单个步骤中评估的窗口（例如，当多个OVER子句包含相同的窗口定义时）。解决方法是在WINDOW子句中定义窗口，并在OVER子句中引用窗口名称。

未作为窗口函数使用的聚合函数在可能的最外层查询中进行聚合。例如，在这个查询中，MySQL 看到COUNT(t1.b)是一个不能存在于外部查询中的东西，因为它在WHERE子句中的位置：

SELECT * FROM t1 WHERE t1.a = (SELECT COUNT(t1.b) FROM t2);

因此，MySQL 在子查询中进行聚合，将t1.b视为常量，并返回t2行的计数。

将WHERE替换为HAVING会导致错误：

mysql> SELECT * FROM t1 HAVING t1.a = (SELECT COUNT(t1.b) FROM t2);
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1
of SELECT list contains nonaggregated column 'test.t1.a'; this is
incompatible with sql_mode=only_full_group_by

错误发生是因为COUNT(t1.b)可能存在于HAVING中，因此使外部查询聚合。

窗口函数（包括作为窗口函数使用的聚合函数）没有前面的复杂性。它们总是在写入它们的子查询中进行聚合，而不是在外部查询中进行。

窗口函数的评估可能会受到windowing_use_high_precision系统变量值的影响，该变量确定是否计算窗口操作时不损失精度。默认情况下，windowing_use_high_precision是启用的。

对于一些移动框架聚合，可以应用逆聚合函数来从聚合中移除值。这可以提高性能，但可能会损失精度。例如，将一个非常小的浮点值加到一个非常大的值上会导致这个非常小的值被这个大值“隐藏”。当稍后对大值进行反转时，小值的效果就会丢失。

由于逆聚合而导致精度丢失仅适用于浮点（近似值）数据类型的操作。对于其他类型，逆聚合是安全的；这包括允许有小数部分但是精确值类型的DECIMAL。

为了更快地执行，MySQL 总是在安全的情况下使用逆聚合：

• 对于浮点值，逆聚合并不总是安全的，可能会导致精度丢失。默认情况下是避免逆聚合，这样会更慢但保留精度。如果可以为了速度而牺牲安全性，可以禁用windowing_use_high_precision以允许逆聚合。

• 对于非浮点数据类型，逆聚合始终是安全的，并且无论windowing_use_high_precision的值如何都会使用。

• windowing_use_high_precision对于MIN()和MAX()没有影响，在任何情况下都不使用逆聚合。

对于方差函数STDDEV_POP(), STDDEV_SAMP(), VAR_POP(), VAR_SAMP()及其同义词的评估，评估可以在优化模式或默认模式下进行。优化模式可能在最后几位有效数字上产生略有不同的结果。如果这种差异是可以接受的，可以禁用windowing_use_high_precision以允许优化模式。

对于EXPLAIN，窗口执行计划信息在传统输出格式中显示的内容太多。要查看窗口信息，请使用EXPLAIN FORMAT=JSON并查找windowing元素。

原文：dev.mysql.com/doc/refman/8.0/en/row-constructor-optimization.html

10.2.1.22 行构造函数表达式优化

行构造函数允许同时比较多个值。例如，以下两个语句在语义上是等价的：

SELECT * FROM t1 WHERE (column1,column2) = (1,1);
SELECT * FROM t1 WHERE column1 = 1 AND column2 = 1;

此外，优化器以相同方式处理这两个表达式。

如果行构造函数的列不覆盖索引的前缀，优化器就不太可能使用可用的索引。考虑以下表，其主键为(c1, c2, c3)：

CREATE TABLE t1 (
  c1 INT, c2 INT, c3 INT, c4 CHAR(100),
  PRIMARY KEY(c1,c2,c3)
);

在此查询中，WHERE子句使用索引中的所有列。然而，行构造函数本身不覆盖索引前缀，导致优化器仅使用c1（key_len=4，即c1的大小）：

mysql> EXPLAIN SELECT * FROM t1
       WHERE c1=1 AND (c2,c3) > (1,1)\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 3
     filtered: 100.00
        Extra: Using where

在这种情况下，将行构造函数表达式重写为等效的非构造函数表达式可能会导致更完整的索引使用。对于给定的查询，行构造函数和等效的非构造函数表达式分别为：

(c2,c3) > (1,1)
c2 > 1 OR ((c2 = 1) AND (c3 > 1))

将查询重写为使用非构造函数表达式会导致优化器在索引中使用所有三列（key_len=12）：

mysql> EXPLAIN SELECT * FROM t1
       WHERE c1 = 1 AND (c2 > 1 OR ((c2 = 1) AND (c3 > 1)))\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 12
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where

因此，为了获得更好的结果，避免将行构造函数与AND/OR表达式混合使用。选择其中一个使用。

在某些条件下，优化器可以对具有行构造函数参数的IN()表达式应用范围访问方法。参见行构造函数表达式的范围优化。

原文：dev.mysql.com/doc/refman/8.0/en/table-scan-avoidance.html

10.2.1.23 避免全表扫描

EXPLAIN的输出在 MySQL 使用全表扫描解析查询时，在type列中显示ALL。这通常发生在以下情况下：

• 表太小，执行表扫描比使用键查找更快。对于少于 10 行且行长度较短的表，这是常见的情况。

• ON或WHERE子句中没有可用的对索引列的限制条件。

• 比较具有常量值的索引列，并且 MySQL 已经计算（基于索引树）常量覆盖表的太大部分，表扫描会更快。参见第 10.2.1.1 节，“WHERE 子句优化”。

• 通过另一列使用基数较低的键（许多行匹配键值）。在这种情况下，MySQL 假设使用键可能需要许多键查找，并且表扫描会更快。

对于小表，表扫描通常是适当的，性能影响可以忽略不计。对于大表，尝试以下技术以避免优化器错误地选择表扫描：

• 使用ANALYZE TABLE *tbl_name*更新扫描表的键分布。参见第 15.7.3.1 节，“ANALYZE TABLE 语句”。

• 对扫描表使用FORCE INDEX，告诉 MySQL 表扫描比使用给定索引要昂贵：

  SELECT * FROM t1, t2 FORCE INDEX (*index_for_column*)
    WHERE t1.*col_name*=t2.*col_name*;
  

  参见第 10.9.4 节，“索引提示”。

• 使用--max-seeks-for-key=1000选项启动mysqld，或使用SET max_seeks_for_key=1000告诉优化器假设没有键扫描导致超过 1,000 个键查找。参见第 7.1.8 节，“服务器系统变量”。

10.2.2 优化子查询、派生表、视图引用和公共表达式

原文：dev.mysql.com/doc/refman/8.0/en/subquery-optimization.html

10.2.2.1 使用半连接转换优化 IN 和 EXISTS 子查询谓词

10.2.2.2 使用材料化优化子查询

10.2.2.3 使用 EXISTS 策略优化子查询

10.2.2.4 使用合并或材料化优化派生表、视图引用和公共表达式

10.2.2.5 派生条件下推优化

MySQL 查询优化器有不同的策略可用于评估子查询：

• 对于与IN、= ANY或EXISTS谓词一起使用的子查询，优化器有以下选择：

  • 半连接

  • 材料化

  • EXISTS 策略

• 对于与NOT IN、<> ALL或NOT EXISTS谓词一起使用的子查询，优化器有以下选择：

  • 材料化

  • EXISTS 策略

对于派生表，优化器有以下选择（这也适用于视图引用和公共表达式）：

• 将派生表合并到外部查询块中

• 将派生表材料化为内部临时表

以下讨论提供了关于前述优化策略的更多信息。

注意

对于使用子查询修改单个表的UPDATE和DELETE语句存在限制，优化器不使用半连接或材料化子查询优化。作为解决方法，尝试将它们重写为使用连接而不是子查询的多表UPDATE和DELETE语句。

原文：dev.mysql.com/doc/refman/8.0/en/semijoins.html。

10.2.2.1 使用半连接转换优化 IN 和 EXISTS 子查询谓词

半连接是一种准备时间转换，可以启用多种执行策略，如表拉出、重复消除、首次匹配、宽松扫描和物化。优化器使用半连接策略来改善子查询执行，如本节所述。

对于两个表之间的内连接，连接将从一个表中返回一行，只要在另一个表中有匹配。但对于一些问题，唯一重要的信息是是否有匹配，而不是匹配的次数。假设有名为class和roster的表，分别列出课程课程和班级名单（每个班级中注册的学生），要列出实际有学生注册的班级，可以使用这个连接：

SELECT class.class_num, class.class_name
    FROM class
    INNER JOIN roster
    WHERE class.class_num = roster.class_num;

然而，结果为每个注册学生列出每个班级一次。对于所提出的问题，这是信息的不必要重复。

假设class表中的class_num是主键，通过使用SELECT DISTINCT可以实现去重，但是先生成所有匹配行再后来消除重复是低效的。

可以通过使用子查询获得相同的无重复结果：

SELECT class_num, class_name
    FROM class
    WHERE class_num IN
        (SELECT class_num FROM roster);

这里，优化器可以识别到IN子句要求子查询只返回roster表中每个班级编号的一个实例。在这种情况下，查询可以使用半连接；也就是说，只返回与roster中的行匹配的class中每行的一个实例。

包含EXISTS子查询谓词的以下语句与包含等效IN子查询谓词的先前语句等效：

SELECT class_num, class_name
    FROM class
    WHERE EXISTS
        (SELECT * FROM roster WHERE class.class_num = roster.class_num);

在 MySQL 8.0.16 及更高版本中，任何带有EXISTS子查询谓词的语句都会受到与具有等效IN子查询谓词的语句相同的半连接转换的影响。

从 MySQL 8.0.17 开始，以下子查询被转换为反连接：

• NOT IN (SELECT ... FROM ...)。

• NOT EXISTS (SELECT ... FROM ...)。

• IN (SELECT ... FROM ...) IS NOT TRUE。

• EXISTS (SELECT ... FROM ...) IS NOT TRUE。

• IN (SELECT ... FROM ...) IS FALSE。

• EXISTS (SELECT ... FROM ...) IS FALSE。

简而言之，对形式为IN (SELECT ... FROM ...)或EXISTS (SELECT ... FROM ...)的子查询的否定被转换为反连接。

反连接是一种仅返回没有匹配的行的操作。考虑这里显示的查询：

SELECT class_num, class_name
    FROM class
    WHERE class_num NOT IN
        (SELECT class_num FROM roster);

此查询在内部重写为反连接SELECT class_num, class_name FROM class ANTIJOIN roster ON class_num，它返回class中每行的一个实例，该实例不与roster中的任何行匹配。这意味着对于class中的每行，一旦在roster中找到匹配，就可以丢弃class中的行。

如果被比较的表达式是可空的，则通常无法应用反连接转换。一个例外是 (... NOT IN (SELECT ...)) IS NOT FALSE 及其等效的 (... IN (SELECT ...)) IS NOT TRUE 可以被转换为反连接。

外连接和内连接语法允许在外部查询规范中，表引用可以是基本表、派生表、视图引用或公共表达式。

在 MySQL 中，一个子查询必须满足这些条件才能被处理为半连接（或在 MySQL 8.0.17 及更高版本中，如果 NOT 修改子查询，则为反连接）：

• 它必须是 WHERE 或 ON 子句的顶层出现的 IN、= ANY 或 EXISTS 谓词的一部分，可能作为 AND 表达式中的一个项。例如：

  SELECT ...
      FROM ot1, ...
      WHERE (oe1, ...) IN
          (SELECT ie1, ... FROM it1, ... WHERE ...);
  

  这里，ot_*i* 和 it_*i* 代表查询的外部和内部部分中的表，oe_*i* 和 ie_*i* 代表引用外部和内部表中列的表达式。

  在 MySQL 8.0.17 及更高版本中，子查询也可以作为被 NOT、IS [NOT] TRUE 或 IS [NOT] FALSE 修饰的表达式的参数。

• 它必须是一个单独的 SELECT，不包含 UNION 结构。

• 它不能包含 HAVING 子句。

• 它不能包含任何聚合函数（无论是显式还是隐式分组）。

• 它不能有 LIMIT 子句。

• 语句不能在外部查询中使用 STRAIGHT_JOIN 连接类型。

• STRAIGHT_JOIN 修饰符不能存在。

• 外部和内部表的数量总和必须小于联接中允许的最大表数量。

• 子查询可以是相关的或不相关的。在 MySQL 8.0.16 及更高版本中，解相关性会查看作为 EXISTS 参数使用的子查询的 WHERE 子句中的平凡相关谓词，并使其能够优化，就像它是在 IN (SELECT b FROM ...) 中使用一样。术语 平凡相关 意味着谓词是一个相等谓词，它是 WHERE 子句中唯一的谓词（或与 AND 结合），并且一个操作数来自子查询中引用的表，另一个操作数来自外部查询块。

• DISTINCT 关键字是允许的，但会被忽略。半连接策略会自动处理重复项的移除。

• 允许使用 GROUP BY 子句，但会被忽略，除非子查询还包含一个或多个聚合函数。

• 允许使用 ORDER BY 子句，但会被忽略，因为排序对于半连接策略的评估是无关紧要的。

如果一个子查询符合上述条件，MySQL 将其转换为半连接（或在 MySQL 8.0.17 或更高版本中，如果适用，转换为反连接）并从以下策略中做出基于成本的选择：

• 将子查询转换为联接，或使用表拉出并在子查询表和外部表之间运行查询作为内连接。表拉出是将一个表从子查询中拉出到外部查询中。

• Duplicate Weedout: 将半连接视为连接运行，并使用临时表去除重复记录。

• FirstMatch: 在扫描内部表以获取行组合时，如果给定值组有多个实例，则选择一个而不是返回它们全部。这种“捷径”扫描并消除了不必要的行的生成。

• LooseScan: 使用允许从每个子查询值组中选择单个值的索引扫描子查询表。

• 将子查询材料化为用于执行连接的索引临时表，其中索引用于去重。当将临时表与外部表连接时，索引也可能稍后用于查找；如果不是，则扫描表。有关材料化的更多信息，请参见第 10.2.2.2 节，“使用材料化优化子查询”。

可以使用以下optimizer_switch系统变量标志启用或禁用这些策略：

• semijoin标志控制是否使用半连接。从 MySQL 8.0.17 开始，这也适用于反连接。

• 如果启用了semijoin，则firstmatch、loosescan、duplicateweedout和materialization标志可以更精细地控制允许的半连接策略。

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

• 如果禁用了duplicateweedout，优化器偶尔可能生成远非最佳的查询计划。这是由于贪婪搜索期间的启发式修剪导致的，可以通过设置optimizer_prune_level=0来避免。

这些标志默认启用。请参见第 10.9.2 节，“可切换优化”。

优化器最小化了对视图和派生表处理的差异。这影响了使用STRAIGHT_JOIN修饰符和具有可以转换为半连接的IN子查询的视图的查询。以下查询说明了这一点，因为处理方式的改变导致了转换的改变，从而产生了不同的执行策略：

CREATE VIEW v AS
SELECT *
FROM t1
WHERE a IN (SELECT b
           FROM t2);

SELECT STRAIGHT_JOIN *
FROM t3 JOIN v ON t3.x = v.a;

优化器首先查看视图并将 IN 子查询转换为半连接，然后检查是否可能将视图合并到外部查询中。由于外部查询中的 STRAIGHT_JOIN 修饰符阻止了半连接，优化器拒绝合并，导致使用物化表进行派生表评估。

EXPLAIN 输出指示了半连接策略的使用如下：

• 对于扩展的 EXPLAIN 输出，随后由 SHOW WARNINGS 显示的文本显示了重写的查询，其中显示了半连接结构。从中可以了解哪些表被提取出半连接。如果子查询被转换为半连接，您应该看到子查询谓词消失，其表和 WHERE 子句被合并到外部查询的连接列表和 WHERE 子句中。

• 在 Extra 列中的 Start temporary 和 End temporary 表示重复消除时使用了临时表。未被提取出来且在 Start temporary 和 End temporary 覆盖的 EXPLAIN 输出行范围内的表，在临时表中具有其 rowid。

• FirstMatch(*tbl_name*) 在 Extra 列中表示连接快捷方式。

• LooseScan(*m*..*n*) 在 Extra 列中表示使用了 LooseScan 策略。m 和 n 是关键部件编号。

• 临时表用于物化的使用通过具有 select_type 值为 MATERIALIZED 和具有 table 值为 <subquery*N*> 的行来表示。

在 MySQL 8.0.21 及更高版本中，半连接转换也可以应用于使用 [NOT] IN 或 [NOT] EXISTS 子查询谓词的单表 UPDATE 或 DELETE 语句，前提是该语句不使用 ORDER BY 或 LIMIT，并且通过优化器提示或 optimizer_switch 设置允许半连接转换。

原文：dev.mysql.com/doc/refman/8.0/en/subquery-materialization.html

10.2.2.2 使用物化优化子查询

优化器使用物化来实现更高效的子查询处理。物化通过生成一个子查询结果作为临时表来加快查询执行，通常在内存中。当 MySQL 首次需要子查询结果时，它将该结果物化为临时表。任何后续需要结果的时候，MySQL 再次引用临时表。优化器可以使用哈希索引对表进行索引，以使查找快速且廉价。索引包含唯一值以消除重复项并使表变得更小。

当可能时，子查询物化使用内存临时表，如果表变得太大，则回退到磁盘存储。参见第 10.4.4 节，“MySQL 中的内部临时表使用”。

如果不使用物化，优化器有时会将非相关子查询重写为相关子查询。例如，以下IN子查询是非相关的（*where_condition*仅涉及t2列而不涉及t1列）：

SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE *where_condition*);

优化器可能会将此重写为EXISTS相关子查询：

SELECT * FROM t1
WHERE EXISTS (SELECT t2.b FROM t2 WHERE *where_condition* AND t1.a=t2.b);

使用临时表进行子查询物化可以避免这种重写，并使得可以仅执行一次子查询，而不是对外部查询的每一行执行一次。

要在 MySQL 中使用子查询物化，必须启用optimizer_switch系统变量materialization标志。（参见第 10.9.2 节，“可切换的优化”。）启用materialization标志后，物化适用于出现在任何地方的子查询谓词（在选择列表中，WHERE，ON，GROUP BY，HAVING或ORDER BY），适用于以下任何用例的谓词：

• 当外部表达式*oe_i或内部表达式ie_i不可为空时，谓词具有此形式。N*为 1 或更大。

  (*oe_1*, *oe_2*, ..., *oe_N*) [NOT] IN (SELECT *ie_1*, *i_2*, ..., *ie_N* ...)
  

• 当存在单个外部表达式*oe和内部表达式ie*时，谓词具有此形式。这些表达式可以为空。

  *oe* [NOT] IN (SELECT *ie* ...)
  

• 谓词为IN或NOT IN，并且UNKNOWN（NULL）的结果与FALSE的结果具有相同的含义。

以下示例说明了UNKNOWN和FALSE谓词评估等价性要求如何影响是否可以使用子查询物化。假设*where_condition*仅涉及t2列而不涉及t1列，因此子查询是非相关的。

此查询需要进行物化：

SELECT * FROM t1
WHERE t1.a IN (SELECT t2.b FROM t2 WHERE *where_condition*);

在这里，IN 谓词返回 UNKNOWN 或 FALSE 都无关紧要。无论如何，来自 t1 的行都不会包含在查询结果中。

一个不使用子查询物化的示例是下面的查询，其中 t2.b 是一个可空列：

SELECT * FROM t1
WHERE (t1.a,t1.b) NOT IN (SELECT t2.a,t2.b FROM t2
                          WHERE *where_condition*);

使用子查询物化有以下限制：

• 内部和外部表达式的类型必须匹配。例如，如果两个表达式都是整数或都是十进制数，优化器可能可以使用物化，但如果一个表达式是整数，另一个是十进制数，则不能使用物化。

• 内部表达式不能是一个 BLOB。

使用带有查询的 EXPLAIN 提供了一些指示，表明优化器是否使用了子查询物化：

• 与不使用物化的查询执行相比，select_type 可能会从 DEPENDENT SUBQUERY 变为 SUBQUERY。这表明，对于每个外部行执行一次的子查询，物化使得子查询只需执行一次。

• 对于扩展的 EXPLAIN 输出，后续 SHOW WARNINGS 显示的文本包括 materialize 和 materialized-subquery。

在 MySQL 8.0.21 及更高版本中，MySQL 还可以对使用 [NOT] IN 或 [NOT] EXISTS 子查询谓词的单表 UPDATE 或 DELETE 语句应用子查询物化，前提是该语句不使用 ORDER BY 或 LIMIT，并且优化器提示或 optimizer_switch 设置允许使用子查询物化。

原文：dev.mysql.com/doc/refman/8.0/en/subquery-optimization-with-exists.html

10.2.2.3 优化 EXISTS 策略的子查询

某些优化适用于使用 IN（或 =ANY）运算符测试子查询结果的比较。本节讨论了这些优化，特别是关于 NULL 值带来的挑战。讨论的最后部分建议您如何帮助优化器。

考虑以下子查询比较：

*outer_expr* IN (SELECT *inner_expr* FROM ... WHERE *subquery_where*)

MySQL 从“外到内”评估查询。也就是说，它首先获取外部表达式 outer_expr 的值，然后运行子查询并捕获其生成的行。

一个非常有用的优化是“通知”子查询感兴趣的行仅为内部表达式 inner_expr 等于 outer_expr 的行。这是通过将适当的相等性推送到子查询的 WHERE 子句中以使其更加严格来完成的。转换后的比较如下所示：

EXISTS (SELECT 1 FROM ... WHERE *subquery_where* AND *outer_expr*=*inner_expr*)

转换后，MySQL 可以使用推送的相等性来限制必须检查的行数以评估子查询。

更一般地，将 N 个值与返回 N 个值行的子查询进行比较会受到相同的转换影响。如果 oe_i 和 ie_i 表示相应的外部和内部表达式值，则此子查询比较：

(*oe_1*, ..., *oe_N*) IN
  (SELECT *ie_1*, ..., *ie_N* FROM ... WHERE *subquery_where*)

变为：

EXISTS (SELECT 1 FROM ... WHERE *subquery_where*
                          AND *oe_1* = *ie_1*
                          AND ...
                          AND *oe_N* = *ie_N*)

为简单起见，以下讨论假定有一对外部和内部表达式值。

刚刚描述的“推送”策略在以下条件之一为真时有效：

• outer_expr 和 inner_expr 不能为 NULL。

• 您无需区分 NULL 和 FALSE 子查询结果。如果子查询是 WHERE 子句中的 OR 或 AND 表达式的一部分，MySQL 假定您不关心。另一个优化器注意到 NULL 和 FALSE 子查询结果无需区分的情况是这种结构：

  ... WHERE *outer_expr* IN (*subquery*)
  

  在这种情况下，WHERE 子句拒绝行，无论 IN (*subquery*) 返回 NULL 还是 FALSE。

假设 outer_expr 已知是非 NULL 值，但子查询没有生成这样的行，使得 outer_expr = inner_expr。那么 *outer_expr* IN (SELECT ...) 的评估如下：

• NULL，如果 SELECT 生成任何行，其中 inner_expr 为 NULL

• FALSE，如果 SELECT 仅生成非 NULL 值或不生成任何内容

在这种情况下，寻找 *outer_expr* = *inner_expr* 的行的方法不再有效。需要寻找这样的行，但如果找不到，则还需要寻找 inner_expr 为 NULL 的行。粗略地说，子查询可以转换为类似于以下内容：

EXISTS (SELECT 1 FROM ... WHERE *subquery_where* AND
        (*outer_expr*=*inner_expr* OR *inner_expr* IS NULL))

需要评估额外的IS NULL条件是 MySQL 拥有ref_or_null访问方法的原因：

mysql> EXPLAIN
       SELECT *outer_expr* IN (SELECT t2.maybe_null_key
                             FROM t2, t3 WHERE ...)
       FROM t1;
*************************** 1\. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
...
*************************** 2\. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: t2
         type: ref_or_null
possible_keys: maybe_null_key
          key: maybe_null_key
      key_len: 5
          ref: func
         rows: 2
        Extra: Using where; Using index
...

unique_subquery和index_subquery子查询特定的访问方法也有“or NULL”变体。

额外的OR ... IS NULL条件使得查询执行稍微复杂一些（子查询内的一些优化变得不适用），但通常是可以容忍的。

当*outer_expr*可以为NULL时情况会更糟。根据 SQL 将NULL解释为“未知值”，NULL IN (SELECT *inner_expr* ...)应该评估为：

• 如果SELECT产生任何行，则为NULL

• 如果SELECT不产生任何行，则为FALSE

为了正确评估，有必要检查SELECT是否产生了任何行，因此*outer_expr* = *inner_expr*不能被推送到子查询中。这是一个问题，因为许多现实世界的子查询会变得非常缓慢，除非相等性可以被推送下去。

本质上，必须有不同的方式来执行子查询，取决于*outer_expr*的值。

优化器选择 SQL 的兼容性而非速度，因此它考虑到*outer_expr*可能是NULL的可能性：

• 如果*outer_expr*为NULL，为了评估以下表达式，需要执行SELECT以确定是否产生任何行：

  NULL IN (SELECT *inner_expr* FROM ... WHERE *subquery_where*)
  

  在这里需要执行原始的SELECT，而不带有之前提到的任何推送下来的相等性。

• 另一方面，当*outer_expr*不为NULL时，绝对必要进行以下比较：

  *outer_expr* IN (SELECT *inner_expr* FROM ... WHERE *subquery_where*)
  

  被转换为使用推送下来条件的这个表达式：

  EXISTS (SELECT 1 FROM ... WHERE *subquery_where* AND *outer_expr*=*inner_expr*)
  

  没有这种转换，子查询会很慢。

为了解决是否将条件推送到子查询中的困境，条件被包装在“触发器”函数中。因此，以下形式的表达式：

*outer_expr* IN (SELECT *inner_expr* FROM ... WHERE *subquery_where*)

被转换为：

EXISTS (SELECT 1 FROM ... WHERE *subquery_where*
                          AND trigcond(*outer_expr*=*inner_expr*))

更一般地，如果子查询比较基于几对外部和内部表达式，转换将采用这种比较：

(*oe_1*, ..., *oe_N*) IN (SELECT *ie_1*, ..., *ie_N* FROM ... WHERE *subquery_where*)

并将其转换为以下表达式：

EXISTS (SELECT 1 FROM ... WHERE *subquery_where*
                          AND trigcond(*oe_1*=*ie_1*)
                          AND ...
                          AND trigcond(*oe_N*=*ie_N*)
       )

每个trigcond(*X*)是一个特殊函数，其计算结果如下：

• 当“链接”外部表达式*oe_i不为NULL时为X*

• 当“链接”外部表达式*oe_i*为NULL时为TRUE

注意

触发器函数不是您使用CREATE TRIGGER创建的那种触发器。

被包裹在trigcond()函数内的相等性不是查询优化器的一流谓词。大多数优化无法处理可能在查询执行时打开和关闭的谓词，因此它们假定任何trigcond(*X*)都是一个未知函数并将其忽略。触发的相等性可以被这些优化使用：

• 参考优化：trigcond(*X*=*Y* [OR *Y* IS NULL])可以用来构建ref、eq_ref或ref_or_null表访问。

• 基于索引查找的子查询执行引擎：trigcond(*X*=*Y*)可以用来构建unique_subquery或index_subquery访问。

• 表条件生成器：如果子查询是几个表的连接，触发的条件将尽快被检查。

当优化器使用触发的条件来创建某种基于索引查找的访问（如前述列表的前两项），它必须有一个针对条件关闭的备用策略。这个备用策略总是相同的：做一个完整的表扫描。在EXPLAIN输出中，备用策略显示为Extra列中的Full scan on NULL key：

mysql> EXPLAIN SELECT t1.col1,
       t1.col1 IN (SELECT t2.key1 FROM t2 WHERE t2.col2=t1.col2) FROM t1\G
*************************** 1\. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
        ...
*************************** 2\. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: t2
         type: index_subquery
possible_keys: key1
          key: key1
      key_len: 5
          ref: func
         rows: 2
        Extra: Using where; Full scan on NULL key

如果你运行EXPLAIN，然后跟着运行SHOW WARNINGS，你可以看到触发的条件：

*************************** 1\. row ***************************
  Level: Note
   Code: 1003
Message: select `test`.`t1`.`col1` AS `col1`,
         <in_optimizer>(`test`.`t1`.`col1`,
         <exists>(<index_lookup>(<cache>(`test`.`t1`.`col1`) in t2
         on key1 checking NULL
         where (`test`.`t2`.`col2` = `test`.`t1`.`col2`) having
         trigcond(<is_not_null_test>(`test`.`t2`.`key1`))))) AS
         `t1.col1 IN (select t2.key1 from t2 where t2.col2=t1.col2)`
         from `test`.`t1`

使用触发条件会带来一些性能影响。现在，一个NULL IN (SELECT ...)表达式可能会导致一个全表扫描（这是慢的），而以前则不会。这是为了获得正确结果而付出的代价（触发条件策略的目标是提高合规性，而不是速度）。

对于多表子查询，执行NULL IN (SELECT ...)特别慢，因为连接优化器不会为外部表达式为NULL的情况进行优化。它假设左侧带有NULL的子查询评估非常罕见，即使有统计数据表明相反。另一方面，如果外部表达式可能是NULL但实际上从未是，那么就不会有性能惩罚。

为了帮助查询优化器更好地执行您的查询，请使用以下建议：

• 如果一个列确实是NOT NULL，就声明它为NOT NULL。这也通过简化列的条件测试来帮助优化器的其他方面。

• 如果你不需要区分NULL和FALSE子查询结果，你可以轻松避免慢执行路径。替换一个看起来像这样的比较：

  *outer_expr* [NOT] IN (SELECT *inner_expr* FROM ...)
  

  使用这个表达式：

  (*outer_expr* IS NOT NULL) AND (*outer_expr* [NOT] IN (SELECT *inner_expr* FROM ...))
  

  然后 NULL IN (SELECT ...) 从未被评估，因为一旦表达式结果明确，MySQL 就会停止评估AND部分。

  另一种可能的重写：

  [NOT] EXISTS (SELECT *inner_expr* FROM ...
          WHERE *inner_expr*=*outer_expr*)
  

optimizer_switch系统变量的subquery_materialization_cost_based标志允许控制在子查询材料化和IN到EXISTS子查询转换之间的选择。参见第 10.9.2 节，“可切换优化”。

原文：dev.mysql.com/doc/refman/8.0/en/derived-table-optimization.html

10.2.2.4 优化派生表、视图引用和公共表达式的合并或物化

优化器可以使用两种策略处理派生表引用（也适用于视图引用和公共表达式）：

• 将派生表合并到外部查询块中

• 将派生表物化为内部临时表

示例 1：

SELECT * FROM (SELECT * FROM t1) AS derived_t1;

通过将派生表derived_t1合并，该查询的执行方式类似于：

SELECT * FROM t1;

示例 2：

SELECT *
  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2 ON t1.f2=derived_t2.f1
  WHERE t1.f1 > 0;

通过将派生表derived_t2合并，该查询的执行方式类似于：

SELECT t1.*, t2.f1
  FROM t1 JOIN t2 ON t1.f2=t2.f1
  WHERE t1.f1 > 0;

通过物化，derived_t1和derived_t2在各自的查询中被视为单独的表。

优化器处理派生表、视图引用和公共表达式的方式相同：尽可能避免不必要的物化，这使得可以将外部查询的条件下推到派生表，并生成更有效的执行计划。（例如，参见 Section 10.2.2.2, “Optimizing Subqueries with Materialization”。）

如果合并会导致外部查询块引用超过 61 个基本表，则优化器选择物化。

如果这些条件都成立，优化器将派生表或视图引用中的ORDER BY子句传播到外部查询块：

• 外部查询没有分组或聚合。

• 外部查询没有指定DISTINCT、HAVING或ORDER BY。

• 外部查询在FROM子句中只有这个派生表或视图引用作为唯一数据源。

否则，优化器将忽略ORDER BY子句。

可以影响优化器是否尝试将派生表、视图引用和公共表达式合并到外部查询块的以下方法：

• 可以使用MERGE和NO_MERGE优化器提示。假设没有其他规则阻止合并，则应用这些提示。参见 Section 10.9.3, “Optimizer Hints”。

• 同样，您可以使用optimizer_switch系统变量的derived_merge标志。默认情况下，该标志已启用以允许合并。禁用该标志将阻止合并并避免ER_UPDATE_TABLE_USED错误。

  derived_merge标志也适用于不包含ALGORITHM子句的视图。因此，如果对使用等效于子查询的表达式的视图引用发生ER_UPDATE_TABLE_USED错误，则将ALGORITHM=TEMPTABLE添加到视图定义中可以阻止合并，并优先于derived_merge值。

• 通过在子查询中使用任何阻止合并的构造，可以禁用合并，尽管这些构造对于材料化的影响不够明显。阻止合并的构造对于派生表、公共表达式和视图引用是相同的：

  • 聚合函数或窗口函数（SUM()、MIN()、MAX()、COUNT()等）

  • DISTINCT

  • GROUP BY

  • HAVING

  • LIMIT

  • UNION或UNION ALL

  • 在选择列表中的子查询

  • 对用户变量的赋值

  • 仅引用文字值（在这种情况下，没有基础表）

如果优化器选择材料化策略而不是合并派生表，则处理查询如下：

• 优化器推迟派生表的材料化，直到在查询执行期间需要其内容。这样做可以提高性能，因为延迟材料化可能导致根本不需要进行材料化。考虑一个将派生表的结果与另一个表连接的查询：如果优化器首先处理另一个表并发现它不返回任何行，则无需继续执行连接，并且优化器可以完全跳过材料化派生表。

• 在查询执行期间，优化器可能向派生表添加索引，以加快从中检索行的速度。

考虑以下包含派生表的EXPLAIN语句，用于一个包含派生表的SELECT查询：

EXPLAIN SELECT * FROM (SELECT * FROM t1) AS derived_t1;

优化器通过延迟派生表的材料化直到在SELECT执行期间需要结果时来避免材料化派生表。在这种情况下，查询不会被执行（因为它出现在EXPLAIN语句中），因此结果永远不会被需要。

即使对于执行的查询，延迟派生表的材料化也可能使优化器完全避免材料化。当发生这种情况时，查询执行速度比执行材料化所需的时间更快。考虑以下查询，该查询将派生表的结果与另一个表连接：

SELECT *
  FROM t1 JOIN (SELECT t2.f1 FROM t2) AS derived_t2
          ON t1.f2=derived_t2.f1
  WHERE t1.f1 > 0;

如果优化过程首先处理t1，并且WHERE子句产生空结果，则连接必然为空，衍生表不需要实体化。

对于需要实体化的衍生表，优化器可能会向实体化表添加索引以加快对其的访问速度。如果此类索引使得可以使用ref访问表，则可以大大减少查询执行期间读取的数据量。考虑以下查询：

SELECT *
 FROM t1 JOIN (SELECT DISTINCT f1 FROM t2) AS derived_t2
         ON t1.f1=derived_t2.f1;

如果构建索引derived_t2中的列f1可以使最低成本执行计划使用ref访问，优化器会自动添加索引。添加索引后，优化器可以将实体化的衍生表视为具有索引的常规表，并且从生成的索引中获得类似的好处。与没有索引的查询执行成本相比，索引创建的开销微不足道。如果ref访问的成本高于其他访问方法，优化器不会创建索引，也不会有任何损失。

对于优化器跟踪输出，合并的衍生表或视图引用不会显示为节点。只有其基础表会出现在顶层查询计划中。

衍生表的实体化适用于通用表达式（CTEs）。此外，以下考虑事项特别适用于 CTEs。

如果查询通过查询实体化了一个 CTE，则即使查询多次引用它，也只会为查询实体化一次。

递归 CTE 始终会被实体化。

如果 CTE 被实体化，优化器会自动添加相关索引，如果估计索引可以加快顶层语句对 CTE 的访问速度。这类似于自动为衍生表创建索引，不同之处在于，如果 CTE 被多次引用，优化器可能会创建多个索引，以最适当的方式加快每个引用的访问速度。

MERGE和NO_MERGE优化提示可以应用于 CTEs。顶层语句中的每个 CTE 引用都可以有自己的提示，允许选择性地合并或实体化 CTE 引用。以下语句使用提示指示cte1应该合并，cte2应该实体化：

WITH
  cte1 AS (SELECT a, b FROM table1),
  cte2 AS (SELECT c, d FROM table2)
SELECT /*+ MERGE(cte1) NO_MERGE(cte2) */ cte1.b, cte2.d
FROM cte1 JOIN cte2
WHERE cte1.a = cte2.c;

CREATE VIEW的ALGORITHM子句不会影响视图定义中的SELECT语句之前的任何WITH")子句的实体化。考虑以下语句：

CREATE ALGORITHM={TEMPTABLE|MERGE} VIEW v1 AS WITH ... SELECT ...

ALGORITHM值仅影响SELECT的实体化，而不影响WITH")子句。

在 MySQL 8.0.16 之前，如果internal_tmp_disk_storage_engine=MYISAM，则尝试使用磁盘临时表实现 CTE 时会出现错误，因为对于 CTE，用于磁盘内部临时表的存储引擎不能是MyISAM。从 MySQL 8.0.16 开始，这不再是一个问题，因为TempTable现在总是使用InnoDB作为磁盘内部临时表的存储引擎。

如前所述，如果实现了 CTE，则即使多次引用，也只会实现一次。为了表示一次性实现，优化器跟踪输出包含一个creating_tmp_table的出现，以及一个或多个reusing_tmp_table的出现。

CTE 类似于派生表，其后跟随materialized_from_subquery节点的引用。对于多次引用的 CTE 也是如此，因此不会出现materialized_from_subquery节点的重复（这会给人一种子查询被多次执行的印象，并产生不必要的冗长输出）。对 CTE 的唯一引用具有完整的materialized_from_subquery节点，其中包含其子查询计划的描述。其他引用具有简化的materialized_from_subquery节点。相同的想法适用于TRADITIONAL格式的EXPLAIN输出：其他引用的子查询不会显示。

原文：dev.mysql.com/doc/refman/8.0/en/derived-condition-pushdown-optimization.html

10.2.2.5 派生条件下推优化

MySQL 8.0.22 及更高版本支持对符合条件的子查询进行派生条件下推。对于诸如SELECT * FROM (SELECT i, j FROM t1) AS dt WHERE i > *constant*这样的查询，在许多情况下可以将外部WHERE条件下推到派生表，从而得到SELECT * FROM (SELECT i, j FROM t1 WHERE i > *constant*) AS dt。当无法将派生表合并到外部查询中（例如，如果派生表使用聚合），将外部WHERE条件下推到派生表应该减少需要处理的行数，从而加快查询的执行速度。

注意

在 MySQL 8.0.22 之前，如果派生表被实体化但未合并，MySQL 会实体化整个表，然后使用WHERE条件限定所有结果行。如果未启用派生条件下推或由于其他原因无法使用，则仍然如此。

外部WHERE条件可以在以下情况下下推到派生实体化表：

• 当派生表不使用聚合或窗口函数时，外部WHERE条件可以直接下推到它。这包括具有多个谓词用AND、OR或两者连接的WHERE条件。

  例如，查询SELECT * FROM (SELECT f1, f2 FROM t1) AS dt WHERE f1 < 3 AND f2 > 11被重写为SELECT f1, f2 FROM (SELECT f1, f2 FROM t1 WHERE f1 < 3 AND f2 > 11) AS dt。

• 当派生表具有GROUP BY且不使用窗口函数时，引用不属于GROUP BY的一个或多个列的外部WHERE条件可以作为HAVING条件下推到派生表。

  例如，SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i, j) AS dt WHERE sum > 100在派生条件下推后被重写为SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i, j HAVING sum > 100) AS dt。

• 当派生表使用GROUP BY且外部WHERE条件中的列是GROUP BY列时，引用这些列的WHERE条件可以直接下推到派生表。

  例如，查询SELECT * FROM (SELECT i,j, SUM(k) AS sum FROM t1 GROUP BY i,j) AS dt WHERE i > 10被重写为SELECT * FROM (SELECT i,j, SUM(k) AS sum FROM t1 WHERE i > 10 GROUP BY i,j) AS dt。

  如果外部WHERE条件中有引用GROUP BY列的谓词以及引用不是GROUP BY列的谓词，前一种类型的谓词被推送为WHERE条件，而后一种类型的谓词被推送为HAVING条件。例如，在查询SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i,j) AS dt WHERE i > 10 AND sum > 100中，外部WHERE子句中的谓词i > 10引用了一个GROUP BY列，而谓词sum > 100则不引用任何GROUP BY列。因此，派生表推送优化导致查询被重写为类似于下面所示的方式：

  SELECT * FROM (
      SELECT i, j, SUM(k) AS sum FROM t1
          WHERE i > 10
          GROUP BY i, j
          HAVING sum > 100
      ) AS dt;
  

要启用派生条件推送，必须将optimizer_switch系统变量的derived_condition_pushdown标志（在此版本中添加）设置为on，这是默认设置。如果optimizer_switch禁用了这个优化，可以使用DERIVED_CONDITION_PUSHDOWN优化提示为特定查询启用它。要禁用给定查询的优化，使用NO_DERIVED_CONDITION_PUSHDOWN优化提示。

派生表条件推送优化受到以下限制和限制：

• 优化不能在派生表包含UNION的情况下使用。这个限制在 MySQL 8.0.29 中被取消。考虑两个表t1和t2，以及一个包含它们联合的视图v，如下所示创建：

  CREATE TABLE t1 (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    c1 INT, 
    KEY i1 (c1)
  );
  
  CREATE TABLE t2 (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    c1 INT, 
    KEY i1 (c1)
  );
  
  CREATE OR REPLACE VIEW v AS
       SELECT id, c1 FROM t1
       UNION ALL
       SELECT id, c1 FROM t2;
  

  如EXPLAIN的输出所示，查询顶层中存在的条件，例如SELECT * FROM v WHERE c1 = 12现在可以被推送到派生表中的两个查询块：

  mysql> EXPLAIN FORMAT=TREE SELECT * FROM v WHERE c1 = 12\G
  *************************** 1\. row ***************************
  EXPLAIN: -> Table scan on v  (cost=1.26..2.52 rows=2)
   -> Union materialize  (cost=2.16..3.42 rows=2)
    *-> Covering index lookup on t1 using i1 (c1=12)  (cost=0.35 rows=1)* *-> Covering index lookup on t2 using i1 (c1=12)  (cost=0.35 rows=1)* 1 row in set (0.00 sec)
  

  在 MySQL 8.0.29 及更高版本中，可以在UNION查询中使用派生表条件推送优化，但有以下例外：

  • 如果UNION查询中的任何实现的派生表是递归公共表达式（参见递归公共表达式），则不能使用条件推送。

  • 包含非确定性表达式的条件不能被推送到派生表。

• 派生表不能使用LIMIT子句。

• 包含子查询的条件不能被推送。

• 如果派生表是外连接的内部表，则不能使用优化。

• 如果一个实现的派生表是一个公共表达式，如果它被多次引用，则条件不会被推送到它。

• 如果条件使用参数，并且条件形式为*derived_column* > ?，则条件可以被下推。如果外部WHERE条件中的派生列是在基础派生表中具有?的表达式，则此条件无法被下推。

• 对于在使用ALGORITHM=TEMPTABLE创建的视图的表上而不是在视图本身上的条件查询，多重相等性在解析时不被识别，因此条件无法被下推。这是因为，在优化查询时，条件下推发生在解析阶段，而多重相等性传播发生在优化阶段。

  对于使用ALGORITHM=MERGE的视图，这种情况并不是问题，其中相等性可以被传播，条件可以被下推。

• 从 MySQL 8.0.28 开始，如果派生表的SELECT列表包含对用户变量的任何赋值，则条件无法被下推。（Bug #104918）

10.2.3 优化 INFORMATION_SCHEMA 查询

原文：dev.mysql.com/doc/refman/8.0/en/information-schema-optimization.html

监视数据库的应用程序可能经常使用INFORMATION_SCHEMA表。为这些表编写查询最有效的方法是使用以下一般准则：

• 尽量只查询INFORMATION_SCHEMA表中作为数据字典表视图的表。

• 尽量只查询静态元数据。选择列或使用检索条件来获取动态元数据会增加处理动态元数据的开销。

注意

在INFORMATION_SCHEMA查询中，数据库和表名的比较行为可能与您期望的不同。详情请参见 Section 12.8.7, “Using Collation in INFORMATION_SCHEMA Searches”。

这些INFORMATION_SCHEMA表实际上是作为数据字典表的视图实现的，因此对它们的查询会从数据字典中检索信息：

CHARACTER_SETS
CHECK_CONSTRAINTS
COLLATIONS
COLLATION_CHARACTER_SET_APPLICABILITY
COLUMNS
EVENTS
FILES
INNODB_COLUMNS
INNODB_DATAFILES
INNODB_FIELDS
INNODB_FOREIGN
INNODB_FOREIGN_COLS
INNODB_INDEXES
INNODB_TABLES
INNODB_TABLESPACES
INNODB_TABLESPACES_BRIEF
INNODB_TABLESTATS
KEY_COLUMN_USAGE
PARAMETERS
PARTITIONS
REFERENTIAL_CONSTRAINTS
RESOURCE_GROUPS
ROUTINES
SCHEMATA
STATISTICS
TABLES
TABLE_CONSTRAINTS
TRIGGERS
VIEWS
VIEW_ROUTINE_USAGE
VIEW_TABLE_USAGE

一些类型的值，即使是非视图的INFORMATION_SCHEMA表，也是通过从数据字典中查找来检索的。这包括数据库和表名、表类型和存储引擎等值。

一些INFORMATION_SCHEMA表包含提供表统计信息的列：

STATISTICS.CARDINALITY
TABLES.AUTO_INCREMENT
TABLES.AVG_ROW_LENGTH
TABLES.CHECKSUM
TABLES.CHECK_TIME
TABLES.CREATE_TIME
TABLES.DATA_FREE
TABLES.DATA_LENGTH
TABLES.INDEX_LENGTH
TABLES.MAX_DATA_LENGTH
TABLES.TABLE_ROWS
TABLES.UPDATE_TIME

这些列代表动态表元数据；即，随着表内容变化而变化的信息。

默认情况下，MySQL 在查询这些列时从mysql.index_stats和mysql.innodb_table_stats数据字典表中检索缓存值，这比直接从存储引擎检索统计信息更有效。如果缓存的统计信息不可用或已过期，MySQL 会从存储引擎中检索最新的统计信息，并将其缓存在mysql.index_stats和mysql.innodb_table_stats数据字典表中。随后的查询将检索缓存的统计信息，直到缓存的统计信息过期。服务器重新启动或首次打开mysql.index_stats和mysql.innodb_table_stats表不会自动更新缓存的统计信息。

information_schema_stats_expiry会话变量定义了缓存统计信息过期之前的时间段。默认值为 86400 秒（24 小时），但时间段可以延长至一年。

要随时更新给定表的缓存值，请使用ANALYZE TABLE。

在以下情况下，查询统计列不会在mysql.index_stats和mysql.innodb_table_stats数据字典表中存储或更新统计信息：

• 当缓存的统计信息尚未过期时。

• 当information_schema_stats_expiry设置为 0 时。

• 当服务器处于read_only、super_read_only、transaction_read_only或innodb_read_only模式时。

• 当查询还检索性能模式数据时。

information_schema_stats_expiry是一个会话变量，每个客户端会话可以定义自己的过期值。一个会话检索并缓存的统计信息对其他会话可用。

注意

如果启用了innodb_read_only系统变量，则分析表可能会失败，因为它无法更新数据字典中使用InnoDB的统计表。对于更新键分布的分析表操作，即使操作更新表本身（例如，如果它是MyISAM表），也可能发生失败。要获取更新后的分布统计信息，请设置information_schema_stats_expiry=0。

对于在数据字典表上实现为视图的INFORMATION_SCHEMA表，基础数据字典表上的索引允许优化器构建高效的查询执行计划。要查看优化器所做的选择，请使用解释。要还查看服务器用于执行INFORMATION_SCHEMA查询的查询，请在解释之后立即使用显示警告。

考虑以下语句，用于识别utf8mb4字符集的排序规则：

mysql> SELECT COLLATION_NAME
       FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
       WHERE CHARACTER_SET_NAME = 'utf8mb4';
+----------------------------+
| COLLATION_NAME             |
+----------------------------+
| utf8mb4_general_ci         |
| utf8mb4_bin                |
| utf8mb4_unicode_ci         |
| utf8mb4_icelandic_ci       |
| utf8mb4_latvian_ci         |
| utf8mb4_romanian_ci        |
| utf8mb4_slovenian_ci       |
...

服务器如何处理该语句？要找出，请使用解释：

mysql> EXPLAIN SELECT COLLATION_NAME
       FROM INFORMATION_SCHEMA.COLLATION_CHARACTER_SET_APPLICABILITY
       WHERE CHARACTER_SET_NAME = 'utf8mb4'\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: cs
   partitions: NULL
         type: const
possible_keys: PRIMARY,name
          key: name
      key_len: 194
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
*************************** 2\. row ***************************
           id: 1
  select_type: SIMPLE
        table: col
   partitions: NULL
         type: ref
possible_keys: character_set_id
          key: character_set_id
      key_len: 8
          ref: const
         rows: 68
     filtered: 100.00
        Extra: NULL 2 rows in set, 1 warning (0.01 sec)

要查看用于满足该语句的查询，请使用显示警告：

mysql> SHOW WARNINGS\G
*************************** 1\. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `mysql`.`col`.`name` AS `COLLATION_NAME`
         from `mysql`.`character_sets` `cs`
         join `mysql`.`collations` `col`
         where ((`mysql`.`col`.`character_set_id` = '45')
         and ('utf8mb4' = 'utf8mb4'))

如显示警告所示，服务器将处理对COLLATION_CHARACTER_SET_APPLICABILITY的查询，就像对mysql系统数据库中的character_sets和collations数据字典表的查询一样。

10.2.4 优化性能模式查询

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

监视数据库的应用程序可能经常使用性能模式表。为了最有效地为这些表编写查询，请利用它们的索引。例如，包括一个WHERE子句，根据索引列中的特定值进行检索行的限制。

大多数性能模式表都有索引。不具有索引的表通常包含少量行或不太可能经常查询。性能模式索引使优化器可以访问除全表扫描之外的执行计划。这些索引还提高了相关对象的性能，例如使用这些表的sys模式视图。

要查看给定性能模式表是否具有索引以及它们是什么，请使用SHOW INDEX或SHOW CREATE TABLE：

mysql> SHOW INDEX FROM performance_schema.accounts\G
*************************** 1\. row ***************************
        Table: accounts
   Non_unique: 0
     Key_name: ACCOUNT
 Seq_in_index: 1
  Column_name: USER
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: HASH
      Comment:
Index_comment:
      Visible: YES
*************************** 2\. row ***************************
        Table: accounts
   Non_unique: 0
     Key_name: ACCOUNT
 Seq_in_index: 2
  Column_name: HOST
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: HASH
      Comment:
Index_comment:
      Visible: YES 
mysql> SHOW CREATE TABLE performance_schema.rwlock_instances\G
*************************** 1\. row ***************************
       Table: rwlock_instances
Create Table: CREATE TABLE `rwlock_instances` (
  `NAME` varchar(128) NOT NULL,
  `OBJECT_INSTANCE_BEGIN` bigint(20) unsigned NOT NULL,
  `WRITE_LOCKED_BY_THREAD_ID` bigint(20) unsigned DEFAULT NULL,
  `READ_LOCKED_BY_COUNT` int(10) unsigned NOT NULL,
  PRIMARY KEY (`OBJECT_INSTANCE_BEGIN`),
  KEY `NAME` (`NAME`),
  KEY `WRITE_LOCKED_BY_THREAD_ID` (`WRITE_LOCKED_BY_THREAD_ID`)
) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

要查看性能模式查询的执行计划以及是否使用任何索引，请使用EXPLAIN：

mysql> EXPLAIN SELECT * FROM performance_schema.accounts
       WHERE (USER,HOST) = ('root','localhost')\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: accounts
   partitions: NULL
         type: const
possible_keys: ACCOUNT
          key: ACCOUNT
      key_len: 278
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL

EXPLAIN输出表明优化器使用包含USER和HOST列的ACCOUNT索引的accounts表。

性能模式索引是虚拟的：它们是性能模式存储引擎的构造，不使用内存或磁盘存储。性能模式向优化器报告索引信息，以便它可以构建高效的执行计划。性能模式反过来使用优化器关于要查找的信息（例如，特定键值），以便它可以执行高效的查找而不构建实际的索引结构。这种实现提供了两个重要的好处：

• 它完全避免了通常发生在经常更新的表上的维护成本。

• 它在查询执行的早期阶段减少了检索的数据量。对于索引列上的条件，性能模式有效地只返回满足查询条件的表行。没有索引，性能模式将返回表中的所有行，要求优化器稍后评估每行的条件以生成最终结果。

性能模式索引是预定义的，不能删除、添加或更改。

性能模式索引类似于哈希索引。例如：

• 它们仅用于使用=或<=>运算符进行相等比较。

• 它们是无序的。如果查询结果必须具有特定的行排序特性，请包含ORDER BY子句。

关于哈希索引的更多信息，请参见第 10.3.9 节，“B-Tree 和哈希索引的比较”。

10.2.5 优化数据更改语句

原文：dev.mysql.com/doc/refman/8.0/en/data-change-optimization.html

10.2.5.1 优化 INSERT 语句

10.2.5.2 优化 UPDATE 语句

10.2.5.3 优化 DELETE 语句

本节解释了如何加快数据更改语句：INSERT，UPDATE和DELETE。传统的 OLTP 应用程序和现代的 Web 应用程序通常执行许多小型数据更改操作，其中并发性至关重要。数据分析和报告应用程序通常运行影响许多行的数据更改操作，其中主要考虑因素是写入大量数据的 I/O 和保持索引最新。对于插入和更新大量数据（在行业中称为 ETL，即“提取-转换-加载”），有时您会使用其他 SQL 语句或外部命令，模仿INSERT，UPDATE和DELETE语句的效果。

原文：dev.mysql.com/doc/refman/8.0/en/insert-optimization.html

10.2.5.1 优化 INSERT 语句

为了优化插入速度，将许多小操作合并为单个大操作。理想情况下，您建立一个单一连接，一次发送许多新行的数据，并延迟所有索引更新和一致性检查直到最后。

插入一行所需的时间由以下因素决定，其中数字表示近似比例：

• 连接：(3)

• 将查询发送到服务器：(2)

• 解析查询：(2)

• 插入行：(1 × 行大小)

• 插入索引：(1 × 索引数)

• 关闭：(1)

这并未考虑到打开表的初始开销，这是每个并发运行的查询都会执行一次的操作。

表的大小通过对数*N*减慢索引的插入速度，假设是 B 树索引。

您可以使用以下方法加快插入速度：

• 如果您正在同时从同一客户端插入许多行，请使用带有多个VALUES列表的INSERT语句一次性插入多行。这比使用单独的单行INSERT语句要快得多（在某些情况下快几倍）。如果要向非空表添加数据，您可以调整bulk_insert_buffer_size变量以使数据插入速度更快。请参阅第 7.1.8 节，“服务器系统变量”。

• 从文本文件加载表时，请使用LOAD DATA。这通常比使用INSERT语句快 20 倍。请参阅第 15.2.9 节，“LOAD DATA 语句”。

• 利用列具有默认值的事实。仅在要插入的值与默认值不同时显式插入值。这减少了 MySQL 必须进行的解析，并提高了插入速度。

• 有关InnoDB表的特定提示，请参阅第 10.5.5 节，“InnoDB 表的批量数据加载”。

• 有关MyISAM表的特定提示，请参阅第 10.6.2 节，“MyISAM 表的批量数据加载”。

原文：dev.mysql.com/doc/refman/8.0/en/update-optimization.html

10.2.5.2 优化 UPDATE 语句

更新语句被优化为像SELECT查询一样，但额外增加了写入的开销。写入的速度取决于被更新的数据量和被更新的索引数量。未更改的索引不会被更新。

另一种获得快速更新的方法是延迟更新，然后稍后连续进行多次更新。如果锁定表，一次性执行多次更新比逐个执行要快得多。

对于使用动态行格式的MyISAM表，将行更新为更长的总长度可能会导致行拆分。如果经常这样做，偶尔使用OPTIMIZE TABLE非常重要。参见 Section 15.7.3.4, “OPTIMIZE TABLE Statement”。

原文：dev.mysql.com/doc/refman/8.0/en/delete-optimization.html

10.2.5.3 优化 DELETE 语句

删除MyISAM表中单独行所需的时间与索引数量成正比。要更快地删除行，可以通过增加key_buffer_size系统变量的大小来增加键缓存的大小。参见 Section 7.1.1, “配置服务器”。

要从MyISAM表中删除所有行，TRUNCATE TABLE *tbl_name*比DELETE FROM *tbl_name*更快。截断操作不是事务安全的；在进行活动事务或活动表锁时尝试进行截断操作时会出现错误。参见 Section 15.1.37, “TRUNCATE TABLE 语句”。

10.2.6 优化数据库权限

原文：dev.mysql.com/doc/refman/8.0/en/permission-optimization.html

权限设置越复杂，所有 SQL 语句的开销就越大。简化由GRANT语句建立的权限可以使 MySQL 在客户端执行语句时减少权限检查开销。例如，如果您没有授予任何表级或列级权限，服务器就不需要检查tables_priv和columns_priv表的内容。同样，如果您没有对任何账户设置资源限制，服务器就不需要执行资源计数。如果您有非常高的语句处理负载，请考虑使用简化的授权结构来减少权限检查开销。

10.2.7 其他优化提示

原文：dev.mysql.com/doc/refman/8.0/en/miscellaneous-optimization-tips.html

本节列出了一些改善查询处理速度的杂项提示：

• 如果您的应用程序进行了几个数据库请求以执行相关更新，将语句组合成存储过程可以提高性能。同样，如果您的应用程序基于几个列值或大量数据计算单个结果，将计算组合成可加载的函数可以提高性能。然后，产生的快速数据库操作可以被其他查询、应用程序甚至用不同编程语言编写的代码重用。有关更多信息，请参阅 第 27.2 节“使用存储过程” 和 向 MySQL 添加函数。

• 要解决ARCHIVE表出现的任何压缩问题，请使用OPTIMIZE TABLE。请参阅 第 18.5 节“ARCHIVE 存储引擎”。

• 如果可能的话，将报告分类为“实时”或“统计”，需要用于统计报告的数据仅从定期从实时数据生成的摘要表中创建。

• 如果您的数据不太符合行和列的表结构，您可以将数据打包存储到一个BLOB列中。在这种情况下，您必须在应用程序中提供代码来打包和解包信息，但这可能会节省读取和写入相关值集的 I/O 操作。

• 对于 Web 服务器，将图像和其他二进制资产存储为文件，并将路径名存储在数据库中，而不是存储文件本身。大多数 Web 服务器更擅长缓存文件而不是数据库内容，因此使用文件通常更快。（尽管在这种情况下，您必须自行处理备份和存储问题。）

• 如果需要真正的高速度，请查看低级别的 MySQL 接口。例如，通过直接访问 MySQL InnoDB 或 MyISAM 存储引擎，与使用 SQL 接口相比，您可能会获得显着的速度提升。

  同样地，对于使用 NDBCLUSTER 存储引擎的数据库，您可能希望调查可能使用 NDB API 的情况（请参阅 MySQL NDB Cluster API 开发人员指南）。

• 复制可以为某些操作提供性能优势。您可以将客户端检索分布在副本之间以分担负载。为了在备份时不减慢源的速度，您可以使用副本进行备份。请参阅第十九章，复制。

10.3 优化和索引

原文：dev.mysql.com/doc/refman/8.0/en/optimization-indexes.html

10.3.1 MySQL 如何使用索引

10.3.2 主键优化

10.3.3 空间索引优化

10.3.4 外键优化

10.3.5 列索引

10.3.6 多列索引

10.3.7 验证索引使用

10.3.8 InnoDB 和 MyISAM 索引统计收集

10.3.9 B-Tree 和 Hash 索引的比较

10.3.10 索引扩展的使用

10.3.11 优化器使用生成列索引

10.3.12 隐藏索引

10.3.13 降序索引

10.3.14 从时间戳列进行索引查找

提高 SELECT 操作性能的最佳方法是在查询中测试的一个或多个列上创建索引。索引条目就像指向表行的指针，允许查询快速确定哪些行符合 WHERE 子句中的条件，并检索这些行的其他列值。所有 MySQL 数据类型都可以被索引。

尽管为查询中使用的每个可能的列创建索引可能很诱人，但不必要的索引会浪费空间，也会浪费 MySQL 确定要使用哪些索引的时间。索引还会增加插入、更新和删除的成本，因为每个索引都必须更新。您必须找到正确的平衡，以使用最佳的索引集实现快速查询。

10.3.1 MySQL 如何使用索引

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

索引用于快速查找具有特定列值的行。没有索引，MySQL 必须从第一行开始，然后读取整个表以找到相关行。表越大，成本越高。如果表中有涉及的列的索引，MySQL 可以快速确定在数据文件中要查找的位置，而无需查看所有数据。这比顺序读取每一行要快得多。

大多数 MySQL 索引（PRIMARY KEY、UNIQUE、INDEX和FULLTEXT）存储在 B-trees 中。例外情况：空间数据类型的索引使用 R-trees；MEMORY表还支持 hash 索引；InnoDB对FULLTEXT索引使用倒排列表。

一般来说，索引的使用如下所述。哈希索引（用于MEMORY表中）的特性在第 10.3.9 节“B-Tree 和 Hash 索引的比较”中描述。

MySQL 在以下操作中使用索引：

• 快速查找与WHERE子句匹配的行。

• 要消除考虑的行。如果存在多个索引选择，MySQL 通常使用找到最少行数（最具选择性的索引）的索引。

• 如果表具有多列索引，则优化器可以使用索引的任何最左前缀来查找行。例如，如果在(col1, col2, col3)上有一个三列索引，则可以在(col1)、(col1, col2)和(col1, col2, col3)上进行索引搜索。有关更多信息，请参见第 10.3.6 节“多列索引”。

• 在执行连接时从其他表中检索行。如果声明为相同类型和大小的列，MySQL 可以更有效地使用列上的索引。在这种情况下，如果它们声明为相同大小，则VARCHAR和CHAR被视为相同。例如，VARCHAR(10)和CHAR(10)是相同大小，但VARCHAR(10)和CHAR(15)不是。

  对于非二进制字符串列之间的比较，两列应使用相同的字符集。例如，将utf8mb4列与latin1列进行比较会排除索引的使用。

  比较不同类型的列（例如将字符串列与时间或数字列进行比较）可能会阻止使用索引，如果值不能直接进行比较而需要转换。对于数字列中的给定值，例如1，它可能与字符串列中的任意数量的值相等，例如'1'，' 1'，'00001'或'01.e1'。这排除了对字符串列使用任何索引。

• 要查找特定索引列*key_col的MIN()或MAX()值。这是通过预处理器进行优化的，它会检查您是否在索引中使用了WHERE *key_part_N* = *constant*，其中key_col*之前的所有关键部分。在这种情况下，MySQL 对每个MIN()或MAX()表达式进行单个关键查找，并将其替换为常量。如果所有表达式都被常量替换，查询将立即返回。例如：

  SELECT MIN(*key_part2*),MAX(*key_part2*)
    FROM *tbl_name* WHERE *key_part1*=10;
  

• 要对表进行排序或分组，如果排序或分组是在可用索引的最左前缀上完成的（例如，ORDER BY *key_part1*, *key_part2*）。如果所有关键部分后面都跟着DESC，则按相反顺序读取关键字。 （或者，如果索引是降序索引，则按正向顺序读取关键字。）请参见 Section 10.2.1.16，“ORDER BY Optimization”，Section 10.2.1.17，“GROUP BY Optimization”和 Section 10.3.13，“Descending Indexes”。

• 在某些情况下，可以优化查询以在不查看数据行的情况下检索值。（为查询提供所有必要结果的索引称为覆盖索引。）如果查询仅使用了某个索引中包含的列，那么所选值可以从索引树中检索以提高速度：

  SELECT *key_part3* FROM *tbl_name*
    WHERE *key_part1*=1
  

对于小表或大表的查询，其中报表查询处理大部分或全部行，索引不那么重要。当查询需要访问大部分行时，顺序读取比通过索引逐个查找更快。顺序读取最小化磁盘寻址，即使查询不需要所有行。有关详细信息，请参见 Section 10.2.1.23，“Avoiding Full Table Scans”。

10.3.2 主键优化

原文：dev.mysql.com/doc/refman/8.0/en/primary-key-optimization.html

表的主键代表您在最关键的查询中使用的列或列集。它有一个关联的索引，用于快速查询性能。查询性能受NOT NULL优化的益处，因为它不能包含任何NULL值。使用InnoDB存储引擎，表数据被物理组织以便根据主键列或列进行超快速查找和排序。

如果您的表很大且重要，但没有明显的列或列集可用作主键，您可以创建一个带有自增值的单独列用作主键。这些唯一的 ID 可以在使用外键连接表时作为指向其他表中相应行的指针。

10.3.3 空间索引优化

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

MySQL 允许在NOT NULL几何值列上创建SPATIAL索引（参见第 13.4.10 节，“创建空间索引”）。优化器检查索引列的SRID属性，以确定用于比较的空间参考系统（SRS），并使用适合 SRS 的计算。 （在 MySQL 8.0 之前，优化器使用笛卡尔计算对SPATIAL索引值进行比较；如果列包含具有非笛卡尔SRID的值，则此类操作的结果是未定义的。）

为了使比较正常工作，SPATIAL索引中的每一列必须受到SRID限制。也就是说，列定义必须包含显式的SRID属性，并且所有列值必须具有相同的SRID。

优化器仅考虑具有SRID限制的列的SPATIAL索引：

• 对于受笛卡尔SRID限制的列上的索引，使笛卡尔边界框计算成为可能。

• 对于受地理SRID限制的列上的索引，使地理边界框计算成为可能。

优化器忽略没有SRID属性（因此不受SRID限制）的列上的SPATIAL索引。MySQL 仍然维护这样的索引，如下所示：

• 它们用于表修改（INSERT，UPDATE，DELETE等）。即使列可能包含混合的笛卡尔和地理值，更新也会像索引是笛卡尔的一样进行。

• 它们仅用于向后兼容性（例如，在 MySQL 5.7 中执行转储并在 MySQL 8.0 中恢复的能力）。因为SPATIAL索引在不受SRID限制的列上对优化器没有用处，因此应修改每个这样的列：

  • 验证列中的所有值是否具有相同的SRID。要确定几何列*col_name*中包含的SRID，请使用以下查询：

    SELECT DISTINCT ST_SRID(*col_name*) FROM *tbl_name*;
    

    如果查询返回多行，则该列包含混合SRID的值。在这种情况下，修改其内容以使所有值具有相同的SRID。

  • 重新定义列以具有显式的SRID属性。

  • 重新创建SPATIAL索引。

10.3.4 外键优化

原文：dev.mysql.com/doc/refman/8.0/en/foreign-key-optimization.html

如果一张表有很多列，并且您查询许多不同的列组合，将不经常使用的数据拆分到每个只有几列的单独表中，然后通过从主表复制数值 ID 列将它们与主表关联起来可能是有效的。这样，每个小表都可以有一个用于快速查找其数据的主键，并且您可以使用连接操作仅查询您需要的列集。根据数据的分布方式，查询可能执行更少的 I/O 并占用更少的缓存内存，因为相关列在磁盘上紧密打包在一起。（为了最大化性能，查询尝试从磁盘读取尽可能少的数据块；只有几列的表可以在每个数据块中容纳更多行。）

10.3.5 列索引

原文：dev.mysql.com/doc/refman/8.0/en/column-indexes.html

最常见的索引类型涉及单个列，在数据结构中存储该列的值的副本，允许快速查找具有相应列值的行。B 树数据结构让索引可以快速找到特定值、一组值或一系列值，对应于=、>、≤、BETWEEN、IN等操作符，在WHERE子句中使用。

每个表的最大索引数和最大索引长度由存储引擎定义。请参见第十七章，“InnoDB 存储引擎”和第十八章，“替代存储引擎”。所有存储引擎至少支持每个表 16 个索引和至少 256 字节的总索引长度。大多数存储引擎具有更高的限制。

有关列索引的更多信息，请参见第 15.1.15 节，“CREATE INDEX 语句”。

• 索引前缀

• 全文索引

• 空间索引

• MEMORY 存储引擎中的索引

索引前缀

使用*col_name*(*N*)语法在字符串列的索引规范中，您可以创建一个仅使用列的前 N 个字符的索引。以这种方式仅对列值的前缀进行索引可以使索引文件变得更小。当您对BLOB或TEXT列进行索引时，必须为索引指定前缀长度。例如：

CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));

对于使用REDUNDANT或COMPACT行格式的InnoDB表，前缀最长可达 767 字节。对于使用DYNAMIC或COMPRESSED行格式的InnoDB表，前缀长度限制为 3072 字节。对于 MyISAM 表，前缀长度限制为 1000 字节。

注意

前缀限制以字节为单位，而在CREATE TABLE、ALTER TABLE和CREATE INDEX语句中，前缀长度被解释为非二进制字符串类型（CHAR、VARCHAR、TEXT）的字符数和二进制字符串类型的字节数（BINARY、VARBINARY、BLOB）。在为使用多字节字符集的非二进制字符串列指定前缀长度时，请考虑这一点。

如果搜索词超过索引前缀长度，则使用索引来排除不匹配的行，然后检查剩余行以查找可能的匹配项。

有关索引前缀的更多信息，请参见 Section 15.1.15, “CREATE INDEX Statement”。

全文索引

FULLTEXT索引用于全文搜索。只有InnoDB和MyISAM存储引擎支持FULLTEXT索引，且仅适用于CHAR、VARCHAR和TEXT列。索引始终在整个列上进行，不支持列前缀索引。有关详细信息，请参见 Section 14.9, “Full-Text Search Functions”。

优化应用于针对单个InnoDB表的某些类型的FULLTEXT查询。具有以下特征的查询特别高效：

• 仅返回文档 ID 或文档 ID 和搜索排名的FULLTEXT查询。

• 将匹配行按得分降序排序并应用LIMIT子句以获取前 N 个匹配行的FULLTEXT查询。要应用此优化，WHERE子句中不能有WHERE子句，且仅有一个按降序排序的ORDER BY子句。

• 仅检索与搜索词匹配的行的COUNT(*)值的FULLTEXT查询，没有额外的WHERE子句。将WHERE子句编码为WHERE MATCH(*text*) AGAINST ('*other_text*')，不包含任何> 0比较运算符。

对于包含全文表达式的查询，MySQL 在查询执行的优化阶段评估这些表达式。优化器不仅查看全文表达式并进行估计，而且在制定执行计划的过程中实际评估它们。

这种行为的一个影响是，对于全文查询，EXPLAIN 通常比对于在优化阶段不进行表达式评估的非全文查询慢。

对于全文查询，EXPLAIN 可能会在 Extra 列中显示 选择表已优化，这是由于匹配发生在优化期间；在这种情况下，后续执行时不需要访问表。

空间索引

您可以在空间数据类型上创建索引。MyISAM 和 InnoDB 支持空间类型的 R 树索引。其他存储引擎使用 B 树来索引空间类型（除了 ARCHIVE 不支持空间类型索引）。

内存存储引擎中的索引

MEMORY 存储引擎默认使用 HASH 索引，但也支持 BTREE 索引。

10.3.6 多列索引

原文：dev.mysql.com/doc/refman/8.0/en/multiple-column-indexes.html

MySQL 可以创建复合索引（即，多列索引）。一个索引最多可以包含 16 列。对于某些数据类型，您可以对列的前缀进行索引（参见 第 10.3.5 节，“列索引”）。

MySQL 可以为测试索引中的所有列或仅测试第一列、前两列、前三列等的查询使用多列索引。如果您在索引定义中以正确的顺序指定列，单个复合索引可以加速对同一表的多种查询。

多列索引可以被视为排序数组，其行包含通过连接索引列的值创建的值。

注意

作为复合索引的替代方案，您可以引入一个根据其他列信息“哈希”生成的列。如果此列较短、相对唯一且已建立索引，则可能比对许多列进行“宽”索引更快。在 MySQL 中，使用此额外列非常容易：

SELECT * FROM *tbl_name*
  WHERE *hash_col*=MD5(CONCAT(*val1*,*val2*))
  AND *col1*=*val1* AND *col2*=*val2*;

假设表具有以下规范：

CREATE TABLE test (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id),
    INDEX name (last_name,first_name)
);

name 索引是在 last_name 和 first_name 列上的索引。该索引可用于查询中指定已知范围值的 last_name 和 first_name 值的组合的查找。它也可用于仅指定 last_name 值的查询，因为该列是索引的最左前缀（如本节后面所述）。因此，name 索引用于以下查询中的查找：

SELECT * FROM test WHERE last_name='Jones';

SELECT * FROM test
  WHERE last_name='Jones' AND first_name='John';

SELECT * FROM test
  WHERE last_name='Jones'
  AND (first_name='John' OR first_name='Jon');

SELECT * FROM test
  WHERE last_name='Jones'
  AND first_name >='M' AND first_name < 'N';

然而，name 索引不用于以下查询中的查找：

SELECT * FROM test WHERE first_name='John';

SELECT * FROM test
  WHERE last_name='Jones' OR first_name='John';

假设您发出以下 SELECT 语句：

SELECT * FROM *tbl_name*
  WHERE col1=*val1* AND col2=*val2*;

如果在 col1 和 col2 上存在多列索引，则可以直接获取适当的行。如果在 col1 和 col2 上存在单列索引，则优化器尝试使用索引合并优化（参见 第 10.2.1.3 节，“索引合并优化”），或者尝试通过决定哪个索引排除更多行并使用该索引来获取行。

如果表具有多列索引，则优化器可以使用索引的任何最左前缀来查找行。例如，如果您在 (col1, col2, col3) 上有一个三列索引，则可以在 (col1)、(col1, col2) 和 (col1, col2, col3) 上进行索引搜索。

如果列不形成索引的最左前缀，则 MySQL 无法使用索引进行查找。假设您有以下所示的 SELECT 语句：

SELECT * FROM *tbl_name* WHERE col1=*val1*;
SELECT * FROM *tbl_name* WHERE col1=*val1* AND col2=*val2*;

SELECT * FROM *tbl_name* WHERE col2=*val2*;
SELECT * FROM *tbl_name* WHERE col2=*val2* AND col3=*val3*;

如果在(col1, col2, col3)上存在索引，只有前两个查询会使用该索引。第三个和第四个查询涉及索引列，但不使用索引进行查找，因为(col2)和(col2, col3)不是(col1, col2, col3)的最左前缀。

10.3.7 验证索引使用情况

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

始终检查您的所有查询是否真正使用了您在表中创建的索引。使用EXPLAIN语句，如第 10.8.1 节“使用 EXPLAIN 优化查询”中所述。

10.3.8 InnoDB 和 MyISAM 索引统计收集

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

存储引擎收集关于表的统计信息供优化器使用。表统计信息基于值组，其中值组是具有相同键前缀值的行集。对于优化器而言，一个重要的统计量是平均值组大小。

MySQL 使用平均值组大小的方式如下：

• 估计每次ref访问必须读取多少行

• 估计部分连接产生多少行，即由形式为的操作产生的行数

  (...) JOIN *tbl_name* ON *tbl_name*.*key* = *expr*
  

随着索引的平均值组大小增加，索引对这两个目的的用处减少，因为每次查找的平均行数增加：为了优化目的，最好是每个索引值都针对表中的少量行。当给定的索引值产生大量行时，索引的用处减少，MySQL 不太可能使用它。

平均值组大小与表基数有关，即值组的数量。SHOW INDEX语句显示基于*N/S的基数值，其中N是表中的行数，S*是平均值组大小。该比率产生表中值组的近似数量。

对于基于<=>比较运算符的连接，NULL与任何其他值没有区别：NULL <=> NULL，就像对于任何其他*N*，*N* <=> *N*一样。

然而，对于基于=运算符的连接，NULL与非NULL值不同：当*expr1或expr2（或两者）为NULL时，*expr1* = *expr2*不成立。这影响了形式为*tbl_name.key* = *expr*的比较的ref访问：如果expr*的当前值为NULL，则 MySQL 不访问表，因为比较不可能为真。

对于=比较，表中有多少NULL值并不重要。为了优化，相关值是非NULL值组的平均大小。然而，MySQL 目前不允许收集或使用该平均大小。

对于InnoDB和MyISAM表，您可以通过innodb_stats_method和myisam_stats_method系统变量来控制表统计信息的收集。这些变量有三个可能的值，区别如下：

• 当变量设置为nulls_equal时，所有NULL值被视为相同（即，它们都形成一个单一的值组）。

  如果NULL值组大小远高于平均非NULL值组大小，这种方法会使平均值组大小向上偏移。这会使索引在优化器看来比实际上对查找非NULL值的连接不那么有用。因此，nulls_equal方法可能会导致优化器在应该使用索引进行 ref 访问时不使用索引。

• 当变量设置为nulls_unequal时，NULL值不被视为相同。相反，每个NULL值形成一个大小为 1 的单独值组。

  如果有许多NULL值，这种方法会使平均值组大小向下偏移。如果平均非NULL值组大小较大，将每个NULL值计为大小为 1 的组会导致优化器高估用于查找非NULL值的连接的索引的价值。因此，nulls_unequal方法可能会导致优化器在其他方法更好的情况下使用此索引进行 ref 查找。

• 当变量设置为nulls_ignored时，NULL值被忽略。

如果你倾向于使用许多使用<=>而不是=的连接，NULL值在比较中并不特殊，一个NULL等于另一个NULL。在这种情况下，nulls_equal是适当的统计方法。

innodb_stats_method 系统变量具有全局值；myisam_stats_method 系统变量具有全局值和会话值。设置全局值会影响相应存储引擎的表的统计信息收集。设置会话值仅影响当前客户端连接的统计信息收集。这意味着你可以通过设置 myisam_stats_method 的会话值，强制重新生成表的统计信息。

要重新生成MyISAM表的统计信息，你可以使用以下任何方法：

• 执行 myisamchk --stats_method=method_name --analyze

• 更改表以使其统计信息过时（例如，插入一行然后删除它），然后设置 myisam_stats_method 并发出 ANALYZE TABLE 语句

关于使用 innodb_stats_method 和 myisam_stats_method 的一些建议：

• 您可以强制显式收集表统计信息，就像刚才描述的那样。但是，MySQL 也可能自动收集统计信息。例如，如果在执行表的语句过程中，其中一些语句修改了表，MySQL 可能会收集统计信息。（例如，这可能发生在大量插入或删除，或一些ALTER TABLE语句中。）如果发生这种情况，统计信息将使用innodb_stats_method或myisam_stats_method在那时的任何值进行收集。因此，如果您使用一种方法收集统计信息，但在以后自动收集表统计信息时系统变量设置为另一种方法，则将使用另一种方法。

• 没有办法知道为给定表生成统计信息时使用了哪种方法。

• 这些变量仅适用于InnoDB和MyISAM表。其他存储引擎只有一种收集表统计信息的方法。通常它更接近nulls_equal方法。

10.3.9 B-树和哈希索引的比较

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

理解 B-树和哈希数据结构可以帮助预测不同查询在使用这些数据结构的不同存储引擎上的性能表现，特别是对于允许选择 B-树或哈希索引的 MEMORY 存储引擎。

• B-树索引特性

• 哈希索引特性

B-树索引特性

B-树索引可用于使用 =, >, >=, <, <=, 或 BETWEEN 操作符的表达式中的列比较。如果 LIKE 的参数是不以通配符字符开头的常量字符串，则也可用于 LIKE 比较。例如，以下 SELECT 语句使用索引：

SELECT * FROM *tbl_name* WHERE *key_col* LIKE 'Patrick%';
SELECT * FROM *tbl_name* WHERE *key_col* LIKE 'Pat%_ck%';

在第一条语句中，只考虑 'Patrick' <= *key_col* < 'Patricl' 的行。在第二条语句中，只考虑 'Pat' <= *key_col* < 'Pau' 的行。

以下 SELECT 语句不使用索引：

SELECT * FROM *tbl_name* WHERE *key_col* LIKE '%Patrick%';
SELECT * FROM *tbl_name* WHERE *key_col* LIKE *other_col*;

在第一条语句中，LIKE 值以通配符字符开头。在第二条语句中，LIKE 值不是一个常量。

如果使用 ... LIKE '%*string*%'，且 string 长度超过三个字符，MySQL 将使用 Turbo Boyer-Moore 算法初始化字符串的模式，然后使用该模式更快地执行搜索。

使用 *col_name* IS NULL 进行搜索时，如果 col_name 被索引，则会使用索引。

任何不跨越 WHERE 子句中的所有 AND 级别的索引都不会被用来优化查询。换句话说，为了能够使用索引，索引的前缀必须在每个 AND 组中被使用。

以下 WHERE 子句使用索引：

... WHERE *index_part1*=1 AND *index_part2*=2 AND *other_column*=3

    /* *index* = 1 OR *index* = 2 */
... WHERE *index*=1 OR A=10 AND *index*=2

    /* optimized like "*index_part1*='hello'" */
... WHERE *index_part1*='hello' AND *index_part3*=5

    /* Can use index on *index1* but not on *index2* or *index3* */
... WHERE *index1*=1 AND *index2*=2 OR *index1*=3 AND *index3*=3;

这些 WHERE 子句 不 使用索引：

 /* *index_part1* is not used */
... WHERE *index_part2*=1 AND *index_part3*=2

    /*  Index is not used in both parts of the WHERE clause  */
... WHERE *index*=1 OR A=10

    /* No index spans all rows  */
... WHERE *index_part1*=1 OR *index_part2*=10

有时候 MySQL 即使有索引也不会使用。发生这种情况的一个情况是，优化器估计使用索引需要 MySQL 访问表中非常大比例的行。在这种情况下，表扫描可能会快得多，因为它需要更少的查找。然而，如果这样的查询使用LIMIT只检索一些行，MySQL 仍然会使用索引，因为它可以更快地找到要返回的少数行。

哈希索引特性

哈希索引具有与刚才讨论的索引略有不同的特性：

• 它们仅用于使用=或<=>运算符的相等比较（但非常快）。它们不用于查找一系列值的比较运算符，如<。依赖这种单值查找的系统被称为“键值存储”；为了在这类应用中使用 MySQL，请尽可能使用哈希索引。

• 优化器无法使用哈希索引加速ORDER BY操作。（这种类型的索引不能用于按顺序搜索下一个条目。）

• MySQL 无法准确确定两个值之间有多少行（这是范围优化器用来决定使用哪个索引的）。如果你将一个MyISAM或InnoDB表更改为哈希索引的MEMORY表，这可能会影响一些查询。

• 只有整个键才能用于搜索行。（使用 B 树索引，任何键的最左前缀都可以用于查找行。）

10.3.10 索引扩展的使用

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

InnoDB会自动通过附加主键列来扩展每个辅助索引。考虑这个表定义：

CREATE TABLE t1 (
  i1 INT NOT NULL DEFAULT 0,
  i2 INT NOT NULL DEFAULT 0,
  d DATE DEFAULT NULL,
  PRIMARY KEY (i1, i2),
  INDEX k_d (d)
) ENGINE = InnoDB;

这个表在列(i1, i2)上定义了主键。它还在列(d)上定义了一个辅助索引k_d，但在内部InnoDB扩展了这个索引，并将其视为列(d, i1, i2)。

优化器在确定如何以及是否使用该索引时，会考虑扩展辅助索引的主键列。这可以导致更高效的查询执行计划和更好的性能。

优化器可以使用扩展的辅助索引进行ref、range和index_merge索引访问，进行松散索引扫描访问，进行连接和排序优化，以及进行MIN()/MAX()优化。

以下示例展示了优化器是否使用扩展辅助索引会如何影响执行计划。假设t1被这些行填充：

INSERT INTO t1 VALUES
(1, 1, '1998-01-01'), (1, 2, '1999-01-01'),
(1, 3, '2000-01-01'), (1, 4, '2001-01-01'),
(1, 5, '2002-01-01'), (2, 1, '1998-01-01'),
(2, 2, '1999-01-01'), (2, 3, '2000-01-01'),
(2, 4, '2001-01-01'), (2, 5, '2002-01-01'),
(3, 1, '1998-01-01'), (3, 2, '1999-01-01'),
(3, 3, '2000-01-01'), (3, 4, '2001-01-01'),
(3, 5, '2002-01-01'), (4, 1, '1998-01-01'),
(4, 2, '1999-01-01'), (4, 3, '2000-01-01'),
(4, 4, '2001-01-01'), (4, 5, '2002-01-01'),
(5, 1, '1998-01-01'), (5, 2, '1999-01-01'),
(5, 3, '2000-01-01'), (5, 4, '2001-01-01'),
(5, 5, '2002-01-01');

现在考虑这个查询：

EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'

执行计划取决于是否使用了扩展索引。

当优化器不考虑索引扩展时，它将索引k_d视为仅(d)。查询的EXPLAIN结果如下：

mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ref
possible_keys: PRIMARY,k_d
          key: k_d
      key_len: 4
          ref: const
         rows: 5
        Extra: Using where; Using index

当优化器考虑索引扩展时，它将k_d视为(d, i1, i2)。在这种情况下，它可以使用最左边的索引前缀(d, i1)来生成更好的执行计划：

mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ref
possible_keys: PRIMARY,k_d
          key: k_d
      key_len: 8
          ref: const,const
         rows: 1
        Extra: Using index

在两种情况下，key指示优化器使用了辅助索引k_d，但EXPLAIN输出显示了使用扩展索引带来的这些改进：

• key_len从 4 字节增加到 8 字节，表示键查找使用了列d和i1，而不仅仅是d。

• ref值从const变为const,const，因为键查找使用了两个键部分，而不是一个。

• rows计数从 5 减少到 1，表示InnoDB应该需要检查更少的行来生成结果。

• Extra值从Using where; Using index变为Using index。这意味着可以仅使用索引读取行，而不需要查询数据行中的列。

使用扩展索引的优化器行为差异也可以通过SHOW STATUS看到：

FLUSH TABLE t1;
FLUSH STATUS;
SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';
SHOW STATUS LIKE 'handler_read%'

上述语句包括FLUSH TABLES和FLUSH STATUS来刷新表缓存和清除状态计数器。

没有索引扩展，SHOW STATUS产生以下结果：

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 5     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

使用索引扩展，SHOW STATUS 会产生这样的结果。Handler_read_next 的值从 5 减少到 1，表明索引的使用更加高效：

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 1     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

optimizer_switch 系统变量的 use_index_extensions 标志允许控制优化器在确定如何使用 InnoDB 表的次要索引时是否考虑主键列。默认情况下，use_index_extensions 是启用的。要检查禁用索引扩展是否可以提高性能，请使用以下语句：

SET optimizer_switch = 'use_index_extensions=off';

优化器对索引扩展的使用受到索引中关键部分数量（16）和最大键长度（3072 字节）的通常限制。

10.3.11 优化器对生成列索引的使用

原文：dev.mysql.com/doc/refman/8.0/en/generated-column-index-optimizations.html

MySQL 支持对生成列创建索引。例如：

CREATE TABLE t1 (f1 INT, gc INT AS (f1 + 1) STORED, INDEX (gc));

生成列gc被定义为表达式f1 + 1。该列也被索引，优化器在执行计划构建过程中可以考虑该索引。在以下查询中，WHERE子句引用了gc，优化器会考虑该列上的索引是否产生更有效的计划：

SELECT * FROM t1 WHERE gc > 9;

优化器可以使用生成列上的索引生成执行计划，即使查询中没有直接按名称引用这些列。如果WHERE、ORDER BY或GROUP BY子句引用与某个索引生成列的定义匹配的表达式，则会发生这种情况。以下查询并不直接引用gc，但确实使用与gc定义匹配的表达式：

SELECT * FROM t1 WHERE f1 + 1 > 9;

优化器识别到表达式f1 + 1与gc的定义匹配，并且gc被索引，因此在执行计划构建过程中考虑了该索引。您可以使用EXPLAIN查看这一点：

mysql> EXPLAIN SELECT * FROM t1 WHERE f1 + 1 > 9\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: gc
          key: gc
      key_len: 5
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using index condition

实际上，优化器已经用与表达式匹配的生成列名称替换了表达式f1 + 1。这也可以在由SHOW WARNINGS显示的扩展EXPLAIN信息中重新编写的查询中看到：

mysql> SHOW WARNINGS\G
*************************** 1\. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`gc`
         AS `gc` from `test`.`t1` where (`test`.`t1`.`gc` > 9)

优化器对生成列索引的使用受到以下限制和条件的约束：

• 要使查询表达式与生成列定义匹配，表达式必须完全相同，并且必须具有相同的结果类型。例如，如果生成列表达式是f1 + 1，则如果查询使用1 + f1，或者如果将f1 + 1（整数表达式）与字符串进行比较，则优化器不会识别匹配。

• 优化适用于这些运算符：=, <, <=, >, >=, BETWEEN和IN()。

  对于除了BETWEEN和IN()之外的运算符，任一操作数都可以被匹配的生成列替换。对于BETWEEN和IN()，只有第一个参数可以被匹配的生成列替换，其他参数必须具有相同的结果类型。目前不支持涉及 JSON 值的比较的BETWEEN和IN()。

• 生成列必须定义为包含至少一个函数调用或前一项提到的运算符之一的表达式。表达式不能仅由对另一列的简单引用组成。例如，gc INT AS (f1) STORED 仅由列引用组成，因此对 gc 的索引不会被考虑。

• 对于将字符串与从返回带引号的字符串的 JSON 函数计算值的索引生成列进行比较，需要在列定义中使用JSON_UNQUOTE()来去除函数值中的额外引号。（对于直接将字符串与函数结果进行比较，JSON 比较器会处理引号的移除，但这不适用于索引查找。）例如，不要像这样编写列定义：

  doc_name TEXT AS (JSON_EXTRACT(jdoc, '$.name')) STORED
  

  要像这样编写：

  doc_name TEXT AS (JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name'))) STORED
  

  使用后一种定义，优化器可以检测到这两个比较的匹配：

  ... WHERE JSON_EXTRACT(jdoc, '$.name') = '*some_string*' ...
  ... WHERE JSON_UNQUOTE(JSON_EXTRACT(jdoc, '$.name')) = '*some_string*' ...
  

  在列定义中没有JSON_UNQUOTE()，优化器只会检测到这些比较中的第一个匹配。

• 如果优化器选择了错误的索引，可以使用索引提示来禁用它，并强制优化器做出不同的选择。