InnoDB 磁盘架构

合集 - MySQL(18)

1.【汇总】 InnoDB 存储引擎2023-08-252.InnoDB 的介绍2023-08-143.Innodb 与 ACID 模型2023-03-224.MySQL事务执行过程2023-03-285.InnoDB 多版本控制2023-03-246.InnoDB 存储引擎架构2023-08-147.InnoDB 内存架构2023-03-27

8.InnoDB 磁盘架构2023-08-25

9.InnoDB 表空间2023-08-1710.InnoDB 双写缓冲区2023-08-1711.MySQL服务器日志2023-03-2812.InnoDB 的锁2023-08-1813.InnoDB 的事务模型2023-08-2314.InnoDB 中不同 SQL 语句设置的锁2023-08-2415.InnoDB 中的死锁2023-08-2416.InnoDB 磁盘 I/O 和 文件空间管理2023-08-2517.MySQL 查询执行计划2024-01-0518.【MySQL优化】索引失效的场景2023-09-14

收起

目录

• InnoDB 磁盘架构
  • 表
    • 创建 InnoDB 表
    • Row Formats
    • Primary Keys
  • 索引
    • 聚集索引与二级索引
      • 聚集索引如何加速查询
      • 二级索引与聚集索引的关系
    • InnoDB 索引的物理结构
    • 排序索引构建
      • 为未来索引增长保留 B-tree 页面空间
      • 排序索引构建和全文索引支持
      • 排序索引构建和压缩表
      • 排序索引构建和重做日志记录
      • 排序索引构建和优化器统计
      • InnoDB Full-Text 索引
      • 辅助索引表
      • 普通索引表
    • InnoDB 全文索引缓存
    • InnoDB 全文索引 DOC_ID 列和 FTS_DOC_ID 列
    • InnoDB 全文索引删除处理
    • InnoDB 全文索引事务处理
  • 表空间
  • 双写缓冲区
  • Doublewrite Buffer
  • Redo Log
  • Undo Log

InnoDB 磁盘架构

表

创建 InnoDB 表

建表语句：

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

默认情况下，InnoDB 表会被创建在 File-Per-Table 表空间下面。

如果变量 innodb_file_per_table = OFF，可以将 InnoDB 表创建到 InnoDB 的系统表空间下面。

Row Formats

InnoDB 表的 行格式（Row Formats）决定了行在磁盘上的物理存储方式。

InnoDB 支持四种行格式：REDUNDANT、COMPACT、DYNAMIC 和 COMPRESSED。

每种格式具有不同的存储特性，动态行格式是默认格式。

Primary Keys

最好为每个表定义一个主键，选择主键列时，选择具有以下特征的列：

• 最重要的查询引用的列；

• 永远不会留空的列；

• 永远不会有重复值的列；

• 一旦插入，列的值就很少改变。

例如，在包含人员信息的表中，我们不会在 (名字, 姓氏) 上创建主键，因为多个人可能具有相同的姓名，姓名列可能留空，有时人们会更改姓名。

由于存在如此多的约束，通常在没有一组明显的列可以用作主键时，我们就需要创建一个带有数字 ID 的新列来充当主键的全部或部分。

例如，我们可以声明一个自动递增的列，以便在插入行时，自动填充升序值：

mysql> CREATE TABLE t5 (id INT AUTO_INCREMENT, b CHAR (20), PRIMARY KEY (id));

主键可以由多于一列组成，任何自增的列都必须放在第一位：

mysql> CREATE TABLE t6 (id INT AUTO_INCREMENT, a INT, b CHAR (20), PRIMARY KEY (id,a));

索引

聚集索引与二级索引

每个 InnoDB 表都有一个特殊的索引，称为聚集索引（clustered index），用于存储行数据。通常，聚集索引与主键（primary key）同义。

为了从查询、插入和其他数据库操作中获得最佳性能，了解 InnoDB 如何使用聚集索引来优化常见的查找和 DML 操作非常重要。

聚集索引：

• 当我们在表上定义主键（primary key）时，InnoDB 会将其用作聚集索引。

  我们应该为每个表定义一个主键，如果没有逻辑唯一且非空的列或列集来作为主键，需要添加一个自动增量列，自动增量列值是唯一的，并且会在插入新行时自动添加。

• 如果没有为表定义 PRIMARY KEY，InnoDB 将使用第一个非空唯一索引列作为聚集索引。

• 如果表没有 PRIMARY KEY 或合适的 UNIQUE 索引，InnoDB 在包含 Row ID 值的合成列上，创建一个名为 GEN_CLUST_INDEX 的隐藏聚集索引。

  这些行按 InnoDB 分配的 Row ID 排序，Row ID 是一个 6 字节字段，随着新行的插入而单调增加。因此，按 Row ID 排序的行实际上是按插入顺序排列的。

聚集索引如何加速查询

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

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

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

如果主键较长，则二级索引会占用更多空间，因此主键较短是有利的。

InnoDB 索引的物理结构

除空间索引外，InnoDB 索引都是 B-tree 数据结构。

空间索引使用 R-trees，它是用于索引多维数据的专用数据结构。

