本文为 MySQL 8.0 官方文档 The InnoDB Storage Engine 的部分翻译。

InnoDB 简介

InnoDB 是一个兼顾高可靠性和高性能的通用存储引擎。在 MySQL 8.0 中，InnoDB 是默认的 MySQL 存储引擎。除非您配置了不同的默认存储引擎，否则发出不带 ENGINE 子句的 CREATE TABLE 语句默认会创建一个 InnoDB 表。

InnoDB 的主要优势

  • 它的 DML(data manipulation language) 操作遵循 ACID 模型，事务具有提交、回滚和崩溃恢复功能，以保护用户数据。请参阅第 15.2 节，“InnoDB 和 ACID 模型”。
  • 行级锁定和 Oracle 风格的一致性读取提高了多用户并发性和性能。请参阅第 15.7 节，“InnoDB 锁定和事务模型”。
  • InnoDB 表将您的数据排列在磁盘上以优化基于主键的查询。每个 InnoDB 表都有一个称为聚集索引的主键索引，用于组织数据以最小化主键查找的 I/O。请参阅第 15.6.2.1 节，“聚集索引和二级索引”。
  • 为了保持数据完整性，InnoDB 支持外键 FOREIGN KEY 约束。使用外键检查插入、更新和删除以确保它们不会导致相关表之间的不一致。

Table InnoDB 存储引擎功能

功能是否支持
B-tree 索引Yes
备份/基于时间点恢复（在服务端实现，而不是在存储引擎中。）Yes
数据库集群支持No
聚集索引Yes
压缩数据Yes
数据缓存Yes
加密数据Yes (在服务器中通过加密函数实现；在 MySQL 5.7 及更高版本中，支持静态数据加密。)
外键支持Yes
全文检索索引Yes (MySQL 5.6 及更高版本支持全文索引。)
地理空间数据类型支持Yes
地理空间索引支持Yes (MySQL 5.7 及更高版本支持地理空间索引。)
哈希索引No (InnoDB 在内部利用哈希索引来实现其自适应哈希索引功能。)
索引缓存Yes
锁定粒度行
MVCC - 多版本并发控制Yes
复制支持 (在服务端实现，而不是在存储引擎中。)Yes
存储限制64TB
T树索引No
事物Yes
数据字典的更新统计信息Yes

使用 InnoDB 表的好处

InnoDB 表具有以下优点：

  • 如果服务器由于硬件或软件问题而意外退出，无论当时数据库中发生了什么，您都无需在重新启动数据库后执行任何特殊操作。InnoDB崩溃恢复会自动完成崩溃之前提交的更改，并撤消正在进行但未提交的更改，允许您重新启动并从上次中断的地方继续。请参阅 第 15.18.2 节，“InnoDB 恢复”。
  • InnoDB 存储引擎维护自己的缓冲池 - buffer pool，在访问数据时将表和索引数据缓存在主内存中。经常使用的数据直接从内存中处理。此缓存适用于多种类型的信息并加快处理速度。在专用数据库服务器上，多达 80% 的物理内存通常分配给缓冲池。请参见第 15.5.1 节，“缓冲池 - Buffer Pool”。
  • 如果将相关数据拆分到不同的表中，则可以设置强制参照完整性的外键。请参见 第 13.1.20.5 节，“外键约束”。
  • 如果磁盘或内存中的数据损坏，校验和机制会在您使用之前提醒您注意虚假数据。该 innodb_checksum_algorithm 变量定义了 InnoDB 使用的校验和算法。
  • 当您为每个表设计具有适当主键列的数据库时，会自动优化涉及这些列的操作。在 WHERE 子句、ORDER BY 子句、 GROUP BY 子句和连接操作中引用主键列的速度非常快 。请参阅 第 15.6.2.1 节，“聚集索引和二级索引”。
  • 插入、更新和删除通过称为更改缓冲 - change buffering 的自动机制进行优化。InnoDB 不仅允许对同一个表进行并发读写访问，它还缓存更改的数据以简化磁盘 I/O。请参阅 第 15.5.2 节，“更改缓冲区 - Change Buffer”。
  • 性能优势不仅限于具有长时间运行查询的大型表。当从表中一遍又一遍地访问相同的行时，自适应哈希索引会接管以加快这些查找，就好像它们来自哈希表一样。请参阅第 15.5.3 节，“自适应哈希索引”。
  • 您可以压缩表和关联的索引。请参阅 第 15.9 节，“InnoDB 表和页面压缩”。
  • 您可以加密您的数据。请参阅 第 15.13 节，“InnoDB 静态数据加密”。
  • 您可以创建和删除索引以及执行其他 DDL 操作，而对性能和可用性的影响要小得多。请参阅 第 15.12.1 节，“在线 DDL 操作”。
  • 截断（Truncating）每个表的文件表空间非常快，可以释放磁盘空间供操作系统重用，而不仅仅是 InnoDB. 请参阅 第 15.6.3.2 节，“单表文件空间”。
  • 表数据的存储布局对于 BLOB 长文本字段和 DYNAMIC 行格式更有效 。请参阅 第 15.10 节，“InnoDB 行格式”。
  • 您可以通过查询 INFORMATION_SCHEMA 表来监控存储引擎的内部工作。请参阅 第 15.15 节，“InnoDB INFORMATION_SCHEMA 表”。
  • 您可以通过查询 Performance Schema 表来监控存储引擎的性能详细信息。请参阅 第 15.16 节，“InnoDB 与 MySQL Performance Schema 的集成”。
  • 您可以将 InnoDB 表与来自其他 MySQL 存储引擎的表混合使用，即使在同一语句中也是如此。例如，您可以使用连接操作在单个查询中组合来自表 InnoDB 和 MEMORY 表的数据 。
  • InnoDB 专为处理大量数据时的 CPU 效率和最大性能而设计。
  • InnoDB 表可以处理大量数据，即使在文件大小限制为 2GB 的操作系统上也是如此。

