MySQL InnoDB INDEX

177 in 极客
从网络上收集整理的资料，如有侵权，请联系删除！

每个 InnoDB 表都有一个称为 「 聚簇索引 」 的特殊索引，通常情况下，这个聚簇索引就是 「 主键 」( primary key ) 。Innodb 使用它存储表中每一行的数据。

如果想要从 查询，插入 和其它数据库操作中获得最佳性能，那么我们就必须了解 InnoDB 如何使用 聚簇索引 来优化每个表的最常见检索和 DML 操作方式

（1）当我们在一个 Innodb 表上定义了一个主键，InnoDB 会默认的使用它作为聚簇索引。

使用 InnoDB 存储引擎时，建议为每个表都添加一个主键。如果该表没有一个逻辑唯一且非空列或列集合，那么可以添加一个带有 AUTO_INCREMENT 约束的自增列作为主键，InnoDB 会自动填充该列。

（2）如果某个 InnoDB 表并没有定义主键。那么 InnoDB 会查找第一个 「 唯一索引 」( UNIQUE Index ) ，因为唯一索引的所有键 ( key ) 都是 NOT ，因此可以用来作为聚簇索引

（3）如果某个 InnoDB 表既没有定义主键，也没有一个合适的唯一索引。InnoDB 会在内部生成一个名为 GEN_CLUST_INDEX 的隐式的聚簇索引

该聚簇索引的键 ( key ) 会包含一个自动为行生成的 ID 值 ( 行号 ) 。

该表中的所有行会按 InnoDB 分配给此类表中的行的 ID 排序。

行 ID 是一个 6 字节的字段，在插入新行时会单调自增。

因此，可以认为物理上的行保存顺序就是该行 ID 排序的排序顺序

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

通过聚簇索引访问行很快，因为索引搜索直接指向包含所有行数据页 ( data page )。

如果表很大，与那种索引页与数据页分离的 MyISAM 存储引擎相比， 聚簇索引体系结构通常可以节省磁盘 I/O 操作。

非聚簇索引和聚簇索引的关系

非聚簇索引，通常也称之为 「 二级索引 」 ( Secondary Indexes ) 或 「 辅助索引 」 ，一般是指聚簇索引之外的所有其它的索引。

在 InnoDB 中，每个辅助索引中的每条记录都会包含该行的主键列 ( 也就是聚簇索引的键 ) ，以及为辅助索引指定的列。InnoDB 使用此主键值来搜索聚簇索引中的行

如果主键很长，那么辅助索引就会占用更多空间，因此使用短主键是有利的，也是我们所推荐的。

聚簇索引和非聚簇索引的区别

1、首先，我们要认识到聚簇索引和非聚簇索引的划分依据是什么 ？

答案就是 InnoDB 会使用聚簇索索引来保存数据，而非聚簇索引的目的仅仅是加快查询速度

2、在第一点认知基础上，我们就可以知道

聚簇索引是唯一的，一个 InnoDB 表只有一个聚簇索引，而且一定会有一个聚簇索引，如果不存在，Innodb 存储引擎会自动添加一个
非聚簇所以可以有多个，而且只能由用户自己添加，InnoDB 默认并不会创建任何非聚簇索引。
3、非聚簇索引中一定包含了聚簇索引的列值，但反过来却不存在。

因此，使用非聚簇索引查询数据一定会用到聚簇索引，但反过来却不存在。

几乎所有的 Innodb 的索引都使用 B 树 数据结构，除了空间索引 ( spatial indexes ) 是个例外。

空间索引使用的是 R 树 数据结构 ，这是一种索引多维数据的专用数据结构。

但不管使用的是任何索引结构，索引记录只存储在 B 树 或 R树 数据结构的叶子节点中。索引页的默认大小为 16KB

当有新的记录插入到 InnoDB 聚簇索引中时，InnoDB 会尝试将页面的 1/16 留空，以便将来插入和更新索引记录

（1）如果按顺序 ( 升序或降序 ) 插入索引记录，则生成的索引页使用率大约为 15/16

（2）如果以随机顺序插入记录，则页面使用率只会在 1/2到 15/16 之间

InnoDB 在创建或重建 B 树索引时执行批量加载，这种索引创建方法称为 「 排序索引构建 」。我们可以使用配置项 innodb_fill_factor 重新设置在排序索引构建期间填充的每个 B 树页面上的空间利用率，那么剩余的空间会保留以待将来的索引增长。这个设置项我们一般称之为 「 填充因子 」

但是，即使我们把 innodb_fill_factor 配置项的值设置为 100 ，聚簇索引页仍然会保留出 1/16 的空间用于将来的索引增长。

另外，需要注意的是 「 空间索引不支持排序索引构建 」

如果 InnoDB 索引页的填充因子低于配置项 MERGE_THRESHOLD 的值 ( 如果未指定，默认情况下为 50% ) ，InnoDB 会尝试优化索引树以释放页面空间。

MERGE_THRESHOLD 同时适用于 B 树和 R 树数据结构

我们可以在初始化 MySQL 实例之前设置 innodb_page_size 配置选项来定义 MySQL 实例中所有 InnoDB 表空间的页面大小。InnoDB 表空间的大小一旦确定，也就是一旦 MySQL 实例初始化完成后，如果不重新初始化实例，则无法更改它。

