15.8 InnoDB Tables and Indexes

15.8 InnoDB Tables and Indexes

15.8.1 InnoDB Tables

15.8.2 InnoDB Indexes

15.8.1 InnoDB Tables

15.8.1.1 Creating InnoDB Tables

15.8.1.2 The Physical Row Structure of an InnoDB Table

15.8.1.3 Moving or Copying InnoDB Tables

15.8.1.4 Converting Tables from MyISAM to InnoDB

15.8.1.5 AUTO_INCREMENT Handling in InnoDB

15.8.1.6 InnoDB and FOREIGN KEY Constraints

15.8.1.7 Limits on InnoDB Tables

15.8.1.1 Creating InnoDB Tables

要想创建 InnoDB 存储引擎的数据表，可以使用如下的 SQL 语句：

# CREATE TABLE t(id int auto_increment primary key,cname char(20) not null) ENGINE=InnoDB;

如果不想在创建数据表的时候每次都带上 ENGINE=InnoDB，那么可以使用如下的语句来查看 InnoDB 存储引擎是否是默认的存储引擎。

# SELECT @@default_storage_engine;

InnoDB 表及其索引可以在 system tablespace、file-per-table、general tablespace 中创建。当启用 innodb_file_per_table 参数时，默认情况下将在一个单独的文件表空间中隐式的创建一个 InnoDB 表。相反的如果禁用了 innodb_file_per_table 参数，则在 InnoDB 系统表空间中隐式的创建一个 InnoDB 表，要想在一般表空间中创建表，可以使用 CREATE TABLE ... TABLESPACE 的语法。

当在独立表空间中创建表时 MySQL 默认在数据目录下的数据库目录中创建一个 .ibd 表空间文件。在 InnoDB 系统表空间中创建的表在 ibdata 文件中。在通用表空间中创建的表则在通用表空间 .ibd 文件中。

InnoDB 会为创建的每个表在数据字典中添加一条记录，该记录包含了数据库名。例如：在 test 数据库下创建一个 t 表，实际的目录显示为 “test/t”，这意味着可以在另一个数据库中创建同名的 t 表，表名不会在 InnoDB 中发生冲突。

InnoDB Tables and Row Formats

InnoDB 数据表的行格式是由参数 innodb_default_row_format 来决定的，默认是 DYNAMIC。Dynamic 和 Compressed 格式的行允许你充分的利用 InnoDB 的特性。例如：表压缩跟长字段的 off-page 存储。创建表举例：

# CREATE TABLE t (a INT, b CHAR (20), PRIMARY KEY (a)) ROW_FORMAT=DYNAMIC;

# CREATE TABLE t (a INT, b CHAR (20), PRIMARY KEY (a)) ROW_FORMAT=COMPRESSED;

InnoDB Tables and Primary Keys

始终为 InnoDB 引擎的数据表创建一个主键，这是一个基本的要求，主键一般有如下的特性：

# 被重要的查询所引用的列

# 该列始终不会出现空值

# 该列的数值始终不会出现重复

# 插入后其值很少会发生改变的列

在创建数据表的时候，最好先定义好主键，他比起你创建好数据表，然后加载数据，最后使用 ALTER TABLE 操作增加一个主键的速度要好很多。

要想查看一个数据表的特性，可以使用如下的语句：

# SHOW TABLE STATUS LIKE ‘t%’ \G;

也可以使用如下的语句：

# SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE NAME='test/t' \G;

15.8.1.2 The Physical Row Structure of an InnoDB Table

InnoDB 存储引擎的数据表的行结构主要取决于你的参数 innodb_default_row_format 的设置，该参数默认是 DYNAMIC。

Determining the Row Format of an InnoDB Table

确认一个数据表的行格式，可以使用如下的语句：

# SHOW TABLE STATUS;

Redundant Row Format Characteristics

REDUNDANT 格式的基本不怎么使用了，他主要是为了保持与旧版本的一致性。

COMPACT Row Format Characteristics

COMPACT 的行格式存储减少了大约 20% 的行存储空间，代价是增加了某些操作的 CPU 使用量。如果你的工作负载受到缓存命中率和磁盘速度的限制，那么 COMPACT 格式是一个很好的选择。如果工作负载很少受到 CPU 速度的限制，那么 COMPACT 格式反而会比较慢。

