InnoDB磁盘结构-索引

最新推荐文章于 2022-06-24 16:19:53 发布

举杯酌酒望明月

最新推荐文章于 2022-06-24 16:19:53 发布

阅读量292

点赞数

分类专栏： # InnoDB存储引擎

本文链接：https://blog.csdn.net/wangchaofan1992/article/details/103530669

版权

InnoDB存储引擎专栏收录该内容

7 篇文章 0 订阅

订阅专栏

聚集索引和二级索引

每个InnoDB表都有一个特殊的索引，称为聚簇索引clustered index ，用于存储行数据。通常，聚簇索引与 primary key同义。为了从查询、插入和其他数据库操作中获得最佳性能，您必须了解InnoDB如何使用聚集索引来优化每个表最常见的查找和DML操作。

在表上定义主键时，InnoDB将其用作聚集索引。为创建的每个表定义主键。如果没有逻辑唯一且非空的列或列集，请添加一个新的自动递增列，其值将自动填充。
如果不为表定义主键，MySQL会查找第一个唯一索引，其中所有键列都不为空，InnoDB会将其用作聚集索引。
如果表没有主键或合适的唯一索引，InnoDB会在包含行ID值的合成列上内部生成一个名为GEN_CLUST_index的隐藏聚集索引。这些行按InnoDB分配给此类表中的行的ID排序。行ID是一个6字节的字段，随着新行的插入而单调增加。因此，按行ID排序的行实际上是按插入顺序排列的。

聚集索引如何加快查询

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

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

除聚集索引以外的所有索引都称为辅助索引。在InnoDB中，二级索引中的每条记录都包含行的主键列，以及为二级索引指定的列。InnoDB使用这个主键值来搜索聚集索引中的行。

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

有关利用InnoDB集群索引和二级索引的指南，请参阅 Section 8.3, “Optimization and Indexes”。

InnoDB索引的物理结构

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

当新的记录被插入到InnoDB聚集索引中时，InnoDB试图保留页面的1/16空间，以便将来插入和更新索引记录。如果按顺序（升序或降序）插入索引记录，则生成的索引页大约为15/16。如果以随机顺序插入记录，则页面从1/2到15/16。

InnoDB在创建或重建B树索引时执行大容量加载。这种索引创建方法称为排序索引构建。innodb_fill_factor configuration选项定义了在排序索引构建期间填充的每个B树页面上的空间百分比，剩余空间保留用于将来索引增长。空间索引不支持排序索引生成。. For more information, see Section 14.6.2.3, “Sorted Index Builds”. 如果innodb_fill_factor设置为100，则聚集索引页中的1/16空间可供将来索引增长使用。

如果InnoDB索引页的填充因子低于MERGE_THRESHOLD（如果未指定，默认为50%），InnoDB将尝试收缩索引树以释放该页。MERGE_THRESHOLD设置同时适用于B-tree和R-tree索引。 For more information, see Section 14.8.12, “Configuring the Merge Threshold for Index Pages”.

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

MySQL 5.7增加了对32KB和64KB页面大小的支持。有关更多信息，请参阅innodb_page_size文档。

使用特定InnoDB页面大小的MySQL实例不能使用来自使用不同页面大小的实例的数据文件或日志文件。

排序索引构建

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

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

在引入排序索引构建之前，使用insertapi一次将索引条目插入到B树一条记录中。此方法涉及打开一个B树光标以找到插入位置，然后使用乐观插入将条目插入到B树页中。如果由于页面已满而导致插入失败，则将执行悲观插入，这涉及打开B树光标，并根据需要拆分和合并B树节点以查找条目空间。这种自顶向下的索引方法的缺点是寻找插入位置的代价，以及B树节点的不断分裂和合并。

排序索引构建使用“自下而上”的方法来构建索引。使用这种方法，对最右边的叶页的引用将保存在B树的所有级别。在必要的B树深度处分配最右边的叶页，并根据其排序顺序插入条目。一旦叶页已满，节点指针将附加到父页，并为下一次插入分配同级叶页。此过程将继续，直到插入所有条目，这可能导致插入到根级别。分配同级页时，将释放对以前固定的叶页的引用，新分配的叶页将成为最右边的叶页和新的默认插入位置。

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

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

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