目前支持的大小为 64KB，32KB，16KB ( 默认 ) ，8KB 和 4KB，对应于选项值 64k，32k，16k，8k 和 `4k 。

注意：使用特定 InnoDB 页面大小的 MySQL 实例不能使用来自不同页面大小的实例的数据文件或日志文件，也就是说两个不同数据页大小的 MySQL 实例的数据文件或日志文件是不能互通的。

常用面试题

1、 InnoDB 表使用什么数据结构，它们的数据都保存在哪里

使用的是 B 树 数据结构，它们的索引数据都保存在叶子节点中。

2、 InnoDB 的页大小一般是多少，把页大小提高为什么能提高 MySQL 的性能。

页大小决定了每次 IO 操作读取的数据大小，设置的越高当然每次读取的数据就越多，可以较少 IO 操作

结束语

其实吧，InnoDB 索引使用的应该是 B+ 树 ，虽然 B+ 树 也是 B 树 的一个变体，但总感觉怪怪的。

在上面有提到：InnoDB 在创建或重建 B 树索引时会执行批量加载，这种索引创建方法称为 「 排序索引构建 」

现在讲讲这个 「 排序索引构建 」，为什么要熟悉呢 ？ 因为它和我们的 InnoDB 创建和重建 B 树索引息息相关。也就和我们的 MySQL 性能优化息息相关了。

排序索引构建

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

索引构建有三个阶段：

1、 在第一阶段，扫描 聚簇索引，生成索引条目 ( entries ) 并将其添加到排序缓冲区。当排序缓冲区满了，索引条目将被排序并写入临时中间文件。这个过程也称为 「 运行 」 ( run )。

2、 在第二阶段，将一个或多个 「 运行 」 写入临时中间文件，并对文件中的所有条目执行合并排序

3、 在第三个也是最后一个阶段，已排序的条目将插入到 B 树中

自顶向下的索引构建方法

在引入引入排序索引构建之前，索引的生成和重建方式是使用插入 API 一次只将一个索引条目插入到 B 树。这种方式会创建并打开 B 树游标以查找插入位置，然后使用 「 乐观插入 」 ( optimistic insert ) 将条目插入 B 树页面。

如果由于页面已满而导致插入失败，则将执行 「 悲观插入 」 ( pessimistic insert ) ，这种方式会创建并打开 B 树游标并根据需要拆分和合并 B 树节点以查找条目的空间。这种 「 自上而下 」的构建索引方法的缺点是搜索插入位置的成本太高且B 树节点需要频繁的拆分和合并

自下而上的排序构建方法

排序索引构建使用 「 自下而上 」 方法来构建索引。使用这种方式，B 树的所有级别始终都会持有最右边的叶子页面的引用。在插入索引时，会分配必要的 B 树深度的最右侧叶页，并根据其排序顺序插入条目。一旦叶子页面已满，节点指针将附加到父页面，并为下一个插入分配一个兄弟叶页面。这个过程会一直重复，直到插入所有条目，这可能导致插入到根级别。当分配兄弟页面时，将释放对先前固定的叶子页面的引用，并且新分配的叶子页面将成为最右侧的叶子页面和新的默认插入位置

为未来索引的增长保留 B 树页面空间

如果想要为未来索引的增长留出空间，可以使用 innodb_fill_factor 配置选项来设置 B 树页面空间要保留的百分比。例如，将 innodb_fill_factor 设置为 80 可在排序索引构建期间留出 B 树页面中 20％ 的空间。此设置同时使用于 B 树叶子节点页和非叶子节点页。但它不适用于用于 TEXT 或 BLOB 条目的外部页。实际使用过程中发现，保留的空间大小可能与配置的不完全相同，因为 innodb_fill_factor 值是建议大小而不是强制限制大小。

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

全文索引支持使用 「 排序索引构建 」，而在此之前，全文索引的构建方式为 「 使用 SQL 将条目插入到全文索引中 」

排序索引构建和压缩表

对于压缩表 ( compressed tables )，先前的索引创建方法将条目同时添加到压缩和未压缩页面的末尾。当修改日志 ( 表示压缩页上的可用空间 ) 变满时，压缩页将被重新压缩。如果压缩由于空间不足而导致压缩失败，则该页将被拆分。

对于排序索引构建，条目仅仅只添加到未压缩的页的末尾。当未压缩的页变满时，它才会被压缩。在大多数情况会使用自适应填充 ( adaptive padding ) 来确保压缩成功，但如果压缩失败，则会拆分该页并再次尝试压缩。此过程将重复进行，知道压缩成功。

排序索引构建和重做日志

在排序索引构建期间会禁用重做日志。取而代之的是使用一个检查点 ( checkpoint ) 确保排序索引构建能够承受崩溃或故障。这个检查点会强制将所有脏页写入磁盘。在排序索引构建期间，会定期发信号通知页面清理程序线程刷新脏页以确保可以快速处理检查点操作。默认的，当清洁页面的数量低于设定的阈值时，页面清理器线程会刷新脏页面。对于已排序的索引构建，会立即刷新脏页以减少检查点开销并并行 I/O 和 CPU 活动

排序索引构建和性能优化统计

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