MySQL Online DDL 原理和踩坑
MySQL 的 DDL(Data Definition Language) 包括增减字段、增减索引等操作。在 MySQL 5.6 之前,MySQL 的 DDL 操作会按照原来的表复制一份,并做相应的修改,例如,对表 A 进行 DDL 的具体过程如下:
1、 按照表 A 的定义新建一个表 B
2、 对表 A 加写锁
3、 在表 B 上执行 DDL 指定的操作
4、 将 A 中的数据拷贝到 B
5、 释放 A 的写锁
6、 删除表 A
7、 将表 B 重命名为 A
在 2-4 的过程中,如果表 A 数据量比较大,拷贝到表 B 的过程会消耗大量时间,并占用额外的存储空间。此外,由于 DDL 操作占用了表 A 的写锁,所以表 A 上的 DDL 和 DML 都将阻塞无法提供服务。
因此,MySQL 5.6 增加了 Online DDL,允许在不中断数据库服务的情况下进行 DDL 操作。
用法
ALTER 语句中可以指定参数 ALGORITHM 和 LOCK 分别指定 DDL 执行的方式和 DDL 期间 DML 的兵法控制
1、 ALGORITHM=INPLACE 表示执行DDL的过程中不发生表拷贝,过程中允许并发执行DML(INPLACE不需要像COPY一样占用大量的磁盘I/O和CPU,减少了数据库负载。同时减少了buffer pool的使用,避免 buffer pool 中原有的查询缓存被大量删除而导致的性能问题)。
如果设置 ALGORITHM=COPY,DDL 就会按 MySQL 5.6 之前的方式,采用表拷贝的方式进行,过程中会阻塞所有的DML