mysql 锁表_Mysql执行DDL操作怎么才能防止锁表，这篇文章值得读一读

版权归 博客园 苏家小萝卜 所有

本文简析Online DDL的实现原理与使用过程注意事项。

任何DDL操作，执行者都需要预先测试或者清晰了解这个操作会给数据库带来的影响是否是在业务期间数据库的可承受范围内，尤其是对大表的DDL操作中，需要密切留意服务器的IO，内存及CPU使用情况(每个DBA总有那么一段被大表的DDL语句坑到的血泪史)。

1 早期DDL实现原理(5.6.7之前)

Innodb早期支持通过copy table跟inplace的方式来执行DDL语句，其原理如下：

• copy table方式
  • 新建跟原表格一致的临时表，并在该临时表上执行DDL语句
  • 锁原表，不允许DML，允许查询
  • 逐行数据从原表拷贝到临时表中**(这个过程是没有排序的)**
  • 拷贝结束后，原表禁止读操作，也就是原表此时不提供读写服务
  • 进行rename操作，完成DDL过程
• inplace方式(fast index creation，仅针对索引的创建跟删除)
  • 新建frm临时文件
  • 锁原表，不允许DML，允许查询
  • 按照聚集索引的顺序，查询数据，找到需要的索引列数据，排序后插入到新的索引页中
  • 原表禁止读操作，也就是原表此时不提供读写服务
  • 进行rename操作，替换frm文件，完成DDL过程

inplace在copy table的基础上做了一个较大的改进，则是不需要copy整个表格，只需要在原来的ibd文件上，新建所需要的索引页，这个过程比copy table节约极大的IO资源占用 且 DDL SQL执行速度大大提高，减少了该表格不提供写服务的时长。

但是inplace仅支持索引的创建于删除，不支持其他的DDL操作，其他的DDL操作，仍然是copy table方式执行。

对于一个线上业务数据库，无论是copy table方式还是inplace方式，这里仍然有一个明显的弊端：操作期间涉及表格不提供写服务！无法对涉及到表格至下INSERT,UPDATE,DELETE操作，仅支持SELECT。

2 Online DDL实现原理

当表格发生DDL操作，可能会出现该表格数分钟甚至数小时不可访问，性能及响应异常，为了有效改善这个情况，MySQL 在5.6.7版本推出了Online DDL。(本文参考官网5.7版本的文档整理及测试)。

在online DDL中，也包含了copy跟inplace两种方式，对于不支持Online DDL的DDL SQL，则采用COPY方式；对于支持Online DDL的DDL SQL，则采用Inplace方式。

这里的Inplace又区分为2类：是否需要rebuild表格，判断标准为：是否修改行记录格式。如果修改了行记录格式，则需要rebuild表格，比如修改列类型、增减列等；如果没有修改行记录格式，仅修改表的元数据，则不需要rebuild表格，仅修改元数据 metadata，比如删除索引、设置默认值及重命名列名等。详细可见下图，具体语法情况见`第4部分`。

那么，新增的Online DDL内部是怎样一个实现原理呢？

有3个阶段：prepare、execute、commit。

• PREPARE
  • 创建新的临时frm文件
  • 持有EXCLUSIVE_MDL锁，禁止读写
  • 根据alter类型，确定执行方式(copy，rebuild，no-rebuild)
  • 更新数据字典的内存对象
  • 若是需要rebuild，分配row_log对象记录的增量
  • 若是需要rebuild， 生成新的临时ibd文件
• EXECUTE
  • 降低EXCLUSIVE-MDL锁，允许读写(copy 不允许写)
  • 记录ddl执行过程中产生的增量row-log(仅rebuild类型需要)
  • 扫描old_table的聚集索引每一条记录record
  • 遍历新表的聚集索引和二级索引，逐一处理
  • 根据record构造对应的索引项
  • 将构造索引项插入sort_buffer块
  • 将sort_buffer块插入新的索引
  • 把row-log中的操作应用到新临时表中，应用到最后一个Block
  • 这部分无操作
  • 如果是仅修改元数据：
  • 其他，则是：
• COMMIT
  • 升级到EXECLUSIVE-MDL锁，禁止读写
  • 重做最后一部分的row_log增量
  • 更新innodb的数据字典表
  • 提交事务，写redo日志
  • 修改统计信息
  • rename 临时的ibd