MySQL 5.6 之后的 ddl 不会锁表吗？

Question

一、背景

dba 的日常工作肯定有一项是 ddl 变更，ddl变更会锁表，这个可以说是 dba 心中永远的痛，特别是执行ddl变更，导致库上大量线程处于 Waiting for meta data lock 状态的时候。 因此 mysql 5.6 的 online ddl 特性是 dba 们最期待的新特性，这个特性解决了执行 ddl 锁表的问题，保证了在进行表变更时，不会堵塞线上业务读写，保障在变更时，库依然能正常对外提供访问。网上关于 online ddl 的文章很多，但涉及原理的很少，都是介绍语法之类的，本文将详细介绍 online ddl 的原理，知其然，更要知其所以然。

DDL（Data Definition Languages）语句：数据定义语言，这些语句定义了不同的数据段、数据库、表、列、索引等数据库对象的定义。
常用的语句关键字主要包括 create、drop、alter等。

DML（Data Manipulation Language）语句：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性。
常用的语句关键字主要包括 insert、delete、udpate 和select 等。(增添改查）

DCL（Data Control Language）语句：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。
主要的语句关键字包括 grant、revoke 等。

SELECT * FROM information_schema.INNODB_TRX;-- 命令是用来查看当前运行的所有事务：
select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx

SELECT * FROM information_schema.INNODB_LOCKS;-- 命令是用来查看当前出现的锁；

SELECT * FROM information_schema.INNODB_LOCK_WAITS;-- 命令是用来查看锁等待的对应关系；

在执行结果中可以看到是否有表锁等待或者死锁，如果有死锁发生，可以通过下面的命令来杀掉当前运行的事务：
KILL id ;   KILL 后面的数字指的是 trx_mysql_thread_id 值。

二、ddl 实现方式

5.6 online ddl 推出以前，执行 ddl 主要有两种方式 copy 方式和 inplace 方式，inplace 方式又称为 fast index creation。相对于 copy 方式，inplace方式不拷贝数据，因此较快。但是这种方式仅支持添加、删除索引两种方式，而且与copy方式一样需要全程锁表，实用性不是很强。下面以加索引为例，简单介绍这两种方式的实现流程

1、copy 方式

(1).新建带索引的临时表 (2).锁原表，禁止DML，允许查询 (3).将原表数据拷贝到临时表(无排序，一行一行拷贝) (4).进行rename，升级字典锁，禁止读写 (5).完成创建索引操作

2、inplace 方式

(1).新建索引的数据字典 (2).锁表，禁止DML，允许查询 (3).读取聚集索引，构造新的索引项，排序并插入新索引 (4).等待打开当前表的所有只读事务提交 (5).创建索引结束

三、online ddl 实现

online 方式实质也包含了copy和inplace方式，对于不支持online的ddl操作采用copy方式，比如修改列类型，删除主键，修改字符集等，这些操作都会导致记录格式发生变化，无法通过简单的全量+增量的方式实现online；对于inplace方式，mysql内部以“是否修改记录格式”为基准也分为两类，一类需要重建表(重新组织记录)，比如optimize table、添加索引、添加/删除列、修改列NULL/NOT NULL属性等；另外一类是只需要修改表的元数据，比如删除索引、修改列名、修改列默认值、修改列自增值等。

Mysql 将这两类方式分别称为rebuild方式和no-rebuild方式。 online ddl主要包括3个阶段，prepare阶段，ddl执行阶段，commit阶段，rebuild方式比no-rebuild方式实质多了一个ddl执行阶段，prepare阶段和commit阶段类似。下面将主要介绍ddl执行过程中三个阶段的流程。

Prepare 阶段

• 创建新的临时frm文件
• 持有EXCLUSIVE-MDL锁，禁止读写
• 根据alter类型，确定执行方式(copy,online-rebuild,online-norebuild)
• 更新数据字典的内存对象
• 分配row_log对象记录增量
• 生成新的临时ibd文件

ddl 执行阶段

• 降级EXCLUSIVE-MDL锁，允许读写
• 扫描old_table的聚集索引每一条记录rec
• 遍历新表的聚集索引和二级索引，逐一处理
• 根据rec构造对应的索引项
• 将构造索引项插入sort_buffer块
• 将sort_buffer块插入新的索引
• 处理ddl执行过程中产生的增量(仅rebuild类型需要)

commit 阶段

• 升级到 EXCLUSIVE-MDL 锁，禁止读写
• 重做最后 row_log 中最后一部分增量
• 更新 innodb 的数据字典表
• 提交事务(刷事务的redo日志)
• 修改统计信息
• rename 临时 idb 文件，frm 文件
• 变更完成

四、常见的 ddl 操作

类型	并发DML	算法	备注
添加/删除索引	Yes	Online(no-rebuild)	全文索引不支持
修改default值；修改列名；修改自增列值；添加/删除外键约束	Yes	Nothing	仅需要修改元数据
添加/删除列；交换列顺序；修改NULL/NOT NULL；修改ROW-FORMAT；添加/修改PK；Optimize table	Yes	Online(rebuild)	由于记录格式改变，需要重建表
修改列类型；删除PK；转换字符集；添加全文索引	No	Copy	需要锁表，不支持online

五、若干问题

1.如何实现数据完整性

使用online ddl后，用户心中一定有一个疑问，一边做ddl，一边做dml，表中的数据不会乱吗？这里面关键部件是row_log。row_log记录了ddl变更过程中新产生的dml操作，并在ddl执行的最后将其应用到新的表中，保证数据完整性。

2.online 与数据一致性如何兼得

实际上，online ddl并非整个过程都是online，在prepare阶段和commit阶段都会持有MDL-Exclusive锁，禁止读写；而在整个ddl执行阶段，允许读写。由于prepare和commit阶段相对于ddl执行阶段时间特别短，因此基本可以认为是全程online的。Prepare阶段和commit阶段的禁止读写，主要是为了保证数据一致性。Prepare阶段需要生成row_log对象和修改内存的字典；Commit阶段，禁止读写后，重做最后一部分增量，然后提交，保证数据一致。

3.如何实现 server 层和 innodb 层一致性

在prepare阶段，server层会生成一个临时的frm文件，里面包含了新表的格式；innodb层生成了临时的ibd文件(rebuild方式)；在ddl执行阶段，将数据从原表拷贝到临时ibd文件，并且将row_log增量应用到临时ibd文件；在commit阶段，innodb层修改表的数据字典，然后提交；最后innodb层和mysql层面分别重命名frm和idb文件。

4.对 innodb 表做 ddl 过程中异常了，为啥再次做 ddl 报 #sql-xxx already exists

原理

MySQL 5.7 以上的版本中，在执行创建或者删除的操作同时，将DML操作日志写入一个缓存中。待修改完成之后再重做到原表上，以保住数据的一致性。这个缓存大小由innodb_online_alter_log_max_size 控制，默认为 128MB，若用户更改表比较频繁，在线 DML 业务压力较大，则 innodb_online_alter_log_max_size 空间不能存放日志，会抛出错误，此时可以调大 innodb_online_alter_log_max_size 获得更多日志缓存空间解决问题 。