MYSQL大表DDL处理知识点

背景

IT人员经常需要线上mysql执行一些增加字段，加入索引的DDL的操作。但是有些表数据量比较大，超过几百万或者字段特别多，需要考虑这些DDL的操作是否会造成锁表进而造成生产环境出现问题。因此在执行某些觉得有风险的操作的DDL时，查看不要锁表，以及知悉其内部操作流程非常重要。

知识点

可以查看SQL执行过程中是否存在阻塞锁

在执行SQL时查看是否会出现锁，如果要看目前是否有所锁，查看一下三张表

select * from information_schema.innodb_locks;
select * from information_schema.innodb_trx;
select * from information_schema.innodb_lock_waits;

可以使用show profiles查看某个SQL的具体执行过程

show profiles是5.0.37之后添加的，某个会话打开该功能后，该会话的SQL都可以得到trace并分析该SQL具体的执行步骤，如下。

可以在测试环境建立测试表，并查看其执行过程，进而估算SQL的执行时间。

Mysql 5.6 Online ddl

从mysql5.6开始，Online DDL特性被引进（存储引擎要求是innodb）。增强了很多种类的Alter Table操作避免拷贝表和锁表，在运行Alter操作的同时允许运行select,insert,update,delete语句。因此在最新版本，我们可以通过使用ALGORITHM和LOCK选项抑制文件拷贝和加锁。
但是即使在mysql5.6，仍然有一些alter操作（增加/删除列，增加/删除主键,改变数据类型等）需要重建。

非常实用，参考下表

额外的工具pt-online-schema-change

在mysql 没有加入online ddl特性之前，大表上的ddl语句对mysql dba造成很大的困扰。于是就有一些额外的三方工具帮助我们解决这些问题pt-online-schema-change就是其中一款，它可以在线执行DDL操作,不会阻塞读写操作从而影响业务程序。

具体的使用，因为笔者没有深入使用过，在此只做推荐，参考一下博文：

https://blog.csdn.net/mchdba/article/details/76253016

https://blog.csdn.net/lijingkuan/article/details/68951089

https://www.aliyun.com/jiaocheng/1120002.html

最后，大表DDL的最佳实践

1、一定要选业务少的时候执行DDL，一定，不要蜜汁自信。

2、执行DDL前，先看一下库中是否有未提交的事务，注意查看事务information_schema.innodb_trx表

3、随时关注服务器日志状况，已有问题要先行解决。show processlist也可以发现一些问题

4、特别危险的操作一定先在预生产环境或测试环境先行模拟，评估风险。

5、尽量避免 kill 会话进程，可能会在某些情况造成数据问题。

6、研发时，预计表就会比较大的时候，要多评审几次，多留一些预置字段，避免DDL操作。

参考资料及博文：

https://www.cnblogs.com/mysql-dba/p/6192897.html