搜云库技术团队
文章永久连接：https://tech.souyunku.com/?p=5818
在上一章节 「 MySQL InnoDB 索引的物理保存结构 」 中我们有提到：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 活动
排序索引构建和性能优化统计
排序索引构建可能会导致性能优化统计信息与以前的索引创建方法生成的统计信息不同，而这种统计数据的差异 ( 预计不会影响工作负载性能 ) 是由于使用了不同的填充索引的算法而引起的。
干货推荐
附录：MySQL InnoDB 面试题：系列文章
- 一、MySQL 面试题 InnoDB 那些事 – 开篇
- 二、MySQL 面试题 InnoDB AUTO_INCREMENT ( 上 )
- 三、MySQL 面试题 InnoDB AUTO_INCREMENT ( 中 )
- 四、MySQL 面试题 InnoDB AUTO_INCREMENT ( 下 )
- 五、MySQL 面试题 InnoDB AUTO_INCREMENT ( 末 )
- 六、MySQL 面试题 InnoDB 聚簇索引和非聚簇索引
- 七、MySQL 面试题 InnoDB 索引的物理保存结构
- 【当前读到】八、MySQL 面试题 InnoDB 排序索引构建
- 九、MySQL 面试题 InnoDB 中的全文检索索引 ( 上 )
- 十、MySQL 面试题 InnoDB 中的全文检索索引 ( 中 )
- 十一、MySQL 面试题 InnoDB 中的全文索引 ( 下 )
- 十二、MySQL 面试题 InnoDB 创建 InnoDB 表
