MySQL8 中文参考（六十九）

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

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

17.6.1.5 将表从MyISAM转换为InnoDB

如果您有想要转换为更可靠和可扩展的InnoDB的MyISAM表，请在转换之前查看以下准则和提示。

注意

在之前的 MySQL 版本中创建的分区MyISAM表与 MySQL 8.0 不兼容。这些表必须在升级之前进行准备，可以通过删除分区或将其转换为InnoDB来完成。有关更多信息，请参阅 Section 26.6.2, “与存储引擎相关的分区限制”。

• 调整MyISAM和InnoDB的内存使用

• 处理过长或过短的事务

• 处理死锁

• 存储布局

• 转换现有表

• 克隆表的结构

• 数据转移

• 存储需求

• 定义主键

• 应用性能考虑

• 了解与InnoDB表相关的文件

调整MyISAM和InnoDB的内存使用

当你从MyISAM表过渡时，降低key_buffer_size配置选项的值，释放不再需要用于缓存结果的内存。增加innodb_buffer_pool_size配置选项的值，它扮演着为InnoDB表分配缓存内存的类似角色。InnoDB缓冲池同时缓存表数据和索引数据，加快查询的查找速度，并将查询结果保留在内存中以便重复使用。有关缓冲池大小配置的指导，请参见第 10.12.3.1 节，“MySQL 如何使用内存”。

处理过长或过短的事务

因为MyISAM表不支持事务，你可能没有太关注autocommit配置选项以及COMMIT和ROLLBACK语句。这些关键词对于允许多个会话同时读写InnoDB表非常重要，在写入密集型工作负载中提供了可观的可扩展性优势。

当事务打开时，系统会保留事务开始时看到的数据快照，如果系统在一个杂乱的事务持续运行时插入、更新和删除数百万行数据，这可能会导致很大的开销。因此，要注意避免运行时间过长的事务：

• 如果你正在使用一个mysql会话进行交互式实验，完成后始终要COMMIT（以完成更改）或ROLLBACK（以撤消更改）。关闭交互式会话而不是长时间保持打开，以避免意外保持事务长时间打开。

• 确保应用程序中的任何错误处理程序也会ROLLBACK未完成的更改或COMMIT已完成的更改。

• ROLLBACK是一个相对昂贵的操作，因为INSERT、UPDATE和DELETE操作在COMMIT之前被写入InnoDB表，预期大多数更改都会成功提交，而回滚是罕见的。在处理大量数据时，避免对大量行进行更改，然后回滚这些更改。

• 当使用一系列INSERT语句加载大量数据时，定期COMMIT结果，以避免持续数小时的事务。在数据仓库的典型加载操作中，如果出现问题，你会截断表（使用TRUNCATE TABLE），然后从头开始，而不是执行ROLLBACK。

上述提示可以节省在过长事务期间可能浪费的内存和磁盘空间。当事务比应该更短时，问题在于过多的 I/O。每次COMMIT时，MySQL 都会确保每个更改都安全记录到磁盘上，这涉及一些 I/O。

• 对于大多数InnoDB表操作，应该使用设置autocommit=0。从效率的角度来看，这样可以避免在连续发出大量INSERT、UPDATE或DELETE语句时产生不必要的 I/O。从安全性的角度来看，这允许你发出一个ROLLBACK语句，以恢复在mysql命令行上犯错或在应用程序的异常处理程序中出现错误的数据。

• 在运行一系列用于生成报告或分析统计数据的查询时，autocommit=1适用于InnoDB表。在这种情况下，与COMMIT或ROLLBACK相关的 I/O 惩罚不存在，而InnoDB可以自动优化只读工作负载。

• 如果您进行一系列相关更改，请在最后一次使用单个COMMIT完成所有更改。例如，如果您将相关信息插入多个表中，请在进行所有更改后执行单个COMMIT。或者如果您运行许多连续的INSERT语句，请在所有数据加载后执行单个COMMIT；如果您正在执行数百万次INSERT语句，也许可以通过在每一万或十万条记录后发出一个COMMIT来拆分巨大的事务，以避免事务过大。

• 请记住，即使是SELECT语句也会开启一个事务，因此在交互式mysql会话中运行一些报告或调试查询后，要么执行一个COMMIT，要么关闭mysql会话。

有关信息，请参阅第 17.7.2.2 节，“自动提交、提交和回滚”。

处理死锁

您可能会在 MySQL 错误日志中看到指向“死锁”的警告消息，或者SHOW ENGINE INNODB STATUS的输出。死锁对于InnoDB表来说并不是一个严重的问题，通常不需要任何纠正措施。当两个事务开始修改多个表，以不同顺序访问这些表时，它们可能会达到一个状态，其中每个事务都在等待另一个事务，而两者都无法继续。当启用死锁检测（默认情况下），MySQL 会立即检测到这种情况并取消（回滚）“较小”的事务，从而允许另一个事务继续。如果使用innodb_deadlock_detect配置选项禁用死锁检测，则InnoDB依赖于innodb_lock_wait_timeout设置，在死锁发生时回滚事务。

无论哪种方式，您的应用程序都需要错误处理逻辑来重新启动因死锁而被强制取消的事务。当重新发出与之前相同的 SQL 语句时，原始的时间问题不再存在。要么另一个事务已经完成，您的事务可以继续，要么另一个事务仍在进行中，您的事务将等待直到其完成。

如果死锁警告频繁发生，您可能需要审查应用程序代码以以一致的方式重新排序 SQL 操作，或缩短事务。您可以启用innodb_print_all_deadlocks选项进行测试，以在 MySQL 错误日志中看到所有死锁警告，而不仅仅是SHOW ENGINE INNODB STATUS输出中的最后一个警告。

更多信息，请参见 Section 17.7.5, “Deadlocks in InnoDB”。

存储布局

要从InnoDB表中获得最佳性能，您可以调整与存储布局相关的多个参数。

当您转换大型、频繁访问且保存重要数据的MyISAM表时，请调查并考虑innodb_file_per_table和innodb_page_size变量，以及CREATE TABLE语句的ROW_FORMAT和KEY_BLOCK_SIZE子句。

在您的初始实验中，最重要的设置是innodb_file_per_table。当启用此设置时（默认情况下），新的InnoDB表会隐式地创建在 file-per-table 表空间中。与InnoDB系统表空间相比，file-per-table 表空间允许在表被截断或删除时由操作系统回收磁盘空间。File-per-table 表空间还支持 DYNAMIC 和 COMPRESSED 行格式以及相关功能，如表压缩、长变长列的高效离页存储和大索引前缀。更多信息，请参见 Section 17.6.3.2, “File-Per-Table Tablespaces”。

您还可以将InnoDB表存储在共享的通用表空间中，支持多个表和所有行格式。更多信息，请参见 Section 17.6.3.3, “General Tablespaces”。

转换现有表

要将非InnoDB表转换为使用InnoDB，请使用ALTER TABLE：

ALTER TABLE *table_name* ENGINE=InnoDB;

复制表的结构

您可以创建一个InnoDB表，它是 MyISAM 表的克隆，而不是使用ALTER TABLE执行转换，以在切换之前测试旧表和新表并排放置。

创建一个具有相同列和索引定义的空InnoDB表。使用SHOW CREATE TABLE *table_name*\G查看完整的CREATE TABLE语句。将ENGINE子句更改为ENGINE=INNODB。

数据传输

要将大量数据传输到在上一节中创建的空InnoDB表中，使用INSERT INTO *innodb_table* SELECT * FROM *myisam_table* ORDER BY *primary_key_columns*插入行。

在插入数据后，您还可以为InnoDB表创建索引。历史上，为InnoDB创建新的辅助索引是一个缓慢的操作，但现在您可以在加载数据后创建索引，而索引创建步骤的开销相对较小。

如果在辅助键上有UNIQUE约束，您可以在导入操作期间暂时关闭唯一性检查以加快表导入速度：

SET unique_checks=0;
*... import operation ...* SET unique_checks=1;

对于大表，这样可以节省磁盘 I/O，因为InnoDB可以使用其更改缓冲区批量写入辅助索引记录。确保数据不包含重复键。unique_checks允许但不要求存储引擎忽略重复键。

为了更好地控制插入过程，您可以分批插入大表：

INSERT INTO newtable SELECT * FROM oldtable
   WHERE yourkey > *something* AND yourkey <= *somethingelse*;

插入所有记录后，您可以重命名表。

在转换大表时，增加InnoDB缓冲池的大小以减少磁盘 I/O。通常，推荐的缓冲池大小为系统内存的 50 到 75％。您还可以增加InnoDB日志文件的大小。

存储要求

如果您打算在转换过程中在InnoDB表中创建多个临时副本，建议您创建文件级表空间中的表，以便在删除表时可以回收磁盘空间。当启用innodb_file_per_table配置选项（默认情况下）时，新创建的InnoDB表会隐式地创建在文件级表空间中。

无论是直接转换MyISAM表还是创建一个克隆的InnoDB表，确保在整个过程中有足够的磁盘空间来容纳新旧表。**InnoDB表需要比MyISAM表更多的磁盘空间。**如果ALTER TABLE操作空间不足，它会启动回滚，如果受限于磁盘，这可能需要数小时。对于插入操作，InnoDB使用插入缓冲区批量合并次要索引记录到索引中。这样可以节省大量磁盘 I/O。但对于回滚操作，没有使用这样的机制，回滚可能比插入操作慢 30 倍。

在出现无法控制的回滚情况下，如果数据库中没有重要数据，可能建议终止数据库进程，而不是等待数百万次磁盘 I/O 操作完成。有关完整的步骤，请参见第 17.21.3 节，“强制 InnoDB 恢复”。

定义主键

PRIMARY KEY子句是影响 MySQL 查询性能和表格及索引空间使用的关键因素。主键在表中唯一标识一行。表中的每一行应该有一个主键值，而且没有两行可以有相同的主键值。

这些是主键的指导原则，后面是更详细的解释。

• 为每个表声明一个PRIMARY KEY。通常，这是在查找单行时在WHERE子句中引用的最重要的列。

• 在原始的CREATE TABLE语句中声明PRIMARY KEY子句，而不是通过ALTER TABLE语句后期添加。

• 仔细选择列和其数据类型。优先选择数值列而不是字符或字符串列。

• 如果没有其他稳定的、唯一的、非空的、数值列可用，考虑使用自增列。

• 如果对主键列的值是否会发生变化存在任何疑问，自增列也是一个不错的选择。更改主键列的值是一项昂贵的操作，可能涉及表内数据和每个次要索引的重新排列。

考虑为任何尚未拥有主键的表添加主键。根据表的最大预期大小，使用最小的实用数值类型。这可以使每行稍微更紧凑，对于大表可以产生大量的空间节省。如果表具有任何次要索引，则空间节省会成倍增加，因为主键值在每个次要索引条目中重复。除了减少磁盘上的数据大小外，较小的主键还可以让更多的数据适应缓冲池，加快各种操作的速度，并提高并发性。

如果表已经在某个较长的列上有一个主键，比如VARCHAR，考虑添加一个新的无符号的AUTO_INCREMENT列，并将主键切换到该列，即使该列在查询中没有被引用。这种设计更改可以在次要索引中产生大量的空间节省。您可以将以前的主键列指定为UNIQUE NOT NULL，以强制执行与PRIMARY KEY子句相同的约束，即防止所有这些列中的重复或空值。

如果您将相关信息分布在多个表中，通常每个表都使用相同的列作为其主键。例如，人事数据库可能有几个表，每个表的主键都是员工编号。销售数据库可能有一些表的主键是客户编号，其他表的主键是订单编号。由于使用主键进行查找非常快速，您可以为这些表构建高效的连接查询。

如果完全省略PRIMARY KEY子句，MySQL 会为您创建一个不可见的主键。这是一个 6 字节的值，可能比您需要的要长，从而浪费空间。由于它是隐藏的，您无法在查询中引用它。

应用程序性能考虑

InnoDB的可靠性和可扩展性功能需要比等效的MyISAM表更多的磁盘存储空间。您可以稍微更改列和索引定义，以获得更好的空间利用率，在处理结果集时减少 I/O 和内存消耗，并制定更好的查询优化计划，以有效利用索引查找。

如果为主键设置了一个数字 ID 列，请使用该值与其他表中的相关值进行交叉引用，特别是对于连接查询。例如，不要接受国家名称作为输入并执行搜索相同名称的查询，而是进行一次查找以确定国家 ID，然后进行其他查询（或单个连接查询）以查找跨多个表的相关信息。而不是将客户或目录项号存储为一串数字，可能使用多个字节，将其转换为数字 ID 以进行存储和查询。一个 4 字节的无符号INT列可以索引超过 40 亿个项目（使用十进制中的十亿：1000 百万）。有关不同整数类型的范围，请参见第 13.1.2 节，“整数类型（精确值） - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT”。

理解与 InnoDB 表相关的文件

InnoDB文件需要比MyISAM文件更多的关注和计划。

• 你不应删除代表InnoDB系统表空间的 ibdata 文件。

• 描述了将InnoDB表移动或复制到不同服务器的方法在第 17.6.1.4 节，“移动或复制 InnoDB 表”中。

原文：dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html

17.6.1.6 InnoDB 中的 AUTO_INCREMENT 处理

InnoDB提供了可配置的锁定机制，可以显著提高向带有AUTO_INCREMENT列的表添加行的 SQL 语句的可伸缩性和性能。要在InnoDB表中使用AUTO_INCREMENT机制，必须将AUTO_INCREMENT列定义为某个索引的第一列或唯一列，以便可以对表执行相当于索引的SELECT MAX(*ai_col*)查找以获取最大列值。索引不需要是PRIMARY KEY或UNIQUE，但为了避免AUTO_INCREMENT列中的重复值，建议使用这些索引类型。

本节描述了AUTO_INCREMENT锁定模式，不同AUTO_INCREMENT锁定模式设置的使用影响以及InnoDB如何初始化AUTO_INCREMENT计数器。

• InnoDB AUTO_INCREMENT 锁定模式

• InnoDB AUTO_INCREMENT 锁定模式使用影响

• InnoDB AUTO_INCREMENT 计数器初始化

• 注释

InnoDB AUTO_INCREMENT 锁定模式

本节描述了用于生成自增值的AUTO_INCREMENT锁定模式，以及每种锁定模式如何影响复制。auto-increment锁定模式是在启动时使用innodb_autoinc_lock_mode变量进行配置的。

在描述innodb_autoinc_lock_mode设置时使用以下术语：

• “INSERT-like”语句

  所有在表中生成新行的语句，包括INSERT，INSERT ... SELECT，REPLACE，REPLACE ... SELECT和LOAD DATA。包括“simple-inserts”，“bulk-inserts”和“mixed-mode”插入。

• “简单插入”

  可以在初始处理语句时提前确定要插入的行数的语句。这包括没有嵌套子查询的单行和多行INSERT和REPLACE语句，但不包括INSERT ... ON DUPLICATE KEY UPDATE。

• “批量插入”

  无法提前确定要插入的行数（和所需的自动递增值数量）的语句。这包括INSERT ... SELECT、REPLACE ... SELECT和LOAD DATA语句，但不包括普通的INSERT。InnoDB在处理每一行时逐个为AUTO_INCREMENT列分配新值。

• “混合模式插入”

  这些是指定一些（但不是所有）新行的自动递增值的“简单插入”语句。以下是一个示例，其中c1是表t1的一个AUTO_INCREMENT列：

  INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
  

  另一种“混合模式插入”是INSERT ... ON DUPLICATE KEY UPDATE，在最坏的情况下实际上是一个INSERT后跟一个UPDATE，其中在更新阶段可能会或可能不会使用AUTO_INCREMENT列的分配值。

innodb_autoinc_lock_mode变量有三种可能的设置。这些设置分别为 0、1 或 2，分别为“传统”、“连续”或“交错”锁定模式。从 MySQL 8.0 开始，交错锁定模式（innodb_autoinc_lock_mode=2）是默认设置。在 MySQL 8.0 之前，连续锁定模式是默认设置（innodb_autoinc_lock_mode=1）。

MySQL 8.0 中交错锁定模式的默认设置反映了从基于语句的复制到基于行的复制作为默认复制类型的更改。基于语句的复制需要连续的自动递增锁定模式，以确保为给定的一系列 SQL 语句分配自动递增值的顺序是可预测和可重复的，而基于行的复制不会受到 SQL 语句执行顺序的影响。

