【译】MySQL InnoDB 排序索引构建

最新推荐文章于 2025-06-13 14:49:30 发布

jmysql

最新推荐文章于 2025-06-13 14:49:30 发布

阅读量276

点赞数

分类专栏： java 文章标签： mysql java 数据库

本文链接：https://blog.youkuaiyun.com/jmysql/article/details/125937345

版权

java 专栏收录该内容

232 篇文章

订阅专栏

本文深入探讨了MySQL InnoDB在5.7版本后改变的二级索引构建方式，从自顶向下转为自底向上的排序索引构建方法，以降低页面拆分和合并的成本。通过详细解释构建过程、用例和算法，帮助读者理解如何更有效地为数据插入预留空间，并在特定场景下使用排序索引构建。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

尚学堂给同学们带来全新的Java300集课程啦!java零基础小白自学Java必备优质教程_手把手图解学习Java，让学习成为一种享受_哔哩哔哩_bilibili

前言

不必了解 MySQL® 和Percona Server for MySQL 如何构建索引。但是，如果您了解处理过程，那么当您想为数据插入保留适当数量的空间时，它可能会有所帮助。从 MySQL 5.7 开始，开发人员改变了他们为 InnoDB 构建二级索引的方式，应用了自底向上而不是早期版本中使用的自顶向下方法。在这篇文章中，我将通过一个示例来说明如何构建 InnoDB 索引。最后，我将解释如何使用这种理解来为 innodb_fill_factor 设置一个合适的值。

索引构建过程

要在现有数据的表上建立索引，在 InnoDB 中有以下阶段：

读取阶段（从聚集索引读取并构建二级索引条目）
合并排序阶段
插入阶段（将排序后的记录插入二级索引）

在 5.6 版本之前，MySQL 通过一次插入一条记录来构建二级索引。这是一种“自上而下”的方法。对插入位置的搜索从根（顶部）开始，并到达相应的叶页（向下）。记录插入到光标指向的叶页上。在查找插入位置以及进行页面拆分和合并（在根级别和非根级别）方面，成本很高。你怎么知道发生了太多的页面拆分和合并？您可以在我的同事 Marco Tusa 较早的博客中了解这一点，请点击此处。

从 MySQL 5.7 开始，添加索引期间的插入阶段使用“排序索引构建”，也称为“批量加载索引”。在这种方法中，索引是“自下而上”构建的。即叶页面（底部）首先构建，然后非叶级别直到根。

用例

在这些情况下使用排序索引构建：

* ALTER TABLE t1 ADD INDEX (or CREATE INDEX)
* ALTER TABLE t1 ADD FULLTEXT INDEX
* ALTER TABLE t1 ADD COLUMN, ALGORITHM=INPLACE
* OPTIMIZE TABLE t1

对于最后两个用例，ALTER 创建一个中间表。中间表索引（主键索引和二级索引）是使用“排序索引构建”构建的。

算法

在 Level 0创建一个页面。同时创建一个指向该页面的游标。
使用 Level 0 的游标插入数据到页面，直到填满。
一旦页面已满，创建一个兄弟页面（不插入数据到兄弟页面）。
为当前满页创建一个节点指针（子页中的最小键），并在上一层（父页）插入一个节点指针。
在上层，检查游标是否已经定位。如果没有，请为该级别创建一个父页面和一个游标。
在父页面插入节点指针。
如果父页面已满，请重复步骤 3、4、5、6。
现在插入兄弟页面并使游标指向兄弟页面。
在所有插入的末尾，每个级别都有一个指向最右侧页面的游标。提交所有游标（意味着提交修改页面的小事务，释放所有锁存器）。

为简单起见，上述算法跳过了有关压缩页面和处理 BLOB（外部存储的 BLOB）的细节。