全文索引 fulltext indexes支持排序索引构建。以前，SQL是用于将条目插入全文索引的。

排序索引生成和压缩表

对于压缩表，以前的索引创建方法将条目追加到压缩页和未压缩页。当修改日志（表示压缩页上的可用空间）已满时，将重新压缩压缩页。如果压缩由于空间不足而失败，则将拆分页。对于排序索引生成，条目仅附加到未压缩的页。当未压缩的页变满时，它将被压缩。自适应填充用于确保在大多数情况下压缩成功，但如果压缩失败，则拆分页面并再次尝试压缩。此过程将继续，直到压缩成功。. For more information about compression of B-Tree pages, see Section 14.9.1.5, “How Compression Works for InnoDB Tables”.

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

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

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

排序的索引构建可能会导致 optimizer 统计信息与以前的索引创建方法所生成的统计信息不同。统计信息的差异（预计不会影响工作负载性能）是由于用于填充索引的算法不同。

InnoDB全文索引

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

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

全文搜索是使用 MATCH() ... AGAINST语法。有关用法信息，请参阅 see Section 12.9, “Full-Text Search Functions”.

InnoDB FULLTEXT 本节中的以下主题描述了索引：

InnoDB全文索引设计

InnoDB全文索引采用反向索引设计。反向索引存储一个单词列表，对于每个单词，则存储该单词出现在其中的文档列表。为了支持邻近搜索，每个单词的位置信息也被存储为字节偏移量。

InnoDB全文索引表

创建InnoDB FULLTEXT索引时，将创建一组索引表，如以下示例所示：

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

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

将反向索引划分为六个辅助索引表以支持并行索引创建。默认情况下，两个线程将单词和相关数据标记、排序和插入索引表。可以使用innodb-ft-u-sort-pll-u-degree选项配置线程数。在大型表上创建全文索引时，请考虑增加线程数。

辅助索引表名称用 FTS_ 前缀， INDEX_*后缀。每个索引表通过索引表名称中与索引表的表id匹配的十六进制值与索引表相关联。例如，test/opening_lines表的table_id为327，十六进制值为0x147。如前例所示，“147”十六进制值出现在与test/opening_lines表关联的索引表的名称中。

十六进制值代表FULLTEXT索引的index_id也出现在辅助索引表名称中。例如，在辅助表名test/FTS_0000000000000147_00000000000001c9_INDEX_1,，十六进制值1C9具有457的十进制值。在opening_lines表（idx）上定义的索引可以通过查询INFORMATION_SCHEMA.INNODB_SYS_INDEXES表来识别该值（457）。

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 tablespace中创建的，则索引表存储在它们自己的表空间中。

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

即使删除全文索引，也可以保留公共辅助表。删除全文索引时，将保留为该索引创建的FTS_DOC_ID列，因为删除FTS_DOC_ID列将需要重建表。需要通用的腋下表来管理FTS_DOC_ID列。

FTS_*_DELETED and FTS_*_DELETED_CACHE

包含已删除但其数据尚未从全文索引中删除的文档的文档ID（文档ID）。 FTS_*_DELETED_CACHE 是 FTS_*_DELETED 表的内存版

FTS_*_BEING_DELETED and FTS_*_BEING_DELETED_CACHE

包含已删除且其数据当前正在从全文索引中删除的文档的文档ID（文档ID）。FTS_*_BEING_DELETED_CACHE table是 FTS_*_BEING_DELETED table的内存版.

FTS_*_CONFIG

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

InnoDB全文索引缓存

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

缓存和批处理刷新行为避免频繁更新辅助索引表，这可能会导致在繁忙插入和更新时间期间并发访问问题。批处理技术还避免了对同一个单词的多个插入，并将重复项最小化。代替逐个刷新每个单词，将同一单词的插入合并并刷新为单个条目，提高插入效率，同时保持辅助索引表尽可能小。

