MySQL8-中文参考-十七-

MySQL8 中文参考（十七）

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

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

10.2.1.17 GROUP BY 优化

最一般的满足GROUP BY子句的方法是扫描整个表，并创建一个新的临时表，其中每个组的所有行都是连续的，然后使用这个临时表来发现组并应用聚合函数（如果有的话）。在某些情况下，MySQL 能够比这做得更好，通过使用索引访问来避免创建临时表。

使用索引进行GROUP BY的最重要的前提条件是所有GROUP BY列引用来自同一索引的属性，并且索引按顺序存储其键（例如BTREE索引是这样的，但HASH索引不是）。临时表的使用是否可以被索引访问替代还取决于查询中使用了索引的哪些部分，为这些部分指定的条件以及选择的聚合函数。

有两种通过索引访问执行GROUP BY查询的方法，如下节所述。第一种方法将分组操作与所有范围谓词（如果有的话）一起应用。第二种方法首先执行范围扫描，然后对结果元组进行分组。

松散索引扫描
紧凑索引扫描

在某些条件下，即使没有GROUP BY，也可以使用松散索引扫描。请参阅跳过扫描范围访问方法。

松散索引扫描

处理GROUP BY最有效的方法是直接使用索引检索分组列。通过这种访问方法，MySQL 使用某些索引类型的属性，即键是有序的（例如BTREE）。这种属性使得可以在索引中查找组，而无需考虑满足所有WHERE条件的所有键。这种访问方法只考虑索引中的一部分键，因此被称为松散索引扫描。当没有WHERE子句时，松散索引扫描读取与组数相同的键，这可能比所有键的数量要小得多。如果WHERE子句包含范围谓词（请参阅第 10.8.1 节“使用 EXPLAIN 优化查询”中对range连接类型的讨论），松散索引扫描查找满足范围条件的每个组的第一个键，然后再次读取最小可能数量的键。这在以下条件下是可能的：

查询只涉及单个表。
GROUP BY仅命名了构成索引最左边前缀的列，没有其他列。（如果查询中有DISTINCT子句而不是GROUP BY，则所有不同的属性都指向构成索引最左边前缀的列。）例如，如果表t1在(c1,c2,c3)上有索引，如果查询中有GROUP BY c1, c2，则可以应用松散索引扫描。如果查询中有GROUP BY c2, c3（列不是最左边前缀）或GROUP BY c1, c2, c4（c4不在索引中），则不适用。
选择列表中使用的唯一聚合函数（如果有）是MIN()和MAX()，并且它们都引用相同的列。该列必须在索引中，并且必须紧随GROUP BY中的列之后。
查询中除了GROUP BY引用的索引部分外，其他索引部分必须是常量（即，它们必须与常量相等），除了MIN()或MAX()函数的参数。
对于索引中的列，必须对完整列值建立索引，而不仅仅是前缀。例如，对于c1 VARCHAR(20), INDEX (c1(10))，索引仅使用c1值的前缀，不能用于松散索引扫描。

如果查询适用于松散索引扫描，则EXPLAIN输出中的Extra列显示Using index for group-by。

假设在表t1(c1,c2,c3,c4)上有索引idx(c1,c2,c3)。可以对以下查询使用松散索引扫描访问方法：

SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT c1, c2 FROM t1;
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 < *const* GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > *const* GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < *const* GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = *const* GROUP BY c1, c2;

以下查询不能使用这种快速选择方法执行，原因如下：

存在除了MIN()或MAX()之外的其他聚合函数：
```
SELECT c1, SUM(c2) FROM t1 GROUP BY c1;
```
GROUP BY子句中的列不构成索引的最左边前缀：
```
SELECT c1, c2 FROM t1 GROUP BY c2, c3;
```
查询引用了GROUP BY部分之后的键的一部分，且没有与常量相等的情况：
```
SELECT c1, c3 FROM t1 GROUP BY c1, c2;
```
如果查询包括WHERE c3 = *const*，则可以使用松散索引扫描。

松散索引扫描访问方法可以应用于选择列表中的其他形式的聚合函数引用，除了已经支持的MIN()和MAX()引用：

支持AVG(DISTINCT)、SUM(DISTINCT)和COUNT(DISTINCT)。AVG(DISTINCT)和SUM(DISTINCT)接受单个参数。COUNT(DISTINCT)可以有多个列参数。
查询中不能包含GROUP BY或DISTINCT子句。
仍然适用前面描述的松散索引扫描限制。

假设在表t1(c1,c2,c3,c4)上有一个索引idx(c1,c2,c3)。松散索引扫描访问方法可用于以下查询：

SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;

SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;

紧凑索引扫描

紧凑索引扫描可以是完整索引扫描或范围索引扫描，具体取决于查询条件。

当不满足松散索引扫描的条件时，仍然可能避免为GROUP BY查询创建临时表。如果WHERE子句中存在范围条件，则此方法仅读取满足这些条件的键。否则，它执行索引扫描。因为此方法在WHERE子句定义的每个范围中读取所有键，或者如果没有范围条件则扫描整个索引，所以称为紧凑索引扫描。使用紧凑索引扫描，只有在找到满足范围条件的所有键之后才执行分组操作。

要使这种方法起作用，查询中涉及到GROUP BY键的所有列都需要有一个常量相等条件，该条件位于GROUP BY键的前部或中间。相等条件中的常量填补了搜索键中的任何“间隙”，从而可以形成索引的完整前缀。然后可以使用这些索引前缀进行索引查找。如果GROUP BY结果需要排序，并且可以形成索引的前缀搜索键，MySQL 还会避免额外的排序操作，因为在有序索引中使用前缀搜索已经按顺序检索了所有键。

假设在表t1(c1,c2,c3,c4)上有一个索引idx(c1,c2,c3)。前面描述的松散索引扫描访问方法无法处理以下查询，但仍可使用紧凑索引扫描访问方法。

GROUP BY中存在一个间隙，但被条件c2 = 'a'覆盖：
```
SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;
```
GROUP BY不是以键的第一部分开头，但有一个条件为该部分提供了一个常量：
```
SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;
```

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

10.2.1.18 DISTINCT 优化

DISTINCT 与 ORDER BY 结合在许多情况下需要一个临时表。

由于 DISTINCT 可能使用 GROUP BY，了解 MySQL 如何处理 ORDER BY 或 HAVING 子句中不属于所选列的列。请参见 Section 14.19.3, “MySQL Handling of GROUP BY”。

在大多数情况下，DISTINCT 子句可以被视为 GROUP BY 的特例。例如，以下两个查询是等效的：

SELECT DISTINCT c1, c2, c3 FROM t1
WHERE c1 > *const*;

SELECT c1, c2, c3 FROM t1
WHERE c1 > *const* GROUP BY c1, c2, c3;

由于这种等价性，适用于 GROUP BY 查询的优化也可以应用于带有 DISTINCT 子句的查询。因此，有关 DISTINCT 查询的优化可能性的更多详细信息，请参见 Section 10.2.1.17, “GROUP BY Optimization”。

当将 LIMIT *row_count* 与 DISTINCT 结合时，MySQL 会在找到 row_count 个唯一行后停止。

如果在查询中没有使用所有表中的列，MySQL 会在找到第一个匹配项后停止扫描任何未使用的表。在以下情况下，假设 t1 在 t2 之前被使用（您可以通过 EXPLAIN 进行检查），MySQL 在找到 t2 的第一行后（对于 t1 中的任何特定行）停止从 t2 中读取：

SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a;

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

10.2.1.19 LIMIT 查询优化

如果只需要结果集中的指定数量的行，请在查询中使用LIMIT子句，而不是获取整个结果集并丢弃额外的数据。

MySQL 有时会优化具有LIMIT *row_count*子句而没有HAVING子句的查询：

如果你使用LIMIT只选择了少数几行，MySQL 在某些情况下会使用索引，而通常情况下它更倾向于进行全表扫描。
如果将LIMIT *row_count*与ORDER BY结合使用，MySQL 会在找到排序结果的前row_count行后停止排序，而不是对整个结果进行排序。如果使用索引进行排序，这是非常快的。如果必须进行文件排序，那么在找到不带LIMIT子句的查询的所有匹配行之前，将选择大多数或全部行，并对它们进行排序，然后才找到前row_count行。在找到初始行之后，MySQL 不会对结果集的任何剩余部分进行排序。

这种行为的一种表现是，带有和不带有LIMIT的ORDER BY查询可能以不同的顺序返回行，如本节后面所述。
如果将LIMIT *row_count*与DISTINCT结合使用，MySQL 会在找到row_count个唯一行后停止。
在某些情况下，GROUP BY可以通过按顺序读取索引（或对索引进行排序），然后计算摘要直到索引值发生变化来解决。在这种情况下，LIMIT *row_count*不会计算任何不必要的GROUP BY值。
一旦 MySQL 向客户端发送了所需数量的行，除非你使用SQL_CALC_FOUND_ROWS，否则它会中止查询。在这种情况下，可以使用SELECT FOUND_ROWS()检索行数。参见第 14.15 节，“信息函数”。
LIMIT 0会快速返回一个空集。这对于检查查询的有效性很有用。它还可以用于在使用使结果集元数据可用的 MySQL API 的应用程序中获取结果列的类型。使用mysql客户端程序，你可以使用--column-type-info选项来显示结果列类型。
如果服务器使用临时表来解析查询，它会使用LIMIT *row_count*子句来计算所需的空间。
如果未对ORDER BY使用索引但同时存在LIMIT子句，优化器可能能够避免使用合并文件，并使用内存中的filesort操作在内存中对行进行排序。

如果多行在ORDER BY列中具有相同的值，服务器可以自由地以任何顺序返回这些行，并且可能根据整体执行计划而有所不同。换句话说，这些行的排序顺序对于非排序列是不确定的。

影响执行计划的一个因素是LIMIT，因此带有和不带有LIMIT的ORDER BY查询可能以不同的顺序返回行。考虑这个查询，它按category列排序，但对于id和rating列是不确定的：

mysql> SELECT * FROM ratings ORDER BY category;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

包含LIMIT可能会影响每个category值内行的顺序。例如，这是一个有效的查询结果：

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  4 |        2 |    3.5 |
|  3 |        2 |    3.7 |
|  6 |        2 |    3.5 |
+----+----------+--------+

在每种情况下，行都按照ORDER BY列排序，这是 SQL 标准要求的全部内容。

如果确保带有和不带有LIMIT时返回相同的行顺序很重要，请在ORDER BY子句中包含额外的列，以使顺序确定。例如，如果id值是唯一的，您可以通过像这样排序来使给定category值的行按id顺序显示：

mysql> SELECT * FROM ratings ORDER BY category, id;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
|  2 |        3 |    5.0 |
|  7 |        3 |    2.7 |
+----+----------+--------+

mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;
+----+----------+--------+
| id | category | rating |
+----+----------+--------+
|  1 |        1 |    4.5 |
|  5 |        1 |    3.2 |
|  3 |        2 |    3.7 |
|  4 |        2 |    3.5 |
|  6 |        2 |    3.5 |
+----+----------+--------+

对于带有ORDER BY或GROUP BY以及LIMIT子句的查询，优化器会尝试默认选择有序索引，以加快查询执行速度。在 MySQL 8.0.21 之前，即使在使用其他优化可能更快的情况下，也无法覆盖此行为。从 MySQL 8.0.21 开始，可以通过将optimizer_switch系统变量的prefer_ordering_index标志设置为off来关闭此优化。

示例：首先我们创建并填充一个表t，如下所示：

# Create and populate a table t:

mysql> CREATE TABLE t (
 ->     id1 BIGINT NOT NULL,
 ->     id2 BIGINT NOT NULL,
 ->     c1 VARCHAR(50) NOT NULL,
 ->     c2 VARCHAR(50) NOT NULL,
 ->  PRIMARY KEY (id1),
 ->  INDEX i (id2, c1)
 -> );

# [Insert some rows into table t - not shown]

验证prefer_ordering_index标志是否已启用：

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

由于以下查询有一个LIMIT子句，我们期望它使用有序索引，如果可能的话。在这种情况下，正如我们从EXPLAIN输出中看到的那样，它使用了表的主键。

mysql> EXPLAIN SELECT c2 FROM t
 ->     WHERE id2 > 3
 ->     ORDER BY id1 ASC LIMIT 2\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: index
possible_keys: i
          key: PRIMARY
      key_len: 8
          ref: NULL
         rows: 2
     filtered: 70.00
        Extra: Using where

现在我们禁用prefer_ordering_index标志，并重新运行相同的查询；这次它使用索引i（其中包括WHERE子句中使用的id2列），以及一个文件排序：

mysql> SET optimizer_switch = "prefer_ordering_index=off";

mysql> EXPLAIN SELECT c2 FROM t
 ->     WHERE id2 > 3
 ->     ORDER BY id1 ASC LIMIT 2\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: range
possible_keys: i
          key: i
      key_len: 8
          ref: NULL
         rows: 14
     filtered: 100.00
        Extra: Using index condition; Using filesort

参见第 10.9.2 节，“可切换优化”。

原文：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()，优化器只会检测到这些比较中的第一个匹配。
如果优化器选择了错误的索引，可以使用索引提示来禁用它，并强制优化器做出不同的选择。

10.3.12 不可见索引

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

MySQL 支持不可见索引；也就是说，优化器不使用的索引。该功能适用于主键之外的索引（显式或隐式）。

索引默认是可见的。要明确控制新索引的可见性，可以在CREATE TABLE、CREATE INDEX或ALTER TABLE的索引定义中使用VISIBLE或INVISIBLE关键字：

CREATE TABLE t1 (
  i INT,
  j INT,
  k INT,
  INDEX i_idx (i) INVISIBLE
) ENGINE = InnoDB;
CREATE INDEX j_idx ON t1 (j) INVISIBLE;
ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;

要更改现有索引的可见性，请在ALTER TABLE ... ALTER INDEX操作中使用VISIBLE或INVISIBLE关键字：

ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE;
ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;

关于索引是可见还是不可见的信息可以从信息模式STATISTICS表或SHOW INDEX输出中获取。例如：

mysql> SELECT INDEX_NAME, IS_VISIBLE
       FROM INFORMATION_SCHEMA.STATISTICS
       WHERE TABLE_SCHEMA = 'db1' AND TABLE_NAME = 't1';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| i_idx      | YES        |
| j_idx      | NO         |
| k_idx      | NO         |
+------------+------------+

不可见索引使得可以测试删除索引对查询性能的影响，而无需进行破坏性更改，如果索引被证明是必需的，则必须撤消更改。对于大表来说，删除和重新添加索引可能很昂贵，而使其不可见和可见是快速的、就地操作。

如果优化器实际上需要或使用了一个不可见的索引，有几种方法可以注意到其在表的查询中的缺失的影响：

包含引用不可见索引的索引提示的查询出现错误。
性能模式数据显示受影响查询的工作负载增加。
查询具有不同的EXPLAIN执行计划。
以前未出现在慢查询日志中的查询现在出现了。

optimizer_switch系统变量的use_invisible_indexes标志控制优化器是否使用不可见索引进行查询执行计划构建。如果标志是off（默认值），优化器会忽略不可见索引（与引入此标志之前的行为相同）。如果标志是on，不可见索引仍然保持不可见，但优化器会考虑它们用于执行计划构建。

使用SET_VAR优化提示临时更新optimizer_switch的值，您可以仅在单个查询的持续时间内启用不可见索引，就像这样：

mysql> EXPLAIN SELECT /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */
     >     i, j FROM t1 WHERE j >= 50\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: j_idx
          key: j_idx
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index condition 
mysql> EXPLAIN SELECT i, j FROM t1 WHERE j >= 50\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: 5
     filtered: 33.33
        Extra: Using where

索引的可见性不影响索引维护。例如，索引仍会根据表行的更改而更新，唯一索引阻止重复插入到列中，无论索引是可见还是不可见。

如果表中没有明确的主键，但在NOT NULL列上有任何UNIQUE索引，则仍可能具有有效的隐式主键。在这种情况下，第一个这样的索引对表行施加与明确主键相同的约束，该索引无法隐藏。考虑以下表定义：

CREATE TABLE t2 (
  i INT NOT NULL,
  j INT NOT NULL,
  UNIQUE j_idx (j)
) ENGINE = InnoDB;

定义中没有明确的主键，但对NOT NULL列j上的索引会对行施加与主键相同的约束，且无法隐藏：

mysql> ALTER TABLE t2 ALTER INDEX j_idx INVISIBLE;
ERROR 3522 (HY000): A primary key index cannot be invisible.

现在假设在表中添加了一个明确的主键：

ALTER TABLE t2 ADD PRIMARY KEY (i);

明确的主键无法隐藏。此外，对j的唯一索引不再充当隐式主键，因此可以隐藏：

mysql> ALTER TABLE t2 ALTER INDEX j_idx INVISIBLE;
Query OK, 0 rows affected (0.03 sec)

10.3.13 降序索引

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