• innodb_autoinc_lock_mode = 0（“传统”锁定模式）

  传统的锁模式提供了在引入 innodb_autoinc_lock_mode 变量之前存在的相同行为。传统的锁模式选项是为了向后兼容性、性能测试以及解决“混合模式插入”问题而提供的，因为可能存在语义上的差异。

  在此锁模式下，所有“类似于 INSERT”的语句为具有 AUTO_INCREMENT 列的表获取特殊的表级 AUTO-INC 锁。通常，此锁持续到语句结束（而不是事务结束），以确保为给定的一系列 INSERT 语句分配自增值的顺序是可预测且可重复的，并确保由任何给定语句分配的自增值是连续的。

  在基于语句的复制中，这意味着当 SQL 语句在副本服务器上复制时，自增列使用与源服务器相同的值。多个 INSERT 语句的执行结果是确定性的，并且副本会复制与源相同的数据。如果多个 INSERT 语句生成的自增值交错，那么两个并发的 INSERT 语句的结果将是不确定的，并且无法可靠地使用基于语句的复制传播到副本服务器。

  为了更清楚地说明，考虑一个使用此表的示例：

  CREATE TABLE t1 (
    c1 INT(11) NOT NULL AUTO_INCREMENT,
    c2 VARCHAR(10) DEFAULT NULL,
    PRIMARY KEY (c1)
  ) ENGINE=InnoDB;
  

  假设有两个正在运行的事务，每个事务都向具有 AUTO_INCREMENT 列的表中插入行。一个事务使用 INSERT ... SELECT 语句插入 1000 行，另一个使用简单的 INSERT 语句插入一行：

  Tx1: INSERT INTO t1 (c2) SELECT 1000 rows from another table ...
  Tx2: INSERT INTO t1 (c2) VALUES ('xxx');
  

  InnoDB 无法提前知道在 Tx1 中的 INSERT 语句中从 SELECT 检索到多少行，并且随着语句的执行，它逐个分配自增值。通过表级锁，持续到语句结束，只有一个涉及表 t1 的 INSERT 语句可以同时执行，并且不同语句生成的自增值不会交错。Tx1 的 INSERT ... SELECT 语句生成的自增值是连续的，而 Tx2 中 INSERT 语句使用的（单个）自增值要么比 Tx1 中使用的所有自增值小，要么大，具体取决于哪个语句先执行。

  只要在从二进制日志重放 SQL 语句时（在使用基于语句的复制或在恢复场景中）SQL 语句以相同的顺序执行，结果与 Tx1 和 Tx2 首次运行时的结果相同。因此，持有直到语句结束的表级锁使得使用自增的INSERT语句在基于语句的复制中是安全的。然而，这些表级锁会限制并发性和可伸缩性，当多个事务同时执行插入语句时。

  在上面的例子中，如果没有表级锁，用于 Tx2 中的INSERT的自增列的值取决于语句执行的时间。如果 Tx2 的INSERT在 Tx1 的INSERT运行时执行（而不是在其开始之前或完成之后），那么两个INSERT语句分配的具体自增值是不确定的，并且可能会因运行而异。

  在连续锁模式下，InnoDB可以避免对“简单插入”语句使用表级AUTO-INC锁，其中行数是预先知道的，并且仍然保持基于语句的复制的确定性执行和安全性。

  如果不使用二进制日志来重放 SQL 语句作为恢复或复制的一部分，可以使用交错锁模式来消除所有表级AUTO-INC锁的使用，以获得更大的并发性和性能，但代价是允许语句分配的自增号中存在间隙，并且可能会有并发执行语句分配的号码交错。

• innodb_autoinc_lock_mode = 1（“连续”锁模式）

  在这种模式下，“批量插入”使用特殊的AUTO-INC表级锁，并在语句结束前保持该锁。这适用于所有INSERT ... SELECT、REPLACE ... SELECT和LOAD DATA语句。只能有一个持有AUTO-INC锁的语句可以执行。如果批量插入操作的源表与目标表不同，那么在从源表选择的第一行获取共享锁后，将在目标表上获取AUTO-INC锁。如果批量插入操作的源表和目标表是同一张表，则在选择的所有行上获取共享锁后，将获取AUTO-INC锁。

  “简单插入”（已知要插入的行数）通过在互斥锁（一种轻量级锁）的控制下获取所需数量的自增值来避免表级AUTO-INC锁，该锁仅在分配过程的持续时间内保持，而不是直到语句完成。除非另一个事务持有AUTO-INC锁，否则不会使用表级AUTO-INC锁。如果另一个事务持有AUTO-INC锁，则“简单插入”将等待AUTO-INC锁，就像它是“批量插入”一样。

  这种锁定模式确保，在存在不事先知道要插入的行数的INSERT语句中（并且自增数字是随着语句的进行而分配的），任何“INSERT-like”语句分配的所有自增值都是连续的，并且操作对于基于语句的复制是安全的。

  简而言之，这种锁定模式在显著提高可伸缩性的同时，对于基于语句的复制是安全的。此外，与“传统”锁定模式一样，任何给定语句分配的自增数字是连续的。与使用自增的“传统”模式相比，对于使用自增的任何语句，语义没有变化，只有一个重要的例外。

  例外情况是“混合模式插入”，其中用户为多行“简单插入”中的某些行的AUTO_INCREMENT列提供显式值，但不是所有行。对于这样的插入，InnoDB分配的自增值比要插入的行数多。然而，所有自动分配的值都是连续生成的（因此高于）最近执行的前一个语句生成的自增值。“多余”的数字会丢失。

• innodb_autoinc_lock_mode = 2（“交错”锁定模式）

  在这种锁定模式下，没有“INSERT-like”语句使用表级AUTO-INC锁，并且多个语句可以同时执行。这是最快和最可伸缩的锁定模式，但在使用基于语句的复制或从二进制日志重放 SQL 语句的恢复场景时不安全。

  在这种锁定模式下，自增值保证在所有同时执行的“INSERT-like”语句中是唯一且单调递增的。然而，由于多个语句可以同时生成数字（即，数字的分配在语句之间交错进行），因此由任何给定语句插入的行生成的值可能不是连续的。

  如果唯一执行的语句是“简单插入”，其中要插入的行数是事先已知的，那么对于单个语句生成的数字不会有间隔，除了“混合模式插入”。然而，当执行“批量插入”时，任何给定语句分配的自增值可能存在间隔。

InnoDB AUTO_INCREMENT 锁定模式的使用影响

• 使用复制的自增

  如果您正在使用基于语句的复制，请将innodb_autoinc_lock_mode设置为 0 或 1，并在源和其副本上使用相同的值。如果您使用innodb_autoinc_lock_mode = 2（“交错”）或源和副本不使用相同锁定模式的配置，则不能确保副本上的自增值与源上的相同。

  如果您正在使用基于行或混合格式的复制，所有的自增锁定模式都是安全的，因为基于行的复制不受 SQL 语句执行顺序的影响（混合格式使用基于行的复制来处理任何对基于语句的复制不安全的语句）。

• “丢失”的自增值和序列间隔

  在所有的锁模式（0、1 和 2）中，如果生成自增值的事务回滚，那些自增值就会“丢失”。一旦为自增列生成了一个值，无论“INSERT-like”语句是否完成，以及包含事务是否回滚，都无法回滚该值。这些丢失的值不会被重用。因此，表中的AUTO_INCREMENT列中的值可能存在间隔。

• 为AUTO_INCREMENT列指定 NULL 或 0

  在所有的锁模式（0、1 和 2）中，如果用户在INSERT中的AUTO_INCREMENT列中指定 NULL 或 0，InnoDB会将该行视为未指定值并为其生成一个新值。

• 为AUTO_INCREMENT列分配负值

  在所有的锁模式（0、1 和 2）中，如果将负值分配给AUTO_INCREMENT列，则自增机制的行为是未定义的。

• 如果AUTO_INCREMENT值变大于指定整数类型的最大整数

  在所有的锁模式（0、1 和 2）中，如果值变大于指定整数类型中可以存储的最大整数，则自增机制的行为是未定义的。

• “批量插入”中的自增值间隔

  当innodb_autoinc_lock_mode设置为 0（“传统”）或 1（“连续”）时，由任何给定语句生成的自增值是连续的，没有间隔，因为表级别的AUTO-INC锁会一直保持到语句结束，而且一次只能执行一个这样的语句。

  当innodb_autoinc_lock_mode设置为 2（“交错”）时，可能会在“批量插入”生成的自增值中出现间隙，但只有在同时执行“类似INSERT”语句时才会出现。

  对于锁定模式 1 或 2，连续语句之间可能会出现间隙，因为对于批量插入，每个语句所需的自增值数量可能未知，可能会出现过估计。

• “混合模式插入”分配的自增值

  考虑一个“混合模式插入”，其中一个“简单插入”为一些（但不是全部）结果行指定了自增值。这样的语句在锁定模式 0、1 和 2 中的行为不同。例如，假设c1是表t1的AUTO_INCREMENT列，并且最近自动生成的序列号是 100。

  mysql> CREATE TABLE t1 (
   -> c1 INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, 
   -> c2 CHAR(1)
   -> ) ENGINE = INNODB;
  

  现在，考虑以下“混合模式插入”语句：

  mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');
  

  当innodb_autoinc_lock_mode设置为 0（“传统”）时，四行新数据为：

  mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
  +-----+------+
  | c1  | c2   |
  +-----+------+
  |   1 | a    |
  | 101 | b    |
  |   5 | c    |
  | 102 | d    |
  +-----+------+
  

  下一个可用的自增值是 103，因为自增值是一次分配一个，而不是在语句执行开始时一次性分配所有。无论是否有同时执行的“类似INSERT”语句（任何类型），这个结果都是正确的。

  当innodb_autoinc_lock_mode设置为 1（“连续”）时，四行新数据也是：

  mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
  +-----+------+
  | c1  | c2   |
  +-----+------+
  |   1 | a    |
  | 101 | b    |
  |   5 | c    |
  | 102 | d    |
  +-----+------+
  

  然而，在这种情况下，下一个可用的自增值是 105，而不是 103，因为在处理语句时分配了四个自增值，但只使用了两个。无论是否有同时执行的“类似INSERT”语句（任何类型），这个结果都是正确的。

  当innodb_autoinc_lock_mode设置为 2（“交错”）时，四行新数据为：

  mysql> SELECT c1, c2 FROM t1 ORDER BY c2;
  +-----+------+
  | c1  | c2   |
  +-----+------+
  |   1 | a    |
  |   *x* | b    |
  |   5 | c    |
  |   *y* | d    |
  +-----+------+
  

  *x和y的值是唯一的且大于先前生成的任何行。然而，x和y*的具体值取决于同时执行语句生成的自增值的数量。

  最后，考虑以下语句，在最近生成的序列号为 100 时发出：

  mysql> INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (101,'c'), (NULL,'d');
  

  无论innodb_autoinc_lock_mode设置为何值，这个语句都会生成一个重复键错误 23000（无法写入；表中有重复键），因为 101 被分配给行(NULL, 'b')，插入行(101, 'c')失败。

• 修改AUTO_INCREMENT列值在一系列INSERT语句中间

  在 MySQL 5.7 及更早版本中，在一系列INSERT语句中间修改AUTO_INCREMENT列值可能会导致“重复条目”错误。例如，如果执行了一个将AUTO_INCREMENT列值更改为大于当前最大自增值的值的UPDATE操作，则后续未指定未使用的自增值的INSERT操作可能会遇到“重复条目”错误。在 MySQL 8.0 及更高版本中，如果将AUTO_INCREMENT列值修改为大于当前最大自增值的值，则新值会被持久化，并且后续的INSERT操作会从新的更大值开始分配自增值。这种行为在以下示例中展示。

  mysql> CREATE TABLE t1 (
   -> c1 INT NOT NULL AUTO_INCREMENT,
   -> PRIMARY KEY (c1)
   ->  ) ENGINE = InnoDB;
  
  mysql> INSERT INTO t1 VALUES(0), (0), (3);
  
  mysql> SELECT c1 FROM t1;
  +----+
  | c1 |
  +----+
  |  1 |
  |  2 |
  |  3 |
  +----+
  
  mysql> UPDATE t1 SET c1 = 4 WHERE c1 = 1;
  
  mysql> SELECT c1 FROM t1;
  +----+
  | c1 |
  +----+
  |  2 |
  |  3 |
  |  4 |
  +----+
  
  mysql> INSERT INTO t1 VALUES(0);
  
  mysql> SELECT c1 FROM t1;
  +----+
  | c1 |
  +----+
  |  2 |
  |  3 |
  |  4 |
  |  5 |
  +----+
  

InnoDB AUTO_INCREMENT 计数器初始化

本节描述了InnoDB如何初始化AUTO_INCREMENT计数器。

如果为InnoDB表指定了AUTO_INCREMENT列，内存中的表对象包含一个称为自增计数器的特殊计数器，用于为该列分配新值。

在 MySQL 5.7 及更早版本中，自增计数器存储在主内存中，而不是在磁盘上。要在服务器重新启动后初始化自增计数器，InnoDB会在包含AUTO_INCREMENT列的表中第一次插入时执行类似以下语句的操作。

SELECT MAX(ai_col) FROM *table_name* FOR UPDATE;

在 MySQL 8.0 中，此行为已更改。每次更改当前最大自增计数器值时，都会将其写入重做日志并保存到数据字典中的检查点。这些更改使得当前最大自增计数器值在服务器重新启动时保持持久性。

在正常关闭后的服务器重新启动时，InnoDB 使用数据字典中存储的当前最大自增值初始化内存中的自增计数器。

在服务器重新启动期间进行崩溃恢复时，InnoDB 使用数据字典中存储的当前最大自增值初始化内存中的自增计数器，并扫描重做日志，查找自上次检查点以来写入的自增计数器值。如果重做记录的值大于内存中的计数器值，则应用重做记录的值。然而，在意外服务器退出的情况下，无法保证之前分配的自增值是否会被重用。每次由于INSERT或UPDATE操作而更改当前最大自增值时，新值会被写入重做日志，但如果在重做日志刷新到磁盘之前发生意外退出，则在服务器重新启动后初始化自增计数器时之前分配的值可能会被重用。

仅当导入表时没有.cfg元数据文件时，InnoDB才会使用类似于SELECT MAX(ai_col) FROM *table_name* FOR UPDATE语句来初始化自增计数器的等效值。否则，如果存在.cfg元数据文件，则从中读取当前最大自增计数器值。除了计数器值的初始化外，当尝试使用ALTER TABLE ... AUTO_INCREMENT = *N*语句将计数器值设置为小于或等于持久化计数器值时，会使用类似于SELECT MAX(ai_col) FROM *table_name*语句来确定表的当前最大自增计数器值。例如，您可能在删除一些记录后尝试将计数器值设置为较小的值。在这种情况下，必须搜索表以确保新的计数器值不小于或等于实际当前最大计数器值。

在 MySQL 5.7 及更早版本中，服务器重新启动会取消AUTO_INCREMENT = N表选项的效果，该选项可用于在CREATE TABLE或ALTER TABLE语句中设置初始计数器值或修改现有计数器值。在 MySQL 8.0 中，服务器重新启动不会取消AUTO_INCREMENT = N表选项的效果。如果将自增计数器初始化为特定值，或者将自增计数器值更改为较大值，则新值会在服务器重新启动时持久化。

注意

ALTER TABLE ... AUTO_INCREMENT = N只能将自增计数器值更改为大于当前最大值的值。

在 MySQL 5.7 及更早版本中，ROLLBACK操作后立即重新启动服务器可能导致先前分配给回滚事务的自增值被重用，实际上回滚了当前最大自增值。在 MySQL 8.0 中，当前最大自增值是持久化的，防止了先前分配值的重用。

如果在初始化自增计数器之前SHOW TABLE STATUS语句检查表，则InnoDB会打开表并使用存储在数据字典中的当前最大自增值来初始化计数器值。然后将该值存储在内存中供后续插入或更新使用。计数器值的初始化使用对表的正常独占锁定读取，该读取持续到事务结束。当初始化具有大于 0 的用户指定自增值的新创建表的自增计数器时，InnoDB遵循相同的过程。

初始化自增计数器后，如果在插入行时没有明确指定自增值，InnoDB 会隐式递增计数器并将新值分配给列。如果插入一行时明确指定了自增列值，并且该值大于当前最大计数器值，则计数器将设置为指定值。

InnoDB 在服务器运行时使用内存中的自增计数器。当服务器停止并重新启动时，InnoDB 会重新初始化自增计数器，如前所述。

auto_increment_offset 变量确定 AUTO_INCREMENT 列值的起始点。默认设置为 1。

auto_increment_increment 变量控制连续列值之间的间隔。默认设置为 1。

注意

当 AUTO_INCREMENT 整数列用尽数值时，后续的 INSERT 操作会返回重复键错误。这是一般的 MySQL 行为。

17.6.2 索引

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

17.6.2.1 聚簇索引和二级索引

17.6.2.2 InnoDB 索引的物理结构

17.6.2.3 排序索引构建

17.6.2.4 InnoDB 全文索引

本节涵盖与InnoDB索引相关的主题。

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

17.6.2.1 聚簇和二级索引

每个InnoDB表都有一个特殊的索引，称为聚簇索引，用于存储行数据。通常，聚簇索引与主键是同义词。为了从查询、插入和其他数据库操作中获得最佳性能，重要的是了解InnoDB如何利用聚簇索引来优化常见的查找和 DML 操作。