DYNAMIC and COMPRESSED Row Format Characteristics

DYNAMIC 跟 COMPRESSED 其实是 COMPACT 的一个变体。

15.8.1.3 Moving or Copying InnoDB Tables

下面主要讲一下在不同的实例之间拷贝 InnoDB 存储引擎的数据表的实现。首先需要保证的是所有的表名都是小写的。也就是说参数：lower_case_table_names 需要为 1。

Moving or Copying InnoDB Tables 的实现技术包含如下几步：

•Transportable Tablespaces

•MySQL Enterprise Backup

•Copying Data Files (Cold Backup Method)

•Export and Import (mysqldump)

15.8.1.4 Converting Tables from MyISAM to InnoDB

如果你原来的项目中有一些数据表是 MyISAM 存储引擎的，现在想把它换成 InnoDB 存储引擎，你需要执行如下的步骤进行转换。

• Adjusting Memory Usage for MyISAM and InnoDB

• Handling Too-Long Or Too-Short Transactions

• Handling Deadlocks

• Planning the Storage Layout

• Converting an Existing Table

• Cloning the Structure of a Table

• Transferring Existing Data

• Storage Requirements

• Defining a PRIMARY KEY for Each Table

• Application Performance Considerations

• Understanding Files Associated with InnoDB Tables

Adjusting Memory Usage for MyISAM and InnoDB

这一步主要是调整参数的设置，MyISAM 存储引擎主要是靠参数 key_buffer_size，而 InnoDB 存储引擎则是依靠参数 innodb_buffer_pool_size。InnoDB 缓冲池缓存表数据和索引数据以加快查询，并将查询结果保存在内存中以便重用。

Handling Too-Long Or Too-Short Transactions

这一步主要是一些大事务的处理。当开启一个事务时，系统会保存事务开始时看到的数据快照，如果一个事务插入、更新和删除数百万行，而其他杂散的事务也在运行，则会导致大量开销。因此，要注意避免运行太久的事务。如果你正在使用 mysql 会话进行交互实验，请在完成时提交（以完成更改）或回滚（以撤消更改），而不是长时间的将这个会话挂起。当使用一系列 INSERT 语句加载大量数据时，定期提交结果，以避免事务持续运行数小时。在数据仓库的典型负载操作中，如果出现问题，将截断表（使用 truncate table），并从开始重新开始，而不是回滚。

对于 InnoDB 表上的大多数操作，应该使用设置 autocommit = 0。从效率的角度来看，当发生大量连续的插入、更新或删除语句时，这可以避免不必要的 I/O。从安全的角度来看，如果在 mysql 命令行或应用程序中出现错误的时候，那么可以使用回滚语句来恢复丢失或混乱的数据。

autocommit = 1 适合 InnoDB 表运行一系列查询以生成报告或分析统计数据的时候。

如果您做了一系列相关的更改，请在最后用一个提交一次性完成所有更改。例如，如果将相关信息插入到多个表中，那么在进行所有更改之后，执行单个提交。或者，如果您运行了许多连续的 INSERT 语句，那么在加载所有数据之后执行一个 COMMIT；如果你正在执行数百万条 INSERT 语句，那么可以通过每 10000 或 100000 条记录发出一个 COMMIT 来分割庞大的事务，这样事务就不会变得太大。

Handling Deadlocks

你可能会在 MySQL 错误日志中看到指向“死锁”的警告消息，或者使用命令 SHOW ENGINE INNODB STATUS 的输出看到死锁信息。尽管听起来很吓人，但死锁对于 InnoDB 表来说并不是一个严重的问题，而且通常不需要任何纠正措施。当两个事务开始修改多个表（以不同的顺序访问表）时，它们可以达到每个事务都在等待另一个事务的状态，而这两个事务都不能继续进行。当启用死锁检测（默认）时，MySQL 立即检测到这个条件，并取消（回滚）“更小”的事务。无论哪种方式，应用程序都需要错误处理逻辑来重新启动由于死锁强制取消的事务。当你重新发出与以前相同的 SQL 语句时，原始的时间问题将不再适用。另一个事务已经完成，您的事务可以继续进行，或者另一个事务仍在进行中，您的事务将等待它完成。

