MySQL8-中文参考-三十-

MySQL8 中文参考（三十）

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

15.7.8 其他管理语句

原文：dev.mysql.com/doc/refman/8.0/en/other-administrative-statements.html

15.7.8.1 二进制日志语句

15.7.8.2 缓存索引语句

15.7.8.3 刷新语句

15.7.8.4 终止语句

15.7.8.5 将索引加载到缓存语句

15.7.8.6 重置语句

15.7.8.7 持久重置语句

15.7.8.8 重新启动语句

15.7.8.9 关闭语句

原文：dev.mysql.com/doc/refman/8.0/en/binlog.html

15.7.8.1 BINLOG Statement

BINLOG '*str*'

BINLOG 是一个内部使用的语句。它由mysqlbinlog程序生成，作为二进制日志文件中某些事件的可打印表示。（参见 Section 6.6.9, “mysqlbinlog — Utility for Processing Binary Log Files”.）'*str*'值是一个 base 64 编码的字符串，服务器解码以确定相应事件指示的数据更改。

在应用mysqlbinlog输出时执行BINLOG语句，用户帐户需要BINLOG_ADMIN权限（或已弃用的SUPER权限），或者REPLICATION_APPLIER权限加上适当的权限来执行每个日志事件。

这个语句只能执行格式描述事件和行事件。

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

15.7.8.2 缓存索引语句

CACHE INDEX {
      *tbl_index_list* [, *tbl_index_list*] ...
    | *tbl_name* PARTITION (*partition_list*)
  }
  IN *key_cache_name*

*tbl_index_list*:
  *tbl_name* [{INDEX|KEY} (*index_name*[, *index_name*] ...)]

*partition_list*: {
    *partition_name*[, *partition_name*] ...
  | ALL
}

缓存索引语句将表索引分配给特定的键缓存。它仅适用于MyISAM表，包括分区的MyISAM表。在索引被分配后，如果需要，它们可以通过加载索引到缓存进行预加载。

以下语句将表t1、t2和t3的索引分配给名为hot_cache的键缓存：

mysql> CACHE INDEX t1, t2, t3 IN hot_cache;
+---------+--------------------+----------+----------+
| Table   | Op                 | Msg_type | Msg_text |
+---------+--------------------+----------+----------+
| test.t1 | assign_to_keycache | status   | OK       |
| test.t2 | assign_to_keycache | status   | OK       |
| test.t3 | assign_to_keycache | status   | OK       |
+---------+--------------------+----------+----------+

缓存索引的语法允许您指定只有特定索引应该分配给缓存。然而，实现会将表的所有索引分配给缓存，因此除了表名外，没有理由指定其他内容。

在缓存索引语句中引用的键缓存可以通过参数设置语句或服务器参数设置来创建其大小。例如：

SET GLOBAL keycache1.key_buffer_size=128*1024;

键缓存参数作为结构化系统变量的成员访问。参见第 7.1.9.5 节，“结构化系统变量”。

在分配索引之前，必须存在一个键缓存，否则会出错：

mysql> CACHE INDEX t1 IN non_existent_cache;
ERROR 1284 (HY000): Unknown key cache 'non_existent_cache'

默认情况下，表索引分配给在服务器启动时创建的主（默认）键缓存。当键缓存被销毁时，分配给它的所有索引将重新分配给默认键缓存。

索引分配影响全局服务器：如果一个客户端将索引分配给给定的缓存，那么无论哪个客户端发出查询，该缓存都用于涉及该索引的所有查询。

缓存索引支持分区的MyISAM表。您可以将一个或多个索引分配给一个给定的键缓存的一个、几个或所有分区。例如，您可以执行以下操作：

CREATE TABLE pt (c1 INT, c2 VARCHAR(50), INDEX i(c1))
    ENGINE=MyISAM
    PARTITION BY HASH(c1)
    PARTITIONS 4;

SET GLOBAL kc_fast.key_buffer_size = 128 * 1024;
SET GLOBAL kc_slow.key_buffer_size = 128 * 1024;

CACHE INDEX pt PARTITION (p0) IN kc_fast;
CACHE INDEX pt PARTITION (p1, p3) IN kc_slow;

前一组语句执行以下操作：

  • 创建一个具有 4 个分区的分区表；这些分区自动命名为p0，...，p3；此表在列c1上有一个名为i的索引。

  • 创建名为kc_fast和kc_slow的 2 个键缓存

  • 将分区p0的索引分配给kc_fast键缓存，将分区p1和p3的索引分配给kc_slow键缓存；剩余分区（p2）的索引使用服务器的默认键缓存。

如果您希望将表pt中所有分区的索引分配给名为kc_all的单个键缓存，可以使用以下两个语句之一：

CACHE INDEX pt PARTITION (ALL) IN kc_all;

CACHE INDEX pt IN kc_all;

刚刚展示的两个语句是等效的，发出任何一个都会产生完全相同的效果。换句话说，如果您希望为分区表的所有分区分配索引到同一个键缓存中，PARTITION (ALL) 子句是可选的。

当为多个分区分配索引到一个键缓存时，这些分区不需要是连续的，也不需要按任何特定顺序列出它们的名称。未明确分配到键缓存的任何分区的索引将自动使用服务器默认的键缓存。

对于分区的MyISAM表也支持索引预加载。有关更多信息，请参见第 15.7.8.5 节，“LOAD INDEX INTO CACHE Statement”。

原文：dev.mysql.com/doc/refman/8.0/en/flush.html

15.7.8.3 FLUSH Statement

FLUSH [NO_WRITE_TO_BINLOG | LOCAL] {
    *flush_option* [, *flush_option*] ...
  | *tables_option*
}

*flush_option*: {
    BINARY LOGS
  | ENGINE LOGS
  | ERROR LOGS
  | GENERAL LOGS
  | HOSTS
  | LOGS
  | PRIVILEGES
  | OPTIMIZER_COSTS
  | RELAY LOGS [FOR CHANNEL *channel*]
  | SLOW LOGS
  | STATUS
  | USER_RESOURCES
}

*tables_option*: {
    *table_synonym*
  | *table_synonym* *tbl_name* [, *tbl_name*] ...
  | *table_synonym* WITH READ LOCK
  | *table_synonym* *tbl_name* [, *tbl_name*] ... WITH READ LOCK
  | *table_synonym* *tbl_name* [, *tbl_name*] ... FOR EXPORT
}

*table_synonym*: {
    TABLE
  | TABLES
}

FLUSH语句有几种变体形式，用于清除或重新加载各种内部缓存、刷新表或获取锁。每个FLUSH操作都需要其描述中指示的权限。

注意

在存储函数或触发器中不可能发出FLUSH语句。但是，您可以在存储过程中使用FLUSH，只要这些存储过程不是从存储函数或触发器中调用的。请参阅第 27.8 节，“存储程序的限制”。

默认情况下，服务器会将FLUSH语句写入二进制日志，以便它们复制到副本中。要禁止记录日志，请指定可选的NO_WRITE_TO_BINLOG关键字或其别名LOCAL。

注意

FLUSH LOGS、FLUSH BINARY LOGS、FLUSH TABLES WITH READ LOCK（带或不带表列表）、以及FLUSH TABLES *tbl_name* ... FOR EXPORT在任何情况下都不会写入二进制日志，因为如果复制到副本中会导致问题。

FLUSH语句会导致隐式提交。请参阅第 15.3.3 节，“导致隐式提交的语句”。

mysqladmin实用程序提供了一个命令行界面，用于执行一些刷新操作，使用命令如flush-hosts、flush-logs、flush-privileges、flush-status和flush-tables。请参阅第 6.5.2 节，“mysqladmin — MySQL 服务器管理程序”。

向服务器发送SIGHUP或SIGUSR1信号会导致发生几种刷新操作，类似于各种形式的FLUSH语句。信号可以由root系统帐户或拥有服务器进程的系统帐户发送。这使得刷新操作可以在不连接到服务器的情况下执行，而这需要一个具有足够权限的 MySQL 帐户进行这些操作。请参阅第 6.10 节，“MySQL 中的 Unix 信号处理”。

重置语句类似于刷新。有关在复制中使用重置的信息，请参见第 15.7.8.6 节，“重置语句”。

以下列表描述了允许的刷新语句flush_option值。有关允许的tables_option值的描述，请参见刷新表语法。

  • 刷新二进制日志

    关闭并重新打开服务器正在写入的任何二进制日志文件。如果启用了二进制日志记录，则相对于上一个文件，二进制日志文件的序列号会增加一。

    此操作需要RELOAD权限。

  • 刷新引擎日志

    关闭并重新打开任何可刷新的已安装存储引擎的日志。这会导致InnoDB将其日志刷新到磁盘。

    此操作需要RELOAD权限。

  • 刷新错误日志

    关闭并重新打开服务器正在写入的任何错误日志文件。

    此操作需要RELOAD权限。

  • 刷新一般日志

    关闭并重新打开服务器正在写入的任何一般查询日志文件。

    此操作需要RELOAD权限。

    此操作对用于一般查询日志的表没有影响（参见第 7.4.1 节，“选择一般查询日志和慢查询日志输出目的地”）。

  • 刷新主机

    清空主机缓存和性能模式host_cache表，该表显示缓存内容，并解除任何被阻止的主机。

    此操作需要RELOAD权限。

    有关为什么可能建议或希望刷新主机缓存的信息，请参见第 7.1.12.3 节，“DNS 查找和主机缓存”。

    注意

    自 MySQL 8.0.23 起，刷新主机已弃用；预计将在未来的 MySQL 版本中删除。取而代之的是截断性能模式host_cache表：

    TRUNCATE TABLE performance_schema.host_cache;

    TRUNCATE TABLE 操作需要表的 DROP 权限，而不是 RELOAD 权限。您应该知道 TRUNCATE TABLE 语句不会被写入二进制日志。要从 FLUSH HOSTS 中获得相同的行为，请在语句中指定 NO_WRITE_TO_BINLOG 或 LOCAL。

  • FLUSH LOGS

    关闭并重新打开服务器正在写入的任何日志文件。

    此操作需要 RELOAD 权限。

    此操作的效果等同于这些操作的综合效果：

    FLUSH BINARY LOGS
FLUSH ENGINE LOGS
FLUSH ERROR LOGS
FLUSH GENERAL LOGS
FLUSH RELAY LOGS
FLUSH SLOW LOGS

  • FLUSH OPTIMIZER_COSTS

    重新读取成本模型表，以便优化器开始使用其中存储的当前成本估算。

    此操作需要 FLUSH_OPTIMIZER_COSTS 或 RELOAD 权限。

    对于任何未识别的成本模型表条目，服务器会向错误日志写入警告。有关这些表的信息，请参见 Section 10.9.5, “The Optimizer Cost Model”。此操作仅影响在刷新后开始的会话。现有会话继续使用它们开始时的成本估算。

  • FLUSH PRIVILEGES

    重新从 mysql 系统模式中的授权表中读取权限。作为此操作的一部分，服务器会读取包含动态权限分配的 global_grants 表，并注册在那里找到的任何未注册的权限。

    重新加载授权表是必要的，以便仅在直接对授权表进行更改时才能启用对 MySQL 权限和用户的更新；对于像 GRANT 或 REVOKE 这样的帐户管理语句，它们会立即生效，不需要这样做。有关更多信息，请参见 Section 8.2.13, “When Privilege Changes Take Effect”。

    此操作需要 RELOAD 权限。

    如果在服务器启动时指定了 --skip-grant-tables 选项以禁用 MySQL 权限系统，则 FLUSH PRIVILEGES 提供了在运行时启用权限系统的方法。

    重置失败登录跟踪（或者如果服务器是使用 --skip-grant-tables 启动的，则启用它），并解锁任何临时锁定的帐户。参见 Section 8.2.15, “Password Management”。

    释放服务器缓存的内存，这是由GRANT、CREATE USER、CREATE SERVER和INSTALL PLUGIN语句导致的。这些内存不会被相应的REVOKE、DROP USER、DROP SERVER和UNINSTALL PLUGIN语句释放，因此对于执行许多导致缓存的语句实例的服务器，除非使用FLUSH PRIVILEGES释放，否则会增加缓存内存使用。

    清除caching_sha2_password认证插件使用的内存缓存。请参见 SHA-2 可插拔认证的缓存操作。

  • FLUSH RELAY LOGS [FOR CHANNEL *channel*]

    关闭并重新打开服务器正在写入的任何中继日志文件。如果启用了中继日志记录，则相对于上一个文件，中继日志文件的序列号将增加一。

    此操作需要RELOAD权限。

    FOR CHANNEL *channel*子句允许您指定操作应用于哪个复制通道。执行FLUSH RELAY LOGS FOR CHANNEL *channel*以刷新特定复制通道的中继日志。如果未命名通道且不存在额外的复制通道，则操作将应用于默认通道。如果未命名通道且存在多个复制通道，则操作将应用于所有复制通道。有关更多信息，请参见第 19.2.2 节，“复制通道”。

  • FLUSH SLOW LOGS

    关闭并重新打开服务器正在写入的任何慢查询日志文件。

    此操作需要RELOAD权限。

    此操作不会影响用于慢查询日志的表（请参见第 7.4.1 节，“选择一般查询日志和慢查询日志输出目的地”）。

  • FLUSH STATUS

    刷新状态指示器。

    此操作将当前线程的会话状态变量值添加到全局值中，并将会话值重置为零。一些全局变量也可能被重置为零。它还将键缓存（默认和命名）的计数器重置为零，并将 Max_used_connections 设置为当前打开连接数。在调试查询时，此信息可能会有用。请参阅 第 1.5 节，“如何报告错误或问题”。

    FLUSH STATUS 不受 read_only 或 super_read_only 的影响，并始终写入二进制日志。

    此操作需要 FLUSH_STATUS 或 RELOAD 权限。

  • FLUSH USER_RESOURCES

    将所有每小时用户资源指标重置为零。

    此操作需要 FLUSH_USER_RESOURCES 或 RELOAD 权限。

    重置资源指标使达到每小时连接、查询或更新限制的客户端可以立即恢复活动。 FLUSH USER_RESOURCES 不适用于由 max_user_connections 系统变量控制的最大同时连接数限制。请参阅 第 8.2.21 节，“设置帐户资源限制”。

FLUSH TABLES 语法

FLUSH TABLES 刷新表，并根据使用的变体获取锁。在 FLUSH 语句中使用的任何 TABLES 变体必须是唯一使用的选项。 FLUSH TABLE 是 FLUSH TABLES 的同义词。

注意