• 当在表上定义PRIMARY KEY时，InnoDB将其用作聚簇索引。每个表应该定义一个主键。如果没有逻辑上唯一且非空的列或列集可用作主键，则添加一个自增列。自增列的值是唯一的，并在插入新行时自动添加。

• 如果没有为表定义PRIMARY KEY，InnoDB将使用第一个所有关键列定义为NOT NULL的UNIQUE索引作为聚簇索引。

• 如果表没有PRIMARY KEY或合适的UNIQUE索引，InnoDB会在一个包含行 ID 值的合成列上生成一个隐藏的聚簇索引，该列包含行 ID 值。行按InnoDB分配的行 ID 递增有序。因此，按行 ID 排序的行在物理上是按插入顺序排列的。

聚簇索引如何加速查询

通过聚簇索引访问行非常快，因为索引搜索直接导向包含行数据的页面。如果表很大，与使用不同页面存储行数据的存储组织相比，聚簇索引架构通常可以节省磁盘 I/O 操作。

二级索引与聚簇索引的关系

除了聚簇索引之外的索引称为二级索引。在InnoDB中，二级索引中的每个记录都包含行的主键列，以及为二级索引指定的列。InnoDB使用这个主键值在聚簇索引中搜索行。

如果主键很长，二级索引会占用更多空间，因此拥有一个较短的主键是有利的。

有关利用InnoDB聚簇和二级索引的指导方针，请参见第 10.3 节，“优化和索引”。

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

17.6.2.2 InnoDB 索引的物理结构

除了空间索引外，InnoDB索引是 B 树数据结构。空间索引使用 R 树，这是专门用于索引多维数据的数据结构。索引记录存储在它们的 B 树或 R 树数据结构的叶子页中。索引页的默认大小为 16KB。页面大小由 MySQL 实例初始化时的innodb_page_size设置确定。请参阅第 17.8.1 节，“InnoDB 启动配置”。

当新记录插入InnoDB的聚簇索引时，InnoDB尝试保留 1/16 的页面空间以供将来插入和更新索引记录。如果索引记录按顺序（升序或降序）插入，则生成的索引页约为 15/16 满。如果记录以随机顺序插入，则页面填充率为 1/2 至 15/16。

创建或重建 B 树索引时，InnoDB执行批量加载。这种索引创建方法称为排序索引构建。innodb_fill_factor变量定义了在排序索引构建期间填充每个 B 树页的空间百分比，剩余空间保留用于未来的索引增长。空间索引不支持排序索引构建。有关更多信息，请参阅第 17.6.2.3 节，“排序索引构建”。将innodb_fill_factor设置为 100 会使聚簇索引页中的 1/16 空间保留用于未来的索引增长。

如果InnoDB索引页的填充因子低于MERGE_THRESHOLD，默认为 50%，InnoDB会尝试收缩索引树以释放页面。MERGE_THRESHOLD设置适用于 B 树和 R 树索引。有关更多信息，请参阅第 17.8.11 节，“配置索引页合并阈值”。

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

17.6.2.3 排序索引构建

InnoDB在创建或重建索引时执行批量加载，而不是逐个插入一个索引记录。这种索引创建方法也被称为排序索引构建。排序索引构建不支持空间索引。

索引构建有三个阶段。在第一阶段中，扫描聚集索引，生成索引条目并添加到排序缓冲区。当排序缓冲区变满时，条目被排序并写入临时中间文件。这个过程也被称为“运行”。在第二阶段，将一个或多个运行写入临时中间文件后，在文件中对所有条目执行合并排序。在第三和最后阶段，排序的条目被插入到 B 树中。

在引入排序索引构建之前，索引条目是使用插入 API 逐个插入到 B 树中的。这种方法涉及打开一个 B 树游标以找到插入位置，然后使用乐观插入将条目插入到 B 树页中。如果由于页面已满而插入失败，则会执行悲观插入，这涉及打开一个 B 树游标，并根据需要拆分和合并 B 树节点以为条目找到空间。构建索引的这种“自顶向下”方法的缺点是搜索插入位置的成本以及不断拆分和合并 B 树节点。

排序索引构建使用“自底向上”的方法构建索引。采用这种方法，B 树的所有级别都保存对最右叶页的引用。在必要的 B 树深度上分配最右叶页，并根据它们的排序顺序插入条目。一旦叶页已满，就会向父页附加一个节点指针，并为下一个插入分配一个兄弟叶页。这个过程会一直持续，直到所有条目都被插入，这可能导致插入到根级别。当分配一个兄弟页时，之前固定的叶页的引用会被释放，新分配的叶页成为最右叶页和新的默认插入位置。

为未来索引增长保留 B 树页空间

为了为未来的索引增长留出空间，您可以使用innodb_fill_factor变量来保留 B-Tree 页面空间的百分比。例如，将innodb_fill_factor设置为 80 会在排序索引构建期间保留 B-Tree 页面中 20％的空间。此设置适用于 B-Tree 叶子和非叶子页面。它不适用于用于TEXT或BLOB条目的外部页面。保留的空间量可能不会完全按照配置，因为innodb_fill_factor值被解释为提示而不是硬限制。

排序索引构建和全文索引支持

支持对全文索引进行排序。以前，SQL 用于向全文索引插入条目。

排序索引构建和压缩表

对于压缩表，先前的索引创建方法将条目附加到压缩和未压缩页面。当修改日志（表示压缩页面上的空闲空间）变满时，压缩页面将被重新压缩。如果由于空间不足而导致压缩失败，则页面将被分割。通过排序索引构建，条目仅附加到未压缩页面。当未压缩页面变满时，它将被压缩。自适应填充用于确保在大多数情况下压缩成功，但如果压缩失败，则页面将被分割，并再次尝试压缩。此过程将持续，直到压缩成功。有关 InnoDB 表 B-Tree 页面压缩的更多信息，请参见第 17.9.1.5 节，“InnoDB 表的压缩工作原理”。

排序索引构建和重做日志

在排序索引构建期间，重做日志被禁用。相反，存在一个检查点以确保索引构建可以经受意外退出或失败。检查点强制将所有脏页写入磁盘。在排序索引构建期间，会定期向页面清理器线程发出信号，以刷新脏页以确保检查点操作可以快速处理。通常情况下，当干净页面数量低于设定阈值时，页面清理器线程会刷新脏页。对于排序索引构建，脏页会被迅速刷新以减少检查点开销并并行化 I/O 和 CPU 活动。

排序索引构建和优化器统计

排序索引构建可能导致与以前的索引创建方法生成的优化器统计数据不同。统计数据的差异不会影响工作负载性能，这是由于用于填充索引的不同算法所致。

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

17.6.2.4 InnoDB 全文索引

全文索引是在基于文本的列（CHAR、VARCHAR或TEXT列）上创建的，以加快对这些列中包含的数据的查询和 DML 操作。

全文索引被定义为CREATE TABLE语句的一部分，或者通过ALTER TABLE或CREATE INDEX添加到现有表中。

使用MATCH() ... AGAINST语法执行全文搜索。有关使用信息，请参见第 14.9 节，“全文搜索函数”。

InnoDB全文索引在本节中以下主题下进行描述：

• InnoDB 全文索引设计

• InnoDB 全文索引表

• InnoDB 全文索引缓存

• InnoDB 全文索引 DOC_ID 和 FTS_DOC_ID 列

• InnoDB 全文索引删除处理

• InnoDB 全文索引事务处理

• 监控 InnoDB 全文索引

InnoDB 全文索引设计

InnoDB全文索引采用倒排索引设计。倒排索引存储单词列表，对于每个单词，还存储该单词出现在的文档列表。为了支持位置搜索，还存储了每个单词的位置信息，作为字节偏移量。

InnoDB 全文索引表

创建InnoDB全文索引时，将创建一组索引表，如下例所示：

mysql> CREATE TABLE opening_lines (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       opening_line TEXT(500),
       author VARCHAR(200),
       title VARCHAR(200),
       FULLTEXT idx (opening_line)
       ) ENGINE=InnoDB;

mysql> SELECT table_id, name, space from INFORMATION_SCHEMA.INNODB_TABLES
       WHERE name LIKE 'test/%';
+----------+----------------------------------------------------+-------+
| table_id | name                                               | space |
+----------+----------------------------------------------------+-------+
|      333 | test/fts_0000000000000147_00000000000001c9_index_1 |   289 |
|      334 | test/fts_0000000000000147_00000000000001c9_index_2 |   290 |
|      335 | test/fts_0000000000000147_00000000000001c9_index_3 |   291 |
|      336 | test/fts_0000000000000147_00000000000001c9_index_4 |   292 |
|      337 | test/fts_0000000000000147_00000000000001c9_index_5 |   293 |
|      338 | test/fts_0000000000000147_00000000000001c9_index_6 |   294 |
|      330 | test/fts_0000000000000147_being_deleted            |   286 |
|      331 | test/fts_0000000000000147_being_deleted_cache      |   287 |
|      332 | test/fts_0000000000000147_config                   |   288 |
|      328 | test/fts_0000000000000147_deleted                  |   284 |
|      329 | test/fts_0000000000000147_deleted_cache            |   285 |
|      327 | test/opening_lines                                 |   283 |
+----------+----------------------------------------------------+-------+

前六个索引表包括倒排索引，并被称为辅助索引表。当传入文档被标记化时，单词（也称为“标记”）与位置信息和相关的DOC_ID一起插入到索引表中。这些单词根据单词的第一个字符的字符集排序权重完全排序并分区在六个索引表中。

倒排索引被分成六个辅助索引表，以支持并行索引创建。默认情况下，两个线程对单词和相关数据进行标记化、排序和插入到索引表中。执行此工作的线程数量可通过 innodb_ft_sort_pll_degree 变量进行配置。在创建大表的全文索引时，考虑增加线程数量。

辅助索引表名以 fts_ 为前缀，并以 index_*#* 为后缀。每个辅助索引表通过辅助索引表名中的十六进制值与索引表相关联，该值与索引表的 table_id 匹配。例如，test/opening_lines 表的 table_id 是 327，其十六进制值为 0x147。如前面的示例所示，与 test/opening_lines 表相关联的辅助索引表的名称中出现了“147”十六进制值。

代表全文索引的 index_id 的十六进制值也出现在辅助索引表名中。例如，在辅助表名 test/fts_0000000000000147_00000000000001c9_index_1 中，十六进制值 1c9 的十进制值为 457。可以通过查询信息模式 INNODB_INDEXES 表来识别在 opening_lines 表上定义的索引（idx）的此值（457）。

mysql> SELECT index_id, name, table_id, space from INFORMATION_SCHEMA.INNODB_INDEXES
       WHERE index_id=457;
+----------+------+----------+-------+
| index_id | name | table_id | space |
+----------+------+----------+-------+
|      457 | idx  |      327 |   283 |
+----------+------+----------+-------+

如果主表是在 每表一个文件 表空间中创建的，则索引表存储在自己的表空间中。否则，索引表存储在索引表所在的表空间中。

前面示例中显示的其他索引表称为常见索引表，用于处理删除和存储全文索引的内部状态。与为每个全文索引创建的倒排索引表不同，这组表对于在特定表上创建的所有全文索引都是通用的。

即使删除全文索引，常见索引表也会保留。删除全文索引时，为索引创建的 FTS_DOC_ID 列将被保留，因为删除 FTS_DOC_ID 列将需要重建先前索引的表。常见索引表用于管理 FTS_DOC_ID 列。

• fts_*_deleted 和 fts_*_deleted_cache

  包含已删除但数据尚未从全文索引中删除的文档的文档 ID（DOC_ID）。fts_*_deleted_cache 是 fts_*_deleted 表的内存版本。

• fts_*_being_deleted 和 fts_*_being_deleted_cache

  包含已删除并且数据目前正在从全文索引中删除的文档的文档 ID（DOC_ID）。fts_*_being_deleted_cache 表是 fts_*_being_deleted 表的内存版本。

• fts_*_config

  存储有关全文索引内部状态的信息。最重要的是，它存储了FTS_SYNCED_DOC_ID，用于标识已解析并刷新到磁盘的文档。在崩溃恢复的情况下，FTS_SYNCED_DOC_ID 值用于标识尚未刷新到磁盘的文档，以便重新解析这些文档并添加回全文索引缓存。要查看此表中的数据，请查询信息模式 INNODB_FT_CONFIG 表。

InnoDB 全文索引缓存

当插入文档时，文档会被标记化，单词和相关数据会被插入到全文索引中。即使是对于小型文档，这个过程也可能导致大量小的插入到辅助索引表中，使得对这些表的并发访问成为一个争议点。为了避免这个问题，InnoDB 使用全文索引缓存来临时缓存最近插入行的索引表插入。这个内存中的缓存结构会保存插入，直到缓存满了，然后批量将它们刷新到磁盘（到辅助索引表）。您可以查询信息模式 INNODB_FT_INDEX_CACHE 表来查看最近插入行的标记化数据。

缓存和批量刷新行为避免了对辅助索引表的频繁更新，这可能导致在繁忙的插入和更新时间发生并发访问问题。批处理技术还避免了对同一个单词的多次插入，并最小化了重复条目。与单独刷新每个单词不同，相同单词的插入会合并并作为单个条目刷新到磁盘，提高了插入效率，同时保持辅助索引表尽可能小。

innodb_ft_cache_size 变量用于配置全文索引缓存大小（每个表的基础），这会影响全文索引缓存刷新的频率。您还可以使用 innodb_ft_total_cache_size 变量为给定实例中的所有表定义一个全局全文索引缓存大小限制。

全文索引缓存存储与辅助索引表相同的信息。然而，全文索引缓存仅缓存最近插入行的标记化数据。当查询时，已经刷新到磁盘（到辅助索引表）的数据不会被带回到全文索引缓存中。辅助索引表中的数据直接查询，然后与全文索引缓存中的结果合并后返回。

InnoDB 全文索引 DOC_ID 和 FTS_DOC_ID 列

InnoDB 使用一个称为DOC_ID的唯一文档标识符，将全文索引中的单词映射到单词出现的文档记录上。这种映射需要在索引表上有一个FTS_DOC_ID列。如果没有定义FTS_DOC_ID列，InnoDB在创建全文索引时会自动添加一个隐藏的FTS_DOC_ID列。下面的示例演示了这种行为。

以下表定义不包括FTS_DOC_ID列：

mysql> CREATE TABLE opening_lines (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       opening_line TEXT(500),
       author VARCHAR(200),
       title VARCHAR(200)
       ) ENGINE=InnoDB;

当使用CREATE FULLTEXT INDEX语法在表上创建全文索引时，会返回一个警告，报告InnoDB正在重建表以添加FTS_DOC_ID列。