MySQL 支持降序索引：在索引定义中的DESC不再被忽略，而是导致键值以降序存储。以前，索引可以以相反顺序扫描，但会导致性能损失。降序索引可以以正向顺序扫描，这更有效率。降序索引还使优化器能够在最有效的扫描顺序中混合使用多列索引，其中一些列按升序顺序排列，而其他列按降序顺序排列。

考虑以下表定义，其中包含两列和四个两列索引定义，用于列的各种升序和降序索引组合：

CREATE TABLE t (
  c1 INT, c2 INT,
  INDEX idx1 (c1 ASC, c2 ASC),
  INDEX idx2 (c1 ASC, c2 DESC),
  INDEX idx3 (c1 DESC, c2 ASC),
  INDEX idx4 (c1 DESC, c2 DESC)
);

表定义会产生四个不同的索引。优化器可以对每个ORDER BY子句执行正向索引扫描，而无需使用filesort操作：

ORDER BY c1 ASC, c2 ASC    -- optimizer can use idx1
ORDER BY c1 DESC, c2 DESC  -- optimizer can use idx4
ORDER BY c1 ASC, c2 DESC   -- optimizer can use idx2
ORDER BY c1 DESC, c2 ASC   -- optimizer can use idx3

使用降序索引需满足以下条件：

仅支持InnoDB存储引擎的降序索引，具有以下限制：
- 如果索引包含降序索引键列或主键包含降序索引列，则辅助索引不支持更改缓冲。
- InnoDB SQL 解析器不使用降序索引。对于InnoDB全文搜索，这意味着索引表的FTS_DOC_ID列上所需的索引不能定义为降序索引。有关更多信息，请参阅 Section 17.6.2.4, “InnoDB Full-Text Indexes”。
支持所有可用升序索引的数据类型的降序索引。
支持普通（非生成的）和生成列（VIRTUAL和STORED）的降序索引。
DISTINCT可以使用包含匹配列的任何索引，包括降序键部分。
具有降序键部分的索引不会用于调用聚合函数但没有GROUP BY子句的查询的MIN()/MAX()优化。
支持BTREE但不支持HASH索引的降序索引。不支持FULLTEXT或SPATIAL索引的降序索引。

明确指定HASH、FULLTEXT和SPATIAL索引的ASC和DESC标识符会导致错误。

您可以在EXPLAIN输出的Extra列中看到优化器能够使用降序索引，如下所示：

mysql> CREATE TABLE t1 (
 -> a INT, 
 -> b INT, 
 -> INDEX a_desc_b_asc (a DESC, b ASC)
 -> );

mysql> EXPLAIN SELECT * FROM t1 ORDER BY a ASC\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: index
possible_keys: NULL
          key: a_desc_b_asc
      key_len: 10
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Backward index scan; Using index

在EXPLAIN FORMAT=TREE输出中，使用降序索引会在索引名称后面添加(reverse)，如下所示：

mysql> EXPLAIN FORMAT=TREE SELECT * FROM t1 ORDER BY a ASC\G 
*************************** 1\. row ***************************
EXPLAIN: -> Index scan on t1 using a_desc_b_asc (reverse)  (cost=0.35 rows=1)

另请参阅 EXPLAIN Extra Information。

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

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

时间值以 UTC 值的形式存储在TIMESTAMP列中，并且插入和检索TIMESTAMP列中的值在会话时区和 UTC 之间转换。（这与CONVERT_TZ()函数执行的相同类型的转换相同。如果会话时区是 UTC，则实际上没有时区转换。）

由于本地时区更改的惯例，如夏令时（DST），UTC 和非 UTC 时区之间的转换在两个方向上不是一对一的。在另一个时区中，原本不同的 UTC 值可能变得相同。以下示例显示了在非 UTC 时区中变为相同的不同 UTC 值：

mysql> CREATE TABLE tstable (ts TIMESTAMP);
mysql> SET time_zone = 'UTC'; -- insert UTC values
mysql> INSERT INTO tstable VALUES
       ('2018-10-28 00:30:00'),
       ('2018-10-28 01:30:00');
mysql> SELECT ts FROM tstable;
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 00:30:00 |
| 2018-10-28 01:30:00 |
+---------------------+
mysql> SET time_zone = 'MET'; -- retrieve non-UTC values
mysql> SELECT ts FROM tstable;
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 02:30:00 |
| 2018-10-28 02:30:00 |
+---------------------+

注意

要使用诸如'MET'或'Europe/Amsterdam'之类的命名时区，必须正确设置时区表。有关说明，请参见第 7.1.15 节“MySQL 服务器时区支持”。

您可以看到，当转换为'MET'时区时，这两个不同的 UTC 值相同。这种现象可能导致对给定的TIMESTAMP列查询的不同结果，具体取决于优化器是否使用索引来执行查询。

假设查询使用WHERE子句从ts列中搜索单个特定值，例如用户提供的时间戳文字：

SELECT ts FROM tstable
WHERE ts = '*literal*';

进一步假设查询在以下条件下执行：

会话时区不是 UTC 并且存在夏令时转换。例如：
```
SET time_zone = 'MET';
```
存储在TIMESTAMP列中的唯一 UTC 值，在会话时区中由于夏令时转换而不是唯一的。（前面显示的示例说明了这种情况。）
查询指定的搜索值在会话时区进入夏令时的那个小时内。

在这些条件下，WHERE子句中的比较以不同的方式进行非索引和索引查找，并导致不同的结果：

如果没有索引或优化器无法使用索引，则比较将在会话时区中进行。优化器执行表扫描，检索每个ts列值，将其从 UTC 转换为会话时区，然后将其与搜索值（也在会话时区中解释）进行比较：
```
mysql> SELECT ts FROM tstable
       WHERE ts = '2018-10-28 02:30:00';
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 02:30:00 |
| 2018-10-28 02:30:00 |
+---------------------+
```
因为存储的ts值被转换为会话时区，所以查询可能返回两个时间戳值，这些值在 UTC 值上是不同的，但在会话时区中是相等的：一个值发生在时钟更改之前的 DST 转换时，另一个值发生在 DST 转换之后。
如果存在可用的索引，则比较将在 UTC 时间发生。优化器执行索引扫描，首先将搜索值从会话时区转换为 UTC，然后将结果与 UTC 索引条目进行比较：
```
mysql> ALTER TABLE tstable ADD INDEX (ts);
mysql> SELECT ts FROM tstable
       WHERE ts = '2018-10-28 02:30:00';
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 02:30:00 |
+---------------------+
```
在这种情况下，（转换后的）搜索值仅与索引条目匹配，因为存储的不同 UTC 值的索引条目也是不同的，搜索值只能匹配其中一个。

由于非索引和索引查找的优化器操作不同，因此查询在每种情况下产生不同的结果。非索引查找的结果返回所有在会话时区匹配的值。索引查找无法做到这一点：

这是在仅知道 UTC 值的存储引擎内执行的。
对于映射到相同 UTC 值的两个不同会话时区值，索引查找仅匹配相应的 UTC 索引条目，并仅返回一行。

在前面的讨论中，存储在tstable中的数据集恰好由不同的 UTC 值组成。在这种情况下，所示形式的所有使用索引的查询最多匹配一个索引条目。

如果索引不是UNIQUE，则表（和索引）可能存储给定 UTC 值的多个实例。例如，ts列可能包含多个 UTC 值为'2018-10-28 00:30:00'的实例。在这种情况下，使用索引的查询将返回每个实例（在结果集中转换为 MET 值'2018-10-28 02:30:00'）。索引使用的查询仍然匹配将转换的搜索值与 UTC 索引条目中的单个值匹配，而不是匹配多个 UTC 值，这些值在会话时区中转换为搜索值。

如果重要的是返回所有在会话时区匹配的ts值，则解决方法是使用IGNORE INDEX提示来抑制索引的使用：

mysql> SELECT ts FROM tstable
       IGNORE INDEX (ts)
       WHERE ts = '2018-10-28 02:30:00';
+---------------------+
| ts                  |
+---------------------+
| 2018-10-28 02:30:00 |
| 2018-10-28 02:30:00 |
+---------------------+

在其他情况下，例如使用FROM_UNIXTIME()和UNIX_TIMESTAMP()函数执行的转换中，同样存在双向时区转换的一对多映射问题。请参阅第 14.7 节，“日期和时间函数”。

10.4 优化数据库结构

原文：dev.mysql.com/doc/refman/8.0/en/optimizing-database-structure.html

10.4.1 优化数据大小

10.4.2 优化 MySQL 数据类型

10.4.3 优化多表操作

10.4.4 MySQL 中内部临时表的使用

10.4.5 数据库和表数量限制

10.4.6 表大小限制

10.4.7 表列数和行大小限制

作为数据库设计师，要寻找最有效的方式来组织模式、表和列。就像调整应用程序代码一样，你要尽量减少 I/O，将相关项目放在一起，并提前规划，以确保随着数据量的增加性能保持高水平。从高效的数据库设计开始，可以让团队成员更容易编写高性能的应用程序代码，并使数据库在应用程序演变和重写时能够持久存在。

10.4.1 优化数据大小

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

设计表格以最小化它们在磁盘上的空间占用。通过减少写入和从磁盘读取的数据量，可以实现巨大的改进。较小的表通常在查询执行期间主动处理其内容时需要较少的主内存。对表数据的任何空间减少也会导致更小的索引，可以更快地处理。

MySQL 支持许多不同的存储引擎（表类型）和行格式。对于每个表，您可以决定使用哪种存储和索引方法。为您的应用程序选择适当的表格格式可以带来很大的性能提升。请参阅第十七章，InnoDB 存储引擎和第十八章，替代存储引擎。

您可以通过使用以下列出的技术获得表的更好性能并最小化存储空间：

表列
行格式
索引
连接
规范化

表列

使用可能的最有效（最小）数据类型。MySQL 有许多专门的类型可以节省磁盘空间和内存。例如，尽可能使用较小的整数类型以获得更小的表。MEDIUMINT通常比INT更好，因为MEDIUMINT列使用的空间少 25%。
如果可能，声明列为NOT NULL。这样可以通过更好地使用索引和消除测试每个值是否为NULL的开销来加快 SQL 操作。您还可以节省一些存储空间，每列一个比特。如果您确实需要在表中使用NULL值，请使用它们。只需避免允许每列都有NULL值的默认设置。

行格式

InnoDB表默认使用DYNAMIC行格式创建。要使用除DYNAMIC之外的行格式，请配置innodb_default_row_format，或在CREATE TABLE或ALTER TABLE语句中明确指定ROW_FORMAT选项。

紧凑系列行格式，包括COMPACT、DYNAMIC和COMPRESSED，减少了行存储空间，但增加了某些操作的 CPU 使用。如果您的工作负载受到缓存命中率和磁盘速度的限制，那么它可能会更快。如果是受 CPU 速度限制的罕见情况，那么它可能会更慢。

当使用变长字符集（如utf8mb3或utf8mb4）时，紧凑系列行格式还优化了CHAR列的存储。使用ROW_FORMAT=REDUNDANT时，CHAR(*N*)占用N × 字符集的最大字节长度。许多语言主要使用单字节的utf8mb3或utf8mb4字符，因此固定存储长度通常会浪费空间。使用紧凑系列行格式，InnoDB为这些列分配一个变量存储量，范围从N到N × 字符集的最大字节长度，通过去除尾随空格。最小存储长度为N字节，以便在典型情况下进行原地更新。有关更多信息，请参阅第 17.10 节，“InnoDB 行格式”。
为了通过以压缩形式存储表数据来进一步减少空间，创建InnoDB表时指定ROW_FORMAT=COMPRESSED，或在现有的MyISAM表上运行myisampack命令。(InnoDB压缩表是可读写的，而MyISAM压缩表是只读的。)
对于MyISAM表，如果没有任何变长列（VARCHAR, TEXT, 或 BLOB列），则使用固定大小的行格式。这样做速度更快，但可能会浪费一些空间。请参阅第 18.2.3 节，“MyISAM 表存储格式”。您可以通过使用CREATE TABLE选项ROW_FORMAT=FIXED来提示希望拥有固定长度行，即使您有VARCHAR列。

索引

表的主索引应尽可能短。这样可以轻松高效地识别每一行。对于InnoDB表，主键列在每个次要索引条目中都会重复，因此如果有许多次要索引，则较短的主键可以节省大量空间。
只创建需要改善查询性能的索引。索引对检索很有用，但会减慢插入和更新操作。如果你主要通过在多个列上搜索来访问表，那么为它们创建一个单一的复合索引，而不是为每个列单独创建索引。索引的第一部分应该是最常用的列。如果你在从表中选择时总是使用许多列，那么索引中的第一列应该是具有最多重复项的列，以获得更好的索引压缩。
如果一个长字符串列很可能在前几个字符上有唯一的前缀，最好只对这个前缀建立索引，使用 MySQL 支持在列的最左边部分创建索引（参见 Section 15.1.15, “CREATE INDEX Statement”）。较短的索引更快，不仅因为它们需要更少的磁盘空间，而且因为它们在索引缓存中提供更多的命中，从而减少磁盘查找。参见 Section 7.1.1, “Configuring the Server”。

连接

在某些情况下，将经常扫描的表拆分为两个部分可能是有益的。特别是如果它是一个动态格式的表，并且可以使用一个较小的静态格式表来在扫描表时找到相关行。
声明在不同表中具有相同信息的列具有相同的数据类型，以加快基于相应列的连接速度。
保持列名简单，这样你可以在不同表中使用相同的名称并简化连接查询。例如，在名为customer的表中，使用name作为列名，而不是customer_name。为了使你的名称在其他 SQL 服务器上可移植，考虑将它们保持在 18 个字符以下。

规范化

通常情况下，尽量保持所有数据非冗余（遵循数据库理论中所谓的第三范式）。不要重复冗长的数值，如姓名和地址，给它们分配唯一的 ID，根据需要在多个较小的表中重复这些 ID，并通过在连接子句中引用 ID 来在查询中连接这些表。
如果速度比磁盘空间和保持多个数据副本的维护成本更重要，例如在商业智能场景中，你分析来自大表的所有数据，你可以放松规范化规则，复制信息或创建摘要表以获得更快的速度。

10.4.2 优化 MySQL 数据类型

原文：dev.mysql.com/doc/refman/8.0/en/optimize-data-types.html

10.4.2.1 优化数值数据

10.4.2.2 优化字符和字符串类型

10.4.2.3 优化 BLOB 类型

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

10.4.2.1 优化数字数据

对于可以表示为字符串或数字的唯一 ID 或其他值，应优先选择数字列而不是字符串列。由于大型数字值可以以比相应字符串更少的字节存储，因此传输和比较它们更快且占用更少的内存。
如果您使用数字数据，从数据库（使用实时连接）访问信息通常比从文本文件访问信息更快。数据库中的信息可能以比文本文件更紧凑的格式存储，因此访问它涉及较少的磁盘访问。您还可以在应用程序中节省代码，因为您可以避免解析文本文件以查找行和列边界。

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

10.4.2.2 优化字符和字符串类型

对于字符和字符串列，请遵循以下准则：

使用二进制排序顺序进行快速比较和排序操作，当您不需要特定于语言的排序功能时。您可以在特定查询中使用BINARY运算符来使用二进制排序。
在比较来自不同列的值时，尽可能声明这些列具有相同的字符集和排序规则，以避免在运行查询时进行字符串转换。
对于大小小于 8KB 的列值，请使用二进制VARCHAR而不是BLOB。GROUP BY和ORDER BY子句可以生成临时表，如果原始表不包含任何BLOB列，则这些临时表可以使用MEMORY存储引擎。
如果一个表包含字符串列，如姓名和地址，但许多查询不检索这些列，请考虑将字符串列拆分到一个单独的表中，并在必要时使用带有外键的连接查询。当 MySQL 从一行中检索任何值时，它会读取包含该行所有列（以及可能其他相邻行）的数据块。保持每行小，只包含最常用的列，可以使更多的行适合每个数据块中。这样的紧凑表减少了常见查询的磁盘 I/O 和内存使用。
当您在InnoDB表中将随机生成的值用作主键时，最好将其前缀设置为一个递增值，例如当前日期和时间。当连续的主键值在物理上靠近时，InnoDB可以更快地插入和检索它们。
请参阅 Section 10.4.2.1, “Optimizing for Numeric Data”，了解为什么数值列通常优于等效的字符串列。

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

10.4.2.3 优化 BLOB 类型

存储包含文本数据的大型 BLOB 时，考虑首先对其进行压缩。当整个表由InnoDB或MyISAM压缩时，请勿使用此技术。
对于具有多个列的表，为了减少不使用 BLOB 列的查询的内存需求，考虑将 BLOB 列拆分到一个单独的表中，并在需要时使用连接查询引用它。
由于检索和显示 BLOB 值的性能要求可能与其他数据类型非常不同，您可以将特定于 BLOB 的表放在不同的存储设备上，甚至放在单独的数据库实例上。例如，检索 BLOB 可能需要大量的顺序磁盘读取，这更适合传统硬盘而不是 SSD 设备。
查看第 10.4.2.2 节，“优化字符和字符串类型”，了解为什么有时二进制VARCHAR列比等效的 BLOB 列更可取。
与非常长的文本字符串进行相等性测试不同，您可以将列值的哈希存储在单独的列中，对该列建立索引，并在查询中测试哈希值。（使用MD5()或CRC32()函数生成哈希值。）由于哈希函数可以为不同的输入产生重复的结果，您仍然在查询中包含一个子句AND *blob_column* = *long_string_value*来防止错误匹配；性能优势来自于对哈希值的较小、易于扫描的索引。