如果经常出现死锁警告，你可能需要检查应用程序代码以一致的方式重新排序 SQL 操作，或者缩短事务。你可以使用 innodb_print_all_deadlocks 选项打印出所有的死锁信息，而不仅仅是使用 SHOW ENGINE INNODB STATUS 输出的最后的死锁信息。

Planning the Storage Layout

这块最主要的还是参数 innodb_file_per_table and innodb_page_size 的设置。

Converting an Existing Table

将一个非 InnoDB 存储引擎的表修改为 InnoDB 存储引擎的表，需要使用 ALTER 命令

# ALTER TABLE TABLE_NAME ENGINE=InnoDB;

Cloning the Structure of a Table

你可以创建一个空的 InnoDB 存储引擎的表，该表的结构与 MyISAM 存储引擎的表是一致。

Transferring Existing Data

使用如下的语句进行数据转储

INSERT INTO innodb_table SELECT * FROM myisam_table ORDER BY primary_key;

Storage Requirements

InnoDB 表需要比 MyISAM 表更多的磁盘空间。如果 ALTER TABLE 操作耗尽了空间，那么它将启动回滚。对于插入，InnoDB 使用 insert 缓冲区将次要索引记录成批地合并到索引中。这节省了很多磁盘 I/O。对于回滚，不使用这种机制，回滚所需的时间可能比插入时间长 30 倍。

Defining a PRIMARY KEY for Each Table

我们说主键是影响 MySQL 查询性能和表的索引空间使用的关键因素。主键惟一地标识了表中的一行。表中的每一行必须有一个主键值，没有两行可以有相同的主键值。

关于主键的一些指导性意见：

# 每个数据表都应该创建一个主键

# 创建主键应该在创建数据表的时候，而不是在创建导入数据之后，再使用 ALTER TABLE 操作完成

# 主键的列以及他的类型的选择也很重要，一般不选择可变长度的字符串类型做主键

# 当没有可满足的列的时候，使用自增 ID auto-increment 是一个不错的选择

Application Performance Considerations

InnoDB 的可靠性和可伸缩性特性需要比 MyISAM 类型的表更多的磁盘存储。因此你需要更好的规划索引的定义，以便更好地利用空间，减少处理结果集时的 I/O 和内存消耗，以及更好地使用索引查找的查询优化计划。

Understanding Files Associated with InnoDB Tables

不能删除 InnoDB system tablespace 的 ibdata 文件

15.8.1.5 AUTO_INCREMENT Handling in InnoDB

InnoDB 提供了一种可配置的锁定机制，可以显著提高使用 AUTO_INCREMENT 列向表添加行的 SQL 语句的性能。要使用 AUTO_INCREMENT，必须将 AUTO_INCREMENT 列定义为索引的一部分，一般都是讲该列单独定义成一列。

下面简单介绍一下 AUTO_INCREMENT 锁模式的行为、不同 AUTO_INCREMENT 锁模式设置的使用含义，以及 InnoDB 如何初始化 AUTO_INCREMENT 计数器。

• InnoDB AUTO_INCREMENT Lock Modes

• InnoDB AUTO_INCREMENT Lock Mode Usage Implications

• InnoDB AUTO_INCREMENT Counter Initialization

InnoDB AUTO_INCREMENT Lock Modes

innodb_autoinc_lock_mode 配置参数有三个可能的设置。分别为“传统”、“连续”或“交叉”锁模式，对应的值设置分别为 0、1、2。从 MySQL 8.0 开始，交叉锁模式（innodb_autoinc_lock_mode = 2） 是默认设置。在 MySQL 8.0 之前，连续锁模式是默认的（innodb_autoinc_lock_mode = 1）。

InnoDB AUTO_INCREMENT Lock Mode Usage Implications

使用基于行格式的复制，所有的自动增量锁模式都是安全的。

在所有的锁模式（0、1、2）中，如果生成自动增量值的事务回滚，那么这些自动增量值就会“丢失”。一旦为自动递增列生成值，无论是否完成“插入式”语句，以及是否回滚包含的事务，都不能回滚该值。这样丢失的值不会被重用。

