Mysql5.6+在线DDL

MySQL各版本，对于DDL的处理方式是不同的，主要有三种：

1）Copy Table方式： 这是InnoDB最早支持的方式。顾名思义，通过临时表拷贝的方式实现的。新建一个带有新结构的临时表，将原表数据全部拷贝到临时表，然后Rename，完成创建操作。这个方式过程中，原表是可读的，不可写。但是会消耗一倍的存储空间。

2）Inplace方式：这是原生MySQL 5.5，以及innodb_plugin中提供的方式。所谓Inplace，也就是在原表上直接进行，不会拷贝临时表。相对于CopyTable方式，这比较高效率。原表同样可读的，但是不可写。

3）Online方式：这是MySQL 5.6以上版本中提供的方式，也是今天我们重点说明的方式。无论是Copy Table方式，还是Inplace方式，原表只能允许读取，不可写。对应用有较大的限制，因此MySQL最新版本中，InnoDB支持了所谓的Online方式DDL。与以上两种方式相比，online方式支持DDL时不仅可以读，还可以写，对于dba来说，这是一个非常棒的改进。

切记：DDL操作要在业务低峰期进行

MySQL5.6+中online ddl：（algorithm=inplace）

ALGORITHM=INPLACE，可以避免重建表带来的IO和CPU消耗，保证ddl期间依然有良好的性能和并发。

ALGORITHM=COPY，需要拷贝原始表，所以不允许并发DML写操作，可读。这种copy方式的效率还是不如 inplace ，因为前者需要记录undo和redo log，而且因为临时占用buffer pool引起短时间内性能受影响。如里没有该参数默认为COPY方式

在线DDL的限制

1）在alter table时，如果涉及到table copy操作，要确保datadir目录有足够的磁盘空间，能够放的下整张表，因为拷贝表的的操作是直接在数据目录下进行的。

2）添加索引无需table copy，但要确保tmpdir目录足够存下索引一列的数据（如果是组合索引，当前临时排序文件一合并到原表上就会删除）

3）在主从环境下，主库执行alter命令在完成之前是不会进入binlog记录事件，如果允许dml操作则不影响记录时间，所以期间不会导致延迟。然而，由于从库是单个SQL Thread按顺序应用relay log，轮到ALTER语句时直到执行完才能下一条，所以从库会在master ddl完成后开始产生延迟。（pt-osc可以控制延迟时间，所以这种场景下它更合适）

4）每个在线DDL ALTER TABLE语句中，不管哪种LOCK，在开始和结束都会产生暂时的独占锁（LOCK=exclusive子句指定的同一种锁）。因此，如果有一个长时间运行的事务在该表上执行INSERT、UPDATE、DELETE或SELECT … FOR UPDATE，则在线DDL操作可能会在事务结束之前等待；如果在ALTER表进行时启动了类似的长时间运行的事务，则在线DDL操作可能会等待事务完成再执行。

5）在执行一个允许并发DML在线 ALTER TABLE时，结束之前这个线程会应用 online log 记录的增量修改，而这些修改是其它thread里产生的，所以有可能会遇到重复键值错误(ERROR 1062 (23000): Duplicate entry)。

6）涉及到table copy时，目前还没有机制限制暂停ddl，或者限制IO阀值

7）在MySQL 5.7.6开始能够通过 performance_schema 观察alter table的进度 ，一般来说，建议把多个alter语句合并在一起进行，避免多次table rebuild带来的消耗。但是也要注意分组，比如需要copy table和只需inplace就能完成的，应该分两个alter语句。

8）如果DDL执行时间很长，期间又产生了大量的dml操作，以至于超过了innodb_online_alter_log_max_size变量所指定的大小，会引起DB_ONLINE_LOG_TOO_BIG 错误。默认为 128M，特别对于需要拷贝大表的alter操作，考虑临时加大该值，以此获得更大的日志缓存空间

9）执行完 ALTER TABLE 之后，最好 ANALYZE TABLE tb1 去更新索引统计信息

注意事项

1）ALGORITHM用法只对varcahr类型有效哦，比如我们对c1列int型进行变更：

MySQL [test]> alter table test_emp ALGORITHM=INPLACE,modify c1 int(11) unsigned not null;

ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

2） 在varchar变更字段长度方面，5.7的新特性ALGORITHM参数可以快速调整varchar类型的字段长度。5.7同5.6一样，增加，删除字段或索引不锁全表，删除主键锁全表。因此，在上线时，一定要执行show processlist命令并观察，此刻是否有某个慢SQL对该表进行操作，以免alter table表时出现锁表现象。

3）只变更int的位数，是可以的，不过这没什么意义，因为无论你int多少，最多都只能存10位，这也就是为什么我们生产库开发规范要定义所有的int都用int(10)。

4）如果字段属性大于并等于varchar(256)（这里的256是指字节（UTF8占用3字节）或者把varchar(80)减少到varchar(70)或者更少)，则仍需要拷贝数据且锁全表。

5）添加字段alter table时，对该表的增删改查均不会锁表。而在这之前，当该表被访问时，需要等其执行完毕后才可以执行alter table。

6）在MySQL5.6中在线DDL会锁全表：增加、删除字段或索引不会锁全表，删除主键会锁全表。

#在线添加索引：
alter table test_emp add index idx_id (c1),ALGORITHM=INPLACE;

#在线添加字段：
alter table test_emp add name varchar(100) not null default '',ALGORITHM=INPLACE;

#在线修改字段属性：
alter table test_emp ALGORITHM=INPLACE,modify c6 varchar(85) not null default '';

#PRIMARY  KEY（主键索引）
ALTER  TABLE  `table_name`  ADD  PRIMARY  KEY (  `column`  ) ,ALGORITHM=INPLACE;

#UNIQUE(唯一索引)
ALTER  TABLE  `table_name`  ADD  UNIQUE (`column` ) ,ALGORITHM=INPLACE;

#INDEX(普通索引)
ALTER  TABLE  `table_name`  ADD  INDEX index_name (  `column`  ),ALGORITHM=INPLACE;

#FULLTEXT(全文索引)
ALTER  TABLE  `table_name`  ADD  FULLTEXT ( `column` ),ALGORITHM=INPLACE;

#多列索引
ALTER  TABLE  `table_name`  ADD  INDEX index_name (  `column1`,  `column2`,  `column3`  ),ALGORITHM=INPLACE;