这里描述的指示通过关闭表来刷新表的描述对于 InnoDB 有所不同，它会将表内容刷新到磁盘，但保持表处于打开状态。这仍然允许在表处于打开状态时复制表文件，只要其他活动不修改它们。

  • FLUSH TABLES

    关闭所有打开的表，强制关闭所有正在使用的表，并刷新准备好的语句缓存。

    此操作需要 FLUSH_TABLES 或 RELOAD 权限。

    有关准备语句缓存的信息，请参阅 第 10.10.3 节，“准备语句和存储程序的缓存”。

    当存在活动的 LOCK TABLES ... READ 时，不允许执行 FLUSH TABLES。要刷新并锁定表，请使用 FLUSH TABLES *tbl_name* ... WITH READ LOCK。

  • FLUSH TABLES *tbl_name* [, *tbl_name*] ...

    使用一个或多个逗号分隔的表名列表，此操作类似于不带名称的 FLUSH TABLES，只是服务器只刷新指定的表。如果指定的表不存在，不会发生错误。

    此操作需要 FLUSH_TABLES 或 RELOAD 权限。

  • FLUSH TABLES WITH READ LOCK

    关闭所有打开的表并为所有数据库的所有表加锁以获取全局读锁。

    此操作需要 FLUSH_TABLES 或 RELOAD 权限。

    如果您有像 Veritas 或 ZFS 这样可以在时间上进行快照的文件系统，这个操作是获取备份的非常方便的方法。使用 UNLOCK TABLES 来释放锁。

    FLUSH TABLES WITH READ LOCK 获取全局读锁而不是表锁，因此与表锁定和隐式提交相关的行为不受相同的影响：

    • UNLOCK TABLES 会隐式提交任何已激活的事务，只有当任何表当前已被 LOCK TABLES 锁定时才会发生提交。对于跟随 FLUSH TABLES WITH READ LOCK 的 UNLOCK TABLES 不会发生提交，因为后者不会获取表锁。

    • 开始事务会导致使用 LOCK TABLES 获取的表锁被释放，就像执行了 UNLOCK TABLES 一样。开始事务不会释放使用 FLUSH TABLES WITH READ LOCK 获取的全局读锁。

    FLUSH TABLES WITH READ LOCK不会阻止服务器向日志表插入行（参见第 7.4.1 节，“选择通用查询日志和慢查询日志输出目的地”）。

  • FLUSH TABLES *tbl_name* [, *tbl_name*] ... WITH READ LOCK

    刷新并获取命名表的读锁。

    此操作需要FLUSH_TABLES或RELOAD权限。因为它获取表锁，所以还需要每个表的LOCK TABLES权限。

    该操作首先为表获取排他性元数据锁，因此它会等待那些打开这些表的事务完成。然后，操作会从表缓存中刷新表，重新打开表，获取表锁（类似于LOCK TABLES ... READ)，并将元数据锁从排他性降级为共享。在操作获取锁并降级元数据锁之后，其他会话可以读取但不能修改这些表。

    此操作仅适用于现有的基本（非TEMPORARY)表。如果名称指向基本表，则使用该表。如果它指向一个TEMPORARY表，则会被忽略。如果名称应用于视图，则会发生ER_WRONG_OBJECT错误。否则，会发生ER_NO_SUCH_TABLE错误。

    使用UNLOCK TABLES来释放锁，LOCK TABLES来释放锁并获取其他锁，或者使用START TRANSACTION来释放锁并开始一个新事务。

    这个FLUSH TABLES变体允许在单个操作中刷新和锁定表。它提供了一个解决方案，因为当存在活动的LOCK TABLES ... READ时，不允许执行FLUSH TABLES。

    此操作不会执行隐式的UNLOCK TABLES，因此如果在存在任何活动的LOCK TABLES的情况下执行操作，或者在释放已获取的锁之前第二次使用它，都会导致错误。

    如果刷新的表是使用HANDLER打开的，则处理程序会被隐式刷新并丢失其位置。

  • FLUSH TABLES *tbl_name* [, *tbl_name*] ... FOR EXPORT

    这个FLUSH TABLES变体适用于InnoDB表。它确保对指定表的更改已刷新到磁盘，以便在服务器运行时可以进行二进制表副本的制作。

    此操作需要FLUSH_TABLES或RELOAD权限。因为它在准备导出表时获取表上的锁，所以还需要每个表的LOCK TABLES和SELECT权限。

    操作的工作方式如下：

    1. 它为命名表获取共享元数据锁。只要其他会话有活动事务修改了这些表或持有这些表的表锁，该操作就会阻塞。在获取锁之后，该操作会阻止试图更新表的事务，同时允许只读操作继续。

    2. 它检查表的所有存储引擎是否支持FOR EXPORT。如果有任何存储引擎不支持，将会发生ER_ILLEGAL_HA错误，操作将失败。

    3. 该操作通知每个表的存储引擎使表准备好导出。存储引擎必须确保任何待处理的更改都已写入磁盘。

    4. 该操作将会将会话置于锁表模式，以便在FOR EXPORT操作完成时不释放先前获取的元数据锁。

    此操作仅适用于现有的基本（非TEMPORARY）表。如果名称指的是基本表，则使用该表。如果指的是TEMPORARY表，则会被忽略。如果名称适用于视图，则会发生ER_WRONG_OBJECT错误。否则，会发生ER_NO_SUCH_TABLE错误。

    InnoDB支持对具有自己的.ibd文件文件的表进行FOR EXPORT（即启用了innodb_file_per_table设置的表）。InnoDB确保在FOR EXPORT操作通知时，任何更改都已刷新到磁盘。这允许在FOR EXPORT操作生效时制作表内容的二进制副本，因为.ibd文件是事务一致的，可以在服务器运行时复制。FOR EXPORT不适用于InnoDB系统表空间文件，也不适用于具有FULLTEXT索引的InnoDB表。

    FLUSH TABLES ...FOR EXPORT支持分区的InnoDB表。

    当收到FOR EXPORT通知时，InnoDB会将通常保存在内存中或在表空间文件之外的磁盘缓冲区中的某些类型数据写入磁盘。对于每个表，InnoDB还会在与表相同的数据库目录中生成一个名为*table_name*.cfg的文件。.cfg文件包含重新导入表空间文件所需的元数据，以便稍后重新导入到相同或不同的服务器中。

    当FOR EXPORT操作完成时，InnoDB已经将所有脏页刷新到表数据文件中。在刷新之前，任何更改缓冲区条目都会被合并。此时，表被锁定并处于静止状态：表在磁盘上处于事务一致状态，您可以将.ibd表空间文件与相应的.cfg文件一起复制，以获得这些表的一致快照。

    要重新导入复制的表数据到 MySQL 实例的过程，请参见第 17.6.1.3 节，“导入 InnoDB 表”。

    在处理完表之后，请使用UNLOCK TABLES释放锁定，LOCK TABLES释放锁定并获取其他锁定，或者使用START TRANSACTION释放锁定并开始新事务。

    在会话中执行这些语句之一时，尝试使用FLUSH TABLES ... FOR EXPORT会产生错误：

    FLUSH TABLES ... WITH READ LOCK
FLUSH TABLES ... FOR EXPORT
LOCK TABLES ... READ
LOCK TABLES ... WRITE

    在会话中有效时，尝试使用任何这些语句会产生错误：FLUSH TABLES ... FOR EXPORT。

    FLUSH TABLES WITH READ LOCK
FLUSH TABLES ... WITH READ LOCK
FLUSH TABLES ... FOR EXPORT

原文：dev.mysql.com/doc/refman/8.0/en/kill.html

15.7.8.4 KILL Statement

KILL [CONNECTION | QUERY] *processlist_id*

每个连接到mysqld的运行在一个单独的线程中。您可以使用KILL *processlist_id*语句终止一个线程。

线程进程列表标识符可以从INFORMATION_SCHEMA PROCESSLIST表的ID列，SHOW PROCESSLIST输出的Id列以及性能模式threads表的PROCESSLIST_ID列中确定。当前线程的值由CONNECTION_ID()函数返回。

KILL允许使用可选的CONNECTION或QUERY修饰符：

  • KILL CONNECTION与没有修饰符的KILL相同：它终止与给定processlist_id相关联的连接，在终止连接正在执行的任何语句之后。

  • KILL QUERY终止连接当前正在执行的语句，但保持连接本身不变。

查看可终止的线程取决于PROCESS权限：

  • 没有PROCESS，您只能看到自己的线程。

  • 使用PROCESS，您可以看到所有线程。

终止线程和语句的能力取决于CONNECTION_ADMIN权限和已弃用的SUPER权限：

  • 没有CONNECTION_ADMIN或SUPER，您只能终止自己的线程和语句。

  • 使用CONNECTION_ADMIN或SUPER，您可以终止所有线程和语句，但要影响正在使用SYSTEM_USER权限执行的线程或语句，您自己的会话还必须具有SYSTEM_USER权限。

您还可以使用mysqladmin processlist和mysqladmin kill命令来检查和终止线程。

当您使用KILL时，会为线程设置特定于线程的 kill 标志。在大多数情况下，线程可能需要一些时间才能终止，因为 kill 标志仅在特定间隔检查：

  • 在SELECT操作期间，对于ORDER BY和GROUP BY循环，每次读取一块行后都会检查标志。如果设置了 kill 标志，则会中止语句。

  • 进行使表复制的ALTER TABLE操作会定期检查 kill 标志，以便从原始表中读取每几行复制的行。如果设置了 kill 标志，则会中止语句并删除临时表。

    KILL语句会立即返回而不等待确认，但 kill 标志检查会在相当短的时间内中止操作。中止操作以执行任何必要的清理也需要一些时间。

  • 在UPDATE或DELETE操作期间，每次读取块和每次更新或删除行后都会检查 kill 标志。如果设置了 kill 标志，则会中止语句。如果您没有使用事务，则更改不会回滚。

  • GET_LOCK()会中止并返回NULL。

  • 如果线程在表锁处理程序中（状态：Locked），则表锁会被快速中止。

  • 如果线程在写调用中等待空闲磁盘空间，则会用“磁盘已满”错误消息中止写操作。

  • EXPLAIN ANALYZE会中止并打印输出的第一行。这适用于 MySQL 8.0.20 及更高版本。

警告

在MyISAM表上终止REPAIR TABLE或OPTIMIZE TABLE操作会导致表损坏且无法使用。在您再次优化或修复它之前（无中断），对这样的表的任何读取或写入都会失败。

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

15.7.8.5 LOAD INDEX INTO CACHE Statement

LOAD INDEX INTO CACHE
  *tbl_index_list* [, *tbl_index_list*] ...

*tbl_index_list*:
  *tbl_name*
    [PARTITION (*partition_list*)]
    [{INDEX|KEY} (*index_name*[, *index_name*] ...)]
    [IGNORE LEAVES]

*partition_list*: {
    *partition_name*[, *partition_name*] ...
  | ALL
}

LOAD INDEX INTO CACHE语句将表索引预加载到由显式CACHE INDEX语句分配的关键缓存中，否则将预加载到默认关键缓存中。

LOAD INDEX INTO CACHE仅适用于MyISAM表，包括分区的MyISAM表。此外，可以为分区表的索引预加载一个、几个或所有分区。

IGNORE LEAVES修饰符仅导致预加载索引的非叶节点的块。

IGNORE LEAVES也支持分区MyISAM表。

以下语句预加载表t1和t2的索引节点（索引块）：

mysql> LOAD INDEX INTO CACHE t1, t2 IGNORE LEAVES;
+---------+--------------+----------+----------+
| Table   | Op           | Msg_type | Msg_text |
+---------+--------------+----------+----------+
| test.t1 | preload_keys | status   | OK       |
| test.t2 | preload_keys | status   | OK       |
+---------+--------------+----------+----------+

此语句从t1预加载所有索引块。它仅从t2预加载非叶节点的块。

LOAD INDEX INTO CACHE的语法允许您指定应预加载表中的哪些索引。但是，实现会将表的所有索引预加载到缓存中，因此除了表名之外，没有理由指定其他内容。

可以预加载分区MyISAM表的特定分区上的索引。例如，以下 2 个语句中，第一个预加载分区表pt的分区p0的索引，而第二个预加载相同表的分区p1和p3的索引：

LOAD INDEX INTO CACHE pt PARTITION (p0);
LOAD INDEX INTO CACHE pt PARTITION (p1, p3);

要为表pt中的所有分区预加载索引，您可以使用以下两个语句中的任何一个：

LOAD INDEX INTO CACHE pt PARTITION (ALL);

LOAD INDEX INTO CACHE pt;

刚刚显示的两个语句是等效的，发出任何一个都具有完全相同的效果。换句话说，如果您希望为分区表的所有分区预加载索引，则PARTITION (ALL)子句是可选的。

当为多个分区预加载索引时，分区不需要连续，并且不需要按任何特定顺序列出它们的名称。

LOAD INDEX INTO CACHE ... IGNORE LEAVES 除非表中所有索引具有相同的块大小，否则会失败。要确定表的索引块大小，请使用myisamchk -dv并检查Blocksize列。

原文：dev.mysql.com/doc/refman/8.0/en/reset.html

15.7.8.6 RESET 语句

RESET *reset_option* [, *reset_option*] ...

*reset_option*: {
    MASTER
  | REPLICA
  | SLAVE
}

RESET 语句用于清除各种服务器操作的状态。您必须具有 RELOAD 权限才能执行 RESET。

有关删除持久化全局系统变量的 RESET PERSIST 语句的信息，请参见 第 15.7.8.7 节，“RESET PERSIST Statement”。

RESET 作为 FLUSH 语句的更强版本。参见 第 15.7.8.3 节，“FLUSH Statement”。

RESET 语句会导致隐式提交。参见 第 15.3.3 节，“导致隐式提交的语句”。

以下列表描述了允许的 RESET 语句 reset_option 值：

  • RESET MASTER

    删除索引文件中列出的所有二进制日志，将二进制日志索引文件重置为空，并创建一个新的二进制日志文件。

  • RESET REPLICA

    使复制忘记其在源二进制日志中的复制位置。还通过删除任何现有的中继日志文件并开始一个新的中继日志来重置中继日志。从 MySQL 8.0.22 开始，请使用 RESET REPLICA 替代 RESET SLAVE。

原文：dev.mysql.com/doc/refman/8.0/en/reset-persist.html

15.7.8.7 RESET PERSIST 语句

RESET PERSIST [[IF EXISTS] *system_var_name*]

RESET PERSIST从数据目录中的mysqld-auto.cnf选项文件中删除持久化的全局系统变量设置。移除持久化系统变量会导致该变量不再从mysqld-auto.cnf在服务器启动时初始化。有关持久化系统变量和mysqld-auto.cnf文件的更多信息，请参见第 7.1.9.3 节，“持久化系统变量”。

在 MySQL 8.0.32 之前，此语句不适用于变量名包含点字符（.）的变量，例如MyISAM多键缓存变量和组件注册的变量。（Bug #33417357）

执行RESET PERSIST所需的权限取决于要移除的系统变量类型：

  • 对于动态系统变量，此语句需要SYSTEM_VARIABLES_ADMIN权限（或已弃用的SUPER权限）。

  • 对于只读系统变量，此语句需要SYSTEM_VARIABLES_ADMIN和PERSIST_RO_VARIABLES_ADMIN权限。

参见第 7.1.9.1 节，“系统变量权限”。

根据变量名和IF EXISTS子句是否存在，RESET PERSIST语句有以下形式：

  • 要从mysqld-auto.cnf中移除所有持久化变量，请使用RESET PERSIST而不命名任何系统变量：

    RESET PERSIST;

    你必须拥有权限来移除mysqld-auto.cnf中包含的动态和只读系统变量，如果这两种变量都存在。

  • 要从mysqld-auto.cnf中移除特定的持久化变量，请在语句中命名它：

    RESET PERSIST *system_var_name*;

    这包括插件系统变量，即使插件当前未安装。如果变量不存在于文件中，则会发生错误。

  • 要从mysqld-auto.cnf中移除特定的持久化变量，但是如果该变量不存在于文件中，则产生警告而不是错误，请在先前的语法中添加一个IF EXISTS子句：

    RESET PERSIST IF EXISTS *system_var_name*;

RESET PERSIST不受persisted_globals_load系统变量值的影响。

RESET PERSIST会影响性能模式persisted_variables表的内容，因为表内容对应于mysqld-auto.cnf文件的内容。另一方面，因为RESET PERSIST不会改变变量值，所以在服务器重新启动之前，它不会对性能模式variables_info表的内容产生影响。

关于清除其他服务器操作状态的RESET语句变体的信息，请参阅第 15.7.8.6 节，“RESET Statement”。

原文：dev.mysql.com/doc/refman/8.0/en/restart.html

15.7.8.8 RESTART Statement

RESTART

此语句停止并重新启动 MySQL 服务器。它需要SHUTDOWN权限。

RESTART的一个用途是当无法或不方便在服务器主机上获得 MySQL 服务器的命令行访问以重新启动时。例如，可以在运行时使用SET PERSIST_ONLY对系统变量进行配置更改，这些变量只能在服务器启动时设置，但服务器仍然必须重新启动才能使这些更改生效。RESTART语句提供了一种在客户端会话中执行此操作的方法，而无需在服务器主机上需要命令行访问。

注意

执行RESTART语句后，客户端可以预期当前连接将丢失。如果启用了自动重新连接，则在服务器重新启动后重新建立连接。否则，必须手动重新建立连接。

成功执行RESTART操作需要mysqld在具有可用于检测为重新启动目的而执行的服务器关闭的监控进程的环境中运行：

  • 在存在监控进程的情况下，RESTART导致mysqld终止，以便监控进程可以确定应启动新的mysqld实例。

  • 如果没有监控进程存在，RESTART将失败并显示错误。

这些平台为RESTART语句提供了必要的监控支持：

  • Windows，在将mysqld作为 Windows 服务或独立运行时。(mysqld分叉，一个进程充当监视器，另一个进程充当服务器。)

  • 使用 systemd 或mysqld_safe管理mysqld的 Unix 和类 Unix 系统。

要配置监控环境，使mysqld启用RESTART语句：

  1. 在启动mysqld之前，将MYSQLD_PARENT_PID环境变量设置为启动mysqld的进程的进程 ID 的值。

  2. 当mysqld由于使用RESTART语句而执行关闭时，它会返回退出码 16。

  3. 当监控过程检测到退出码为 16 时，它会重新启动mysqld。否则，它会退出。

下面是在bash shell 中实现的最小示例：

#!/bin/bash

export MYSQLD_PARENT_PID=$$

export MYSQLD_RESTART_EXIT=16

while true ; do
  bin/mysqld *mysqld options here*
  if [ $? -ne $MYSQLD_RESTART_EXIT ]; then
    break
  fi
done

在 Windows 上，用于实现RESTART的分叉使得确定要附加到进行调试的服务器进程更加困难。为了缓解这个问题，使用--gdb启动服务器会抑制分叉，除了设置调试环境的其他操作。在非调试设置中，可以使用--no-monitor 仅用于抑制监控进程的分叉。对于使用--gdb或--no-monitor启动的服务器，执行RESTART会导致服务器简单地退出而不重新启动。

Com_restart状态变量跟踪RESTART语句的数量。因为状态变量在每次服务器启动时初始化，并且不会跨重启持续存在，Com_restart通常值为零，但如果执行了RESTART语句但失败了，它可能是非零值。

原文：dev.mysql.com/doc/refman/8.0/en/shutdown.html

15.7.8.9 关闭语句

SHUTDOWN

此语句停止 MySQL 服务器。它需要SHUTDOWN 权限。

SHUTDOWN 提供了一个 SQL 级别的接口，可以使用 mysqladmin shutdown 命令或 mysql_shutdown() C API 函数来实现相同的功能。成功的 SHUTDOWN 序列包括检查权限、验证参数，并向客户端发送一个 OK 数据包。然后服务器关闭。

Com_shutdown 状态变量跟踪 SHUTDOWN 语句的数量。因为状态变量在每次服务器启动时初始化，并且在重新启动时不会保留，所以 Com_shutdown 通常值为零，但如果执行了但失败了 SHUTDOWN 语句，则可能为非零。

另一种停止服务器的方法是发送一个 SIGTERM 信号，可以由 root 或拥有服务器进程的帐户执行。SIGTERM 使得可以在不连接到服务器的情况下执行服务器关闭。参见 第 6.10 节，“MySQL 中的 Unix 信号处理”。

15.8 实用语句

原文：dev.mysql.com/doc/refman/8.0/en/sql-utility-statements.html

15.8.1 DESCRIBE 语句

15.8.2 EXPLAIN 语句

15.8.3 HELP 语句

15.8.4 USE 语句

15.8.1 描述语句

原文：dev.mysql.com/doc/refman/8.0/en/describe.html

DESCRIBE 和 EXPLAIN 语句是同义词，用于获取关于表结构或查询执行计划的信息。更多信息，请参见 第 15.7.7.5 节，“显示列语句”，以及 第 15.8.2 节，“解释语句”。

15.8.2 EXPLAIN Statement

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

{EXPLAIN | DESCRIBE | DESC}
    *tbl_name* [*col_name* | *wild*]