对于 InnoDB 可以应用于 MySQL 服务器和应用程序代码的特定调整技术，请参阅 第 8.5 节“优化 InnoDB 表”。

InnoDB 表的最佳实践

本节介绍使用 InnoDB 表时的最佳实践 。

  • 使用最常查询的一列或多列来为每个表指定一个主键，如果没有明显的主键，则指定一个自增值。
  • 在根据多个表中的相同 ID 值从多个表中提取数据的地方使用连接 - join。为了快速连接性能，在连接列上定义外键，并在每个表中用相同的数据类型声明这些列。添加外键确保引用的列被索引，这可以提高性能。外键还会将删除和更新传播到所有受影响的表，并在父表中不存在相应 ID 时阻止在子表中插入数据。
  • 关闭自动提交。每秒提交数百次会限制性能（受存储设备的写入速度限制）。
  • 通过用 START TRANSACTION 和 COMMIT 语句将相关的 DML 操作集分组到事务中。虽然您不想太频繁地提交，但您也不想发出大量运行数小时而不提交的 INSERT、 UPDATE、 或 DELETE 语句。
  • 不要使用 LOCK TABLES 语句。InnoDB 可以在不牺牲可靠性或高性能的情况下同时处理对同一个表的所有读取和写入的多个会话。要获得对一组行的独占写访问权限，请使用 SELECT ... FOR UPDATE 语法锁定您打算更新的行。
  • 启用 innodb_file_per_table 变量或使用通用表空间将表的数据和索引放入单独的文件而不是系统表空间。innodb_file_per_table 默认情况下启用该变量。
  • 评估您的数据和访问模式是否受益于InnoDB表或页面压缩功能。您可以在 InnoDB 不牺牲读/写能力的情况下压缩表。
  • 使用--sql_mode=NO_ENGINE_SUBSTITUTION 选项运行服务器以防止使用您不想使用的存储引擎创建表。

InnoDB 架构

下图显示了构成InnoDB存储引擎架构的内存和磁盘结构。内存结构包括缓冲池 - Buffer Pool、自适应哈希索引 - Adaptive Hash Index、更改缓冲区 - Buffer Pool 和日志缓冲区 - Log Buffer。 磁盘结构包括表空间 - Table Space、重做日志 - Redo Log 和双写缓冲区文件 - Doublewrite Buffer Files。

InnoDB 架构

InnoDb 内存结构

缓冲池 - Buffer Pool

缓冲池是主内存中的一个区域，用于在 InnoDB访问时缓存表和索引数据。缓冲池允许直接从内存访问经常使用的数据，从而加快处理速度。在专用服务器上，多达 80% 的物理内存通常分配给缓冲池。

为了提高大量读取操作的效率，缓冲池被划分为可能包含多行的页 - Page。为了缓存管理的效率，缓冲池被实现为页的链表；很少使用的数据使用最近最少使用 (LRU) 算法的变体从缓存中老化。

了解如何利用缓冲池将经常访问的数据保存在内存中是 MySQL 调优的一个重要方面。

缓冲池 LRU 算法

缓冲池作为列表 - List 使用 LRU 算法的变体来进行管理。当需要空间向缓冲池添加新页时，最近最少使用的页会被逐出，并将新页添加到列表中间。此中点插入策略将列表视为两个子列表：

  • 在头部，最近访问的新（“年轻”）页的子列表
  • 在尾部，最近访问过的旧页的子列表

缓冲池列表

该算法将经常使用的页保留在新的子列表中。旧的子列表包含不太常用的页；这些页是要被驱逐的候选页。

