MySQL5.5版本之前存在的一个普遍被人诟病的问题，我们先看下MySQL对于索引的添加或者删除的这类DDL操作过程：
- 首先创建一个新的临时表（alter table后新定义的表结构）
- 把原表中的数据导入到临时表
- 删除原表
- 把临时表重命名为原来的表名
若对一张大表进行索引添加或删除，这需要很长的时间，MySQL数据库会发生阻塞。这意味着数据库服务将长时间不可用，对MySQL数据库索引的维护就会非常痛苦。同时需要注意的是，临时表创建路径是通过tmpdir参数进行设置的，所以必须保证tmpdir有足够的空间可以存放临时表，否则会导致创建索引失败。
InnoDB 1.0.x版本开始支持一种称为Fast Index Creation的索引创建方式(FIC)。对于辅助索引的创建，InnoDB会对创建索引的表加上一个S锁（可读，不可写）。它在创建过程中，不需要重建表，速度较之前会快2很多。不过只能对表进行读操作。多同时对表有大量的写操作，数据库同样会不可用。
Online Scheme Change
Online Scheme Change(在线架构改变)简称OSC,最初是Facebook实现的一种在线执行DDL的方式。它可以在对表结构更改过程中，可以同时对表提供读写事务操作，即可以为在线业务提供不间断服务。这可以提高MySQL数据库DDL操作时的并发性。
osc的大致原理是：
- 创建一个和原始表一样的新表，对新表执行alter table操作（添加索引或列）。
- 创建一个deltas表，对原表创建insert、update、delete操作的触发器，触发器产生的记录将写入到deltas表中。
- 将原表中数据写入新表。这里会通过分片将数据输出到多个外部文件中，然后将外部文件数据导入到新表中，将deltas表的数据应用到新表。
- 交换原表和新表的名称（操作很快，阻塞时间极短）。
osc要求进行修改的表一定要有主键，且表本身不能存在外键和触发器。osc过程中可能会导致主从不一致。
Online DDL
osc虽然解决了大部分的问题，但还是有一定的局限性。MySQL5.6版本开始支持Online DDL，运行辅助索引创建的同时对表进行insert、update、delete这类操作，这就提高了MySQL数据库在生产环境中的可用性。
不仅辅助索引，以下DDL操作也都支持在线更新：
- 辅助索引的创建、删除
- 改变自增长值
- 添加、删除外键约束
- 列的重命名
通过alter table语法，用户可以选择索引的创建方式：
1
2
3
4
5
alter table tb_name
ADD {index|key} [index_name]
[index_type] (index_col_name,...)
algorithm [=] {DEFAULT|INPLACE|COPY}
lock [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}
alogorithm指定创建或删除索引的算法。
- COPY: 按照之前创建临时表的方式。
- INPLACE: 创建或删除操作不需要创建临时表。
- DEFAULT：根据参数old_alter_table判断是通过INPLACE还是COPY算法。默认值是OFF,表示采用INPLACE的方式。
1
2
3
4
5
6
7
mysql>show variables like 'old_alter_table';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| old_alter_table | OFF |
+-----------------+-------+
1 row in set
lock部分为索引创建或删除时对表添加锁的情况：
- NONE：执行索引创建或删除时，对目标表不添加任何锁。其它事务可进行读写，不会阻塞。
- SHARE：执行索引创建或删除时，目标表创建一个S锁（读锁，不可写）。
- EXCLUSIVE：执行所以创建或删除时，对目标表创建一个X锁，读写都不能进行，会阻塞所有线程。
- DEFAULT：判断当前是否可使用NONE模式，若不能，再判断是否可以使用SHARE模式，最后判断是否可以使用EXCLUSIVE模式。DEFAULT会通过判断事务的最大并发性来判断执行DDL的模式。
InnoDB存储引擎实现的Online DDL的原理是在执行创建或删除的同时，将insert、update、delete这类的ddl操作日志写入到一个缓存中。等待完成索引创建后再将日志重放应用到表上，达到数据的一致性。这个缓存的大小由参数innodb_online_alter_log_max_size控制，默认大小是128M。如更新的表较大且过程中同时有大量写事务，可适当调整该参数，若空间不足以存放日志则会抛出错误。
注意：由于Online DDL在索引创建完成后再通过重做日志达到数据一致性，这意味着在索引创建过程中，SQL优化器不会选择正在创建中的索引。