mysql> CREATE FULLTEXT INDEX idx ON opening_lines(opening_line);
Query OK, 0 rows affected, 1 warning (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning |  124 | InnoDB rebuilding table to add column FTS_DOC_ID |
+---------+------+--------------------------------------------------+

当使用ALTER TABLE向没有FTS_DOC_ID列的表添加全文索引时，会返回相同的警告。如果在CREATE TABLE时创建全文索引并且没有指定FTS_DOC_ID列，则InnoDB会自动添加一个隐藏的FTS_DOC_ID列，而不会有警告。

在CREATE TABLE时定义FTS_DOC_ID列比在已加载数据的表上创建全文索引要便宜。如果在加载数据之前在表上定义了FTS_DOC_ID列，则不需要重建表及其索引即可添加新列。如果不关心CREATE FULLTEXT INDEX的性能，请省略FTS_DOC_ID列，让InnoDB为您创建。InnoDB会在FTS_DOC_ID列上创建一个隐藏的FTS_DOC_ID列以及一个唯一索引（FTS_DOC_ID_INDEX）。如果要创建自己的FTS_DOC_ID列，则该列必须定义为BIGINT UNSIGNED NOT NULL，并命名为FTS_DOC_ID（全大写），如下面的示例所示：

注意

FTS_DOC_ID列不需要定义为AUTO_INCREMENT列，但这样做可以使数据加载更容易。

mysql> CREATE TABLE opening_lines (
       FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       opening_line TEXT(500),
       author VARCHAR(200),
       title VARCHAR(200)
       ) ENGINE=InnoDB;

如果选择自己定义FTS_DOC_ID列，则需要负责管理该列，以避免空值或重复值。FTS_DOC_ID值不能被重复使用，这意味着FTS_DOC_ID值必须是递增的。

可选地，您可以在FTS_DOC_ID列上创建所需的唯一FTS_DOC_ID_INDEX（全大写）。

mysql> CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on opening_lines(FTS_DOC_ID);

如果不创建FTS_DOC_ID_INDEX，InnoDB会自动创建它。

注意

由于InnoDB SQL 解析器不使用降序索引，因此无法将FTS_DOC_ID_INDEX定义为降序索引。

最大使用的FTS_DOC_ID值和新的FTS_DOC_ID值之间允许的间隔为 65535。

为避免重建表，删除全文索引时会保留FTS_DOC_ID列。

InnoDB 全文索引删除处理

删除具有全文索引列的记录可能导致辅助索引表中的大量小删除，使得对这些表的并发访问成为争议点。为了避免这个问题，每当从索引表中删除记录时，删除文档的DOC_ID会被记录在特殊的FTS_*_DELETED表中，并且索引记录仍然保留在全文索引中。在返回查询结果之前，FTS_*_DELETED表中的信息用于过滤已删除的DOC_ID。这种设计的好处是删除快速且廉价。缺点是删除记录后索引的大小不会立即减小。要删除已删除记录的全文索引条目，请在具有innodb_optimize_fulltext_only=ON的索引表上运行OPTIMIZE TABLE以重建全文索引。有关更多信息，请参阅优化 InnoDB 全文索引。

InnoDB 全文索引事务处理

InnoDB全文索引由于其缓存和批处理行为具有特殊的事务处理特性。具体来说，在事务提交时处理全文索引的更新和插入操作，这意味着全文搜索只能看到已提交的数据。以下示例演示了这种行为。只有在插入的行被提交后，全文搜索才会返回结果。

mysql> CREATE TABLE opening_lines (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       opening_line TEXT(500),
       author VARCHAR(200),
       title VARCHAR(200),
       FULLTEXT idx (opening_line)
       ) ENGINE=InnoDB;

mysql> BEGIN;

mysql> INSERT INTO opening_lines(opening_line,author,title) VALUES
       ('Call me Ishmael.','Herman Melville','Moby-Dick'),
       ('A screaming comes across the sky.','Thomas Pynchon','Gravity\'s Rainbow'),
       ('I am an invisible man.','Ralph Ellison','Invisible Man'),
       ('Where now? Who now? When now?','Samuel Beckett','The Unnamable'),
       ('It was love at first sight.','Joseph Heller','Catch-22'),
       ('All this happened, more or less.','Kurt Vonnegut','Slaughterhouse-Five'),
       ('Mrs. Dalloway said she would buy the flowers herself.','Virginia Woolf','Mrs. Dalloway'),
       ('It was a pleasure to burn.','Ray Bradbury','Fahrenheit 451');

mysql> SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST('Ishmael');
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+

mysql> COMMIT;

mysql> SELECT COUNT(*) FROM opening_lines WHERE MATCH(opening_line) AGAINST('Ishmael');
+----------+
| COUNT(*) |
+----------+
|        1 |
+----------+

监控 InnoDB 全文索引

您可以通过查询以下INFORMATION_SCHEMA表来监视和检查InnoDB全文索引的特殊文本处理方面：

• INNODB_FT_CONFIG

• INNODB_FT_INDEX_TABLE

• INNODB_FT_INDEX_CACHE

• INNODB_FT_DEFAULT_STOPWORD

• INNODB_FT_DELETED

• INNODB_FT_BEING_DELETED

通过查询INNODB_INDEXES和INNODB_TABLES，您还可以查看全文索引和表的基本信息。

欲了解更多信息，请参阅第 17.15.4 节，“InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables”。

17.6.3 表空间

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

17.6.3.1 系统表空间

17.6.3.2 每表一个文件的表空间

17.6.3.3 通用表空间

17.6.3.4 撤销表空间

17.6.3.5 临时表空间

17.6.3.6 在服务器离线时移动表空间文件

17.6.3.7 禁用表空间路径验证

17.6.3.8 优化 Linux 上表空间空间分配

17.6.3.9 表空间 AUTOEXTEND_SIZE 配置

本节涵盖与InnoDB表空间相关的主题。

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

17.6.3.1 系统表空间

系统表空间是更改缓冲区的存储区域。如果表是在系统表空间而不是文件表或通用表空间中创建的，则还可能包含表和索引数据。在以前的 MySQL 版本中，系统表空间包含InnoDB数据字典。在 MySQL 8.0 中，InnoDB将元数据存储在 MySQL 数据字典中。请参见第十六章 MySQL 数据字典。在以前的 MySQL 版本中，系统表空间还包含双写缓冲区存储区域。从 MySQL 8.0.20 开始，此存储区域位于单独的双写文件中。请参见第 17.6.4 节，“双写缓冲区”。

系统表空间可以有一个或多个数据文件。默认情况下，在数据目录中创建一个名为ibdata1的系统表空间数据文件。系统表空间数据文件的大小和数量由innodb_data_file_path启动选项定义。有关配置信息，请参见系统表空间数据文件配置。

有关系统表空间的其他信息在本节的以下主题下提供：

• 调整系统表空间大小

• 使用原始磁盘分区作为系统表空间

调整系统表空间大小

本节描述了如何增加或减少系统表空间的大小。

增加系统表空间大小

增加系统表空间大小的最简单方法是将其配置为自动扩展。为此，请在innodb_data_file_path设置中为最后一个数据文件指定autoextend属性，并重新启动服务器。例如：

innodb_data_file_path=ibdata1:10M:autoextend

当指定autoextend属性时，数据文件会根据需要自动以 8MB 递增的大小增加。innodb_autoextend_increment变量控制增量大小。

您还可以通过添加另一个数据文件来增加系统表空间大小。要这样做：

1. 停止 MySQL 服务器。

2. 如果在 innodb_data_file_path 设置中的最后一个数据文件定义了 autoextend 属性，请将其删除，并修改大小属性以反映当前数据文件大小。要确定要指定的适当数据文件大小，请检查文件系统的文件大小，并将该值向下舍入到最接近的 MB 值，其中 1 MB 等于 1024 x 1024 字节。

3. 将一个新的数据文件追加到 innodb_data_file_path 设置中，可选择指定 autoextend 属性。autoextend 属性只能针对 innodb_data_file_path 设置中的最后一个数据文件指定。

4. 启动 MySQL 服务器。

例如，这个表空间有一个自动扩展的数据文件：

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:10M:autoextend

假设随着时间的推移，数据文件已经增长到 988MB。这是在修改大小属性以反映当前数据文件大小，并指定一个新的 50MB 自动扩展数据文件后的 innodb_data_file_path 设置：

innodb_data_home_dir =
innodb_data_file_path = /ibdata/ibdata1:988M;/disk2/ibdata2:50M:autoextend

当添加新的数据文件时，请不要指定现有的文件名。InnoDB 在启动服务器时会创建并初始化新的数据文件。

注意

不能通过更改其大小属性来增加现有系统表空间数据文件的大小。例如，将 innodb_data_file_path 设置从 ibdata1:10M:autoextend 更改为 ibdata1:12M:autoextend 在启动服务器时会产生以下错误：

[ERROR] [MY-012263] [InnoDB] The Auto-extending innodb_system
data file './ibdata1' is of a different size 640 pages (rounded down to MB) than
specified in the .cnf file: initial 768 pages, max 0 (relevant if non-zero) pages!

错误表明现有数据文件大小（以 InnoDB 页表示）与配置文件中指定的数据文件大小不同。如果遇到此错误，请恢复先前的 innodb_data_file_path 设置，并参考系统表空间调整大小的说明。

减小 InnoDB 系统表空间的大小

不支持减小现有系统表空间的大小。实现较小系统表空间的唯一选项是从备份中恢复数据到一个新的 MySQL 实例，该实例创建时具有所需的系统表空间大小配置。

有关创建备份的信息，请参见 第 17.18.1 节，“InnoDB 备份”。

有关为新系统表空间配置数据文件的信息，请参见 系统表空间数据文件配置。

为避免大型系统表空间，考虑使用每表表空间或通用表空间存储您的数据。每表表空间是默认的表空间类型，在创建InnoDB表时隐式使用。与系统表空间不同，每表表空间在被截断或删除时将磁盘空间返回给操作系统。有关更多信息，请参见第 17.6.3.2 节，“每表表空间”。通用表空间是多表表空间，也可以用作系统表空间的替代方案。请参见第 17.6.3.3 节，“通用表空间”。

使用原始磁盘分区作为系统表空间

原始磁盘分区可以用作系统表空间数据文件。这种技术可以在 Windows 和一些 Linux 和 Unix 系统上进行非缓冲 I/O，而无需文件系统开销。在您的系统上执行有和没有原始分区的测试，以验证它们是否提高了性能。

使用原始磁盘分区时，请确保运行 MySQL 服务器的用户 ID 对该分区具有读写权限。例如，如果将服务器作为mysql用户运行，则分区必须可读可写。如果使用--memlock选项运行服务器，则服务器必须以root身份运行，因此分区必须可读可写。

下面描述的过程涉及选项文件的修改。有关更多信息，请参见第 6.2.2.2 节，“使用选项文件”。

在 Linux 和 Unix 系统上分配原始磁盘分区

1. 创建新数据文件时，在innodb_data_file_path选项的数据文件大小后立即指定关键字newraw。分区的大小必须至少与您指定的大小相同。请注意，在InnoDB中，1MB 是 1024 × 1024 字节，而在磁盘规范中，1MB 通常表示 1,000,000 字节。

   [mysqld]
   innodb_data_home_dir=
   innodb_data_file_path=/dev/hdd1:3Gnewraw;/dev/hdd2:2Gnewraw
   

2. 重新启动服务器。InnoDB注意到newraw关键字并初始化新分区。但是，不要创建或更改任何InnoDB表。否则，下次重新启动服务器时，InnoDB会重新初始化分区，您的更改将丢失。（作为安全措施，当指定任何带有newraw的分区时，InnoDB会阻止用户修改数据。）

3. 在InnoDB初始化新分区后，停止服务器，将数据文件规范中的newraw更改为raw：

   [mysqld]
   innodb_data_home_dir=
   innodb_data_file_path=/dev/hdd1:3Graw;/dev/hdd2:2Graw
   

4. 重新启动服务器。InnoDB现在允许进行更改。

在 Windows 上分配原始磁盘分区

在 Windows 系统上，适用于 Linux 和 Unix 系统的相同步骤和相关指南适用，只是在 Windows 上innodb_data_file_path设置略有不同。

1. 在创建新数据文件时，在innodb_data_file_path选项后立即指定关键字newraw：

   [mysqld]
   innodb_data_home_dir=
   innodb_data_file_path=//./D::10Gnewraw
   

   //./对应于 Windows 访问物理驱动器的语法\\.\。在上面的示例中，D:是分区的驱动器号。

2. 重新启动服务器。InnoDB注意到newraw关键字并初始化新分区。

3. 在InnoDB初始化新分区后，停止服务器，将数据文件规范中的newraw更改为raw：

   [mysqld]
   innodb_data_home_dir=
   innodb_data_file_path=//./D::10Graw
   

4. 重新启动服务器。InnoDB现在允许进行更改。

译文：dev.mysql.com/doc/refman/8.0/en/innodb-file-per-table-tablespaces.html

17.6.3.2 每表一个表空间

每表一个表空间包含单个InnoDB表的数据和索引，并存储在单个数据文件中的文件系统中。

每表一个表空间的特性在本节中的以下主题下描述：

• 每表一个表空间配置

• 每表一个表空间数据文件

• 每表一个表空间优势

• 每表一个表空间的缺点

每表一个表空间配置

InnoDB默认在每表一个表空间中创建表。此行为由innodb_file_per_table变量控制。禁用innodb_file_per_table会导致InnoDB在系统表空间中创建表。

可以在选项文件中指定innodb_file_per_table设置，也可以使用SET GLOBAL语句在运行时进行配置。在运行时更改设置需要足够权限来设置全局系统变量。请参见第 7.1.9.1 节，“系统变量权限”。

选项文件：

[mysqld]
innodb_file_per_table=ON

在运行时使用SET GLOBAL：

mysql> SET GLOBAL innodb_file_per_table=ON;

每表一个表空间数据文件

每表一个表空间在 MySQL 数据目录下的模式目录中创建一个.ibd数据文件。.ibd文件以表名（*table_name*.ibd）命名。例如，表test.t1的数据文件将在 MySQL 数据目录下的test目录中创建：

mysql> USE test;

mysql> CREATE TABLE t1 (
   id INT PRIMARY KEY AUTO_INCREMENT,
   name VARCHAR(100)
 ) ENGINE = InnoDB;

$> cd /*path*/*to*/*mysql*/data/test
$> ls
t1.ibd

您可以使用CREATE TABLE语句的DATA DIRECTORY子句隐式地在数据目录之外创建一个每表一个表空间数据文件。有关更多信息，请参见第 17.6.1.2 节，“外部创建表”。

每表一个表空间优势

每表一个表空间相对于共享表空间（如系统表空间或通用表空间）具有以下优势。

• 在每表表空间中创建的表被截断或删除后，磁盘空间将返回给操作系统。在共享表空间中截断或删除表会在共享表空间数据文件中创建可用于InnoDB数据的空闲空间。换句话说，共享表空间数据文件在截断或删除表后不会缩小。

• 在共享表空间中的表上执行表复制的ALTER TABLE操作可能会增加表空间所占用的磁盘空间。这些操作可能需要额外的空间，与表中的数据加索引相同。这些空间不会像每表表空间那样释放回操作系统。

• 在每表表空间文件中的表上执行TRUNCATE TABLE性能更好。

• 可以在单独的存储设备上为 I/O 优化、空间管理或备份目的创建每表表空间数据文件。请参见第 17.6.1.2 节，“外部创建表”。

• 您可以从另一个 MySQL 实例导入位于每表表空间中的表。请参见第 17.6.1.3 节，“导入 InnoDB 表”。

• 在每表表空间文件中创建的表支持与系统表空间不支持的DYNAMIC和COMPRESSED行格式相关的功能。请参见第 17.10 节，“InnoDB 行格式”。

• 存储在单独表空间数据文件中的表在数据损坏发生、备份或二进制日志不可用或 MySQL 服务器实例无法重新启动时，可以节省时间并提高成功恢复的机会。

• 在每表表空间文件中创建的表可以使用 MySQL 企业备份快速备份或恢复，而不会中断其他InnoDB表的使用。这对备份计划不同或需要较少备份的表非常有益。详细信息请参见进行部分备份。

• 每表表空间允许通过监视表空间数据文件的大小来监视文件系统上的表大小。

• 常见的 Linux 文件系统不允许在将innodb_flush_method设置为O_DIRECT时对单个文件进行并发写入。因此，在使用此设置时，使用每表表空间文件的方式可能会带来性能改进。

• 共享表空间中的表受到 64TB 表空间大小限制的限制。相比之下，每个每表表空间都有一个 64TB 的大小限制，为表增长提供了充足的空间。

以文件为单位的表空间的缺点

与系统表空间或通用表空间等共享表空间相比，以文件为单位的表空间具有以下缺点。

• 使用以文件为单位的表空间，每个表可能会有未使用的空间，只能由同一表的行利用，如果管理不当可能会导致空间浪费。

• fsync 操作是在多个以文件为单位的数据文件上执行的，而不是在单个共享表空间数据文件上执行。由于 fsync 操作是针对每个文件的，多个表的写操作不能合并，这可能导致更多的 fsync 操作。

• mysqld 必须为每个以文件为单位的表空间保持一个打开的文件句柄，如果在以文件为单位的表空间中有大量表，可能会影响性能。

• 每个表都有自己的数据文件时，需要更多的文件描述符。

• 存在更多的碎片化可能会妨碍 DROP TABLE 和表扫描性能。然而，如果管理碎片化，以文件为单位的表空间可以改善这些操作的性能。

• 当删除位于以文件为单位的表空间中的表时，需要扫描缓冲池，对于大型缓冲池可能需要几秒钟。扫描是使用广泛的内部锁执行的，这可能会延迟其他操作。

• innodb_autoextend_increment 变量定义了在自动扩展的共享表空间文件满时扩展大小的增量大小，但不适用于自动扩展的以文件为单位的表空间文件，这些文件无论 innodb_autoextend_increment 设置如何都会自动扩展。初始以文件为单位的表空间扩展量很小，之后以 4MB 的增量进行扩展。

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

17.6.3.3 通用表空间

通用表空间是使用CREATE TABLESPACE语法创建的共享InnoDB表空间。通用表空间的功能和特性在本节中的以下主题下描述：

• 通用表空间功能

• 创建通用表空间

• 向通用表空间添加表

• 通用表空间行格式支持

• 使用 ALTER TABLE 在表空间之间移动表

• 重命名通用表空间

• 删除通用表空间

• 通用表空间限制

通用表空间功能

通用表空间提供以下功能：

• 与系统表空间类似，通用表空间是可以存储多个表数据的共享表空间。

• 通用表空间相对于每表一个表空间具有潜在的内存优势。服务器在表空间的生命周期内将表空间元数据保存在内存中。较少的通用表空间中的多个表消耗的表空间元数据内存比相同数量的表在单独的每表一个表空间中少。

• 通用表空间数据文件可以放置在相对于 MySQL 数据目录或独立于 MySQL 数据目录的目录中，这为您提供了许多与每表一个表空间的数据文件和存储管理功能。与每表一个表空间一样，将数据文件放置在 MySQL 数据目录之外的能力允许您单独管理关键表的性能，为特定表设置 RAID 或 DRBD，或将表绑定到特定磁盘，例如。

• 通用表空间支持所有表行格式和相关功能。

• TABLESPACE选项可与CREATE TABLE一起使用，以在通用表空间、每表一个表空间或系统表空间中创建表。

• TABLESPACE 选项可与 ALTER TABLE 一起使用，以在通用表空间、每表一个文件的表空间和系统表空间之间移动表。

创建通用表空间

使用 CREATE TABLESPACE 语法创建通用表空间。

CREATE TABLESPACE *tablespace_name*
    [ADD DATAFILE '*file_name*']
    [FILE_BLOCK_SIZE = *value*]
        [ENGINE [=] *engine_name*]

通用表空间可以在数据目录内或外创建。为避免与隐式创建的每表一个文件的表空间冲突，不支持在数据目录下的子目录中创建通用表空间。在数据目录之外创建通用表空间时，目录必须存在，并且在创建表空间之前必须为 InnoDB 所知。要使未知目录为 InnoDB 所知，将目录添加到 innodb_directories 参数值中。innodb_directories 是一个只读启动选项。配置它需要重新启动服务器。

示例：

在数据目录中创建一个通用表空间：

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;

或

mysql> CREATE TABLESPACE `ts1` Engine=InnoDB;

在 MySQL 8.0.14 中，ADD DATAFILE 子句是可选的，在此之前是必需的。如果在创建表空间时未指定 ADD DATAFILE 子句，则会隐式创建一个具有唯一文件名的表空间数据文件。唯一文件名是一个 128 位 UUID，格式为五组十六进制数字，用破折号分隔 (aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee)。通用表空间数据文件包括一个 .ibd 文件扩展名。在复制环境中，源上创建的数据文件名与副本上创建的数据文件名不同。

在数据目录之外的目录中创建一个通用表空间：

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE '/my/tablespace/directory/ts1.ibd' Engine=InnoDB;

您可以指定相对于数据目录的路径，只要表空间目录不在数据目录下即可。在此示例中，my_tablespace 目录与数据目录处于同一级别：

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE '../my_tablespace/ts1.ibd' Engine=InnoDB;

注意

ENGINE = InnoDB 子句必须作为 CREATE TABLESPACE 语句的一部分定义，或者 InnoDB 必须被定义为默认存储引擎 (default_storage_engine=InnoDB)。

将表添加到通用表空间

创建通用表空间后，可以使用 CREATE TABLE *tbl_name* ... TABLESPACE [=] *tablespace_name* 或 ALTER TABLE *tbl_name* TABLESPACE [=] *tablespace_name* 语句将表添加到表空间中，如下例所示：

CREATE TABLE:

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1;

ALTER TABLE:

mysql> ALTER TABLE t2 TABLESPACE ts1;

注意

在 MySQL 5.7.24 中弃用了向共享表空间添加表分区的支持，并在 MySQL 8.0.13 中移除。共享表空间包括InnoDB系统表空间和通用表空间。

有关详细的语法信息，请参阅CREATE TABLE和ALTER TABLE。

通用表空间行格式支持

通用表空间支持所有表行格式（REDUNDANT，COMPACT，DYNAMIC，COMPRESSED），但由于不同的物理页大小，压缩表和非压缩表不能共存于同一通用表空间中。

要使通用表空间包含压缩表（ROW_FORMAT=COMPRESSED），必须指定FILE_BLOCK_SIZE选项，并且FILE_BLOCK_SIZE值必须是相对于innodb_page_size值的有效压缩页大小。此外，压缩表的物理页大小（KEY_BLOCK_SIZE）必须等于FILE_BLOCK_SIZE/1024。例如，如果innodb_page_size=16KB和FILE_BLOCK_SIZE=8K，则表的KEY_BLOCK_SIZE必须为 8。

以下表显示了允许的innodb_page_size、FILE_BLOCK_SIZE和KEY_BLOCK_SIZE组合。FILE_BLOCK_SIZE的值也可以用字节指定。要确定给定FILE_BLOCK_SIZE的有效KEY_BLOCK_SIZE值，将FILE_BLOCK_SIZE值除以 1024。32K 和 64K 的InnoDB页大小不支持表压缩。有关KEY_BLOCK_SIZE的更多信息，请参阅CREATE TABLE和第 17.9.1.2 节，“创建压缩表”。

表 17.3 压缩表允许的页大小、FILE_BLOCK_SIZE 和 KEY_BLOCK_SIZE 组合

InnoDB 页大小（innodb_page_size）	允许的 FILE_BLOCK_SIZE 值	允许的 KEY_BLOCK_SIZE 值
64KB	64K (65536)	不支持压缩
32KB	32K (32768)	不支持压缩
16KB	16K (16384)	无。如果innodb_page_size等于FILE_BLOCK_SIZE，则表空间不能包含压缩表。
16KB	8K (8192)	8
16KB	4K (4096)	4
16KB	2K (2048)	2
16KB	1K (1024)	1
8KB	8K (8192)	无。如果innodb_page_size等于FILE_BLOCK_SIZE，则表空间不能包含压缩表。
8KB	4K (4096)	4
8KB	2K (2048)	2
8KB	1K (1024)	1
4KB	4K (4096)	无。如果innodb_page_size等于FILE_BLOCK_SIZE，则表空间不能包含压缩表。
4KB	2K (2048)	2
4KB	1K (1024)	1
InnoDB 页面大小（innodb_page_size）	允许的 FILE_BLOCK_SIZE 值	允许的 KEY_BLOCK_SIZE 值

此示例演示了创建通用表空间并添加压缩表。该示例假定默认的innodb_page_size为 16KB。FILE_BLOCK_SIZE为 8192 要求压缩表具有 8 的KEY_BLOCK_SIZE。

mysql> CREATE TABLESPACE `ts2` ADD DATAFILE 'ts2.ibd' FILE_BLOCK_SIZE = 8192 Engine=InnoDB;

mysql> CREATE TABLE t4 (c1 INT PRIMARY KEY) TABLESPACE ts2 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

在创建通用表空间时如果不指定FILE_BLOCK_SIZE，FILE_BLOCK_SIZE默认为innodb_page_size。当FILE_BLOCK_SIZE等于innodb_page_size时，表空间只能包含具有未压缩行格式（COMPACT，REDUNDANT和DYNAMIC行格式）的表。

使用 ALTER TABLE 在表空间之间移动表

使用带有TABLESPACE选项的ALTER TABLE可以将表移动到现有的通用表空间，新的每个表的文件表空间，或系统表空间。

注意

在 MySQL 5.7.24 中，支持将表分区放置在共享表空间中的功能已被弃用，并在 MySQL 8.0.13 中移除。共享表空间包括InnoDB系统表空间和通用表空间。

要将表从每个表的文件表空间或系统表空间移动到通用表空间，请指定通用表空间的名称。通用表空间必须存在。有关更多信息，请参阅ALTER TABLESPACE。

ALTER TABLE tbl_name TABLESPACE [=] *tablespace_name*;

要将表从通用表空间或每个表的文件表空间移动到系统表空间，请将innodb_system指定为表空间名称。

ALTER TABLE tbl_name TABLESPACE [=] innodb_system;

要将表从系统表空间或通用表空间移动到每个表的文件表空间，请将innodb_file_per_table指定为表空间名称。

ALTER TABLE tbl_name TABLESPACE [=] innodb_file_per_table;

ALTER TABLE ... TABLESPACE操作会导致完整的表重建，即使TABLESPACE属性与其先前值相同也是如此。

ALTER TABLE ... TABLESPACE语法不支持将表从临时表空间移动到持久表空间。

DATA DIRECTORY子句允许与CREATE TABLE ... TABLESPACE=innodb_file_per_table结合使用，但否则不支持与TABLESPACE选项结合使用。截至 MySQL 8.0.21，DATA DIRECTORY子句中指定的目录必须为InnoDB所知。有关更多信息，请参阅使用 DATA DIRECTORY 子句。

在从加密表空间移动表时会有一些限制。请参阅加密限制。

重命名通用表空间

支持使用ALTER TABLESPACE ... RENAME TO语法重命名通用表空间。

ALTER TABLESPACE s1 RENAME TO s2;

重命名通用表空间需要 CREATE TABLESPACE 权限。

无论 autocommit 设置如何，RENAME TO 操作都会隐式在 autocommit 模式下执行。

在对驻留在表空间中的表执行 LOCK TABLES 或 FLUSH TABLES WITH READ LOCK 时，无法执行 RENAME TO 操作。

在重命名表空间时，对通用表空间中的表采取排他性 元数据锁，防止并发的 DDL。支持并发的 DML。

删除通用表空间

DROP TABLESPACE 语句用于删除一个 InnoDB 通用表空间。

在进行 DROP TABLESPACE 操作之前，必须从表空间中删除所有表。如果表空间不为空，DROP TABLESPACE 将返回错误。

使用类似以下查询来识别通用表空间中的表。

mysql> SELECT a.NAME AS space_name, b.NAME AS table_name FROM INFORMATION_SCHEMA.INNODB_TABLESPACES a,
       INFORMATION_SCHEMA.INNODB_TABLES b WHERE a.SPACE=b.SPACE AND a.NAME LIKE 'ts1';
+------------+------------+
| space_name | table_name |
+------------+------------+
| ts1        | test/t1    |
| ts1        | test/t2    |
| ts1        | test/t3    |
+------------+------------+

当表空间中的最后一个表被删除时，通用 InnoDB 表空间不会自动删除。必须使用 DROP TABLESPACE *tablespace_name* 明确删除表空间。

通用表空间不属于任何特定数据库。DROP DATABASE 操作可以删除属于通用表空间的表，但无法删除表空间，即使 DROP DATABASE 操作删除了属于表空间的所有表。

与系统表空间类似，截断或删除存储在通用表空间中的表会在通用表空间的 .ibd 数据文件 中内部创建可用于新的 InnoDB 数据的空间。与在 DROP TABLE 操作期间删除文件-每表表空间时释放空间到操作系统不同。

此示例演示了如何删除一个 InnoDB 通用表空间。通用表空间 ts1 创建了一个单表。在删除表空间之前必须先删除表。

mysql> CREATE TABLESPACE `ts1` ADD DATAFILE 'ts1.ibd' Engine=InnoDB;

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) TABLESPACE ts1 Engine=InnoDB;