默认情况下，算法操作如下：

  • 缓冲池的 3/8 专用于旧子列表。
  • 列表的中间是新子列表尾部与旧子列表头部相交的边界。
  • 当 InnoDB 将页读入缓冲池时，它最初将它插入到中间（旧子列表的头部）。可以读取该页，因为它是用户初始化操作（例如 SQL 查询）所必需的，或者是由 InnoDB 自动执行的预读操作的一部分。
  • 访问旧子列表中的页使其 “变新”，将其移动到新子列表的头部。如果页面是因为用户启动的操作需要它而被读取，则第一次访问会立即发生，并且页会变新。如果页面是由于预读操作而读取的，则第一次访问不会立即发生，并且在页被逐出之前可能根本不会发生。
  • 随着数据库的运行，缓冲池中未被访问的页面会通过向列表尾部移动来“老化”。新旧子列表中的页随着其他页的更新而老化。旧子列表中的页也会随着页插入中点而老化。最终，一个未使用的页到达旧子列表的尾部并被驱逐。

默认情况下，查询读取的页会立即移动到新的子列表中，这意味着它们在缓冲池中停留的时间更长。例如，为 mysqldump 操作或 SELECT 没有 WHERE 子句的语句执行的表扫描可以将大量数据带入缓冲池并驱逐等量的旧数据，即使这些新数据不需要再使用。类似地，由预读后台线程加载且仅访问一次的页也会被移动到新列表的头部。这些情况会将经常使用的页推送到旧的子列表，在那里它们会被逐出。有关优化此行为的信息，请参阅 第 15.8.3.3 节，“使缓冲池扫描具有抵抗性”和 第 15.8.3.4 节，“配置 InnoDB 缓冲池预取（预读）”。

InnoDB 标准监视器输出在 BUFFER POOL AND MEMORY 有关缓冲池 LRU 算法操作的部分中包含多个字段。有关详细信息，请参阅使用 InnoDB 标准监视器监视缓冲池。

更改缓冲区 - Change Buffer

更改缓冲区是一种特殊的数据结构，当二级索引页不在缓冲池中时，它会缓存对二级索引页的更改 。可能由INSERT、 UPDATE或 DELETE操作 (DML) 导致的缓冲更改稍后在其他读取操作将页加载到缓冲池时合并。

<strong>更改缓冲区</strong>

与聚集索引不同，二级索引通常是非唯一的，并且以相对随机的顺序插入二级索引。同样，删除和更新可能会影响索引树中不相邻的二级索引页。合并更改的缓存，以便稍后在其他操作将受影响的页读入缓冲池时可避免大量随机访问 I/O，而这些 I/O 需要将二级索引页面从磁盘读入缓冲池。

在系统大部分空闲时或在缓慢关闭期间运行的清洗 - purge 操作会定期将更新的索引页写入磁盘。与将每个值立即写入磁盘相比，purge 操作可以更有效地为一系列索引值写入磁盘块。

当有许多受影响的行和许多二级索引要更新时，更改缓冲区合并可能需要几个小时。在此期间，磁盘 I/O 增加，这可能会导致磁盘绑定查询的显着减慢。在提交事务后，甚至在服务器关闭并重新启动之后，更改缓冲区合并也可能继续发生（ 有关更多信息，请参阅第 15.21.3 节，“强制 InnoDB 恢复”）。

在内存中，更改缓冲区占据了缓冲池的一部分。在磁盘上，更改缓冲区是系统表空间的一部分，当数据库服务器关闭时，索引更改会在其中缓冲。

缓存在更改缓冲区中的数据类型由 innodb_change_buffering 变量控制。有关更多信息，请参阅 配置更改缓冲。您还可以配置最大更改缓冲区大小。有关更多信息，请参阅 配置更改缓冲区最大大小。

如果索引包含降序索引列或主键包含降序索引列，则二级索引不支持更改缓冲。

有关更改缓冲区的常见问题解答，请参阅第 A.16 节，“MySQL 8.0 常见问题解答：InnoDB 更改缓冲区”。

自适应哈希索引 - Adaptive Hash Index

自适应哈希索引能够让 InnoDB 在具有适当组合的工作负载和足够的缓冲池内存的系统上的执行更像内存数据库，而不会牺牲事务功能或可靠性。自适应哈希索引由 innodb_adaptive_hash_index 变量启用 ，或在服务器启动时由参数 --skip-innodb-adaptive-hash-index 关闭。

根据观察到的搜索模式，使用索引键的前缀构建哈希索引。前缀可以是任意长度，也可能只有 B 树中的某些值出现在哈希索引中。哈希索引是针对经常访问的索引页按需构建的。

