Mysql Online DDL

简介

mysql Online DDL这个特性是在mysql5.6开始支持的，更早期版本mysql进行ddl对于DBA来说是非常痛苦的。本文使用的MYSQL版本为5.7

(root@localhost) [test] > select version();
+------------+
| version()  |
+------------+
| 5.7.24-log |
+------------+
1 row in set (0.00 sec)

主要说明

onlineDDL这个新特性解决了早起版本mysql进行DDL操作同时带来锁表的问题，在DDL执行的过程中依然可以保证读写状态，不影响数据库对外提供服务，大大提高了数据库和表维护效率

早期版本mysql执行DDL语句（数据定义语言）主要以如下方式进行

copy方式

这是innodb最早实现的方式，主要实现方式

1. 创建与原表结构定义一致的临时表
2. 对原表加锁，不允许执行DML语句（数据操作语言增删改查操作）
3. 在临时表上执行DDL语句
4. 拷贝原表数据到临时表
5. 原表与临时表进行rename操作，此时会升级原表上的锁，不允许读写，直到完成DDL操作

INPLACE

INPLACE方式也称为InnoDB fast index creation，是MySQL5.5及之后版本为了提高创建二级索引效率的方式，所以INPLACE方式仅限于二级索引的创建跟删除，关于fast index creation可以参考官方文档：InnoDB fast index creation，主要实现步骤：

1. 创建临时的frm文件；
2. 对原表加锁，不允许执行DML，但允许查询；
3. 根据聚集索引的顺序，构造新的索引项，按照顺序插入新索引页；
4. 升级原表上的锁，不允许读写操作；
5. 进行RENAME操作，替换原表的frm文件，完成DDL操作。

相对于copy方式，inplace方式在原表上进行，不会生成临时表，也不会拷贝原表数据，减少了很多系统I/O资源，但还是无法进行DML操作，也只适用于索引的创建与删除，并不适用于其他类型的DDL语句

5.6之后的版本

在mysql6.7之后的版本引入了新特性

online DDL方式：

OnlineDDL方式是基于mysql5.5的INPLACE上进行增强。online DDL同样包含俩种方式

1. COPY方式；
2. INPLACE方式。

其中有些DDL语句不支持onlineDDL的就采用copy方式，因为onlineDDL是对早期IMPLACE方式的增加，所以INPLACE方式根据是否涉及记录格式的修改又分为俩种类型

1. rebuilds Table：因为DDL有涉及到记录格式的修改，如字段的增删类型修改等
2. No-Rebuilds Table：不涉及记录格式的修改，如索引删除，字段名修改等

• ALGORITHM={COPY|INPLACE}
  指定DDL执行时对表的操作方式。首选是INPLACE，但并非所有的语句都支持INPLACE，需要根据DDL语句类型决定。

• LOCK={NONE|SHARED|DEFAULT|EXCLUSIVE}
  指定DDL执行时对表锁定方式。默认情况下MySQL在表执行DDL时会尽量使用最少的锁定，LOCK选项可以为DDL语句指定执行更为严格的锁定方式，一旦指定的锁级别低于DDL语句执行所需的锁级别，则DDL语句会执行失败。

  • NONE：允许并发查询和DML操作；
  • SHARED：允许并发查询，但不允许DML操作；
  • DEFAULT：允许尽可能多的并发查询或DML操作(或两者都允许)，没指定LOCK选项默认就为DEFAULT；
  • EXCLUSIVE：不允许并发查询和DML操作。

Online DDL类型

MySQL5.7支持的Online DDL操作类型主要有以下种类：

由以上表格可以看出涉及索引的DDL操作都可以使用INPLACE方式来完成，除了创建全文索引与空间索引以外都允许DML操作

-- 创建添加二级索引
create index index_name on table_name (column[,column]..);
或
alter table table_name add index index_name (column[,column]..);

-- 删除索引
drop index index_name on table_name;
或
alter table table_name drop index index_name;

-- 重命名索引
alter table table_name rename index old_index_name to new_index_name, algorithm=inplace, lock=none;

-- 创建全文索引
create fulltext index index_name on table_name(column[,column]..);

-- 创建空间索引
create table geom (g geometry not null);
alter table geom add spatial index(g), algorithm=inplace, lock=none;

-- 修改索引类型
alter table table_name drop index index_name, add index index_name(column[,column]..) using btree, algorithm=inplace;

字段操作

从表格中可以看出除了修改字段类型的DDL语句

-- 添加字段，如果添加的是自增列，还是不允许DML操作
alter table table_name add column column_name column_definition, algorithm=inplace, lock=none;

-- 删除字段
alter table table_name drop column column_name, algorithm=inplace, lock=none;