mysql> DROP TABLE t1;

mysql> DROP TABLESPACE ts1;

注意

*tablespace_name* 在 MySQL 中是区分大小写的标识符。

通用表空间限制

• 生成的或现有的表空间不能更改为通用表空间。

• 临时通用表空间的创建不受支持。

• 通用表空间不支持临时表。

• 与系统表空间类似，截断或删除存储在通用表空间中的表会在通用表空间的.ibd 数据文件内部创建可用于新的InnoDB数据的空闲空间。空间不会像 file-per-table 表空间那样释放回操作系统。

  此外，在共享表空间（通用表空间或系统表空间）中存在的表上进行表复制的ALTER TABLE操作可能会增加表空间使用的空间量。这些操作需要与表中的数据和索引一样多的额外空间。表复制的ALTER TABLE操作所需的额外空间不会像 file-per-table 表空间那样释放回操作系统。

• 不支持对属于通用表空间的表执行ALTER TABLE ... DISCARD TABLESPACE和ALTER TABLE ...IMPORT TABLESPACE操作。

• 在 MySQL 5.7.24 中弃用了将表分区放置在通用表空间中的支持，并在 MySQL 8.0.13 中将其移除。

• 在源和副本位于同一主机上的复制环境中，不支持ADD DATAFILE子句，因为这会导致源和副本在相同位置创建同名的表空间，这是不支持的。但是，如果省略ADD DATAFILE子句，则表空间将在数据目录中以唯一的生成文件名创建，这是允许的。

• 截至 MySQL 8.0.21，通用表空间不能在 undo 表空间目录（innodb_undo_directory）中创建，除非该目录被InnoDB所知。已知目录是由datadir、innodb_data_home_dir和innodb_directories变量定义的目录。

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

17.6.3.4 撤销表空间

撤销表空间包含撤销日志，这些日志是包含有关如何撤消事务对聚簇索引记录的最新更改的信息的记录集。

本节中以下主题中描述了撤销表空间：

• 默认撤销表空间

• 撤销表空间大小

• 添加撤销表空间

• 删除撤销表空间

• 移动撤销表空间

• 配置回滚段的数量

• 截断撤销表空间

• 撤销表空间状态变量

默认撤销表空间

当初始化 MySQL 实例时会创建两个默认的撤销表空间。默认的撤销表空间在初始化时创建，以提供回滚段的位置，这些回滚段必须在 SQL 语句被接受之前存在。支持自动截断撤销表空间需要至少两个撤销表空间。请参阅截断撤销表空间。

默认的撤销表空间是在由innodb_undo_directory变量定义的位置创建的。如果innodb_undo_directory变量未定义，则默认的撤销表空间将在数据目录中创建。默认的撤销表空间数据文件命名为undo_001和undo_002。数据字典中定义的相应撤销表空间名称为innodb_undo_001和innodb_undo_002。

截至 MySQL 8.0.14，可以使用 SQL 在运行时创建额外的撤销表空间。请参阅添加撤销表空间。

撤销表空间大小

在 MySQL 8.0.23 之前，撤销表空间的初始大小取决于innodb_page_size的值。对于默认的 16KB 页大小，初始撤销表空间文件大小为 10MiB。对于 4KB、8KB、32KB 和 64KB 页大小，初始撤销表空间文件大小分别为 7MiB、8MiB、20MiB 和 40MiB。从 MySQL 8.0.23 开始，初始撤销表空间大小通常为 16MiB。当通过截断操作创建新的撤销表空间时，初始大小可能会有所不同。在这种情况下，如果文件扩展大小大于 16MB，并且上一个文件扩展发生在最近一秒内，则新的撤销表空间将以innodb_max_undo_log_size变量定义的四分之一大小创建。

在 MySQL 8.0.23 之前，撤销表空间每次扩展四个区段。从 MySQL 8.0.23 开始，撤销表空间至少扩展 16MB。为了处理激进的增长，如果上一个文件扩展发生在不到 0.1 秒之前，则文件扩展大小加倍。文件扩展大小可以多次加倍，最多达到 256MB。如果上一个文件扩展发生在超过 0.1 秒之前，则文件扩展大小减半，这也可以多次发生，最少为 16MB。如果为撤销表空间定义了AUTOEXTEND_SIZE选项，则它将按照上述逻辑确定的扩展大小和AUTOEXTEND_SIZE设置中的较大值进行扩展。有关AUTOEXTEND_SIZE选项的信息，请参见 Section 17.6.3.9, “Tablespace AUTOEXTEND_SIZE Configuration”。

添加撤销表空间

由于长时间运行的事务可能导致撤销日志变得很大，创建额外的撤销表空间可以帮助防止单个撤销表空间变得过大。从 MySQL 8.0.14 开始，可以使用CREATE UNDO TABLESPACE语法在运行时创建额外的撤销表空间。

CREATE UNDO TABLESPACE *tablespace_name* ADD DATAFILE '*file_name*.ibu';

撤销表空间文件名必须具有.ibu扩展名。在定义撤销表空间文件名时，不允许指定相对路径。允许使用完全限定路径，但路径必须为InnoDB所知。已知路径是由innodb_directories变量定义的路径。建议使用唯一的撤销表空间文件名，以避免在移动或克隆数据时出现潜在的文件名冲突。

注意

在复制环境中，源和每个副本必须有自己的撤销表空间文件目录。将撤销表空间文件的创建复制到公共目录会导致文件名冲突。

在启动时，由innodb_directories变量定义的目录将被扫描以查找撤销表空间文件。（扫描还会遍历子目录。）由innodb_data_home_dir、innodb_undo_directory和datadir变量定义的目录将自动附加到innodb_directories值中，无论innodb_directories变量是否被显式定义。因此，撤销表空间可以位于任何这些变量定义的路径中。

如果撤销表空间文件名不包含路径，则撤销表空间将创建在由innodb_undo_directory变量定义的目录中。如果该变量未定义，则撤销表空间将创建在数据目录中。

注意

InnoDB恢复过程要求撤销表空间文件位于已知目录中。在重做恢复和其他数据文件打开之前，必须发现并打开撤销表空间文件，以允许未提交的事务和数据字典更改被回滚。在恢复之前找不到的撤销表空间无法使用，这可能导致数据库不一致。如果数据字典中已知的撤销表空间未找到，则在启动时会报告错误消息。已知目录要求还支持撤销表空间的可移植性。请参阅移动撤销表空间。

要在相对于数据目录的路径中创建撤销表空间，请将innodb_undo_directory变量设置为相对路径，并在创建撤销表空间时仅指定文件名。

要查看撤销表空间的名称和路径，请查询INFORMATION_SCHEMA.FILES：

SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES
  WHERE FILE_TYPE LIKE 'UNDO LOG';

一个 MySQL 实例支持最多 127 个撤销表空间，包括 MySQL 实例初始化时创建的两个默认撤销表空间。

注意