10.4.3 优化多表

原文：dev.mysql.com/doc/refman/8.0/en/optimize-multi-tables.html

10.4.3.1 MySQL 如何打开和关闭表

10.4.3.2 在同一数据库中创建许多表的缺点

保持单个查询快速的一些技术涉及将数据分布在许多表中。当表的数量达到数千甚至数百万时，处理所有这些表的开销成为一个新的性能考虑因素。

译文：dev.mysql.com/doc/refman/8.0/en/table-cache.html

10.4.3.1 MySQL 如何打开和关闭表

当你执行 mysqladmin status 命令时，你应该看到类似以下内容：

Uptime: 426 Running threads: 1 Questions: 11082
Reloads: 1 Open tables: 12

Open tables 值为 12 可能会让人感到困惑，如果你的表少于 12 张。

MySQL 是多线程的，因此可能会有许多客户端同时为给定表发出查询。为了最小化多个客户端会话在同一表上具有不同状态的问题，每个并发会话独立打开表。这会使用额外的内存，但通常会提高性能。对于 MyISAM 表，每个打开表的客户端都需要一个额外的文件描述符用于数据文件。（相比之下，索引文件描述符在所有会话之间共享。）

table_open_cache 和 max_connections 系统变量影响服务器保持打开的文件的最大数量。如果你增加其中一个或两个值，可能会遇到操作系统对每个进程打开文件描述符数量的限制。许多操作系统允许你增加打开文件的限制，尽管方法因系统而异。请查阅你的操作系统文档，确定是否可以增加限制以及如何操作。

table_open_cache 与 max_connections 相关。例如，对于 200 个并发运行的连接，至少指定一个表缓存大小为 200 * *N*，其中 N 是你执行的任何查询中任何连接中的最大表数。你还必须为临时表和文件保留一些额外的文件描述符。

确保你的操作系统能够处理由 table_open_cache 设置所暗示的打开文件描述符数量。如果 table_open_cache 设置过高，MySQL 可能会耗尽文件描述符，并表现出拒绝连接或无法执行查询等症状。

还要考虑到 MyISAM 存储引擎每个唯一打开表需要两个文件描述符。要增加供 MySQL 使用的文件描述符数量，设置 open_files_limit 系统变量。参见 Section B.3.2.16, “File Not Found and Similar Errors”。

打开表的缓存保持在table_open_cache条目的水平。服务器在启动时自动调整缓存大小。要显式设置大小，请在启动时设置table_open_cache系统变量。MySQL 可能会临时打开比这更多的表来执行查询，如本节后面所述。

MySQL 在以下情况下关闭未使用的表并从表缓存中删除它：

当缓存已满且线程尝试打开不在缓存中的表时。
当缓存包含超过table_open_cache条目且缓存中的表不再被任何线程使用时。
当发生表刷新操作时。当有人发出FLUSH TABLES语句或执行mysqladmin flush-tables或mysqladmin refresh命令时。

当表缓存填满时，服务器使用以下过程来定位要使用的缓存条目：

当前未使用的表将被释放，从最近最少使用的表开始。
如果必须打开新表，但缓存已满且无法释放表，则根据需要临时扩展缓存。当缓存处于临时扩展状态且表从已使用到未使用状态时，表将被关闭并从缓存中释放。

每个并发访问都会打开一个MyISAM表。这意味着如果两个线程访问同一张表，或者一个线程在同一查询中两次访问表（例如，通过将表与自身连接），则需要打开表两次。每个并发打开都需要在表缓存中有一个条目。任何MyISAM表的第一次打开需要两个文件描述符：一个用于数据文件，一个用于索引文件。对表的每次额外使用只需要一个数据文件的文件描述符。索引文件描述符在所有线程之间共享。

如果您使用HANDLER *tbl_name* OPEN语句打开表，则为线程分配一个专用表对象。此表对象不与其他线程共享，并且直到线程调用HANDLER *tbl_name* CLOSE或线程终止时才关闭。发生这种情况时，表将放回表缓存（如果缓存未满）。请参阅第 15.2.5 节，“HANDLER 语句”。

要确定您的表缓存是否太小，请检查Opened_tables状态变量，该变量指示自服务器启动以来的表打开操作次数：

mysql> SHOW GLOBAL STATUS LIKE 'Opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 2741  |
+---------------+-------+

如果值非常大或增长迅速，即使您没有发出许多FLUSH TABLES语句，也应在服务器启动时增加table_open_cache的值。

原文：dev.mysql.com/doc/refman/8.0/en/creating-many-tables.html

10.4.3.2 在同一数据库中创建许多表的缺点

如果在同一个数据库目录中有许多MyISAM表，那么打开、关闭和创建操作会很慢。如果在许多不同的表上执行SELECT语句，当表缓存已满时会有一些开销，因为每次打开一个表时，都必须关闭另一个表。您可以通过增加表缓存中允许的条目数来减少这种开销。

10.4.5 数据库和表数量的限制

原文：dev.mysql.com/doc/refman/8.0/en/database-count-limit.html

MySQL 对数据库的数量没有限制。底层文件系统可能对目录数量有限制。

MySQL 对表的数量没有限制。底层文件系统可能对代表表的文件数量有限制。各个存储引擎可能会施加特定于引擎的限制。InnoDB允许最多 4 十亿个表。

10.4.6 表大小限制

原文：dev.mysql.com/doc/refman/8.0/en/table-size-limit.html

MySQL 数据库的有效最大表大小通常由操作系统对文件大小的限制确定，而不是由 MySQL 内部限制确定。有关操作系统文件大小限制的最新信息，请参考特定于您操作系统的文档。

Windows 用户请注意，FAT 和 VFAT（FAT32）不被认为适用于与 MySQL 一起生产使用。请改用 NTFS。

如果遇到整个表错误，可能有几个原因：

磁盘可能已满。
您正在使用InnoDB表，并且在InnoDB表空间文件中已用完空间。表空间大小的最大值也是表的最大大小。有关表空间大小限制，请参阅 Section 17.22, “InnoDB Limits”。

通常，对于大于 1TB 的表，建议将表分区到多个表空间文件中。
您已达到操作系统文件大小限制。例如，您正在使用MyISAM表，但操作系统仅支持最大为 2GB 的文件，并且您已达到数据文件或索引文件的限制。
您正在使用MyISAM表，表所需的空间超过了内部指针大小允许的范围。MyISAM默认允许数据和索引文件增长到 256TB，但此限制可更改为最大允许的大小 65,536TB（256⁷ − 1 字节）。

如果您需要一个大于默认限制的MyISAM表，并且您的操作系统支持大文件，CREATE TABLE语句支持AVG_ROW_LENGTH和MAX_ROWS选项。请参阅 Section 15.1.20, “CREATE TABLE Statement”。服务器使用这些选项来确定允许多大的表。

如果现有表的指针大小太小，您可以使用ALTER TABLE更改选项以增加表的最大允许大小。请参阅 Section 15.1.9, “ALTER TABLE Statement”。
```
ALTER TABLE *tbl_name* MAX_ROWS=1000000000 AVG_ROW_LENGTH=*nnn*;
```
您只需为具有BLOB或TEXT列的表指定AVG_ROW_LENGTH；在这种情况下，MySQL 无法仅基于行数优化所需的空间。

要更改MyISAM表的默认大小限制，请设置myisam_data_pointer_size，该变量设置内部行指针使用的字节数。如果您没有指定MAX_ROWS选项，该值将用于设置新表的指针大小。myisam_data_pointer_size的值可以从 2 到 7。例如，对于使用动态存储格式的表，值为 4 允许表达到 4GB；值为 6 允许表达到 256TB。使用固定存储格式的表具有更大的最大数据长度。有关存储格式特性，请参见第 18.2.3 节，“MyISAM 表存储格式”。

您可以使用以下语句检查最大数据和索引大小：
```
SHOW TABLE STATUS FROM *db_name* LIKE '*tbl_name*';
```
您还可以使用myisamchk -dv /path/to/table-index-file。参见第 15.7.7 节，“SHOW 语句”，或第 6.6.4 节，“myisamchk — MyISAM 表维护实用程序”。

解决MyISAM表文件大小限制的其他方法如下：
- 如果您的大表是只读的，您可以使用myisampack进行压缩。myisampack通常将表压缩至少 50%，因此您实际上可以拥有更大的表。myisampack还可以将多个表合并为单个表。参见第 6.6.6 节，“myisampack — 生成压缩的只读 MyISAM 表”。
- MySQL 包含一个MERGE库，使您能够将具有相同结构的一组MyISAM表作为单个MERGE表处理。参见第 18.7 节，“MERGE 存储引擎”。
你正在使用MEMORY（HEAP）存储引擎；在这种情况下，您需要增加max_heap_table_size系统变量的值。参见第 7.1.8 节，“服务器系统变量”。

10.4.7 表列数和行大小的限制

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

本节描述了表中列数和单个行大小的限制。

列数限制
行大小限制

列数限制

MySQL 每个表的列数有 4096 列的硬限制，但对于给定表，实际最大值可能会更少。确切的列限制取决于几个因素：

表的最大行大小限制了列的数量（可能也是大小），因为所有列的总长度不能超过这个大小。参见行大小限制。
单个列的存储要求限制了适合于给定最大行大小的列数。某些数据类型的存储要求取决于诸如存储引擎、存储格式和字符集等因素。参见第 13.7 节，“数据类型存储要求”。
存储引擎可能会施加额外的限制，限制表列数。例如，InnoDB每个表有 1017 列的限制。参见第 17.22 节，“InnoDB 限制”。有关其他存储引擎的信息，请参见第十八章，“替代存储引擎”。
功能键部分（参见第 15.1.15 节，“CREATE INDEX 语句”）被实现为隐藏的虚拟生成的存储列，因此表索引中的每个功能键部分都计入表总列限制。

行大小限制

给定表的最大行大小由几个因素决定：

MySQL 表的内部表示具有最大行大小限制为 65,535 字节，即使存储引擎能够支持更大的行。BLOB和TEXT列仅对行大小限制贡献 9 到 12 字节，因为它们的内容与行的其余部分分开存储。
InnoDB表的最大行大小，适用于数据库页面内存储的数据，对于 4KB、8KB、16KB 和 32KB 的innodb_page_size设置，略小于半页。例如，默认 16KB 的InnoDB页面大小的最大行大小略小于 8KB。对于 64KB 页面，最大行大小略小于 16KB。参见第 17.22 节，“InnoDB 限制”。

如果包含变长列的行超过InnoDB的最大行大小，InnoDB会选择将变长列存储在外部页外存储，直到行符合InnoDB行大小限制。存储在本地的变长列数据量因行格式而异。有关更多信息，请参见第 17.10 节，“InnoDB 行格式”。
不同的存储格式使用不同数量的页头和页尾数据，这会影响可用于行的存储量。
- 有关InnoDB行格式的信息，请参见第 17.10 节，“InnoDB 行格式”。
- 有关MyISAM存储格式的信息，请参见第 18.2.3 节，“MyISAM 表存储格式”。

行大小限制示例

MySQL 的最大行大小限制为 65,535 字节，以下示例演示了InnoDB和MyISAM的情况。该限制是强制执行的，无论存储引擎如何，即使存储引擎可能支持更大的行。

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g VARCHAR(6000)) ENGINE=InnoDB CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535\. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g VARCHAR(6000)) ENGINE=MyISAM CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535\. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs

在以下MyISAM示例中，将列更改为TEXT可以避免 65,535 字节的行大小限制，并且允许操作成功，因为BLOB和TEXT列只对行大小贡献了 9 到 12 字节。

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g TEXT(6000)) ENGINE=MyISAM CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)

对于InnoDB表，将列更改为TEXT可以避免 MySQL 的 65,535 字节行大小限制，并且InnoDB的变长列页外存储避免了InnoDB行大小限制。

mysql> CREATE TABLE t (a VARCHAR(10000), b VARCHAR(10000),
       c VARCHAR(10000), d VARCHAR(10000), e VARCHAR(10000),
       f VARCHAR(10000), g TEXT(6000)) ENGINE=InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)

变长列的存储包括长度字节，这些字节计入行大小。例如，VARCHAR(255) CHARACTER SET utf8mb3列需要两个字节来存储值的长度，因此每个值最多可以占用 767 字节。

创建表t1的语句成功，因为列需要 32,765 + 2 字节和 32,766 + 2 字节，这在最大行大小为 65,535 字节的范围内：

mysql> CREATE TABLE t1
       (c1 VARCHAR(32765) NOT NULL, c2 VARCHAR(32766) NOT NULL)
       ENGINE = InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.02 sec)

创建表t2的语句失败，因为虽然列长度在 65,535 字节的最大长度范围内，但需要额外的两个字节来记录长度，这导致行大小超过 65,535 字节：

mysql> CREATE TABLE t2
       (c1 VARCHAR(65535) NOT NULL)
       ENGINE = InnoDB CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535\. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs

将列长度减少到 65,533 或更少可以使语句成功。

mysql> CREATE TABLE t2
       (c1 VARCHAR(65533) NOT NULL)
       ENGINE = InnoDB CHARACTER SET latin1;
Query OK, 0 rows affected (0.01 sec)

对于MyISAM表，NULL列需要额外的空间来记录它们的值是否为NULL。每个NULL列需要额外的一位，向上取整到最近的字节。

创建表t3的语句失败，因为MyISAM除了需要变长列长度字节所需的空间外，还需要为NULL列留出空间，导致行大小超过 65,535 字节：
```
mysql> CREATE TABLE t3
       (c1 VARCHAR(32765) NULL, c2 VARCHAR(32766) NULL)
       ENGINE = MyISAM CHARACTER SET latin1;
ERROR 1118 (42000): Row size too large. The maximum row size for the used
table type, not counting BLOBs, is 65535\. This includes storage overhead,
check the manual. You have to change some columns to TEXT or BLOBs
```
有关InnoDB NULL列存储的信息，请参阅第 17.10 节“InnoDB 行格式”。

InnoDB限制行大小（在数据库页面内本地存储的数据）略小于 4KB、8KB、16KB 和 32KB innodb_page_size设置的一半数据库页面大小，并且略小于 64KB 页面的 16KB。

创建表t4的语句失败，因为定义的列超过了 16KB InnoDB页面的行大小限制。