如果一个表几乎完全放在主内存中，哈希索引会通过启用任何元素的直接查找来加速查询，将索引值转换为一种指针。 InnoDB具有监视索引搜索的机制。如果 InnoDB 注意到查询可以从构建哈希索引中受益，它会自动这样做。

对于某些工作负载，哈希索引查找的加速优势大大超过了监视索引查找和维护哈希索引结构的带来的额外工作。对自适应哈希索引的访问有时也会成为繁重工作负载下的争用源，例如多个并发连接。带有 LIKE 运算符和 % 通配符的查询也往往不会受益。对于无法从自适应哈希索引中受益的工作负载，将其关闭可减少不必要的性能开销。由于很难提前预测自适应哈希索引是否适合特定系统和工作负载，请考虑在启用和禁用它的情况下运行基准测试。

自适应哈希索引功能是分区的。每个索引都绑定到一个特定的分区，每个分区都由一个单独的闩锁 - latch 保护。分区由 innodb_adaptive_hash_index_parts 变量控制 。该 innodb_adaptive_hash_index_parts 变量默认设置为 8。最大设置为 512。

您可以在 SHOW ENGINE INNODB STATUS 输出的 SEMAPHORES 部分监控自适应哈希索引的使用和争用。如果有大量线程在等待 btr0sea.c 中创建的 rw-latch，请考虑增加自适应散列索引分区的数量或禁用自适应散列索引。

有关哈希索引的性能特征的信息，请参阅第 8.3.9 节，“B 树和哈希索引的比较”。

日志缓冲区 - Log Buffer

日志缓冲区是保存要写入到磁盘上日志文件的数据的内存区域。日志缓冲区大小由 innodb_log_buffer_size 变量定义 。默认大小为 16MB。日志缓冲区的内容会定期刷新到磁盘。大型日志缓冲区使大型事务能够运行，而无需在事务提交之前将重做日志数据写入磁盘。因此，如果您有更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘 I/O。

该 innodb_flush_log_at_trx_commit 变量控制日志缓冲区的内容如何写入和刷新到磁盘。该 innodb_flush_log_at_timeout 变量控制日志刷新频率。

有关相关信息，请参阅 内存配置 和 第 8.5.4 节, “优化 InnoDB 重做日志”。

InnoDB 物理结构

表 - Tables

创建 InnoDB 表

InnoDB 表是使用 CREATE TABLE 语句创建的 ；例如：

CREATE TABLE t1 (a INT, b CHAR (20), PRIMARY KEY (a)) ENGINE=InnoDB;

当把 InnoDB 定义为默认存储引擎时不需要 ENGINE=InnoDB 该子句 ，默认情况下就是 InnoDB。但是，如果 CREATE TABLE 要在默认存储引擎不存在 InnoDB 或未知的其他 MySQL 服务器实例上重放语句，则该子句很有用 。您可以通过发出以下语句来确定 MySQL 服务器实例上的默认存储引擎：

mysql> SELECT @@default_storage_engine;
+--------------------------+
| @@default_storage_engine |
+--------------------------+
| InnoDB                   |
+--------------------------+

InnoDB 默认情况下，表是在 File-Per-Table Tablespaces 表空间中创建的。要让 InnoDB 在 InnoDB 系统表空间（System Tablespace）中创建表，请在创建表之前禁用该变量 innodb_file_per_table。要让 InnoDB 在通用表空间（General Tablespaces）中创建表，请使用 CREATE TABLE ... TABLESPACE 语法。有关更多信息，请参阅 第 15.6.3 节，“表空间”。

行格式

InnoDB 表的行格式决定了其行在磁盘上的物理存储方式。 InnoDB 支持四种行格式，每种格式具有不同的存储特性。支持的行格式包括 REDUNDANT，COMPACT， DYNAMIC，和 COMPRESSED。默认的行格式为 DYNAMIC。有关行格式特征的信息，请参阅 第 15.10 节，“InnoDB 行格式”。

该 innodb_default_row_format 变量定义了默认的行格式。表的行格式也可以使用 CREATE TABLE 或 ALTER TABLE 语句中的 ROW_FORMAT 表选项显式定义。请参阅 定义表的行格式。

表 InnoDB 行格式概述

行格式紧凑的存储特性增强的可变长度列存储大索引键前缀支持压缩支持支持的表空间类型
REDUNDANTNoNoNoNosystem, file-per-table, general
COMPACTYesNoNoNosystem, file-per-table, general
DYNAMICYesYesYesNosystem, file-per-table, general
COMPRESSEDYesYesYesYesfile-per-table, general

主键

建议您为您创建的每个表定义一个主键。选择主键列时，选择具有以下特征的列：

  • 最重要的查询引用的列。
  • 永远不会留空的列。
  • 从不具有重复值的列。
  • 插入后很少更改值的列。