在 MySQL 8.0.14 之前，通过配置innodb_undo_tablespaces启动变量来创建额外的撤销表空间。从 MySQL 8.0.14 开始，此变量已被弃用，不再可配置。

在 MySQL 8.0.14 之前，增加innodb_undo_tablespaces设置会���建指定数量的撤销表空间，并将它们添加到活动撤销表空间列表中。减少innodb_undo_tablespaces设置会从活动撤销表空间列表中删除撤销表空间。从活动列表中删除的撤销表空间会保持活动状态，直到它们不再被现有事务使用。可以使用SET语句在运行时配置innodb_undo_tablespaces变量，也��以在配置文件中定义。

在 MySQL 8.0.14 之前，停用的撤销表空间无法删除。在缓慢关闭后可以手动删除撤销表空间文件，但不建议这样做，因为停用的撤销表空间在服务器重新启动后可能会在一段时间内包含活动的撤销日志，如果在关闭服务器时存在未完成的事务。从 MySQL 8.0.14 开始，可以使用DROP UNDO TABALESPACE语法删除撤销表空间。请参阅 Dropping Undo Tablespaces。

删除撤销表空间

截至 MySQL 8.0.14 版本，使用CREATE UNDO TABLESPACE语法创建的撤销表空间可以使用DROP UNDO TABALESPACE语法在运行时删除。

在删除撤销表空间之前，撤销表空间必须为空。要清空撤销表空间，必须首先使用ALTER UNDO TABLESPACE语法将撤销表空间标记为非活动状态，以便该表空间不再用于为新事务分配回滚段。

ALTER UNDO TABLESPACE *tablespace_name* SET INACTIVE;

将撤销表空间标记为非活动状态后，当前在撤销表空间中使用回滚段的事务被允许完成，以及在这些事务完成之前启动的任何事务。事务完成后，清除系统释放撤销表空间中的回滚段，并将撤销表空间截断为其初始大小。（在截断撤销表空间时使用相同的过程。请参阅 Truncating Undo Tablespaces。）一旦撤销表空间为空，就可以删除它。

DROP UNDO TABLESPACE *tablespace_name*;

注意

或者，如果需要，可以将撤销表空间保留为空状态，并在以后重新激活，方法是发出ALTER UNDO TABLESPACE *tablespace_name* SET ACTIVE语句。

撤销表空间的状态可以通过查询信息模式INNODB_TABLESPACES表来监视。

SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
  WHERE NAME LIKE '*tablespace_name*';

一个inactive状态表示回滚段在撤销表空间中不再被新事务使用。一个empty状态表示一个撤销表空间是空的，可以被删除，或者可以使用ALTER UNDO TABLESPACE *tablespace_name* SET ACTIVE语句重新激活。尝试删除一个非空的撤销表空间会返回错误。

当 MySQL 实例初始化时创建的默认撤销表空间（innodb_undo_001和innodb_undo_002）不能被删除。但是，它们可以通过使用ALTER UNDO TABLESPACE *tablespace_name* SET INACTIVE语句设置为非活动状态。在默认撤销表空间可以设置为非活动状态之前，必须有一个撤销表空间来替代它。始终需要至少两个活动的撤销表空间来支持自动截断撤销表空间。

移动撤销表空间

使用CREATE UNDO TABLESPACE语法创建的撤销表空间可以在服务器离线状态下移动到任何已知目录。已知目录是由innodb_directories变量定义的目录。由innodb_data_home_dir、innodb_undo_directory和datadir定义的目录会自动附加到innodb_directories值中，无论innodb_directories变量是否被显式定义。这些目录及其子目录在启动时会被扫描以查找撤销表空间文件。移动到这些目录中的撤销表空间文件在启动时会被发现，并假定为已移动的撤销表空间。

当 MySQL 实例初始化时创建的默认撤销表空间（innodb_undo_001和innodb_undo_002）必须位于由innodb_undo_directory变量定义的目录中。如果innodb_undo_directory变量未定义，则默认撤销表空间位于数据目录中。如果在服务器离线状态下移动默认撤销表空间，则必须使用配置为新目录的innodb_undo_directory变量启动服务器。

撤销日志的 I/O 模式使撤销表空间成为 SSD 存储的良好选择。

配置回滚段的数量

innodb_rollback_segments变量定义了分配给每个撤销表空间和全局临时表空间的回滚段的数量。innodb_rollback_segments变量可以在启动时或服务器运行时进行配置。

innodb_rollback_segments的默认设置为 128，这也是最大值。有关回滚段支持的事务数量的信息，请参见第 17.6.6 节，“撤销日志”。

截断撤销表空间

有两种截断撤销表空间的方法，可以单独使用或结合使用来管理撤销表空间的大小。一种方法是自动的，使用配置变量启用。另一种方法是手动的，使用 SQL 语句执行。

自动方法不需要监视撤销表空间的大小，并且一旦启用，它会执行撤销表空间的停用、截断和重新激活，无需手动干预。如果您希望控制何时将撤销表空间脱机进行截断，则可能更喜欢手动截断方法。例如，您可能希望避免在高负载时间截断撤销表空间。

自动截断

自动截断撤销表空间需要至少两个活动撤销表空间，这确保了一个撤销表空间保持活动状态，而另一个被脱机进行截断。默认情况下，在初始化 MySQL 实例时会创建两个撤销表空间。

要自动截断撤销表空间，请启用innodb_undo_log_truncate变量。例如：

mysql> SET GLOBAL innodb_undo_log_truncate=ON;

当启用innodb_undo_log_truncate变量时，超过innodb_max_undo_log_size变量定义的大小限制的撤销表空间将被截断。innodb_max_undo_log_size变量是动态的，默认值为 1073741824 字节（1024 MiB）。

mysql> SELECT @@innodb_max_undo_log_size;
+----------------------------+
| @@innodb_max_undo_log_size |
+----------------------------+
|                 1073741824 |
+----------------------------+

当启用innodb_undo_log_truncate变量时：

1. 超过innodb_max_undo_log_size设置的默认和用户定义的撤销表空间将被标记为截断。选择要截断的撤销表空间是循环进行的，以避免每次截断相同的撤销表空间。

2. 位于所选撤销表空间中的回滚段被设置为不活动，以便它们不分配给新事务。当前正在使用回滚段的现有事务被允许完成。

3. 清除系统通过释放不再使用的撤销日志来清空回滚段。

4. 在撤销表空间中的所有回滚段被释放后，截断操作运行并将撤销表空间截断至其初始大小。

   截断操作后的撤销表空间大小可能比初始大小大，因为操作完成后立即使用。

   innodb_undo_directory变量定义默认撤销表空间文件的位置。如果未定义innodb_undo_directory变量，则默认撤销表空间位于数据目录中。包括使用CREATE UNDO TABLESPACE语法创建的用户定义撤销表空间在内的所有撤销表空间文件的位置可以通过查询信息模式FILES表来确定：

   SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE LIKE 'UNDO LOG';
   

5. 回滚段被重新激活，以便分配给新事务。

手动截断

手动截断撤销表空间需要至少三个活动的撤销表空间。始终需要两个活动的撤销表空间来支持自动截断的可能性。三个撤销表空间的最低要求满足此要求，同时允许手动将撤销表空间下线。

要手动启动撤销表空间的截断，请发出以下语句：

ALTER UNDO TABLESPACE *tablespace_name* SET INACTIVE;

撤销表空间标记为不活动后，当前正在使用撤销表空间中回滚段的事务被允许完成，以及在这些事务完成之前启动的任何事务。事务完成后，清除系统释放撤销表空间中的回滚段，撤销表空间被截断至其初始大小，并且撤销表空间状态从inactive变为empty。

注意

当ALTER UNDO TABLESPACE *tablespace_name* SET INACTIVE语句停用撤销表空间时，清除线程会在下一个机会查找该撤销表空间。一旦找到撤销表空间并标记为截断，清除线程会以增加的频率返回，以快速清空和截断撤销表空间。

要检查撤销表空间的状态，请查询信息模式INNODB_TABLESPACES表。

SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
  WHERE NAME LIKE '*tablespace_name*';

一旦撤销表空间处于empty状态，可以通过发出以下语句重新激活：

ALTER UNDO TABLESPACE *tablespace_name* SET ACTIVE;

处于空状态的撤消表空间也可以被删除。请参阅删除撤消表空间。

加快自动截断撤消表空间

清除线程负责清空和截断撤消表空间。默认情况下，清除线程在调用清除的 128 次中查找撤消表空间以截断一次。清除线程查找撤消表空间以截断的频率由innodb_purge_rseg_truncate_frequency变量控制，默认设置为 128。

mysql> SELECT @@innodb_purge_rseg_truncate_frequency;
+----------------------------------------+
| @@innodb_purge_rseg_truncate_frequency |
+----------------------------------------+
|                                    128 |
+----------------------------------------+

要增加频率，请降低innodb_purge_rseg_truncate_frequency设置。例如，为了使清除线程在调用清除的 32 次中查找撤消表空间一次，将innodb_purge_rseg_truncate_frequency设置为 32。

mysql> SET GLOBAL innodb_purge_rseg_truncate_frequency=32;

截断撤消表空间文件的性能影响

当撤消表空间被截断时，撤消表空间中的回滚段将被停用。其他撤消表空间中的活动回滚段将负责整个系统负载，这可能导致轻微的性能下降。性能受影响的程度取决于多个因素：

• 撤消表空间数量

• 撤消日志数量

• 撤消表空间大小

• I/O 子系统的速度

• 现有的长时间运行事务

• 系统负载

避免潜在性能影响的最简单方法是增加撤消表空间的数量。

监控撤消表空间截断

从 MySQL 8.0.16 开始，提供了用于监视与撤消日志截断相关的后台活动的undo和purge子系统计数器。有关计数器名称和描述，请查询信息模式INNODB_METRICS表。

SELECT NAME, SUBSYSTEM, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%truncate%';

有关启用计数器和查询计数器数据的信息，请参阅第 17.15.6 节，“InnoDB INFORMATION_SCHEMA Metrics Table”。

撤消表空间截断限制

从 MySQL 8.0.21 开始，同一撤消表空间在检查点之间的截断操作次数限制为 64 次。该限制防止由于在繁忙系统上设置了过低的innodb_max_undo_log_size而导致的过多撤消表空间截断操作可能引起的潜在问题。如果超过限制，撤消表空间仍然可以变为非活动状态，但直到下一个检查点之后才会被截断。在 MySQL 8.0.22 中，该限制从 64 提高到了 50,000。

撤消表空间截断恢复

撤销表空间截断操作会在服务器日志目录中创建一个临时undo_*space_number*_trunc.log文件。该日志目录由innodb_log_group_home_dir定义。如果在截断操作期间发生系统故障，临时日志文件允许启动过程识别正在被截断的撤销表空间，并继续操作。

撤销表空间状态变量

以下状态变量允许跟踪总撤销表空间数、隐式（InnoDB创建的）撤销表空间数、显式（用户创建的）撤销表空间数以及活动撤销表空间数：

mysql> SHOW STATUS LIKE 'Innodb_undo_tablespaces%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| Innodb_undo_tablespaces_total    | 2     |
| Innodb_undo_tablespaces_implicit | 2     |
| Innodb_undo_tablespaces_explicit | 0     |
| Innodb_undo_tablespaces_active   | 2     |
+----------------------------------+-------+

查看状态变量描述，请参阅第 7.1.10 节，“服务器状态变量”。

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

17.6.3.5 临时表空间

InnoDB 使用会话临时表空间和全局临时表空间。

会话临时表空间

会话临时表空间存储用户创建的临时表和优化器创建的内部临时表，当 InnoDB 配置为磁盘上内部临时表的存储引擎时。从 MySQL 8.0.16 开始，用于磁盘上内部临时表的存储引擎是 InnoDB。（以前，存储引擎由 internal_tmp_disk_storage_engine 的值确定。）

会话临时表空间从临时表空间池中分配给会话，当首次请求创建磁盘上的临时表时。每个会话最多分配两个表空间，一个用于用户创建的临时表，另一个用于优化器创建的内部临时表。分配给会话的临时表空间用于会话创建的所有磁盘上的临时表。会话断开连接时，其临时表空间被截断并释放回池中。服务器启动时创建一个包含 10 个临时表空间的池。池的大小永远不会缩小，表空间会根据需要自动添加到池中。会话临时表空间在正常关闭或初始化中止时被移除。会话临时表空间文件在创建时为五个页面大小，并具有 .ibt 文件扩展名。

为会话临时表空间保留了 40 万个空间 ID 范围。因为会话临时表空间池每次服务器启动时都会重新创建，所以会话临时表空间的空间 ID 在服务器关闭时不会持久化，并且可能被重用。

innodb_temp_tablespaces_dir 变量定义了会话临时表空间创建的位置。默认位置是数据目录中的 #innodb_temp 目录。如果无法创建临时表空间池，则拒绝启动。

$> cd *BASEDIR*/data/#innodb_temp
$> ls
temp_10.ibt  temp_2.ibt  temp_4.ibt  temp_6.ibt  temp_8.ibt
temp_1.ibt   temp_3.ibt  temp_5.ibt  temp_7.ibt  temp_9.ibt

在基于语句的复制（SBR）模式下，在副本上创建的临时表位于一个会话临时表空间中，该表空间仅在 MySQL 服务器关闭时截断。

INNODB_SESSION_TEMP_TABLESPACES 表提供有关会话临时表空间的元数据。

信息模式 INNODB_TEMP_TABLE_INFO 表提供有关在 InnoDB 实例中活动的用户创建的临时表的元数据。

全局临时表空间

全局临时表空间（ibtmp1）存储对用户创建的临时表所做更改的回滚段。

innodb_temp_data_file_path变量定义了全局临时表空间数据文件的相对路径、名称、大小和属性。如果未为innodb_temp_data_file_path指定任何值，则默认行为是在innodb_data_home_dir目录中创建一个名为ibtmp1的单个自动扩展数据文件。初始文件大小略大于 12MB。

全局临时表空间在正常关闭或中止初始化时被移除，并在每次服务器启动时重新创建。全局临时表空间在创建时接收一个动态生成的空间 ID。如果无法创建全局临时表空间，则拒绝启动。如果服务器意外停止，则不会移除全局临时表空间。在这种情况下，数据库管理员可以手动移除全局临时表空间或重新启动 MySQL 服务器。重新启动 MySQL 服务器会自动移除并重新创建全局临时表空间。

全局临时表空间不能位于原始设备上。

信息模式FILES表提供有关全局临时表空间的元数据。发出类似以下查询以查看全局临时表空间元数据：

mysql> SELECT * FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME='innodb_temporary'\G

默认情况下，全局临时表空间数据文件是自动扩展的，并根据需要增加大小。

要确定全局临时表空间数据文件是否自动扩展，请检查innodb_temp_data_file_path设置：

mysql> SELECT @@innodb_temp_data_file_path;
+------------------------------+
| @@innodb_temp_data_file_path |
+------------------------------+
| ibtmp1:12M:autoextend        |
+------------------------------+

要检查全局临时表空间数据文件的大小，请使用类似以下查询检查信息模式FILES表：

mysql> SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE
       AS TotalSizeBytes, DATA_FREE, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES
       WHERE TABLESPACE_NAME = 'innodb_temporary'\G
*************************** 1\. row ***************************
      FILE_NAME: ./ibtmp1
TABLESPACE_NAME: innodb_temporary
         ENGINE: InnoDB
   INITIAL_SIZE: 12582912
 TotalSizeBytes: 12582912
      DATA_FREE: 6291456
   MAXIMUM_SIZE: NULL

TotalSizeBytes显示全局临时表空间数据文件的当前大小。有关其他字段值的信息，请参阅第 28.3.15 节，“信息模式 FILES 表”。

或者，在操作系统上检查全局临时表空间数据文件大小。全局临时表空间数据文件位于由innodb_temp_data_file_path变量定义的目录中。

要回收全局临时表空间数据文件占用的磁盘空间，重新启动 MySQL 服务器。重新启动服务器会根据innodb_temp_data_file_path定义的属性删除并重新创建全局临时表空间数据文件。

为了限制全局临时表空间数据文件的大小，配置innodb_temp_data_file_path以指定最大文件大小。例如：

[mysqld]
innodb_temp_data_file_path=ibtmp1:12M:autoextend:max:500M

配置innodb_temp_data_file_path需要重新启动服务器。

原文：dev.mysql.com/doc/refman/8.0/en/innodb-moving-data-files-offline.html

17.6.3.6 在服务器离线时移动表空间文件

定义在启动时用于扫描表空间文件的innodb_directories变量支持在服务器离线时将表空间文件移动或恢复到新位置。在启动过程中，发现的表空间文件将被用于数据字典中引用的文件，并且数据字典将被更新以引用已重新定位的文件。如果扫描发现重复的表空间文件，则启动将失败，并显示错误，指示找到了相同表空间 ID 的多个文件。

