参考官方文档:
https://dev.mysql.com/doc/refman/5.7/en/innodb-indexes.html


14.8.2.1 聚簇和二级索引


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

• 当你在你的表上定义PRIMARY KEY时，InnoDB将它用作聚簇索引。 为您创建的每个表定义一个主键。 如果没有逻辑唯一且非空的列或一组列，请添加一个新的自动增量列，其值将自动填入。
• 如果您没有为您的表定义PRIMARY KEY，MySQL会查找第一个UNIQUE索引，其中所有键列都不为NULL，InnoDB将其用作聚簇索引。
• 如果该表没有PRIMARY KEY或合适的UNIQUE索引，InnoDB会在包含行ID值的合成列内部生成一个名为GEN_CLUST_INDEX的隐藏聚簇索引。 这些行按照InnoDB分配给此表中的行的ID进行排序。 行ID是一个6字节的字段，随着新行的插入而单调递增。 因此，由行ID排序的行在物理上取决于插入顺序。

聚簇索引如何加速查询


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


二级指标如何与聚集指数相关联


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


如果主键很长，二级索引使用更多空间，所以有一个短主键是有利的。


有关利用InnoDB集群索引和二级索引的指导原则，请参见第8.3节“优化和索引”。



14.8.2.2 innodDB 索引的物理结构


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


将新记录插入InnoDB聚集索引时，InnoDB会尝试将页面的1/16空闲以供将来插入和更新索引记录。 如果索引记录以连续顺序（升序或降序）插入，则生成的索引页大约为15/16。 如果记录以随机顺序插入，则页面从1/2到15/16。


InnoDB在创建或重建B-tree索引时执行批量加载。 这种索引创建方法被称为排序索引构建。 innodb_fill_factor配置选项定义了在排序索引构建期间填充的每个B树页面上的空间百分比，剩余空间用于未来索引增长。 空间索引不支持排序索引构建。 有关更多信息，请参见第14.8.2.3节“分类索引构建”。 Innodb_fill_factor设置为100（默认值），可以使聚集索引页面的1/16空间free 以用于未来的索引增长。


您可以通过在初始化MySQL实例之前设置innodb_page_size配置选项来为MySQL实例中的所有InnoDB表空间定义页面大小。 一旦定义了实例的页面大小，如果不重新初始化实例，则无法对其进行更改。 支持的大小为64KB，32KB，16KB（默认），8KB和4KB，对应于选项值64k，32k，16k，8k和4k。


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


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




14.8.2.3 排序索引构建


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


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


在引入排序索引构建之前，使用插入API一次将索引条目插入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值被解释为提示而非硬限制。


分类索引构建和全文索引支持


全文索引支持排序索引构建。 以前，SQL被用来将条目插入到全文索引中。




排序索引构建和压缩表


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



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


重排日志在排序索引构建过程中被禁用。 相反，有一个检查点可确保索引构建能够承受崩溃或故障。 检查点强制将所有脏页写入磁盘。 在有序索引构建期间，页面清理器线程会定期发送信号以刷新脏页面，以确保可以快速处理检查点操作。 通常，当干净页面数量低于设定阈值时，页面清理器线程会刷新脏页面。 对于有序的索引构建，会立即刷新脏页以减少检查点开销并且平行化I / O和CPU活动。




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


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



14.8.2.4 InnoDB FULLTEXT 索引




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




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




使用MATCH（）... AGAINST语法执行全文搜索




InnoDB全文索引设计


InnoDB FULLTEXT索引具有倒排索引设计。 倒排索引存储单词列表，并为每个单词列出单词出现的文档列表。为支持邻近搜索，每个单词的位置信息也以字节偏移量的形式存储。


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/%';

前六个表表示倒排索引，并被称为辅助索引表。 当输入文档被标记化时，单个词（也称为“tokens”）与位置信息和相关联的文档ID（DOC_ID）一起被插入到索引表中。 基于字的第一个字符的字符集分类权重，单词被完全排序并在六个索引表中分区。




倒排索引被分成六个辅助索引表以支持并行索引创建。 默认情况下，两个线程为标记，排序并将单词和关联数据插入到索引表中。 线程数可以使用innodb_ft_sort_pll_degree选项进行配置。 考虑在大型表上创建FULLTEXT索引时增加线程数。




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




表示FULLTEXT索引的index_id的十六进制值也出现在辅助索引表名称中。 例如，在辅助表名称test / FTS_0000000000000147_00000000000001c9_INDEX_1中，十六进制值1c9的十进制值为457.可以通过查询INFORMATION_SCHEMA.INNODB_SYS_INDEXES表中的值（457）来标识在opens_lines表（idx）上定义的索引。


mysql> SELECT index_id, name, table_id, space from INFORMATION_SCHEMA.INNODB_SYS_INDEXES WHERE index_id=45

如果主表是在每个表-文件表空间中创建的，则索引表将存储在其自己的表空间中。


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



即使全文索引被删除，通用辅助表也会被保留。 删除全文索引时，保留为索引创建的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

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




InnoDB 全文索引 cache


插入文档时，将对其进行标记，并将单个词和关联的数据插入到FULLTEXT索引中。 即使对于小文档，该过程也可能导致大量小插入到辅助索引表中，从而使这些表的并发访问成为争用的焦点。 为了避免这个问题，InnoDB使用FULLTEXT索引缓存临时缓存最近插入的行的索引表插入。 这个内存中的缓存结构持有插入，直到缓存满了，然后将它们批量刷新到磁盘（到辅助索引表）。 您可以查询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;



使用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（全部大写），如下例所示：



注意：
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 _ * _ DELETED表中的信息用于过滤掉已删除的文档ID。 这种设计的好处是删除速度快而且价格低廉。 缺点是在删除记录后索引的大小不会立即减小。 要删除已删除记录的全文索引条目，请使用innodb_optimize_fulltext_only = ON在索引表上运行OPTIMIZE TABLE以重建全文索引。




InnoDB全文索引事务处理


由于其缓存和批处理行为，InnoDB FULLTEXT索引具有特殊的事务处理特性。 具体而言，FULLTEXT索引上的更新和插入在事务提交时处理，这意味着FULLTEXT搜索只能查看已提交的数据。 以下示例演示了此行为。 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> 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 FULLTEXT索引的特殊文本处理方面：

• 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查看FULLTEXT索引和表的基本信息。