-- 重命名字段
/*
为了允许并发DML操作，需保持重命名后字段类型一致，只修改字段名；
如果重命名的字段在外键定义中，外键定义也会自动更新为新字段名。
*/
alter table table_name change old_column_name new_column_name data_type, algorithm=inplace, lock=none;

-- 重排序字段，使用first或after
after table table_name modify column column_name column_definition first, algorithm=inplace, lock=none;

-- 字段修改类型，只支持COPY方式
alter table table_name change column column column_definition, algorithm=copy;

-- 扩展VARCHAR字段大小
/*
当varchar字节长度为0~255时，需要额外一个字节进行编码；
当varchar字节长度大于255时，则需要额外两个字节进行编码；
当varchar字节长度在0~255之间时，并且需从小变大的情况，支持INPLACE方式；
当varchar字节长度字节编码数从1变为2或者从2变为1时，则需要用COPY方式。
*/
alter table table_name change column column column_definition, algorithm=inplace, lock=none;
-- 例表：
t(c1 varchar(20))
alter table t change c1 c1 varchar(85), algorithm=inplace, lock=none;
alter table t change c1 c1 varchar(10), algorithm=copy;
alter table t change c1 c1 varchar(100), algorithm=copy;

-- 字段指定默认值
alter table table_name alter column column_name set default literal, algorithm=inplace, lock=none;

-- 删除字段默认值
alter table table_name alter column column_name drop default, algorithm=inplace, lock=none;

-- 修改自增列值
alter table table_name auto_increment=next_value, algorithm=inplace, lock=none;

-- 字段指定NULL
alter table table_name modify column column_name data_type NULL, algorithm=inplace, lock=none;

-- 字段指定NOT NULL
-- sql_mode中需包含选项STRICT_TRANS_TABLES和STRICT_ALL_TABLES才能使用INPLACE，否则需使用COPY
alter table table_name modify column column_name data_type NOT NULL, algorithm=inplace, lock=none;

-- 修改枚举(ENUM OR SET)定义值
-- 例表：
t (c2 enum('a','b','c'))
alter table t modify column c2 enum('a','b','c','d'), algorithm=inplace, lock=none;

组合字段操作

组合字段操作类型如下表所示：

-- 例表：
t (c1 int,c2 varchar(20))
-- 添加存储组合字段
alter table t add column (c3 int generated always as (c1 + 2) stored), algorithm=copy;

-- 删除存储组合字段
alter table t drop column c3, algorithm=inplace, lock=none;

-- 修改存储组合字段顺序
alter table t modify column c3 int generated always as (c1 + 1) stored first, algorithm=copy;

-- 添加虚拟组合字段
-- 对于非分区表才可以使用INPLACE方式，不能与其他的alter table语句合并使用
alter table t add column (c3 int generated always as (c1 + 1) virtual), algorithm=inplace, lock=none;

-- 删除虚拟组合字段
-- 对于非分区表才可以使用INPLACE方式，不能与其他的alter table语句合并使用
alter table t drop column c3, algorithm=inplace, lock=none;

-- 修改虚拟组合字段顺序
alter table t modify column c3 int generated always as (c1 + 1) virtual first, algorithm=copy;

主键操作

主键操作类型如下表所示：

-- 例表：
t (c1 int,c2 varchar(20))
-- 添加主键
alter table t add primray key (c1),algorithm=inplace, lock=none;

采用INPLACE方式进行表数据重构，如果添加主键涉及字段没有NOT NULL属性时，则无法使用INPLACE方式，只能使用COPY方式。InnoDB表为索引组织表，当涉及到聚集索引的重新构建时需要对表中数据进行拷贝，为了减少性能开销，最好在建表时就指定主键。

因为InnoDB表的特殊性，DDL操作主键一定会涉及到表行数据的拷贝操作，但通过INPLACE方式添加比COPY方式添加主要有如下优势：
1.不需要记录undo和redo日志，记录日志会提升性能开销；
2.二级索引数据行是预先排序的，可以按顺序加载；
3.无需使用到change buffer，因为没有随机数据插入二级索引当中。

-- 删除主键
alter table t drop primary key,algorithm=copy;

-- 删除主键并添加新主键
alter table t drop primary key,add primary key(c1,c2), algorithm=inplace, lock=none;

外键操作

外键操作类型如下表所示：

-- 添加外键
当系统参数foreign_key_checks = 0时，可以使用INPLACE方式，否则，只能使用COPY方式。
alter table t1 add constraint fk_name foreign key index(col1) references t2(col2) referential_actions;

-- 删除外键
alter table t drop foreign key fk_name;

表操作

-- 修改行格式
alter table table_name row_format = format, algorithm=inplace, lock=none;

-- 修改索引键块大小
alter table table_name key_block_size = value, algorithm=inplace, lock=none;

-- 设置永久表统计信息选项
alter table table_name stats_persistent = 0, stats_sample_pages = 20, stats_auto_recalc = 1, algorithm=inplace, lock=none;