由innodb_data_home_dir、innodb_undo_directory和datadir变量定义的目录会自动附加到innodb_directories参数值中。这些目录在启动时会被扫描，无论是否明确指定了innodb_directories设置。这些目录的隐式添加允许移动系统表空间文件、数据目录或撤销表空间文件，而无需配置innodb_directories设置。但是，当目录发生变化时，必须更新设置。例如，在重新定位数据目录后，必须在重新启动服务器之前更新--datadir设置。

innodb_directories变量可以在启动命令或 MySQL 选项文件中指定。由于分号（;）被一些命令解释器解释为特殊字符，因此在参数值周围使用引号。 （例如，Unix shell 将其视为命令终止符。）

启动命令：

mysqld --innodb-directories="*directory_path_1*;*directory_path_2*"

MySQL 选项文件：

[mysqld]
innodb_directories="*directory_path_1*;*directory_path_2*"

以下过程适用于移动单个 file-per-table 和 general tablespace 文件、system tablespace 文件、undo tablespace 文件或数据目录。在移动文件或目录之前，请查看以下使用说明。

1. 停止服务器。

2. 将表空间文件或目录移动到所需位置。

3. 使InnoDB知道新目录。

   • 如果移动单个 file-per-table 或通用表空间文件，请将未知目录添加到innodb_directories的值中。

     • 由innodb_data_home_dir、innodb_undo_directory和datadir变量定义的目录会自动附加到innodb_directories参数值中，因此无需指定这些目录。

     • 一个 file-per-table 表空间文件只能移动到与模式同名的目录中。例如，如果actor表属于sakila模式，则actor.ibd数据文件只能移动到名为sakila的目录中。

     • 通用表空间文件不能移动到数据目录或数据目录的子目录中。

   • 如果移动系统表空间文件、撤销表空间或数据目录，请根据需要更新innodb_data_home_dir、innodb_undo_directory和datadir设置。

4. 重新启动服务器。

使用说明

• 不能在innodb_directories参数值中使用通配符表达式。

• innodb_directories扫描还会遍历指定目录的子目录。重复的目录和子目录将从要扫描的目录列表中丢弃。

• innodb_directories支持移动InnoDB表空间文件。不支持移动属于InnoDB以外存储引擎的文件。当移动整个数据目录时，此限制也适用。

• innodb_directories支持在将文件移动到扫描目录时重命名表空间文件。它还支持将表空间文件移动到其他支持的操作系统。

• 在将表空间文件移动到不同操作系统时，请确保表空间文件名不包含目标系统上具有特殊含义或特殊含义的字符。

• 将数据目录从 Windows 操作系统移动到 Linux 操作系统时，请修改二进制日志文件路径在二进制日志索引文件中使用反斜杠而不是正斜杠。默认情况下，二进制日志索引文件与二进制日志文件具有相同的基本名称，扩展名为'.index'。二进制日志索引文件的位置由--log-bin定义。默认位置是数据目录。

• 如果将表空间文件移动到不同操作系统会引入跨平台复制，那么数据库管理员有责任确保包含特定平台目录的 DDL 语句的正确复制。允许指定目录的语句包括CREATE TABLE ... DATA DIRECTORY和CREATE TABLESPACE ... ADD DATAFILE。

• 将使用绝对路径或位于数据目录之外的位置创建的文件-每表和通用表空间的目录添加到innodb_directories设置中。否则，在恢复过程中，InnoDB将无法定位这些文件。有关更多信息，请参阅崩溃恢复期间的表空间发现。

  要查看表空间文件位置，请查询信息模式FILES表：

  mysql> SELECT TABLESPACE_NAME, FILE_NAME FROM INFORMATION_SCHEMA.FILES \G
  

原文：dev.mysql.com/doc/refman/8.0/en/innodb-disabling-tablespace-path-validation.html

17.6.3.7 禁用表空间路径验证

在启动时，InnoDB 会扫描由 innodb_directories 变量定义的目录，以查找表空间文件。发现的表空间文件的路径会与数据字典中记录的路径进行验证。如果路径不匹配，则会更新数据字典中的路径。

innodb_validate_tablespace_paths 变量是在 MySQL 8.0.21 中引入的，允许禁用表空间路径验证。此功能适用于表空间文件未移动的环境。禁用路径验证可以提高在具有大量表空间文件的系统上的启动时间。如果 log_error_verbosity 设置为 3，在禁用表空间路径验证时启动时会打印以下消息：

[InnoDB] Skipping InnoDB tablespace path validation. 
Manually moved tablespace files will not be detected!

警告

在移动表空间文件后以禁用表空间路径验证的方式启动服务器可能导致未定义的行为。

原文：dev.mysql.com/doc/refman/8.0/en/innodb-optimize-tablespace-page-allocation.html

17.6.3.8 优化 Linux 上的表空间空间分配

截至 MySQL 8.0.22，您可以优化InnoDB在 Linux 上为文件表和通用表空间分配空间的方式。默认情况下，当需要额外空间时，InnoDB会为表空间分配页面，并在这些页面上物理写入 NULL。如果频繁分配新页面，此行为可能会影响性能。从 MySQL 8.0.22 开始，您可以在 Linux 系统上禁用innodb_extend_and_initialize以避免在新分配的表空间页面上物理写入 NULL。当禁用innodb_extend_and_initialize时，空间将使用posix_fallocate()调用分配给表空间文件，而无需物理写入 NULL。

当使用posix_fallocate()调用分配页面时，默认情况下扩展大小较小，通常一次只分配几个页面，这可能导致碎片化并增加随机 I/O。为避免此问题，在启用posix_fallocate()调用时增加表空间扩展大小。可以使用AUTOEXTEND_SIZE选项将表空间扩展大小增加到 4GB。更多信息，请参见第 17.6.3.9 节，“表空间 AUTOEXTEND_SIZE 配置”。

InnoDB在分配新表空间页面之前写入重做日志记录。如果页面分配操作中断，则在恢复期间从重做日志记录中重放该操作。 （从重做日志记录中重放的页面分配操作会在新分配的页面上物理写入 NULL。）无论innodb_extend_and_initialize设置如何，都会在分配页面之前写入重做日志记录。

在非 Linux 系统和 Windows 上，InnoDB将新页面分配给表空间并在这些页面上物理写入 NULL，这是默认行为。在这些系统上尝试禁用innodb_extend_and_initialize将返回以下错误：

在此平台上不支持更改 innodb_extend_and_initialize。回退到默认设置。

原文：dev.mysql.com/doc/refman/8.0/en/innodb-tablespace-autoextend-size.html

17.6.3.9 表空间 AUTOEXTEND_SIZE 配置

默认情况下，当每个表文件或通用表空间需要额外空间时，表空间根据以下规则逐步扩展：

• 如果表空间小于一个区段的大小，则每次扩展一个页面。

• 如果表空间大于 1 个区段但小于 32 个区段，则每次扩展一个区段。

• 如果表空间大于 32 个区段，则每次扩展四个区段。

有关区段大小的信息，请参阅 Section 17.11.2, “File Space Management”。

从 MySQL 8.0.23 开始，可以通过指定AUTOEXTEND_SIZE选项来配置每个表文件或通用表空间的扩展量。配置更大的扩展大小可以帮助避免碎片化，并促进大量数据的摄入。

要为每个表文件表空间配置扩展大小，请在CREATE TABLE或ALTER TABLE语句中指定AUTOEXTEND_SIZE大小：

CREATE TABLE t1 (c1 INT) AUTOEXTEND_SIZE = 4M;

ALTER TABLE t1 AUTOEXTEND_SIZE = 8M;

要为通用表空间配置扩展大小，请在CREATE TABLESPACE或ALTER TABLESPACE语句中指定AUTOEXTEND_SIZE大小：

CREATE TABLESPACE ts1 AUTOEXTEND_SIZE = 4M;

ALTER TABLESPACE ts1 AUTOEXTEND_SIZE = 8M;

注意

AUTOEXTEND_SIZE选项也可用于创建撤销表空间，但是撤销表空间的扩展行为有所不同。有关更多信息，请参阅 Section 17.6.3.4, “Undo Tablespaces”。

AUTOEXTEND_SIZE设置必须是 4M 的倍数。指定不是 4M 的倍数的AUTOEXTEND_SIZE设置会返回错误。

AUTOEXTEND_SIZE的默认设置为 0，这会导致表空间根据上述默认行为进行扩展。

允许的最大AUTOEXTEND_SIZE为 4GB。表空间的最大大小在 Section 17.22, “InnoDB Limits”中有描述。

最小的AUTOEXTEND_SIZE设置取决于InnoDB页面大小，如下表所示：

InnoDB 页面大小	最小 AUTOEXTEND_SIZE
4K	4M
8K	4M
16K	4M
32K	8M
64K	16M

默认的InnoDB页面大小为 16K（16384 字节）。要确定 MySQL 实例的InnoDB页面大小，请查询innodb_page_size设置：

mysql> SELECT @@GLOBAL.innodb_page_size;
+---------------------------+
| @@GLOBAL.innodb_page_size |
+---------------------------+
|                     16384 |
+---------------------------+

当更改表空间的AUTOEXTEND_SIZE设置后，随后发生的第一个扩展会将表空间大小增加到AUTOEXTEND_SIZE设置的倍数。随后的扩展将按配置的大小进行。

当使用非零 AUTOEXTEND_SIZE 设置创建文件表空间或通用表空间时，表空间将以指定的 AUTOEXTEND_SIZE 大小初始化。

不能使用 ALTER TABLESPACE 来配置文件表空间的 AUTOEXTEND_SIZE。必须使用 ALTER TABLE。

对于在文件表空间中创建的表，SHOW CREATE TABLE 仅在将 AUTOEXTEND_SIZE 配置为非零值时显示该选项。

要确定任何 InnoDB 表空间的 AUTOEXTEND_SIZE，请查询信息模式 INNODB_TABLESPACES 表。例如：

mysql> SELECT NAME, AUTOEXTEND_SIZE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES 
       WHERE NAME LIKE 'test/t1';
+---------+-----------------+
| NAME    | AUTOEXTEND_SIZE |
+---------+-----------------+
| test/t1 |         4194304 |
+---------+-----------------+

mysql> SELECT NAME, AUTOEXTEND_SIZE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES 
       WHERE NAME LIKE 'ts1';
+------+-----------------+
| NAME | AUTOEXTEND_SIZE |
+------+-----------------+
| ts1  |         4194304 |
+------+-----------------+

注意

AUTOEXTEND_SIZE 的值为 0，这是默认设置，意味着表空间根据上述默认表空间扩展行为进行扩展。

17.6.4 双写缓冲区

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

双写缓冲区是InnoDB在将页面写入其在InnoDB数据文件中正确位置之前，从缓冲池刷新的页面写入的存储区域。如果在页面写入过程中发生操作系统、存储子系统或意外的mysqld进程退出，InnoDB可以在崩溃恢复期间从双写缓冲区找到页面的良好副本。

尽管数据被写入两次，但双写缓冲区并不需要两倍的 I/O 开销或两倍的 I/O 操作。数据以大块顺序写入双写缓冲区，通过单个fsync()调用到操作系统（除非innodb_flush_method设置为O_DIRECT_NO_FSYNC）。

在 MySQL 8.0.20 之前，双写缓冲区存储区域位于InnoDB系统表空间中。从 MySQL 8.0.20 开始，双写缓冲区存储区域位于双写文件中。

提供以下变量用于双写缓冲区配置：

• innodb_doublewrite

  innodb_doublewrite变量控制双写缓冲区是否启用。在大多数情况下，默认情况下启用双写缓冲区。要禁用双写缓冲区，请将innodb_doublewrite设置为OFF。例如，在执行基准测试时，如果您更关注性能而不是数据完整性，则考虑禁用双写缓冲区。

  从 MySQL 8.0.30 开始，innodb_doublewrite支持DETECT_AND_RECOVER和DETECT_ONLY设置。

  DETECT_AND_RECOVER设置与ON设置相同。使用此设置时，双写缓冲区完全启用，数据库页面内容被写入双写缓冲区，在恢复过程中访问以修复不完整的页面写入。

  使用DETECT_ONLY设置时，只有元数据被写入双写缓冲区。数据库页面内容不会被写入双写缓冲区，恢复过程也不会使用双写缓冲区来修复不完整的页面写入。这种轻量级设置仅用于检测不完整的页面写入。

  MySQL 8.0.30 及更高版本支持动态更改innodb_doublewrite设置，使双写缓冲区在ON、DETECT_AND_RECOVER和DETECT_ONLY之间切换。MySQL 不支持在启用双写缓冲区的设置和OFF之间进行动态更改，反之亦然。

  如果双写缓冲区位于支持原子写入的 Fusion-io 设备上，则双写缓冲区将自动禁用，并且数据文件写入将使用 Fusion-io 原子写入。但是，请注意innodb_doublewrite设置是全局的。当双写缓冲区被禁用时，所有数据文件都被禁用，包括那些不位于 Fusion-io 硬件上的文件。此功能仅受支持于 Fusion-io 硬件，并且仅在 Linux 上为 Fusion-io NVMFS 启用。为充分利用此功能，建议将innodb_flush_method设置为O_DIRECT。

• innodb_doublewrite_dir

  innodb_doublewrite_dir变量（在 MySQL 8.0.20 中引入）定义了InnoDB创建双写文件的目录。如果未指定目录，则双写文件将在innodb_data_home_dir目录中创建，默认情况下为数据目录。

  自动在指定目录名称前加上井号'#'，以避免与模式名称冲突。但是，如果在目录名称中明确指定了'.'、'#'或'/'前缀，则不会在目录名称前加上井号'#'。

  理想情况下，双写目录应放置在最快的存储介质上。

• innodb_doublewrite_files

  innodb_doublewrite_files变量定义了双写文件的数量。默认情况下，为每个缓冲池实例创建两个双写文件：一个刷新列表双写文件和一个 LRU 列表双写文件。

  刷新列表双写文件用于从缓冲池刷新列表刷新的页面。刷新列表双写文件的默认大小为InnoDB页面大小*双写页面字节。

  LRU 列表双写文件用于从缓冲池 LRU 列表刷新的页面。它还包含用于单页刷新的插槽。LRU 列表双写文件的默认大小为InnoDB页面大小*(双写页面+(512/缓冲池实例数))，其中 512 是为单页刷新保留的插槽总数。

  至少有两个双写文件。双写文件的最大数量是缓冲池实例数的两倍。（缓冲池实例数由innodb_buffer_pool_instances变量控制。）

  双写文件的命名格式如下：#ib_*page_size*_*file_number*.dblwr（或者使用DETECT_ONLY设置时为.bdblwr）。例如，对于一个InnoDB页大小为 16KB 且只有一个缓冲池的 MySQL 实例，会创建以下双写文件：

  #ib_16384_0.dblwr
  #ib_16384_1.dblwr
  

  innodb_doublewrite_files变量旨在用于高级性能调优。默认设置对大多数用户来说应该是合适的。

• innodb_doublewrite_pages

  innodb_doublewrite_pages变量（MySQL 8.0.20 版本引入）控制了每个线程的最大双写页数。如果未指定值，innodb_doublewrite_pages将设置为innodb_write_io_threads的值。该变量旨在用于高级性能调优。默认值对大多数用户来说应该是合适的。

• innodb_doublewrite_batch_size

  innodb_doublewrite_batch_size变量（MySQL 8.0.20 版本引入）控制了一批中要写入的双写页的数量。该变量旨在用于高级性能调优。默认值对大多数用户来说应该是合适的。

截至 MySQL 8.0.23 版本，InnoDB会自动加密属于加密表空间的双写文件页（参见第 17.13 节，“InnoDB 数据静态加密”）。同样，属于页压缩表空间的双写文件页会被压缩。因此，双写文件可以包含不同类型的页，包括未加密和未压缩的页，加密的页，压缩的页，以及既加密又压缩的页。

17.6.5 重做日志

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

重做日志是一种基于磁盘的数据结构，在崩溃恢复期间用于纠正由不完整事务写入的数据。在正常操作期间，重做日志对由 SQL 语句或低级 API 调用导致的更改表数据的请求进行编码。在意外关闭之前未完成更新数据文件的修改在初始化期间和在接受连接之前自动重放。有关重做日志在崩溃恢复中的作用的信息，请参见第 17.18.2 节，“InnoDB 恢复”。

重做日志在磁盘上以重做日志文件的形式物理表示。写入重做日志文件的数据以受影响的记录为编码，这些数据被统称为重做。数据通过重做日志文件的传递由一个不断增加的 LSN 值表示。重做日志数据在数据修改发生时追加，而最旧的数据在检查点进展时被截断。

有关重做日志的信息和程序在以下主题中描述：

• 配置重做日志容量（MySQL 8.0.30 或更高版本）

• 配置重做日志容量（MySQL 8.0.30 之前）

• 自动重做日志容量配置

• 重做日志归档

• 禁用重做日志记录

• 相关主题

配置重做日志容量（MySQL 8.0.30 或更高版本）

从 MySQL 8.0.30 开始，innodb_redo_log_capacity系统变量控制重做日志文件占用的磁盘空间量。您可以在启动时或运行时使用SET GLOBAL语句在选项文件中设置此变量；例如，以下语句将重做日志容量设置为 8GB：