例如，在包含有关人员信息的表中，您不会创建主键，(firstname, lastname) 因为可以有多个人具有相同的姓名，姓名列可能留空，有时人们会更改他们的姓名。由于有如此多的约束，通常没有一组明显的列用作主键，因此您创建一个带有数字 ID 的新列作为主键的全部或部分。您可以声明一个 自动递增 列，以便在插入行时自动填充升序值：

# ID 的值可以充当不同表中相关项之间的指针。
CREATE TABLE t5 (id INT AUTO_INCREMENT, b CHAR (20), PRIMARY KEY (id));

# 主键可以由多列组成。任何 autoinc 列都必须排在第一位。
CREATE TABLE t6 (id INT AUTO_INCREMENT, a INT, b CHAR (20), PRIMARY KEY (id,a));

有关自动增量列的更多信息，请参阅 第 15.6.1.6 节，“InnoDB 中的 AUTO_INCREMENT 处理”。

尽管表在没有定义主键的情况下也能正常工作，但主键涉及性能的许多方面，并且是任何大型或经常使用的表的关键设计方面。建议您始终在 CREATE TABLE 语句中指定主键。如果创建表，加载数据，然后运行 ALTER TABLE 添加主键，该操作比创建表时定义主键要慢得多。有关主键的更多信息，请参阅第 15.6.2.1 节，“聚集索引和二级索引”。

查看 InnoDB 表属性

要查看 InnoDB 表的属性，可以发出一条 SHOW TABLE STATUS 语句：

mysql> SHOW TABLE STATUS FROM test LIKE 't%' \G;
*************************** 1. row ***************************
           Name: t1
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2021-02-18 12:18:28
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_0900_ai_ci
       Checksum: NULL
 Create_options: 
        Comment:

有关 SHOW TABLE STATUS 输出的信息，请参阅 第 13.7.7.38 节，“显示表状态语句”。

您还可以通过查询 InnoDB Information Schema 系统表来访问表属性：

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t1' \G
*************************** 1. row ***************************
     TABLE_ID: 1144
         NAME: test/t1
         FLAG: 33
       N_COLS: 5
        SPACE: 30
   ROW_FORMAT: Dynamic
ZIP_PAGE_SIZE: 0
   SPACE_TYPE: Single
 INSTANT_COLS: 0

有关更多信息，请参阅 第 15.15.3 节，“InnoDB INFORMATION_SCHEMA 模式对象表”。

InnoDB 中的 AUTO_INCREMENT 处理

InnoDB 提供了一种可配置的锁定机制，可以显着提高向具有 AUTO_INCREMENT 列的表添加行的 SQL 语句的可伸缩性和性能。要对 InnoDB 表使用该 AUTO_INCREMENT 机制，必须将 AUTO_INCREMENT 列定义为索引的一部分，以便可以对表执行等效的索引查找 SELECT MAX(ai_col) 以获得最大列值。通常，这是通过将该列作为某个表索引的第一列来实现的。

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

InnoDB AUTO_INCREMENT 锁定模式

本节介绍用于生成自动增量值的 AUTO_INCREMENT 锁模式，以及每种锁模式如何影响复制。在启动时使用 innodb_autoinc_lock_mode 变量来配置自动增量锁定模式。

以下术语用于描述 innodb_autoinc_lock_mode 设置：

  • “类似 Insert”的语句

    在表中生成新行的所有语句，包括 INSERT、INSERT ... SELECT、REPLACE、REPLACE ... SELECT 和 LOAD DATA。包括“简单插入”、“批量插入”和“混合模式”插入。

  • “简单插入”

    可以预先确定要插入的行数的语句（在最初处理语句时）。这包括没有嵌套子查询的单行和多行 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-INC 锁定，用于插入到带有 AUTO_INCREMENT 列的表中。此锁通常保持到语句的末尾（而不是事务的末尾），以确保为给定的 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 SELECT 中的 INSERT 语句中检索了多少行 ，并且随着语句的进行，它一次分配一个自动增量值。使用表级锁，一直保持到语句的末尾，一次只能执行一条 INSERT 引用 table 的语句 t1，并且不同语句的自增数的生成不会交错。Tx1 INSERT ... SELECT 语句生成的自增值是连续的，并且使用的（单个）自增值 INSERT Tx2 中的语句小于或大于所有用于 Tx1 的语句，具体取决于哪个语句先执行。

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

    在前面的示例中，如果没有表级锁，则用于 INSERT 到 Tx2 的自增列的值取决于语句的执行时间。如果 INSERT Tx2 的 INSERT 在 Tx1 的运行时执行（而不是在它开始之前或完成之后），则这两个 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 ” 语句中是唯一的并且单调递增 。但是，由于多个语句可以同时生成数字（即，数字的分配在语句之间交错），因此为任何给定语句插入的行生成的值可能不是连续的。

    如果执行的唯一语句是提前知道要插入的行数的“简单插入”，则为单个语句生成的数字没有间隙，“混合模式插入”除外 。但是，当执行“批量插入”时，任何给定语句分配的自动增量值中可能存在间隙。