InnoDB AUTO_INCREMENT Counter Initialization

如果你为一个 InnoDB 表指定一个 AUTO_INCREMENT 列，那么内存中的表对象包含一个名为“自动递增计数器”的特殊计数器，该计数器在为该列分配新值时使用。在 MySQL 8.0 中，当前最大的自动增量计数器值在每次更改时写入重做日志，并保存到每个检查点上的专用系统表。这些更改使当前的最大自动增量计数器值在服务器重新启动时保持不变。在 MySQL 8.0 中，服务器重新启动不会取消 AUTO_INCREMENT = N 表选项的效果。如果将自动递增计数器初始化为特定的值，或者将自动递增计数器的值更改为较大的值，则新值将在服务器重新启动时持久化。

15.8.1.6 InnoDB and FOREIGN KEY Constraints

• Foreign Key Definitions

• Referential Actions

• Foreign Key Restrictions for Generated Columns and Virtual Indexes

• Foreign Key Usage and Error Information

Foreign Key Definitions

InnoDB 存储引擎支持外键约束，允许在数据表上创建外键，但是现在的项目中已经基本上很少使用外键约束了。

Referential Actions

InnoDB 通过深度优先算法执行级联操作，但是这个深度不能超过 15 层。

Foreign Key Restrictions for Generated Columns and Virtual Indexes

外键约束不能引用虚拟生成的列

Foreign Key Usage and Error Information

可以通过查询 INFORMATION_SCHEMA 获得关于外键及其用法的一般信息。KEY_COLUMN_USAGE 表和更多针对 InnoDB 表的信息可以在 INNODB_FOREIGN 和 INNODB_FOREIGN_COLS 表中找到，也可以在 INFORMATION_SCHEMA 数据库中找到。除了使用 SHOW ERRORS 外，如果涉及到 InnoDB 表的外键错误，您可以通过检查 SHOW ENGINE InnoDB 状态的输出来获得关于最近的 InnoDB 外键错误的详细解释。

15.8.1.7 Limits on InnoDB Tables

• Maximums and Minimums

• Restrictions on InnoDB Tables

• Locking and Transactions

Maximums and Minimums

一个数据表最多可以包含 1017 列。

一个数据表最多可以包含 64 个二级索引。

InnoDB 类型的数据表的索引最多是 3072 个字节， DYNAMIC 格式的。

一个索引最多可以 16 个列。

如果一行少于半页长，则所有的行都存储在页面内部。如果超过半页，则为外部的页外存储选择可变长度列，直到行适合半页。

InnoDB 每行的长度其实是少于 65535 字节的。

Restrictions on InnoDB Tables

ANALYSE TABLE 操作通过更新索引的统计值，但是这是个估值，并不准确。

InnoDB 存储引擎不保存表中行的内部计数，因为并发事务可能同时“看到”不同数量的行。因此，SELECT COUNT(*) 语句只计算当前事务可见的行。

InnoDB 处理 SELECT COUNT(*) 是通过扫描聚集索引实现的。

当 AUTO_INCREMENT 数值达到最大值的时候，就会出现提示主键冲突。这个错误比较有意思。可以自己测试的玩玩。

在 InnoDB 存储引擎中，你不可以使用如下的字段名称作为列名（DB_ROW_ID, DB_TRX_ID, DB_ROLL_PTR, and DB_MIX_ID）

Locking and Transactions

如果 innodb_table_locks = 1，则锁表会在每个表上获取两个锁。除了 MySQL 层的表锁之外，它还获得了一个 InnoDB 表锁。InnoDB 的锁在提交或者回滚的时候都会释放掉所有的锁信息。事务的并发数最大是 96 * 1023。

15.8.2 InnoDB Indexes

15.8.2.1 Clustered and Secondary Indexes

15.8.2.2 The Physical Structure of an InnoDB Index

15.8.2.3 Sorted Index Builds

15.8.2.4 InnoDB FULLTEXT Indexes

15.8.2.1 Clustered and Secondary Indexes

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

在表上定义主键时，InnoDB 会将它用作聚集索引。为创建的每个 InnoDB 表定义一个主键是一个基本的要求。如果没有逻辑惟一和非空列，则可以添加一个自动增量列（auto-increment）作为主键，其值将自动填充。