索引记录存储在其 B-tree 或 R-tree 数据结构的叶子节点的页中，索引页大小默认是 16 KB，可以通过变量 innodb_page_size 设置。

当新记录插入到 InnoDB 聚集索引中时，InnoDB 会尝试保留 1/16 的页面，以供将来插入和更新索引记录：

• 如果索引记录顺序（升序或降序）插入，那么，生成的索引页大约占页面的 15/16，页面最后剩余 1 /16 会尝试被保留。

• 如果索引记录随机插入，那么，生成的索引页能利用到 1/2 ~ 15/16，页面剩余的部分会尝试被保留。

InnoDB 在创建或重建 B-tree 索引时，会执行批量加载，这种索引创建方法称为排序索引构建（sorted index build）。

变量 innodb_fill_factor 定义了在排序索引构建期间，每个 B-tree 页上填充的空间百分比，剩余空间保留用于将来的索引增长。

如果 InnoDB 索引页的填充因子低于 MERGE_THRESHOLD（如果未指定，默认值为 50%），InnoDB 会尝试收缩索引树以释放页面。

MERGE_THRESHOLD 设置适用于 B-tree 索引和 R-tree 索引。

排序索引构建

InnoDB 在创建或重建索引时，会执行批量加载，而不是一次插入一条索引记录，这种索引创建方法也称为排序索引构建（sorted index build）。

注意，空间索引不支持排序索引构建。

索引构建分为三个阶段：

• 在第一阶段，扫描聚集索引，生成索引条目，并将其添加到 排序缓冲区（sort buffer） 中，当排序缓冲区已满时，条目将被排序并写入临时中间文件。

• 在第二阶段，将排序缓冲区中的结果，写入临时中间文件，对文件中的所有条目执行并归排序（merge sort）。

• 在第三阶段，通过多线程的方式，将排序后的条目插入到 B-tree 中。

Sorted index builds use a “bottom-up” approach to building an index. With this approach, a reference to the right-most leaf page is held at all levels of the B-tree. The right-most leaf page at the necessary B-tree depth is allocated and entries are inserted according to their sorted order. Once a leaf page is full, a node pointer is appended to the parent page and a sibling leaf page is allocated for the next insert. This process continues until all entries are inserted, which may result in inserts up to the root level. When a sibling page is allocated, the reference to the previously pinned leaf page is released, and the newly allocated leaf page becomes the right-most leaf page and new default insert location.

排序索引构建使用“自下而上”建立索引的方法，通过这种方法，每层 B-tree 的节点都会保存对最右侧叶节点页面的引用。最右侧叶节点的页面会在 B-tree 的合适深度被分配，然后，根据条目的排序顺序插入条目。

如果一个叶节点的页面满了，节点指针就会追加到父节点的页面，并会为下一次插入分配一个同级的叶节点的页面。这个过程将持续进行，直到插入所有条目，这可能会导致插入到根级别。

当分配兄弟节点页面时，对先前固定的叶节点页面的引用将被释放，新分配的叶节点页面将成为最右侧的叶节点页面和新的默认插入位置。（类似链表的插入过程）

为未来索引增长保留 B-tree 页面空间

要为将来的索引增长留出空间，可以使用该 innodb_fill_factor 变量保留一定百分比的 B-tree 页面空间。

例如，设置 innodb_fill_factor 为 80，在排序索引构建期间保留 B-tree 页面中 20% 的空间。

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

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

全文索引（fulltext index）支持排序索引构建。

排序索引构建和压缩表

对于压缩表，排序后的索引条目会同时被追加到压缩的页面和未压缩的页面。当修改日志（代表压缩页上的可用空间）变满时，被压缩的页面将被重新压缩。

如果由于空间不足而压缩失败，页面将被分裂。通过排序索引构建，条目仅附加到未压缩的页面。当未压缩的页面已满时，就会对其进行压缩。

自适应填充用于确保在大多数情况下能压缩成功，但如果压缩失败，则会拆分页面并再次尝试压缩。此过程一直持续到压缩成功为止。

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

在排序索引构建期间，重做日志记录会被禁用，此时，通过检查点（checkpoint）可以确保索引构建期间，能够承受意外退出或失败，检查点强制将所有脏页写入磁盘。

在排序索引构建期间，页面清理线程（page cleaner thread）会定期收到信号并刷新脏页（dirty pages），这样就可以确保检查点操作被快速处理。

通常，当干净页面的数量低于设定阈值时，页面清理线程会刷新脏页面。对于排序索引构建，脏页会立即刷新，以减少检查点开销并并行化 I/O 和 CPU 活动。

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

排序索引构建可能会导致优化器统计信息，与排序后的索引生成的统计信息不同。统计数据的差异不会影响工作负载性能，因为用于填充索引的算法不同。

InnoDB Full-Text 索引

全文索引（full-text index） 是在基于文本的列（CHAR、VARCHAR 或 TEXT 列）上创建的，它可以加快对这些列中包含的数据的查询和 DML 操作。

InnoDB full-text indexes have an inverted index design. Inverted indexes store a list of words, and for each word, a list of documents that the word appears in. To support proximity search, position information for each word is also stored, as a byte offset.

