MySQL5.6在线表结构变更（online ddl）总结

outlook

　　MySQL从5.6.17以后，支持在线修改表结构操作（online ddl），即在变更表结构的过程中，不阻塞dml和dql操作.
　　根据操作过程中是否需要表拷贝，online ddl可分为下面两大类：
　　1.需要表拷贝的 ddl 操作：
　　增加、删除、重排列。
　　增加、删除主键。
　　改变表的 ROW_FORMAT 或 KEY_BLOCK_SIZE属性。
　　改变的字段的null状态。
　　执行OPTIMIZE TABLE，优化表。
　　使用 FORCE 选项重建表。
　　使用ALTER TABLE ... ENGINE=INNODB 语句。
　　首次创建全文索引。
　　2.不需要表拷贝的 ddl 操作：
　　创建、增加、删除普通索引。
　　创建第二个及后续的全文索引。
　　为字段设置默认值。
　　改变auto-increment值。
　　删除外键约束。
　　添加外键约束（ 只有在foreign_key_checks=off时）
　　仅仅改变列的名称
　　设置表的持续统计选项（STATS_PERSISTENT, STATS_AUTO_RECALC STATS_SAMPLE_PAGES）
　　特别说明：全文索引需要特别注意，创建了全文索引的表基本上不支持在线ddl操作。
　　ySQL 5.6 Online DDL把这种特性扩展到了添加列、删除列、修改列类型、列重命名、设置默认值等等，实际效果要看所使用的选项和操作类别来定。
1.1 Online DDL选项
　　MySQL 在线DDL分为 INPLACE 和 COPY 两种方式，通过在ALTER语句的ALGORITHM参数指定。

• 　　ALGORITHM=INPLACE，可以避免重建表带来的IO和CPU消耗，保证ddl期间依然有良好的性能和并发。
  
• 　　ALGORITHM=COPY，需要拷贝原始表，所以不允许并发DML写操作，可读。这种copy方式的效率还是不如 inplace ，因为前者需要记录undo和redo log，而且因为临时占用buffer pool引起短时间内性能受影响。
  

　　上面只是 Online DDL 内部的实现方式，此外还有 LOCK 选项控制是否锁表，根据不同的DDL操作类型有不同的表现：默认mysql尽可能不去锁表，但是像修改主键这样的昂贵操作不得不选择锁表。

• 　　LOCK=NONE，即DDL期间允许并发读写涉及的表，比如为了保证>ALGORITHM=COPY默认LOCK级别
  
• 　　LOCK=SHARED，即DDL期间表上的写操作会被阻塞，但不影响读取。
  
• 　　LOCK=DEFAULT，让mysql自己去判断lock的模式，原则是mysql尽可能不去锁表
  
• 　　LOCK=EXCLUSIVE，即DDL期间该表不可用，堵塞任何读写请求。如果你想alter操作在最短的时间内完成，或者表短时间内不可用能接受，可以手动指定。
  

　　但是有一点需要说明，无论任何模式下，online ddl开始之前都需要一个短时间排它锁(exclusive)来准备环境，所以alter命令发出后，会首先等待该表上的其它操作完成，在alter命令之后的请求会出现等待waiting meta data lock。同样在ddl结束之前，也要等待alter期间所有的事务完成，也会堵塞一小段时间。所以尽量在ALTER TABLE之前确保没有大事务在执行，否则一样出现连环锁表。
1.2 考虑不同的DDL操作类别
　　从上面的介绍可以看出，不是5.6支持在线ddl就可以随心所欲的alter table，锁不锁表要看情况：
　　提示：下表根据官方 Summary of Online Status for DDL Operations 整理挑选的常用操作。

• 　　In-Place为Yes是优选项，说明该操作支持INPLACE
  
• 　　Copies Table为No是优选项，因为为Yes需要重建表。大部分情况与In-Place是相反的
  
• 　　Allows Concurrent DML?为Yes是优选项，说明ddl期间表依然可读写，可以指定 LOCK=NONE（如果操作允许的话mysql自动就是NONE）
  
• 　　Allows Concurrent Query?默认所有DDL操作期间都允许查询请求，放在这只是便于参考
  
• 　　Notes会对前面几列Yes/No带*号的限制说明
  