mysql> CREATE TABLE t4 (
       c1 CHAR(255),c2 CHAR(255),c3 CHAR(255),
       c4 CHAR(255),c5 CHAR(255),c6 CHAR(255),
       c7 CHAR(255),c8 CHAR(255),c9 CHAR(255),
       c10 CHAR(255),c11 CHAR(255),c12 CHAR(255),
       c13 CHAR(255),c14 CHAR(255),c15 CHAR(255),
       c16 CHAR(255),c17 CHAR(255),c18 CHAR(255),
       c19 CHAR(255),c20 CHAR(255),c21 CHAR(255),
       c22 CHAR(255),c23 CHAR(255),c24 CHAR(255),
       c25 CHAR(255),c26 CHAR(255),c27 CHAR(255),
       c28 CHAR(255),c29 CHAR(255),c30 CHAR(255),
       c31 CHAR(255),c32 CHAR(255),c33 CHAR(255)
       ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC DEFAULT CHARSET latin1;
ERROR 1118 (42000): Row size too large (> 8126). Changing some columns to TEXT or BLOB may help.
In current row format, BLOB prefix of 0 bytes is stored inline.

10.5 优化 InnoDB 表

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

10.5.1 优化 InnoDB 表的存储布局

10.5.2 优化 InnoDB 事务管理

10.5.3 优化 InnoDB 只读事务

10.5.4 优化 InnoDB 重做日志记录

10.5.5 InnoDB 表的批量数据加载

10.5.6 优化 InnoDB 查询

10.5.7 优化 InnoDB DDL 操作

10.5.8 优化 InnoDB 磁盘 I/O

10.5.9 优化 InnoDB 配置变量

10.5.10 优化具有多个表的 InnoDB 系统

InnoDB 是 MySQL 客户通常在生产数据库中使用的存储引擎，可靠性和并发性非常重要。InnoDB 是 MySQL 的默认存储引擎。本节解释了如何为 InnoDB 表优化数据库操作。

10.5.1 优化 InnoDB 表的存储布局

原文：dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-storage-layout.html

一旦你的数据达到稳定大小，或者一个增长中的表增加了几十或几百兆字节，考虑使用OPTIMIZE TABLE语句重新组织表并压缩任何浪费的空间。重新组织的表需要更少的磁盘 I/O 来执行全表扫描。这是一种简单的技术，当其他技术如改进索引使用或调整应用程序代码不可行时，可以提高性能。

OPTIMIZE TABLE会复制表的数据部分并重建索引。好处来自于索引内数据的改进打包，以及表空间和磁盘上的碎片减少。好处取决于每个表中的数据。你可能会发现对于某些表有显著的收益，而对于其他表则没有，或者收益会随着时间的推移而减少，直到下次优化表为止。如果表很大或者重建的索引不适合缓冲池，这个操作可能会很慢。在向表中添加大量数据后的第一次运行通常比后续运行慢得多。
在InnoDB中，拥有一个长的PRIMARY KEY（要么是一个具有冗长值的单列，要么是几个列形成的长复合值）会浪费大量磁盘空间。一行的主键值会在指向同一行的所有二级索引记录中重复。（参见第 17.6.2.1 节，“聚簇索引和二级索引”。）如果你的主键很长，可以创建一个AUTO_INCREMENT列作为主键，或者索引长VARCHAR列的前缀而不是整个列。
使用VARCHAR数据类型来存储变长字符串或具有许多NULL值的列，而不是使用CHAR。CHAR(*N*)列始终需要N个字符来存储数据，即使字符串较短或其值为NULL。较小的表更适合缓冲池，并减少磁盘 I/O。

当使用COMPACT行格式（默认的InnoDB格式）和变长字符集，比如utf8mb4或sjis时，CHAR(*N*)列占据可变数量的空间，但至少占用N字节。
对于大表，或包含大量重复文本或数字数据的表，考虑使用COMPRESSED行格式。需要更少的磁盘 I/O 将数据带入缓冲池，或执行全表扫描。在做出永久决定之前，通过使用COMPRESSED与COMPACT行格式来衡量你可以实现的压缩量。

10.5.2 优化 InnoDB 事务管理

原文：dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-transaction-management.html

为了优化InnoDB事务处理，找到事务特性的性能开销和服务器工作负载之间的理想平衡。例如，如果一个应用程序每秒提交数千次，可能会遇到性能问题，如果每 2-3 小时才提交一次，则可能会遇到不同的性能问题。

默认的 MySQL 设置AUTOCOMMIT=1可能会对繁忙的数据库服务器施加性能限制。在实际情况下，通过发出SET AUTOCOMMIT=0或START TRANSACTION语句，然后在进行所有更改后发出COMMIT语句，将几个相关的数据更改操作包装成一个单独的事务。

如果事务对数据库进行了修改，则每次事务提交时InnoDB必须将日志刷新到磁盘。当每个更改后跟着一个提交时（与默认的自动提交设置一样），存储设备的 I/O 吞吐量限制了每秒潜在操作的数量。
或者，对于仅包含单个SELECT语句的事务，打开AUTOCOMMIT有助于InnoDB识别只读事务并对其进行优化。有关要求，请参阅第 10.5.3 节，“优化 InnoDB 只读事务”。
避免在插入、更新或删除大量行后执行回滚。如果一个大型事务正在减慢服务器性能，回滚可能会使问题变得更糟，可能需要执行的时间是原始数据更改操作的几倍。杀死数据库进程并不会有帮助，因为回滚会在服务器启动时重新开始。

为了最大程度减少出现此问题的机会：
- 增加缓冲池的大小，以便所有数据更改都可以被缓存而不是立即写入磁盘。
- 设置innodb_change_buffering=all，以便在插入操作之外还缓冲更新和删除操作。
- 在大数据更改操作期间定期发出COMMIT语句，可能将单个删除或更新拆分为操作较少行数的多个语句。
一旦发生失控的回滚，增加缓冲池大小，使回滚变为 CPU 限制并快速运行，或者杀死服务器并使用innodb_force_recovery=3重新启动，如第 17.18.2 节，“InnoDB 恢复”中所述。

默认设置innodb_change_buffering=all预计不会经常出现这个问题，它允许更新和删除操作在内存中被缓存，使它们在第一次执行时更快，并且在需要时回滚也更快。确保在处理有许多插入、更新或删除的长时间运行事务的服务器上使用这个参数设置。
如果可以容忍在意外退出时丢失一些最新提交的事务，可以将innodb_flush_log_at_trx_commit参数设置为 0。InnoDB尝试每秒刷新一次日志，尽管刷新不是保证的。
当行被修改或删除时，行和相关的撤销日志不会立即被物理删除，甚至在事务提交后也不会立即删除。旧数据会被保留，直到比它更早或同时开始的事务完成，这样这些事务就可以访问被修改或删除行的先前状态。因此，长时间运行的事务可能会阻止InnoDB清除被不同事务更改的数据。
当在长时间运行的事务中修改或删除行时，使用READ COMMITTED和REPEATABLE READ隔离级别的其他事务需要更多工作来重建旧数据，如果它们读取相同的行。
当一个长时间运行的事务修改表时，来自其他事务对该表的查询不会利用覆盖索引技术。通常可以从二级索引检索所有结果列的查询，而是从表数据中查找适当的值。

如果发现二级索引页的PAGE_MAX_TRX_ID太新，或者如果二级索引中的记录被标记为删除，InnoDB可能需要使用聚簇索引查找记录。

10.5.3 优化 InnoDB 只读事务

原文：dev.mysql.com/doc/refman/8.0/en/innodb-performance-ro-txn.html

InnoDB 可以避免为已知为只读的事务设置事务 ID（TRX_ID 字段）所带来的开销。只有可能执行写操作或锁定读取（如 SELECT ... FOR UPDATE）的事务才需要事务 ID。消除不必要的事务 ID 可减少每次查询或数据更改语句构建读视图时所查询的内部数据结构的大小。

当：

事务是通过 START TRANSACTION READ ONLY 语句启动的。在这种情况下，尝试对数据库进行更改（对于 InnoDB、MyISAM 或其他类型的表）会导致错误，并且事务将继续处于只读状态：
```
ERROR 1792 (25006): Cannot execute statement in a READ ONLY transaction.
```
在只读事务中，您仍然可以对会话特定的临时表进行更改，或者为其发出锁定查询，因为这些更改和锁定对任何其他事务都不可见。
autocommit 设置已打开，因此可以保证事务是单个语句，并且组成事务的单个语句是“非锁定” SELECT 语句。也就是说，不使用 FOR UPDATE 或 LOCK IN SHARED MODE 子句的 SELECT。
事务是在没有 READ ONLY 选项的情况下启动的，但尚未执行更新或明确锁定行的语句。在需要更新或明确锁定之前，事务保持在只读模式下。

因此，对于像报表生成器这样的读密集型应用程序，您可以通过将它们组合在 START TRANSACTION READ ONLY 和 COMMIT 中，或者在运行 SELECT 语句之前打开 autocommit 设置，或者简单地避免在查询中插入任何数据更改语句来调整一系列 InnoDB 查询。

有关 START TRANSACTION 和 autocommit 的信息，请参见 Section 15.3.1, “START TRANSACTION, COMMIT, and ROLLBACK Statements”。

注意

符合自动提交、非锁定和只读（AC-NL-RO）条件的事务将被排除在某些内部InnoDB数据结构之外，因此不会在SHOW ENGINE INNODB STATUS输出中列出。

10.5.4 优化 InnoDB 重做日志

原文：dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-logging.html

考虑以下优化重做日志记录的准则：

增加重做日志文件的大小。当 InnoDB 写满重做日志文件时，必须将缓冲池的修改内容写入磁盘中的一个检查点。较小的重做日志文件会导致许多不必要的磁盘写入。

从 MySQL 8.0.30 开始，重做日志文件大小由 innodb_redo_log_capacity 设置确定。InnoDB 尝试维护相同大小的 32 个重做日志文件，每个文件大小等于 1/32 * innodb_redo_log_capacity。因此，更改 innodb_redo_log_capacity 设置会改变重做日志文件的大小。

在 MySQL 8.0.30 之前，重做日志文件的大小和数量是使用 innodb_log_file_size 和 innodb_log_files_in_group 变量进行配置的。

有关修改重做日志文件配置的信息，请参见第 17.6.5 节，“重做日志”。
考虑增加日志缓冲区的大小。较大的日志缓冲区使大型事务能够在提交之前运行而无需将日志写入磁盘。因此，如果您有更新、插入或删除许多行的事务，使日志缓冲区更大可以节省磁盘 I/O。日志缓冲区大小通过 innodb_log_buffer_size 配置选项进行配置，在 MySQL 8.0 中可以动态配置。
配置 innodb_log_write_ahead_size 配置选项以避免“读写”。此选项定义了重做日志的预写块大小。将 innodb_log_write_ahead_size 设置为匹配操作系统或文件系统缓存块大小。读写发生在由于重做日志的预写块大小与操作系统或文件系统缓存块大小不匹配而导致重做日志块未完全缓存到操作系统或文件系统时。

innodb_log_write_ahead_size 的有效值是 InnoDB 日志文件块大小的倍数（2^n）。最小值为 InnoDB 日志文件块大小（512）。当指定最小值时，不会发生预写。最大值等于 innodb_page_size 的值。如果指定的值大于 innodb_page_size 的值，则 innodb_log_write_ahead_size 设置将被截断为 innodb_page_size 的值。

如果将 innodb_log_write_ahead_size 的值设置得太低，与操作系统或文件系统缓存块大小相比，会导致读写。将值设置得太高可能会对日志文件写入的 fsync 性能产生轻微影响，因为会一次写入多个块。
MySQL 8.0.11 引入了专用的日志写入线程，用于将重做日志记录从日志缓冲区写入系统缓冲区并将系统缓冲区刷新到重做日志文件。以前，各个用户线程负责这些任务。从 MySQL 8.0.22 开始，您可以使用 innodb_log_writer_threads 变量启用或禁用日志写入线程。专用的日志写入线程可以提高高并发系统的性能，但对于低并发系统，禁用专用的日志写入线程可以提供更好的性能。
通过用户线程优化等待刷新重做的自旋延迟的使用。自旋延迟有助于减少延迟。在低并发期间，减少延迟可能不是首要任务，避免在这些时期使用自旋延迟可能会降低能耗。在高并发期间，您可能希望避免在自旋延迟上消耗处理能力，以便用于其他工作。以下系统变量允许设置定义自旋延迟使用边界的高水位线和低水位线值。
- innodb_log_wait_for_flush_spin_hwm：定义了超过该值的最大平均日志刷新时间，用户线程在等待刷新的重做时不再自旋。默认值为 400 微秒。
- innodb_log_spin_cpu_abs_lwm: 定义了在刷新重做时等待时，用户线程不再自旋的最低 CPU 使用量。该值表示为 CPU 核使用量的总和。例如，默认值为 80，即一个 CPU 核的 80%。在具有多核处理器的系统上，值为 150 表示一个 CPU 核的 100% 使用量加上第二个 CPU 核的 50% 使用量。
- innodb_log_spin_cpu_pct_hwm: 定义了在刷新重做时等待时，用户线程不再自旋的最大 CPU 使用量。该值表示为所有 CPU 核的总处理能力的百分比。默认值为 50%。例如，在具有四个 CPU 核的服务器上，两个 CPU 核的 100% 使用量是所有 CPU 处理能力的 50%。
  
  innodb_log_spin_cpu_pct_hwm 配置选项遵守处理器亲和性。例如，如果服务器有 48 个核心，但 mysqld 进程只固定在四个 CPU 核心上，其他 44 个 CPU 核心将被忽略。

10.5.5 InnoDB 表的批量数据加载

原文：dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-bulk-data-loading.html

这些性能提示补充了快速插入的一般准则，详见 Section 10.2.5.1, “优化 INSERT 语句”。

当将数据导入InnoDB时，关闭自动提交模式，因为它会为每次插入执行一次日志刷新到磁盘。在导入操作期间禁用自动提交，请使用SET autocommit和COMMIT语句包围它：
```
SET autocommit=0;
*... SQL import statements ...* COMMIT;
```
mysqldump选项--opt创建的转储文件可以快速导入到InnoDB表中，即使没有用SET autocommit和COMMIT语句包装它们。
如果在次要键上有UNIQUE约束，则可以在导入会话期间暂时关闭唯一性检查以加快表导入速度：
```
SET unique_checks=0;
*... SQL import statements ...* SET unique_checks=1;
```
对于大表，这样可以节省大量磁盘 I/O，因为InnoDB可以使用其更改缓冲区批量写入次要索引记录。确保数据不包含重复键。
如果您的表中有FOREIGN KEY约束，可以通过在导入会话期间关闭外键检查来加快表导入速度：
```
SET foreign_key_checks=0;
*... SQL import statements ...* SET foreign_key_checks=1;
```
对于大��，这可以节省大量磁盘 I/O。
如果需要插入许多行，请使用多行INSERT语法以减少客户端和服务器之间的通信开销：
```
INSERT INTO yourtable VALUES (1,2), (5,5), ...;
```
这个提示适用于任何表的插入，不仅仅是InnoDB表。
在对具有自增列的表进行批量插入时，将innodb_autoinc_lock_mode设置为 2（交错）而不是 1（连续）。有关详细信息，请参阅 Section 17.6.1.6, “InnoDB 中的 AUTO_INCREMENT 处理”。
在执行批量插入时，按PRIMARY KEY顺序插入行会更快。InnoDB表使用聚簇索引，这使得按PRIMARY KEY顺序使用数据相对快速。按PRIMARY KEY顺序执行批量插入对于完全不适合缓冲池的表格尤为重要。
在将数据加载到InnoDB的FULLTEXT索引时，为了获得最佳性能，请按照以下步骤进行：
1. 在表创建时定义一个名为FTS_DOC_ID的列，类型为BIGINT UNSIGNED NOT NULL，并创建一个名为FTS_DOC_ID_INDEX的唯一索引。例如：
```
CREATE TABLE t1 (
FTS_DOC_ID BIGINT unsigned NOT NULL AUTO_INCREMENT,
title varchar(255) NOT NULL DEFAULT '',
text mediumtext NOT NULL,
PRIMARY KEY (`FTS_DOC_ID`)
) ENGINE=InnoDB;
CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on t1(FTS_DOC_ID);
```
2. 将数据加载到表中。
3. 在数据加载后创建FULLTEXT索引。
注意

在表创建时添加FTS_DOC_ID列时，请确保在更新FULLTEXT索引列时更新FTS_DOC_ID列，因为FTS_DOC_ID必须随着每个INSERT或UPDATE而单调递增。如果选择不在表创建时添加FTS_DOC_ID并让InnoDB为您管理 DOC ID，InnoDB会在下一次CREATE FULLTEXT INDEX调用时添加FTS_DOC_ID作为隐藏列。然而，这种方法需要重建表，可能会影响性能。
如果要将数据加载到一个新的 MySQL 实例中，请考虑使用ALTER INSTANCE {ENABLE|DISABLE} INNODB REDO_LOG语法来禁用重做日志记录。禁用重做日志记录有助于加快数据加载速度，避免重做日志写入。更多信息，请参见禁用重做日志记录。

警告

此功能仅用于将数据加载到新的 MySQL 实例中。不要在生产系统上禁用重做日志记录。允许在禁用重做日志记录时关闭并重新启动服务器，但在禁用重做日志记录时发生意外服务器停止可能会导致数据丢失和实例损坏。
使用 MySQL Shell 导入数据。MySQL Shell 的并行表导入实用程序util.importTable()为大型数据文件提供了快速数据导入到 MySQL 关系表的功能。MySQL Shell 的转储加载实用程序util.loadDump()也提供了并行加载功能。请参见 MySQL Shell 实用程序。

10.5.6 优化 InnoDB 查询

原文：dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-queries.html

要为InnoDB表调整查询，为每个表创建适当的索引集。有关详细信息，请参见第 10.3.1 节，“MySQL 如何使用索引”。遵循这些InnoDB索引指南：

因为每个InnoDB表都有一个主键（无论您是否请求），请为每个表指定一组主键列，这些列在最重要和时间关键的查询中使用。
不要在主键中指定太多或太长的列，因为这些列值会在每个二级索引中重复。当索引包含不必要的数据时，读取这些数据的 I/O 和缓存它的内存会降低服务器的性能和可伸缩性。
不要为每一列创建单独的二级索引，因为每个查询只能使用一个索引。很少被测试的列或者只有少量不同值的列上的索引可能对任何查询都没有帮助。如果对同一表有许多查询，测试不同列的组合，请尝试创建少量的连接索引而不是大量的单列索引。如果一个索引包含了结果集所需的所有列（称为覆盖索引），查询可能可以完全避免读取表数据。
如果一个索引列不能包含任何NULL值，请在创建表时将其声明为NOT NULL。当优化器知道每列是否包含NULL值时，可以更好地确定哪个索引对查询最有效。
您可以使用第 10.5.3 节，“优化 InnoDB 只读事务”中的技术，为InnoDB表优化单个查询事务。

10.5.7 优化 InnoDB DDL 操作

原文：dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-ddl-operations.html

表和索引的许多 DDL 操作（CREATE，ALTER和DROP语句）可以在线执行。详细信息请参见第 17.12 节，“InnoDB 和在线 DDL”。
支持在线 DDL 添加辅助索引意味着通常可以通过在加载数据后添加辅助索引来加快创建和加载表及相关索引的过程。
使用TRUNCATE TABLE来清空表，而不是DELETE FROM *tbl_name*。外键约束可能会使TRUNCATE语句像常规的DELETE语句一样工作，在这种情况下，一系列命令，如DROP TABLE和CREATE TABLE可能是最快的。
因为主键对于每个InnoDB表的存储布局至关重要，并且更改主键的定义涉及重新组织整个表，因此始终将主键设置为CREATE TABLE语句的一部分，并提前计划，以便之后不需要ALTER或DROP主键。

10.5.8 优化 InnoDB 磁盘 I/O

原文：dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-diskio.html

如果您遵循数据库设计的最佳实践和 SQL 操作的调优技术，但由于大量磁盘 I/O 活动而导致数据库仍然缓慢，请考虑这些磁盘 I/O 优化。如果 Unix 的 top 工具或 Windows 任务管理器显示您的工作负载的 CPU 使用率百分比低于 70%，那么您的工作负载可能受到磁盘限制。

增加缓冲池大小

当表数据被缓存在 InnoDB 缓冲池中时，可以通过查询重复访问而无需进行任何磁盘 I/O。使用 innodb_buffer_pool_size 选项指定缓冲池的大小。这个内存区域非常重要，通常建议将 innodb_buffer_pool_size 配置为系统内存的 50 到 75%。有关更多信息，请参见 Section 10.12.3.1, “How MySQL Uses Memory”。
调整刷新方法

在某些 GNU/Linux 和 Unix 版本中，使用 Unix 的 fsync() 调用（InnoDB 默认使用）和类似方法将文件刷新到磁盘的速度令人惊讶地慢。如果数据库写入性能成为问题，可以通过将 innodb_flush_method 参数设置为 O_DSYNC 进行基准测试。
配置操作系统刷新的阈值

默认情况下，当 InnoDB 创建一个新的数据文件，例如新的日志文件或表空间文件时，该文件会在刷新到磁盘之前完全写入操作系统缓存，这可能会导致大量的磁盘写入活动一次性发生。为了强制操作系统缓存中的数据进行较小的周期性刷新，可以使用 innodb_fsync_threshold 变量定义一个阈值值，以字节为单位。当达到字节阈值时，操作系统缓存的内容将被刷新到磁盘。默认值为 0，强制默认行为，即仅在文件完全写入缓存后才将数据刷新到磁盘。

指定阈值以强制进行较小的周期性刷新，在多个 MySQL 实例使用相同存储设备的情况下可能会有益。例如，创建一个新的 MySQL 实例及其关联的数据文件可能会导致大量的磁盘写入活动，影响使用相同存储设备的其他 MySQL 实例的性能。配置阈值有助于避免这种写入活动的激增。
使用 fdatasync() 替代 fsync()

在支持 fdatasync() 系统调用的平台上，MySQL 8.0.26 中引入的 innodb_use_fdatasync 变量允许使用 fdatasync() 而不是 fsync() 进行操作系统刷新。fdatasync() 系统调用不会刷新文件元数据，除非需要进行后续数据检索，从而提供潜在的性能优势。

一些 innodb_flush_method 设置的子集，如 fsync、O_DSYNC 和 O_DIRECT 使用 fsync() 系统调用。当使用这些设置时，innodb_use_fdatasync 变量适用。
在 Linux 上使用本机 AIO 时，请使用 noop 或 deadline I/O 调度程序

InnoDB 在 Linux 上使用异步 I/O 子系统（本机 AIO）来执行数据文件页的预读和写入请求。此行为由 innodb_use_native_aio 配置选项控制，默认情况下已启用。使用本机 AIO 时，I/O 调度程序的类型对 I/O 性能有更大的影响。通常建议使用 noop 和 deadline I/O 调度程序。进行基准测试，以确定哪种 I/O 调度程序为您的工作负载和环境提供最佳结果。有关更多信息，请参见第 17.8.6 节，“在 Linux 上使用异步 I/O”。
在 Solaris 10 的 x86_64 架构上使用直接 I/O

在 Solaris 10 的 x86_64 架构（AMD Opteron）上使用 InnoDB 存储引擎时，为避免降低 InnoDB 性能，请对 InnoDB 相关文件使用直接 I/O。要为用于存储 InnoDB 相关文件的整个 UFS 文件系统使用直接 I/O，请使用 forcedirectio 选项挂载它；参见 mount_ufs(1M)。（Solaris 10/x86_64 的默认设置不使用此选项。）要仅对 InnoDB 文件操作应用直接 I/O 而不是整个文件系统，请设置 innodb_flush_method = O_DIRECT。使用此设置，InnoDB 调用 directio() 而不是 fcntl() 进行数据文件的 I/O（不适用于日志文件的 I/O）。
在 Solaris 2.6 或更高版本上为数据和日志文件使用原始存储

在 Solaris 2.6 及更高版本和任何平台（sparc/x86/x64/amd64）上使用具有大 innodb_buffer_pool_size 值的 InnoDB 存储引擎时，使用如前所述的 forcedirectio 挂载选项在原始设备或单独的直接 I/O UFS 文件系统上进行 InnoDB 数据文件和日志文件的基准测试。如果要对日志文件使用直接 I/O，则需要使用挂载选项而不是设置 innodb_flush_method。使用 Veritas 文件系统 VxFS 的用户应使用 convosync=direct 挂载选项。

不要将其他 MySQL 数据文件，如MyISAM表的文件，放在直接 I/O 文件系统上。可执行文件或库不得放在直接 I/O 文件系统上。
使用额外的存储设备

可以使用额外的存储设备设置 RAID 配置。有关相关信息，请参见第 10.12.1 节，“优化磁��I/O”。

或者，InnoDB表空间数据文件和日志文件可以放在不同的物理磁盘上。有关更多信息，请参考以下章节：
- 第 17.8.1 节，“InnoDB 启动配置”
- 第 17.6.1.2 节，“外部创建表”
- 创建通用表空间
- 第 17.6.1.4 节，“移动或复制 InnoDB 表”
考虑非旋转存储

非旋转存储通常为随机 I/O 操作提供更好的性能；而旋转存储适用于顺序 I/O 操作。在将数据和日志文件分布在旋转和非旋转存储设备上时，请考虑每个文件上主要执行的 I/O 操作类型。

随机 I/O 导向的文件通常包括 file-per-table 和 general tablespace 数据文件，undo tablespace 文件，以及 temporary tablespace 文件。顺序 I/O 导向的文件包括InnoDBsystem tablespace 文件（由于 MySQL 8.0.20 之前的 Doublewrite buffering 和 change buffering），MySQL 8.0.20 引入的 doublewrite 文件，以及诸如 binary log 文件和 redo log 文件等日志文件。

使用非旋转存储时，请查看以下配置选项的设置：
- innodb_checksum_algorithm
  
  crc32选项使用更快的校验算法，建议用于快速存储系统。
- innodb_flush_neighbors
  
  为旋转存储设备优化 I/O。对于非旋转存储或旋转和非旋转存储的混合情况，请禁用它。默认情况下禁用。
- innodb_idle_flush_pct
  
  允许在空闲时期限制页面刷新，这有助于延长非旋转存储设备的寿命。在 MySQL 8.0.18 中引入。
- innodb_io_capacity
  
  默认设置为 200 通常足以满足低端非旋转存储设备的需求。对于高端、总线连接设备，请考虑更高的设置，例如 1000。
- innodb_io_capacity_max
  
  默认值为 2000 适用于使用非旋转存储的工作负载。对于高端、总线连接的非旋转存储设备，请考虑更高的设置，例如 2500。
- innodb_log_compressed_pages
  
  如果重做日志存储在非旋转存储上，请考虑禁用此选项以减少日志记录。参见禁用压缩页面的日志记录。
- innodb_log_file_size（在 MySQL 8.0.30 中已弃用）
  
  如果重做日志存储在非旋转存储上，请配置此选项以最大化缓存和写入组合。
- innodb_redo_log_capacity
  
  如果重做日志存储在非旋转存储上，请配置此选项以最大化缓存和写入组合。
- innodb_page_size
  
  考虑使用与磁盘内部扇区大小匹配的页面大小。早期的 SSD 设备通常具有 4KB 扇区大小。一些新设备具有 16KB 扇区大小。默认的InnoDB页面大小为 16KB。保持页面大小接近存储设备块大小可以最大程度地减少被重写到磁盘的未更改数据量。
- binlog_row_image
  
  如果二进制日志存储在非旋转存储上，并且所有表都有主键，请考虑将此选项设置为minimal以减少日志记录。
确保您的操作系统已启用 TRIM 支持。通常情况下，默认情况下已启用。
增加 I/O 容量以避免积压

如果由于InnoDB检查点操作而导致吞吐量周期性下降，请考虑增加innodb_io_capacity配置选项的值。较高的值会导致更频繁的刷新，避免积压的工作导致吞吐量下降。
如果刷新没有落后，请降低 I/O 容量

如果系统没有落后于InnoDB 刷新操作，请考虑降低innodb_io_capacity配置选项的值。通常情况下，您应该尽可能保持此选项值较低，但不要太低以至于导致吞吐量周期性下降，如前述项目所述。在您可以降低选项值的典型情况下，您可能会在SHOW ENGINE INNODB STATUS的输出中看到类似以下组合：
- 历史列表长度较低，低于几千。
- 插入缓冲区合并接近插入的行数。
- 缓冲池中修改的页面始终远低于innodb_max_dirty_pages_pct的缓冲池。（在服务器不进行大量插入操作时进行测量；在进行大量插入操作时，修改页面的百分比会显著上升是正常的。）
- 日志序列号 - 最后检查点小于总InnoDB 日志文件大小的 7/8 或理想情况下小于 6/8。
将系统表空间文件存储在 Fusion-io 设备上

您可以通过将包含双写存储区域的文件存储在支持原子写入的 Fusion-io 设备上，利用双写缓冲区相关的 I/O 优化。（在 MySQL 8.0.20 之前，双写缓冲区存储区域位于系统表空间数据文件中。从 MySQL 8.0.20 开始，存储区域位于双写文件中。请参阅 Section 17.6.4, “Doublewrite Buffer”。）当双写存储区域文件放置在支持原子写入的 Fusion-io 设备上时，双写缓冲区会自动禁用，并且 Fusion-io 原子写入会用于所有数据文件。此功能仅在 Fusion-io 硬件上受支持，并且仅在 Linux 上为 Fusion-io NVMFS 启用。为充分利用此功能，建议将innodb_flush_method设置为O_DIRECT。

注意

由于双写缓冲区设置是全局的，因此对于不位于 Fusion-io 硬件上的数据文件，双写缓冲区也被禁用。
禁用压缩页面的日志记录

当使用InnoDB表的压缩功能时，重新压缩的页的图像在对压缩数据进行更改时会被写入重做日志。这种行为由innodb_log_compressed_pages控制，默认情况下启用，以防止在恢复过程中使用不同版本的zlib压缩算法导致的损坏。如果您确定zlib版本不会更改，请禁用innodb_log_compressed_pages以减少修改压缩数据的工作负载生成的重做日志。

10.5.9 优化 InnoDB 配置变量

原文：dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-configuration-variables.html

不同的设置对于负载轻、可预测的服务器和一直运行在满负荷或经历高活动峰值的服务器效果最佳。

因为InnoDB存储引擎自动执行许多优化，许多性能调整任务涉及监视以确保数据库性能良好，并在性能下降时更改配置选项。有关详细的InnoDB性能监控信息，请参见第 17.16 节，“InnoDB 与 MySQL 性能模式的集成”。

您可以执行的主要配置步骤包括：

控制InnoDB缓冲更改数据的数据更改操作类型，以避免频繁的小磁盘写入。请参见配置更改缓冲。因为默认情况下是缓冲所有类型的数据更改操作，只有在需要减少缓冲量时才更改此设置。
使用innodb_adaptive_hash_index选项打开或关闭自适应哈希索引功能。有关更多信息，请参见第 17.5.3 节，“自适应哈希索引”。您可能会在异常活动期间更改此设置，然后将其恢复为原始设置。
如果上下文切换是瓶颈，设置InnoDB处理的并发线程数量上限。请参见第 17.8.4 节，“配置 InnoDB 的线程并发性”。
控制InnoDB在读取操作中进行预取的数量。当系统有未使用的 I/O 容量时，增加预取可以提高查询性能。过多的预取可能会导致在负载较重的系统上定期性能下降。参见第 17.8.3.4 节，“配置 InnoDB 缓冲池预取（预读）”。
如果您有一个高端 I/O 子系统，默认值未完全利用，则增加后台线程的数量以进行读取或写入操作。参见第 17.8.5 节，“配置后台 InnoDB I/O 线程的数量”。
控制InnoDB在后台执行的 I/O 量。参见 Section 17.8.7, “配置 InnoDB I/O 容量”。如果观察到性能周期性下降，您可能需要缩减此设置。
控制确定InnoDB何时执行某些类型后台写入的算法。参见 Section 17.8.3.5, “配置缓冲池刷新”。该算法适用于某些工作负载，但不适用于其他工作负载，因此如果观察到性能周期性下降，您可能需要禁用此功能。
充分利用多核处理器及其缓存内存配置，以最小化上下文切换的延迟。参见 Section 17.8.8, “配置自旋锁轮询”。
防止一次性操作（如表扫描）干扰存储在InnoDB缓冲池中的频繁访问数据。参见 Section 17.8.3.3, “使缓冲池扫描抗干扰”。
调整日志文件的大小，以确保可靠性和崩溃恢复。InnoDB日志文件通常保持较小，以避免崩溃后长时间的启动时间。MySQL 5.5 中引入的优化加快了崩溃恢复过程的某些步骤。特别是，由于改进的内存管理算法，扫描重做日志和应用重做日志的速度更快。如果您人为地保持日志文件较小以避免长时间的启动时间，现在可以考虑增加日志文件大小，以减少由重做日志记录回收引起的 I/O。
配置InnoDB缓冲池的大小和实例数量，对于具有多个千兆字节缓冲池的系统尤为重要。参见 Section 17.8.3.2, “配置多个缓冲池实例”。
增加最大并发事务数，极大地提高了最繁忙数据库的可伸缩性。参见 Section 17.6.6, “撤销日志”。
将清除操作（一种垃圾收集类型）移入后台线程。参见 Section 17.8.9, “清除配置”。为了有效地衡量此设置的结果，首先调整其他与 I/O 和线程相关的配置设置。
减少InnoDB在并发线程之间的切换次数，以使繁忙服务器上的 SQL 操作不会排队形成“交通堵塞”。为innodb_thread_concurrency选项设置一个值，对于高性能现代系统，可以设置为大约 32。增加innodb_concurrency_tickets选项的值，通常设置为 5000 左右。这些选项的组合设置了InnoDB在任一时间处理的线程数上限，并允许每个线程在被交换出之前完成大量工作，从而保持等待线程数较低，使操作可以在没有过多上下文切换的情况下完成。

10.5.10 优化具有多个表的 InnoDB 系统

原文：dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-many-tables.html

如果您已配置了非持久化优化器统计信息（非默认配置），InnoDB在首次访问表时计算索引基数值，而不是将这些值存储在表中。在将数据分区到多个表的系统上，这一步可能需要较长时间。由于这种开销仅适用于初始表打开操作，为了“预热”表以供以后使用，立即在启动后访问它，例如通过发出类似SELECT 1 FROM *tbl_name* LIMIT 1的语句。

优化器统计信息默认情况下会持久化到磁盘，通过innodb_stats_persistent配置选项启用。有关持久化优化器统计信息的信息，请参阅第 17.8.10.1 节，“配置持久化优化器统计参数”。

10.6 MyISAM 表优化

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

10.6.1 优化 MyISAM 查询

10.6.2 MyISAM 表的批量数据加载

10.6.3 优化 REPAIR TABLE 语句

MyISAM 存储引擎在处理读多写少的数据或低并发操作时表现最佳，因为表锁限制了同时进行更新的能力。在 MySQL 中，默认的存储引擎是 InnoDB 而不是 MyISAM。

10.6.1 优化 MyISAM 查询

原文：dev.mysql.com/doc/refman/8.0/en/optimizing-queries-myisam.html

一些加快MyISAM表查询速度的一般提示：

为了帮助 MySQL 更好地优化查询，请使用ANALYZE TABLE或在加载数据后对表运行myisamchk --analyze。这将更新每个索引部分的一个值，该值表示具有相同值的平均行数。（对于唯一索引，这始终是 1。） MySQL 在基于非常量表达式连接两个表时使用此值来决定选择哪个索引。您可以通过使用SHOW INDEX FROM *tbl_name*并检查Cardinality值来检查表分析的结果。 myisamchk --description --verbose显示索引分布信息。
要根据索引对索引和数据进行排序，请使用myisamchk --sort-index --sort-records=1（假设您要按索引 1 排序）。如果您有一个想要按照索引顺序读取所有行的唯一索引，这是使查询更快的好方法。第一次以这种方式对大表进行排序可能需要很长时间。
尽量避免在频繁更新的MyISAM表上执行复杂的SELECT查询，以避免由于读者和写者之间的争用而导致的表锁定问题。
MyISAM支持并发插入：如果表在数据文件中间没有空闲块，则可以在其他线程从表中读取时向其中插入新行。如果能够这样做很重要，请考虑以避免删除行的方式使用表。另一种可能性是在从表中删除大量行后运行OPTIMIZE TABLE来碎片整理表。通过设置concurrent_insert变量来改变此行为。您可以强制追加新行（从而允许并发插入），即使在已删除行的表中也是如此。请参阅第 10.11.3 节，“并发插入”。
对于频繁更改的MyISAM表，请尽量避免所有可变长度列（VARCHAR、BLOB和TEXT）。如果表包含任何一个可变长度列，表将使用动态行格式。参见 Chapter 18, Alternative Storage Engines。
通常不值得将表拆分为不同的表，只是因为行变得很大。在访问行时，最大的性能损失是需要查找行的第一个字节的磁盘寻道。找到数据后，大多数现代磁盘可以以足够快的速度读取整个行，适用于大多数应用程序。唯一需要拆分表的情况是，如果是使用动态行格式的MyISAM表，您可以将其更改为固定行大小，或者如果您经常需要扫描表但不需要大多数列。参见 Chapter 18, Alternative Storage Engines。
如果您通常按*expr1*, *expr2*, ...顺序检索行，则使用ALTER TABLE ... ORDER BY *expr1*, *expr2*, ...。在对表进行广泛更改后使用此选项，您可能能够获得更高的性能。
如果您经常需要根据大量行的信息计算结果，可能最好引入一个新表并实时更新计数器。以下形式的更新非常快：
```
UPDATE *tbl_name* SET *count_col*=*count_col*+1 WHERE *key_col*=*constant*;
```
当您使用只有表级锁定（多个读者与单个写者）的 MySQL 存储引擎，如MyISAM时，这一点非常重要。这也可以提高大多数数据库系统的性能，因为在这种情况下，行锁定管理器的工作量较少。
定期使用OPTIMIZE TABLE以避免动态格式MyISAM表的碎片化。参见 Section 18.2.3, “MyISAM Table Storage Formats”。
使用DELAY_KEY_WRITE=1表选项声明MyISAM表可以使索引更新更快，因为它们在表关闭之前不会刷新到磁盘。缺点是，如果在打开这样的表时有什么东西终止了服务器，您必须通过设置myisam_recover_options系统变量来确保表的正常，或者在重新启动服务器之前运行myisamchk。（然而，即使在这种情况下，使用DELAY_KEY_WRITE也不会丢失任何内容，因为关键信息始终可以从数据行生成。）
在MyISAM索引中，字符串会自动进行前缀和末尾空格压缩。参见第 15.1.15 节，“CREATE INDEX 语句”。
通过在应用程序中缓存查询或答案，然后一次性执行多个插入或更新操作，可以提高性能。在此操作期间锁定表确保索引缓存仅在所有更新完成后刷新一次。

10.6.2 MyISAM 表的批量数据加载

原文：dev.mysql.com/doc/refman/8.0/en/optimizing-myisam-bulk-data-loading.html

这些性能提示补充了 Section 10.2.5.1, “Optimizing INSERT Statements”中快速插入的��般准则。

对于MyISAM表，如果数据文件中间没有删除的行，可以使用并发插入同时添加行，同时运行SELECT语句。参见 Section 10.11.3, “Concurrent Inserts”。
经过一些额外工作，可以使LOAD DATA在MyISAM表中运行得更快，尤其是当表中有许多索引时。使用以下过程：
1. 执行FLUSH TABLES语句或mysqladmin flush-tables命令。
2. 使用myisamchk --keys-used=0 -rq /path/to/db/tbl_name来删除表中所有索引的使用。
3. 使用LOAD DATA向表中插入数据。这不会更新任何索引，因此非常快。
4. 如果将来只打算从表中读取数据，请使用myisampack对其进行压缩。参见 Section 18.2.3.3, “Compressed Table Characteristics”。
5. 使用myisamchk -rq /path/to/db/tbl_name重新创建索引。这会在将索引写入磁盘之前在内存中创建索引树，比在LOAD DATA期间更新索引要快得多，因为它避免了大量的磁盘查找。生成的索引树也是完全平衡的。
6. 执行FLUSH TABLES语句或mysqladmin flush-tables命令。
LOAD DATA 如果你要插入数据的MyISAM表是空的，它会自动执行前述优化。自动优化和显式使用该过程的主要区别在于，你可以让myisamchk为索引创建分配更多临时内存，而不是在执行LOAD DATA语句时让服务器为索引重建分配更多内存。

你也可以通过以下语句来禁用或启用MyISAM表的非唯一索引，而不是使用myisamchk。如果使用这些语句，你可以跳过FLUSH TABLES操作：
```
ALTER TABLE *tbl_name* DISABLE KEYS;
ALTER TABLE *tbl_name* ENABLE KEYS;
```
为了加快对非事务表执行的多语句INSERT操作的速度，锁定你的表：
```
LOCK TABLES a WRITE;
INSERT INTO a VALUES (1,23),(2,34),(4,33);
INSERT INTO a VALUES (8,26),(6,29);
...
UNLOCK TABLES;
```
这有助于性能，因为索引缓冲区仅在所有INSERT语句完成后一次性刷新到磁盘。通常情况下，会有与INSERT语句数量相同的索引缓冲区刷新。如果你可以使用单个INSERT插入所有行，则不需要显式的锁定语句。

锁定还降低了多连接测试的总时间，尽管单个连接的最大等待时间可能会增加，因为它们在等待锁。假设五个客户端同时尝试执行插入操作如下：
- 连接 1 执行 1000 次插入
- 连接 2、3 和 4 执行 1 次插入
- 连接 5 执行 1000 次插入
如果不使用锁定，连接 2、3 和 4 会在 1 和 5 之前完成。如果使用锁定，连接 2、3 和 4 可能不会在 1 或 5 之前完成，但总时间应该快约 40%。

在 MySQL 中，INSERT、UPDATE和DELETE操作非常快，但通过在进行超过约五次连续插入或更新的操作周围添加锁，你可以获得更好的整体性能。如果你进行了很多连续的插入操作，你可以偶尔执行一次LOCK TABLES，然后再执行一次UNLOCK TABLES（每 1000 行左右），以允许其他线程访问表。这仍然会带来良好的性能提升。

对于加载数据，INSERT仍然比LOAD DATA慢得多，即使使用了刚刚概述的策略。
为了提高MyISAM表的性能，无论是对于LOAD DATA还是INSERT，都可以通过增加key_buffer_size系统变量来扩大键缓存。参见第 7.1.1 节，“配置服务器”。

10.6.3 优化修复表语句

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

修复表对于MyISAM表类似于使用myisamchk进行修复操作，并且一些相同的性能优化也适用：

myisamchk具有控制内存分配的变量。您可以通过设置这些变量来改善性能，如第 6.6.4.6 节“myisamchk 内存使用”中所述。
对于修复表，同样的原则适用，但由于修复是由服务器完成的，您需要设置服务器系统变量，而不是myisamchk变量。此外，除了设置内存分配变量外，增加myisam_max_sort_file_size系统变量的值会增加修复使用更快的文件排序方法并避免较慢的按键缓存方法的可能性。在检查确保有足够的空间来保存表文件的副本后，将变量设置为系统的最大文件大小。空间必须在包含原始表文件的文件系统中可用。

假设使用以下选项设置其内存分配变量进行myisamchk表修复操作：

--key_buffer_size=128M --myisam_sort_buffer_size=256M
--read_buffer_size=64M --write_buffer_size=64M

一些myisamchk变量对应于服务器系统变量：

myisamchk变量	系统变量
`key_buffer_size`	`key_buffer_size`
`myisam_sort_buffer_size`	`myisam_sort_buffer_size`
`read_buffer_size`	`read_buffer_size`
`write_buffer_size`	无

每个服务器系统变量都可以在运行时设置，其中一些变量（myisam_sort_buffer_size，read_buffer_size）除了全局值外还有一个会话值。设置会话值会限制更改的影响范围仅限于当前会话，并不影响其他用户。更改全局变量（key_buffer_size，myisam_max_sort_file_size）会影响其他用户。对于key_buffer_size，您必须考虑到该缓冲区与其他用户共享。例如，如果您将myisamchk的key_buffer_size变量设置为 128MB，您可以将相应的key_buffer_size系统变量设置得比这更大（如果尚未设置更大），以允许其他会话中的活动使用键缓冲区。然而，更改全局键缓冲区大小会使缓冲区无效，导致其他会话的磁盘 I/O 增加并减慢。避免这个问题的替代方法是使用一个单独的键缓存，将要修复的表的索引分配给它，并在修复完成后取消分配。参见 Section 10.10.2.2, “Multiple Key Caches”。

根据前述说明，可以按照以下方式执行REPAIR TABLE操作，以使用类似于myisamchk命令的设置。在这里，分配了一个单独的 128MB 键缓冲区，并假定文件系统至少允许文件大小为 100GB。

SET SESSION myisam_sort_buffer_size = 256*1024*1024;
SET SESSION read_buffer_size = 64*1024*1024;
SET GLOBAL myisam_max_sort_file_size = 100*1024*1024*1024;
SET GLOBAL repair_cache.key_buffer_size = 128*1024*1024;
CACHE INDEX *tbl_name* IN repair_cache;
LOAD INDEX INTO CACHE *tbl_name*;
REPAIR TABLE *tbl_name* ;
SET GLOBAL repair_cache.key_buffer_size = 0;

如果您打算更改一个全局变量，但只想在REPAIR TABLE操作的持续时间内对其他用户的影响最小化，可以将其值保存在用户变量中，并在操作后恢复。例如：

SET @old_myisam_sort_buffer_size = @@GLOBAL.myisam_max_sort_file_size;
SET GLOBAL myisam_max_sort_file_size = 100*1024*1024*1024;
REPAIR TABLE tbl_name ;
SET GLOBAL myisam_max_sort_file_size = @old_myisam_max_sort_file_size;

影响REPAIR TABLE的系统变量可以在服务器启动时全局设置，如果您希望这些值默认生效。例如，将以下行添加到服务器的my.cnf文件中：

[mysqld]
myisam_sort_buffer_size=256M
key_buffer_size=1G
myisam_max_sort_file_size=100G

这些设置不包括read_buffer_size。将read_buffer_size全局设置为一个较大的值会影响所有会话，并且可能会因为为具有许多同时会话的服务器进行过多的内存分配而导致性能下降。

10.7 为 MEMORY 表进行优化

原文：dev.mysql.com/doc/refman/8.0/en/optimizing-memory-tables.html

考虑使用MEMORY表存储经常访问的非关键数据，这些数据是只读的或很少更新的。通过在真实工作负载下将应用程序与等效的InnoDB或MyISAM表进行基准测试，以确认任何额外性能是否值得冒失去数据的风险，或者在应用程序启动时从基于磁盘的表复制数据的开销。

为了获得MEMORY表的最佳性能，请检查针对每个表的查询类型，并为每个关联索引指定要使用的类型，可以是 B-tree 索引或哈希索引。在CREATE INDEX语句中，使用USING BTREE或USING HASH子句。B-tree 索引对于通过诸如>或BETWEEN等运算符进行大于或小于比较的查询非常快速。哈希索引仅对通过=运算符查找单个值或通过IN运算符查找一组受限制的值的查询非常快速。关于为什么USING BTREE通常比默认的USING HASH更好的选择，请参见 Section 10.2.1.23, “Avoiding Full Table Scans”。有关不同类型MEMORY索引的实现细节，请参见 Section 10.3.9, “Comparison of B-Tree and Hash Indexes”。

10.8 理解查询执行计划

原文：dev.mysql.com/doc/refman/8.0/en/execution-plan-information.html

10.8.1 使用 EXPLAIN 优化查询

10.8.2 EXPLAIN 输出格式

10.8.3 扩展的 EXPLAIN 输出格式

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

10.8.5 估算查询性能

根据您的表、列、索引的细节以及WHERE子句中的条件，MySQL 优化器考虑许多技术来高效执行 SQL 查询中涉及的查找操作。对于大表的查询可以在不读取所有行的情况下执行；涉及多个表的连接可以在不比较每个行的组合的情况下执行。优化器选择执行最有效查询的一组操作称为“查询执行计划”，也称为EXPLAIN计划。您的目标是识别EXPLAIN计划中表明查询已经优化良好的方面，并学习 SQL 语法和索引技术，以改进计划，如果您看到一些低效的操作。

10.8.1 使用 EXPLAIN 优化查询

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

EXPLAIN语句提供有关 MySQL 如何执行语句的信息：

EXPLAIN适用于SELECT、DELETE、INSERT、REPLACE和UPDATE语句。
当使用EXPLAIN解释可解释的语句时，MySQL 会显示有关语句执行计划的优化器信息。也就是说，MySQL 会解释它将如何处理该语句，包括有关表如何连接以及顺序的信息。有关使用EXPLAIN获取执行计划信息的信息，请参见 Section 10.8.2, “EXPLAIN Output Format”。
当使用EXPLAIN与FOR CONNECTION *connection_id*而不是可解释的语句一起使用时，它会显示在指定连接中执行的语句的执行计划。请参见 Section 10.8.4, “Obtaining Execution Plan Information for a Named Connection”。
对于SELECT语句，EXPLAIN生成可使用SHOW WARNINGS显示的附加执行计划信息。请参见 Section 10.8.3, “Extended EXPLAIN Output Format”。
EXPLAIN对于检查涉及分区表的查询很有用。请参见 Section 26.3.5, “Obtaining Information About Partitions”。
FORMAT选项可用于选择输出格式。TRADITIONAL以表格格式呈现输出。如果没有FORMAT选项，则默认为此格式。JSON格式以 JSON 格式显示信息。

借助EXPLAIN，您可以看到应该向表中添加索引以使语句通过使用索引查找行而更快执行的位置。您还可以使用EXPLAIN来检查优化器是否以最佳顺序连接表。为了向优化器提供提示，使用与SELECT语句中表的命名顺序相对应的连接顺序，可以在语句开头使用SELECT STRAIGHT_JOIN而不仅仅是SELECT。（参见 Section 15.2.13, “SELECT Statement”。）然而，STRAIGHT_JOIN可能会阻止索引的使用，因为它禁用了半连接转换。请参见 Section 10.2.2.1, “Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations”。

优化器跟踪有时可能提供与EXPLAIN互补的信息。然而，优化器跟踪格式和内容可能会在版本之间发生变化。有关详细信息，请参见 MySQL Internals: Tracing the Optimizer。

如果您发现索引没有被使用，而您认为它们应该被使用，请运行ANALYZE TABLE来更新表统计信息，例如键的基数，这可能会影响优化器的选择。请参见 Section 15.7.3.1, “ANALYZE TABLE Statement”。

注意

EXPLAIN也可以用于获取表中列的信息。EXPLAIN *tbl_name*与DESCRIBE *tbl_name*和SHOW COLUMNS FROM *tbl_name*是同义的。有关更多信息，请参见 Section 15.8.1, “DESCRIBE Statement”和 Section 15.7.7.5, “SHOW COLUMNS Statement”。

10.8.2 EXPLAIN 输出格式

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

EXPLAIN语句提供有关 MySQL 如何执行语句的信息。EXPLAIN与SELECT，DELETE，INSERT，REPLACE和UPDATE语句一起使用。

EXPLAIN为SELECT语句中使用的每个表返回一行信息。它按照 MySQL 在处理语句时读取它们的顺序在输出中列出表。这意味着 MySQL 从第一个表中读取一行，然后在第二个表中找到匹配的行，然后在第三个表中找到匹配的行，依此类推。当所有表都被处理时，MySQL 输出所选列，并通过表列表回溯，直到找到一个表，其中有更多匹配的行。从这个表中读取下一行，然后继续下一个表的过程。

注意

MySQL Workbench 具有 Visual Explain 功能，提供了EXPLAIN输出的可视化表示。请参阅 Tutorial: Using Explain to Improve Query Performance。

EXPLAIN 输出列
EXPLAIN 连接类型
EXPLAIN 额外信息
EXPLAIN 输出解释

EXPLAIN 输出列

本节描述了EXPLAIN生成的输出列。后续章节提供有关type和Extra列的附加信息。

每个EXPLAIN的输出行提供有关一个表的信息。每行包含表 10.1，“EXPLAIN 输出列”中总结的值，并在表后详细描述。表中显示列名在表的第一列；第二列提供了在使用FORMAT=JSON时输出中显示的等效属性名称。

表 10.1 EXPLAIN 输出列

列	JSON 名称	含义
`id`	`select_id`	`SELECT`标识符
`select_type`	None	`SELECT` 类型
`table`	`table_name`	输出行的表
`partitions`	`partitions`	匹配的分区
`type`	`access_type`	连接类型
`possible_keys`	`possible_keys`	可选择的索引
`key`	`key`	实际选择的索引
`key_len`	`key_length`	所选键的长度
`ref`	`ref`	与索引进行比较的列
`rows`	`rows`	预计要检查的行数
`filtered`	`filtered`	表条件过滤的行的百分比
`Extra`	None	附加信息
列	JSON 名称	含义

注意

在 JSON 格式的 EXPLAIN 输出中，值为 NULL 的 JSON 属性不会显示。

id（JSON 名称：select_id）

SELECT标识符。这是查询中SELECT的顺序号。如果行引用其他行的联合结果，则该值可以为NULL。在这种情况下，table列显示类似<union*M*,*N*>的值，表示该行引用具有id值为M和N的行的联合。

select_type（JSON 名称：无）

SELECT的类型，可以是以下表中显示的任何类型之一。JSON 格式的 EXPLAIN 将 SELECT 类型公开为 query_block 的属性，除非它是 SIMPLE 或 PRIMARY。表中还显示了 JSON 名称（如果适用）。

`select_type` 值	JSON 名称	含义
`SIMPLE`	None	简单的`SELECT`（不使用`UNION`或子查询）
`PRIMARY`	None	最外层的`SELECT`
`UNION`	None	`UNION`中的第二个或更后的`SELECT`语句
`DEPENDENT UNION`	`dependent` (`true`)	`UNION`中的第二个或更后的`SELECT`语句，依赖于外部查询
`UNION RESULT`	`union_result`	`UNION`的结果。
`SUBQUERY`	None	子查询中的第一个`SELECT`
`DEPENDENT SUBQUERY`	`dependent`（`true`）	子查询中的第一个`SELECT`，依赖于外部查询
`DERIVED`	无	派生表
`DEPENDENT DERIVED`	`dependent`（`true`）	依赖于另一张表的派生表
`MATERIALIZED`	`materialized_from_subquery`	物化子查询
`UNCACHEABLE SUBQUERY`	`cacheable`（`false`）	不能缓存结果且必须对外部查询的每一行重新评估的子查询
`UNCACHEABLE UNION`	`cacheable`（`false`）	`UNION`中的第二个或更后续的选择属于不可缓存子查询（参见`UNCACHEABLE SUBQUERY`）
`select_type`值	JSON 名称	含义

DEPENDENT通常表示使用相关子查询。参见第 15.2.15.7 节，“相关子查询”。

DEPENDENT SUBQUERY评估与UNCACHEABLE SUBQUERY评估不同。对于DEPENDENT SUBQUERY，子查询仅针对其外部上下文的不同变量值集重新评估一次。对于UNCACHEABLE SUBQUERY，子查询对外部上下文的每一行重新评估。

当你在EXPLAIN中指定FORMAT=JSON时，输出中没有与select_type直接等价的单个属性；query_block属性对应于给定的SELECT。大多数刚刚显示的SELECT子查询类型的等效属性是可用的（例如materialized_from_subquery对应于MATERIALIZED），并在适当时显示。对于SIMPLE或PRIMARY，没有 JSON 等价物。

非SELECT语句的select_type值显示受影响表的语句类型。例如，对于DELETE语句，select_type为DELETE。

table（JSON 名称：table_name）

输出行所指的表的名称。这也可以是以下值之一：
- <union*M*,*N*>：该行指的是具有id值为M和N的行的并集。
- <derived*N*>：该行指的是具有id值为N的行的派生表结果。派生表可能是由FROM子句中的子查询导致的。
- <subquery*N*>：该行指的是具有id值为N的行的物化子查询的结果。参见第 10.2.2.2 节，“使用物化进行子查询优化”。
partitions（JSON 名称：partitions）

查询将匹配的分区。对于非分区表，该值为NULL。参见第 26.3.5 节，“获取有关分区的信息”。
type（JSON 名称：access_type）

连接类型。有关不同类型的描述，请参见EXPLAIN Join Types。
possible_keys（JSON 名称：possible_keys）

possible_keys列指示 MySQL 可以选择从中查找此表中的行的索引。请注意，此列与从EXPLAIN输出中显示的表的顺序无关。这意味着possible_keys中的一些键在实践中可能无法与生成的表顺序一起使用。

如果此列为NULL（或在 JSON 格式输出中未定义），则没有相关的索引。在这种情况下，您可以通过检查WHERE子句来查看是否引用了适合索引的某些列，从而改善查询的性能。如果是这样，请创建一个适当的索引，并再次使用EXPLAIN检查查询。参见第 15.1.9 节，“ALTER TABLE Statement”。

要查看表具有哪些索引，请使用SHOW INDEX FROM *tbl_name*。
key（JSON 名称：key）

key列指示 MySQL 实际决定使用的键（索引）。如果 MySQL 决定使用possible_keys中的一个索引来查找行，那么该索引将列为键值。

key可能指的是possible_keys值中不存在的索引。如果possible_keys中的索引都不适合查找行，但查询选择的所有列都是某个其他索引的列，就会发生这种情况。也就是说，命名的索引覆盖了选择的列，因此虽然它不用于确定要检索哪些行，但索引扫描比数据行扫描更有效率。

对于InnoDB，即使查询还选择了主键，次要索引也可能覆盖了选择的列，因为InnoDB将主键值与每个次要索引一起存储。如果key为NULL，则 MySQL 找不到用于更有效地执行查询的索引。

要强制 MySQL 使用或忽略possible_keys列中列出的索引，请在查询中使用FORCE INDEX、USE INDEX或IGNORE INDEX。参见第 10.9.4 节，“Index Hints”。

对于MyISAM表，运行ANALYZE TABLE有助于优化器选择更好的索引。对于MyISAM表，myisamchk --analyze也是一样的。参见第 15.7.3.1 节，“ANALYZE TABLE Statement”，以及第 9.6 节，“MyISAM Table Maintenance and Crash Recovery”。
key_len（JSON 名称：key_length）

key_len列指示 MySQL 决定使用的键的长度。key_len的值使您能够确定 MySQL 实际使用多部分键的部分数。如果key列说NULL，则key_len列也说NULL。

由于键存储格式，对于可以为NULL的列，键长度比NOT NULL列多一个。
ref（JSON 名称：ref）

ref列显示与在key列中命名的索引进行比较以从表中选择行的哪些列或常量。

如果值为func，则使用的值是某个函数的结果。要查看哪个函数，请在EXPLAIN之后使用SHOW WARNINGS查看扩展的EXPLAIN输出。该函数实际上可能是算术运算符等运算符。
rows（JSON 名称：rows）

rows列指示 MySQL 认为必须检查的行数以执行查询。

对于InnoDB表，此数字是一个估计值，可能并不总是准确的。
filtered（JSON 名称：filtered）

filtered列指示由表条件过滤的表行的估计百分比。最大值为 100，表示未发生任何行过滤。从 100 递减的值表示过滤量增加。rows显示估计检查的行数，rows × filtered显示与下表连接的行数。例如，如果rows为 1000，filtered为 50.00（50％），则要与下表连接的行数为 1000 × 50％= 500。
Extra（JSON 名称：无）

此列包含有关 MySQL 如何解析查询的其他信息。有关不同值的描述，请参见EXPLAIN Extra Information。

没有与Extra列对应的单个 JSON 属性；但是，此列中可能出现的值会作为 JSON 属性或作为message属性的文本公开。

解释连接类型

EXPLAIN输出的type列描述了表是如何连接的。在 JSON 格式的输出中，这些被发现为access_type属性的值。以下列表描述了连接类型，从最佳类型到最差类型：

system

表只有一行（=系统表）。这是const连接类型的特殊情况。
const

该表最多只有一行匹配行，在查询开始时读取。因为只有一行，来自此行的列值可以被优化器的其余部分视为常量。const表非常快，因为只读取一次。

当您将PRIMARY KEY或UNIQUE索引的所有部分与常量值进行比较时，将使用const。在以下查询中，tbl_name可以作为const表使用：
```
SELECT * FROM *tbl_name* WHERE *primary_key*=1;

SELECT * FROM *tbl_name*
  WHERE *primary_key_part1*=1 AND *primary_key_part2*=2;
```
eq_ref

对于前面表的每一行组合，从该表中读取一行。除了system和const类型之外，这是最佳的连接类型。当连接使用索引的所有部分并且索引是PRIMARY KEY或UNIQUE NOT NULL索引时使用。

可以用于使用=运算符比较的索引列。比较值可以是常量或使用在此表之前读取的表的列的表达式。在以下示例中，MySQL 可以使用eq_ref连接来处理ref_table：
```
SELECT * FROM *ref_table*,*other_table*
  WHERE *ref_table*.*key_column*=*other_table*.*column*;

SELECT * FROM *ref_table*,*other_table*
  WHERE *ref_table*.*key_column_part1*=*other_table*.*column*
  AND *ref_table*.*key_column_part2*=1;
```
ref

对于前面表的每一行组合，从该表中读取具有匹配索引值的所有行。如果连接仅使用键的最左前缀或键不是PRIMARY KEY或UNIQUE索引（换句话说，如果连接不能基于键值选择单行），则使用ref。如果使用的键仅匹配少数行，则这是一种很好的连接类型。

可以用于使用=或<=>运算符比较的索引列。在以下示例中，MySQL 可以使用ref连接来处理ref_table：
```
SELECT * FROM *ref_table* WHERE *key_column*=*expr*;

SELECT * FROM *ref_table*,*other_table*
  WHERE *ref_table*.*key_column*=*other_table*.*column*;

SELECT * FROM *ref_table*,*other_table*
  WHERE *ref_table*.*key_column_part1*=*other_table*.*column*
  AND *ref_table*.*key_column_part2*=1;
```
fulltext

使用FULLTEXT索引执行连接。
ref_or_null

这种连接类型类似于ref，但额外搜索包含NULL值的行。这种连接类型优化通常在解析子查询时最常使用。在以下示例中，MySQL 可以使用ref_or_null连接来处理ref_table：
```
SELECT * FROM *ref_table*
  WHERE *key_column*=*expr* OR *key_column* IS NULL;
```
请参阅第 10.2.1.15 节，“IS NULL Optimization”。
index_merge

此连接类型指示使用了索引合并优化。在这种情况下，输出行中的key列包含使用的索引列表，key_len包含用于索引的最长键部分列表。有关更多信息，请参见第 10.2.1.3 节，“索引合并优化”。
unique_subquery

对于以下形式的一些IN子查询，此类型替换了eq_ref：
```
*value* IN (SELECT *primary_key* FROM *single_table* WHERE *some_expr*)
```
unique_subquery只是一个索引查找函数，完全替换子查询以提高效率。
index_subquery

此连接类型类似于unique_subquery。它替换了IN子查询，但适用于以下形式的非唯一索引子查询：
```
*value* IN (SELECT *key_column* FROM *single_table* WHERE *some_expr*)
```
range

仅检索给定范围内的行，使用索引选择行。输出行中的key列指示使用的索引。key_len包含使用的最长键部分。对于这种类型，ref列为NULL。

当使用任何=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN、LIKE或IN()运算符将键列与常量进行比较时，可以使用range：
```
SELECT * FROM *tbl_name*
  WHERE *key_column* = 10;

SELECT * FROM *tbl_name*
  WHERE *key_column* BETWEEN 10 and 20;

SELECT * FROM *tbl_name*
  WHERE *key_column* IN (10,20,30);

SELECT * FROM *tbl_name*
  WHERE *key_part1* = 10 AND *key_part2* IN (10,20,30);
```
index

index连接类型与ALL相同，只是扫描索引树。有两种情况：
- 如果索引是查询的覆盖索引，并且可以用于满足表中所需的所有数据，那么只扫描索引树。在这种情况下，Extra列显示Using index。索引扫描通常比ALL更快，因为索引的大小通常比表数据小。
- 执行全表扫描，使用从索引读取的数据行按索引顺序查找数据行。Extra列中不会出现Uses index。
当查询仅使用属于单个索引的列时，MySQL 可以使用此连接类型。
ALL

对于前面表的每个行组合，都会进行完整表扫描。如果表不是第一个未标记为 const 的表，通常情况下这是不好的，而在其他情况下通常是非常糟糕的。通常，您可以通过添加索引来避免 ALL，这些索引使得可以基于常量值或来自早期表的列值检索行。

EXPLAIN 额外信息

EXPLAIN 输出的 Extra 列包含关于 MySQL 如何解析查询的附加信息。以下列表解释了此列中可能出现的值。每个项目还指示了 JSON 格式输出中显示 Extra 值的属性。对于其中一些，有一个特定的属性。其他显示为 message 属性的文本。

如果要使查询尽可能快速，请注意 Extra 列值为 Using filesort 和 Using temporary，或者在 JSON 格式的 EXPLAIN 输出中，using_filesort 和 using_temporary_table 属性等于 true。

Backward index scan (JSON：backward_index_scan)

优化器能够在 InnoDB 表上使用降序索引。与 Using index 一起显示。有关更多信息，请参见 Section 10.3.13, “Descending Indexes”。
Child of '*table*' pushed join@1 (JSON：message 文本)

此表被引用为 table 的子表，可以将其推送到 NDB 内核中进行连接。仅适用于 NDB Cluster，在启用推送连接时。有关更多信息和示例，请参见 ndb_join_pushdown 服务器系统变量的描述。
const row not found (JSON 属性：const_row_not_found)

对于诸如 SELECT ... FROM *tbl_name* 的查询，表是空的。
Deleting all rows (JSON 属性：message)

对于 DELETE，一些存储引擎（例如 MyISAM）支持一种移除所有表行的处理程序方法，以简单快速的方式执行。如果引擎使用此优化，则显示此 Extra 值。
Distinct (JSON 属性：distinct)

MySQL 正在寻找不同的值，因此在找到第一个匹配行后，停止为当前行组合搜索更多行。
FirstMatch(*tbl_name*) (JSON 属性：first_match)

半连接 FirstMatch 加入快捷策略用于 tbl_name。
Full scan on NULL key (JSON 属性：message)

当优化器无法使用索引查找访问方法时，对于子查询优化，这是一个后备策略。
Impossible HAVING (JSON 属性：message)

HAVING 子句始终为假，无法选择任何行。
Impossible WHERE (JSON 属性：message)

WHERE子句始终为 false，无法选择任何行。
在读取 const 表后注意到不可能的 WHERE（JSON 属性：message）。

MySQL 已读取所有const（和system）表，并注意到WHERE子句始终为 false。
LooseScan(*m*..*n*)（JSON 属性：message）。

使用半连接 LooseScan 策略。m和n是关键部分号。
没有匹配的最小/最大行（JSON 属性：message）。

没有任何行满足查询条件，比如SELECT MIN(...) FROM ... WHERE *condition*。
const表中没有匹配的行（JSON 属性：message）。

对于具有连接的查询，存在一个空表或一个表不满足唯一索引条件的行。
分区修剪后没有匹配的行（JSON 属性：message）。

对于DELETE或UPDATE语句，在分区修剪后，优化器发现没有要删除或更新的内容。这在某种程度上类似于SELECT语句的Impossible WHERE。
未使用任何表（JSON 属性：message）。

查询没有FROM子句，或者有一个FROM DUAL子句。

对于INSERT或REPLACE语句，当没有SELECT部分时，EXPLAIN显示此值。例如，对于EXPLAIN INSERT INTO t VALUES(10)，因为这等效于EXPLAIN INSERT INTO t SELECT 10 FROM DUAL。
不存在（JSON 属性：message）。

MySQL 能够在查询上进行LEFT JOIN优化，并在找到符合LEFT JOIN条件的一行后，不再检查此表中的其他行组合。以下是可以通过这种方式优化的查询类型示例：
```
SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id
  WHERE t2.id IS NULL;
```
假设t2.id被定义为NOT NULL。在这种情况下，MySQL 扫描t1并使用t1.id的值查找t2中的行。如果 MySQL 在t2中找到匹配的行，它知道t2.id永远不会是NULL，并且不会扫描具有相同id值的t2中的其他行。换句话说，对于t1中的每一行，MySQL 只需要在t2中进行一次查找，而不管实际上有多少行与t2中的id值匹配。

在 MySQL 8.0.17 及更高版本中，这也可能表示形如NOT IN (*subquery*)或NOT EXISTS (*subquery*)的WHERE条件已在内部转换为反连接。这会删除子查询，并将其表合并到顶层查询的计划中，提供了更好的成本规划。通过合并半连接和反连接，优化器可以更自由地重新排列执行计划中的表，在某些情况下可以得到更快的计划。

通过在执行EXPLAIN后检查SHOW WARNINGS中的Message列，或在EXPLAIN FORMAT=TREE的输出中，可以看到给定查询执行时进行的反连接转换。

注意

反连接是半连接*table_a* JOIN *table_b* ON *condition*的补集。反连接返回table_a中所有没有与table_b匹配condition的行。
计划尚未准备好（JSON 属性：无）

当优化器尚未完成为在命名连接中执行的语句创建执行计划时，会出现EXPLAIN FOR CONNECTION中的这个值。如果执行计划输出包含多行，则根据优化器在确定完整执行计划的进度而定，任何一行或所有行都可能具有这个Extra值。
Range checked for each record (index map: *N*)（JSON 属性：message）

MySQL 没有找到合适的索引可用，但发现在了解前面表的列值后，可能会使用一些索引。对于前面表中的每一行组合，MySQL 检查是否可以使用range或index_merge访问方法来检索行。这并不是非常快速，但比完全没有索引的连接要快。适用条件如第 10.2.1.2 节，“范围优化”和第 10.2.1.3 节，“索引合并优化”中所述，唯一的例外是前面表的所有列值都已知且被视为常量。

索引从 1 开始编号，与表的SHOW INDEX显示的顺序相同。索引映射值N是一个位掩码值，指示哪些索引是候选索引。例如，值0x19（二进制 11001）表示索引 1、4 和 5 被考虑。
Recursive（JSON 属性：recursive）

这表示该行适用于递归SELECT的部分，即递归公共表达式。参见第 15.2.20 节，“WITH（公共表达式）”。
Rematerialize（JSON 属性：rematerialize）

在表T的EXPLAIN行中显示Rematerialize (X,...)，其中X是任何侧向派生表，当读取T的新行时触发其重新生成。例如：
```
SELECT
  ...
FROM
  t,
  LATERAL (*derived table that refers to t*) AS dt
...
```
派生表的内容在每次顶部查询处理新行时重新生成，以使其保持最新状态。
扫描了*N*个数据库（JSON 属性：message）

这指示服务器在处理INFORMATION_SCHEMA表查询时执行多少目录扫描，如 Section 10.2.3, “Optimizing INFORMATION_SCHEMA Queries”中所述。N的值可以是 0、1 或all。
Select tables optimized away（JSON 属性：message）

优化器确定了以下两点：1）最多只返回一行，2）为了产生这一行，必须读取一组确定性的行。当要读取的行可以在优化阶段读取时（例如，通过读取索引行），在查询执行期间就不需要读取任何表。

当查询隐式分组（包含聚合函数但没有GROUP BY子句）时，第一个条件得到满足。当每个使用的索引执行一次行查找时，第二个条件得到满足。读取的索引数决定了要读取的行数。

考虑以下隐式分组查询：
```
SELECT MIN(c1), MIN(c2) FROM t1;
```
假设通过读取一个索引行可以检索到MIN(c1)，通过从不同索引读取一行可以检索到MIN(c2)。也就是说，对于每个列c1和c2，存在一个索引，其中该列是索引的第一列。在这种情况下，通过读取两个确定性行生成一行返回。

如果要读取的行不是确定性的，则不会出现此Extra值。考虑以下查询：
```
SELECT MIN(c2) FROM t1 WHERE c1 <= 10;
```
假设(c1, c2)是一个覆盖索引。使用此索引，必须扫描所有c1 <= 10的行以找到最小的c2值。相比之下，考虑以下查询：
```
SELECT MIN(c2) FROM t1 WHERE c1 = 10;
```
在这种情况下，具有c1 = 10的第一个索引行包含最小的c2值。只需读取一行即可生成返回的行。

对于维护每个表的确切行数的存储引擎（例如MyISAM，但不包括InnoDB），对于缺少或始终为真的WHERE子句且没有GROUP BY子句的COUNT(*)查询，此Extra值可能出现。（这是存储引擎影响是否可以读取确定数量的行的隐式分组查询的一个实例。）
Skip_open_table，Open_frm_only，Open_full_table（JSON 属性：message）

这些值指示适用于INFORMATION_SCHEMA表查询的文件打开优化。
- Skip_open_table：不需要打开表文件。信息已经从数据字典中获取。
- Open_frm_only：只需读取数据字典以获取表信息。
- Open_full_table：未优化的信息查找。必须从数据字典中读取表信息，并通过读取表文件。
Start temporary，End temporary（JSON 属性：message）

这表明临时表用于半连接去重策略。
unique row not found（JSON 属性：message）

对于诸如SELECT ... FROM *tbl_name*的查询，没有行满足表上的UNIQUE索引或PRIMARY KEY的条件。
Using filesort（JSON 属性：using_filesort）

MySQL 必须进行额外的传递来找出如何按排序顺序检索行。排序是通过根据连接类型遍历所有行并为匹配WHERE子句的所有行存储排序键和指向行的指针来完成的。然后对键进行排序，并按排序顺序检索行。请参阅第 10.2.1.16 节，“ORDER BY 优化”。
Using index（JSON 属性：using_index）

仅使用索引树中的信息从表中检索列信息，而无需进行额外的查找以读取实际行。当查询仅使用单个索引的列时，可以使用此策略。

对于具有用户定义的聚簇索引的InnoDB表，即使Extra列中缺少Using index，该索引也可以使用。如果type是index，并且key是PRIMARY，则是这种情况。

有关使用的任何覆盖索引的信息显示在EXPLAIN FORMAT=TRADITIONAL和EXPLAIN FORMAT=JSON中。从 MySQL 8.0.27 开始，也会显示在EXPLAIN FORMAT=TREE中。
Using index condition（JSON 属性：using_index_condition）

通过访问索引元组并首先测试它们来读取表。通过这种方式，索引信息用于推迟（“推下去”）读取完整的表行，除非有必要。请参阅第 10.2.1.6 节，“索引条件推迟优化”。
Using index for group-by（JSON 属性：using_index_for_group_by）

类似于Using index表访问方法，Using index for group-by表示 MySQL 找到了一个可以用于检索GROUP BY或DISTINCT查询的所有列的索引，而无需额外访问实际表的磁盘。此外，索引以最有效的方式使用，以便对于每个组，只读取了少量索引条目。有关详细信息，请参阅第 10.2.1.17 节，“GROUP BY 优化”。
Using index for skip scan（JSON 属性：using_index_for_skip_scan)

