“InnoDB：InnoDB 磁盘结构：Indexes”的版本间差异

2023年3月31日 (五) 23:23的最新版本

聚集索引和二级索引【！！！】

每个 InnoDB 表都有一个称为“clustered index”的特殊索引，其中存储了行数据。通常，聚集索引与 primary key 同义。

在表上定义“PRIMARY KEY”时，InnoDB 会将其用作聚集索引。为您创建的每个表定义一个主键。如果没有逻辑唯一且非空的列或列集，请添加一个新的“auto-increment”列，其值将自动填写。
如果您没有为表定义“PRIMARY KEY”，则 MySQL 将找到第一个“UNIQUE”索引，其中所有键列均为“NOT NULL”，而 InnoDB 将其用作聚集索引。
如果表没有“PRIMARY KEY”或合适的“UNIQUE”索引，则 InnoDB 在包含行 ID 值的合成列上内部生成名为“GEN_CLUST_INDEX”的隐藏聚集索引。这些行由 InnoDB 分配给该表中的行的 ID 排序。行 ID 是一个 6 字节的字段，随着插入新行而单调增加。因此，按行 ID 排序的行实际上在插入 Sequences 上。

聚集索引如何加快查询速度

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

二级索引如何与聚集索引相关

除聚集索引以外的所有索引都称为“secondary indexes”（二级索引、第二索引、辅助索引）。在 InnoDB 中，辅助索引中的每个记录都包含该行的主键列以及为该辅助索引指定的列。 InnoDB 使用此主键值在聚集索引中搜索行。

如果主键较长，则辅助索引将使用更多空间，因此具有较短的主键是有利的。

InnoDB 索引的物理结构

除空间索引外，InnoDB 索引是B-tree数据结构。空间索引使用R-trees，这是用于索引多维数据的专用数据结构。索引记录存储在其 B 树或 R 树数据结构的叶页中。索引页的默认大小为 16KB。

当将新记录插入 InnoDB clustered index 时，InnoDB 会尝试使页面的 1/16 空闲，以供将来插入和更新索引记录。如果按 Sequences 插入索引记录(升序或降序)，则所得到的索引页约为 15/16 装满。

如果以随机 Sequences 插入记录，则页面的容量为 1/2 到 15/16。【？？？】

InnoDB 在创建或重建 B 树索引时执行批量加载。这种索引创建方法称为排序索引构建。 “innodb_fill_factor”配置选项定义了在排序索引构建期间每个 B 树页面上已填充的空间百分比，剩余的空间保留用于将来的索引增长。

空间索引不支持排序索引构建。
“innodb_fill_factor”设置为 100 会使 Cluster index 页面中的空间的 1/16 留给将来的索引增长。

如果 InnoDB 索引页的填充因子下降到“MERGE_THRESHOLD”以下（如果未指定，默认值为 50％），则 InnoDB 尝试收缩索引树以释放页面。

“MERGE_THRESHOLD”设置适用于 B 树和 R 树索引。

您可以通过在初始化 MySQL 实例之前设置“innodb_page_size”配置选项来为 MySQL 实例中的所有 InnoDB 表空间定义 page size。一旦定义了实例的页面大小，就不能在不重新初始化实例的情况下对其进行更改。支持的大小为 64KB，32KB，16KB(默认)，8KB 和 4KB。

MySQL 5.7 中增加了对 32KB 和 64KB 页面大小的支持。
使用特定 InnoDB 页面大小的 MySQL 实例不能使用来自使用不同页面大小的实例的数据文件或日志文件。

排序索引构建【？？？】

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

索引构建分为三个阶段：

第一阶段：扫描 clustered index（聚集索引），生成索引项并将其添加到排序缓冲区（sort buffer）。当排序缓冲区变满时，索引项被排序并写入临时中间文件。
- 此过程也称为一个“run”。
第二阶段：将一个或多个“run”写入临时中间文件，对文件中的所有索引项执行合并排序。
第三阶段：将已排序的索引项插入 B-tree。

非排序索引构建和排序索引构建：