索引 - Indexes

聚集索引和二级索引

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

  • 在表上定义 PRIMARY KEY 时，InnoDB 将其用作聚集索引。应该为每个表定义一个主键。如果没有逻辑唯一且非空的列或列集使用主键，请添加自动增量列。自动递增列值是唯一的，并在插入新行时自动添加。
  • 如果您没有为表定义 PRIMARY KEY，则 InnoDB 使用第一个所有键列都定义为 NOT NULL 的 UNIQUE 索引作为聚集索引。
  • 如果表没有 PRIMARY KEY 或没有合适的 UNIQUE 索引，则 InnoDB 会在包含行 ID 值的合成列上生成一个名为 GEN_CLUST_INDEX 的隐藏聚集索引。行按 InnoDB 分配的行 ID 排序。行 ID 是一个 6 字节的字段，随着插入新行而单调增加。因此，按行 ID 排序的行在物理上是按插入顺序排列的。
聚集索引如何加快查询速度

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

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

聚集索引以外的索引称为二级索引。在 InnoDB 中，二级索引中的每条记录都包含该行的主键列，以及为二级索引指定的列。InnoDB 使用此主键值搜索聚集索引中的行。

如果主键很长，二级索引会占用更多的空间，所以主键短是有利的。

有关利用 InnoDB 聚集索引和二级索引的指南，请参阅 第 8.3 节 “优化和索引”。

InnoDB 索引的物理结构

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

当新记录插入到 InnoDB 聚集索引中时， InnoDB 尝试保留 1/16 的页空闲空间以供将来插入和更新索引记录。如果按顺序（升序或降序）插入索引记录，则生成的索引页大约为 15/16。如果以随机顺序插入记录，则页从 1/2 到 15/16 。

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

如果 InnoDB 索引页面的填充因子（fill factor）低于 MERGE_THRESHOLD，默认情况下为 50%，如果未指定，则 InnoDB 尝试收缩索引树以释放页面。该 MERGE_THRESHOLD 设置适用于 B 树和 R 树索引。有关更多信息，请参阅 第 15.8.11 节，“配置索引页面的合并阈值”。

排序索引构建

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

索引构建分为三个阶段：

  1. 在第一阶段， 扫描聚集索引，生成索引条目并添加到排序缓冲区。当排序缓冲区变满时，条目被排序并写出到临时中间文件。此过程也称为 “运行-run”。
  2. 在第二阶段，将一次或多次运行写入临时中间文件，对文件中的所有条目执行归并排序。
  3. 在第三个也是最后一个阶段，排序后的条目被插入到 B 树中。

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

排序索引构建使用“自下而上”建立索引的方法。即叶子页（底部）首先构建，然后构建非叶子级别直到根（向上）。

使用这种方法，对最右侧叶页的引用保存在 B 树的所有级别。分配必要 B 树深度的最右侧叶页，并根据其排序顺序插入条目。一旦叶页已满，节点指针将附加到父页，并为下一次插入分配同级叶页。这个过程一直持续到所有条目都被插入，这可能会导致插入到根级别。分配同级页时，释放对先前固定的叶页的引用，新分配的叶页成为最右侧的叶页和新的默认插入位置。

表空间 - Tablespaces

系统表空间

系统表空间是更改缓冲区的存储区域。如果表是在系统表空间中创建的，而不是在每个表文件或通用表空间中创建，则它还可能包含表和索引数据。在以前的 MySQL 版本中，系统表空间包含 InnoDB 数据字典。在 MySQL 8.0 中，InnoDB 将元数据存储在 MySQL 数据字典中。请参阅第 14 章，MySQL 数据字典。在以前的 MySQL 版本中，系统表空间还包含双写缓冲区存储区。从 MySQL 8.0.20 开始，此存储区域位于单独的双写文件中。请参见 第 15.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 属性定义的，请将其删除，然后修改 size 属性以反映当前数据文件的大小。要确定指定的数据文件大小，请检查文件系统的文件大小，并将该值向下舍入到最接近的 MB 值，其中 1MB 等于 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。这是 innodb_data_file_path 修改 size 属性以反映当前数据文件大小后的设置，并在指定新的 50MB 自动扩展数据文件后：

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

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

减少 InnoDB 系统表空间的大小

不支持减小现有系统表空间的大小。实现更小的系统表空间的唯一选择是将数据从备份恢复到使用所需系统表空间大小配置创建的新 MySQL 实例。

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

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