-- 优化表
-- 如果表中有全文索引，则不能使用INPLACE方式，不能使用algorithm和lock子句。
optimize table table_name;

-- 使用FORCE选项重建表
-- 如果表中有全文索引，则不能使用INPLACE方式。
alter table table_name force, algorithm=inplace, lock=none;

-- 使用NULL重建表
-- 如果表中有全文索引，则不能使用INPLACE方式。
alter table table_name engine = InnoDB, algorithm=inplace, lock=none;

-- 重命名表
alter table table_name rename to new_table_name, algorithm=inplace, lock=none;

表分区操作类型如下表所示：

表空间操作

表空间操作类型如下表所示：

主要涉及独立表空间加密的Online DDL操作：

alter table table_name encryption='Y', algorithm=copy;

OlineDDL的过程

onlineDDL主要由准备，执行，提交三个阶段

PAEPAR

• 创建新的临时frm文件
• 根据EXCLUSAVE-MDL锁，禁止读写操作
• 根据ALTER类型确定执行方式(copy,Online-Rebuilds,Online-No-Rebuilds)；
• 跟新数据字典的内存对象
• 分配row_log对象记录增量（Rebuilds需要）
• 生成新的临时idb文件（Rebuilds需要）

EXECUTE

• 降级EXCLUSIVE-MDL锁，允许读写；
• 记录执行期间产生的DML增量到row_log中(Rebuilds需要)；
• 扫描old_table的聚集索引中每一条记录record；
• 遍历新表的聚集索引和二级索引，逐一处理；
• 根据record构造对应的索引项；
• 将构造的索引项插入sort_buffer块中；
• 将sort_buffer块插入到新的索引中；
• 将row_log中的记录应用到新临时表中，应用到最后一个block；

COMMIT

• 升级到EXECLUSIVE-MDL锁，禁止读写；
• 重做row_log中最后一部分的增量；
• 更新InnoDB的数据字典表；
• 提交事务，写InnoDB redo日志；
• 修改统计信息；
• RENAME临时的ibd和frm文件；
• 执行变更完成。

row_log记录了DDL执行期间产生的DML操作，这保证了变更期间表的并发性，通过以上过程可以看出在EXECUTE(执行)阶段表允许读写操作，操作记录在row_log中，在最后阶段应用到新表当中，保证了数据的完整性。

总之，对于线上环境的DDL语句执行同样也需要保持敬畏之心，无论执行的DDL语句是复杂还是简单，最好可以评估下执行成本，还有需要选择在业务低峰期进行操作。

总结

5.6之前

• 对原始表加写锁
• 按照原始表执行语句的定义，重新定义一个空的临时表
• 对临时表进行添加索引（如果有）
• 再将原始表中的数据逐条copy到临时表中
• 当原始表中所有记录被copy临时表中
• 当原始表中的所有记录被copy到临时表后，将原始表进行删除。再将临时表命名为原始表表名
• 这样的话整个DDL过程就是全程锁表的

5.6之后

• 对原始表加写锁
• 按照原始表和执行语句的定义，重新定义一个空的临时表。并申请rowlog的空间
• 拷贝原表数据到临时表，此时的表数据修改操作，都会存放在rowlog中，此时该表客户端可以操作
• 原始表数据全部拷贝完成后，将会rowlog中的改动同步到临时表，这个过程客户端是不能操作的
• 当原始表的所有记录都被copy到临时表，并且copy期间客户端的所有增删改操作都同步到临时表。再将临时表命名为原始表

ALTER TABLE 加字段会加锁。只是Mysql5.6版本之后新增了ONLINE DDL的功能，可以使该表不能使用的时间大大缩短。

注意

ALTER TABLE 加字段的时候。如果该表的数据量非常大。不要设置default值。
比如，当前有2000万以上数据量的表。如果加字段加了default值。Mysql会执行在执行Online DDL之后，对整个表的数据进行更新默认值的操作，即

UPDATE `table_name` SET new_col = [默认值] WHERE TRUE

这样就相当于是更新了2000w+的数据，而且是在同一个事务里。也就是说这个事务会把整个表都锁住，直到所有的数据记录都更新完默认值以后，才会提交。

这个时间非常长，而且由于会锁全表的记录，所以该表不可用的时间会非常长。

笔者实验过16核，32G，Mysql默认配置。500w的数据量加一个字段。

不加default值，整个DDL更新过程是66秒。而且整个更新过程，该表的查询、修改、新增操作都是可用的。几乎对该表的可用性没有任何影响。

加default值，整个DDL更新过程是213秒。经过测试，大约在100秒之后，该表的查询、修改、新增操作都会陷入等待状态。

https://www.cnblogs.com/dbabd/p/10381942.html#_label00