如果该表没有主键或合适的唯一索引，InnoDB 将在包含行 ID 值的合成列上生成隐藏的聚集索引。由行 ID 排序的行在物理上是按插入顺序排列的。

How the Clustered Index Speeds Up Queries

通过聚集索引访问行的速度很快，因为搜索直接指向包含行数据的页。如果一个表很大，聚集索引体系结构通常只需要一个磁盘 I/O 操作。

How Secondary Indexes Relate to the Clustered Index

除聚集索引之外的所有索引都称为辅助索引。在 InnoDB 中，辅助索引中的每个记录都包含行的主键列，以及为辅助索引指定的其他列。InnoDB 使用这个主键值搜索聚集索引中的行。如果主键较长，则次索引使用更多的空间，因此使用短的主键比较有利。

15.8.2.2 The Physical Structure of an InnoDB Index

除了空间索引之外，InnoDB 索引是 b 树数据结构。索引记录存储在 B-tree 或 R-tree 数据结构的页中。索引页的默认大小是 16KB。当新记录被插入到 InnoDB 聚集索引中时，InnoDB 会保留页面的 1/16 作为新的索引记录的插入和旧的索引记录的更新。如果按顺序插入索引记录，则产生的索引页大约为 15/16。如果记录按随机顺序插入，the pages are from 1/2 to 15/16 full。InnoDB 在创建或重建 B-tree 索引时是执行批量加载。这种创建索引的方法称为排序索引构建。innodb_fill_factor 配置选项定义在排序索引构建期间填充的每个 B-tree 页面上的空间百分比，其余空间用于将来的索引增长。如果 InnoDB 索引页的填充因子低于 MERGE_THRESHOLD 如果未指定，默认为 50%，InnoDB 尝试收缩索引树以释放页面。通过在初始化 MySQL 实例之前设置 innodb_page_size 配置选项，可以为 MySQL 实例中的所有 InnoDB 表空间定义页面大小。一旦定义了实例的页面大小，您就不能在不重新初始化实例的情况下更改它。支持的大小是 64KB、32KB、16KB、8KB和4KB。

15.8.2.3 Sorted Index Builds

InnoDB 在创建或重构索引时执行大量负载，而不是插入一个索引记录。这种创建索引的方法也称为排序索引构建。

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

在引入排序索引构建之前，每次使用 insert api 将索引条目插入到 B-tree one 记录中。此方法涉及打开 B-tree 游标以找到插入位置，然后使用乐观插入将条目插入到 B-tree 页面。如果由于页面已满而导致插入失败，则执行悲观插入，这涉及打开 B-tree 游标，并根据需要分割和合并 B-tree 节点，以找到条目的空间。这种构建索引的“自顶向下”方法的缺点是搜索插入位置的成本。排序索引构建使用“自底向上”方法来构建索引。使用这种方法，在 B 树的所有级别上都有对最右叶页的引用。在必要的 B tree 深度处分配最右的叶子页，并根据它们的排序顺序插入条目。当一个叶子页面被填满时，一个节点指针被附加到父页面，一个同级叶子页面被分配给下一个插入。此过程将继续，直到插入所有条目，这可能导致插入到根级别。当分配一个同级页面时，引用之前被固定的 l。

Reserving B-tree Page Space for Future Index Growth

要为将来的索引增长预留空间，可以使用 innodb_fill_factor 配置选项来保留一定百分比的 B-tree 页面空间。例如，将 innodb_fill_factor 设置为 80 将在排序索引构建期间保留 B-tree 页面 20% 的空间。此设置适用于 B-tree 叶子和非叶子页面。它不适用于用于文本或 BLOB 条目的外部页面。预留的空间量可能不完全配置，因为 innodb_fill_factor 值被解释为一个提示，而不是硬限制。

Sorted Index Builds and Redo Logging

在排序的索引构建过程中禁用重做日志记录。相反，有一个检查点来确保索引构建能够经受崩溃或失败。检查点强制将所有脏页写入磁盘。在一个排序的索引构建过程中，页面清理线程会定期发出信号来刷新脏页面，以确保检查点操作可以快速处理。通常，当清除页面的数量低于设置的阈值时，页面清理线程会刷新脏页面。对于已排序的索引构建，脏页被及时刷新，以减少检查点开销并并行地进行 I/O 跟 CPU 操作。