为避免使用大型系统表空间，请考虑为您的数据使用 file-per-table 表空间或通用表空间。File-per-table 表空间是默认的表空间类型，在创建 InnoDB 表时隐式使用。与系统表空间不同，每个表的文件表空间在被截断或删除时将磁盘空间返回给操作系统。通用表空间是多表表空间，也可以用作系统表空间的替代。见 第 15.6.3.3 节，“通用表空间”。

File-Per-Table 表空间

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

File-Per-Table 表空间配置

InnoDB 默认情况下，在 file-per-table 表空间中创建表。此行为由 innodb_file_per_table 变量控制 。禁用 innodb_file_per_table 导致 InnoDB在系统表空间中创建表。

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

选项文件：

[mysqld]
innodb_file_per_table=ON

SET GLOBAL 运行时 使用：

mysql> SET GLOBAL innodb_file_per_table=ON;

File-Per-Table 表空间数据文件

在 MySQL 数据目录下的架构目录中的 .idb 数据文件中创建一个 file-per-table 表空间。该 .ibd 文件以表 ( *table_name*.ibd)命名。例如，例如在 MySQL 数据目录下的 test 目录下创建表 test.t1 的数据文件：

mysql> USE test;

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

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

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

File-Per-Table 表空间优势

File-per-table 表空间与共享表空间（例如系统表空间或通用表空间）相比具有以下优势。

  • 截断或删除在 file-per-table 表空间中创建的表后，磁盘空间将返回给操作系统。截断或删除存储在共享表空间中的表会在共享表空间数据文件中创建空闲空间，该空间只能用于 InnoDB 数据。换句话说，共享表空间数据文件在表被截断或删除后不会缩小。
  • 对位于共享表空间中的表进行表复制 ALTER TABLE 操作会增加表空间占用的磁盘空间量。此类操作可能需要与表中的数据加上索引一样多的额外空间。该空间不会像用于 file-per-table 表空间那样释放回操作系统。
  • TRUNCATE TABLE 在驻留在 file-per-table 表空间中的表上执行时，性能更好。
  • File-per-table 表空间数据文件可以在单独的存储设备上创建，用于 I/O 优化、空间管理或备份目的。请参阅 第 15.6.1.2 节，“在外部创建表”。
  • 您可以从另一个 MySQL 实例导入驻留在 file-per-table 表空间中的表。请参阅 第 15.6.1.3 节，“导入 InnoDB 表”。
  • 在 file-per-table 表空间中创建的表支持 DYNAMIC 与 COMPRESSED 行格式相关的功能，而系统表空间不支持这些功能。请参阅 第 15.10 节，“InnoDB 行格式”。
  • 当发生数据损坏、备份或二进制日志不可用或 MySQL 服务器实例无法重新启动时，存储在单个表空间数据文件中的表可以节省时间并提高成功恢复的机会。
  • 可以使用 MySQL Enterprise Backup 快速备份或恢复在 file-per-table 表空间中创建的表，而不会中断其他 InnoDB 表的使用 。这对于备份计划不同或需要较少备份的表很有用。有关详细信息，请参阅进行部分备份。
  • File-per-table 表空间允许通过监视表空间数据文件的大小来监视文件系统上的表大小。
  • 当 innodb_flush_method 设置为 O_DIRECT 时，常见的 Linux 文件系统不允许并发写入单个文件，例如共享表空间数据文件。因此，结合此设置使用每个表的文件表空间时，可能会提高性能。
  • 共享表空间中的表的大小受 64TB 表空间大小限制。相比之下，每个表每个文件的表空间都有 64TB 的大小限制，这为单个表的大小增长提供了足够的空间。

File-Per-Table 表空间的缺点

与共享表空间（例如系统表空间或通用表空间）相比，File-Per-Table 文件表空间具有以下缺点。

  • 使用 file-per-table 表空间，每个表可能有未使用的空间，只能由同一表的行使用，如果管理不当，可能会导致空间浪费。
  • fsync 操作是在多个 file-per-table 数据文件而不是单个共享表空间数据文件上执行的。由于 fsync 操作是针对每个文件的，因此无法合并多个表的写操作，这会导致 fsync 操作总数更高。
  • mysqld 必须为每个 file-per-table 表空间保留一个打开的文件句柄，如果在 file-per-table 表空间中有多个表，这可能会影响性能。
  • 当每个表都有自己的数据文件时，需要更多的文件描述符。
  • 存在更多碎片的可能性，这会阻碍 DROP TABLE 和表扫描性能。但是，如果管理碎片，每个表的文件表空间可以提高这些操作的性能。
  • 删除驻留在 file-per-table 表空间中的表时会扫描缓冲池，这对于大型缓冲池可能需要几秒钟。使用广泛的内部锁执行扫描，这可能会延迟其他操作。
  • 该 innodb_autoextend_increment 变量定义了在自动扩展共享表空间文件变满时扩展其大小的增量大小，不适用于 file-per-table 表空间文件，这些文件无论 innodb_autoextend_increment 如何设置都会自动扩展。每个表的初始文件表空间扩展是少量的，之后扩展以 4MB 的增量发生。