OperationIn-Place?Copies Table?Allows Concurrent DML?Allows Concurrent Query?Notes添加索引Yes*No*YesYes对全文索引的一些限制删除索引YesNoYesYes仅修改表的元数据OPTIMIZE TABLEYesYesYesYes从 5.6.17开始使用ALGORITHM=INPLACE，当然如果指定了old_alter_table=1或mysqld启动带--skip-new则将还是COPY模式。如果表上有全文索引只支持COPY对一列设置默认值YesNoYesYes仅修改表的元数据对一列修改auto-increment 的值YesNoYesYes仅修改表的元数据添加 foreign key constraintYes*No*YesYes为了避免拷贝表，在约束创建时会禁用foreign_key_checks删除 foreign key constraintYesNoYesYesforeign_key_checks 不影响改变列名Yes*No*Yes*Yes为了允许DML并发, 如果保持相同数据类型，仅改变列名添加列Yes*Yes*Yes*Yes尽管允许 ALGORITHM=INPLACE ，但数据大幅重组，所以它仍然是一项昂贵的操作。当添加列是auto-increment，不允许DML并发删除列YesYes*YesYes尽管允许 ALGORITHM=INPLACE ，但数据大幅重组，所以它仍然是一项昂贵的操作修改列数据类型NoYes*NoYes修改类型或添加长度，都会拷贝表，而且不允许更新操作更改列顺序YesYesYesYes尽管允许 ALGORITHM=INPLACE ，但数据大幅重组，所以它仍然是一项昂贵的操作修改ROW_FORMAT　　和KEY_BLOCK_SIZE
YesYesYesYes尽管允许 ALGORITHM=INPLACE ，但数据大幅重组，所以它仍然是一项昂贵的操作设置列属性NULL　　或NOT NULL
YesYesYesYes尽管允许 ALGORITHM=INPLACE ，但数据大幅重组，所以它仍然是一项昂贵的操作添加主键Yes*YesYesYes尽管允许 ALGORITHM=INPLACE ，但数据大幅重组，所以它仍然是一项昂贵的操作。　　如果列定义必须转化NOT NULL，则不允许INPLACE
删除并添加主键YesYesYesYes在同一个>从表看出，In-Place为No，DML一定是No，说明ALGORITHM=COPY一定会发生拷贝表，只读。但ALGORITHM=INPLACEE也要可能发生拷贝表，但可以并发DML:

• 　　添加、删除列，改变列顺序
  
• 　　添加或删除主键
  
• 　　改变行格式ROW_FORMAT和压缩块大小KEY_BLOCK_SIZE
  
• 　　改变列NULL或NOT NULL
  
• 　　优化表OPTIMIZE TABLE
  
• 　　强制 rebuild 该表
  

　　不允许并发DML的情况有：修改列数据类型、删除主键、变更表字符集，即这些类型操作ddl是不能online的。
　　另外，更改主键索引与普通索引处理方式是不一样的，主键即聚集索引，体现了表数据在物理磁盘上的排列，包含了数据行本身，需要拷贝表；而普通索引通过包含主键列来定位数据，所以普通索引的创建只需要一次扫描主键即可，而且是在已有数据的表上建立二级索引，更紧凑，将来查询效率更高。
　　修改主键也就意味着要重建所有的普通索引。删除二级索引更简单，修改InnoDB系统表信息和数据字典，标记该所以不存在，标记所占用的表空间可以被新索引或数据行重新利用。
　　MySQL5.6几种建索引方式比较。
　　mysql> select version();
　　+-----------+
　　| version() |
　　+-----------+
　　| 5.6.17 |
　　+-----------+
　　mysql> CREATE TABLE test AS SELECT * FROM information_schema.columns;
　　mysql> INSERT INTO test SELECT * FROM test;
　　mysql> INSERT INTO test SELECT * FROM test;
　　mysql> SELECT COUNT(1) FROM test;
　　+----------+
　　| COUNT(1) |
　　+----------+
　　| 312928 |
　　+----------+
　　1 row in set (0.17 sec)
　　ALGORITHM=inplace，就地进行，如果允许的话则修改操作可以直接在该表上执行。
　　mysql> CREATE INDEX ind_t_column_name ON test(column_name) ALGORITHM=inplace;
　　Query OK, 0 rows affected (1.54 sec)
　　Records: 0 Duplicates: 0 Warnings: 0

　　mysql>>　　Query OK, 0 rows affected (0.00 sec)
　　Records: 0 Duplicates: 0 Warnings: 0
　　mysql> CREATE INDEX ind_t_column_name ON test(column_name);
　　Query OK, 0 rows affected (1.70 sec)
　　Records: 0 Duplicates: 0 Warnings: 0
　　ALGORITHM=copy，用于标识改操作是否需要整个表。
　　mysql> CREATE INDEX ind_t_column_name ON test(column_name) ALGORITHM=copy;
　　Query OK, 312928 rows affected (5.93 sec)
　　Records: 312928 Duplicates: 0 Warnings: 0
　　注：
　　SET old_alter_table=0; --ALGORITHM=inplace
　　SET old_alter_table=1; --ALGORITHM=copy