{EXPLAIN | DESCRIBE | DESC}
    [*explain_type*]
    {*explainable_stmt* | FOR CONNECTION *connection_id*}

{EXPLAIN | DESCRIBE | DESC} ANALYZE [FORMAT = TREE] *select_statement*

*explain_type*: {
    FORMAT = *format_name*
}

*format_name*: {
    TRADITIONAL
  | JSON
  | TREE
}

*explainable_stmt*: {
    SELECT statement
  | TABLE statement
  | DELETE statement
  | INSERT statement
  | REPLACE statement
  | UPDATE statement
}

DESCRIBE 和 EXPLAIN 语句是同义词。在实践中，DESCRIBE 关键字更常用于获取有关表结构的信息，而 EXPLAIN 用于获取查询执行计划（即 MySQL 如何执行查询的解释）。

以下讨论使用 DESCRIBE 和 EXPLAIN 关键字，但 MySQL 解析器将它们视为完全同义词。

  • 获取表结构信息

  • 获取执行计划信息

  • 使用 EXPLAIN ANALYZE 获取信息

获取表结构信息

DESCRIBE 提供了关于表中列的信息：

mysql> DESCRIBE City;
+------------+----------+------+-----+---------+----------------+
| Field      | Type     | Null | Key | Default | Extra          |
+------------+----------+------+-----+---------+----------------+
| Id         | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name       | char(35) | NO   |     |         |                |
| Country    | char(3)  | NO   | UNI |         |                |
| District   | char(20) | YES  | MUL |         |                |
| Population | int(11)  | NO   |     | 0       |                |
+------------+----------+------+-----+---------+----------------+

DESCRIBE 是 SHOW COLUMNS 的快捷方式。这些语句也显示视图的信息。SHOW COLUMNS 的描述提供了有关输出列的更多信息。请参阅 Section 15.7.7.5, “SHOW COLUMNS Statement”。

默认情况下，DESCRIBE 显示表中所有列的信息。col_name，如果提供，是表中列的名称。在这种情况下，语句仅显示指定列的信息。wild，如果提供，是一个模式字符串。它可以包含 SQL 的 % 和 _ 通配符字符。在这种情况下，语句仅显示名称与字符串匹配的列的输出。除非字符串包含空格或其他特殊字符，否则无需将字符串括在引号内。

DESCRIBE 语句是为了与 Oracle 兼容而提供的。

SHOW CREATE TABLE、SHOW TABLE STATUS 和 SHOW INDEX 语句也提供有关表的信息。请参阅 Section 15.7.7, “SHOW Statements”。

explain_format系统变量在 MySQL 8.0.32 中添加，对于用于获取有关表列信息的EXPLAIN输出没有影响。

获取执行计划信息

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

  • EXPLAIN适用于SELECT、DELETE、INSERT、REPLACE和UPDATE语句。在 MySQL 8.0.19 及更高版本中，它还适用于TABLE语句。

  • 当使用EXPLAIN解释可解释的语句时，MySQL 会显示有关语句执行计划的优化器信息。也就是说，MySQL 会解释它将如何处理该语句，包括有关表如何连接以及连接顺序的信息。有关使用EXPLAIN获取执行计划信息的信息，请参见 Section 10.8.2, “EXPLAIN Output Format”。

  • 当使用EXPLAIN与FOR CONNECTION *connection_id*而不是可解释的语句一起使用时，它会显示在指定连接中执行的语句的执行计划。请参见 Section 10.8.4, “Obtaining Execution Plan Information for a Named Connection”。

  • 对于可解释的语句，EXPLAIN生成额外的执行计划信息，可以使用SHOW WARNINGS显示。请参见 Section 10.8.3, “Extended EXPLAIN Output Format”。

  • EXPLAIN对于检查涉及分区表的查询很有用��请参见 Section 26.3.5, “Obtaining Information About Partitions”。

  • FORMAT选项可用于选择输出格式。TRADITIONAL以表格形式呈现输出。如果没有FORMAT选项，则默认为此格式。JSON格式以 JSON 格式显示信息。在 MySQL 8.0.16 及更高版本中，TREE提供类似树状的输出，比TRADITIONAL格式更精确地描述了查询处理的方式；它是唯一显示哈希连接使用情况的格式（请参见 Section 10.2.1.4, “Hash Join Optimization”），并且始终用于EXPLAIN ANALYZE。

    截至 MySQL 8.0.32，EXPLAIN使用的默认输出格式（即，当没有FORMAT选项时）由explain_format系统变量的值确定。此变量的确切影响将在本节后面描述。

EXPLAIN需要执行解释语句所需的相同权限。此外，EXPLAIN还需要对任何解释的视图具有SHOW VIEW权限。如果指定的连接属于不同用户，则EXPLAIN ... FOR CONNECTION还需要PROCESS权限。

MySQL 8.0.32 中引入的explain_format系统变量确定在显示查询执行计划时EXPLAIN的输出格式。此变量可以采用与FORMAT选项一起使用的任何值，另外还添加了DEFAULT作为TRADITIONAL的同义词。以下示例使用world数据库中的country表，该表可以从 MySQL: Other Downloads 获取：

mysql> USE world; # Make world the current database
Database changed

检查explain_format的值，我们看到它具有默认值，因此EXPLAIN（没有FORMAT选项）因此使用传统的表格输出：

mysql> SELECT @@explain_format;
+------------------+
| @@explain_format |
+------------------+
| TRADITIONAL      |
+------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT Name FROM country WHERE Code Like 'A%';
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | country | NULL       | range | PRIMARY       | PRIMARY | 12      | NULL |   17 |   100.00 | Using where |
+----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

如果将explain_format的值设置为TREE，然后重新运行相同的EXPLAIN语句，输出将使用类似树状的格式：

mysql> SET @@explain_format=TREE;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@explain_format;
+------------------+
| @@explain_format |
+------------------+
| TREE             |
+------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT Name FROM country WHERE Code LIKE 'A%';
+--------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                      |
+--------------------------------------------------------------------------------------------------------------+
| -> Filter: (country.`Code` like 'A%')  (cost=3.67 rows=17)
 -> Index range scan on country using PRIMARY over ('A' <= Code <= 'A????????')  (cost=3.67 rows=17)  |
+--------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

如前所述，FORMAT选项会覆盖此设置。使用FORMAT=JSON而不是FORMAT=TREE执行相同的EXPLAIN语句，可以看到这一点：

mysql> EXPLAIN FORMAT=JSON SELECT Name FROM country WHERE Code LIKE 'A%';
+------------------------------------------------------------------------------+
| EXPLAIN                                                                      |
+------------------------------------------------------------------------------+
| {
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "3.67"
    },
    "table": {
      "table_name": "country",
      "access_type": "range",
      "possible_keys": [
        "PRIMARY"
      ],
      "key": "PRIMARY",
      "used_key_parts": [
        "Code"
      ],
      "key_length": "12",
      "rows_examined_per_scan": 17,
      "rows_produced_per_join": 17,
      "filtered": "100.00",
      "cost_info": {
        "read_cost": "1.97",
        "eval_cost": "1.70",
        "prefix_cost": "3.67",
        "data_read_per_join": "16K"
      },
      "used_columns": [
        "Code",
        "Name"
      ],
      "attached_condition": "(`world`.`country`.`Code` like 'A%')"
    }
  }
}                                                                              |
+------------------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

要将EXPLAIN的默认输出返回到表格格式，请将explain_format设置为TRADITIONAL。或者，您可以将其设置为DEFAULT，效果相同，如下所示：

mysql> SET @@explain_format=DEFAULT;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@explain_format;
+------------------+
| @@explain_format |
+------------------+
| TRADITIONAL      |
+------------------+
1 row in set (0.00 sec)

借助EXPLAIN，您可以看到应该在哪些表上添加索引，以便通过使用索引查找行来使语句执行更快。您还可以使用EXPLAIN来检查优化器是否以最佳顺序连接表。为了提示优化器使用与在SELECT语句中命名表的顺序相对应的连接顺序，可以在语句开头使用SELECT STRAIGHT_JOIN而不仅仅是SELECT。（参见 Section 15.2.13, “SELECT Statement”.）

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

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

注意

MySQL Workbench 具有可视化解释功能，提供 EXPLAIN 输出的可视化表示。请参阅 教程：使用 Explain 改进查询性能。

使用 EXPLAIN ANALYZE 获取信息

MySQL 8.0.18 引入了 EXPLAIN ANALYZE，它运行一个语句并生成带有时间和额外基于迭代器的信息的 EXPLAIN 输出，展示优化器的期望与实际执行的匹配情况。对于每个迭代器，提供以下信息：

  • 预估执行成本

    （某些迭代器不受成本模型考虑，因此不包括在估计中。）

  • 预估返回的行数

  • 返回第一行所需的时间

  • 执行此迭代器所花费的时间（包括子迭代器，但不包括父迭代器），以毫秒表示。

    （当存在多个循环时，此数字显示每个循环的平均时间。）

  • 迭代器返回的行数

  • 循环次数

查询执行信息使用 TREE 输出格式显示，其中节点表示迭代器。EXPLAIN ANALYZE 总是使用 TREE 输出格式。在 MySQL 8.0.21 及更高版本中，可以选择使用 FORMAT=TREE 明确指定；不支持除 TREE 之外的其他格式。

EXPLAIN ANALYZE 可以与 SELECT 语句一起使用，也可以与多表 UPDATE 和 DELETE 语句一起使用。从 MySQL 8.0.19 开始，还可以与 TABLE 语句一起使用。

从 MySQL 8.0.20 开始，您可以使用 KILL QUERY 或 CTRL-C 终止此语句。

EXPLAIN ANALYZE 不能与 FOR CONNECTION 一起使用。

示例输出：

mysql> EXPLAIN ANALYZE SELECT * FROM t1 JOIN t2 ON (t1.c1 = t2.c2)\G
*************************** 1\. row ***************************
EXPLAIN: -> Inner hash join (t2.c2 = t1.c1)  (cost=4.70 rows=6)
(actual time=0.032..0.035 rows=6 loops=1)
 -> Table scan on t2  (cost=0.06 rows=6)
(actual time=0.003..0.005 rows=6 loops=1)
 -> Hash
 -> Table scan on t1  (cost=0.85 rows=6)
(actual time=0.018..0.022 rows=6 loops=1)

mysql> EXPLAIN ANALYZE SELECT * FROM t3 WHERE i > 8\G
*************************** 1\. row ***************************
EXPLAIN: -> Filter: (t3.i > 8)  (cost=1.75 rows=5)
(actual time=0.019..0.021 rows=6 loops=1)
 -> Table scan on t3  (cost=1.75 rows=15)
(actual time=0.017..0.019 rows=15 loops=1)

mysql> EXPLAIN ANALYZE SELECT * FROM t3 WHERE pk > 17\G
*************************** 1\. row ***************************
EXPLAIN: -> Filter: (t3.pk > 17)  (cost=1.26 rows=5)
(actual time=0.013..0.016 rows=5 loops=1)
 -> Index range scan on t3 using PRIMARY  (cost=1.26 rows=5)
(actual time=0.012..0.014 rows=5 loops=1)

示例输出中使用的表是通过以下显示的语句创建的：

CREATE TABLE t1 (
    c1 INTEGER DEFAULT NULL,
    c2 INTEGER DEFAULT NULL
);

CREATE TABLE t2 (
    c1 INTEGER DEFAULT NULL,
    c2 INTEGER DEFAULT NULL
);

CREATE TABLE t3 (
    pk INTEGER NOT NULL PRIMARY KEY,
    i INTEGER DEFAULT NULL
);

输出中显示的 actual time 的值以毫秒表示。

截至 MySQL 8.0.32，explain_format系统变量对EXPLAIN ANALYZE有以下影响：

  • 如果此变量的值为TRADITIONAL或TREE（或同义词DEFAULT），EXPLAIN ANALYZE将使用TREE格式。这确保了该语句继续默认使用TREE格式，就像在引入explain_format之前一样。

  • 如果explain_format的值为JSON，则除非在语句中指定FORMAT=TREE，否则EXPLAIN ANALYZE会返回错误。这是因为EXPLAIN ANALYZE仅支持TREE输出格式。

我们在这里说明了第二点描述的行为，重复使用了前一个示例中的最后一个EXPLAIN ANALYZE语句：

mysql> SET @@explain_format=JSON;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@explain_format;
+------------------+
| @@explain_format |
+------------------+
| JSON             |
+------------------+
1 row in set (0.00 sec)

mysql> EXPLAIN ANALYZE SELECT * FROM t3 WHERE pk > 17\G
ERROR 1235 (42000): This version of MySQL doesn't yet support 'EXPLAIN ANALYZE with JSON format' 
mysql> EXPLAIN ANALYZE FORMAT=TRADITIONAL SELECT * FROM t3 WHERE pk > 17\G
ERROR 1235 (42000): This version of MySQL doesn't yet support 'EXPLAIN ANALYZE with TRADITIONAL format' 
mysql> EXPLAIN ANALYZE FORMAT=TREE SELECT * FROM t3 WHERE pk > 17\G
*************************** 1\. row ***************************
EXPLAIN: -> Filter: (t3.pk > 17)  (cost=1.26 rows=5)
(actual time=0.013..0.016 rows=5 loops=1)
 -> Index range scan on t3 using PRIMARY  (cost=1.26 rows=5)
(actual time=0.012..0.014 rows=5 loops=1)

使用FORMAT=TRADITIONAL或FORMAT=JSON与EXPLAIN ANALYZE总是会引发错误，无论explain_format的值如何。

从 MySQL 8.0.33 开始，EXPLAIN ANALYZE和EXPLAIN FORMAT=TREE输出中的数字将根据以下规则进行格式化：

  • 0.001-999999.5 范围内的数字以十进制数形式打印。

    小于 1000 的十进制数有三个有效数字；其余的有四、五或六个。

  • 超出 0.001-999999.5 范围的数字以工程格式打印。这些值的示例是1.23e+9和934e-6。

  • 不会打印尾随的零。例如，我们打印2.3而不是2.30，打印1.2e+6而不是1.20e+6。

  • 小于1e-12的数字打印为0。

15.8.3 HELP 语句

原文：dev.mysql.com/doc/refman/8.0/en/help.html

HELP '*search_string*'

HELP语句从 MySQL 参考手册返回在线信息。其正确操作要求在mysql数据库中初始化帮助主题信息（参见 Section 7.1.17, “服务器端帮助支持”）。

HELP语句搜索给定搜索字符串的帮助表，并显示搜索结果。搜索字符串不区分大小写。

搜索字符串可以包含通配符字符%和_。这些字符的含义与使用LIKE运算符执行的模式匹配操作相同。例如，HELP 'rep%'返回以rep开头的主题列表。

HELP语句不需要像；或\G这样的终止符。

HELP语句理解几种类型的搜索字符串：

  • 在最一般的级别上，使用contents检索顶级帮助类别的列表：

    HELP 'contents'

  • 要获取给定帮助类别（如Data Types）中主题的列表，请使用类别名称：

    HELP 'data types'

  • 要获取有关特定帮助主题（如ASCII()函数或CREATE TABLE语句）的帮助，请使用相关的关键字或关键字：

    HELP 'ascii'
HELP 'create table'

换句话说，搜索字符串匹配一个类别、多个主题或一个主题。以下描述指示结果集可能采用的形式。

  • 空结果

    未找到与搜索字符串匹配的结果。

    示例：HELP 'fake'

    结果：

    Nothing found
Please try to run 'help contents' for a list of all accessible topics

  • 包含单行结果集

    这意味着搜索字符串找到了帮助主题。结果包括以下项目：

    • name：主题名称。

    • description：主题的描述性帮助文本。

    • example：一个或多个用法示例。（可能为空。）

    示例：HELP 'log'

    结果：

    Name: 'LOG'
Description:
Syntax:
LOG(X), LOG(B,X)

If called with one parameter, this function returns the natural
logarithm of X. If X is less than or equal to 0.0E0, the function
returns NULL and a warning "Invalid argument for logarithm" is
reported. Returns NULL if X or B is NULL.

The inverse of this function (when called with a single argument) is
the EXP() function.

URL: https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html

Examples:
mysql> SELECT LOG(2);
        -> 0.69314718055995
mysql> SELECT LOG(-2);
        -> NULL

  • 主题列表。

    这意味着搜索字符串匹配了多个帮助主题。

    示例：HELP 'status'

    结果：

    Many help items for your request exist.
To make a more specific request, please type 'help <item>',
where <item> is one of the following topics:
   FLUSH
   SHOW
   SHOW ENGINE
   SHOW FUNCTION STATUS
   SHOW MASTER STATUS
   SHOW PROCEDURE STATUS
   SHOW REPLICA STATUS
   SHOW SLAVE STATUS
   SHOW STATUS
   SHOW TABLE STATUS

  • 主题列表。

    如果搜索字符串匹配一个类别，也会显示列表。

    示例：HELP 'functions'

    结果：

    You asked for help about help category: "Functions"
For more information, type 'help <item>', where <item> is one of the following
categories:
   Aggregate Functions and Modifiers
   Bit Functions
   Cast Functions and Operators
   Comparison Operators
   Date and Time Functions
   Encryption Functions
   Enterprise Encryption Functions
   Flow Control Functions
   GROUP BY Functions and Modifiers
   GTID
   Information Functions
   Internal Functions
   Locking Functions
   Logical Operators
   Miscellaneous Functions
   Numeric Functions
   Performance Schema Functions
   Spatial Functions
   String Functions
   Window Functions
   XML

15.8.4 USE 语句

原文：dev.mysql.com/doc/refman/8.0/en/use.html

USE *db_name*

USE语句告诉 MySQL 使用命名的数据库作为后续语句的默认（当前）数据库。此语句需要数据库或其中某个对象的一些权限。