表示使用了 Skip Scan 访问方法。请参阅 Skip Scan 范围访问方法。
Using join buffer (Block Nested Loop)，Using join buffer (Batched Key Access)，Using join buffer (hash join)（JSON 属性：using_join_buffer）

早期连接的表被分段读入连接缓冲区，然后它们的行从缓冲区中使用来与当前表进行连接。(Block Nested Loop)表示使用块嵌套循环算法，(Batched Key Access)表示使用批量键访问算法，(hash join)表示使用哈希连接。也就是说，前一行EXPLAIN输出中的表的键被缓冲，匹配的行从出现Using join buffer的行所代表的表中批量获取。

在 JSON 格式输出中，using_join_buffer的值始终是Block Nested Loop、Batched Key Access或hash join之一。

MySQL 8.0.18 开始提供哈希连接；MySQL 8.0.20 或更高版本的 MySQL 不使用块嵌套循环算法。有关这些优化的更多信息，请参见第 10.2.1.4 节，“哈希连接优化”和块嵌套循环连接算法。

有关批量键访问算法的信息，请参见批量键访问连接。
Using MRR（JSON 属性：message）

表使用多范围读取优化策略进行读取。请参见第 10.2.1.11 节，“多范围读取优化”。
Using sort_union(...)，Using union(...)，Using intersect(...)（JSON 属性：message）

