MySQL系列四（Online DDL、Online DDL的Alter、批量删除导致IO出现瓶颈）

Online DDL

在MySQL中，实现Online DDL（在线数据定义语言）操作的方法主要取决于使用的存储引擎。以下是MySQL中实现Online DDL操作的一些常见方法：

1. InnoDB在线DDL：

   • InnoDB是MySQL默认的事务性存储引擎，在较新的版本中支持一些在线DDL操作。
   • 通过设置参数innodb_online_alter_log_max_size和innodb_online_alter_log_trigger，可以允许InnoDB在执行DDL操作期间保持在线。
   • 使用ALTER TABLE语句进行表结构修改时，InnoDB可以在后台完成重建表的操作，以避免锁定整个表。

2. pt-online-schema-change工具：

   • pt-online-schema-change是Percona Toolkit提供的一个工具，用于实现MySQL在线DDL操作。
   • 该工具使用了复制和临时表的技术，允许在进行DDL操作的同时继续对表进行读写操作。
   • 它创建一个与原表结构相同的临时表，将原表的数据复制到临时表中，然后在临时表上执行DDL操作，最后将数据同步回原表。

3. gh-ost工具：

   • gh-ost（GitHub Online Schema Migrations for MySQL）是由GitHub开源的一个工具，专门用于实现MySQL的在线DDL操作。
   • 它使用复制和事务的方式，以逐步迁移的方式在后台执行DDL操作，同时保持对原表的读写操作。
   • gh-ost具有高度的可配置性和安全性，可以应对复杂的DDL操作和大规模的数据表。

需要注意的是，虽然上述工具和方法可以实现Online DDL操作，但在执行过程中仍可能对数据库性能产生一定的影响。因此，在进行Online DDL操作时，建议进行充分的测试和评估，并根据具体情况选择适当的方法和工具。此外，备份数据和确保数据的一致性也是进行DDL操作的重要步骤。

Online DDL的Alter

MySQL 5.6版本之前更新步骤

1. 对原始表加写锁。
2. 按照原始表的定义，重新定义⼀个空的临时表。
3. 对临时表进⾏添加索引（如果有）。
4. 再将原始表中的数据逐条Copy到临时表中。
5. 当原始表中的所有记录都被Copy临时表后，将原始表进⾏删除。再将临时表命名为原始表表名。
6. 这样的话整个DDL过程的就是全程锁表的。

MySQL 5.6版本之后更新步骤

1. 对原始表加写锁
2. 按照原始表和执⾏语句的定义，重新定义⼀个空的临时表。并申请rowlog的空间。
3. 拷贝原表数据到临时表，此时的表数据修改操作（增删改）都会存放在rowlog中。此时该表客户端可以进⾏操作的。
4. 原始表数据全部拷贝完成后，会将rowlog中的改动全部同步到临时表，这个过程客户端是不能操作的（insert 会报Duplicate entry 'XXX' for key 'XXX'）。
5. 当原始表中的所有记录都被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秒之后，该表的查询、修改、新增操作都会陷⼊等待状态。

批量删除导致IO出现瓶颈

背景：线上临时表积压数据过多，虽然有索引和数据归档，但是每日新增数据量>删除&归档数量，导致整体数据操作效率变慢。所以提交数据库删除工单，结果导致线上数据库半个小时内整个数据库集群链接不可用。

原因：数据表有大字段，根据ID一次性删除时数据库IO出现瓶颈。

解决办法：有大字段时，建议分批次删除，一次只删除50万~100万数据。