SET GLOBAL innodb_redo_log_capacity = 8589934592;

在运行时设置时，配置更改会立即生效，但新限制可能需要一些时间才能完全实施。如果重做日志文件占用的空间少于指定值，则从缓冲池中更积极地刷新脏页到表空间数据文件，最终增加重做日志文件占用的磁盘空间。如果重做日志文件占用的空间多于指定值，则更积极地刷新脏页，最终减少重做日志文件占用的磁盘空间。

innodb_redo_log_capacity变量取代了已弃用的innodb_log_files_in_group和innodb_log_file_size变量。当定义了innodb_redo_log_capacity设置时，innodb_log_files_in_group和innodb_log_file_size设置将被忽略；否则，这些设置将用于计算innodb_redo_log_capacity设置（innodb_log_files_in_group * innodb_log_file_size = innodb_redo_log_capacity）。如果没有设置这些变量中的任何一个，重做日志容量将设置为innodb_redo_log_capacity的默认值，即 104857600 字节（100MB）。最大重做日志容量为 128GB。

重做日志文件位于数据目录中的#innodb_redo目录中，除非通过innodb_log_group_home_dir变量指定了不同的目录。如果定义了innodb_log_group_home_dir，则重做日志文件位于该目录中的#innodb_redo目录中。有两种类型的重做日志文件，普通和备用。普通的重做日志文件是正在使用的。备用的重做日志文件是等待使用的。InnoDB尝试总共维护 32 个重做日志文件，每个文件的大小等于 1/32 * innodb_redo_log_capacity；但是，在修改innodb_redo_log_capacity设置后，文件大小可能会有所不同。

重做日志文件使用#ib_redo*N*的命名约定，其中*N*是重做日志文件编号。备用的重做日志文件以_tmp后缀表示。以下示例显示了一个#innodb_redo目录中的重做日志文件，其中有 21 个活动重做日志文件和 11 个备用重做日志文件，按顺序编号。

'#ib_redo582'  '#ib_redo590'  '#ib_redo598'      '#ib_redo606_tmp'
'#ib_redo583'  '#ib_redo591'  '#ib_redo599'      '#ib_redo607_tmp'
'#ib_redo584'  '#ib_redo592'  '#ib_redo600'      '#ib_redo608_tmp'
'#ib_redo585'  '#ib_redo593'  '#ib_redo601'      '#ib_redo609_tmp'
'#ib_redo586'  '#ib_redo594'  '#ib_redo602'      '#ib_redo610_tmp'
'#ib_redo587'  '#ib_redo595'  '#ib_redo603_tmp'  '#ib_redo611_tmp'
'#ib_redo588'  '#ib_redo596'  '#ib_redo604_tmp'  '#ib_redo612_tmp'
'#ib_redo589'  '#ib_redo597'  '#ib_redo605_tmp'  '#ib_redo613_tmp'

每个普通的重做日志文件与特定范围的 LSN 值相关联；例如，以下查询显示了前面示例中列出的活动重做日志文件的START_LSN和END_LSN值：

mysql> SELECT FILE_NAME, START_LSN, END_LSN FROM performance_schema.innodb_redo_log_files;
+----------------------------+--------------+--------------+
| FILE_NAME                  | START_LSN    | END_LSN      |
+----------------------------+--------------+--------------+
| ./#innodb_redo/#ib_redo582 | 117654982144 | 117658256896 |
| ./#innodb_redo/#ib_redo583 | 117658256896 | 117661531648 |
| ./#innodb_redo/#ib_redo584 | 117661531648 | 117664806400 |
| ./#innodb_redo/#ib_redo585 | 117664806400 | 117668081152 |
| ./#innodb_redo/#ib_redo586 | 117668081152 | 117671355904 |
| ./#innodb_redo/#ib_redo587 | 117671355904 | 117674630656 |
| ./#innodb_redo/#ib_redo588 | 117674630656 | 117677905408 |
| ./#innodb_redo/#ib_redo589 | 117677905408 | 117681180160 |
| ./#innodb_redo/#ib_redo590 | 117681180160 | 117684454912 |
| ./#innodb_redo/#ib_redo591 | 117684454912 | 117687729664 |
| ./#innodb_redo/#ib_redo592 | 117687729664 | 117691004416 |
| ./#innodb_redo/#ib_redo593 | 117691004416 | 117694279168 |
| ./#innodb_redo/#ib_redo594 | 117694279168 | 117697553920 |
| ./#innodb_redo/#ib_redo595 | 117697553920 | 117700828672 |
| ./#innodb_redo/#ib_redo596 | 117700828672 | 117704103424 |
| ./#innodb_redo/#ib_redo597 | 117704103424 | 117707378176 |
| ./#innodb_redo/#ib_redo598 | 117707378176 | 117710652928 |
| ./#innodb_redo/#ib_redo599 | 117710652928 | 117713927680 |
| ./#innodb_redo/#ib_redo600 | 117713927680 | 117717202432 |
| ./#innodb_redo/#ib_redo601 | 117717202432 | 117720477184 |
| ./#innodb_redo/#ib_redo602 | 117720477184 | 117723751936 |
+----------------------------+--------------+--------------+

在执行检查点时，InnoDB将检查点 LSN 存储在包含此 LSN 的文件的头部。在恢复过程中，所有重做日志文件都会被检查，恢复从最新的检查点 LSN 开始。

提供了几个状态变量用于监视重做日志和重做日志容量调整操作；例如，您可以查询Innodb_redo_log_resize_status以查看调整操作的状态：

mysql> SHOW STATUS LIKE 'Innodb_redo_log_resize_status';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_redo_log_resize_status | OK    |
+-------------------------------+-------+

Innodb_redo_log_capacity_resized状态变量显示当前重做日志容量限制：

mysql> SHOW STATUS LIKE 'Innodb_redo_log_capacity_resized';
 +----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| Innodb_redo_log_capacity_resized | 104857600 |
+----------------------------------+-----------+

其他适用的状态变量包括：

• Innodb_redo_log_checkpoint_lsn

• Innodb_redo_log_current_lsn

• Innodb_redo_log_flushed_to_disk_lsn

• Innodb_redo_log_logical_size

• Innodb_redo_log_physical_size

• Innodb_redo_log_read_only

• Innodb_redo_log_uuid

请参考状态变量描述以获取更多信息。

您可以通过查询innodb_redo_log_files性能模式表查看有关活动重做日志文件的信息。以下查询检索所有表列的数据：

SELECT FILE_ID, START_LSN, END_LSN, SIZE_IN_BYTES, IS_FULL, CONSUMER_LEVEL 
FROM performance_schema.innodb_redo_log_files;

配置重做日志容量（MySQL 8.0.30 之前）

在 MySQL 8.0.30 之前，默认情况下，InnoDB在数据目录中创建两个重做日志文件，命名为ib_logfile0和ib_logfile1，并以循环方式写入这些文件。

修改重做日志容量需要更改重做日志文件的数量或大小，或两者兼而有之。

1. 停止 MySQL 服务器，并确保它在没有错误的情况下关闭。

2. 编辑my.cnf以更改重做日志文件配置。要更改重做日志文件大小，请配置innodb_log_file_size。要增加重做日志文件数量，请配置innodb_log_files_in_group。

3. 再次启动 MySQL 服务器。

如果InnoDB检测到innodb_log_file_size与重做日志文件大小不同，它会写入日志检查点，关闭并删除旧日志文件，以请求的大小创建新日志文件，并打开新日志文件。

自动重做日志容量配置

当启用innodb_dedicated_server时，InnoDB会自动配置某些InnoDB参数，包括重做日志容量。自动配置适用于驻留在专用于 MySQL 的服务器上的 MySQL 实例，其中 MySQL 服务器可以使用所有可用的系统资源。有关更多信息，请参见第 17.8.12 节，“为专用 MySQL 服务器启用自动配置”。

重做日志归档

备份工具有时可能在备份操作进行时未能跟上重做日志生成的速度，导致由于这些记录被覆盖而丢失重做日志记录。这个问题在备份操作期间有大量 MySQL 服务器活动，并且重做日志文件存储介质的运行速度比备份存储介质快时最常发生。MySQL 8.0.17 中引入的重做日志归档功能通过将重做日志记录顺序写入归档文件来解决这个问题，除了重做日志文件外。备份工具可以根据需要从归档文件中复制重做日志记录，从而避免数据的潜在丢失。

如果在服务器上配置了重做日志归档，MySQL 企业备份，可用于MySQL 企业版，在备份 MySQL 服务器时使用重做日志归档功能。

在服务器上启用重做日志归档需要为innodb_redo_log_archive_dirs系统变量设置一个值。该值被指定为带标签的重做日志归档目录的分号分隔列表。*label:directory*对由冒号(:)分隔。例如：

mysql> SET GLOBAL innodb_redo_log_archive_dirs='*label1*:*directory_path1*[;*label2*:*directory_path2*;…]';

label是归档目录的任意标识符。它可以是任何字符的字符串，但不允许使用冒号(:)。空标签也是允许的，但在这种情况下仍然需要冒号(:)。必须指定directory_path。在激活重做日志归档时，用于重做日志归档文件的目录必须存在，否则会返回错误。路径可以包含冒号(':')，但不允许使用分号(;)。

必须在激活重做日志归档之前配置innodb_redo_log_archive_dirs变量。默认值为NULL，不允许激活重做日志归档。

注意

您指定的归档目录必须满足以下要求。（这些要求在激活重做日志归档时会被强制执行。）：

• 目录必须存在。目录不会被重做日志归档过程创建。否则，将返回以下错误：

  错误 3844 (HY000)：重做日志归档目录'directory_path1'不存在���不是目录

• 目录不能对所有用户开放访问。这是为了防止重做日志数据暴露给系统上的未经授权用户。否则，将返回以下错误：

  错误 3846 (HY000)：重做日志归档目录'directory_path1'对所有操作系统用户可访问

• 目录不能是由datadir、innodb_data_home_dir、innodb_directories、innodb_log_group_home_dir、innodb_temp_tablespaces_dir、innodb_tmpdir、innodb_undo_directory或secure_file_priv定义的目录，也不能是这些目录的父目录或子目录。否则，将返回类似以下的错误：

  错误 3845 (HY000)：重做日志归档目录'directory_path1'在服务器目录'datadir' - '/path/to/data_directory'中、下或上

当支持重做日志归档的备份实用程序启动备份时，备份实用程序通过调用innodb_redo_log_archive_start()函数激活重做日志归档。

如果您没有使用支持重做日志归档的备份实用程序，也可以手动激活重做日志归档，如下所示：

mysql> SELECT innodb_redo_log_archive_start('*label*', '*subdir*');
+------------------------------------------+
| innodb_redo_log_archive_start('*label*') |
+------------------------------------------+
| 0                                        |
+------------------------------------------+

或：

mysql> DO innodb_redo_log_archive_start('*label*', '*subdir*');
Query OK, 0 rows affected (0.09 sec)

注意

激活重做日志归档的 MySQL 会话（使用innodb_redo_log_archive_start()）必须保持打开状态以进行归档。相同的会话必须停用重做日志归档（使用innodb_redo_log_archive_stop()）。如果会话在显式停用重做日志归档之前终止，则服务器会隐式停用重做日志归档并删除重做日志归档文件。

其中*label是由innodb_redo_log_archive_dirs定义的标签；subdir是用于指定保存归档文件的label*标识的目录的子目录的可选参数；它必须是一个简单的目录名称（不允许斜杠（/）、反斜杠（\）或冒号（:））。subdir可以为空，为 null，或者可以省略。

只有具有INNODB_REDO_LOG_ARCHIVE权限的用户才能通过调用innodb_redo_log_archive_start()激活重做日志归档，或使用innodb_redo_log_archive_stop()停用它。运行备份实用程序的 MySQL 用户或手动激活和停用重做日志归档的 MySQL 用户必须具有此权限。

重做日志归档文件路径为*directory_identified_by_label*/[*subdir*/]archive.*serverUUID*.000001.log，其中*directory_identified_by_label*是由innodb_redo_log_archive_start()的*label*参数标识的归档目录。*subdir*是用于innodb_redo_log_archive_start()的可选参数。

例如，重做日志归档文件的完整路径和名称类似于以下内容：

/*directory_path*/*subdirectory*/archive.e71a47dc-61f8-11e9-a3cb-080027154b4d.000001.log

备份工具完成复制InnoDB数据文件后，通过调用innodb_redo_log_archive_stop()函数来停用重做日志归档。

如果您没有使用支持重做日志归档的备份工具，也可以手动停用重做日志归档，如下所示：

mysql> SELECT innodb_redo_log_archive_stop();
+--------------------------------+
| innodb_redo_log_archive_stop() |
+--------------------------------+
| 0                              |
+--------------------------------+

或者：

mysql> DO innodb_redo_log_archive_stop();
Query OK, 0 rows affected (0.01 sec)

在停止函数成功完成后，备份工具会从归档文件中查找相关部分的重做日志数据，并将其复制到备份中。

备份工具完成复制重做日志数据并不再需要重做日志归档文件后，会删除该归档文件。

在正常情况下，归档文件的删除是备份工具的责任。但是，如果重做日志归档操作在调用innodb_redo_log_archive_stop()之前意外退出，则 MySQL 服务器会删除该文件。

性能考虑

激活重做日志归档通常会因为额外的写入活动而产生轻微的性能成本。

在 Unix 和类 Unix 操作系统上，性能影响通常较小，假设没有持续高速更新。在 Windows 上，性能影响通常略高，假设情况相同。

如果更新速率持续较高且重做日志归档文件与重做日志文件位于相同存储介质上，则由于复合写入活动，性能影响可能更为显著。

如果更新速率持续较高且重做日志归档文件位于比重做日志文件更慢的存储介质上，则性能会受到任意影响。

写入重做日志归档文件不会妨碍正常的事务日志记录，除非重做日志归档文件存储介质的速率远远低于重做日志文件存储介质，并且有大量持久化的重做日志块等待写入重做日志归档文件。在这种情况下，事务日志记录速率会降低到可以由重做日志归档文件所在的较慢存储介质管理的水平。

禁用重做日志记录

截至 MySQL 8.0.21 版本，您可以使用ALTER INSTANCE DISABLE INNODB REDO_LOG语句禁用重做日志记录。此功能旨在将数据加载到新的 MySQL 实例中。禁用重做日志记录通过避免重做日志写入和双写缓冲来加快数据加载速度。

警告

此功能仅用于将数据加载到新的 MySQL 实例中。不要在生产系统上禁用重做日志。允许在禁用重做日志时关闭和重新启动服务器，但在禁用重做日志时发生意外服务器停止可能会导致数据丢失和实例损坏。

在禁用重做日志后尝试重新启动服务器会被拒绝，并显示以下错误：

[ERROR] [MY-013598] [InnoDB] Server was killed when Innodb Redo 
logging was disabled. Data files could be corrupt. You can try 
to restart the database with innodb_force_recovery=6

在这种情况下，初始化一个新的 MySQL 实例并重新启动数据加载过程。

启用和禁用重做日志需要INNODB_REDO_LOG_ENABLE权限。

Innodb_redo_log_enabled状态变量允许监视重做日志状态。

在禁用重做日志时，不允许克隆操作和重做日志归档，反之亦然。

ALTER INSTANCE [ENABLE|DISABLE] INNODB REDO_LOG操作需要独占备份元数据锁，这会阻止其他ALTER INSTANCE操作并发执行。其他ALTER INSTANCE操作必须等待锁释放后才能执行。

以下过程演示了在将数据加载到新的 MySQL 实例时如何禁用重做日志。

1. 在新的 MySQL 实例上，向负责禁用重做日志的用户帐户授予INNODB_REDO_LOG_ENABLE权限。

   mysql> GRANT INNODB_REDO_LOG_ENABLE ON *.* to 'data_load_admin';
   

2. 作为data_load_admin用户，禁用重做日志：

   mysql> ALTER INSTANCE DISABLE INNODB REDO_LOG;
   

3. 检查Innodb_redo_log_enabled状态变量，确保重做日志已禁用。

   mysql> SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled';
   +-------------------------+-------+
   | Variable_name           | Value |
   +-------------------------+-------+
   | Innodb_redo_log_enabled | OFF   |
   +-------------------------+-------+
   

4. 运行数据加载操作。

5. 作为data_load_admin用户，在数据加载操作完成后启用重做日志：

   mysql> ALTER INSTANCE ENABLE INNODB REDO_LOG;
   

6. 检查Innodb_redo_log_enabled状态变量，确保重做日志已启用。

   mysql> SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_enabled';
   +-------------------------+-------+
   | Variable_name           | Value |
   +-------------------------+-------+
   | Innodb_redo_log_enabled | ON    |
   +-------------------------+-------+
   

相关主题

• 重做日志配置

• 第 10.5.4 节，“优化 InnoDB 重做日志”

• 重做日志加密