在会话结束或发出另一个USE语句之前，命名的数据库将保持默认状态：

USE db1;
SELECT COUNT(*) FROM mytable;   # selects from db1.mytable
USE db2;
SELECT COUNT(*) FROM mytable;   # selects from db2.mytable

数据库名称必须在单行上指定。数据库名称中不支持换行符。

通过USE语句将特定数据库设置为默认数据库并不妨碍访问其他数据库中的表。以下示例访问了db1数据库中的author表和db2数据库中的editor表：

USE db1;
SELECT author_name,editor_name FROM author,db2.editor
  WHERE author.editor_id = db2.editor.editor_id;

第十六章 MySQL 数据字典

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

目录

16.1 数据字典模式

16.2 基于文件的元数据存储的移除

16.3 字典数据的事务性存储

16.4 字典对象缓存

16.5 INFORMATION_SCHEMA 和数据字典集成

16.6 序列化字典信息（SDI）

16.7 数据字典使用差异

16.8 数据字典限制

MySQL Server 包含一个事务性数据字典，用于存储有关数据库对象的信息。在以前的 MySQL 版本中，字典数据存储在元数据文件、非事务性表和存储引擎特定的数据字典中。

本章描述了数据字典的主要特点、优点、使用差异和限制。有关数据字典功能的其他影响，请参阅 MySQL 8.0 发行说明中的“数据字典注释”部分。

MySQL 数据字典的优点包括：

  • 统一存储字典数据的集中式数据字典模式的简单性。参见 第 16.1 节，“数据字典模式”。

  • 基于文件的元数据存储的移除。参见 第 16.2 节，“基于文件的元数据存储的移除”。

  • 字典数据的事务性、崩溃安全存储。参见 第 16.3 节，“字典数据的事务性存储”。

  • 字典对象的统一和集中式缓存。参见 第 16.4 节，“字典对象缓存”。

  • 对一些 INFORMATION_SCHEMA 表进行了更简单和改进的实现。参见 第 16.5 节，“INFORMATION_SCHEMA 和数据字典集成”。

  • 原子 DDL。参见 第 15.1.1 节，“原子数据定义语句支持”。

重要

启用数据字典的服务器与没有数据字典的服务器相比，存在一些一般的操作差异；参见第 16.7 节，“数据字典使用差异”。此外，对于升级到 MySQL 8.0，升级过程与以前的 MySQL 版本有些不同，并且需要您通过检查特定的先决条件来验证安装的升级准备情况。更多信息，请参见第三章，升级 MySQL，特别是第 3.6 节，“准备安装以进行升级”。

16.1 数据字典模式

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

数据字典表受到保护，只能在 MySQL 的调试版本中访问。但是，MySQL 支持通过INFORMATION_SCHEMA表和SHOW语句访问存储在数据字典表中的数据。有关组成数据字典的表的概述，请参阅数据字典表。

MySQL 系统表仍然存在于 MySQL 8.0 中，并且可以通过在mysql系统数据库上发出SHOW TABLES语句来查看。通常，MySQL 数据字典表和系统表之间的区别在于数据字典表包含执行 SQL 查询所需的元数据，而系统表包含辅助数据，如时区和帮助信息。 MySQL 系统表和数据字典表在升级方式上也有所不同。MySQL 服务器管理数据字典升级。请参阅数据字典如何升级。升级 MySQL 系统表需要运行完整的 MySQL 升级过程。请参阅第 3.4 节，“MySQL 升级过程升级了什么”。

数据字典如何升级

MySQL 的新版本可能包含对数据字典表定义的更改。这些更改存在于新安装的 MySQL 版本中，但在执行 MySQL 二进制文件的就地升级时，更改将在使用新二进制文件重新启动 MySQL 服务器时应用。在启动时，服务器的数据字典版本将与数据字典中存储的版本信息进行比较，以确定是否应该升级数据字典表。如果需要升级并且受支持，服务器将使用更新的定义创建数据字典表，将持久化的元数据复制到新表中，以原子方式用新表替换旧表，并重新初始化数据字典。如果不需要升级，则启动将继续而不更新数据字典表。

数据字典表的升级是一个原子操作，这意味着所有必要的数据字典表都会被升级，否则操作将失败。如果升级操作失败，服务器启动将以错误结束。在这种情况下，可以使用旧的服务器二进制文件和旧的数据目录来启动服务器。当再次使用新的服务器二进制文件启动服务器时，数据字典升级将重新尝试。

通常，在成功升级数据字典表后，不可能使用旧的服务器二进制文件重新启动服务器。因此，在升级数据字典表后，不支持将 MySQL 服务器二进制文件降级到先前的 MySQL 版本。

mysqld --no-dd-upgrade 选项可用于防止在启动时自动升级数据字典表。当指定 --no-dd-upgrade 时，如果服务器发现服务器的数据字典版本与数据字典中存储的版本不同，则启动将失败，并显示禁止数据字典升级的错误。

使用 MySQL 的调试版本查看数据字典表

数据字典表默认受保护，但可以通过使用带有调试支持的 MySQL 编译（使用-DWITH_DEBUG=1 CMake 选项）并指定+d,skip_dd_table_access_check debug 选项和修饰符来访问。有关编译调试版本的信息，请参见 Section 7.9.1.1, “Compiling MySQL for Debugging”。

警告

不建议直接修改或写入数据字典表，这可能导致您的 MySQL 实例无法运行。

在使用带有调试支持的 MySQL 编译后，使用此 SET 语句使数据字典表对mysql 客户端会话可见：

mysql> SET SESSION debug='+d,skip_dd_table_access_check';

使用此查询检索数据字典表的列表：

mysql> SELECT name, schema_id, hidden, type FROM mysql.tables where schema_id=1 AND hidden='System';

使用 SHOW CREATE TABLE 查看数据字典表定义。例如：

mysql> SHOW CREATE TABLE mysql.catalogs\G

16.2 移除基于文件的元数据存储

原文：dev.mysql.com/doc/refman/8.0/en/data-dictionary-file-removal.html

在先前的 MySQL 版本中，字典数据部分存储在元数据文件中。基于文件的元数据存储的问题包括昂贵的文件扫描、易受文件系统相关错误的影响、处理复制和崩溃恢复失败状态的复杂代码，以及缺乏可扩展性，使得难以为新功能和关系对象添加元数据。

下面列出的元数据文件已从 MySQL 中移除。除非另有说明，先前存储在元数据文件中的数据现在存储在数据字典表中。

  • .frm文件：表元数据文件。随着.frm文件的移除：

    • 由.frm文件结构施加的 64KB 表定义大小限制已被移除。

    • 信息模式TABLES表的VERSION列报告了一个硬编码值10，这是 MySQL 5.7 中使用的最后一个.frm文件版本。

  • .par文件：分区定义文件。InnoDB在 MySQL 5.7 中引入对InnoDB表的本机分区支持后停止使用分区定义文件。

  • .TRN文件：触发器命名空间文件。

  • .TRG文件：触发器参数文件。

  • .isl文件：InnoDB符号链接文件，包含在数据目录之外创建的 file-per-table 表空间文件的位置。

  • db.opt文件：数据库配置文件。这些文件，每个数据库目录一个，包含数据库默认字符集属性。

  • ddl_log.log文件：该文件包含由数据定义语句（如DROP TABLE和ALTER TABLE）生成的元数据操作记录。

16.3 字典数据的事务性存储

原文：dev.mysql.com/doc/refman/8.0/en/data-dictionary-transactional-storage.html

数据字典模式将字典数据存储在事务性（InnoDB）表中。数据字典表位于mysql数据库中，与非数据字典系统表一起。

数据字典表是在名为mysql.ibd的单个InnoDB表空间中创建的，该表空间位于 MySQL 数据目录中。mysql.ibd表空间文件必须位于 MySQL 数据目录中，其名称不能被修改或被其他表空间使用。

字典数据受到与保护存储在InnoDB表中的用户数据相同的提交、回滚和崩溃恢复功能的保护。

16.4 字典对象缓存

原文：dev.mysql.com/doc/refman/8.0/en/data-dictionary-object-cache.html

字典对象缓存是一个共享的全局缓存，用于将先前访问过的数据字典对象存储在内存中，以实现对象重用并最小化磁盘 I/O。与 MySQL 使用的其他缓存机制类似，字典对象缓存使用基于 LRU 的驱逐策略从内存中驱逐最近未使用的对象。

字典对象缓存包括存储不同对象类型的缓存分区。一些缓存分区大小限制是可配置的，而其他一些是硬编码的。

  • 表空间定义缓存分区：存储表空间定义对象。tablespace_definition_cache选项设置了可以存储在字典对象缓存中的表空间定义对象数量的限制。默认值为 256。

  • 模式定义缓存分区：存储模式定义对象。schema_definition_cache选项设置了可以存储在字典对象缓存中的模式定义对象数量的限制。默认值为 256。

  • 表定义缓存分区：存储表定义对象。对象限制设置为max_connections的值，其默认值为 151。

    表定义缓存分区与使用table_definition_cache配置选项配置的表定义缓存并行存在。这两个缓存都存储表定义，但为 MySQL 服务器的不同部分提供服务。一个缓存中的对象不依赖于另一个缓存中对象的存在。

  • 存储程序定义缓存分区：存储存储程序定义对象。stored_program_definition_cache选项设置了可以存储在字典对象缓存中的存储程序定义对象数量的限制。默认值为 256。

    存储程序定义缓存分区与使用stored_program_cache选项配置的存储过程和存储函数缓存并行存在。

    stored_program_cache选项为每个连接设置了缓存的存储过程或函数的软上限，并且每次连接执行存储过程或函数时都会检查该限制。另一方面，存储程序定义缓存分区是一个共享缓存，用于存储其他目的的存储程序定义对象。存储程序定义缓存分区中的对象的存在与存储过程缓存或存储函数缓存中对象的存在没有依赖关系，反之亦然。

  • 字符集定义缓存分区：存储字符集定义对象，并且具有硬编码的对象限制为 256。

  • 校对定义缓存分区：存储校对定义对象，并且具有硬编码的对象限制为 256。

有关字典对象缓存配置选项的有效值信息，请参考第 7.1.8 节，“服务器系统变量”。

16.5 INFORMATION_SCHEMA 和数据字典集成

原文：dev.mysql.com/doc/refman/8.0/en/data-dictionary-information-schema.html

引入数据字典后，以下 INFORMATION_SCHEMA 表被实现为数据字典表上的视图：

  • CHARACTER_SETS

  • CHECK_CONSTRAINTS

  • COLLATIONS

  • COLLATION_CHARACTER_SET_APPLICABILITY

  • COLUMNS

  • COLUMN_STATISTICS

  • EVENTS

  • FILES

  • INNODB_COLUMNS

  • INNODB_DATAFILES

  • INNODB_FIELDS

  • INNODB_FOREIGN

  • INNODB_FOREIGN_COLS

  • INNODB_INDEXES

  • INNODB_TABLES

  • INNODB_TABLESPACES

  • INNODB_TABLESPACES_BRIEF

  • INNODB_TABLESTATS

  • KEY_COLUMN_USAGE

  • KEYWORDS

  • PARAMETERS

  • PARTITIONS

  • REFERENTIAL_CONSTRAINTS

  • RESOURCE_GROUPS

  • ROUTINES

  • SCHEMATA

  • STATISTICS

  • ST_GEOMETRY_COLUMNS

  • ST_SPATIAL_REFERENCE_SYSTEMS

  • TABLES

  • TABLE_CONSTRAINTS

  • TRIGGERS

  • VIEWS

  • VIEW_ROUTINE_USAGE

  • VIEW_TABLE_USAGE

现在对这些表的查询更加高效，因为它们从数据字典表中获取信息，而不是通过其他更慢的方式。特别是，对于每个是数据字典表上视图的 INFORMATION_SCHEMA 表：

  • 服务器不再必须为 INFORMATION_SCHEMA 表的每个查询创建临时表。

  • 当底层数据字典表存储先前通过目录扫描（例如，枚举数据库名称或数据库中表名称）或文件打开操作（例如，从.frm文件中读取信息）获取的值时，INFORMATION_SCHEMA 现在使用表查找而不是查询这些值。（此外，即使对于非视图的 INFORMATION_SCHEMA 表，例如数据库和表名称等值也是通过数据字典查找而不需要目录或文件扫描获取的。）

  • 底层数据字典表上的索引允许优化器构建高效的查询执行计划，这在以前的实现中是不成立的，以前的实现是使用临时表处理 INFORMATION_SCHEMA 表的每个查询。

前述的改进也适用于显示与数据字典表上的视图对应的信息的SHOW语句。例如，SHOW DATABASES显示与SCHEMATA表相同的信息。

除了引入对数据字典表的视图之外，现在STATISTICS和TABLES表中包含的表统计信息现在被缓存以提高INFORMATION_SCHEMA查询性能。information_schema_stats_expiry系统变量定义了缓存表统计信息过期之前的时间段。默认值为 86400 秒（24 小时）。如果没有缓存的统计信息或统计信息已过期，则在查询表统计列时从存储引擎中检索统计信息。要随时更新给定表的缓存值，请使用ANALYZE TABLE。

information_schema_stats_expiry可以设置为0，以便INFORMATION_SCHEMA查询直接从存储引擎中检索最新的统计信息，这不如检索缓存的统计信息快。

更多信息，请参见 Section 10.2.3, “Optimizing INFORMATION_SCHEMA Queries”。

MySQL 8.0 中的INFORMATION_SCHEMA表与数据字典紧密相关，导致几个使用差异。请参见 Section 16.7, “Data Dictionary Usage Differences”。

16.6 序列化字典信息（SDI）

原文：dev.mysql.com/doc/refman/8.0/en/serialized-dictionary-information.html

除了在数据字典中存储有关数据库对象的元数据外，MySQL 还以序列化形式存储它。这些数据被称为序列化字典信息（SDI）。InnoDB将 SDI 数据存储在其表空间文件中。NDBCLUSTER将 SDI 数据存储在 NDB 字典中。其他存储引擎将 SDI 数据存储在为给定表创建的.sdi文件中，该文件位于表的数据库目录中。SDI 数据以紧凑的JSON格式生成。

所有InnoDB表空间文件中都存在序列化字典信息（SDI），临时表空间和撤销表空间文件除外。InnoDB表空间文件中的 SDI 记录仅描述表空间中包含的表和表对象。

SDI 数据通过对表进行 DDL 操作或CHECK TABLE FOR UPGRADE来更新。当 MySQL 服务器升级到新版本时，SDI 数据不会被更新。

SDI 数据的存在提供了元数据冗余。例如，如果数据字典不可用，可以使用ibd2sdi工具直接从InnoDB表空间文件中提取对象元数据。

对于InnoDB，一个 SDI 记录需要一个索引页，默认大小为 16KB。但是，SDI 数据经过压缩以减少存储占用空间。

对于由多个表空间组成的分区InnoDB表，SDI 数据存储在第一个分区的表空间文件中。

MySQL 服务器在 DDL 操作期间使用内部 API 来创建和维护 SDI 记录。

IMPORT TABLE语句根据.sdi文件中包含的信息导入MyISAM表。有关更多信息，请参见 Section 15.2.6, “IMPORT TABLE Statement”。

16.7 数据字典使用差异

原文：dev.mysql.com/doc/refman/8.0/en/data-dictionary-usage-differences.html

使用启用数据字典的 MySQL 服务器与没有数据字典的服务器相比，存在一些操作上的差异：

  • 以前，启用innodb_read_only系统变量仅阻止了InnoDB存储引擎的表的创建和删除。从 MySQL 8.0 开始，启用innodb_read_only阻止了所有存储引擎的这些操作。任何存储引擎的表创建和删除操作都会修改mysql系统数据库中的数据字典表，但这些表使用InnoDB存储引擎，在启用innodb_read_only时无法修改。对需要修改数据字典表的其他表操作也适用相同原则。例如：

    • ANALYZE TABLE失败，因为它更新了存储在数据字典中的表统计信息。

    • ALTER TABLE *tbl_name* ENGINE=*engine_name*失败，因为它更新了存储在数据字典中的存储引擎指定。

    注意

    启用innodb_read_only对mysql系统数据库中的非数据字典表也有重要影响。有关详细信息，请参阅第 17.14 节，“InnoDB 启动选项和系统变量”中对innodb_read_only的描述。

  • 以前，mysql系统数据库中的表对 DML 和 DDL 语句可见。从 MySQL 8.0 开始，数据字典表是不可见的，不能直接修改或查询。然而，在大多数情况下，有对应的INFORMATION_SCHEMA表可以查询。这使得在服务器开发进行时可以更改底层数据字典表，同时保持稳定的INFORMATION_SCHEMA接口供应用程序使用。

  • MySQL 8.0 中的INFORMATION_SCHEMA表与数据字典密切相关，导致了几个使用上的差异：

    • 以前，在STATISTICS和TABLES表中查询表统计信息的INFORMATION_SCHEMA查询直接从存储引擎中检索统计信息。从 MySQL 8.0 开始，默认情况下使用缓存的表统计信息。information_schema_stats_expiry系统变量定义缓存表统计信息过期之前的时间段。默认值为 86400 秒（24 小时）。（要随时更新给定表的缓存值，请使用ANALYZE TABLE。）如果没有缓存的统计信息或统计信息已过期，则在查询表统计列时从存储引擎中检索统计信息。要始终直接从存储引擎检索最新的统计信息，请将information_schema_stats_expiry设置为0。有关更多信息，请参见 Section 10.2.3, “Optimizing INFORMATION_SCHEMA Queries”。

    • 几个INFORMATION_SCHEMA表是数据字典表上的视图，这使得优化器可以使用这些基础表上的索引。因此，根据优化器的选择，INFORMATION_SCHEMA查询的结果行顺序可能与以前的结果不同。如果查询结果必须具有特定的行排序特性，请包含ORDER BY子句。

    • 对INFORMATION_SCHEMA表的查询可能以不同的大小写返回列名，而不同于早期的 MySQL 系列。应用程序应以不区分大小写的方式测试结果集列名。如果这不可行，一个解决方法是在选择列表中使用列别名，以返回所需大小写的列名。例如：

      SELECT TABLE_SCHEMA AS table_schema, TABLE_NAME AS table_name
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'users';

    • mysqldump 和 mysqlpump 不再转储INFORMATION_SCHEMA数据库，即使在命令行上明确命名。

    • CREATE TABLE *dst_tbl* LIKE *src_tbl* 要求src_tbl是一个基本表，如果是一个在数据字典表上的INFORMATION_SCHEMA表的视图，则会失败。

    • 以前，从INFORMATION_SCHEMA表中选择的列的结果集标题使用查询中指定的大写。这个查询生成一个带有table_name标题的结果集：

      SELECT table_name FROM INFORMATION_SCHEMA.TABLES;

      从 MySQL 8.0 开始，这些标题是大写的；前面的查询生成一个带有TABLE_NAME标题的结果集。如果需要，可以使用列别名来实现不同的大小写。例如：

      SELECT table_name AS 'table_name' FROM INFORMATION_SCHEMA.TABLES;

  • 数据目录影响着mysqldump和mysqlpump从mysql系统数据库中导出信息的方式：

    • 以前，可以导出mysql系统数据库中的所有表。从 MySQL 8.0 开始，mysqldump和mysqlpump只会导出该数据库中的非数据字典表。

    • 以前，在使用--all-databases选项时，不需要--routines和--events选项来包含存储过程和事件：导出包括mysql系统数据库，因此也包括包含存储过程和事件定义的proc和event表。从 MySQL 8.0 开始，event和proc表不再使用。相应对象的定义存储在数据字典表中，但这些表不会被导出。要在使用--all-databases进行导出时包含存储过程和事件，需要显式使用--routines和--events选项。

    • 以前，--routines选项需要proc表的SELECT权限。从 MySQL 8.0 开始，该表不再使用；--routines现在需要全局的SELECT权限。

    • 以前，可以一起导出存储过程和事件定义以及它们的创建和修改时间戳，通过导出proc和event表。从 MySQL 8.0 开始，这些表不再使用，因此无法导出时间戳。

  • 以前，创建包含非法字符的存储过程会产生警告。从 MySQL 8.0 开始，这将会报错。

