MySQL 创建辅助索引的底层过程
目录
执行 ALTER 创建索引的时候， MySQL 的处理过程是怎么样的呢？
MySQL 5.5 之前（默认使用 MyISAM 作为存储引擎）
过程：
- 取旧表 ALTER TABLE 后的结构，以该结构创建临时表
- 原表加写锁，原表数据导入到临时表
- 删除原表
- 临时表重命名为原表的表名
缺点：
- 原表是大表时，速度很慢
- 过程中服务不可用
MySQL 5.5 开始（默认使用 InnoDB 作为存储引擎）
过程：
- 对表加共享锁
- 扫描表，生成辅助索引 B+Tree
- 解锁
解决了哪些问题？
- 速度慢的问题
- 索引创建过程中无法读的问题
没有解决哪些问题？
- 索引创建过程中无法写的问题
带来了哪些问题？
无
Online Schema Change（Facebook）
过程：
- 创建和原表结构一样的新表
- 确保原表有主键，并且不存在外键和触发器。记录所有索引信息。
- 创建 deltas 表
- 在原表中创建触发器，将 INSERT、UPDATE、DELETE 操作记录到 deltas 表
不是把数据迁移过取。类似于 Redis 的 AOF。 - 开始 OSC 操作的事务
- 导出原表数据 做法：对数据分片，每次只到处部分数据，减少原表锁定时间
- 删除新表所有辅助索引
- 数据导入新表
- 将记录在 deltas 表的操作重放一次，目标表为新表
- 对新表重建辅助索引
- 重放 deltas 表在重建辅助索引过程中新记录的操作
- 交换新旧表的名字
- 删除旧表和 deltas 表
解决了哪些问题？
- 索引创建过程中无法写的问题
- 数据导出对资源的占用问题
没有解决哪些问题？
无
带来了哪些问题？
- 原表必须存在主键
- 原表不能存在外键和触发器
Online DDL （MySQL 5.6）
四种关于锁的选择：
- NONE
不对原表加锁 - SHARE
对原表加共享锁 - EXCLUSIVE
对原表加排他锁 - DEFAULT
按顺序判断能否使用 NONE，SHARE，EXCLUSIVE
NONE 原理（无法过程描述）：
- INSERT、UPDATE、DELETE 操作不加锁，而是把操作记录写入缓存
- 创建索引
- 重放缓存的操作记录
解决了哪些问题？
- 索引创建过程中无法写的问题
- 数据导出对资源的占用问题
- 对原表的要求问题
没有解决哪些问题？
无
带来了哪些问题？
- 需要控制 innodb_online_alter_log_max_size ，其默认为 128MB。超过限制导致写事务报错。