MySQL索引创建过程深度剖析

MySQL索引创建过程深度剖析：从排序构建到在线DDL的底层奥秘

索引是MySQL性能优化的核心，但创建索引时，数据库内部究竟发生了什么？为什么大表创建索引可能导致系统抖动？Online DDL真的完全“在线”吗？本文从源码与数据结构层面，带你深度理解MySQL创建索引的完整流程与底层机制。

 

一、引言：创建索引，不只是“建个结构”

 

当你执行一条简单的建索引语句：

CREATE INDEX idx_name ON users (name);

或者使用ALTER TABLE添加索引，MySQL需要完成一系列复杂的操作：解析SQL、校验权限、扫描全表、排序数据、构建B+树、更新元数据……更重要的是，在InnoDB引擎下，如何平衡索引构建效率与并发读写需求，是数据库设计者面临的重要挑战。

本文将以InnoDB存储引擎为例，深入讲解索引创建的全流程，并探讨页分裂、Change Buffer等底层机制如何影响性能。

 

二、索引创建的整体流程（宏观视角）

下图展示了创建索引的主要阶段：

 

三、阶段一：语法解析与权限验证

MySQL接收到CREATE INDEX或ALTER TABLE ADD INDEX命令后：

• 解析器检查语法正确性。

• 预处理器验证索引列是否存在，数据类型是否支持索引（如BLOB/TEXT必须指定前缀长度），是否已有同名索引。

• 权限检查：用户是否拥有ALTER权限。

如果通过，进入优化器与执行器阶段。

四、阶段二：选择索引创建算法（Online DDL）

在MySQL 5.6及以后，InnoDB支持Online DDL，允许在创建索引时同时执行DML操作（INSERT/UPDATE/DELETE）。根据算法不同，分为：

算法	是否允许并发DML	表拷贝	适用场景
COPY	❌ 不允许	需要	老版本或特定DDL（如修改列类型）
INPLACE	✅ 允许（部分）	不需要	大部分索引创建/删除
INSTANT	✅ 允许	不需要	8.0+ 仅支持在表末尾加列等少量操作

 对于创建二级索引，INPLACE算法是默认且最优的。它使用排序构建法（Sorted Index Build），避免全表拷贝。

五、阶段三：排序构建B+树（核心步骤）

当使用INPLACE算法创建普通索引时，InnoDB执行以下步骤：

1. 扫描表收集索引键值

• 开启一个事务，对表加意向共享锁（IS），防止其他DDL。

• 扫描聚簇索引（主键），读取需要构建索引的列数据。

• 为了不阻塞DML，InnoDB通过Row Logging记录扫描过程中的增量修改（类似在线备份）。

2. 排序与构建索引页

• 将收集到的（索引键值 + 主键值）临时存储到排序缓冲区（内存中）。如果数据量超过innodb_sort_buffer_size，则会使用磁盘临时文件。

• 使用优先队列排序（或快排）对键值对进行全局排序。

• 将排好序的键值对按照页大小（16KB） 划分为多个块，每个块内的数据已经有序，然后直接构建成B+树的叶子节点和内节点，写入临时文件（或直接在表空间中创建新的索引树）。

3. 应用增量日志

• 在扫描表期间，如果有并发的DML操作，InnoDB会将这些操作记录在一个内部的变更缓冲区（row log） 中。

• 当B+树基本构建完成后，再将这个日志重放到新索引上，保证索引数据与表数据最终一致。

为什么快？ 排序构建法避免了传统的插入构建（逐行插入索引页）导致的频繁页分裂和随机I/O，大幅提升了大表索引创建效率。

六、阶段四：更新数据字典与元数据

• 索引创建完成后，InnoDB将索引信息写入数据字典表（mysql库中的innodb_indexes等）。

• 更新表元数据（.frm或8.0后的序列化字典信息）。

• 提交内部事务，释放意向锁。

此时，索引已经对会话可见。

七、阶段五：锁定与阻塞分析

虽然Online DDL允许并发DML，但在某些阶段仍然会加锁：

 

阶段	所加锁类型	对DML影响
扫描表准备阶段	意向共享锁（IS）	不阻塞读写，但阻塞其他DDL
应用增量日志阶段	共享排他锁（允许DML，禁止DDL）	DML可执行，但无法同时创建索引
提交元数据阶段	元数据锁（MDL）	极短时间内阻塞所有操作

因此，虽然叫“在线”，但不能100%无感知。建议在业务低峰期执行大表索引创建。

八、底层机制深究：页分裂与页合并

当通过传统方式（非排序构建）逐行插入索引时，B+树可能发生页分裂。即使使用排序构建，在应用增量日志阶段，也可能因为新插入的行导致索引页分裂。

• 页分裂：当索引页写满后，再插入新键值，InnoDB会申请一个新页，并将原页的一半记录移动到新页，调整父节点指针。页分裂开销较大，可能导致索引树暂时不均衡。

• 页合并：当删除键值导致页内记录过少时，InnoDB会尝试与相邻页合并，释放空间。

创建索引时，由于数据是有序的，通常不会发生大量页分裂。但如果在构建过程中有并发的插入操作，可能会触发页分裂，导致性能下降。

九、Change Buffer：加速二级索引插入

Change Buffer是InnoDB的缓存机制：当二级索引页不在缓冲池中时，对索引的修改（插入、更新、删除）会先记录到Change Buffer中，后续读操作时再合并（merge）回索引页。

在创建索引期间，如果表并不繁忙，Change Buffer可以暂缓对新索引页的直接修改，减少随机I/O。但也要注意，如果Change Buffer占用空间过大，后台合并线程可能会拖慢整体性能。

 

十、实践建议：如何高效创建索引

1. 优先使用Online DDL（MySQL 5.6+默认启用），避免全表拷贝。

2. 调整相关参数：

   • innodb_sort_buffer_size：排序缓冲区大小，建议32MB~256MB，根据服务器内存调整。

   • innodb_online_alter_log_max_size：在线DDL期间增量日志的最大大小，超出会报错并回滚，建议设置足够大（例如2GB）。

   • innodb_change_buffer_max_size：如果表写入频繁，适当调高Change Buffer。

3. 选择低峰期执行：虽然Online DDL允许读写，但仍然会有短暂锁表，且会消耗大量I/O和CPU。

4. 监控进度：使用SHOW PROCESSLIST或performance_schema观察DDL状态，MySQL 8.0还提供了INNODB_ALTER_TABLE_PROGRESS表。

5. 分批次创建索引：如果一张表需要创建多个索引，尽量一次性通过ALTER TABLE ADD INDEX idx1 (...), ADD INDEX idx2 (...)完成，减少重复扫描表的次数。

十一、总结

 

阶段	核心动作	关键技术
解析验证	语法、权限、列存在性	-
算法选择	COPY / INPLACE / INSTANT	Online DDL
排序构建	扫描聚簇索引、排序、生成B+树	排序索引构建（Sorted Index Build）
增量应用	应用row log	在线变更日志
元数据更新	更新数据字典	事务性DDL
清理	释放临时资源	-

一句话总结：MySQL创建索引并非简单的“添加一个数据结构”，而是一个涉及全表扫描、排序、增量同步、元数据更新和资源管理的高效工程。理解这一过程，有助于你在大表维护时做出更合理的决策，避免生产环境抖动。

如果觉得本文对你有帮助，欢迎点赞、收藏、转发！
关注我，获取更多MySQL内核与性能优化深度内容。