16.8 数据字典限制

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

本节描述了 MySQL 数据字典引入的临时限制。

  • 在数据目录下手动创建数据库目录（例如，使用mkdir）是不受支持的。MySQL 服务器不会识别手动创建的数据库目录。

  • 由于写入存储、撤销日志和重做日志而不是.frm文件，DDL 操作需要更长时间。

第十七章 InnoDB 存储引擎

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

目录

17.1 InnoDB 简介

17.1.1 使用 InnoDB 表的好处

17.1.2 InnoDB 表的最佳实践

17.1.3 验证 InnoDB 是否为默认存储引擎

17.1.4 使用 InnoDB 进行测试和基准测试

17.2 InnoDB 和 ACID 模型

17.3 InnoDB 多版本

17.4 InnoDB 架构

17.5 InnoDB 内存结构

17.5.1 缓冲池

17.5.2 变更缓冲区

17.5.3 自适应哈希索引

17.5.4 日志缓冲区

17.6 InnoDB 磁盘结构

17.6.1 表

17.6.2 索引

17.6.3 表空间

17.6.4 双写缓冲区

17.6.5 重做日志

17.6.6 撤销日志

17.7 InnoDB 锁定和事务模型

17.7.1 InnoDB 锁定

17.7.2 InnoDB 事务模型

17.7.3 InnoDB 中由不同 SQL 语句设置的锁

17.7.4 幻影行

17.7.5 InnoDB 中的死锁

17.7.6 事务调度

17.8 InnoDB 配置

17.8.1 InnoDB 启动配置

17.8.2 为只读操作配置 InnoDB

17.8.3 InnoDB 缓冲池配置

17.8.4 为 InnoDB 配置线程并发性

17.8.5 配置后台 InnoDB I/O 线程的数量

17.8.6 在 Linux 上使用异步 I/O

17.8.7 配置 InnoDB I/O 容量

17.8.8 配置自旋锁轮询

17.8.9 清理配置

17.8.10 为 InnoDB 配置优化器统计信息

17.8.11 配置索引页的合并阈值

17.8.12 启用专用 MySQL 服务器的自动配置

17.9 InnoDB 表和页面压缩

17.9.1 InnoDB 表压缩

17.9.2 InnoDB 页面压缩

17.10 InnoDB 行格式

17.11 InnoDB 磁盘 I/O 和文件空间管理

17.11.1 InnoDB 磁盘 I/O

17.11.2 文件空间管理

17.11.3 InnoDB 检查点

17.11.4 表格碎片整理

17.11.5 使用 TRUNCATE TABLE 回收磁盘空间

17.12 InnoDB 和在线 DDL

17.12.1 在线 DDL 操作

17.12.2 在线 DDL 性能和并发性

17.12.3 在线 DDL 空间需求

17.12.4 在线 DDL 内存管理

17.12.5 配置在线 DDL 操作的并行线程

17.12.6 使用在线 DDL 简化 DDL 语句

17.12.7 在线 DDL 失败条件

17.12.8 在线 DDL 限制

17.13 InnoDB 数据静态加密

17.14 InnoDB 启动选项和系统变量

17.15 InnoDB INFORMATION_SCHEMA 表格

17.15.1 关于压缩的 InnoDB INFORMATION_SCHEMA 表格

17.15.2 InnoDB INFORMATION_SCHEMA 事务和锁信息

17.15.3 InnoDB INFORMATION_SCHEMA 模式对象表

17.15.4 InnoDB INFORMATION_SCHEMA 全文索引表格

17.15.5 InnoDB INFORMATION_SCHEMA 缓冲池表格

17.15.6 InnoDB INFORMATION_SCHEMA 指标表

17.15.7 InnoDB INFORMATION_SCHEMA 临时表信息表

17.15.8 从 INFORMATION_SCHEMA.FILES 检索 InnoDB 表空间元数据

17.16 InnoDB 与 MySQL 性能模式的集成

17.16.1 使用性能模式监视 InnoDB 表的 ALTER TABLE 进度

17.16.2 使用性能模式监视 InnoDB 互斥等待

17.17 InnoDB 监视器

17.17.1 InnoDB 监视器类型

17.17.2 启用 InnoDB 监视器

17.17.3 InnoDB 标准监视器和锁监视器输出

17.18 InnoDB 备份和恢复

17.18.1 InnoDB 备份

17.18.2 InnoDB 恢复

17.19 InnoDB 和 MySQL 复制

17.20 InnoDB memcached 插件

17.20.1 InnoDB memcached 插件的优势

17.20.2 InnoDB memcached 架构

17.20.3 设置 InnoDB memcached 插件

17.20.4 InnoDB memcached 多次获取和范围查询支持

17.20.5 InnoDB memcached 插件的安全考虑

17.20.6 为 InnoDB memcached 插件编写应用程序

17.20.7 InnoDB memcached 插件和复制

17.20.8 InnoDB memcached 插件内部

17.20.9 修复 InnoDB memcached 插件的故障

17.21 InnoDB 故障排除

17.21.1 修复 InnoDB I/O 问题

17.21.2 修复恢复失败

17.21.3 强制 InnoDB 恢复

17.21.4 修复 InnoDB 数据字典操作

17.21.5 InnoDB 错误处理

17.22 InnoDB 限制

17.23 InnoDB 限制和限制

17.1 InnoDB 简介

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

17.1.1 使用 InnoDB 表的好处

17.1.2 InnoDB 表的最佳实践

17.1.3 验证 InnoDB 是否为默认存储引擎

17.1.4 使用 InnoDB 进行测试和基准测试

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

InnoDB 的主要优势

  • 其 DML 操作遵循 ACID 模型，具有事务特性，包括提交、回滚和崩溃恢复功能，以保护用户数据。参见 第 17.2 节，“InnoDB 和 ACID 模型”。

  • 行级锁定和 Oracle 风格的一致性读取增加了多用户并发性和性能。参见 第 17.7 节，“InnoDB 锁定和事务模型”。

  • InnoDB 表会在磁盘上优化数据以便基于主键进行查询。每个 InnoDB 表都有一个称为聚集索引的主键索引，它组织数据以最小化主键查找的 I/O。参见 第 17.6.2.1 节，“聚集索引和辅助索引”。

  • 为了保持数据完整性，InnoDB 支持 FOREIGN KEY 约束。通过外键，插入、更新和删除操作会被检查，以确保它们不会导致相关表之间的不一致。参见 第 15.1.20.5 节，“FOREIGN KEY 约束”。

表 17.1 InnoDB 存储引擎特性

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

要比较InnoDB与 MySQL 提供的其他存储引擎的功能，请参见第十八章，“替代存储引擎”中的存储引擎功能表。

InnoDB 增强和新功能

有关InnoDB增强和新功能的信息，请参考：

  • 在第 1.3 节，“MySQL 8.0 中的新功能”中列出的InnoDB增强列表。

  • 发布说明。

其他 InnoDB 信息和资源

  • 有关InnoDB相关术语和定义，请参见 MySQL 词汇表。

  • 有关专门针对InnoDB存储引擎的论坛，请参见MySQL 论坛::InnoDB。

  • InnoDB以与 MySQL 相同的 GNU GPL 许可证第 2 版（1991 年 6 月）发布。有关 MySQL 许可的更多信息，请参见www.mysql.com/company/legal/licensing/。

17.1.1 使用 InnoDB 表的好处

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

InnoDB表具有以下好处：

  • 如果服务器因硬件或软件问题意外退出，无论在数据库中发生了什么，重新启动数据库后无需执行任何特殊操作。InnoDB崩溃恢复会自动完成在崩溃发生前提交的更改，并撤消正在进行但尚未提交的更改，使您可以从上次中断的地方重新启动并继续。参见第 17.18.2 节，“InnoDB 恢复”。

  • InnoDB存储引擎维护自己的缓冲池，将表和索引数据缓存在主内存中，随着数据的访问而访问。经常使用的数据直接从内存中处理。该缓存适用于许多类型的信息并加快处理速度。在专用数据库服务器上，通常将高达 80%的物理内存分配给缓冲池。参见第 17.5.1 节，“缓冲池”。

  • 如果将相关数据拆分到不同的表中，您可以设置强制执行引用完整性的外键。参见第 15.1.20.5 节，“外键约束”。

  • 如果数据在磁盘或内存中损坏，校验和机制会在您使用之前警告您有误的数据。innodb_checksum_algorithm变量定义了InnoDB使用的校验和算法。

  • 当为每个表设计具有适当主键列的数据库时，涉及这些列的操作会自动优化。在WHERE子句、ORDER BY子句、GROUP BY子句和连接操作中引用主键列非常快速。参见第 17.6.2.1 节，“聚簇索引和二级索引”。

  • 插入、更新和删除通过称为更改缓冲的自动机制进行优化。InnoDB不仅允许对同一表进行并发读写访问，还会缓存已更改的数据以简化磁盘 I/O。参见第 17.5.2 节，“更改缓冲”。

  • 性能优势不仅限于具有长时间运行查询的大型表。当从表中反复访问相同的行时，自适应哈希索引会接管，使这些查找变得更快，就像它们来自哈希表一样。参见第 17.5.3 节，“自适应哈希索引”。

  • 您可以压缩表和相关索引。参见第 17.9 节，“InnoDB 表和页面压缩”。

  • 您可以加密您的数据。参见第 17.13 节，“InnoDB 数据静态加密”。

  • 您可以创建和删除索引，并执行其他 DDL 操作，对性能和可用性的影响要小得多。参见第 17.12.1 节，“在线 DDL 操作”。

  • 截断文件表空间（file-per-table tablespace）非常快速，可以释放磁盘空间供操作系统重复使用，而不仅仅是InnoDB。参见第 17.6.3.2 节，“File-Per-Table Tablespaces”。

  • 表数据的存储布局对于BLOB和长文本字段，使用DYNAMIC行格式更有效。参见第 17.10 节，“InnoDB 行格式”。

  • 通过查询INFORMATION_SCHEMA表，您可以监控存储引擎的内部工作方式。参见第 17.15 节，“InnoDB INFORMATION_SCHEMA 表”。

  • 通过查询 Performance Schema 表，您可以监控存储引擎的性能详细信息。参见第 17.16 节，“InnoDB 与 MySQL Performance Schema 集成”。

  • 您可以在同一语句中混合使用InnoDB表和其他 MySQL 存储引擎的表。例如，您可以使用连接操作将InnoDB和MEMORY表的数据合并在单个查询中。

  • InnoDB已经为 CPU 效率和处理大数据量时的最大性能而设计。

  • InnoDB表可以处理大量数据，即使在文件大小限制为 2GB 的操作系统上也可以。

对于您可以应用于 MySQL 服务器和应用程序代码的InnoDB特定调优技术，请参见第 10.5 节，“针对 InnoDB 表进行优化”。

17.1.2 InnoDB 表的最佳实践

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

本节描述了使用InnoDB表时的最佳实践。

  • 为每个表指定一个主键，使用最常查询的列或列，或者如果没有明显的主键，则使用自增值。

  • 使用连接（joins）在从多个表中提取数据时，基于这些表中相同 ID 值进行连接。为了获得快速的连接性能，在连接列上定义外键，并在每个表中声明具有相同数据类型的列。添加外键可以确保引用列被索引，从而提高性能。外键还会将删除和更新传播到所有受影响的表，并防止在子表中插入数据，如果相应的 ID 在父表中不存在。

  • 关闭自动提交。每秒提交数百次会限制性能（受存储设备的写入速度限制）。

  • 将相关的 DML 操作组合成事务，通过使用START TRANSACTION和COMMIT语句将它们括起来。虽然您不希望经常提交，但也不希望发出运行数小时而不提交的大批量INSERT、UPDATE或DELETE语句。

  • 不要使用LOCK TABLES语句。InnoDB可以处理多个会话同时读写同一张表，而不会牺牲可靠性或高性能。要获得对一组行的独占写访问权限，请使用SELECT ... FOR UPDATE语法，仅锁定您打算更新的行。

  • 启用innodb_file_per_table变量或使用通用表空间将表的数据和索引放入单独的文件中，而不是系统表空间。innodb_file_per_table变量默认启用。

  • 评估您的数据和访问模式是否受益于InnoDB表或页面压缩功能。您可以压缩InnoDB表而不会牺牲读/写能力。

  • 使用--sql_mode=NO_ENGINE_SUBSTITUTION选项运行服务器，以防止使用您不想使用的存储引擎创建表。

17.1.3 验证 InnoDB 是否是默认存储引擎

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

发出 SHOW ENGINES 语句以查看可用的 MySQL 存储引擎。在 SUPPORT 列中查找 DEFAULT。

mysql> SHOW ENGINES;

或者查询信息模式 ENGINES 表。

mysql> SELECT * FROM INFORMATION_SCHEMA.ENGINES;

17.1.4 使用 InnoDB 进行测试和基准测试

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

如果 InnoDB 不是默认存储引擎，您可以通过在命令行上定义 --default-storage-engine=InnoDB 或在 MySQL 服务器选项文件的 [mysqld] 部分定义 default-storage-engine=innodb 来重新启动服务器，以确定数据库服务器和应用程序是否正确使用 InnoDB。

由于更改默认存储引擎仅影响新创建的表，运行应用程序安装和设置步骤以确认一切安装正确，然后运行应用程序功能以确保数据加载、编辑和查询功能正常工作。如果表依赖于特定于另一个存储引擎的功能，您将收到错误信息。在这种情况下，将 ENGINE=*other_engine_name* 子句添加到 CREATE TABLE 语句中以避免错误。

如果您没有对存储引擎做出明确决定，并且想要预览使用 InnoDB 创建某些表时的工作情况，为每个表发出命令 ALTER TABLE table_name ENGINE=InnoDB;。或者，为了在不干扰原始表的情况下运行测试查询和其他语句，制作一份副本：

CREATE TABLE ... ENGINE=InnoDB AS SELECT * FROM *other_engine_table*;

为了在真实工作负载下评估完整应用程序的性能，请安装最新的 MySQL 服务器并运行基准测试。

测试完整的应用程序生命周期，从安装、重度使用到服务器重启。在数据库繁忙时杀死服务器进程以模拟断电，验证在重新启动服务器时数据是否成功恢复。

测试任何复制配置，特别是如果您在源服务器和副本上使用不同的 MySQL 版本和选项。

17.2 InnoDB 和 ACID 模型

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

ACID 模型是一组强调对商业数据和关键应用程序重要性的可靠性方面的数据库设计原则。MySQL 包括诸如 InnoDB 存储引擎等组件，严格遵循 ACID 模型，以确保数据不会被损坏，并且结果不会受到异常条件（如软件崩溃和硬件故障）的扭曲。当您依赖符合 ACID 标准的特性时，您无需重新发明一致性检查和崩溃恢复机制。在您拥有额外的软件保护、超可靠的硬件或者可以容忍少量数据丢失或不一致的应用程序的情况下，您可以调整 MySQL 设置，以交换一些 ACID 可靠性以获得更大的性能或吞吐量。

以下各节讨论了 MySQL 特性，特别是 InnoDB 存储引擎，如何与 ACID 模型的各个类别交互：

  • A: 原子性。

  • C: 一致性。

  • I:: 隔离性。

  • D: 持久性。

原子性

ACID 模型中的原子性主要涉及 InnoDB 事务。相关的 MySQL 特性包括：

  • autocommit 设置。

  • COMMIT 语句。

  • ROLLBACK 语句。

一致性