这些指示了特定算法，显示了如何为index_merge连接类型合并索引扫描。请参见第 10.2.1.3 节，“索引合并优化”。
Using temporary（JSON 属性：using_temporary_table）

要解决查询，MySQL 需要创建一个临时表来保存结果。如果查询包含不同列的GROUP BY和ORDER BY子句，通常会发生这种情况。
Using where（JSON 属性：attached_condition）

WHERE子句用于限制要与下一个表匹配或发送到客户端的行。除非您明确打算从表中获取或检查所有行，否则如果Extra值不是Using where且表连接类型为ALL或index，则查询可能存在问题。

Using where在 JSON 格式输出中没有直接对应项；attached_condition属性包含使用的任何WHERE条件。
Using where with pushed condition（JSON 属性：message）

此项仅适用于NDB表。这意味着 NDB Cluster 正在使用条件下推优化来提高非索引列和常量之间直接比较的效率。在这种情况下，条件被“推送”到集群的数据节点，并同时在所有数据节点上进行评估。这消除了在网络上传送不匹配的行的需要，并且可以将这类查询的速度提高 5 到 10 倍，相对于可能但未使用条件下推的情况。有关更多信息，请参见 Section 10.2.1.5, “Engine Condition Pushdown Optimization”。
Zero limit（JSON 属性：message）