InnoDB 全文索引采用倒排索引（inverted index） 设计，倒排索引会存储单词列表，同时，对于每个单词，倒排索引会存储该单词出现的文档列表。为了支持邻近搜索，每个单词的位置信息也被存储为字节偏移量。

辅助索引表

创建 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 |
+----------+----------------------------------------------------+-------+

倒排索引被划分为 6 个辅助索引表，支持并行索引创建。如上述示例所示，前 6 个索引表构成倒排索引，称为辅助索引表。当传入的文档被标记化后，各个单词及其位置信息和它关联的 DOC_ID 将被一起插入到这 6 个索引表中。然后，根据单词第一个字符的字符集权重排序，对单词进行完全排序，并在 6 个索引表中进行分区。

默认情况下，有两个线程会对单词进行标记化、排序，并将其与关联的数据插入到索引表中。其中，执行此工作的线程数，可以使用 innodb_ft_sort_pll_degree 变量进行配置，如果要在大型表上创建全文索引，可以考虑增加线程数。

辅助索引表名称前缀为 fts_，后缀为 index_#，每个辅助索引表通过辅助索引表名称中与索引表的 table_id 匹配的十六进制值与索引表关联。

例如，test/opening_lines 表的 table_id 是 327，其十六进制值为 0x147。如前面的示例所示，十六进制的 0x147 会出现在与 test/opening_lines 表关联的辅助索引表的名称中。

同时，表示全文索引的 index_id 的十六进制值也会出现在辅助索引表名称中。

例如，在辅助表名称 test/fts_0000000000000147_00000000000001c9_index_1 中，其中，0x1c9 = 457，我们可以在 INNODB_INDEXES 表中通过 index_id = 457 来查询 opening_lines 表上定义的索引。

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

如果主表（primary table）是在每个表文件的表空间中创建的，那么，辅助索引表就会存储在它们自己的表空间中；否则，辅助索引表存储在索引表所在的表空间中。

普通索引表

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

即使删除全文索引，公共索引表也会保留，当我们删除全文索引时，为该索引创建的 FTS_DOC_ID 列将被保留，因为删除 FTS_DOC_ID 列前需要重建前面的辅助索引表。

公共索引表需要用于管理 FTS_DOC_ID 列：

• fts_*_deleted 和 fts_*_deleted_cache

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

• fts_*_being_deleted 和 fts_*_being_deleted_cache

  包含已删除文档及其数据当前正在从全文索引中删除的文档的 document 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 使用 全文索引缓存（full-text index cache） 来临时缓存索引表中最近插入的行，这种内存中的缓存结构会一直保存插入的行，直到缓存满了之后，然后再将它们批量刷新（flush）到磁盘中，即辅助索引表中。我们可以通过 INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE 表，查看最近插入的行的标记化数据。

通过缓存和批量刷新的策略，避免了对辅助索引表的频繁更新，这可能会导致在繁忙的插入和更新期间出现并发访问问题。批处理技术还可以避免同一单词的多次插入，可以最大限度地减少重复条目，它将同一个单词的信息合并，然后作为单个条目刷新到磁盘，而不会单独刷新每个单词，这样就可以提高插入效率，同时保持辅助索引表尽可能小。

相关配置项：

• 变量 innodb_ft_cache_size ：配置每个表的全文索引缓存大小，它会影响全文索引缓存的刷新频率；

• 变量 innodb_ft_total_cache_size ：为所有表的 InnoDB 全文搜索索引缓存分配的总内存。

全文索引缓存保存的信息与辅助索引表中的信息相同，但是，全文索引缓存仅缓存最近插入的行的标记化数据。

查询时，已经刷新到辅助索引表（磁盘）中的数据，不会被带回到全文索引缓存中，此时，Innodb 会直接查询辅助索引表中的数据，并将辅助索引表中的结果与全文索引缓存的结果合并后返回。

InnoDB 全文索引 DOC_ID 列和 FTS_DOC_ID 列

InnoDB 使用 唯一文档标识符（unique document identifier，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 列，且不会提示警告。

与在已加载数据的表上创建全文索引相比，一次性定义 FTS_DOC_ID 列的成本要低，因此，最好在创建表时，指定全文索引列。

InnoDB 全文索引删除处理

删除具有全文索引列的记录可能会导致辅助索引表中出现大量小删除，从而使对这些表的并发访问成为争论点。

为了避免这个问题，每当 DOC_ID 从索引表中删除一条记录 FTS_*_DELETED 时，已删除文档的记录就会记录在一个特殊的表中，而索引记录仍保留在全文索引中。在返回查询结果之前，InnoDB 会利用FTS_*_DELETED 表中的信息，过滤掉已删除的 DOC_IDS 内容。

这种设计的好处是删除速度快且成本低；缺点是删除记录后索引的大小不会立即减小。

如果我们要彻底移除已删除记录的全文索引条目，需要配置 innodb_optimize_fulltext_only=ON ，然后，在索引表上运行 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 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 |
+----------+

表空间

双写缓冲区

Doublewrite Buffer

Redo Log

Undo Log

参考：

• 15.6.1 Tables