MySQL8 查询性能调优教程（九）

原文：MySQL 8 Query Performance Tuning

协议：CC BY-NC-SA 4.0

二十四、更改查询计划

性能不佳的查询不能按预期工作有几个可能的原因。这包括从查询明显错误到低级原因，如非最佳查询计划或资源争用。本章将讨论一些常见的情况和解决方案。

本章首先介绍了本章中大多数示例使用的测试数据，并讨论了过度全表扫描的症状。然后讲述了查询中的错误如何会导致严重的性能问题，以及为什么即使存在索引也不总是可以使用。本章的中间部分介绍了通过改进索引使用或重写复杂查询来改进查询的各种方法。最后一部分讨论了如何使用SKIP LOCKED子句实现队列系统，以及如何处理带有多个OR条件或带有多个值的IN ()子句的查询。

测试数据

本章主要使用专门为本章中的示例创建的测试数据。如果您想亲自尝试这些示例，本书的 GitHub 资源库中的文件chapter_24.sql包含了必要的表定义和数据。该脚本将删除chapter_24模式，并用表创建它。

您可以使用 MySQL Shell 中的\source命令或mysql命令行客户端中的SOURCE命令来执行该脚本。例如:

mysql shell> \source chapter_24.sql
...
mysql shell> SHOW TABLES FROM chapter_24;
+----------------------+
| Tables_in_chapter_24 |
+----------------------+
| address              |
| city                 |
| country              |
| jobqueue             |
| language             |
| mytable              |
| payment              |
| person               |
+----------------------+
8 rows in set (0.0033 sec)

该脚本要求在获取chapter_24.sql脚本之前安装world示例数据库。

Note

由于索引统计数据是使用对索引的随机深入来确定的，因此在每次分析之后，它们的值不会相同。因此，在尝试本章中的例子时，不要期望得到相同的输出。

过度全表扫描的症状

最严重的性能问题的原因之一是全表扫描，特别是当涉及到连接并且全表扫描不在查询块的第一个表上时。它会给 MySQL 带来太多的工作，还会影响其他连接。当 MySQL 无法使用索引进行查询时，就会发生全表扫描，这是因为没有过滤条件或者没有针对当前条件的索引。全表扫描的一个副作用是，大量数据被拉入缓冲池，可能永远不会返回给应用。这可能会使磁盘 I/O 量急剧增加，从而导致进一步的性能问题。

当查询执行过多的表扫描时，您需要注意的症状是 CPU 使用率增加、被访问的行数增加、索引使用率低、磁盘 I/O 可能增加以及 InnoDB 缓冲池效率降低。

检测过度全表扫描的最佳方法是使用您的监控。直接的方法是寻找在性能模式中被标记为使用全表扫描的查询，并比较检查的行数与返回的或受影响的行数之比，如第 19 章所述。您还可以查看 timeseries 图来发现被访问的行太多或 CPU 使用太多的模式。图 24-1 显示了在一个 MySQL 实例上进行全表扫描期间的监控图示例。(employees数据库在您想要模拟这种情况时非常有用，因为它有足够大的表来允许一些相对较大的扫描。)

图 24-1

当存在带有全表扫描的查询时监控图形

请注意在图表的左侧，通过完全扫描读取的行的行访问率和 CPU 使用率是如何增加的。另一方面，与被访问的行数相比，返回的行数变化很小(百分比)。特别是第二个图表显示了通过索引读取行的速率与完全扫描的比较，以及读取的行和返回的行之间的比率，这表明了一个问题。

Tip

在 MySQL 8.0.18 和更高版本中，与连接相关的全表扫描并不是一个大问题，因为散列连接可以用于等价连接。也就是说，散列连接仍然会将比所需更多的数据拉入缓冲池。

最大的问题是什么时候 CPU 使用率过高，访问的行数过多，不幸的是，答案是“视情况而定”如果您考虑 CPU 的使用情况，那么它真正能告诉您的是正在进行的工作，对于正在访问的行数和访问速率，这些指标只是告诉您应用正在请求数据。问题是当做了太多的工作，并且为应用需要答案的问题访问了太多的行时。在某些情况下，优化查询可能会增加这些指标中的一些，而不是减少它们——这只是因为带有优化查询的 MySQL 能够做更多的工作。

这是基线如此重要的一个例子。考虑度量标准的变化通常比查看它们的快照更有用。类似地，综合考虑这些指标——比如比较返回的行数和访问的行数——比单独考虑它们会得到更多。

接下来的两节讨论了访问过多行的查询示例以及如何改进它们。

错误的查询

查询性能最差的一个常见原因是查询写错了。这似乎是一个不太可能的原因，但实际上它比你想象的更容易发生。通常，问题是缺少联接或筛选条件，或者引用了错误的表。如果您使用一个框架，例如，使用对象关系映射(ORM)，框架中的一个错误也可能是罪魁祸首。

在极端情况下，缺少过滤条件的查询会使应用超时查询(但不会终止查询)并重试，因此 MySQL 会不断执行越来越多性能非常差的查询。这反过来会使 MySQL 失去连接。

另一种可能性是，第一个提交的查询开始将数据从磁盘拉入缓冲池。然后，每个后续查询会越来越快，因为它们可以从缓冲池中读取一些行，然后当它们到达尚未从磁盘中读取的行时会变慢。最后，查询的所有副本将在很短的时间内完成，并开始向应用返回大量数据，这会使网络饱和。一个饱和的网络可能会因为握手错误而导致连接尝试失败(在performance_schema.host_cache中的COUNT_HANDSHAKE_ERRORS列)，并且产生连接的主机最终可能会被阻塞。

这可能看起来很极端，在大多数情况下，它不会变得那么糟糕。然而，由于生成查询的框架中的一个 bug，这本书的作者确实经历了这种情况。鉴于 MySQL 实例现在通常位于云中的虚拟机中，可能具有有限的可用资源，如 CPU 和网络，也更有可能是一个糟糕的查询可能会耗尽资源。

作为缺少连接条件的查询和查询计划的一个例子，可以考虑列出连接city和country表的 24-1 。

mysql> EXPLAIN
        SELECT ci.CountryCode, ci.ID, ci.Name,
               ci.District, co.Name AS Country,
               ci.Population
          FROM world.city ci
               INNER JOIN world.country co\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: co
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 239
     filtered: 100
        Extra: NULL
*************************** 2\. row ***************************
           id: 1
  select_type: SIMPLE
        table: ci
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4188
     filtered: 100
        Extra: Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.0008 sec)

mysql> EXPLAIN ANALYZE
        SELECT ci.CountryCode, ci.ID, ci.Name,
               ci.District, co.Name AS Country,
               ci.Population
          FROM world.city ci
               INNER JOIN world.country co\G ************ 1\. row *********
EXPLAIN:
-> Inner hash join  (cost=100125.15 rows=1000932) (actual time=0.194..80.427 rows=974881 loops=1)
    -> Table scan on ci  (cost=1.78 rows=4188) (actual time=0.025..2.621 rows=4079 loops=1)
    -> Hash
        -> Table scan on co  (cost=25.40 rows=239) (actual time=0.041..0.089 rows=239 loops=1)

1 row in set (0.4094 sec)

Listing 24-1Query that is missing a join condition

注意这两个表的访问类型是如何设置为ALL的，以及连接是如何在块嵌套循环中使用连接缓冲区的。通常具有类似症状的原因是正确的查询，但是该查询不能使用索引。EXPLAIN ANALYZE输出显示在 8.0.18 版本中使用了散列连接。它还显示总共返回了差不多一百万行！查询的直观解释图如图 24-2 所示。

图 24-2

缺少联接条件的查询的可视化说明

请注意这两个(红色)全表扫描是如何脱颖而出的，以及查询成本估计如何超过 100，000。

多个全表扫描的组合、非常高的估计返回行数和非常高的成本估计是您需要寻找的警示信号。

产生类似症状的查询性能差的一个原因是 MySQL 不能为过滤和连接条件使用索引。

没有使用索引

当查询需要在表中查找行时，它基本上可以通过两种方式完成:在全表扫描中直接访问行，或者通过索引。在有高选择性过滤器的情况下，通过索引访问行通常比通过表扫描要快得多。

显然，如果过滤器适用的列上没有索引，MySQL 别无选择，只能使用表扫描。您可能会发现，即使有索引，也无法使用。出现这种情况的三个常见原因是:列不是多列索引中的第一列，数据类型与比较不匹配，以及对带有索引的列使用了函数。本节将讨论这些原因。

Tip

与全表扫描相比，优化器认为索引的选择性不足以使其值得使用，这种情况也可能发生。这种情况在“改进索引使用”一节中处理，同时还有 MySQL 使用错误索引的例子。

不是索引的左前缀

要使用索引，必须使用索引的左前缀。例如，如果一个索引包含三列作为(a, b, c)，那么列b上的条件只能在列a上也有相等条件时使用过滤器。

可以使用索引的条件示例如下

WHERE a = 10 AND b = 20 AND c = 30
WHERE a = 10 AND b = 20 AND c > 10
WHERE a = 10 AND b = 20
WHERE a = 10 AND b > 20
WHERE a = 10

不能有效使用索引的一个例子是WHERE b = 20。在 MySQL 8.0.13 和更高版本中，如果a是一个NOT NULL列，MySQL 可以使用跳过扫描范围优化来使用索引。如果a允许NULL值，则该指标不能使用。条件WHERE c = 20在任何情况下都不能使用索引。

类似地，对于条件WHERE a > 10 AND b = 20，索引将仅用于对a列进行过滤。当查询仅使用索引中列的子集时，索引中列的顺序与应用的筛选器相对应是很重要的。如果其中一列有范围条件，请确保该列是索引中使用的最后一列。例如，考虑清单 24-2 中的表和查询。

mysql> SHOW CREATE TABLE chapter_24.mytable\G
*************************** 1\. row ***************************
       Table: mytable
Create Table: CREATE TABLE `mytable` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `abc` (`a`,`b`,`c`)
) ENGINE=InnoDB AUTO_INCREMENT=16385 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.0004 sec)

mysql> EXPLAIN
        SELECT *
          FROM chapter_24.mytable
         WHERE a > 10 AND b = 20\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: mytable
   partitions: NULL
         type: range
possible_keys: abc
          key: abc
      key_len: 4
          ref: NULL
         rows: 8326
     filtered: 10
        Extra: Using where; Using index
1 row in set, 1 warning (0.0007 sec)

Listing 24-2Query that cannot use the index effectively due to column order

注意在EXPLAIN输出中,key_len只有 4 个字节，然而如果索引同时用于a和b列，它应该是 9 个字节。输出还显示，估计只有 10%被检查的行将被包括在内。图 24-3 直观解释了同一个例子。

图 24-3

索引中非最佳列顺序的直观解释

请注意，使用的关键零件(靠近盒子底部，有附加细节)只列出了列a。但是，如果您更改索引中列的顺序，使列b在列a之前建立索引，那么该索引可以用于这两列上的条件。清单 24-3 展示了添加新索引(b, a, c)后查询计划的变化。

mysql> ALTER TABLE chapter_24.mytable
         ADD INDEX bac (b, a, c);