innodb_ft_cache_size变量用于配置全文索引缓存大小（基于每个表），这会影响刷新全文索引缓存的频率。还可以使用innodb-ft-u-total-cache-size选项为给定实例中的所有表定义全局全文索引缓存大小限制。

全文索引Cache存储与辅助索引表相同的信息。但是，全文索引缓存仅缓存最近插入行的标记化数据。已被刷新到磁盘（到全文辅助表）的数据在查询时不会返回到全文索引缓存中。辅助索引表中的数据是直接查询的，辅助索引表的结果与全文索引缓存的结果在返回之前合并。

InnoDB全文索引文档ID和FTS_DOC_ID列

InnoDB使用称为文档ID（DOC_ID）的唯一文档标识符将全文索引中的单词映射到出现单词的文档记录。映射需要索引表上的FTS_DOC_ID列。如果没有定义FTS_DOC_ID列，InnoDB会在创建全文索引时自动添加一个隐藏的FTS_DOC_ID列。下面的示例演示此行为。

下表定义不包括FTS U DOC U 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 full text 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列的表时，将返回相同的警告。如果在创建表时创建全文索引，并且没有指定FTS_DOC_ID列，InnoDB会在没有警告的情况下添加一个隐藏的FTS_DOC_ID列。

在创建表时定义FTS_DOC_ID列比在已加载数据的表上创建全文索引要便宜。如果在加载数据之前在表上定义了FTS_DOC_ID列，则无需重新生成表及其索引即可添加新列。如果您不关心CREATE FULLTEXT INDEX的性能，请省略FTS_DOC_ID列，让InnoDB为您创建它。InnoDB在FTS-DOC-ID列上创建一个隐藏的FTS-DOC-ID列和一个唯一的索引（FTS-DOC-ID-u索引）。如果要创建自己的FTS_DOC_ID列，则必须将该列定义为BIGINT UNSIGNED NOT NULL并命名为FTS_DOC_ID（全大写），如下例所示：

Note

FTS_DOC_ID列不需要定义为AUTO_INCREMENT列，但是AUTO_INCREMENT可以简化数据加载。

mysql> CREATE TABLE opening_lines (
       FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       opening_line TEXT(500),
       author VARCHAR(200),
       title VARCHAR(200)
       ) ENGINE=InnoDB;

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

或者，您可以在FTS-DOC-ID列上创建所需的唯一 FTS_DOC_ID_INDEX（全大写）。

mysql> CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on opening_lines(FTS_DOC_ID);

如果不创建 FTS_DOC_ID_INDEX，InnoDB会自动创建它。

在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挈u DELETED表中的信息筛选出已删除的文档ID。这种设计的好处是删除速度快、成本低。缺点是删除记录后索引的大小不会立即减小。要删除已删除记录的全文索引项，请在 innodb_optimize_fulltext_only=ON的索引表上运行OPTIMIZE TABLE以重新生成全文索引。 For more information, see Optimizing InnoDB Full-Text Indexes.

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全文索引

您可以通过查询以下信息架构表来监视和检查InnoDB全文索引的特殊文本处理方面：

您还可以通过查询INNODB_SYS_INDEXES 和 INNODB_SYS_TABLES来查看全文索引和表的基本信息。

For more information, see Section 14.16.4, “InnoDB INFORMATION_SCHEMA FULLTEXT Index Tables”.

举杯酌酒望明月

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
InnoDB磁盘结构-索引

目录聚集索引和二级索引InnoDB索引的物理结构排序索引构建InnoDB全文索引聚集索引和二级索引每个InnoDB表都有一个特殊的索引，称为聚簇索引clustered index，用于存储行数据。通常，聚簇索引与primary key同义。为了从查询、插入和其他数据库操作中获得最佳性能，您必须了解InnoDB如何使用聚集索引来优化每个表最常见的查找和DML操...
复制链接

扫一扫