ACID 模型中的一致性主要涉及内部 InnoDB 处理以保护数据免受崩溃的影响。相关的 MySQL 特性包括：

  • InnoDB 双写缓冲区。参见 Section 17.6.4, “Doublewrite Buffer”.

  • InnoDB 崩溃恢复。参见 InnoDB Crash Recovery.

隔离性

ACID 模型中的隔离性主要涉及 InnoDB 事务，特别是适用于每个事务的隔离级别。相关的 MySQL 特性包括：

  • autocommit 设置。

  • 事务隔离级别和 SET TRANSACTION 语句。参见 Section 17.7.2.1, “Transaction Isolation Levels”.

  • InnoDB 锁定 的低级细节。细节可以在 INFORMATION_SCHEMA 表中查看（参见 Section 17.15.2, “InnoDB INFORMATION_SCHEMA Transaction and Locking Information”）以及 Performance Schema 的 data_locks 和 data_lock_waits 表。

持久性

ACID 模型中的持久性方面涉及 MySQL 软件功能与您特定硬件配置的交互。由于取决于 CPU、网络和存储设备的功能，这方面是最复杂的，无法提供具体的指导方针。（这些指导方针可能采取“购买新硬件”的形式。）相关的 MySQL 功能包括：

  • InnoDB 双写缓冲区。参见 Section 17.6.4, “Doublewrite Buffer”。

  • innodb_flush_log_at_trx_commit 变量。

  • sync_binlog 变量。

  • innodb_file_per_table 变量。

  • 存储设备中的写入缓冲区，如磁盘驱动器、固态硬盘或 RAID 阵列。

  • 存储设备中的带电池备份缓存。

  • 用于运行 MySQL 的操作系统，特别是对 fsync() 系统调用的支持。

  • 为所有运行 MySQL 服务器和存储 MySQL 数据的计算机服务器和存储设备提供电力保护的不间断电源（UPS）。

  • 你的备份策略，如备份频率和类型，以及备份保留期限。

  • 对于分布式或托管数据应用程序，MySQL 服务器硬件所在的数据中心的特定特性，以及数据中心之间的网络连接。

17.3 InnoDB 多版本

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

InnoDB是一个多版本存储引擎。它保留有关已更改行的旧版本的信息，以支持事务功能，如并发和回滚。此信息存储在称为回滚段的数据结构中的撤销表空间中。请参见第 17.6.3.4 节，“撤销表空间”。InnoDB使用回滚段中的信息执行事务回滚所需的撤销操作。它还使用信息为一致读取构建行的早期版本。请参见第 17.7.2.3 节，“一致非锁定读取”。

在内部，InnoDB为存储在数据库中的每一行添加了三个字段：

  • 一个 6 字节的DB_TRX_ID字段表示最后一次插入或更新行的事务标识符。此外，删除在内部被视为将一个特殊位设置为标记删除的更新。

  • 一个名为 roll pointer 的 7 字节的DB_ROLL_PTR字段。roll pointer 指向写入回滚段的撤销日志记录。如果行已更新，则撤销日志记录包含重建行内容所需的信息，使其恢复到更新之前的状态。

  • 一个 6 字节的DB_ROW_ID字段包含一个行 ID，随着插入新行而单调递增。如果InnoDB自动生成聚簇索引，则索引包含行 ID 值。否则，DB_ROW_ID列不会出现在任何索引中。

回滚段中的撤销日志分为插入和更新撤销日志。插入撤销日志仅在事务回滚中需要，并且可以在事务提交后立即丢弃。更新撤销日志也用于一致读取，但只有在没有事务存在时才能丢弃，对于这些事务，InnoDB已分配了一个快照，一致读取可能需要更新撤销日志中的信息来构建数据库行的早期版本。有关撤销日志的其他信息，请参见第 17.6.6 节，“撤销日志”。

建议您定期提交事务，包括仅发出一致读取的事务。否则，InnoDB无法从更新撤销日志中丢弃数据，回滚段可能会变得过大，填满其所在的撤销表空间。有关管理撤销表空间的信息，请参见第 17.6.3.4 节，“撤销表空间”。

回滚段中撤销日志记录的物理大小通常小于相应的插入或更新行。您可以使用此信息来计算回滚段所需的空间。

在InnoDB多版本方案中，当使用 SQL 语句删除行时，行不会立即从数据库中物理删除。只有当InnoDB丢弃为删除编写的更新撤销日志记录时，才会物理删除相应的行和其索引记录。这个删除操作称为清理，通常非常快，通常与执行删除操作的 SQL 语句花费的时间相同。

如果在表中以大致相同的速率批量插入和删除行，清理线程可能会开始滞后，表会因为所有“死”行而变得越来越大，使得所有操作都受限于磁盘，非常缓慢。在这种情况下，限制新行操作的速度，并通过调整innodb_max_purge_lag系统变量为清理线程分配更多资源。更多信息，请参见第 17.8.9 节，“清理配置”。

多版本和辅助索引

InnoDB 多版本并发控制（MVCC）对待辅助索引与聚簇索引的方式不同。聚簇索引中的记录会原地更新，并且它们的隐藏系统列指向撤销日志条目，可以从中重建记录的早期版本。与聚簇索引记录不同，辅助索引记录不包含隐藏系统列，也不会原地更新。

当更新辅助索引列时，旧的辅助索引记录会被标记为删除，新记录会被插入，而标记为删除的记录最终会被清除。当辅助索引记录被标记为删除或辅助索引页被新事务更新时，InnoDB会在聚簇索引中查找数据库记录。在聚簇索引中，记录的DB_TRX_ID会被检查，如果记录在读取事务启动后被修改，则从撤销日志中检索记录的正确版本。

如果辅助索引记录被标记为删除或辅助索引页被新事务更新，不会使用覆盖索引技术。InnoDB不会从索引结构返回值，而是在聚簇索引中查找记录。

然而，如果启用了 index condition pushdown (ICP)优化，并且WHERE条件的部分可以仅使用索引字段进行评估，MySQL 服务器仍然会将WHERE条件的这部分推送到存储引擎，在那里使用索引进行评估。如果找不到匹配的记录，则避免聚簇索引查找。如果找到匹配的记录，即使在标记为删除的记录中，InnoDB也会在聚簇索引中查找记录。

17.4 InnoDB 架构

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

以下图表显示了组成InnoDB存储引擎架构的内存和磁盘结构。有关每个结构的信息，请参见第 17.5 节，“InnoDB 内存结构”和第 17.6 节，“InnoDB 磁盘结构”。

图 17.1 InnoDB 架构

InnoDB 架构图显示内存和磁盘结构。内存结构包括缓冲池、自适应哈希索引、变更缓冲区和日志缓冲区。磁盘结构包括表空间、重做日志和双写缓冲区文件。

17.5 InnoDB 内存结构

原文：dev.mysql.com/doc/refman/8.0/en/innodb-in-memory-structures.html

17.5.1 缓冲池

17.5.2 变更缓冲区

17.5.3 自适应哈希索引

17.5.4 日志缓冲区

本节描述了InnoDB的内存结构及相关主题。

17.5.1 缓冲池

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

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

为了高效处理高容量读操作，缓冲池被划分为可以潜在地容纳多行的页面。为了高效管理缓存，缓冲池被实现为页面的链表；很少使用的数据通过最近最少使用（LRU）算法的变体从缓存中淘汰。

知道如何利用缓冲池将频繁访问的数据保留在内存中是 MySQL 调优的重要方面。

缓冲池 LRU 算法

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

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

  • 在尾部，是最近访问较少的旧页面的子列表

图 17.2 缓冲池列表

内容在周围的文本中描述。

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

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

  • 缓冲池的 3/8 专门用于旧子列表。

  • 列表的中点是新子列表的尾部与旧子列表的头部相遇的边界。

  • 当InnoDB将页面读入缓冲池时，它最初将其插入到中点（旧子列表的头部）。页面可以被读取，因为它是用户发起的操作（如 SQL 查询）所需，或者作为InnoDB自动执行的预读取操作的一部分。

  • 访问旧子列表中的页面会使其“年轻”，将其移动到新子列表的头部。如果页面是因为用户发起的操作而被读取，第一次访问会立即发生并使页面变为年轻。如果页面是由于预读取操作而被读取，第一次访问不会立即发生，并且在页面被驱逐之前可能根本不会发生。

  • 随着数据库的运行，缓冲池中未被访问的页面会“老化”，向列表的尾部移动。随着其他页面变为新页面，新旧子列表中的页面都会老化。随着页面在中点插入，旧子列表中的页面也会老化。最终，一个保持未使用状态的页面到达旧子列表的尾部并被驱逐。

默认情况下，由查询读取的页面立即移动到新子列表中，这意味着它们在缓冲池中停留的时间更长。例如，为了进行mysqldump操作或没有WHERE子句的SELECT语句执行表扫描，可能会将大量数据带入缓冲池并驱逐相同数量的旧数据，即使新数据永远不会再次使用。类似地，由预读后台线程加载并仅访问一次的页面被移动到新列表的开头。这些情况可能会将经常使用的页面推到旧子列表，使其成为可能被驱逐的对象。有关优化此行为的信息，请参见第 17.8.3.3 节，“使缓冲池具有扫描抵抗性”和第 17.8.3.4 节，“配置 InnoDB 缓冲池预取（预读）”。

InnoDB标准监视器输出包含关于缓冲池 LRU 算法操作的BUFFER POOL AND MEMORY部分中的几个字段。详情请参见使用 InnoDB 标准监视器监视缓冲池。

缓冲池配置

您可以配置缓冲池的各个方面以提高性能。

  • 理想情况下，您应将缓冲池的大小设置为尽可能大的值，同时留出足够的内存供服务器上的其他进程运行，以避免过多的分页。缓冲池越大，InnoDB就越像一个内存数据库，从磁盘读取数据一次，然后在后续读取中从内存访问数据。请参见第 17.8.3.1 节，“配置 InnoDB 缓冲池大小”。

  • 在具有足够内存的 64 位系统上，您可以将缓冲池分割为多个部分，以减少并发操作之间对内存结构的争用。详情请参见第 17.8.3.2 节，“配置多个缓冲池实例”。

  • 无论有大量不经常访问的数据进入缓冲池的操作突然激增，您都可以将经常访问的数据保留在内存中。详情请参见第 17.8.3.3 节，“使缓冲池具有扫描抵抗性”。

  • 您可以控制何时以及如何执行预读取请求，以异步地将页面预取到缓冲池中，以便提前满足对它们的需求。有关详细信息，请参见第 17.8.3.4 节，“配置 InnoDB 缓冲池预读取（预读取）”。

  • 您可以控制后台刷新发生的时间以及刷新速率是否根据工作负载动态调整。有关详细信息，请参见第 17.8.3.5 节，“配置缓冲池刷新”。

  • 您可以配置InnoDB如何保留当前缓冲池状态，以避免服务器重新启动后出现漫长的热身期。有关详细信息，请参见第 17.8.3.6 节，“保存和恢复缓冲池状态”。

使用 InnoDB 标准监视器监视缓冲池

InnoDB标准监视器输出可以通过SHOW ENGINE INNODB STATUS访问，提供有关缓冲池操作的指标。缓冲池指标位于InnoDB标准监视器输出的BUFFER POOL AND MEMORY部分：

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 2198863872
Dictionary memory allocated 776332
Buffer pool size   131072
Free buffers       124908
Database pages     5720
Old database pages 2071
Modified db pages  910
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 4, not young 0
0.10 youngs/s, 0.00 non-youngs/s
Pages read 197, created 5523, written 5060
0.00 reads/s, 190.89 creates/s, 244.94 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not
0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read
ahead 0.00/s
LRU len: 5720, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

以下表格描述了InnoDB标准监视器报告的缓冲池指标。

InnoDB标准监视器输出中提供的每秒平均值是基于自上次打印InnoDB标准监视器输出以来的经过时间。

表 17.2 InnoDB 缓冲池指标

名称 描述
总分配内存 缓冲池分配的总内存（以字节为单位）。
分配的字典内存 InnoDB数据字典分配的总内存（以字节为单位）。
缓冲池大小 分配给缓冲池的总页数。
空闲缓冲区 缓冲池空闲列表的总页数。
数据库页面 缓冲池 LRU 列表的总页数。
旧数据库页面 缓冲池旧 LRU 子列表的总页数。
修改的数据库页面 缓冲池中当前修改的页面数。
待读取 等待读入缓冲池的缓冲池页面数。
待写入 LRU 缓冲池中旧脏页的数量，将从 LRU 列表底部写入。
待写入刷新列表 在检查点期间刷新的缓冲池页面数。
待写入单页 缓冲池内独立页面写入的待处理数量。
年轻化的页面 缓冲池 LRU 列表中年轻化的页面总数（移动到“新”页面子列表的头部）。
未年轻化的页面 缓冲池 LRU 列表中未年轻化的页面总数（保持在“旧”子列表中未年轻化的页面）。
年轻化/s 缓冲池 LRU 列表中对旧页面的每秒平均访问次数，导致页面变年轻。有关更多信息，请参阅表后的注释。
非年轻化/s 缓冲池 LRU 列表中对旧页面的每秒平均访问次数，导致页面不变年轻。有关更多信息，请参阅表后的注释。
读取的页面 从缓冲池中读取的页面总数。
创建的页面 在缓冲池内创建的页面总数。
写入的页面 从缓冲池中写入的页面总数。
读取/s 每秒缓冲池页面读取的平均数量。
创建/s 每秒创建的缓冲池页面的平均数量。
写入/s 每秒缓冲池页面写入的平均数量。
缓冲池命中率 从缓冲池读取的页面与从磁盘存储读取的页面的缓冲池页面命中率。
年轻化率 页面访问导致页面变年轻的平均命中率。有关更多信息，请参阅表后的注释。
非（年轻化率） 页面访问未导致页面变年轻的平均命中率。有关更多信息，请参阅表后的注释。
预读取的页面 预读取操作的每秒平均次数。
未访问而被驱逐的页面 每秒从缓冲池中未被访问而被驱逐的页面的平均数量。
随机读取预读 随机读取预读操作的每秒平均次数。
LRU 长度 缓冲池 LRU 列表的总大小（以页面为单位）。
unzip_LRU 长度 缓冲池 unzip_LRU 列表的长度（以页面为单位）。
I/O sum 访问的缓冲池 LRU 列表页面总数。
I/O cur 当前间隔内访问的缓冲池 LRU 列表页面总数。
I/O unzip sum 解压缩的缓冲池 unzip_LRU 列表页面总数。
I/O unzip cur 当前间隔内解压缩缓冲池 unzip_LRU 列表页面的总数。
名称 描述

注释:

  • youngs/s 指标仅适用于旧页面。它基于页面访问次数。对于给定页面可能有多次访问，所有这些访问都会计数。如果在没有进行大型扫描时看到非常低的 youngs/s 值，请考虑减少延迟时间或增加用于旧子列表的缓冲池百分比。增加百分比会使旧子列表变大，使得该子列表中的页面移动到尾部所需的时间更长，从而增加这些页面再次被访问并变年轻的可能性。请参阅第 17.8.3.3 节，“使缓冲池具有扫描抵抗性”。

  • non-youngs/s 指标仅适用于旧页面。它基于页面访问次数。对于给定页面可能会有多次访问，所有这些都会被计算。如果在执行大表扫描时看不到更高的 non-youngs/s 值（以及更高的 youngs/s 值），请增加延迟值。参见 Section 17.8.3.3, “使缓冲池具有扫描抵抗力”。

  • young-making 率考虑了所有缓冲池页面访问，而不仅仅是旧子列表中页面的访问。young-making 率和 not 率通常不会加起来等于整体缓冲池命中率。旧子列表中的页面命中会导致页面移动到新子列表，但新子列表中的页面命中只有在它们距离头部一定距离时才会导致页面移动到头部。

  • not (young-making rate) 是页面访问未导致页面由于未达到由 innodb_old_blocks_time 定义的延迟，或由于新子列表中的页面命中未导致页面移动到头部的平均命中率。此率考虑了所有缓冲池页面访问，而不仅仅是旧子列表中页面的访问。

缓冲池 服务器状态变量 和 INNODB_BUFFER_POOL_STATS 表提供了许多与 InnoDB 标准监视器输出中找到的缓冲池指标相同的信息。有关更多信息，请参见 Example 17.10, “查询 INNODB_BUFFER_POOL_STATS 表”。

17.5.2 更改缓冲区

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

更改缓冲区是一种特殊的数据结构，用于缓存对次要索引页面的更改，当这些页面不在缓冲池中时。缓冲的更改可能来自INSERT、UPDATE或DELETE操作（DML），稍后在其他读取操作将这些页面加载到缓冲池时进行合并。

图 17.3 更改缓冲区

内容在周围的文本中描述。

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

定期地，在系统大部分空闲时运行的清理操作，或在慢速关闭期间，将更新的索引页面写入磁盘。清理操作可以更有效地为一系列索引值写入磁盘块，而不是立即将每个值写���磁盘。

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

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

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

如果索引包含降序索引列或主键包含降序索引列，则不支持对辅助索引进行改变缓冲。

有关改变缓冲的常见问题的答案，请参阅 Section A.16, “MySQL 8.0 FAQ: InnoDB Change Buffer”。

配置改变缓冲

当在表上执行INSERT、UPDATE和DELETE操作时，索引列的值（特别是辅助键的值）通常是无序的，需要大量 I/O 才能更新辅助索引。当相关页面不在缓冲池中时，改变缓冲缓存对辅助索引条目的更改，从而避免通过立即从磁盘读取页面来执行昂贵的 I/O 操作。当页面加载到缓冲池中时，缓冲的更改会合并，更新后的页面稍后会刷新到磁盘。当服务器几乎空闲时，InnoDB主线程会合并缓冲的更改，并在慢关闭期间执行。

由于可以减少磁盘读写次数，改变缓冲对于 I/O 受限的工作负载最有价值；例如，具有大量 DML 操作（如批量插入）的应用程序受益于改变缓冲。