通用表空间 - General Tablespaces

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

通用表空间功能

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

  • 与系统表空间类似，通用表空间是能够为多个表存储数据的共享表空间。
  • 通用表空间比 file-per-table 表空间具有潜在的内存优势 。服务器在表空间的生命周期内将表空间元数据保存在内存中。与在每个单独的文件表空间中的相同数量的表相比，较少的通用表空间中的多个表为表空间元数据消耗的内存更少。
  • 通用表空间数据文件可以放在一个相对于或独立于 MySQL 数据目录的目录中，它为您提供了许多和 File-Per-Table 表空间的数据文件和存储管理一样的能力 。与 file-per-table 表空间一样，比如将数据文件放置在 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]

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

例子：

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

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

或者

mysql> CREATE TABLESPACE `ts1` Engine=InnoDB;

该 ADD DATAFILE子句从 MySQL 8.0.14 开始是可选的，在此之前是必需的。如果 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。

通用表空间限制

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

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

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

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

    此外，ALTER TABLE 对位于共享表空间（通用表空间或系统表空间）中的表进行表复制操作可能会增加表空间使用的空间量。此类操作需要与表中的数据加上索引一样多的额外空间。表复制 ALTER 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 开始，除非 InnoDB 直接知道该目录，否则不能在撤消表空间目录 ( innodb_undo_directory) 中创建通用表空间。InnoDB 已知的目录包括那些被定义的 datadir， innodb_data_home_dir 和 innodb_directories 变量。

撤销表空间 - Undo Tablespaces

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

默认撤消表空间

初始化 MySQL 实例时会创建两个默认的 undo 表空间。默认撤消表空间在初始化时创建，为在接受 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 之前，undo 表空间的初始大小取决于 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 之前，undo 表空间一次扩展四个区。从 MySQL 8.0.23 开始，undo 表空间至少扩展了 16MB。为了应对急剧增长，如果前一个文件扩展名发生的时间少于 0.1 秒，则文件扩展名大小会加倍。扩展大小的两倍可以发生多次，最大为 256MB。如果前一个文件扩展名早于 0.1 秒出现，则扩展名大小将减少一半，也可能多次出现，最小为 16MB。如果该 AUTOEXTEND_SIZE 选项是为撤销表空间定义的，则它会被扩展 AUTOEXTEND_SIZE 设置和扩展大小由上述逻辑确定。有关该 AUTOEXTEND_SIZE 选项的信息，请参阅 第 15.6.3.9 节，“表空间 AUTOEXTEND_SIZE 配置”。

临时表空间 - Temporary Tablespaces

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

会话临时表空间

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

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

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

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

shell> cd BASEDIR/data/#innodb_temp
shell> 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 表提供有关会话临时表空间的元数据。

该 INFORMATION_SCHEMA.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 服务器会自动删除并重新创建全局临时表空间。

全局临时表空间不能驻留在原始设备上。

双写缓冲区 - Doublewrite Buffer

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

虽然数据被写入两次，但双写缓冲区不需要两倍的 I/O 开销或两倍的 I/O 操作。数据以一个大的顺序块写入双写缓冲区，只需 fsync() 调用一次操作系统（innodb_flush_method 设置为 O_DIRECT_NO_FSYNC 的情况除外）。

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

重做日志 - Redo Log

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

默认情况下，重做日志在磁盘上由两个名为 ib_logfile0 和 ib_logfile1 物理文件表示。MySQL 以循环方式写入重做日志文件。重做日志中的数据根据受影响的记录进行编码；这些数据统称为重做。通过重做日志的数据通道由不断增加的 LSN 值表示。

撤销日志 - Undo Log

撤消日志是与单个读写事务相关联的撤消日志记录的集合。撤消日志记录包含有关如何撤消事务对聚集索引记录的最新更改的信息。如果另一个事务需要查看原始数据作为一致性读取操作的一部分，则未修改的数据将从撤消日志记录中检索。撤消日志存在于 撤消日志段中 (undo log segment)，而撤消日志段包含在回滚段中 (rollback segments)。回滚段驻留在撤销表空间 (undo tablespace)和全局临时表空间中。

驻留在全局临时表空间中的撤消日志用于修改用户定义临时表中数据的事务。这些撤消日志不会被重做日志，因为它们不是崩溃恢复所必需的。它们仅用于在服务器运行时回滚。这种类型的撤消日志通过避免重做日志记录 I/O 来提高性能。