在引入排序索引构建之前，索引项是使用插入 API 一次插入一条记录到B树中的。这个方法包括打开一个 B-tree 游标来找到插入位置，然后使用乐观插入将条目插入到 B-tree 页面中。如果由于页面已满而导致插入失败，则将执行悲观插入，这涉及打开一个 B-tree 游标，并根据需要拆分和合并B树节点，以找到条目的空间。
- 这种“自顶向下”建立索引的方法的缺点是搜索插入位置的开销以及 B树节点的不断拆分和合并。
排序索引的构建使用“自下而上”的方法来构建索引。通过这种方法，在 B 树的所有级别都保留了对最右边的叶子页的引用。在必要的 B 树深度处分配最右边的叶子页，并根据其排序 Sequences 插入条目。一旦叶子页已满，节点指针将附加到父页面，并且为下一个插入分配同级叶子页。此过程将一直持续到插入所有条目为止，这可能会导致插入到根级别。分配同级页面后，将释放对先前固定的叶子页面的引用，并且新分配的叶子页面将成为最右边的叶子页面和新的默认插入位置。【？】

保留 B 树页面空间以用于将来的索引增长

要为将来的索引增长留出空间，可以使用“innodb_fill_factor”配置选项来保留一定比例的 B 树页面空间。（例如，将“innodb_fill_factor”设置为 80 会在排序索引构建过程中保留 B 树页面中 20％的空间。）

此设置适用于 B 树叶子页面和非叶子页面。它不适用于用于“TEXT”或“BLOB”条目的外部页面。
保留的空间量可能与配置的不完全一样，因为“innodb_fill_factor”值被解释为提示而不是硬限制。

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

全文索引支持排序索引生成。以前，SQL用于将条目插入到全文索引中。

排序索引构建和压缩表

对于压缩表，以前的索引创建方法将条目附加到压缩和未压缩的页面上。当修改日志（表示压缩页面上的可用空间）已满时，将重新压缩压缩页面。如果由于空间不足而导致压缩失败，则页面将被拆分。使用排序的索引构建时，条目仅追加到未压缩的页面。当未压缩的页面已满时，它将被压缩。自适应填充用于确保大多数情况下压缩成功，但是，如果压缩失败，则将拆分页面并再次尝试压缩。该过程一直持续到压缩成功为止。

排序索引构建和重做日志记录

Redo logging 在排序索引构建期间被禁用。而是使用 checkpoint 来确保索引构建可以承受崩溃或失败。该检查点强制将所有脏页写入磁盘。在排序索引构建过程中，会定期向 page cleaner 线程发送 signal 以刷新 dirty pages，以确保可以快速处理检查点操作。通常，当清除页数低于设置的阈值时，页面清除程序线程将刷新脏页。对于排序的索引构建，脏页将立即刷新，以减少检查点开销并并行化 I/O 和 CPU 活动。

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

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

InnoDB 全文索引【？？？】

在基于文本的列（“CHAR”，“VARCHAR”或“TEXT”列）上创建全文索引，以帮助加快对这些列中包含的数据的查询和 DML 操作，而忽略定义为停用词的任何单词。

全文索引被定义为“CREATE TABLE”语句的一部分，或使用“ALTER TABLE”或“CREATE INDEX”添加到现有表中。
使用“MATCH() ... AGAINST”语法执行全文搜索。
【见：“MySQL 函数和运算符：全文搜索功能”】

InnoDB 全文索引设计

InnoDB 全文索引具有倒排索引设计：

倒排索引存储一个单词列表，对于每个单词，存储单词出现的文档列表。为了支持邻近搜索，每个单词的位置信息也作为字节偏移量存储。

如何理解倒排索引（inverted index，反向索引）？
正向索引（forward index）：文档（页面） ---> 关键词（内容）。

反向索引（inverted index）：关键词（内容） ---> 文档（页面）。是实现“单词-文档矩阵”的一种具体存储形式。（也是搜索引擎的一种实现方式）

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_SYS_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 |
+----------+----------------------------------------------------+-------+