然而，改变缓冲占用缓冲池的一部分，减少了用于缓存数据页的可用内存。如果工作集几乎适合缓冲池，或者如果您的表具有相对较少的辅助索引，禁用改变缓冲可能是有用的。如果工作数据集完全适合缓冲池，改变缓冲不会带来额外的开销，因为它仅适用于不在缓冲池中的页面。

innodb_change_buffering变量控制InnoDB执行改变缓冲的程度。您可以为插入、删除操作（当索引记录最初标记为删除时）和清除操作（当索引记录物理删除时）启用或禁用缓冲。更新操作是插入和删除的组合。默认的innodb_change_buffering值为all。

允许的innodb_change_buffering值包括：

  • all

    默认值：缓冲插入、删除标记操作和清除操作。

  • none

    不要缓冲任何操作。

  • inserts

    缓冲插入操作。

  • deletes

    缓冲删除标记操作。

  • changes

    缓冲插入和删除标记操作。

  • purges

    缓冲后台中发生的物理删除操作。

您可以在 MySQL 选项文件（my.cnf或my.ini）中设置innodb_change_buffering变量，或使用SET GLOBAL语句动态更改它，这需要足够的权限来设置全局系统变量。请参见 第 7.1.9.1 节，“系统变量权限”。更改设置会影响新操作的缓冲；现有缓冲条目的合并不受影响。

配置变更缓冲区最大大小。

innodb_change_buffer_max_size变量允许将变更缓冲区的最大大小配置为缓冲池总大小的百分比。默认情况下，innodb_change_buffer_max_size设置为 25。最大设置为 50。

考虑在具有大量插入、更新和删除活动的 MySQL 服务器上增加innodb_change_buffer_max_size，其中变更缓冲区合并无法跟上新的变更缓冲区条目，导致变更缓冲区达到其最大大小限制。

考虑在用于报告的静态数据的 MySQL 服务器上减少innodb_change_buffer_max_size，或者如果变更缓冲区消耗了与缓冲池共享的内存空间过多，导致页面比预期更早地从缓冲池中过期。

使用代表性工作负载测试不同设置，以确定最佳配置。innodb_change_buffer_max_size 变量是动态的，允许在不重新启动服务器的情况下修改设置。

监控变更缓冲区。

可用于变更缓冲区监视的选项如下：

  • InnoDB标准监视器输出包括变更缓冲区状态信息。要查看监视器数据，请发出SHOW ENGINE INNODB STATUS语句。

    mysql> SHOW ENGINE INNODB STATUS\G

    变更缓冲区状态信息位于INSERT BUFFER AND ADAPTIVE HASH INDEX标题下，并且类似于以下内容：

    -------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 4425293, used cells 32, node heap has 1 buffer(s)
13577.57 hash searches/s, 202.47 non-hash searches/s

    有关更多信息，请参见 第 17.17.3 节，“InnoDB 标准监视器和锁监视器输出”。

  • 信息模式INNODB_METRICS表提供了InnoDB标准监视器输出中找到的大部分数据点以及其他数据点。要查看变更缓冲区指标和每个指标的描述，请发出以下查询：

    mysql> SELECT NAME, COMMENT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME LIKE '%ibuf%'\G

    参见第 17.15.6 节，“InnoDB INFORMATION_SCHEMA 指标表”。

  • 信息模式INNODB_BUFFER_PAGE表提供关于缓冲池中每个页面的元数据，包括变更缓冲区索引和变更缓冲区位图页面。 变更缓冲区页面由PAGE_TYPE标识。 IBUF_INDEX是变更缓冲区索引页面的页面类型，IBUF_BITMAP是变更缓冲区位图页面的页面类型。

    警告

    查询INNODB_BUFFER_PAGE表可能会引入显着的性能开销。 为避免影响性能，请在测试实例上重现要调查的问题，并在测试实例上运行您的查询。

    例如，您可以查询INNODB_BUFFER_PAGE表，以确定IBUF_INDEX和IBUF_BITMAP页面的大致数量占总缓冲池页面的百分比。

    mysql> SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE
       WHERE PAGE_TYPE LIKE 'IBUF%') AS change_buffer_pages,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE) AS total_pages,
       (SELECT ((change_buffer_pages/total_pages)*100))
       AS change_buffer_page_percentage;
+---------------------+-------------+-------------------------------+
| change_buffer_pages | total_pages | change_buffer_page_percentage |
+---------------------+-------------+-------------------------------+
|                  25 |        8192 |                        0.3052 |
+---------------------+-------------+-------------------------------+

    有关INNODB_BUFFER_PAGE表提供的其他数据信息，请参阅第 28.4.2 节，“INFORMATION_SCHEMA INNODB_BUFFER_PAGE 表”。 有关相关用法信息，请参阅第 17.15.5 节，“InnoDB INFORMATION_SCHEMA 缓冲池表”。

  • 性能模式为高级性能监控提供了变更缓冲区互斥等待仪器。 要查看变更缓冲区仪器，执行以下查询：

    mysql> SELECT * FROM performance_schema.setup_instruments
       WHERE NAME LIKE '%wait/synch/mutex/innodb/ibuf%';
+-------------------------------------------------------+---------+-------+
| NAME                                                  | ENABLED | TIMED |
+-------------------------------------------------------+---------+-------+
| wait/synch/mutex/innodb/ibuf_bitmap_mutex             | YES     | YES   |
| wait/synch/mutex/innodb/ibuf_mutex                    | YES     | YES   |
| wait/synch/mutex/innodb/ibuf_pessimistic_insert_mutex | YES     | YES   |
+-------------------------------------------------------+---------+-------+

    关于监控InnoDB互斥等待的信息，请参阅第 17.16.2 节，“使用性能模式监控 InnoDB 互斥等待”。

17.5.3 自适应哈希索引

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

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

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

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

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

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

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

有关哈希索引性能特征的信息，请参见第 10.3.9 节，“B-Tree 和哈希索引的比较”。

17.5.4 日志缓冲区

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

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

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

有关相关信息，请参阅内存配置，以及第 10.5.4 节，“优化 InnoDB 重做日志记录”。

17.6 InnoDB 磁盘结构

原文：dev.mysql.com/doc/refman/8.0/en/innodb-on-disk-structures.html

17.6.1 表

17.6.2 索引

17.6.3 表空间

17.6.4 双写缓冲区

17.6.5 重做日志

17.6.6 撤销日志

本节描述了InnoDB的磁盘结构及相关主题。

17.6.1 表

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

17.6.1.1 创建 InnoDB 表

17.6.1.2 外部创建表

17.6.1.3 导入 InnoDB 表

17.6.1.4 移动或复制 InnoDB 表

17.6.1.5 从 MyISAM 转换表到 InnoDB

17.6.1.6 InnoDB 中的 AUTO_INCREMENT 处理

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

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

17.6.1.1 创建 InnoDB 表

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

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

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

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

InnoDB表默认在每个表的表空间中创建。要在InnoDB系统表空间中创建InnoDB表，请在创建表之前禁用innodb_file_per_table变量。要在一般表空间中创建InnoDB表，请使用CREATE TABLE ... TABLESPACE语法。更多信息，请参见第 17.6.3 节，“表空间”。

行格式

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

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

主键

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

  • 被最重要查询引用的列。

  • 从不留空的列。

  • 从不具有重复值的列。

  • 一旦插入后很少或几乎不会更改值的列。

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

# The value of ID can act like a pointer between related items in different tables.
CREATE TABLE t5 (id INT AUTO_INCREMENT, b CHAR (20), PRIMARY KEY (id));

# The primary key can consist of more than one column. Any autoinc column must come first.
CREATE TABLE t6 (id INT AUTO_INCREMENT, a INT, b CHAR (20), PRIMARY KEY (id,a));

有关自增列的更多信息，请参阅第 17.6.1.6 节，“AUTO_INCREMENT Handling in InnoDB”。

虽然表在没有定义主键的情况下可以正常工作，但主键涉及到性能的许多方面，对于任何大型或经常使用的表来说，主键是一个关键的设计方面。建议您在CREATE TABLE语句中始终指定主键。如果您创建表、加载数据，然后运行ALTER TABLE以后添加主键，那么该操作比在创建表时定义主键要慢得多。有关主键的更多信息，请参阅第 17.6.2.1 节，“Clustered and Secondary Indexes”。

查看 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输出的信息，请参阅第 15.7.7.38 节，“SHOW TABLE STATUS Statement”。

您还可以通过查询InnoDB信息模式系统表来访问InnoDB表属性：

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

有关更多信息，请参阅第 17.15.3 节，“InnoDB INFORMATION_SCHEMA Schema Object Tables”。

原文：dev.mysql.com/doc/refman/8.0/en/innodb-create-table-external.html

17.6.1.2 创建外部表

创建InnoDB表的外部原因有很多；也就是说，在数据目录之外创建表。这些原因可能包括空间管理、I/O 优化，或者将表放在具有特定性能或容量特征的存储设备上，例如。

InnoDB支持以下方法来外部创建表：

  • 使用 DATA DIRECTORY 子句

  • 使用 CREATE TABLE ... TABLESPACE 语法

  • 在外部通用表空间中创建表

使用 DATA DIRECTORY 子句

您可以通过在 CREATE TABLE 语句中指定 DATA DIRECTORY 子句来在外部目录中创建一个 InnoDB 表。

CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '*/external/directory*';

DATA DIRECTORY 子句支持在每表表空间中创建的表。当 innodb_file_per_table 变量启用时（默认情况下启用），表会隐式地在每表表空间中创建。

mysql> SELECT @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                       1 |
+-------------------------+

有关每表表空间的更多信息，请参见第 17.6.3.2 节，“每表表空间”。

当您在 CREATE TABLE 语句中指定 DATA DIRECTORY 子句时，表的数据文件（*table_name*.ibd）将在指定目录下的模式目录中创建。

从 MySQL 8.0.21 开始，使用 DATA DIRECTORY 子句在数据目录之外创建的表和表分区受到 InnoDB 知道的目录的限制。此要求允许数据库管理员控制表空间数据文件的创建位置，并确保在恢复期间可以找到数据文件（请参见崩溃恢复期间的表空间发现）。已知目录是由 datadir、innodb_data_home_dir 和 innodb_directories 变量定义的那些目录。您可以使用以下语句来检查这些设置：

mysql> SELECT @@datadir,@@innodb_data_home_dir,@@innodb_directories;

如果要使用的目录未知，请在创建表之前将其添加到innodb_directories设置中。innodb_directories变量是只读的。配置它需要重新启动服务器。有关设置系统变量的一般信息，请参见第 7.1.9 节，“使用系统变量”。

以下示例演示了使用DATA DIRECTORY子句在外部目录中创建表。假定innodb_file_per_table变量已启用，并且该目录为InnoDB所知。

mysql> USE test;
Database changed

mysql> CREATE TABLE t1 (c1 INT PRIMARY KEY) DATA DIRECTORY = '*/external/directory*';

# MySQL creates the table's data file in a schema directory
# under the external directory

$> cd /external/directory/test
$> ls
t1.ibd
使用说明：

  • MySQL 最初会保持表空间数据文件打开，阻止您卸载设备，但如果服务器繁忙，可能最终会关闭文件。请注意不要在 MySQL 运行时意外卸载外部设备，或在设备断开连接时启动 MySQL。当相关数据文件丢失时尝试访问表会导致需要服务器重启的严重错误。

    如果在预期路径中找不到数据文件，服务器重启可能会失败。在这种情况下，您可以从备份中恢复表空间数据文件，或删除表以从数据字典中删除有关其信息。

  • 在将表放在 NFS 挂载的卷上之前，请查看使用 NFS 与 MySQL 中概述的潜在问题。

  • 如果使用 LVM 快照、文件复制或其他基于文件的机制来备份表的数据文件，请始终首先使用FLUSH TABLES ... FOR EXPORT语句，以确保所有在内存中缓冲的更改被刷新到磁盘上，然后再进行备份。

  • 使用DATA DIRECTORY子句在外部目录中创建表是使用符号链接的替代方法，InnoDB不支持。

  • 在源和副本位于同一主机的复制环境中不支持DATA DIRECTORY子句。DATA DIRECTORY子句需要完整的目录路径。在这种情况下复制路径会导致源和副本在相同位置创建表。

  • 截至 MySQL 8.0.21，无法再在撤销表空间目录（innodb_undo_directory）中创建文件-每表表空间中的表，除非该目录为InnoDB所知。已知目录是由datadir、innodb_data_home_dir和innodb_directories变量定义的目录。

使用 CREATE TABLE ... TABLESPACE 语法

CREATE TABLE ... TABLESPACE语法可以与DATA DIRECTORY子句结合使用，以在外部目录中创建表。为此，请将innodb_file_per_table指定为表空间名称。

mysql> CREATE TABLE t2 (c1 INT PRIMARY KEY) TABLESPACE = innodb_file_per_table
       DATA DIRECTORY = '/external/directory';

此方法仅支持在每个表的文件表空间中创建的表，但不需要启用innodb_file_per_table变量。在其他方面，此方法与上述描述的CREATE TABLE ... DATA DIRECTORY方法等效。相同的使用说明适用。

在外部通用表空间中创建表

您可以在外部目录中的通用表空间中创建表。

  • 有关在外部目录中创建通用表空间的信息，请参见创建通用表空间。

  • 有关在通用表空间中创建表的信息，请参见将表添加到通用表空间。

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

17.6.1.3 导入 InnoDB 表

本节描述如何使用可传输表空间功能导入表，该功能允许导入位于文件-每表表空间中的表、分区表或单个表分区。有许多原因您可能想要导入表：

  • 在非生产 MySQL 服务器实例上运行报告，以避免对生产服务器造成额外负载。

  • 将数据复制到新的副本服务器。

  • 从备份的表空间文件中恢复表。

  • 作为比导入转储文件更快的移动数据的方法，后者需要重新插入数据和重建索引。

  • 将数据移动到一个存储介质更适合您存储需求的服务器上。例如，您可以将繁忙的表移动到 SSD 设备，或将大表移动到高容量的 HDD 设备。

可传输表空间功能在本节中的以下主题中进行了描述：

  • 先决条件

  • 导入表

  • 导入分区表

  • 导入表分区

  • 限制

  • 使用说明

  • 内部结构

先决条件

  • innodb_file_per_table变量必须启用，默认情况下已启用。

  • 表空间的页面大小必须与目标 MySQL 服务器实例的页面大小匹配。InnoDB页面大小由innodb_page_size变量定义，在初始化 MySQL 服务器实例时配置。

  • 如果表具有外键关系，在执行DISCARD TABLESPACE之前必须禁用foreign_key_checks。此外，应在同一逻辑时间点导出所有相关的外键表，因为ALTER TABLE ... IMPORT TABLESPACE不会对导入的数据强制执行外键约束。为此，请停止更新相关表，提交所有事务，在表上获取共享锁，并执行导出操作。

  • 当从另一个 MySQL 服务器实例导入表时，两个 MySQL 服务器实例必须具有通用可用性（GA）状态，并且必须是相同版本。否则，表必须在导入的 MySQL 服务器实例中创建。

  • 如果表是通过在CREATE TABLE语句中指定DATA DIRECTORY子句在外部目录中创建的，则在目标实例上替换的表必须使用相同的DATA DIRECTORY子句进行定义。如果子句不匹配，则会报告模式不匹配错误。要确定源表是否使用DATA DIRECTORY子句定义，请使用SHOW CREATE TABLE查看表定义。有关使用DATA DIRECTORY子句的信息，请参见第 17.6.1.2 节，“外部创建表”。

  • 如果表定义中未明确定义ROW_FORMAT选项或使用了ROW_FORMAT=DEFAULT，则源实例和目标实例上的innodb_default_row_format设置必须相同。否则，在尝试导入操作时会报告模式不匹配错误。使用SHOW CREATE TABLE检查表定义。使用SHOW VARIABLES检查innodb_default_row_format设置。有关相关信息，请参见定义表的行格式。

导入表

此示例演示了如何导入一个位于文件表空间中的常规非分区表。

  1. 在目标实例上，创建一个与您打算导入的表具有相同定义的表。（您可以使用SHOW CREATE TABLE语法获取表定义。）如果表定义不匹配，在尝试导入操作时会报告模式不匹配错误。

    mysql> USE test;
mysql> CREATE TABLE t1 (c1 INT) ENGINE=INNODB;

  2. 在目标实例上，丢弃刚刚创建的表的表空间。（在导入之前，您必须丢弃接收表的表空间。）

    mysql> ALTER TABLE t1 DISCARD TABLESPACE;

  3. 在源实例上，运行FLUSH TABLES ... FOR EXPORT以使您打算导入的表静止。当表被静止时，只允许对表进行只读事务。

    mysql> USE test;
mysql> FLUSH TABLES t1 FOR EXPORT;

    FLUSH TABLES ... FOR EXPORT确保对命名表的更改刷新到磁盘，以便在服务器运行时可以进行二进制表复制。运行FLUSH TABLES ... FOR EXPORT时，InnoDB在表的模式目录中生成一个.cfg元数据文件。.cfg文件包含在导入操作期间用于模式验证的元数据。

    注意

    执行FLUSH TABLES ... FOR EXPORT的连接在操作运行时必须保持打开状态；否则，随着连接关闭，.cfg文件将被删除，因为锁在连接关闭时被释放。

  4. 从源实例复制.ibd文件和.cfg元数据文件到目标实例。例如：

    $> scp */path/to/datadir*/test/t1.{ibd,cfg} destination-server:*/path/to/datadir*/test

    在释放共享锁之前，必须复制.ibd文件和.cfg文件，如下一步骤所述。

    注意

    如果您从加密表空间导入表，InnoDB会生成一个.cfp文件，除了一个.cfg元数据文件。.cfp文件必须与.cfg文件一起复制到目标实例。.cfp文件包含一个传输密钥和一个加密表空间密钥。在导入时，InnoDB使用传输密钥解密表空间密钥。有关相关信息，请参见第 17.13 节，“InnoDB 数据静止加密”。

  5. 在源实例上，使用UNLOCK TABLES释放FLUSH TABLES ... FOR EXPORT语句获取的锁：

    mysql> USE test;