15.8.2.4 InnoDB FULLTEXT Indexes

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

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

使用 MATCH() ... AGAINST 执行全文搜索。

InnoDB FULLTEXT

• InnoDB Full-Text Index Design

• InnoDB Full-Text Index Tables

• InnoDB Full-Text Index Cache

• InnoDB Full-Text Index Document ID and FTS_DOC_ID Column

• InnoDB Full-Text Index Deletion Handling

• InnoDB Full-Text Index Transaction Handling

• Monitoring InnoDB Full-Text Indexes

InnoDB Full-Text Index Design

InnoDB 全文本索引具有反向索引设计。反向索引存储一个单词列表，对于每一个单词都有一个该词出现的文档列表。为了支持接近搜索，每个单词的位置信息也被作为一个字节偏移存储。

InnoDB Full-Text Index Tables

在创建 InnoDB 全文索引时，将创建一组索引表。

# 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;

# SELECT table_id,NAME,space

FROM INFORMATION_SCHEMA.INNODB_TABLES

WHERENAME LIKE 'test/%';

前六个表表示反向索引，称为辅助索引表。当传入文档被标记时，单个单词(也称为“标记”)会插入到索引表中，以及位置信息和相关的文档 ID (DOC_ID)。根据字的第一个字符的字符集排序权，在六个索引表中对字进行完全排序和分区。

反向索引被划分为 6 个辅助索引表，以支持并行索引创建。默认情况下，两个线程将标记、排序和将单词和相关数据插入到索引表中。可以使用 innodb_ft_sort_pll_degree 选项配置线程数。在大型表上创建全文索引时，考虑增加线程的数量。

辅助索引表名以 fts_ 为前缀，以 index_* 为后缀。每个索引表都与索引表名中的十六进制值相关联，表名与索引表的 table_id 相匹配。

表示 FULLTEXT 索引的 index_id 的十六进制值也出现在辅助索引表名中。可以通过查询INFORMATION_SCHEMA 来标识 opening_lines 表 (idx) 上定义的索引。

# SELECT index_id, name, table_id, space from INFORMATION_SCHEMA.INNODB_INDEXES WHERE index_id=xx

如果主表是在一个文件表空间中创建的，那么索引表就存储在它们自己的表空间中。

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

即使删除了全文索引，也要保留常用的辅助表。当删除全文索引时，将保留为索引创建的 FTS_DOC_ID列，因为删除 FTS_DOC_ID 列将需要重新构建表。管理 FTS_DOC_ID 列需要常见的派生表。

fts_*_deleted and fts_*_deleted_cache

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

fts_*_being_deleted and fts_*_being_deleted_cache

包含被删除的文档的文档 id (DOC_ID)，这些文档的数据目前正在从全文索引中删除。fts_*_being_deleted_cache 表是 fts_*_being_deleted 表的内存版本。

fts_*_config

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

InnoDB Full-Text Index Cache

当插入一个文档时，它被标记，单个单词和相关数据被插入到 FULLTEXT 索引中。这个过程，甚至对于小文档来说，都可能导致对辅助索引表的大量小插入，从而使对这些表的并发访问成为一个争论点。为了避免这个问题，InnoDB 使用一个全文索引缓存来临时缓存最近插入的行的索引表插入。这个内存内缓存结构保存插入，直到缓存满，然后批处理将它们刷新到磁盘(到辅助索引表)。您可以查询INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE 表，用于查看最近插入的行的标记化数据。缓存和批处理刷新行为避免了对辅助索引表的频繁更新，这可能导致在繁忙的插入和更新时间并发访问问题。批处理技术还可以避免对同一个单词进行多次插入，并最小化重复的条目。与其将每个单词单独刷新，不如将同一个单词的插入合并到磁盘中，并将其作为单个条目进行刷新，从而提高插入效率，同时尽可能减小辅助索引表。innodb_ft_cache_size 变量用于配置全文索引缓存大小(基于每个表)，这将影响刷新全文索引缓存的频率。您还可以使用 innodb_ft_total_cache_size 选项为给定实例中的所有表定义一个全局全文本索引缓存大小限制。全文索引缓存存储与辅助索引表相同的信息。但是，全文索引缓存仅缓存最近插入的行的标记数据。当查询时，已经刷新到磁盘(到全文辅助表)的数据不会返回到全文索引缓存中。直接查询辅助索引表中的数据，并将辅助索引表的结果与全文索引缓存的结果合并，然后再返回。