Query OK, 0 rows affected (1.4098 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN
       SELECT *
         FROM chapter_24.mytable
        WHERE a > 10 AND b = 20\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: mytable
   partitions: NULL
         type: range
possible_keys: abc,bac
          key: bac
      key_len: 9
          ref: NULL
         rows: 160
     filtered: 100
        Extra: Using where; Using index
1 row in set, 1 warning (0.0006 sec)

Listing 24-3Query plan with the index in optimal order

注意key_len列现在如何返回 9 个字节，并且filtered列显示 100%被检查的行将包含在表中。同样的情况也反映在视觉解释中，如图 24-4 所示。

图 24-4

当存在最优排序的索引时，进行可视化解释

在图中，您可以看到将要检查的行数从 8000 多行减少到了 160 行，并且使用的关键部分现在包括了b和a列。估计的查询开销也从 1683.84 降低到 33.31。

数据类型不匹配

您需要注意的另一件事是，条件的两端使用相同的数据类型，并且对字符串使用相同的排序规则。如果不是这样，MySQL 可能无法使用索引。

当查询由于数据类型或排序规则不匹配而无法以最佳方式工作时，一开始可能很难意识到问题出在哪里。查询是正确的，但是 MySQL 拒绝使用您期望的索引。除了查询计划与您预期的不同之外，查询结果也可能是错误的。这可能是由于铸造造成的，例如:

mysql> SELECT ('a130' = 0), ('130a131' = 130);
+--------------+-------------------+
| ('a130' = 0) | ('130a131' = 130) |
+--------------+-------------------+
|            1 |                 1 |
+--------------+-------------------+
1 row in set, 2 warnings (0.0004 sec)

请注意字符串“a130”是如何被视为等于整数 0 的。发生这种情况是因为字符串以非数字字符开头，因此被转换为值 0。同样，字符串“130a131”被视为等于整数 130，因为该字符串的前导数字部分被转换为整数 130。当对一个WHERE子句或一个连接条件使用强制转换时，也会出现同样的意外匹配。在这种情况下，检查查询的警告有时有助于发现问题。

如果您考虑本章测试模式中的country和world表(表定义将在讨论示例时显示)，您可以看到一个不使用索引的连接示例，此时这两个表使用CountryId列连接。清单 24-4 展示了一个查询及其查询计划的例子。

mysql> EXPLAIN
        SELECT ci.ID, ci.Name, ci.District,
               co.Name AS Country, ci.Population
          FROM chapter_24.city ci
               INNER JOIN chapter_24.country co
                     USING (CountryId)
         WHERE co.CountryCode = 'AUS'\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: co
   partitions: NULL
         type: const
possible_keys: PRIMARY,CountryCode
          key: CountryCode
      key_len: 12
          ref: const
         rows: 1
     filtered: 100
        Extra: NULL
*************************** 2\. row ***************************
           id: 1
  select_type: SIMPLE
        table: ci
   partitions: NULL
         type: ALL
possible_keys: CountryId
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4079
     filtered: 10
        Extra: Using where

2 rows in set, 3 warnings (0.0009 sec)
Warning (code 1739): Cannot use ref access on index 'CountryId' due to type or collation conversion on field 'CountryId'
Warning (code 1739): Cannot use range access on index 'CountryId' due to type or collation conversion on field 'CountryId'
Note (code 1003): /* select#1 */ select `chapter_24`.`ci`.`ID` AS `ID`,`chapter_24`.`ci`.`Name` AS `Name`,`chapter_24`.`ci`.`District` AS `District`,'Australia' AS `Country`,`chapter_24`.`ci`.`Population` AS `Population` from `chapter_24`.`city` `ci` join `chapter_24`.`country` `co` where ((`chapter_24`.`ci`.`CountryId` = '15'))

Listing 24-4Query not using an index due to mismatching data types

注意，ci ( city)表的访问类型是ALL。这个查询既不会使用块嵌套循环，也不会使用散列连接，因为co ( country)表是一个常量。这里包含了警告(如果您不使用启用了警告的 MySQL Shell，您将需要执行SHOW WARNINGS来获取警告)，因为它们提供了一个有价值的提示，说明为什么不能使用索引，例如:*由于字段‘country id’*上的类型或排序规则转换，不能对索引‘country id’使用 ref 访问。因此，有一个索引是候选索引，但由于数据类型或排序规则发生了变化，因此无法使用该索引。图 24-5 显示了使用可视化解释的相同查询计划。

图 24-5

直观解释数据类型不匹配的地方

这是您需要基于文本的输出来获得所有细节的情况之一，因为 Visual Explain 不包括警告。当您看到这样的警告时，请返回并检查表定义。这些如清单 24-5 所示。

CREATE TABLE `chapter_24`.`city` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(35) NOT NULL DEFAULT ",
  `CountryCode` char(3) NOT NULL DEFAULT ",
  `CountryId` char(3) NOT NULL,
  `District` varchar(20) NOT NULL DEFAULT ",
  `Population` int unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  KEY `CountryId` (`CountryId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `chapter_24`.`country` (
  `CountryId` int unsigned NOT NULL AUTO_INCREMENT,
  `CountryCode` char(3) NOT NULL,
  `Name` varchar(52) NOT NULL,
  `Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
  `Region` varchar(26) DEFAULT NULL,
  PRIMARY KEY (`CountryId`),
  UNIQUE INDEX `CountryCode` (`CountryCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Listing 24-5The table definitions for the city and country tables

这里很明显，city表的CountryId列是一个char(3)列，但是国家表的CountryId是一个整数。这就是为什么当city表是连接中的第二个表时，不能使用city.CountryId列的索引。

Note

如果连接以另一种方式进行，第一个表是city表，第二个表是country表，那么city.CountryId仍然被转换为整数，而country.CountryId没有改变，因此可以使用country.CountryId上的索引。

还要注意，这两个表的排序规则是不同的。city表使用utf8mb4_general_ci排序规则(MySQL 5.7 和更早版本中的默认utf8mb4排序规则)，而country表使用utf8mb4_0900_ai_ci(MySQL 8 中的默认utf8mb4排序规则)。不同的字符集或排序规则甚至会阻止查询一起执行:

SELECT ci.ID, ci.Name, ci.District,
       co.Name AS Country, ci.Population
  FROM chapter_24.city ci
       INNER JOIN chapter_24.country co
             USING (CountryCode)
 WHERE co.CountryCode = 'AUS';
ERROR: 1267: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation '='

如果您在 MySQL 8 中创建一个表，并在查询中与在早期 MySQL 版本中创建的表一起使用，这是需要注意的。在这种情况下，您需要确保所有的表都使用相同的排序规则。

数据类型不匹配的问题是在过滤器中使用函数的特例，就像 MySQL 进行隐式转换一样。一般来说，在过滤器中使用函数可以防止索引的使用。

功能依赖性

不使用索引的最后一个常见原因是对列应用了函数，例如:WHERE MONTH(birth_date) = 7。在这种情况下，您需要重写条件以避免该函数，或者您需要添加一个函数索引。

如果可能，处理函数的使用阻止了索引的使用的情况的最好方法是重写查询以避免使用函数。虽然也可以使用函数索引，但除非它有助于创建覆盖索引，否则索引会增加开销，这可以通过重写来避免。考虑一个查询，该查询想要查找出生于 1970 年的人的详细信息，如使用chapter_24.person表的清单 24-6 中的示例所示。

mysql> SHOW CREATE TABLE chapter_24.person\G
*************************** 1\. row ***************************
       Table: person
Create Table: CREATE TABLE `person` (
  `PersonId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `FirstName` varchar(50) DEFAULT NULL,
  `Surname` varchar(50) DEFAULT NULL,
  `BirthDate` date NOT NULL,
  `AddressId` int(10) unsigned DEFAULT NULL,
  `LanguageId` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`PersonId`),
  KEY `BirthDate` (`BirthDate`),
  KEY `AddressId` (`AddressId`),
  KEY `LanguageId` (`LanguageId`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.0012 sec)

mysql> EXPLAIN
         SELECT *
           FROM chapter_24.person
          WHERE YEAR(BirthDate) = 1970\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: person
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
     filtered: 100
        Extra: Using where
1 row in set, 1 warning (0.0006 sec)

Listing 24-6The person table and finding persons born in 1970

这个查询使用YEAR()函数来确定这个人出生的年份。另一种方法是寻找 1970 年 1 月 1 日至 1971 年 12 月 31 日(包括这两天)之间出生的所有人，这相当于同一件事。清单 24-7 显示在这种情况下使用了birthdate列上的索引。

mysql> EXPLAIN
        SELECT *
          FROM chapter_24.person
         WHERE BirthDate BETWEEN '1970-01-01'
                             AND '1970-12-31'\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: person
   partitions: NULL
         type: range
possible_keys: BirthDate
          key: BirthDate
      key_len: 3
          ref: NULL
         rows: 6
     filtered: 100
        Extra: Using index condition
1 row in set, 1 warning (0.0009 sec)

Listing 24-7Rewriting the YEAR() function to a date range condition

这种重写将查询从使用检查 1000 行的表扫描减少到只检查 6 行的索引范围扫描。当函数用于有效提取一系列值的日期时，类似的重写通常是可能的。

Note

使用LIKE操作符重写日期或日期时间范围条件是很有诱惑力的，例如:WHERE birthdate LIKE '1970-%'。这将不允许 MySQL 使用查询，并且不鼓励这样做。请改用合适的范围。

以刚才演示的方式重写使用函数的条件并不总是可能的。可能是条件没有映射到单个范围，或者查询是由框架或第三方应用生成的，因此您不能更改它。在这种情况下，您可以添加一个函数索引。

Note

MySQL 8.0.13 及更高版本支持函数索引。如果您使用早期版本，建议您进行升级。如果这是不可能的，或者您还需要函数返回的值，您可以通过添加带有函数表达式的虚拟列并在虚拟列上创建索引来模拟函数索引。

例如，考虑一个查询，该查询查找在给定月份生日的所有人，例如，因为您想向他们发送生日祝福。原则上，这可以通过使用范围来实现，但是每年需要一个范围，这既不实际也不太有效。相反，您可以使用MONTH()函数提取月份的数值(一月是 1，十二月是 12)。清单 24-8 展示了如何添加一个函数索引，该索引可以与一个查询一起使用，该查询查找chapter_24.person表中所有生日在当月的人。

mysql> ALTER TABLE chapter_24.person
         ADD INDEX ((MONTH(BirthDate)));
Query OK, 0 rows affected (0.4845 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN
         SELECT *
           FROM chapter_24.person
          WHERE MONTH(BirthDate) = MONTH(NOW())\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: person
   partitions: NULL
         type: ref
possible_keys: functional_index
          key: functional_index
      key_len: 5
          ref: const
         rows: 88
     filtered: 100
        Extra: NULL
1 row in set, 1 warning (0.0006 sec)

Listing 24-8Using a functional index

添加了MONTH(BirthDate)上的函数索引后，查询计划显示使用的索引是functional_index。

关于如何为当前没有使用索引的查询添加索引支持的讨论到此结束。还有几个与使用索引相关的重写。这些将在下一节讨论。

改进索引的使用

前一节考虑了没有索引用于 join 或WHERE子句的查询。在某些情况下，使用了一个索引，但是您可以改进该索引，或者另一个索引提供了更好的性能，或者由于过滤器的复杂性而无法有效地使用索引。本节将介绍一些使用索引改进查询的例子。

添加覆盖索引

在某些情况下，当您查询一个表时，过滤是由索引执行的，但是您已经请求了几个其他列，所以 MySQL 需要检索整行。在这种情况下，将这些额外的列添加到索引中会更有效，这样索引就包含了查询所需的所有列。

考虑一下chapter_24样本数据库中的city表:

CREATE TABLE `city` (
  `ID` int unsigned NOT NULL AUTO_INCREMENT,
  `Name` varchar(35) NOT NULL DEFAULT ",
  `CountryCode` char(3) NOT NULL DEFAULT ",
  `CountryId` char(3) NOT NULL,
  `District` varchar(20) NOT NULL DEFAULT ",
  `Population` int unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  KEY `CountryId` (`CountryId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

如果您想查找所有带有CountryCode = 'USA'的城市的名称和地区，那么您可以使用CountryCode索引来查找这些行。这是高效的，如清单 24-9 所示。

mysql> EXPLAIN
        SELECT Name, District
          FROM chapter_24.city
         WHERE CountryCode = 'USA'\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: ref
possible_keys: CountryCode
          key: CountryCode
      key_len: 12
          ref: const
         rows: 274
     filtered: 100
        Extra: NULL
1 row in set, 1 warning (0.0376 sec)

Listing 24-9Querying cities by a non-covering index

请注意，索引使用了 12 个字节(3 个字符，每个字符最多 4 个字节宽)，并且Extra列不包括Using index。如果创建一个新索引，将CountryCode作为第一列，将District和Name作为剩余列，那么索引中就有了查询所需的所有列。选择District和Name的顺序，因为它们最有可能与过滤器中的CountryCode以及ORDER BY和GROUP BY子句一起使用。如果同样可能在过滤器中使用列，则在索引中选择Name在District之前，因为城市名称比地区名称更具选择性。清单 24-10 展示了一个这样的例子以及新的查询计划。

mysql> ALTER TABLE chapter_24.city
       ALTER INDEX CountryCode INVISIBLE,
         ADD INDEX Country_District_Name
                  (CountryCode, District, Name);
Query OK, 0 rows affected (1.6630 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN
        SELECT Name, District
          FROM chapter_24.city
         WHERE CountryCode = 'USA'\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: city
   partitions: NULL
         type: ref
possible_keys: Country_District_Name
          key: Country_District_Name
      key_len: 12
          ref: const
         rows: 274
     filtered: 100
        Extra: Using index
1 row in set, 1 warning (0.0006 sec)

Listing 24-10Querying cities by a covering index

当添加新索引时，刚好覆盖CountryCode列的旧索引是不可见的。这样做是因为新索引也可以用于旧索引的所有用途，所以通常没有理由保留两个索引。(假设CountryCode列上的索引比新索引小，一些查询可能会受益于旧索引。通过使它不可见，您可以在删除它之前验证它是不需要的。)

密钥长度仍然返回为 12 个字节，因为这是用于过滤的。然而,Extra列现在包含了Using index,表明正在使用覆盖索引。

错误的索引

当 MySQL 可以在几个索引之间进行选择时，优化器必须根据两个查询计划的估计成本来决定使用哪个。由于索引统计和成本估计不准确，MySQL 可能会选择错误的索引。特殊情况是优化器选择不使用索引，即使可以使用它，或者优化器选择使用索引，这样做可以更快地进行表扫描。无论哪种方式，您都需要使用索引提示。

Tip

正如第 17 章所讨论的，索引提示也可以用来影响一个索引是用于排序还是分组。有必要使用索引提示的一个例子是，当查询选择使用索引进行排序而不是过滤时，这会导致性能下降，反之亦然。可能发生相反情况的一种情况是，当您有一个LIMIT子句并且使用索引进行排序时，可能会允许查询提前停止查询。

当您怀疑使用了错误的索引时，您需要查看EXPLAIN输出的possible_keys列，以确定哪些索引是候选索引。清单 24-11 显示了一个查找关于在 2020 年年满 20 岁并说英语的日本人的信息的例子。(假设你想给他们寄一张生日卡。)树格式的EXPLAIN输出的一部分已经被省略号代替，以通过将大部分行保持在页面宽度内来提高可读性。

mysql> SHOW CREATE TABLE chapter_24.person\G
*************************** 1\. row ***************************
       Table: person
Create Table: CREATE TABLE `person` (
  `PersonId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `FirstName` varchar(50) DEFAULT NULL,
  `Surname` varchar(50) DEFAULT NULL,
  `BirthDate` date NOT NULL,
  `AddressId` int(10) unsigned DEFAULT NULL,
  `LanguageId` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`PersonId`),
  KEY `BirthDate` (`BirthDate`),
  KEY `AddressId` (`AddressId`),
  KEY `LanguageId` (`LanguageId`),
  KEY `functional_index` ((month(`BirthDate`)))
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.0007 sec)

mysql> SHOW CREATE TABLE chapter_24.address\G

*************************** 1\. row ***************************
       Table: address
Create Table: CREATE TABLE `address` (
  `AddressId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `City` varchar(35) NOT NULL,
  `District` varchar(20) NOT NULL,
  `CountryCode` char(3) NOT NULL,
  PRIMARY KEY (`AddressId`),
  KEY `CountryCode` (`CountryCode`,`District`,`City`)
) ENGINE=InnoDB AUTO_INCREMENT=4096 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.0007 sec)

mysql> SHOW CREATE TABLE chapter_24.language\G
*************************** 1\. row ***************************
       Table: language
Create Table: CREATE TABLE `language` (
  `LanguageId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Language` varchar(35) NOT NULL,
  PRIMARY KEY (`LanguageId`),
  KEY `Language` (`Language`)
) ENGINE=InnoDB AUTO_INCREMENT=512 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.0005 sec)

mysql> UPDATE mysql.innodb_index_stats
          SET stat_value = 1000
        WHERE database_name = 'chapter_24'
              AND table_name = 'person'
              AND index_name = 'LanguageId'
              AND stat_name = 'n_diff_pfx01';
Query OK, 1 row affected (0.0920 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> FLUSH TABLE chapter_24.person;
Query OK, 0 rows affected (0.0686 sec)

mysql> EXPLAIN
        SELECT PersonId, FirstName,
               Surname, BirthDate
          FROM chapter_24.person
               INNER JOIN chapter_24.address

                    USING (AddressId)
               INNER JOIN chapter_24.language
                    USING (LanguageId)
         WHERE BirthDate BETWEEN '2000-01-01'
                             AND '2000-12-31'
               AND CountryCode = 'JPN'
               AND Language = 'English'\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: language
   partitions: NULL
         type: ref
possible_keys: PRIMARY,Language
          key: Language
      key_len: 142
          ref: const
         rows: 1
     filtered: 100
        Extra: Using index
*************************** 2\. row ***************************
           id: 1
  select_type: SIMPLE
        table: person
   partitions: NULL
         type: ref
possible_keys: BirthDate,AddressId,LanguageId
          key: LanguageId
      key_len: 5
          ref: chapter_24.language.LanguageId
         rows: 1
     filtered: 5
        Extra: Using where
*************************** 3\. row ***************************
           id: 1
  select_type: SIMPLE
        table: address
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,CountryCode
          key: PRIMARY
      key_len: 4
          ref: chapter_24.person.AddressId
         rows: 1
     filtered: 6.079921722412109
        Extra: Using where
3 rows in set, 1 warning (0.0008 sec)

mysql> EXPLAIN FORMAT=TREE
        SELECT PersonId, FirstName,
               Surname, BirthDate
          FROM chapter_24.person
               INNER JOIN chapter_24.address

                    USING (AddressId)
               INNER JOIN chapter_24.language
                    USING (LanguageId)
         WHERE BirthDate BETWEEN '2000-01-01'
                             AND '2000-12-31'
               AND CountryCode = 'JPN'
               AND Language = 'English'\G
*************************** 1\. row ***************************
EXPLAIN:
-> Nested loop inner join  (cost=0.72 rows=0)
    -> Nested loop inner join  (cost=0.70 rows=0)
        -> Index lookup on language using Language...
        -> Filter: ((person.BirthDate between '2000-01-01' and '2000-12-31') and (person.AddressId is not null))...
            -> Index lookup on person using LanguageId...
    -> Filter: (address.CountryCode = 'JPN')  (cost=0.37 rows=0)
        -> Single-row index lookup on address using PRIMARY...

1 row in set (0.0006 sec)

Listing 24-11Finding information about the countries where English is spoken

本例中的关键表是与language和address表连接的person表。UPDATE和FLUSH语句用于通过更新mysql.innodb_index_stats表并刷新该表以使新的索引统计生效，来模拟索引统计已经过时。

查询可以使用BirthDate、AddressId或LanguageId索引。当优化器向存储引擎请求每个条件的行数时，三个WHERE子句(每个表上一个)的有效性被非常精确地确定。优化器的困难在于根据连接条件的有效性以及每个连接使用哪个索引来确定最佳连接顺序。根据EXPLAIN的输出，优化器选择从language表开始，使用LanguageId索引连接到person表，最后连接到address表。

如果您怀疑查询使用了错误的索引(在这种情况下，使用LanguageId连接到person表并不是最佳选择，因为索引统计数据是“错误的”)，首先要做的是更新索引统计数据。其结果如清单 24-12 所示。

mysql> ANALYZE TABLE
               chapter_24.person,
               chapter_24.address,
               chapter_24.language;
+---------------------+---------+----------+----------+
| Table               | Op      | Msg_type | Msg_text |
+---------------------+---------+----------+----------+
| chapter_24.person   | analyze | status   | OK       |
| chapter_24.address  | analyze | status   | OK       |
| chapter_24.language | analyze | status   | OK       |
+---------------------+---------+----------+----------+
3 rows in set (0.2634 sec)

mysql> EXPLAIN
        SELECT PersonId, FirstName,
               Surname, BirthDate
          FROM chapter_24.person
               INNER JOIN chapter_24.address
                    USING (AddressId)
               INNER JOIN chapter_24.language
                    USING (LanguageId)
         WHERE BirthDate BETWEEN '2000-01-01'
                             AND '2000-12-31'
               AND CountryCode = 'JPN'
               AND Language = 'English'\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: language
   partitions: NULL
         type: ref
possible_keys: PRIMARY,Language
          key: Language
      key_len: 142
          ref: const
         rows: 1
     filtered: 100
        Extra: Using index
*************************** 2\. row ***************************
           id: 1
  select_type: SIMPLE
        table: person
   partitions: NULL
         type: range
possible_keys: BirthDate,AddressId,LanguageId
          key: BirthDate
      key_len: 3
          ref: NULL
         rows: 8
     filtered: 10
        Extra: Using index condition; Using where; Using join buffer (Block Nested Loop)
*************************** 3\. row ***************************
           id: 1
  select_type: SIMPLE
        table: address
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,CountryCode
          key: PRIMARY
      key_len: 4
          ref: chapter_24.person.AddressId
         rows: 1
     filtered: 6.079921722412109
        Extra: Using where
3 rows in set, 1 warning (0.0031 sec)

mysql> EXPLAIN FORMAT=TREE
        SELECT PersonId, FirstName,
               Surname, BirthDate
          FROM chapter_24.person
               INNER JOIN chapter_24.address
                    USING (AddressId)
               INNER JOIN chapter_24.language
                    USING (LanguageId)
         WHERE BirthDate BETWEEN '2000-01-01'
                             AND '2000-12-31'
               AND CountryCode = 'JPN'
               AND Language = 'English'\G
*************************** 1\. row ***************************
EXPLAIN:
-> Nested loop inner join  (cost=7.01 rows=0)
    -> Inner hash join...
        -> Filter: (person.AddressId is not null)...
            -> Index range scan on person using BirthDate...
        -> Hash
            -> Index lookup on language using Language...
    -> Filter: (address.CountryCode = 'JPN')...
        -> Single-row index lookup on address using PRIMARY...

1 row in set (0.0009 sec)

Listing 24-12Updating the index statistics to change the query plan

这极大地改变了查询计划(为了可读性，只包括了树格式查询计划的一部分)，通过比较树格式查询计划可以很容易地看出这一点。这些表仍然以相同的顺序连接，但是现在使用散列连接来连接语言和人员表。这是有效的，因为只需要语言表中的一行，所以对person表进行表扫描并根据出生日期进行过滤是一个不错的选择。在大多数使用错误索引的情况下，更新索引统计信息可以解决问题，可能是在更改 InnoDB 对表进行索引潜水的次数之后。

Caution

ANALYZE TABLE为被分析的表触发一个隐式的FLUSH TABLES。如果您有使用被分析的表的长时间运行的查询，则在长时间运行的查询完成之前，不能启动需要访问这些表的其他查询。

在某些情况下，不可能通过更新索引统计信息来解决性能问题。在这种情况下，您可以使用索引提示(IGNORE INDEX、USE INDEX和FORCE INDEX)来影响 MySQL 将使用的索引。清单 24-13 展示了一个在将索引统计数据改回过时状态后，对与之前相同的查询执行此操作的示例。

mysql> UPDATE mysql.innodb_index_stats
          SET stat_value = 1000
        WHERE database_name = 'chapter_24'
              AND table_name = 'person'
              AND index_name = 'LanguageId'
              AND stat_name = 'n_diff_pfx01';
Query OK, 1 row affected (0.0920 sec)

Rows matched: 1  Changed: 1  Warnings: 0

mysql> FLUSH TABLE chapter_24.person;
Query OK, 0 rows affected (0.0498 sec)

mysql> EXPLAIN
        SELECT PersonId, FirstName,
               Surname, BirthDate
          FROM chapter_24.person USE INDEX (BirthDate)
               INNER JOIN chapter_24.address
                    USING (AddressId)
               INNER JOIN chapter_24.language
                    USING (LanguageId)
         WHERE BirthDate BETWEEN '2000-01-01'
                             AND '2000-12-31'
               AND CountryCode = 'JPN'
               AND Language = 'English'\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: language
   partitions: NULL
         type: ref
possible_keys: PRIMARY,Language
          key: Language
      key_len: 142
          ref: const
         rows: 1
     filtered: 100
        Extra: Using index
*************************** 2\. row ***************************
           id: 1
  select_type: SIMPLE
        table: person
   partitions: NULL
         type: range
possible_keys: BirthDate
          key: BirthDate
      key_len: 3
          ref: NULL
         rows: 8
     filtered: 0.625
        Extra: Using index condition; Using where; Using join buffer (Block Nested Loop)
*************************** 3\. row ***************************
           id: 1
  select_type: SIMPLE
        table: address
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY,CountryCode
          key: PRIMARY
      key_len: 4
          ref: chapter_24.person.AddressId
         rows: 1
     filtered: 6.079921722412109
        Extra: Using where
3 rows in set, 1 warning (0.0016 sec)

Listing 24-13Improving the query plan using an index hint

这一次为person表添加了USE INDEX (BirthDate)索引提示，它给出了与索引统计信息更新时相同的查询计划。注意，person表的可能键只包括BirthDate。这种方法的缺点是，如果数据发生变化，优化器不具备更改查询计划的灵活性，因此BirthDate索引不再是最佳的。

这个例子在person表上有三个不同的条件(生日的日期范围和两个连接条件)。在某些情况下，当一个表上有多个条件时，对查询进行一些更广泛的重写是有益的。

重写复杂的索引条件

在某些情况下，查询变得非常复杂，以至于优化器不可能提出一个好的查询计划，因此有必要重写查询。重写有助于在同一个表上包含多个筛选器的情况下，索引合并算法无法有效地使用。

考虑以下查询:

mysql> EXPLAIN FORMAT=TREE
        SELECT *
          FROM chapter_24.person
         WHERE BirthDate < '1930-01-01'
            OR AddressId = 3417\G
*************************** 1\. row ***************************
EXPLAIN:
-> Filter: ((chapter_24.person.BirthDate < DATE'1930-01-01') or (chapter_24.person.AddressId = 3417))  (cost=88.28 rows=111)
    -> Index range scan on person using sort_union(BirthDate,AddressId)  (cost=88.28 rows=111)

1 row in set (0.0006 sec)

对于BirthDate和AddressId列都有索引，但是没有跨越这两列的索引。一种可能是使用索引合并，如果优化器认为好处足够大，它将默认选择索引合并。通常，这是执行查询的首选方式，但是对于某些查询(尤其是比本例更复杂的查询)，将两个条件拆分成两个查询并使用 union 合并结果会有所帮助:

mysql> EXPLAIN FORMAT=TREE
       (SELECT *
          FROM chapter_24.person
         WHERE BirthDate < '1930-01-01'
       ) UNION DISTINCT (
        SELECT *
          FROM chapter_24.person
         WHERE AddressId = 3417
       )\G
*************************** 1\. row ***************************
EXPLAIN:
-> Table scan on <union temporary>  (cost=2.50 rows=0)
    -> Union materialize with deduplication
        -> Index range scan on person using BirthDate, with index condition: (chapter_24.person.BirthDate < DATE'1930-01-01')  (cost=48.41 rows=107)
        -> Index lookup on person using AddressId (AddressId=3417)  (cost=1.40 rows=4)

1 row in set (0.0006 sec)

一个UNION DISTINCT(也是默认的 union)用于确保满足两个标准的行不会被包含两次。图 24-6 并排显示了两个查询计划。

图 24-6

原始查询和重写查询的查询计划

左边是使用索引合并的原始查询(sort_union算法),右边是手工编写的联合。

重写复杂查询

优化器在 MySQL 8 中添加了几个转换规则，因此它可以将查询重写为性能更好的形式。这意味着，随着优化器知道越来越多的转换，重写复杂查询的需求不断减少。例如，在 8 . 0 . 17 版本中，增加了对将NOT IN(子查询)NOT EXISTS(子查询)IN(子查询)IS NOT TRUE和EXISTS(子查询)IS NOT TRUE重写为反联接的支持，这意味着子查询被移除。

也就是说，考虑如何重写查询仍然是好的，这样您就可以在优化器没有达到最佳解决方案或者不知道如何自己重写的情况下帮助优化器。还有一些情况下，您可以利用对通用表表达式(CTEs 也称为with语法)和窗口函数的支持，使查询更有效、更易读。本节将首先考虑常见的表表达式和窗口函数，然后使用IN(子查询)将查询重写为一个连接并使用两个查询。

Common Table Expressions and Window Functions

深入讨论使用常用表表达式和窗口函数的细节已经超出了本书的范围。本章将包括几个例子来说明如何使用这些特性。一个很好的概述起点是由丹尼尔·巴塞洛缪(Daniel Bartholomew)揭示并由 Apress ( www.apress.com/gp/book/9781484231197 )出版的)的 MariaDB 和 MySQL 公共表表达式和窗口函数。

Guilhem Bichot(在 MySQL 中实现常用表表达式的 MySQL 开发者)在特性刚开发出来的时候也写过一个关于常用表表达式的博客系列，分为四个部分: https://mysqlserverteam.com/?s=common+table+expressions 。还有其他 MySQL 开发者的两篇关于窗口功能的博客: https://mysqlserverteam.com/?s=window+functions 。

关于最新的信息，最好的来源是 MySQL 参考手册。 https://dev.mysql.com/doc/refman/en/with.html 中描述了常用的表格表达式。根据函数是常规函数还是聚合函数，窗口函数分为两部分: https://dev.mysql.com/doc/refman/en/window-functions.html ，其中还包括对窗口函数的一般性讨论，以及 https://dev.mysql.com/doc/refman/en/group-by-functions.html ，用于聚合窗口函数。

常用表表达式

公用表表达式功能允许您在查询开始时定义一个子查询，并在查询的主要部分将其用作普通表。使用公共表表达式代替内联子查询有几个优点，包括更好的性能和可读性。更好的性能部分来自于支持在一个查询中多次引用公共表表达式，而内联子查询只能被引用一次。

例如，考虑一个针对sakila数据库的查询，该数据库计算负责租赁的每个员工每月的销售额:

SELECT DATE_FORMAT(r.rental_date,
                   '%Y-%m-01'
       ) AS FirstOfMonth,
       r.staff_id,
       SUM(p.amount) as SalesAmount
  FROM sakila.payment p
       INNER JOIN sakila.rental r
            USING (rental_id)
 GROUP BY FirstOfMonth, r.staff_id;

如果你想知道每月的销售额变化有多大，那么你需要将一个月的销售额与前一个月的销售额进行比较。要做到这一点而不使用公共表表达式，您要么需要将查询结果存储在一个临时表中，要么将其复制为两个子查询。清单 24-14 显示了后者的一个例子。

SELECT current.staff_id,
       YEAR(current.FirstOfMonth) AS Year,
       MONTH(current.FirstOfMonth) AS Month,
       current.SalesAmount,
       (current.SalesAmount
          - IFNULL(prev.SalesAmount, 0)
       ) AS DeltaAmount
  FROM (
         SELECT DATE_FORMAT(r.rental_date,
                            '%Y-%m-01'
                ) AS FirstOfMonth,
                r.staff_id,
                SUM(p.amount) as SalesAmount
           FROM sakila.payment p
                INNER JOIN sakila.rental r
                     USING (rental_id)
          GROUP BY FirstOfMonth, r.staff_id
       ) current
       LEFT OUTER JOIN (
         SELECT DATE_FORMAT(r.rental_date,
                            '%Y-%m-01'
                ) AS FirstOfMonth,
                r.staff_id,
                SUM(p.amount) as SalesAmount

           FROM sakila.payment p
                INNER JOIN sakila.rental r
                     USING (rental_id)
          GROUP BY FirstOfMonth, r.staff_id
       ) prev ON prev.FirstOfMonth
                    = current.FirstOfMonth
                      - INTERVAL 1 MONTH
             AND prev.staff_id = current.staff_id
 ORDER BY current.staff_id,
          current.FirstOfMonth;

Listing 24-14The monthly sales and change in sales without CTEs

这很难成为最容易阅读和理解的查询。这两个子查询是相同的，并且与用于查找每个员工每月销售额的子查询相同。通过比较同一名工作人员的当月和上月，将两个衍生表连接起来。最后，结果按工作人员和当月排序。结果如清单 24-15 所示。

+----------+------+-------+-------------+-------------+
| staff_id | Year | Month | SalesAmount | DeltaAmount |
+----------+------+-------+-------------+-------------+
|        1 | 2005 |     5 |     2340.42 |     2340.42 |
|        1 | 2005 |     6 |     4832.37 |     2491.95 |
|        1 | 2005 |     7 |    14061.58 |     9229.21 |
|        1 | 2005 |     8 |    12072.08 |    -1989.50 |
|        1 | 2006 |     2 |      218.17 |      218.17 |
|        2 | 2005 |     5 |     2483.02 |     2483.02 |
|        2 | 2005 |     6 |     4797.52 |     2314.50 |
|        2 | 2005 |     7 |    14307.33 |     9509.81 |
|        2 | 2005 |     8 |    11998.06 |    -2309.27 |
|        2 | 2006 |     2 |      296.01 |      296.01 |
+----------+------+-------+-------------+-------------+
10 rows in set (0.1406 sec)

Listing 24-15The result of the monthly sales query

从结果中需要注意的一点是，2005 年 9 月到 2006 年 1 月这几个月没有销售数据。该查询假设该期间的销售额为 0。当重写该查询以使用窗口函数时，将展示如何添加缺少的月份。

图 24-7 显示了这个版本的查询的查询计划。

图 24-7

非 CTE 查询的可视化解释

查询计划显示子查询被评估了两次；然后，在名为 current 的子查询上使用全表扫描执行连接，并在嵌套循环中使用索引(和自动生成的索引)进行连接，以形成按文件排序的结果。

如果使用公用表表达式，只需定义一次子查询并引用两次即可。这简化了查询并使其性能更好。使用公共表表达式的查询版本如清单 24-16 所示。

WITH monthly_sales AS (
  SELECT DATE_FORMAT(r.rental_date,
                     '%Y-%m-01'
         ) AS FirstOfMonth,
         r.staff_id,
         SUM(p.amount) as SalesAmount
    FROM sakila.payment p
         INNER JOIN sakila.rental r
              USING (rental_id)
   GROUP BY FirstOfMonth, r.staff_id
)
SELECT current.staff_id,
       YEAR(current.FirstOfMonth) AS Year,
       MONTH(current.FirstOfMonth) AS Month,
       current.SalesAmount,
       (current.SalesAmount
          - IFNULL(prev.SalesAmount, 0)
       ) AS DeltaAmount
  FROM monthly_sales current
       LEFT OUTER JOIN monthly_sales prev
               ON prev.FirstOfMonth
                     = current.FirstOfMonth
                       - INTERVAL 1 MONTH
              AND prev.staff_id = current.staff_id
 ORDER BY current.staff_id,
          current.FirstOfMonth;

Listing 24-16The monthly sales and change in sales using CTE

公共表表达式首先用关键字WITH定义，并命名为monthly_sales。查询的主要部分中的表列表可以引用monthly_sales。该查询的执行时间大约是原始查询的一半。一个额外的好处是，如果业务逻辑发生变化，您只需要在一个地方更新它，这降低了查询中出现错误的可能性。图 24-8 显示了使用公共表表达式的查询版本的查询计划。

图 24-8

使用公用表表达式时的直观解释

查询计划显示子查询只执行一次，然后作为常规表重用。否则，查询计划保持不变。

您也可以使用窗口函数来解决这个问题。

窗口功能

窗口函数允许您定义一个框架，其中窗口函数返回值依赖于框架中的其他行。您可以使用它来生成行号和总计百分比，将一行与上一行或下一行进行比较，等等。在这里，我们将探讨前面的例子，即找出每月的销售数字，并将其与前一个月进行比较。

您可以使用LAG() window 函数来获取前一行中某一列的值。清单 24-17 展示了如何使用它来重写月度销售查询，以使用LAG()窗口函数以及添加没有销售额的月份。

WITH RECURSIVE
  month AS
  (SELECT MIN(DATE_FORMAT(rental_date,
                          '%Y-%m-01'
          )) AS FirstOfMonth,
          MAX(DATE_FORMAT(rental_date,
                          '%Y-%m-01'
          )) AS LastMonth
     FROM sakila.rental
    UNION
   SELECT FirstOfMonth + INTERVAL 1 MONTH,
          LastMonth
     FROM month
    WHERE FirstOfMonth < LastMonth
),
  staff_member AS (
  SELECT staff_id
    FROM sakila.staff
),
  monthly_sales AS (
  SELECT month.FirstOfMonth,
         s.staff_id,
         IFNULL(SUM(p.amount), 0) as SalesAmount
    FROM month
         CROSS JOIN staff_member s
         LEFT OUTER JOIN sakila.rental r
                 ON r.rental_date >=
                       month.FirstOfMonth
                AND r.rental_date < month.FirstOfMonth
                                    + INTERVAL 1 MONTH
                AND r.staff_id = s.staff_id
         LEFT OUTER JOIN sakila.payment p
              USING (rental_id)
   GROUP BY FirstOfMonth, s.staff_id
)
SELECT staff_id,
       YEAR(FirstOfMonth) AS Year,
       MONTH(FirstOfMonth) AS Month,
       SalesAmount,
       (SalesAmount
          - LAG(SalesAmount, 1, 0) OVER w_month
       ) AS DeltaAmount
  FROM monthly_sales
WINDOW w_month AS (ORDER BY staff_id, FirstOfMonth)
 ORDER BY staff_id, FirstOfMonth;

Listing 24-17Combing CTEs and the LAG() window function

这个查询乍一看似乎很复杂；但是，这样做的原因是，前两个常用表表达式用于将第一个月和最后一个月之间的每个月的销售数据与租赁数据相加。month和staff_member表之间的叉积(注意如何使用一个显式的CROSS JOIN来表明交叉连接是有意的)被用作monthly_sales表的基础，并在rental和payment表上进行外部连接。

主查询现在变得简单了，因为所有需要的信息都可以在monthly_sales表中找到。通过staff_id和FirstOfMonth,对销售数据进行排序来定义窗口，并且在该窗口上使用LAG()窗口功能。清单 24-18 显示了结果。

+----------+------+-------+-------------+-------------+
| staff_id | Year | Month | SalesAmount | DeltaAmount |
+----------+------+-------+-------------+-------------+
|        1 | 2005 |     5 |     2340.42 |     2340.42 |
|        1 | 2005 |     6 |     4832.37 |     2491.95 |
|        1 | 2005 |     7 |    14061.58 |     9229.21 |
|        1 | 2005 |     8 |    12072.08 |    -1989.50 |
|        1 | 2005 |     9 |        0.00 |   -12072.08 |
|        1 | 2005 |    10 |        0.00 |        0.00 |
|        1 | 2005 |    11 |        0.00 |        0.00 |
|        1 | 2005 |    12 |        0.00 |        0.00 |
|        1 | 2006 |     1 |        0.00 |        0.00 |
|        1 | 2006 |     2 |      218.17 |      218.17 |
|        2 | 2005 |     5 |     2483.02 |     2264.85 |
|        2 | 2005 |     6 |     4797.52 |     2314.50 |
|        2 | 2005 |     7 |    14307.33 |     9509.81 |
|        2 | 2005 |     8 |    11998.06 |    -2309.27 |
|        2 | 2005 |     9 |        0.00 |   -11998.06 |
|        2 | 2005 |    10 |        0.00 |        0.00 |
|        2 | 2005 |    11 |        0.00 |        0.00 |
|        2 | 2005 |    12 |        0.00 |        0.00 |
|        2 | 2006 |     1 |        0.00 |        0.00 |
|        2 | 2006 |     2 |      296.01 |      296.01 |
+----------+------+-------+-------------+-------------+

Listing 24-18The result of the sales query using the LAG() function

请注意没有销售数据的月份是如何添加销售金额为 0 的。

Note

窗口不需要数据排序所依据的值。如果省略month和staff_member表达式，2006 年 2 月的滞后时间将变成 2005 年 8 月。这很可能是您想要的——但是与清单 24-14 中的原始查询找到的解决方案相比，这是一个不同的结果。这是留给读者的一个练习，让他们更改查询并查看不同之处。

将子查询重写为连接

当有子查询时，可以选择将子查询更改为连接。在可能的情况下，优化器通常会自己执行这种重写，但是偶尔，在过程中帮助优化器也是有用的。

例如，考虑以下查询:

SELECT *
  FROM chapter_24.person
 WHERE AddressId IN (
         SELECT AddressId
           FROM chapter_24.address
          WHERE CountryCode = 'AUS'
                AND District = 'Queensland');

该查询查找居住在澳大利亚昆士兰州的所有人。它也可以写成person和address表之间的连接:

SELECT person.*
  FROM chapter_24.person
       INNER JOIN chapter_24.address
             USING (AddressId)
 WHERE CountryCode = 'AUS'
       AND District = 'Queensland';

事实上，MySQL 进行了完全相同的重写(除了优化器选择地址表作为第一个表，因为过滤器就在那里)。这是半连接优化的一个例子。如果您遇到优化器无法重写查询的查询，您可以像这样进行重写。通常，越接近只包含连接的查询，查询的性能就越好。然而，查询调优的过程要比这复杂得多，有时反其道而行之可以提高查询性能。教训是永远要考验。

您可以使用的另一个选项是将查询分成几个部分，然后分步执行。

将查询拆分成多个部分

最后一种选择是将查询分成两个或更多部分。由于 MySQL 8 支持通用表表达式和窗口函数，这种类型的重写不像 MySQL 的旧版本那样频繁。然而，记住这一点是有用的。

Tip

不要低估将一个复杂的查询拆分成两个或更多更简单的查询并逐渐生成查询结果的能力。

例如，考虑与前面讨论中相同的查询，其中您查找居住在澳大利亚昆士兰州的所有人。您可以将子查询作为一个单独的查询来执行，然后将结果放回IN()操作符中。这种重写最适用于应用能够以编程方式生成下一个查询的应用。为了简单起见，这个讨论将只显示所需的 SQL。清单 24-19 显示了这两个查询。

mysql> SET SESSION transaction_isolation = 'REPEATABLE-READ';
Query OK, 0 rows affected (0.0002 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.0400 sec)

mysql> SELECT AddressId
         FROM chapter_24.address
        WHERE CountryCode = 'AUS'
              AND District = 'Queensland';
+-----------+
| AddressId |
+-----------+
|       132 |
|       143 |
|       136 |
|       142 |
+-----------+
4 rows in set (0.0008 sec)

mysql> SELECT *
         FROM chapter_24.person
        WHERE AddressId IN (132, 136, 142, 143)\G
*************************** 1\. row ***************************
  PersonId: 79
 FirstName: Dimitra
   Surname: Turner
 BirthDate: 1937-11-16
 AddressId: 132
LanguageId: 110
*************************** 2\. row ***************************
  PersonId: 356
 FirstName: Julian
   Surname: Serrano
 BirthDate: 2017-07-30
 AddressId: 132
LanguageId: 110
2 rows in set (0.0005 sec)

mysql> COMMIT;
Query OK, 0 rows affected (0.0003 sec)

Listing 24-19Splitting a query into two steps

使用具有REPEATABLE-READ事务隔离级别的事务来执行查询，这意味着两个SELECT查询将使用相同的读取视图，从而以相同的方式对应于相同的时间点，就像您将问题作为一个查询来执行一样。对于这样简单的查询，使用多个查询没有任何好处；然而，在真正复杂的查询的情况下，将查询的一部分分离出来(可能包括一些连接)可能是一个优势。将查询分成几部分的另一个好处是，在某些情况下，您可以提高缓存效率。对于本例，如果有其他查询使用相同的子查询来查找昆士兰的地址，缓存可以让您将结果重复用于多种用途。

队列系统:跳过锁定

与数据库相关的一个常见任务是处理存储在队列中的一些任务列表。一个例子是在商店里处理订单。所有任务都被处理并且只被处理一次是很重要的，但是哪个应用线程处理每个任务并不重要。SKIP LOCKED条款非常适合这种情况。

考虑清单 24-20 中定义的表jobqueue。

mysql> SHOW CREATE TABLE chapter_24.jobqueue\G
*************************** 1\. row ***************************
       Table: jobqueue
Create Table: CREATE TABLE `jobqueue` (
  `JobId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `SubmitDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `HandledDate` datetime DEFAULT NULL,
  PRIMARY KEY (`JobId`),
  KEY `HandledDate` (`HandledDate`,`SubmitDate`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.0004 sec)

mysql> SELECT *
         FROM chapter_24.jobqueue;
+-------+---------------------+-------------+
| JobId | SubmitDate          | HandledDate |
+-------+---------------------+-------------+
|     1 | 2019-07-01 19:32:30 | NULL        |
|     2 | 2019-07-01 19:32:33 | NULL        |
|     3 | 2019-07-01 19:33:40 | NULL        |
|     4 | 2019-07-01 19:35:12 | NULL        |
|     5 | 2019-07-01 19:40:24 | NULL        |
|     6 | 2019-07-01 19:40:28 | NULL        |
+-------+---------------------+-------------+
6 rows in set (0.0005 sec)

Listing 24-20The jobqueue table

and data

当HandledDate为NULL时，则任务尚未处理，待处理。例如，如果您的应用设置为获取最早的未处理任务，并且您希望依靠 InnoDB 行锁来防止两个线程执行同一任务，那么您可以使用SELECT ... FOR UPDATE(在现实世界中，该语句将是更大事务的一部分):

SELECT JobId
  FROM chapter_24.jobqueue
 WHERE HandledDate IS NULL
 ORDER BY SubmitDate
 LIMIT 1
   FOR UPDATE;

这对于第一个请求很有效，但是下一个请求将被阻塞，直到发生锁等待超时或者第一个任务已经被处理，因此任务处理是序列化的。诀窍是确保在您过滤和排序的列上有一个索引，然后使用SKIP LOCKED子句。那么第二个连接将简单地跳过锁定的行，找到满足搜索条件的第一个非锁定的行。清单 24-21 显示了两个连接的例子，每个连接从队列中获取一个任务。

Connection 1> START TRANSACTION;
Query OK, 0 rows affected (0.0002 sec)

Connection 1> SELECT JobId
                FROM chapter_24.jobqueue
               WHERE HandledDate IS NULL
               ORDER BY SubmitDate
               LIMIT 1
                 FOR UPDATE
                SKIP LOCKED;
+-------+
| JobId |
+-------+
|     1 |
+-------+
1 row in set (0.0004 sec)

Connection 2> START TRANSACTION;
Query OK, 0 rows affected (0.0003 sec)

Connection 2> SELECT JobId
                FROM chapter_24.jobqueue
               WHERE HandledDate IS NULL
               ORDER BY SubmitDate
               LIMIT 1
                 FOR UPDATE
                SKIP LOCKED;
+-------+
| JobId |
+-------+
|     2 |
+-------+
1 row in set (0.0094 sec)

Listing 24-21Fetching tasks with SKIP LOCKED

现在，两个连接都可以获取任务并同时处理它们。一旦任务完成，可以设置HandledDate并将任务标记为完成。与连接设置锁列相比，这种方法的优点是，如果连接由于某种原因失败，锁会自动释放。

您可以使用性能模式中的data_locks表来查看哪个连接拥有每个锁(锁的顺序取决于线程 id，线程 id 对您来说是不同的):

mysql> SELECT THREAD_ID, INDEX_NAME, LOCK_DATA
         FROM performance_schema.data_locks
        WHERE OBJECT_SCHEMA = 'chapter_24'
              AND OBJECT_NAME = 'jobqueue'
              AND LOCK_TYPE = 'RECORD'
        ORDER BY THREAD_ID, EVENT_ID;
+-----------+------------+-----------------------+
| THREAD_ID | INDEX_NAME | LOCK_DATA             |
+-----------+------------+-----------------------+
|     21705 | PRIMARY    | 1                     |
|     21705 | SubmitDate | NULL, 0x99A383381E, 1 |
|     25101 | PRIMARY    | 2                     |
|     25101 | SubmitDate | NULL, 0x99A3833821, 2 |
+-----------+------------+-----------------------+
4 rows in set (0.0008 sec)

十六进制值是SubmitDate列的编码日期时间值。从输出中可以看出，每个连接在二级索引中持有一个记录锁，在主键中持有一个记录锁，正如从SELECT查询返回的JobId值中所预期的那样。

许多 OR 或 IN 条件

一种可能导致性能混乱的查询类型是具有许多范围条件的查询。当有许多OR条件或者IN ()操作符有许多值时，这通常会是一个问题。在某些情况下，对条件的微小更改可能会完全改变查询计划。

当优化器在索引列上遇到范围条件时，它有两种选择:它可以假设索引中的所有值出现的频率相同，或者它可以要求存储引擎进行索引搜索以确定每个范围的频率。前者最便宜，但后者准确得多。要决定使用哪种方法，有eq_range_index_dive_limit选项(默认值为 200)。如果有eq_range_index_dive_limit或更多的范围，优化器将只查看索引的基数，并假设所有值以相同的频率出现。如果范围较少，将要求存储引擎提供每个范围。

当每个值出现频率相等的假设不成立时，可能会出现性能问题。在这种情况下，当超过由eq_range_index_dive_limit,设置的阈值时，匹配条件的估计行数可能会突然发生显著变化，从而导致完全不同的查询计划。(当在IN ()操作符中有许多值时，真正重要的是匹配所包含值的平均行数接近从索引统计中获得的估计值。因此，列表中的值越多，包含的代表性样本就越多。)

清单 24-22 展示了一个payment表的例子，它有一个带索引的列ContactId。大多数行的ContactId设置为NULL,，索引的基数为 21。

mysql> SHOW CREATE TABLE chapter_24.payment\G
*************************** 1\. row ***************************
       Table: payment
Create Table: CREATE TABLE `payment` (
  `PaymentId` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `Amount` decimal(5,2) NOT NULL,
  `ContactId` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`PaymentId`),
  KEY `ContactId` (`ContactId`)
) ENGINE=InnoDB AUTO_INCREMENT=32798 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.0004 sec)

mysql> SELECT COUNT(ContactId), COUNT(*)
         FROM chapter_24.payment;
+------------------+----------+
| COUNT(ContactId) | COUNT(*) |
+------------------+----------+
|               20 |    20000 |
+------------------+----------+
1 row in set (0.0060 sec)

mysql> SELECT CARDINALITY
         FROM information_schema.STATISTICS
        WHERE TABLE_SCHEMA = 'chapter_24'
              AND TABLE_NAME = 'payment'
              AND INDEX_NAME = 'ContactId';
+-------------+
| CARDINALITY |
+-------------+
|          21 |
+-------------+
1 row in set (0.0009 sec)

mysql> SET SESSION eq_range_index_dive_limit=5;
Query OK, 0 rows affected (0.0003 sec)

mysql> EXPLAIN
        SELECT *
          FROM chapter_24.payment
         WHERE ContactId IN (1, 2, 3, 4)\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: range
possible_keys: ContactId

          key: ContactId
      key_len: 5
          ref: NULL
         rows: 4
     filtered: 100
        Extra: Using index condition
1 row in set, 1 warning (0.0006 sec)

Listing 24-22Query with many range conditions

在示例中，eq_range_index_dive_limit被设置为 5，以避免需要指定一长串值。对于四个值，优化器已经请求了这四个值中每一个值的统计信息，估计的行数是 4。但是，如果您将值列表变长，事情就会发生变化:

mysql> EXPLAIN
        SELECT *
          FROM chapter_24.payment
         WHERE ContactId IN (1, 2, 3, 4, 5)\G
*************************** 1\. row ***************************
...
          key: ContactId
      key_len: 5
          ref: NULL
         rows: 4785
...

突然，估计有 4785 行匹配，而不是真正匹配的 5 行。仍然使用索引，但是如果连接中涉及到带有这个条件的付款表，那么优化器很可能会选择非最佳的连接顺序。如果您让值列表变得更长，优化器将完全停止使用索引，并进行全表扫描，因为它认为索引非常有效:

mysql> EXPLAIN
        SELECT *
          FROM chapter_24.payment
         WHERE ContactId IN (1, 2, 3, 4, 5, 6, 7)\G
*************************** 1\. row ***************************
...
         type: ALL
possible_keys: ContactId
          key: NULL
...
         rows: 20107
...

这个查询只返回 7 行，所以索引是高度选择性的。那么，如何提高优化器的理解能力呢？根据不良估计原因的确切性质，有各种可能的行动。对于这个特殊的问题，您有以下选择:

• 增加eq_range_index_dive_limit。

• 更改innodb_stats_method选项。

• 强制 MySQL 使用索引。

最简单的解决方法就是增加eq_range_index_dive_limit。默认值为 200，这是一个很好的起点。如果您有一个候选查询，您可以使用不同的值eq_range_index_dive_limit进行测试，并确定执行索引挖掘所增加的成本是否值得从获得更好的行估计中节省下来的成本。测试查询的新值eq_range_index_dive_limit的一个好方法是在SET_VAR()优化器提示中设置该值:

SELECT /*+ SET_VAR(eq_range_index_dive_limit=8) */
       *
  FROM chapter_24.payment
 WHERE ContactId IN (1, 2, 3, 4, 5, 6, 7);

在这种情况下，依赖基数导致如此糟糕的行估计的原因是，几乎所有的行都将ContactId设置为NULL。默认情况下，InnoDB 认为索引中具有NULL值的所有行都具有相同的值。这就是为什么在这个例子中基数只有 21。如果您将innodb_stats_method切换到nulls_ignored，基数将只基于非NULL值进行计算，如清单 24-23 所示。

mysql> SET GLOBAL innodb_stats_method = nulls_ignored;
Query OK, 0 rows affected (0.0003 sec)

mysql> ANALYZE TABLE chapter_24.payment;
+--------------------+---------+----------+----------+
| Table              | Op      | Msg_type | Msg_text |
+--------------------+---------+----------+----------+
| chapter_24.payment | analyze | status   | OK       |
+--------------------+---------+----------+----------+
1 row in set (0.1411 sec)

mysql> SELECT CARDINALITY
         FROM information_schema.STATISTICS
        WHERE TABLE_SCHEMA = 'chapter_24'
              AND TABLE_NAME = 'payment'
              AND INDEX_NAME = 'ContactId';
+-------------+
| CARDINALITY |
+-------------+
|       20107 |
+-------------+
1 row in set (0.0009 sec)

mysql> EXPLAIN
        SELECT *
          FROM chapter_24.payment
         WHERE ContactId IN (1, 2, 3, 4, 5, 6, 7)\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: range
possible_keys: ContactId
          key: ContactId
      key_len: 5
          ref: NULL
         rows: 7
     filtered: 100
        Extra: Using index condition
1 row in set, 1 warning (0.0011 sec)

Listing 24-23Using innodb_stats_method = nulls_ignored

这种方法的最大问题是innodb_stats_method只能全局设置，因此它会影响所有的表，并且可能会对其他查询产生负面影响。对于本例，将innodb_stats_method设置回默认值，并再次重新计算索引统计信息:

mysql> SET GLOBAL innodb_stats_method = DEFAULT;
Query OK, 0 rows affected (0.0004 sec)

mysql> SELECT @@global.innodb_stats_method\G
*************************** 1\. row ***************************
@@global.innodb_stats_method: nulls_equal
1 row in set (0.0003 sec)

mysql> ANALYZE TABLE chapter_24.payment;
+--------------------+---------+----------+----------+
| Table              | Op      | Msg_type | Msg_text |
+--------------------+---------+----------+----------+
| chapter_24.payment | analyze | status   | OK       |
+--------------------+---------+----------+----------+
1 row in set (0.6683 sec)

最后一个选项是使用索引提示来强制 MySQL 使用索引。您将需要清单 24-24 中所示的FORCE INDEX变体。

mysql> EXPLAIN
        SELECT *
          FROM chapter_24.payment FORCE INDEX (ContactId)
         WHERE ContactId IN (1, 2, 3, 4, 5, 6, 7)\G
*************************** 1\. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: range
possible_keys: ContactId
          key: ContactId
      key_len: 5
          ref: NULL
         rows: 6699
     filtered: 100
        Extra: Using index condition
1 row in set, 1 warning (0.0007 sec)

Listing 24-24Using FORCE INDEX to force MySQL to use the index

这将使查询的执行速度更快，就好像它有更准确的统计数据一样。但是，如果payment表是具有相同WHERE子句的连接的一部分，那么行估计仍然是错误的(估计的 6699 行与实际的 7 行相比)，因此查询计划可能仍然是错误的，在这种情况下，您需要告诉优化器最佳的连接顺序是什么。

摘要

本章展示了几个提高查询性能的技术示例。第一个主题是查看过度全表扫描的症状，然后查看全表扫描的两个主要原因:查询错误和索引无法使用。无法使用索引的典型原因是所使用的列没有形成索引的左前缀、数据类型不匹配或者对列使用了函数。

也可能出现使用索引的情况，但使用情况可以改善。这可以是转换索引以覆盖查询所需的所有列，使用错误的索引，或者用复杂的条件重写查询可以改进查询计划。

重写复杂的查询也很有用。MySQL 8 支持常用的表表达式和窗口函数，它们既可以用来简化查询，也可以使查询性能更好。在其他情况下，它可以帮助进行一些优化器通常会做的重写，或者将查询分成多个部分。

最后，讨论了两种常见的情况。第一个是使用一个队列，其中可以使用SKIP LOCKED子句有效地访问第一个非锁定行。第二种情况是有一个很长的OR条件列表或者一个有很多值的IN ()操作符，当范围的数量达到由eq_range_index_dive_limit选项设置的数量时，这可能会导致查询计划发生令人惊讶的变化。

下一章着眼于提高 DDL 和批量数据装载的性能。

二十五、DDL 和批量数据加载

有时，需要执行模式更改或将大量数据导入表中。这可能是为了适应新功能、恢复备份、导入由第三方流程生成的数据，或者类似的目的。虽然原始磁盘写性能自然非常重要，但是您也可以在 MySQL 端做一些事情来提高这些操作的性能。

Tip

如果您遇到恢复备份需要太长时间的问题，请考虑切换到直接复制数据文件(物理备份)的备份方法，如使用 MySQL Enterprise Backup。物理备份的一个主要优点是，它们比逻辑备份(包含作为INSERT语句或 CSV 文件的数据)的恢复速度快得多。

本章从讨论模式变化开始，然后转到加载数据的一些一般考虑事项。这些注意事项也适用于一次插入单行的情况。本章的其余部分将介绍如何通过按主键顺序插入来提高数据加载性能，缓冲池和辅助索引如何影响性能、配置以及调整语句本身。最后演示了 MySQL Shell 的并行导入特性。

模式更改

当需要对模式进行更改时，存储引擎可能需要做大量的工作，可能需要制作一个全新的表副本。这一节将讨论如何加速这个过程，首先是模式更改支持的算法，然后是配置等其他考虑因素。

Note

虽然OPTIMIZE TABLE没有对表的模式进行任何更改，但 InnoDB 将其实现为一个ALTER TABLE后跟一个ANALYZE TABLE。所以本节的讨论也适用于OPTIMIZE TABLE。

算法

MySQL 支持几种用于ALTER TABLE的算法，这些算法决定了如何执行模式更改。一些模式更改可以通过更改表定义“立即”完成，而另一方面，一些更改需要将整个表复制到新表中。

按照所需工作量的顺序，这些算法是

• INSTANT : 只对表格定义进行修改。虽然变化并不十分迅速，但却非常快。MySQL 8.0.12 及更高版本中提供了INSTANT算法。

• INPLACE : 通常在现有的表空间文件中进行更改(表空间 id 不变)，但也有一些例外，如ALTER TABLE <table name> FORCE(由OPTIMIZE TABLE使用)，它更像COPY算法，但允许并发数据更改。这可能是一个相对廉价的操作，但也可能涉及复制所有数据。

• COPY : 将现有数据复制到新的表空间文件中。这是影响最大的算法，因为它通常需要更多的锁，导致更多的 I/O，并且花费更长的时间。

通常，INSTANT和INPLACE算法允许并发数据更改，这减少了对其他连接的影响，而COPY至少需要一个读锁。MySQL 将根据请求的更改选择影响最小的算法，但您也可以显式请求特定的算法。例如，如果您希望确保 MySQL 不会继续进行更改，如果您选择的算法不受支持，这将非常有用。使用ALGORITHM关键字指定算法，例如:

mysql> ALTER TABLE world.city
         ADD COLUMN Council varchar(50),
             ALGORITHM=INSTANT;

如果无法使用请求的算法执行更改，语句将失败，并显示一个ER_ALTER_OPERATION_NOT_SUPPORTED错误(错误号 1845)，例如:

mysql> ALTER TABLE world.city
        DROP COLUMN Council,
             ALGORITHM=INSTANT;
ERROR: 1845: ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.

如果你能使用INSTANT算法，你显然会得到最好的ALTER TABLE性能。在编写时，允许使用INSTANT算法进行以下操作:

• 添加一个新列作为表中的最后一列。

• 添加生成的虚拟列。

• 删除生成的虚拟列。

• 为现有列设置默认值。

• 删除现有列的默认值。

• 更改数据类型为enum或set的列所允许的值列表。一个要求是该列的存储大小不变。

• 更改是否为现有索引显式设置索引类型(如BTREE)。

还有一些限制值得注意:

• 行格式不能是COMPRESSED。

• 该表不能有全文索引。

• 不支持临时表。

• 数据字典中的表不能使用即时算法。

Tip

例如，如果您需要向现有的表中添加一列，请确保将其作为最后一列添加，以便可以“立即”添加

就性能而言，就地更改通常(但不总是)比复制更改更快。此外，当在线进行模式更改(LOCK=NONE)时，InnoDB 必须跟踪模式更改执行期间所做的更改。这增加了开销，并且在操作结束时应用模式更改期间所做的更改需要时间。如果您能够在表上使用共享锁(LOCK=SHARED)或排他锁(LOCK=EXCLUSIVE)，那么与允许并发更改相比，您通常可以获得更好的性能。

其他考虑因素

由于就地或复制ALTER TABLE所做的工作是非常磁盘密集型的，所以对性能的最大影响是磁盘的速度以及在模式更改期间有多少其他写活动。这意味着，从性能角度来看，最好选择在实例和主机上几乎没有其他写入活动时，执行需要复制或移动大量数据的模式更改。这包括备份本身可能是非常 I/O 密集型的。

Tip

您可以使用性能模式监控 InnoDB 表的ALTER TABLE和OPTIMIZE TABLE的进度。最简单的方法是使用sys.session视图并查看progress列，它显示了总工作百分比的大致进度。默认情况下，该功能处于启用状态。

如果您的ALTER TABLE包括创建或重建二级索引(这包括OPTIMIZE TABLE和重建表的其他语句)，您可以使用innodb_sort_buffer_size选项来指定每个排序缓冲区可以使用多少内存。请注意，单个ALTER TABLE将创建多个缓冲区，因此注意不要将值设置得太大。默认值为 1 MiB，最大允许值为 64 MiB。在某些情况下，较大的缓冲区可能会提高性能。

当您创建全文索引时，您可以使用innodb_ft_sort_pll_degree选项来指定 InnoDB 将使用多少个线程来构建搜索索引。默认值为 2，支持的值介于 1 和 32 之间。如果您在大型表上创建全文索引，增加innodb_ft_sort_pll_degree的值可能是一个优势。

需要考虑的一个特殊 DDL 操作是删除或截断表。

删除或截断表

似乎没有必要考虑删除表的性能优化。似乎所有需要做的就是删除表空间文件并删除对表的引用。实际上，事情并不那么简单。

删除或截断表时的主要问题是缓冲池中对表数据的所有引用。特别是，自适应散列索引会引起问题。因此，在删除或截断大型表时，可以通过在操作期间禁用自适应散列索引来大大提高性能，例如:

mysql> SET GLOBAL innodb_adaptive_hash_index = OFF;
Query OK, 0 rows affected (0.1008 sec)

mysql> DROP TABLE <name of large table>;

mysql> SET GLOBAL innodb_adaptive_hash_index = ON;
Query OK, 0 rows affected (0.0098 sec)

禁用自适应散列索引将使受益于散列索引的查询运行得更慢，但是对于大小为几百吉字节或更大的表，禁用自适应散列索引的相对较小的减速通常优于潜在的停顿，因为删除对被删除或截断的表的引用的开销。

关于执行模式更改的讨论到此结束。本章的其余部分讨论加载数据。

一般数据加载注意事项

在讨论如何提高大容量插入的性能之前，有必要进行一个小测试并讨论结果。在测试中，200，000 行被插入到两个表中。其中一个表使用自动递增计数器作为主键，另一个表使用随机整数作为主键。两个表的行大小相同。

Tip

本节和下一节中的讨论同样适用于非批量插入。

数据加载完成后，清单 25-1 中的脚本可以用来确定表空间文件中每个页面的年龄，这是根据日志序列号(LSN)来测量的。日志序列号越高，页面被修改的时间越近。这个脚本的灵感来自杰瑞米·科尔 ¹ 的 innodb_ruby，它生成了一个类似于 innodb_ruby space-lsn-age-illustrate-svg命令的地图。但是 innodb_ruby 还不支持 MySQL 8，所以单独开发了一个 Python 程序。该程序已经过 Python 2.7 (Linux)和 3.6 (Linux 和微软 Windows)的测试。它也可以在本书的 GitHub 存储库中的 listing_25_1.py 文件中找到。

'''Read a MySQL 8 file-per-table tablespace file and generate an
SVG formatted map of the LSN age of each page.

Invoke with the --help argument to see a list of arguments and
Usage instructions.'''

import sys
import argparse
import math
from struct import unpack

# Some constants from InnoDB
FIL_PAGE_OFFSET = 4          # Offset for the page number
FIL_PAGE_LSN = 16            # Offset for the LSN
FIL_PAGE_TYPE = 24           # Offset for the page type
FIL_PAGE_TYPE_ALLOCATED = 0  # Freshly allocated page

def mach_read_from_2(page, offset):
    '''Read 2 bytes in big endian. Based on the function of the same
    name in the InnoDB source code.'''
    return unpack('>H', page[offset:offset + 2])[0]

def mach_read_from_4(page, offset):
    '''Read 4 bytes in big endian. Based on the function of the same
    name in the InnoDB source code.'''
    return unpack('>L', page[offset:offset + 4])[0]

def mach_read_from_8(page, offset):
    '''Read 8 bytes in big endian. Based on the function of the same
    name in the InnoDB source code.'''
    return unpack('>Q', page[offset:offset + 8])[0]

def get_color(lsn, delta_lsn, greyscale):
    '''Get the RGB color of a relative lsn.'''
    color_fmt = '#{0:02x}{1:02x}{2:02x}'

    if greyscale:
        value = int(255 * lsn / delta_lsn)
        color = color_fmt.format(value, value, value)
    else:
        # 0000FF -> 00FF00 -> FF0000 -> FFFF00
        # 256 + 256 + 256 values
        value = int((3 * 256 - 1) * lsn / delta_lsn)
        if value < 256:
            color = color_fmt.format(0, value, 255 - value)
        elif value < 512:
            value = value % 256
            color = color_fmt.format(value, 255 - value, 0)
        else:
            value = value % 256
            color = color_fmt.format(255, value, 0)

    return color

def gen_svg(min_lsn, max_lsn, lsn_age, args):
    '''Generate an SVG output and print to stdout.'''
    pages_per_row = args.width
    page_width = args.size
    num_pages = len(lsn_age)
    num_rows = int(math.ceil(1.0 * num_pages / pages_per_row))
    x1_label = 5 * page_width + 1
    x2_label = (pages_per_row + 7) * page_width
    delta_lsn = max_lsn - min_lsn

    print('<?xml version="1.0"?>')
    print('<svg xmlns:="http://www.w3.org/2000/svg" version="1.1">')
    print('<text x="{0}" y="{1}" font-family="monospace" font-size="{2}" '
          .format(x1_label, int(1.5 * page_width) + 1, page_width) +
          'font-weight="bold" text-anchor="end">Page</text>')

    page_number = 0
    page_fmt = '  <rect x="{0}" y="{1}" width="{2}" height="{2}" fill="{3}" />'
    label_fmt = '  <text x="{0}" y="{1}" font-family="monospace" '
    label_fmt += 'font-size="{2}" text-anchor="{3}">{4}</text>'
    for i in range(num_rows):
        y = (i + 2) * page_width
        for j in range(pages_per_row):
            x = 6 * page_width + j * page_width
            if page_number >= len(lsn_age) or lsn_age[page_number] is None:
                color = 'black'
            else:
                relative_lsn = lsn_age[page_number] - min_lsn
                color = get_color(relative_lsn, delta_lsn, args.greyscale)

            print(page_fmt.format(x, y, page_width, color))
            page_number += 1

        y_label = y + page_width
        label1 = i * pages_per_row
        label2 = (i + 1) * pages_per_row
        print(label_fmt.format(x1_label, y_label, page_width, 'end', label1))
        print(label_fmt.format(x2_label, y_label, page_width, 'start', label2))

    # Create a frame around the pages
    frame_fmt = '  <path stroke="black" stroke-width="1" fill="none" d="'
    frame_fmt += 'M{0},{1} L{2},{1} S{3},{1} {3},{4} L{3},{5} S{3},{6} {2},{6}'
    frame_fmt += ' L{0},{6} S{7},{6} {7},{5} L{7},{4} S{7},{1} {0},{1} Z" />'
    x1 = int(page_width * 6.5)
    y1 = int(page_width * 1.5)
    x2 = int(page_width * 5.5) + page_width * pages_per_row
    x2b = x2 + page_width
    y1b = y1 + page_width
    y2 = int(page_width * (1.5 + num_rows))
    y2b = y2 + page_width
    x1c = x1 - page_width
    print(frame_fmt.format(x1, y1, x2, x2b, y1b, y2, y2b, x1c))

    # Create legend
    x_left = 6 * page_width
    x_right = x_left + pages_per_row * page_width
    x_mid = x_left + int((x_right - x_left) * 0.5)
    y = y2b + 2 * page_width
    print('<text x="{0}" y="{1}" font-family="monospace" '.format(x_left, y) +
          'font-size="{0}" text-anchor="start">{1}</text>'.format(page_width,
                                                                  min_lsn))
    print('<text x="{0}" y="{1}" font-family="monospace" '.format(x_right, y) +
          'font-size="{0}" text-anchor="end">{1}</text>'.format(page_width,
                                                                  max_lsn))
    print('<text x="{0}" y="{1}" font-family="monospace" '.format(x_mid, y) +
          'font-size="{0}" font-weight="bold" text-anchor="middle">{1}</text>'
          .format(page_width, 'LSN Age'))

    color_width = 1
    color_steps = page_width * pages_per_row
    y = y + int(page_width * 0.5)
    for i in range(color_steps):
        x = 6 * page_width + i * color_width
        color = get_color(i, color_steps, args.greyscale)
        print('<rect x="{0}" y="{1}" width="{2}" height="{3}" fill="{4}" />'
              .format(x, y, color_width, page_width, color))

    print('</svg>')

def analyze_lsn_age(args):
    '''Read the tablespace file and find the LSN for each page.'''
    page_size_bytes = int(args.page_size[0:-1]) * 1024
    min_lsn = None
    max_lsn = None
    lsn_age = []
    with open(args.tablespace, 'rb') as fs:
        # Read at most 1000 pages at a time to avoid storing too much
        # in memory at a time.
        chunk = fs.read(1000 * page_size_bytes)
        while len(chunk) > 0:
            num_pages = int(math.floor(len(chunk) / page_size_bytes))
            for i in range(num_pages):
                # offset is the start of the page inside the
                # chunk of data
                offset = i * page_size_bytes
                # The page number, lsn for the page, and page
                # type can be found at the FIL_PAGE_OFFSET,
                # FIL_PAGE_LSN, and FIL_PAGE_TYPE offsets
                # relative to the start of the page.
                page_number = mach_read_from_4(chunk, offset + FIL_PAGE_OFFSET)
                page_lsn = mach_read_from_8(chunk, offset + FIL_PAGE_LSN)
                page_type = mach_read_from_2(chunk, offset + FIL_PAGE_TYPE)

                if page_type == FIL_PAGE_TYPE_ALLOCATED:
                    # The page has not been used yet
                    continue

                if min_lsn is None:
                    min_lsn = page_lsn
                    max_lsn = page_lsn
                else:
                    min_lsn = min(min_lsn, page_lsn)
                    max_lsn = max(max_lsn, page_lsn)

                if page_number == len(lsn_age):
                    lsn_age.append(page_lsn)
                elif page_number > len(lsn_age):
                    # The page number is out of order - expand the list first
                    lsn_age += [None] * (page_number - len(lsn_age))
                    lsn_age.append(page_lsn)
                else:
                    lsn_age[page_number] = page_lsn

            chunk = fs.read(1000 * page_size_bytes)

    sys.stderr.write("Total # Pages ...: {0}\n".format(len(lsn_age)))
    gen_svg(min_lsn, max_lsn, lsn_age, args)

def main():
    '''Parse the arguments and call the analyze_lsn_age()
    function to perform the analysis.'''
    parser = argparse.ArgumentParser(
        prog='listing_25_1.py',
        description='Generate an SVG map with the LSN age for each page in an' +
        ' InnoDB tablespace file. The SVG is printed to stdout.')

    parser.add_argument(
        '-g', '--grey', '--greyscale', default=False,
        dest='greyscale', action="store_true",
        help='Print the LSN age map in greyscale.')

    parser.add_argument(
        '-p', '--page_size', '--page-size', default="16k",
        dest='page_size',
        choices=['4k', '8k', '16k', '32k', '64k'],
        help='The InnoDB page size. Defaults to 16k.')

    parser.add_argument(
        '-s', '--size', default=16, dest="size",
        choices=[4, 8, 12, 16, 20, 24], type=int,
        help='The size of the square representing a page in the output. ' +
        'Defaults to 16.')

    parser.add_argument(
        '-w', '--width', default=64, dest="width",
        type=int,
        help='The number of pages to include per row in the output. ' +
        'The default is 64.')

    parser.add_argument(
        dest='tablespace',
        help='The tablespace file to analyze.')

    args = parser.parse_args()
    analyze_lsn_age(args)

if __name__ == '__main__':
    main()

Listing 25-1Python program to map the LSN age of InnoDB pages

在由每页的FIL_PAGE_OFFSET、FIL_PAGE_LSN和FIL_PAGE_TYPE常量定义的位置(以字节为单位)提取页码、日志序列号和页面类型。如果页面类型具有常量FIL_PAGE_TYPE_ALLOCATED的值，这意味着它还没有被使用，因此可以跳过它——这些页面在日志序列号映射中被涂成黑色。

Tip

如果您想探索页面标题中可用的信息，源代码中的文件storage/innobase/include/fil0types.h ( https://github.com/mysql/mysql-server/blob/8.0/storage/innobase/include/fil0types.h )和 MySQL 内部手册中 fil 标题的描述( https://dev.mysql.com/doc/internals/en/innodb-fil-header.html )是很好的起点。

您可以通过使用--help参数调用程序来获得使用程序的帮助。唯一必需的参数是要分析的表空间文件的路径。除非您已经将innodb_page_size选项设置为 16384 字节以外的值，否则您只需要可选参数的默认值，除非您想要更改生成的地图的尺寸和大小。

Caution

不要在生产系统上使用该程序！程序中有最少的错误检查以使它尽可能的简单，并且它本质上是实验性的。

您现在可以生成测试表了。清单 25-2 展示了如何创建table_autoinc表。这是带有自动递增主键的表。

mysql-sql> CREATE SCHEMA chapter_25;
Query OK, 1 row affected (0.0020 sec)

mysql-sql> CREATE TABLE chapter_25.table_autoinc (
             id bigint unsigned NOT NULL auto_increment,
             val varchar(36),
             PRIMARY KEY (id)
           );
Query OK, 0 rows affected (0.3382 sec)

mysql-sql> \py
Switching to Python mode...

mysql-py> for i in range(40):
              session.start_transaction()
              for j in range(5000):
                  session.run_sql("INSERT INTO chapter_25.table_autoinc (val) VALUES (UUID())")
              session.commit()

Query OK, 0 rows affected (0.1551 sec)

Listing 25-2Populating a table with an auto-incrementing primary key

该表有一个bigint主键和一个用 UUIDs 填充的varchar(36)，以创建一些随机数据。MySQL Shell 的 Python 语言模式用于插入数据。8.0.17 及更高版本中提供了session.run_sql()方法。最后，您可以执行listing_25_1.py脚本来生成可伸缩矢量图形(SVG)格式的表空间年龄图:

shell> python listing_25_1.py <path to datadir>\chapter_25\table_autoinc.ibd > table_autoinc.svg
Total # Pages ...: 880

程序的输出显示，表空间中有 880 个页面，文件末尾可能还有一些未使用的页面。

图 25-1 显示了table_autoinc表的日志序列号年龄图。

图 25-1

按主键顺序插入时每页的 LSN 年龄

在图中，左上角代表表空间的第一页。当您从左到右、从上到下浏览该图时，页面越来越深入到表空间文件中，右下方表示最后的页面。该图显示，除了第一页之外，这些页的年龄模式遵循与该图底部的 LSN 年龄标度相同的模式。这意味着随着您在表空间中的前进，页的年龄会变得越来越年轻。前几页是例外，例如，它们包括表空间头。

这个模式显示了数据被顺序地插入到表空间中，使得表空间尽可能紧凑。这也使得如果一个查询从逻辑上连续的几个页中读取数据，那么它们在表空间文件中也是物理上连续的。

如果你随机插入，会是什么样子呢？随机顺序插入的一个常见示例是将 UUID 作为主键，但是为了确保两个表的行大小相同，使用了一个随机整数。清单 25-3 展示了如何填充table_random表。

mysql-py> \sql
Switching to SQL mode... Commands end with ;

mysql-sql> CREATE TABLE chapter_25.table_random (
             id bigint unsigned NOT NULL,
             val varchar(36),
             PRIMARY KEY (id)
           );
Query OK, 0 rows affected (0.0903 sec)

mysql-sql> \py
Switching to Python mode...

mysql-py> import random
mysql-py> import math
mysql-py> maxint = math.pow(2, 64) - 1
mysql-py> random.seed(42)

mysql-py> for i in range(40):
              session.start_transaction()
              for j in range(5000):
                  session.run_sql("INSERT INTO chapter_25.table_random VALUE ({0}, UUID())".format(random.randint(0, maxint)))
              session.commit()

Query OK, 0 rows affected (0.0185 sec)

Listing 25-3Populating a table with a random primary key

Python random模块用于生成 64 位随机无符号整数。种子是显式设置的，因为已知(通过实验)种子为 42 会在一行中生成 200，000 个不同的数字，因此不会出现重复的键错误。当表被填充后，执行listing_25_1.py脚本:

shell> python listing_25_1.py <path to datadir>\chapter_25\table_random.ibd > table_random.svg
Total # Pages ...: 1345

listing_25_1.py脚本的输出显示这个表空间中有 1345 个页面。生成的年龄图如图 25-2 所示。

图 25-2

以随机顺序插入时每页的 LSN 年龄

这一次，日志序列号年龄模式完全不同。除了未使用的页面之外，所有页面的年龄颜色对应于最新日志序列号的颜色。这意味着所有包含数据的页面都是在同一时间最后更新的，或者说，它们都是在大容量装载结束之前写入的。包含数据的页数是 1345，而包含自动递增主键的表中使用了 880 页。这就增加了超过 50%的页面。

以随机顺序插入数据会导致相同数量的数据有更多的页面，这是因为 InnoDB 在插入数据时会填满页面。当按顺序主键顺序插入数据时，这意味着下一行将总是在前一行之后，因此当行按主键顺序排序时，这种方法很有效。如图 25-3 所示。

图 25-3

按顺序插入时添加新行的示例

该图显示了插入的两行新行。id = 1005 的行刚好可以放入第 N 页，所以当插入 id = 1006 的行时，它会被插入到下一页。在这个场景中，一切都很好，很紧凑。

当行以随机顺序到达时，有时需要将行插入到已经满得没有空间容纳新行的页面中。在这种情况下，InnoDB 将现有页面一分为二，这两个页面中的每一个页面都有原始页面的一半数据，因此有空间容纳新行。如图 25-4 所示。

图 25-4

随机插入导致的页面分割示例

在这种情况下，id = 3500 的行被插入，但是在逻辑上它所属的页面 N 中没有更多空间。因此，第 N 页被分成第 N 页和第 N+1 页，每页大约有一半的数据。

页面分割有两个直接后果。首先，以前占用一个页面的数据现在使用了两个页面，这就是为什么随机插入最终会多占用 50%的页面，这也意味着相同的数据在缓冲池中需要更多的空间。额外页面的一个显著副作用是，B 树索引最终会有更多的叶页面和树中潜在的更多层，并且考虑到树中的每一层都意味着访问页面时的额外寻道，这会导致额外的 I/O。

其次，以前一起读入内存的行现在位于磁盘上不同位置的两个页面中。当 InnoDB 增加表空间文件的大小时，它是通过在页面大小为 16 KiB 或更小时分配一个 1 MiB 的新区来实现的。这有助于提高磁盘 I/O 的顺序性(在某种程度上，新的扩展区可以在磁盘上获得连续的扇区)。发生的页拆分越多，页就越多，不仅在一个扩展区内，而且在多个扩展区之间，从而导致更多的随机磁盘 I/O。当由于页拆分而创建新页时，它很可能位于磁盘上完全不同的部分，因此在读取页时，随机 I/O 的数量会增加。如图 25-5 所示。

图 25-5

磁盘上页面位置的示例

在图中描绘了三个范围。为简单起见，每个区段中只显示了五个页面(默认页面大小为 16 KiB，每个区段有 64 个页面)。属于页面分割一部分的页面会突出显示。第 11 页是在最后一页是第 13 页时被拆分的，因此第 11 页和第 12 页仍然相对靠近。然而，当创建了几个额外的页面时，第 15 页被拆分，这意味着第 16 页在下一个区段中结束。

更深的 B 树、占用缓冲池空间的更多页面和更多随机 I/O 的组合意味着以随机主键顺序插入行的表的性能不如以主键顺序插入数据的表。性能差异不仅适用于插入数据；它也适用于数据的后续使用。因此，按主键顺序插入数据对于优化性能非常重要。接下来将讨论如何实现这一点。

按主键顺序插入

正如前面的讨论所示，按主键顺序插入数据有很大的优势。实现这一点最简单的方法是通过使用一个无符号整数并声明自动递增的列来自动生成主键值。或者，您需要确保数据是按照主键顺序插入的。本节将调查这两种情况。

自动递增主键

确保数据按主键顺序插入的最简单方法是允许 MySQL 通过使用自动递增的主键来自己赋值。您可以通过在创建表时为主键列指定auto_increment属性来做到这一点。也可以结合多列主键使用自动递增列；在这种情况下，自动递增列必须是索引中的第一列。

清单 25-4 展示了一个创建两个表的例子，这两个表使用一个自动增加的列以主键顺序插入数据。

mysql> \sql
Switching to SQL mode... Commands end with ;

mysql> DROP SCHEMA IF EXISTS chapter_25;
Query OK, 0 rows affected, 1 warning (0.0456 sec)

mysql> CREATE SCHEMA chapter_25;
Query OK, 1 row affected (0.1122 sec)

mysql> CREATE TABLE chapter_25.t1 (
         id int unsigned NOT NULL auto_increment,
         val varchar(10),
         PRIMARY KEY (id)
       );
Query OK, 0 rows affected (0.4018 sec)

mysql> CREATE TABLE chapter_25.t2 (
         id int unsigned NOT NULL auto_increment,
         CreatedDate datetime NOT NULL
                              DEFAULT CURRENT_TIMESTAMP(),
         val varchar(10),
         PRIMARY KEY (id, CreatedDate)
       );
Query OK, 0 rows affected (0.3422 sec)

Listing 25-4Creating tables with an auto-increment primary key

t1表只有一个主键列，值是自动递增的。使用无符号整数而不是有符号整数的原因是自动增量值总是大于 0，因此使用无符号整数在用尽可用值之前允许两倍的值。这些示例使用了一个 4 字节的整数，如果使用所有的值，它允许的行数略少于 43 亿。如果这还不够，您可以将该列声明为bigint unsigned，它使用 8 个字节，允许 1.8E19 行。

t2表向主键添加了一个datetime列，例如，如果您希望在创建行时进行分区，这个列会很有用。自动递增的id列仍然确保用唯一的主键创建行，并且因为id列是主键中的第一列，所以即使主键中的后续列本质上是随机的，行仍然按主键顺序插入。

当您使用自动递增主键时，您可以使用sys模式中的schema_auto_increment_columns视图来检查自动递增值的使用，并监控是否有任何表接近耗尽它们的值。清单 25-5 显示了sakila.payment表的输出。

mysql> SELECT *
         FROM sys.schema_auto_increment_columns
        WHERE table_schema = 'sakila'
              AND table_name = 'payment'\G
*************************** 1\. row ***************************
        table_schema: sakila
          table_name: payment
         column_name: payment_id
           data_type: smallint
         column_type: smallint(5) unsigned
           is_signed: 0
         is_unsigned: 1
           max_value: 65535
      auto_increment: 16049
auto_increment_ratio: 0.2449
1 row in set (0.0024 sec)

Listing 25-5Using the sys.schema_auto_increment_columns view

您可以从输出中看到，该表使用了一个用于自动增量值的smallint unsigned列，其最大值为65535，该列被命名为payment_id。下一个自动增量值是 16049，因此使用了可用值的 24.49%。

如果从外部源插入数据，可能已经为主键列分配了值(即使使用自动递增主键)。让我们看看在这种情况下你能做什么。

插入现有数据

无论您需要插入由某个进程生成的数据、恢复备份，还是使用不同的存储引擎转换表，最好在插入之前确保它处于主键顺序。如果您生成数据或者数据已经存在，那么您可以考虑在插入数据之前对其进行排序。或者，在导入完成后，使用OPTIMIZE TABLE语句重建表。

重建chapter_25.t1表的一个例子是

mysql> OPTIMIZE TABLE chapter_25.t1\G
*************************** 1\. row ***************************
   Table: chapter_25.t1
      Op: optimize
Msg_type: note
Msg_text: Table does not support optimize, doing recreate + analyze instead
*************************** 2\. row ***************************
   Table: chapter_25.t1
      Op: optimize
Msg_type: status
Msg_text: OK
2 rows in set (0.6265 sec)

对于大型表，重建可能需要大量时间，但是除了在开始和结束时需要锁以确保一致性的短暂时间之外，该过程是在线的。

如果您使用mysqldump程序创建备份，您可以添加--order-by-primary选项，这使得mysqldump添加一个包含主键中的列的ORDER BY子句(mysqlpump没有等价的选项)。如果备份是使用存储引擎(使用所谓的堆组织数据，如 MyISAM)创建的表，目的是将其恢复到 InnoDB 表(使用数据的索引组织)，这将非常有用。

Tip

虽然在使用不带ORDER BY子句的查询时，通常不应该依赖于返回行的顺序，但 InnoDB 的索引组织行意味着，即使省略了ORDER BY子句，全表扫描通常(但不保证)会按主键顺序返回行。一个值得注意的例外是，当表包含一个覆盖所有列的二级索引，并且优化器选择将该索引用于查询时。

如果将数据从一个表复制到另一个表，也可以使用相同的原则。清单 25-6 展示了一个将world.city表中的行复制到world.city_new表中的例子。

mysql> CREATE TABLE world.city_new
         LIKE world.city;
Query OK, 0 rows affected (0.8607 sec)

mysql> INSERT INTO world.city_new
       SELECT *
         FROM world.city
        ORDER BY ID;
Query OK, 4079 rows affected (2.0879 sec)

Records: 4079  Duplicates: 0  Warnings: 0

Listing 25-6Ordering data by the primary key when copying it

作为最后一种情况，考虑当您有一个 UUID 作为主键。

UUID 主键

例如，如果您的主键仅限于一个 UUID，因为您无法更改应用来支持自动递增主键，那么您可以通过交换 UUID 组件并将 uuid 存储在二进制列中来提高性能。

一个 UUID (MySQL 使用 UUID 版本 1)由一个时间戳和一个序列号(如果时间戳向后移动，例如在夏令时更改期间，以保证唯一性)以及 MAC 地址组成。

Caution

在某些情况下，泄露 MAC 地址可能会被认为是一个安全问题，因为它可以用来识别计算机和潜在的用户。

时间戳是一个 60 位值，使用 UTC，从 1582 年 10 月 15 日午夜(公历开始使用时)开始，时间间隔为 100 纳秒。 ² 它被分成三部分，最低有效部分在前，最高有效部分在后。(对于 UUID 版本，时间戳的高位字段也包括四位。UUID 的组成也如图 25-6 所示。)

图 25-6

UUID 版本 1 的五个部分

时间戳的较低部分代表 100 纳秒或不到 430 秒的最多 4，294，967，295 (0xffffffff)个间隔。这意味着，从排序的角度来看，时间戳的低位部分每隔 7 分钟略少于 10 秒滚动一次，使 UUID 重新开始。这就是为什么普通 UUIDs 不能很好地用于索引组织的数据，因为这意味着插入很大程度上是在主键树中的随机位置。

MySQL 8 包含两个新函数来操作 UUIDs，使它们更适合作为 InnoDB 中的主键:UUID_TO_BIN()和BIN_TO_UUID()。这些函数分别将 UUID 从十六进制表示形式转换为二进制表示形式，然后再转换回来。它们接受相同的两个参数:要转换的 UUID 值和是否交换时间戳的高低部分。清单 25-7 展示了一个使用函数插入和检索数据的例子。

mysql> CREATE TABLE chapter_25.t3 (
         id binary(16) NOT NULL,
         val varchar(10),
         PRIMARY KEY (id)
       );
Query OK, 0 rows affected (0.4413 sec)

mysql> INSERT INTO chapter_25.t3
       VALUES (UUID_TO_BIN(
                 '14614d6e-b5a8-11e9-ae6e-080027b7c106',
                 TRUE
              ), 'abc');
Query OK, 1 row affected (0.2166 sec)

mysql> SELECT BIN_TO_UUID(id, TRUE) AS id, val
         FROM chapter_25.t3\G
*************************** 1\. row ***************************
 id: 14614d6e-b5a8-11e9-ae6e-080027b7c106
val: abc
1 row in set (0.0004 sec)

Listing 25-7Using the UUID_TO_BIN() and BIN_TO_UUID() functions

这种方法有两个优点。因为 UUID 交换了低时间和高时间分量，所以它变得单调递增，这使得它更适合于按索引组织的行。二进制存储意味着 UUID 只需要 16 个字节的存储，而不是十六进制版本的 36 个字节，用破折号来分隔 UUID 的各个部分。请记住，由于数据是按主键组织的，主键被添加到辅助索引中，因此可以从索引转到行，因此存储主键所需的字节越少，辅助索引就越小。

InnoDB 缓冲池和二级索引

对于批量数据加载的性能来说，最重要的一个因素是 InnoDB 缓冲池的大小。本节讨论为什么缓冲池对于大容量数据装载很重要。

当您向表中插入数据时，InnoDB 需要能够将数据存储在缓冲池中，直到数据被写入表空间文件。缓冲池中存储的数据越多，InnoDB 将脏页刷新到表空间文件的效率就越高。然而，还有第二个原因是维护二级索引。

在插入数据时，需要维护辅助索引，但是辅助索引的排序顺序与主键不同，所以在插入数据时，它们会不断地重新排列。只要可以在内存中维护索引，插入率就可以保持很高，但是当索引不再适合缓冲池时，维护索引的成本会突然变得更高，插入率会显著下降。图 25-7 说明了性能如何依赖于处理二级索引的缓冲池的可用性。

图 25-7

与缓冲池中的索引大小相比的插入性能

该图显示了插入速率在一段时间内大致保持不变，而在这段时间内，越来越多的缓冲池用于辅助索引。当缓冲池中无法存储更多的索引时，插入速率会突然下降。在极端情况下，将数据加载到一个只有一个二级索引的表中，该索引包含整行，而没有其他内容，当二级索引使用了将近一半的缓冲池(剩余的用于主键)时，就会出现这种情况。

您可以使用information_schema.INNODB_BUFFER_PAGE表来确定一个索引在缓冲池中使用了多少空间。例如，通过world.city表上的CountryCode索引来查找缓冲池中使用的内存量

mysql> SELECT COUNT(*) AS NumPages,
              IFNULL(SUM(DATA_SIZE), 0) AS DataSize,
              IFNULL(SUM(IF(COMPRESSED_SIZE = 0,
                            @@global.innodb_page_size,
                            COMPRESSED_SIZE
                           )
                        ),
                     0
                    ) AS CompressedSize
         FROM information_schema.INNODB_BUFFER_PAGE
        WHERE TABLE_NAME = '`world`.`city`'
              AND INDEX_NAME = 'CountryCode';
+----------+----------+----------------+
| NumPages | DataSize | CompressedSize |
+----------+----------+----------------+
|        3 |    27148 |          49152 |
+----------+----------+----------------+
1 row in set (0.1027 sec)

结果将取决于你使用了多少索引，所以一般来说你的结果会有所不同。这个查询最好用在测试系统上，因为查询INNODB_BUFFER_PAGE表会有很大的开销。

Caution

在您的生产系统上查询INNODB_BUFFER_PAGE表时要小心，因为开销可能很大，尤其是当您有一个包含许多表和索引的大型缓冲池时。

当辅助索引无法放入缓冲池时，有三种策略可以避免性能下降，如下所示:

• 增加缓冲池的大小。

• 插入数据时删除辅助索引。

• 给桌子分区。

在进行大容量装载时增加缓冲池大小是最明显的策略，但也是最不可能有用的策略。它主要用于将数据插入到已经有大量数据的表中，并且您知道在数据加载期间，您可以将其他进程需要的一些内存用于缓冲池。在这种情况下，支持动态调整缓冲池的大小非常有用。例如，将缓冲池大小设置为 256 MiB

mysql> SET GLOBAL innodb_buffer_pool_size = 256 * 1024 * 1024;
Query OK, 0 rows affected (0.0003 sec)

数据加载完成后，可以将缓冲池大小设置回通常的值(如果使用默认值，则为 134217728)。

如果要插入到一个空表中，一个非常有用的策略是在加载数据之前删除所有的辅助索引(可能为数据验证留下唯一的索引),然后再添加索引。在大多数情况下，这比在加载数据时试图维护索引更有效，如果您使用它来创建备份，这也是mysqlpump实用程序所做的。

最后一个策略是对表进行分区。这很有帮助，因为索引是分区的本地索引(这就是分区键必须是所有唯一索引的一部分的原因)，所以如果您按分区顺序插入数据，InnoDB 只需维护当前分区中数据的索引。这使得每个索引更小，所以它们更容易放入缓冲池。

配置

您可以通过配置执行加载的会话来影响加载性能。这包括考虑关闭约束检查、如何生成自动增量 id 等等。

表 25-1 总结了除缓冲池大小之外与批量数据性能相关的最重要的配置选项。范围是该选项是可以在会话级别更改，还是仅在全局可用。

表 25-1

影响数据加载性能的配置选项

|

选项名称

|

范围

|

描述

|
| — | — | — |
| foreign_key_checks | 会议 | 指定是否检查新行是否违反外键。禁用此选项可以提高具有外键的表的性能。 |
| unique_checks | 会议 | 指定是否检查新行是否违反唯一约束。禁用此选项可以提高具有唯一索引的表的性能。 |
| innodb_autoinc_lock_mode | 全球的 | 指定 InnoDB 如何确定下一个自动增量值。将该选项设置为 MySQL 8 中的默认值——需要binlog_format = ROW)可以获得最佳性能，但代价是可能会出现不连续的自动增量值。需要重启 MySQL。 |
| innodb_flush_log_at_trx_commit | 全球的 | 确定 InnoDB 刷新对数据文件所做更改的频率。如果使用许多小事务导入数据，将此选项设置为 0 或 2 可以提高性能。 |
| sql_log_bin | 会议 | 当设置为 0 或OFF时，禁用二进制日志。这将大大减少写入的数据量。 |
| transaction_isolation | 会议 | 设置事务隔离级别。如果您没有读取 MySQL 中的现有数据，可以考虑将隔离级别设置为READ UNCOMMITTED。 |

所有选项都有副作用，所以请仔细考虑更改设置是否适合您。例如，如果您将数据从现有实例导入到新实例，并且您知道外键和唯一键约束没有问题，那么您可以为导入数据的会话禁用foreign_key_checks和unique_checks选项。另一方面，如果您从一个不确定数据完整性的源进行导入，最好启用约束检查以确保数据质量，即使这意味着较慢的加载性能。

对于innodb_flush_log_at_trx_commit选项，您需要考虑丢失最后一秒左右已提交事务的风险是否可以接受。如果您的数据加载进程是实例上唯一的事务，并且很容易重做导入，那么您可以将innodb_flush_log_at_trx_commit设置为 0 或 2，以减少刷新次数。这种改变对小额事务非常有用。如果导入每秒提交的次数少于一次，那么更改带来的好处很少。如果您更改了innodb_flush_log_at_trx_commit，那么在导入后记得将该值设置回 1。

对于二进制日志，禁用写入导入的数据很有用，因为这大大减少了必须写入磁盘的数据更改量。如果二进制日志与重做日志和数据文件位于同一个磁盘上，这将特别有用。如果您不能修改导入过程来禁用sql_log_bin，您可以考虑使用skip-log-bin选项重新启动 MySQL 来完全禁用二进制日志，但是请注意，这也会影响系统上的所有其他事务。如果在导入过程中禁用了二进制日志记录，则在导入后立即创建完整备份会很有用，这样您就可以再次使用二进制日志进行时间点恢复。

Tip

如果您使用复制，请考虑在禁用sql_log_bin的情况下，在拓扑中的每个实例上单独进行数据导入。不过请注意，只有当 MySQL 不生成自动递增主键时，它才会起作用，并且只有当您需要导入大量数据时，才值得增加复杂性。对于 MySQL 8.0.17 中的初始加载，您可以只填充复制的源，并使用克隆插件 ³ 来创建副本。

您还可以通过选择导入数据的语句以及如何使用事务来提高加载性能。

事务和加载方法

一个事务表示一组更改，InnoDB 在提交事务之前不会完全应用这些更改。每次提交都涉及到将数据写入重做日志，并包括其他开销。如果您有非常小的事务(比如一次插入一行),这种开销会显著影响加载性能。

最佳事务规模没有金科玉律。对于较小的行大小，通常几千行就足够了，对于较大的行大小，选择较少的行。最终，您将需要在您的系统上进行测试，并使用您的数据来确定最佳的事务大小。

对于加载方法，主要有两种选择:INSERT语句或LOAD DATA [LOCAL] INFILE语句。总的来说，LOAD DATA比INSERT语句执行得更好，因为解析更少。对于INSERT语句，使用扩展的 insert 语法有一个优点，即使用一个语句而不是多个单行语句插入多行。

Tip

当使用mysqlpump进行备份时，可以将--extended-insert选项设置为每个INSERT语句包含的行数，默认值为 250。对于mysqldump，--extended-insert选项作为开关工作。启用时(默认)，mysqldump将自动决定每条语句的行数。

使用LOAD DATA加载数据的一个优点是 MySQL Shell 可以自动并行加载。

MySQL Shell 并行加载数据

将数据加载到 MySQL 时可能会遇到的一个问题是，单个线程无法将 InnoDB 推到它所能承受的极限。如果将数据分成几批，并使用多线程加载数据，可以提高整体加载速率。自动完成这项工作的一个选项是使用 MySQL Shell 8.0.17 和更高版本的并行数据加载特性。

通过 Python 模式下的util.import_table()实用程序和 JavaScript 模式下的util.importTable()方法可以获得并行加载特性。这个讨论将假设您正在使用 Python 模式。第一个参数是文件名，第二个(可选)参数是带有可选参数的字典。您可以使用util.help()方法获得import_table()实用程序的帮助文本，比如

mysql-py> util.help('import_table')

帮助文本包括所有设置的详细描述，这些设置可以通过第二个参数中指定的字典给出。

MySQL Shell 禁用重复键和外键检查，并将执行导入的连接的事务隔离级别设置为READ UNCOMMITTED，以尽可能减少导入过程中的开销。

默认情况下，将数据插入到当前模式的一个表中，该表与不带扩展名的文件同名。例如，如果文件名为t_load.csv，默认的表名为t_load。清单 25-8 显示了一个将文件D:\MySQL\Files\t_load.csv加载到表chapter_25.t_load中的简单示例。t_load.csv文件可以从这本书的 GitHub 库中以t_load.csv.zip的名称获得。

mysql> \sql
Switching to SQL mode... Commands end with ;

mysql-sql> CREATE SCHEMA IF NOT EXISTS chapter_25;
Query OK, 1 row affected, 1 warning (0.0490 sec)

mysql-sql> DROP TABLE IF EXISTS chapter_25.t_load;
Query OK, 0 rows affected (0.3075 sec)

mysql-sql> CREATE TABLE chapter_25.t_load (
             id int unsigned NOT NULL auto_increment,
             val varchar(40) NOT NULL,
             PRIMARY KEY (id),
             INDEX (val)
           );
Query OK, 0 rows affected (0.3576 sec)

mysql> SET GLOBAL local_infile = ON;
Query OK, 0 rows affected (0.0002 sec)

mysql> \py
Switching to Python mode...

mysql-py> \use chapter_25
Default schema set to `chapter_25`.

mysql-py> util.import_table('D:/MySQL/Files/t_load.csv')
Importing from file 'D:/MySQL/Files/t_load.csv' to table `chapter_25`.`t_load` in MySQL Server at localhost:3306 using 2 threads
[Worker000] chapter_25.t_load: Records: 721916  Deleted: 0  Skipped: 0  Warnings: 0
[Worker001] chapter_25.t_load: Records: 1043084  Deleted: 0  Skipped: 0  Warnings: 0
100% (85.37 MB / 85.37 MB), 446.55 KB/s
File 'D:/MySQL/Files/t_load.csv' (85.37 MB) was imported in 1 min 52.1678 sec at 761.13 KB/s
Total rows affected in chapter_25.t_load: Records: 1765000  Deleted: 0  Skipped: 0  Warnings: 0

Listing 25-8Using the util.import_table() utility with default settings

创建chapter_25模式时的警告取决于您之前是否创建了该模式。请注意，您必须启用local_infile选项，该实用程序才能工作。

该示例最有趣的部分是导入的执行。当您没有指定任何内容时，MySQL Shell 会将文件分割成 50 MB 的块，并使用多达八个线程。在本例中，文件大小为 85.37 MB (MySQL Shell 使用文件大小度量标准–85.37 MB 与 81.42 MiB 相同)，因此它提供了两个块，其中第一个是 50 MB，第二个是 35.37 MB。这不是一个可怕的好分布。

Tip

在调用util.import_table()实用程序之前，您必须在服务器端启用local_infile。

你可以选择做的是告诉 MySQL Shell 以多大的尺寸分割。最佳情况是每个线程最终处理相同数量的数据。例如，如果您想要划分 85.37 MB 的数据，请将块大小设置为略大于一半，例如 43 MB。如果为大小指定了一个小数值，则向下舍入。还有几个其他选项可以设置，清单 25-9 显示了设置其中一些选项的示例。

mysql-py> \sql TRUNCATE TABLE chapter_25.t_load
Query OK, 0 rows affected (1.1294 sec)

mysql-py> settings = {
              'schema': 'chapter_25',
              'table': 't_load',
              'columns': ['id', 'val'],
              'threads': 4,
              'bytesPerChunk': '21500k',
              'fieldsTerminatedBy': '\t',
              'fieldsOptionallyEnclosed': False,
              'linesTerminatedBy': '\n'
          }

mysql-py> util.import_table('D:/MySQL/Files/t_load.csv', settings)
Importing from file 'D:/MySQL/Files/t_load.csv' to table `chapter_25`.`t_load` in MySQL Server at localhost:3306 using 4 threads
[Worker001] chapter_25.t_load: Records: 425996  Deleted: 0  Skipped: 0  Warnings: 0
[Worker002] chapter_25.t_load: Records: 440855  Deleted: 0  Skipped: 0  Warnings: 0
[Worker000] chapter_25.t_load: Records: 447917  Deleted: 0  Skipped: 0  Warnings: 0
[Worker003] chapter_25.t_load: Records: 450232  Deleted: 0  Skipped: 0  Warnings: 0
100% (85.37 MB / 85.37 MB), 279.87 KB/s
File 'D:/MySQL/Files/t_load.csv' (85.37 MB) was imported in 2 min 2.6656 sec at 695.99 KB/s
Total rows affected in chapter_25.t_load: Records: 1765000  Deleted: 0  Skipped: 0  Warnings: 0

Listing 25-9Using util.import_table() with several custom settings

在这种情况下，目标模式、表和列是显式指定的，文件被分成四个大致相等的块，线程数设置为四。CSV 文件的格式也包括在设置中(指定的值是默认值)。

根据硬件、数据和运行的其他查询，最佳线程数量会有很大的不同。您需要进行试验，为您的系统找到最佳设置。

摘要

本章讨论了决定 DDL 语句和大容量数据装载性能的因素。第一个主题是关于ALTER TABLE和OPTIMIZE TABLE的模式变化。当您更改模式时，支持三种不同的算法。性能最好的算法是INSTANT算法，该算法可用于在行尾添加列和一些元数据更改。第二好的算法是INPLACE，它在大多数情况下会修改现有表空间文件中的数据。最后，也是通常最昂贵的算法是COPY。

在无法使用INSTANT算法的情况下，将会有大量的 I/O，因此磁盘性能很重要，需要磁盘 I/O 的其他工作越少越好。它也有助于锁定表，因此 MySQL 不需要跟踪数据更改并在模式更改结束时应用它们。

对于插入数据，我们讨论了按主键顺序插入的重要性。如果插入顺序是随机的，则会导致更大的表、聚集索引的更深的 B 树索引、更多的磁盘寻道和更多的随机 I/O。以主键顺序插入数据的最简单方法是使用自动递增主键，并让 MySQL 确定下一个值。对于 UUID，MySQL 8 添加了UUID_TO_BIN()和BIN_TO_UUID()函数，允许您将 UUID 所需的存储减少到 16 个字节，并交换时间戳的低阶和高阶部分，以使 uuid 单调增加。

当您插入数据时，插入速率突然变慢的一个典型原因是辅助索引不再适合缓冲池。如果插入到一个空表中，在导入过程中删除索引是一个优势。分区也有帮助，因为它将索引分成每个分区一部分，所以一次只需要索引的一部分。

在某些情况下，您可以禁用约束检查，减少重做日志的刷新，禁用二进制日志记录，并将事务隔离降低到READ UNCOMMITTED。这些配置更改都将有助于减少开销；但是，所有这些都有副作用，所以您必须仔细考虑这些更改是否能被您的系统接受。您还可以通过调整事务大小来平衡提交开销的减少和处理大型事务的开销，从而影响性能。

对于批量插入，您有两个加载数据的选项。可以使用常规的INSERT语句，也可以使用LOAD DATA语句。后者通常是首选方法。它还允许您使用 MySQL Shell 8.0.17 和更高版本的并行表导入功能。

在下一章中，您将了解如何提高复制的性能。

Footnotes [1](#Fn1_source)

https://github.com/jeremycole/innodb_ruby

2

www.ietf.org/rfc/rfc4122.txt

3

https://dev.mysql.com/doc/refman/en/clone-plugin.html