MySQL8-中文参考-六十八-

布客飞龙
77 阅读1小时+

MySQL8 中文参考（六十八）

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

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。只有在导入整个表或表的一部分后才执行提交。

avatar
文章
阅读
粉丝