InnoDB Full-Text Index Document ID and FTS_DOC_ID Column

InnoDB 使用一个惟一的文档标识符作为文档 ID (DOC_ID)，将全文索引中的单词映射到文档中出现单词的记录。映射需要索引表上的 FTS_DOC_ID 列。如果没有定义FTS_DOC_ID 列，InnoDB 会在创建全文索引时自动添加隐藏的 FTS_DOC_ID 列。下面举个例子：

# 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;

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

# CREATE FULLTEXT INDEX idx ON opening_lines (opening_line);

# SHOW WARNINGS;

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

在创建表时定义 FTS_DOC_ID 列比在已经加载了数据的表上创建全文索引花费的代价要小很多。如果在加载数据之前在表上定义了 FTS_DOC_ID 列，则无需重新构建表及其索引以添加新列。如果您不关心创建全文索引性能，请忽略 FTS_DOC_ID 列，让 InnoDB 创建它。InnoDB 创建一个隐藏的 FTS_DOC_ID 列以及 fts_id 列上的唯一索引(FTS_DOC_ID_INDEX)。如果您想创建自己的 FTS_DOC_ID 列，那么必须将列定义为 BIGINT UNSIGNED NOT NULL 并命名为 FTS_DOC_ID (大写)。

如果你选择自己定义 FTS_DOC_ID 列，那么您将负责管理该列，以避免出现空值或重复值。FTS_DOC_ID 值不能重用，这意味着必须不断增加 FTS_DOC_ID 值。

您可以选择在 FTS_DOC_ID 列上创建所需的唯一 FTS_DOC_ID_INDEX (大写)。

# CREATE UNIQUE INDEX FTS_DOC_ID_INDEX ON opening_lines (FTS_DOC_ID);

InnoDB Full-Text Index Deletion Handling

删除包含全文索引列的记录可能会导致辅助索引表中大量的小删除，从而使对这些表的并发访问成为一个争论点。为了避免这个问题，每当从索引表中删除一条记录时，被删除文档的文档 ID (DOC_ID) 就会被记录到一个特殊的 FTS_*_DELETED 表中，并且索引记录仍然保存在全文索引中。在返回查询结果之前，使用 FTS_*_DELETED 表中的信息过滤掉删除的文档 ID。这种设计的好处是删除操作快速且廉价。缺点是在删除记录之后，索引的大小不会立即减少。要删除已删除记录的全文索引条目，请在索引表上使用 innodb_optimize_fulltext_only = on 然后运行 optimization TABLE，以重新构建全文索引。

InnoDB Full-Text Index Transaction Handling

InnoDB FULLTEXT 索引具有特殊的事务处理特性，因为它具有缓存和批处理的特性。具体地说，在事务提交时对全文索引的更新和插入进行处理，这意味着全文搜索只能看到提交的数据即全文搜索只在插入的行提交之后返回结果。下面举个例子：

# 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;

BEGIN;

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');

SELECT COUNT(*) FROM opening_lines

WHERE MATCH (opening_line) AGAINST ('Ishmael');

此时发现查询找不到数据

COMMIT;

SELECT COUNT(*) FROM opening_lines

WHERE MATCH (opening_line) AGAINST ('Ishmael');

Monitoring InnoDB Full-Text Indexes

通过查询以下 INFORMATION_SCHEMA 数据表，你可以监视和检查 InnoDB FULLTEXT 索引的 text-processing 。

• INNODB_FT_CONFIG

• INNODB_FT_INDEX_TABLE

• INNODB_FT_INDEX_CACHE

• INNODB_FT_DEFAULT_STOPWORD

• INNODB_FT_DELETED

• INNODB_FT_BEING_DELETED