MySQL中，一种情况是新建一个表结构，先创建索引，再导入100w条数据，另一种情况是先导入100w条数据，再创建索引，哪种情况的效率高呢？答案是第2种，先导入100w条数据再创建索引的效率更高。具体原因隐藏在B+树的分裂机制、磁盘I/O模式以及排序算法的底层逻辑中。

一、B+树索引的基础结构

1. B+树的核心特点

• 平衡多路搜索树：所有叶子节点位于同一层，保证查询效率稳定。
• 节点容量固定：每个节点存储多个键值（如InnoDB默认页大小16KB）。
• 叶子节点链表：叶子节点通过指针串联，支持范围查询的高效遍历。

2. 索引维护的“分裂”与“合并”

• 节点分裂（Split）：当插入新键导致节点容量溢出时，节点会分裂为两个新节点，并向上递归调整父节点。
• 节点合并（Merge）：当删除操作导致节点键值过少时，可能触发节点合并，优化空间利用率。

二、逐条插入的代价：B+树的频繁分裂

假设向一个已创建索引的表中插入100万条数据：

• 随机键值插入：若插入的数据主键无序（如UUID），每次插入可能导致叶子节点随机分布，触发大量节点分裂。
• 级联分裂成本：分裂操作需要修改父节点、兄弟节点的指针，甚至可能逐层向上分裂到根节点。
• 磁盘I/O放大：每次分裂需要写入多个数据页（原节点、新节点、父节点等），导致随机写操作激增。

假设每个叶子节点容纳100条记录：

• 理想情况下，100万数据需要约10,000个叶子节点。
• 若主键无序，实际可能触发数千次节点分裂，导致叶子节点数量远大于10,000。
• 结果：磁盘I/O次数增加、索引碎片化、写入延迟显著上升。

三、批量构建索引的优化逻辑

1. 排序预处理：减少节点分裂

当数据导入完成后一次性创建索引，MySQL会先对数据排序（按索引键顺序），再批量构建B+树：

• 有序插入：排序后的数据可以按顺序填充B+树节点，最大限度减少分裂次数。
• 批量填充叶子节点：每个叶子节点一次性填满，无需频繁分裂。
• 自底向上构建：从叶子节点开始构建，逐层生成父节点，避免逐条插入时的递归调整。

2. 排序算法的优化

• 内存排序：若数据量小于sort_buffer_size，直接在内存中完成排序。
• 外部排序（归并排序）：对于海量数据，采用分块排序+多路归并，减少磁盘I/O次数。
• 100万数据排序后构建索引，可能仅需约10,000次顺序I/O（远低于随机插入的数十万次I/O）。

四、存储引擎的关键差异

1. InnoDB的聚簇索引

• 数据即索引：主键索引的叶子节点直接存储行数据。
• 优化策略：
  • 若主键为自增ID，数据按顺序写入，可减少聚簇索引的分裂。
  • 二级索引仍建议在数据导入后创建。

2. MyISAM的非聚簇索引

• 数据与索引分离：索引叶子节点存储数据行的物理地址。
• 优势：数据导入时无需维护索引，批量构建索引的优化效果更显著。

五、实战优化策略

1. 海量数据导入的标准流程

1. 删除非必要索引：仅保留主键（若必须）。
2. 调整参数：临时增大sort_buffer_size、innodb_buffer_pool_size。
3. 数据导入：使用LOAD DATA INFILE或批量INSERT。
4. 批量创建索引：按选择性从高到低依次创建索引。
5. 重建统计信息：执行ANALYZE TABLE更新索引统计信息。

2. 高级技巧

• 并行创建索引：MySQL 8.0+支持ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE，减少锁竞争。
• 利用外部工具：如Percona的pt-online-schema-change实现在线索引创建。

六、总结

• 边插入边维护索引的代价主要来自B+树的频繁分裂和随机I/O，而批量构建索引通过排序预处理和顺序写入，大幅降低了这两类开销。
• 适用于需要一次性导入海量数据的场景，如数据仓库ETL、日志表迁移、历史数据归档等。