mysql> UNLOCK TABLES;

    UNLOCK TABLES操作也会删除.cfg文件。

  6. 在目标实例上，导入表空间：

    mysql> USE test;
mysql> ALTER TABLE t1 IMPORT TABLESPACE;
导入分区表

本示例演示了如何导入一个分区表，其中每个表分区都驻留在一个文件表表空间中。

  1. 在目标实例上，创建一个与要导入的分区表相同定义的分区表。（您可以使用SHOW CREATE TABLE语法获取表定义。）如果表定义不匹配，则在尝试导入操作时会报告模式不匹配错误。

    mysql> USE test;
mysql> CREATE TABLE t1 (i int) ENGINE = InnoDB PARTITION BY KEY (i) PARTITIONS 3;

    在/*datadir*/test目录中，每个分区都有一个.ibd文件的表空间。

    mysql> \! ls */path/to/datadir*/test/
t1#p#p0.ibd  t1#p#p1.ibd  t1#p#p2.ibd

  2. 在目标实例上，丢弃分区表的表空间。（在导入操作之前，必须丢弃接收表的表空间。）

    mysql> ALTER TABLE t1 DISCARD TABLESPACE;

    分区表的三个表空间.ibd文件将从/*datadir*/test目录中丢弃。

  3. 在源实例上，运行FLUSH TABLES ... FOR EXPORT来使您打算导入的分区表静止。当表被静止时，只允许对表进行只读事务。

    mysql> USE test;
mysql> FLUSH TABLES t1 FOR EXPORT;

    FLUSH TABLES ... FOR EXPORT确保将对命名表的更改刷新到磁盘，以便在服务器运行时可以进行二进制表复制。当运行FLUSH TABLES ... FOR EXPORT时，InnoDB为表的每个表空间文件在表的模式目录中生成.cfg元数据文件。

    mysql> \! ls */path/to/datadir*/test/
t1#p#p0.ibd  t1#p#p1.ibd  t1#p#p2.ibd
t1#p#p0.cfg  t1#p#p1.cfg  t1#p#p2.cfg

    .cfg文件包含在导入表空间时用于模式验证的元数据。FLUSH TABLES ... FOR EXPORT只能在表上运行，而不能在单个表分区上运行。

  4. 将.ibd和.cfg文件从源实例模式目录复制到目标实例模式目录。例如：

    $>scp */path/to/datadir*/test/t1*.{ibd,cfg} destination-server:*/path/to/datadir*/test

    在释放共享锁之前，必须复制.ibd和.cfg文件，如下一步所述。

    注意

    如果从加密表空间导入表，则InnoDB会生成一个.cfp文件，除了一个.cfg元数据文件。.cfp文件必须与.cfg文件一起复制到目标实例。.cfp文件包含传输密钥和加密表空间密钥。在导入时，InnoDB使用传输密钥解密表空间密钥。有关相关信息，请参见第 17.13 节，“InnoDB 数据静止加密”。

  5. 在源实例上，使用UNLOCK TABLES释放由FLUSH TABLES ... FOR EXPORT获取的锁：

    mysql> USE test;
mysql> UNLOCK TABLES;

  6. 在目标实例上，导入分区表的表空间：

    mysql> USE test;
mysql> ALTER TABLE t1 IMPORT TABLESPACE;
导入表分区

本示例演示了如何导入单个表分区，其中每个分区都驻留在一个文件表空间文件中。

在下面的示例中，导入了一个四分区表的两个分区（p2和p3）。

  1. 在目标实例上，创建一个与要导入分区的分区表定义相同的分区表（可以使用SHOW CREATE TABLE语法获取表定义）。如果表定义不匹配，则在尝试导入操作时会报告模式不匹配错误。

    mysql> USE test;
mysql> CREATE TABLE t1 (i int) ENGINE = InnoDB PARTITION BY KEY (i) PARTITIONS 4;

    在/*datadir*/test目录中，每个分区都有一个.ibd文件。

    mysql> \! ls */path/to/datadir*/test/
t1#p#p0.ibd  t1#p#p1.ibd  t1#p#p2.ibd t1#p#p3.ibd

  2. 在目标实例上，丢弃要从源实例导入的分区。（在导入分区之前，必须从接收分区表中丢弃相应的分区。）

    mysql> ALTER TABLE t1 DISCARD PARTITION p2, p3 TABLESPACE;

    两个丢弃分区（p2和p3）的表空间.ibd文件从目标实例的/*datadir*/test目录中移除，留下以下文件：

    mysql> \! ls */path/to/datadir*/test/
t1#p#p0.ibd  t1#p#p1.ibd

    注意

    当在子分区表上运行ALTER TABLE ... DISCARD PARTITION ... TABLESPACE时，允许使用分区和子分区表名称。指定分区名称时，该分区的子分区也包括在操作中。

  3. 在源实例上，运行FLUSH TABLES ... FOR EXPORT使分区表静止。当表被静止时，只允许对表进行只读事务。

    mysql> USE test;
mysql> FLUSH TABLES t1 FOR EXPORT;

    FLUSH TABLES ... FOR EXPORT确保将对命名表的更改刷新到磁盘，以便在实例运行时可以进行二进制表复制。运行FLUSH TABLES ... FOR EXPORT时，InnoDB为表模式目录中的每个表空间文件生成一个.cfg元数据文件。

    mysql> \! ls */path/to/datadir*/test/
t1#p#p0.ibd  t1#p#p1.ibd  t1#p#p2.ibd t1#p#p3.ibd
t1#p#p0.cfg  t1#p#p1.cfg  t1#p#p2.cfg t1#p#p3.cfg

    .cfg文件包含在导入操作期间用于模式验证的元数据。FLUSH TABLES ... FOR EXPORT只能在表上运行，而不能在单独的表分区上运行。

  4. 从源实例模式目录复制分区p2和分区p3的.ibd和.cfg文件到目标实例模式目录。

    $> scp t1#p#p2.ibd t1#p#p2.cfg t1#p#p3.ibd t1#p#p3.cfg destination-server:*/path/to/datadir*/test

    在释放共享锁之前，必须复制.ibd和.cfg文件，如下一步骤所述。

    注意

    如果您从加密表空间导入分区，则InnoDB会生成一个.cfg元数据文件以及一个.cfp文件。.cfp文件必须与.cfg文件一起复制到目标实例。.cfp文件包含传输密钥和加密表空间密钥。在导入时，InnoDB使用传输密钥解密表空间密钥。有关更多信息，请参见第 17.13 节，“InnoDB 数据静态加密”。

  5. 在源实例上，使用UNLOCK TABLES释放由FLUSH TABLES ... FOR EXPORT获取的锁：

    mysql> USE test;
mysql> UNLOCK TABLES;

  6. 在目标实例上，导入表分区p2和p3：

    mysql> USE test;
mysql> ALTER TABLE t1 IMPORT PARTITION p2, p3 TABLESPACE;

    注意

    当在分区化表上运行ALTER TABLE ... IMPORT PARTITION ... TABLESPACE时，允许使用分区和子分区表名称。指定分区名称时，该分区的子分区将包含在操作中。

限制

  • 可传输表空间功能仅支持驻留在文件表空间中的表。不支持驻留在系统表空间或通用表空间中的表。共享表空间中的表无法静止。

  • 不支持在具有FULLTEXT索引的表上运行FLUSH TABLES ... FOR EXPORT，因为无法刷新全文搜索辅助表。在导入具有FULLTEXT索引的表后，运行OPTIMIZE TABLE来重建FULLTEXT索引。或者，在导出操作之前删除FULLTEXT索引，并在目标实例上导入表后重新创建索引。

  • 由于.cfg元数据文件的限制，当导入分区表时，不会报告分区类型或分区定义差异，但会报告列差异。

  • 在 MySQL 8.0.19 之前，在表空间导入操作期间，索引键部分排序顺序信息不会存储到使用的.cfg元数据文件中。因此，假定索引键部分排序顺序为升序，这是默认值。因此，如果一个表在导入操作中定义为具有 DESC 索引键部分排序顺序，而另一个表没有，则记录可能以意外的顺序排序。解决方法是删除并重新创建受影响的索引。有关索引键部分排序顺序的信息，请参见第 15.1.15 节，“CREATE INDEX Statement”。

    MySQL 8.0.19 中更新了.cfg文件格式，包括索引键部分排序顺序信息。上述问题不会影响 MySQL 8.0.19 服务器实例或更高版本之间的导入操作。

使用说明

  • 除了包含立即添加或删除列的表之外，ALTER TABLE ... IMPORT TABLESPACE在导入表时不需要.cfg元数据文件。然而，在没有使用.cfg文件导入时不会执行元数据检查，并会发出类似以下警告：

    Message: InnoDB: IO Read error: (2, No such file or directory) Error opening '.\
test\t.cfg', will attempt to import without schema verification
1 row in set (0.00 sec)

    只有在不期望出现模式不匹配且表不包含任何立即添加或删除列时，才应考虑在没有.cfg元数据文件的情况下导入表。在无法访问元数据的崩溃恢复场景中，无需.cfg文件即可导入可能是有用的。

    尝试使用ALGORITHM=INSTANT导入包含已添加或删除列的表而没有使用.cfg文件可能导致未定义的行为。

  • 在 Windows 上，InnoDB在内部以小写存储数据库、表空间和表名。为避免在 Linux 和 Unix 等区分大小写的操作系统上出现导入问题，请使用小写名称创建所有数据库、表空间和表。确保名称以小写创建的一种便捷方法是在初始化服务器之前将lower_case_table_names设置为 1。（禁止使用与服务器初始化时使用的设置不同的lower_case_table_names设置启动服务器。）

    [mysqld]
lower_case_table_names=1

  • 在子分区表上运行ALTER TABLE ... DISCARD PARTITION ... TABLESPACE和ALTER TABLE ... IMPORT PARTITION ... TABLESPACE时，允许使用分区和子分区表名称。指定分区名称时，该分区的子分区将包含在操作中。

内部结构

以下信息描述了在表导入过程中写入错误日志的内部和消息。

当在目标实例上运行ALTER TABLE ... DISCARD TABLESPACE时：

  • 表以 X 模式被锁定。

  • 表空间从表中分离。

当在源实例上运行FLUSH TABLES ... FOR EXPORT时：

  • 用于导出的表被以共享模式锁定。

  • 清理协调器线程被停止。

  • 脏页被同步到磁盘。

  • 表元数据被写入二进制.cfg文件。

该操作的预期错误日志消息：

[Note] InnoDB: Sync to disk of '"test"."t1"' started.
[Note] InnoDB: Stopping purge
[Note] InnoDB: Writing table metadata to './test/t1.cfg'
[Note] InnoDB: Table '"test"."t1"' flushed to disk

当在源实例上运行UNLOCK TABLES时：

  • 二进制.cfg文件被删除。

  • 被导入的表或表的共享锁被释放，并且清理协调器线程被重新启动。

该操作的预期错误日志消息：

[Note] InnoDB: Deleting the meta-data file './test/t1.cfg'
[Note] InnoDB: Resuming purge

当在目标实例上运行ALTER TABLE ... IMPORT TABLESPACE时，导入算法对每个被导入的表空间执行以下操作：

  • 检查每个表空间页是否损坏。

  • 更新每个页面上的空间 ID 和日志序列号（LSN）。

  • 标志被验证并且头页的 LSN 被更新。

  • B 树页被更新。

  • 页面状态被设置为脏，以便写入磁盘。

该操作的预期错误日志消息：

[Note] InnoDB: Importing tablespace for table 'test/t1' that was exported
from host '*host_name*'
[Note] InnoDB: Phase I - Update all pages
[Note] InnoDB: Sync to disk
[Note] InnoDB: Sync to disk - done!
[Note] InnoDB: Phase III - Flush changes to disk
[Note] InnoDB: Phase IV - Flush complete

注意

你可能还会收到一个警告，表空间被丢弃了（如果你丢弃了目标表的表空间），以及一个消息说明由于缺少.ibd文件而无法计算统计信息：

[Warning] InnoDB: Table "test"."t1" tablespace is set as discarded.
7f34d9a37700 InnoDB: cannot calculate statistics for table
"test"."t1" because the .ibd file is missing. For help, please refer to
http://dev.mysql.com/doc/refman/8.0/en/innodb-troubleshooting.html

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

17.6.1.4 移动或复制 InnoDB 表

本节描述了将一些或所有 InnoDB 表移动或复制到不同服务器或实例的技术。例如，您可能会将整个 MySQL 实例移动到更大、更快的服务器；您可能会克隆整个 MySQL 实例到新的复制服务器；您可能会将单个表复制到另一个实例以开发和测试应用程序，或者将其复制到数据仓库服务器以生成报告。

在 Windows 上，InnoDB 总是在内部以小写存储数据库和表名。要将数据库以二进制格式从 Unix 移动到 Windows 或从 Windows 移动到 Unix，请使用小写名称创建所有数据库和表。实现这一目标的一种便捷方法是在创建任何数据库或表之前将以下行添加到您的 my.cnf 或 my.ini 文件的 [mysqld] 部分：

[mysqld]
lower_case_table_names=1

注意

禁止使用与服务器初始化时使用的设置不同的 lower_case_table_names 设置启动服务器。

移动或复制 InnoDB 表的技术包括：

  • 导入表

  • MySQL Enterprise Backup

  • 复制数据文件（冷备份方法）

  • 从逻辑备份恢复

导入表

位于文件表空间中的表可以使用 可传输表空间 功能从另一个 MySQL 服务器实例或备份中导入。请参阅 第 17.6.1.3 节，“导入 InnoDB 表”。

MySQL Enterprise Backup

MySQL Enterprise Backup 产品可让您在最小干扰运营的情况下备份正在运行的 MySQL 数据库，并生成数据库的一致快照。当 MySQL Enterprise Backup 复制表时，读写可以继续进行。此外，MySQL Enterprise Backup 可以创建压缩备份文件，并备份表的子集。结合 MySQL 二进制日志，您可以执行按时间点恢复。MySQL Enterprise Backup 包含在 MySQL Enterprise 订阅的一部分中。

有关 MySQL Enterprise Backup 的更多详细信息，请参阅 第 32.1 节，“MySQL Enterprise Backup 概述”。

复制数据文件（冷备份方法）

您可以通过复制 第 17.18.1 节，“InnoDB 备份” 中列出的所有相关文件来移动 InnoDB 数据库。

在所有具有相同浮点数格式的平台上，InnoDB数据和日志文件是二进制兼容的。如果浮点格式不同，但你的表中没有使用FLOAT - FLOAT, DOUBLE")或DOUBLE - FLOAT, DOUBLE")数据类型，则程序是相同的：只需复制相关文件。

当移动或复制基于文件的表格.ibd文件时，源和目标系统的数据库目录名称必须相同。存储在InnoDB共享表空间中的表定义包括数据库名称。表空间文件中存储的事务 ID 和日志序列号也在不同的数据库之间有所不同。

要将一个.ibd文件和相关表从一个数据库移动到另一个数据库，使用RENAME TABLE语句：

RENAME TABLE *db1.tbl_name* TO *db2.tbl_name*;

如果你有一个“干净”的.ibd文件备份，你可以按照以下步骤将其恢复到它原来的 MySQL 安装中：

  1. 自从你复制.ibd文件以来，表不能被删除或截断，因为这样会改变存储在表空间内部的表 ID。

  2. 发出这个ALTER TABLE语句来删除当前的.ibd文件：

    ALTER TABLE *tbl_name* DISCARD TABLESPACE;

  3. 将备份的.ibd文件复制到正确的数据库目录中。

  4. 发出这个ALTER TABLE语句，告诉InnoDB使用新的.ibd文件来替换表：

    ALTER TABLE *tbl_name* IMPORT TABLESPACE;

    注意

    ALTER TABLE ... IMPORT TABLESPACE功能不会对导入的数据强制执行外键约束。

在这种情况下，“干净”的.ibd文件备份是指满足以下要求的备份：

  • .ibd文件中没有事务中未提交的修改。

  • .ibd文件中没有未合并的插入缓冲区条目。

  • 清除已从.ibd文件中删除标记的索引记录。

  • mysqld已经将.ibd文件的所有修改页面从缓冲池刷新到文件中。

你可以使用以下方法制作一个干净的备份.ibd文件：

  1. 停止所有来自mysqld服务器��活动，并提交所有事务。

  2. 等到SHOW ENGINE INNODB STATUS显示数据库中没有活动事务，并且InnoDB的主线程状态为等待服务器活动。然后你可以复制.ibd文件。

制作一个.ibd文件的干净副本的另一种方法是使用 MySQL 企业备份产品：

  1. 使用 MySQL 企业备份来备份InnoDB安装。

  2. 在备份上启动第二个mysqld服务器，并让它清理备份中的.ibd文件。

从逻辑备份中恢复

你可以使用类似 mysqldump 这样的实用程序执行逻辑备份，它会生成一组可以执行的 SQL 语句，以便在转移到另一个 SQL 服务器时重新生成原始数据库对象定义和表数据。使用这种方法，无论格式是否不同或者你的表是否包含浮点数据都无关紧要。

为了提高此方法的性能，在导入数据时禁用 autocommit。只有在导入整个表或表的一部分后才执行提交。

原文：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 数据静态加密”）。同样，属于页压缩表空间的双写文件页会被压缩。因此，双写文件可以包含不同类型的页，包括未加密和未压缩的页，加密的页，压缩的页，以及既加密又压缩的页。

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