前六个 table 代表反向索引，并称为辅助索引表。对传入文档进行标记时，各个单词（也称为“令牌”）与位置信息和关联的文档 ID（DOC_ID）一起插入索引表中。根据单词第一个字符的字符集排序权重，单词在六个索引表中得到完全排序和分区。【？？？】
倒排索引分为六个辅助索引表，以支持并行索引创建。默认情况下，两个线程对索引表中的单词和相关数据进行标记化，排序和插入。线程数可使用“innodb_ft_sort_pll_degree”选项配置。在大型表上创建全文索引时，请考虑增加线程数。【？？？】
辅助索引表名称的前缀为“FTS_”，后缀为“INDEX_”。每个索引表都通过与索引表的 table_id 匹配的索引表名称中的十六进制值与索引表相关联。【？？？】
例如，“test/opening_lines”表的 table_id 是327，其十六进制值为“0x147”。如前面的示例所示，十六进制值“147”出现在与“test/opening_lines”表关联的索引表的名称中。
表示全文索引的 index_id 的十六进制值也出现在辅助索引表名称中。【？？？】
例如，在辅助表名称“test/FTS_0000000000000147_00000000000001c9_INDEX_1”中，十六进制值“1c9”的十进制值为“457”，可以通过在“INFORMATION_SCHEMA.INNODB_SYS_INDEXES”表中查询该值（457）来标识在“opening_linestable(idx)”上定义的索引。
```
mysql> SELECT index_id, name, table_id, space from INFORMATION_SCHEMA.INNODB_SYS_INDEXES
       WHERE index_id=457;
+----------+------+----------+-------+
| index_id | name | table_id | space |
+----------+------+----------+-------+
|      457 | idx  |      327 |   283 |
+----------+------+----------+-------+
```

如果主表是在 file-per-table 表空间中创建的，则索引表将存储在其自己的表空间中。

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

即使删除了全文索引，也会保留公用辅助表。删除全文索引时，将保留为索引创建的“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”值来标识尚未刷新到磁盘的文档，以便可以重新分析文档并将其添加回全文索引缓存中。要查看此表中的数据，请查询“INFORMATION_SCHEMA.INNODB_FT_CONFIG”表。

InnoDB 全文索引缓存

插入文档后，将对其进行标记化，并将各个单词和关联的数据插入全文索引。即使对于小型文档，此过程也可能导致大量小的插入辅助索引表，从而使同时访问这些表成为争执点。为避免此问题，InnoDB 使用全文索引缓存来临时缓存最近插入的行的索引表插入。此内存中的高速缓存结构将保留插入，直到高速缓存已满，然后将其批量刷新到磁盘(至辅助索引表)。您可以查询“INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE”表来查看最近插入的行的标记化数据。

缓存和批处理刷新行为避免了对辅助索引表的频繁更新，这可能会在繁忙的插入和更新时间期间导致并发访问问题。批处理技术还避免了同一单词的多次插入，并最大程度地减少了重复输入。代替单独刷新每个单词，对同一单词的插入进行合并并作为单个条目刷新到磁盘，从而提高了插入效率，同时使辅助索引表尽可能小。
“innodb_ft_cache_size”变量用于配置全文索引缓存大小(基于每个表)，这会影响刷新全文索引缓存的频率。您还可以使用“innodb_ft_total_cache_size”选项为给定实例中的所有表定义全局全文索引缓存大小限制。

全文索引缓存存储与辅助索引表相同的信息。但是，全文索引缓存仅缓存最近插入的行的标记化数据。查询时，已刷新到磁盘(到全文本辅助表)的数据不会带回到全文本索引高速缓存中。直接查询辅助索引表中的数据，并将辅助索引表中的结果与全文索引缓存中的结果合并，然后再返回。

InnoDB 全文索引文档 ID 和 FTS_DOC_ID 列

InnoDB 使用称为文档 ID（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”(全部大写)，如以下示例所示：
```
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”列定义为“AUTO_INCREMENT”列，但是“AUTO_INCREMENT”可以使加载数据更加容易】

如果您选择自己定义“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 自动创建它。
在 MySQL 5.7.13 之前，使用的最大“FTS_DOC_ID”值和新“FTS_DOC_ID”值之间的允许间隙为 10000。在 MySQL 5.7.13 及更高版本中，允许的间隙为 65535。
为避免重建表，删除全文索引时将保留“FTS_DOC_ID”列。

InnoDB 全文索引删除处理【！】

删除具有全文索引列的记录可能会导致辅助索引表中的许多小删除，从而使对这些表的并发访问成为争用点。为避免此问题，每当从索引表中删除记录时，已删除文档的文档 ID(DOC_ID)就会记录在特殊的“FTS_*_DELETED”表中，并且索引记录仍保留在全文索引中。返回查询结果之前，“FTS_*_DELETED”表中的信息用于过滤出已删除的文档 ID。这种设计的好处是删除快速且便宜。缺点是删除记录后不会立即减小索引的大小。

要删除已删除记录的全文索引条目，请在带有的索引表上运行“OPTIMIZE TABLE”以重建全文索引。

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_SYS_INDEXES”和“INNODB_SYS_TABLES”来查看全文索引和表的基本信息。