MySQL8-中文参考-三十八-

布客飞龙
34 阅读1小时+

MySQL8 中文参考（三十八）

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

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_sizekey_buffer_size
myisam_sort_buffer_sizemyisam_sort_buffer_size
read_buffer_sizeread_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 名称含义
idselect_idSELECT标识符
select_typeNoneSELECT 类型
tabletable_name输出行的表
partitionspartitions匹配的分区
typeaccess_type连接类型
possible_keyspossible_keys可选择的索引
keykey实际选择的索引
key_lenkey_length所选键的长度
refref与索引进行比较的列
rowsrows预计要检查的行数
filteredfiltered表条件过滤的行的百分比
ExtraNone附加信息
列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 名称含义
    SIMPLENone简单的SELECT（不使用UNION或子查询）
    PRIMARYNone最外层的SELECT
    UNIONNoneUNION中的第二个或更后的SELECT语句
    DEPENDENT UNIONdependent (true)UNION中的第二个或更后的SELECT语句，依赖于外部查询
    UNION RESULTunion_resultUNION的结果。
    SUBQUERYNone子查询中的第一个SELECT
    DEPENDENT SUBQUERYdependent（true）子查询中的第一个SELECT，依赖于外部查询
    DERIVED无派生表
    DEPENDENT DERIVEDdependent（true）依赖于另一张表的派生表
    MATERIALIZEDmaterialized_from_subquery物化子查询
    UNCACHEABLE SUBQUERYcacheable（false）不能缓存结果且必须对外部查询的每一行重新评估的子查询
    UNCACHEABLE UNIONcacheable（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;

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

  • 要比较的列已声明如下。

    表列数据类型
    ttActualPCCHAR(10)
    ttAssignedPCCHAR(10)
    ttClientIDCHAR(10)
    etEMPLOYIDCHAR(15)
    doCUSTNMBRCHAR(15)

  • 表具有以下索引。

    表索引
    ttActualPC
    ttAssignedPC
    ttClientID
    etEMPLOYID（主键）
    doCUSTNMBR（主键）

  • tt.ActualPC的值分布不均匀。

最初，在执行任何优化之前，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。

avatar
文章
阅读
粉丝