查询有一个LIMIT 0子句，无法选择任何行。

解释输出解释

通过将EXPLAIN输出中rows列中的值相乘，您可以很好地了解连接的好坏。这应该大致告诉您 MySQL 必须检查多少行才能执行查询。如果使用max_join_size系统变量限制查询，这个行乘积也用于确定要执行哪些多表SELECT语句以及要中止哪些。请参见 Section 7.1.1, “Configuring the Server”。

以下示例展示了如何根据EXPLAIN提供的信息逐步优化多表连接。

假设你有以下所示的SELECT语句，并计划使用EXPLAIN来检查它：

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
               tt.ProjectReference, tt.EstimatedShipDate,
               tt.ActualShipDate, tt.ClientID,
               tt.ServiceCodes, tt.RepetitiveID,
               tt.CurrentProcess, tt.CurrentDPPerson,
               tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
               et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
          AND tt.ActualPC = et.EMPLOYID
          AND tt.AssignedPC = et_1.EMPLOYID
          AND tt.ClientID = do.CUSTNMBR;

对于此示例，请做出以下假设：

要比较的列已声明如下。

表列数据类型

tt ActualPC CHAR(10)

tt AssignedPC CHAR(10)

tt ClientID CHAR(10)

et EMPLOYID CHAR(15)

do CUSTNMBR CHAR(15)
表具有以下索引。

表索引

tt ActualPC

tt AssignedPC

tt ClientID

et EMPLOYID（主键）

do CUSTNMBR（主键）
tt.ActualPC的值分布不均匀。

表	列	数据类型
`tt`	`ActualPC`	`CHAR(10)`
`tt`	`AssignedPC`	`CHAR(10)`
`tt`	`ClientID`	`CHAR(10)`
`et`	`EMPLOYID`	`CHAR(15)`
`do`	`CUSTNMBR`	`CHAR(15)`

表	索引
`tt`	`ActualPC`
`tt`	`AssignedPC`
`tt`	`ClientID`
`et`	`EMPLOYID`（主键）
`do`	`CUSTNMBR`（主键）

最初，在执行任何优化之前，EXPLAIN语句产生以下信息：

table type possible_keys key  key_len ref  rows  Extra
et    ALL  PRIMARY       NULL NULL    NULL 74
do    ALL  PRIMARY       NULL NULL    NULL 2135
et_1  ALL  PRIMARY       NULL NULL    NULL 74
tt    ALL  AssignedPC,   NULL NULL    NULL 3872
           ClientID,
           ActualPC
      Range checked for each record (index map: 0x23)

因为每个表的 type 都是 ALL，这个输出表明 MySQL 正在生成所有表的笛卡尔积；也就是说，每一行的组合都要被检查。这需要很长时间，因为必须检查每个表中行数的乘积。对于这个案例，这个乘积是 74 × 2135 × 74 × 3872 = 45,268,558,720 行。如果表更大，你可以想象需要多长时间。

这里的一个问题是，如果声明为相同类型和大小，MySQL 可以更有效地使用列上的索引。在这种情况下，如果声明为相同大小，VARCHAR 和 CHAR 被视为相同。tt.ActualPC 声明为 CHAR(10)，et.EMPLOYID 声明为 CHAR(15)，因此存在长度不匹配。

要解决列长度不匹配的差异，使用 ALTER TABLE 将 ActualPC 从 10 个字符扩展到 15 个字符：

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

现在 tt.ActualPC 和 et.EMPLOYID 都是 VARCHAR(15)。再次执行 EXPLAIN 语句会产生这个结果：

table type   possible_keys key     key_len ref         rows    Extra
tt    ALL    AssignedPC,   NULL    NULL    NULL        3872    Using
             ClientID,                                         where
             ActualPC
do    ALL    PRIMARY       NULL    NULL    NULL        2135
      Range checked for each record (index map: 0x1)
et_1  ALL    PRIMARY       NULL    NULL    NULL        74
      Range checked for each record (index map: 0x1)
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC 1

这不是完美的，但要好得多：rows 值的乘积减少了 74 倍。这个版本在几秒钟内执行。

可以进行第二次修改以消除 tt.AssignedPC = et_1.EMPLOYID 和 tt.ClientID = do.CUSTNMBR 比较的列长度不匹配：

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
                      MODIFY ClientID   VARCHAR(15);

经过这种修改，EXPLAIN 产生了如下所示的输出：

table type   possible_keys key      key_len ref           rows Extra
et    ALL    PRIMARY       NULL     NULL    NULL          74
tt    ref    AssignedPC,   ActualPC 15      et.EMPLOYID   52   Using
             ClientID,                                         where
             ActualPC
et_1  eq_ref PRIMARY       PRIMARY  15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY  15      tt.ClientID   1

到这一步，查询已经被优化得几乎尽可能好了。剩下的问题是，默认情况下，MySQL 假设 tt.ActualPC 列中的值是均匀分布的，而对于 tt 表来说并非如此。幸运的是，可以轻松地告诉 MySQL 分析键的分布情况：

mysql> ANALYZE TABLE tt;

在额外的索引信息下，连接是完美的，EXPLAIN 产生了这个结果：

table type   possible_keys key     key_len ref           rows Extra
tt    ALL    AssignedPC    NULL    NULL    NULL          3872 Using
             ClientID,                                        where
             ActualPC
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC   1
et_1  eq_ref PRIMARY       PRIMARY 15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY 15      tt.ClientID   1

EXPLAIN 输出中的 rows 列是 MySQL 连接优化器的一个估计。通过比较 rows 的乘积与查询返回的实际行数，检查这些数字是否接近真实情况。如果数字相差很大，你可能会通过在 SELECT 语句中使用 STRAIGHT_JOIN 并尝试以不同顺序列出表来获得更好的性能。（但是，STRAIGHT_JOIN 可能会阻止索引的使用，因为它禁用了半连接转换。参见 Section 10.2.2.1, “Optimizing IN and EXISTS Subquery Predicates with Semijoin Transformations”.）

在某些情况下，当使用子查询时，可以执行修改数据的语句；有关更多信息，请参见第 15.2.15.8 节，“派生表”时使用EXPLAIN SELECT。

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

posted @ 2024-06-23 16:25 绝不原创的飞龙阅读(8) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

龙哥盟

掠夺·扩张·投机·博弈

MySQL8-中文参考-十七-

MySQL8 中文参考（十七）

10.2.1.17 GROUP BY 优化

松散索引扫描

紧凑索引扫描

10.2.1.18 DISTINCT 优化

10.2.1.19 LIMIT 查询优化

10.2.1.20 函数调用优化

10.2.1.21 窗口函数优化

10.2.1.22 行构造函数表达式优化

10.2.1.23 避免全表扫描

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

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

10.2.2.2 使用物化优化子查询

10.2.2.3 优化 EXISTS 策略的子查询

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

10.2.2.5 派生条件下推优化

10.2.3 优化 INFORMATION_SCHEMA 查询

10.2.4 优化性能模式查询

10.2.5 优化数据更改语句

10.2.5.1 优化 INSERT 语句

10.2.5.2 优化 UPDATE 语句

10.2.5.3 优化 DELETE 语句

10.2.6 优化数据库权限

10.2.7 其他优化提示

10.3 优化和索引

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-树和哈希索引的比较

B-树索引特性

哈希索引特性

10.3.10 索引扩展的使用

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

10.3.12 不可见索引

10.3.13 降序索引

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

10.4 优化数据库结构

10.4.1 优化数据大小

表列

行格式

索引

连接

规范化

10.4.2 优化 MySQL 数据类型

10.4.2.1 优化数字数据

10.4.2.2 优化字符和字符串类型

10.4.2.3 优化 BLOB 类型

10.4.3 优化多表

10.4.3.1 MySQL 如何打开和关闭表

10.4.3.2 在同一数据库中创建许多表的缺点

10.4.5 数据库和表数量的限制

10.4.6 表大小限制

10.4.7 表列数和行大小的限制

列数限制

行大小限制

行大小限制示例

10.5 优化 InnoDB 表

10.5.1 优化 InnoDB 表的存储布局

10.5.2 优化 InnoDB 事务管理

10.5.3 优化 InnoDB 只读事务

10.5.4 优化 InnoDB 重做日志

10.5.5 InnoDB 表的批量数据加载

10.5.6 优化 InnoDB 查询

10.5.7 优化 InnoDB DDL 操作

10.5.8 优化 InnoDB 磁盘 I/O

10.5.9 优化 InnoDB 配置变量

10.5.10 优化具有多个表的 InnoDB 系统

10.6 MyISAM 表优化