各位观众老爷们，大家好！我是今天的主讲人，江湖人称“代码老司机”。今天咱们聊聊一个让DBA和开发都头疼，但又不得不面对的问题：MySQL在线DDL。
想象一下，你正悠哉游哉地刷着短视频，突然运营跑过来跟你说：“用户量暴涨！商品表必须加个索引！赶紧的！” 你心里一万只草泥马奔腾而过，加索引就加索引，但线上表啊！一锁表，用户体验立马凉凉！
所以，如何在不影响用户体验的情况下，优雅地修改表结构，这就是在线DDL要解决的问题。今天，咱们就来扒一扒MySQL在线DDL的各种姿势，让你的线上变更不再提心吊胆。
什么是DDL？为什么需要在线？
首先，咱们得弄清楚DDL是啥。DDL (Data Definition Language) 是数据定义语言，简单来说，就是用来定义数据库结构的语句，比如CREATE TABLE, ALTER TABLE, DROP TABLE等等。
传统的DDL操作通常会锁表，这意味着在操作期间，其他用户无法对表进行读写操作。对于访问量大的在线系统来说，这是不可接受的。所以，我们需要“在线”的DDL，也就是在修改表结构的同时，尽量减少甚至避免对线上业务的影响。
MySQL在线DDL的进化史
MySQL的在线DDL功能也不是一开始就这么强大的，它经历了一个漫长的进化过程：
- MySQL 5.1及更早版本： 只能通过复制表的方式进行DDL操作。 创建一个新表，将数据复制过去，然后rename。 整个过程非常耗时，而且需要大量的磁盘空间。 这个阶段，基本上就是噩梦。
- MySQL 5.5： 引入了
INPLACE算法。 允许在原表上直接进行DDL操作，不再需要复制表。 但仍然会锁表，只不过锁的时间大大缩短了。 - MySQL 5.6： 引入了
ONLINE算法。 真正意义上的在线DDL，可以在执行DDL操作的同时，允许用户对表进行读写操作。 - MySQL 5.7及以后： 对ONLINE DDL进行了进一步的优化和增强，支持更多的DDL操作。
Online DDL的三种算法：COPY、INPLACE和ONLINE
理解这三种算法是掌握在线DDL的关键：
| 算法 | 描述 | 锁表情况 | 空间需求 | 适用场景 |
|---|---|---|---|---|
| COPY | 创建一个和原表结构相同的新表，将原表的数据复制到新表中，然后删除原表，将新表重命名为原表的名字。 | 锁表 | 需要两倍的表空间 | 尽量避免使用，除非没有其他选择。 |
| INPLACE | 在原表上直接进行DDL操作。 | 共享锁 | 较少 | 适用于只需要修改元数据，或者修改的数据量较小的DDL操作。 比如修改列的注释，或者增加一个允许NULL的列。 |
| ONLINE | 在原表上直接进行DDL操作，并且允许用户在DDL操作期间对表进行读写操作。 | 几乎无锁 | 较少 | 适用于大多数DDL操作，比如增加索引，修改列的数据类型等等。 但是，有些操作仍然需要短暂的锁表时间，比如修改主键。 |
如何选择合适的算法？
MySQL会根据你的DDL语句和表结构，自动选择合适的算法。 但是，你也可以通过ALGORITHM和LOCK子句来显式地指定算法和锁的类型。
ALGORITHM: 用于指定DDL操作的算法。 可以是COPY,INPLACE或ONLINE。LOCK: 用于指定DDL操作期间的锁类型。 可以是NONE,SHARED,EXCLUSIVE或DEFAULT。
例如：
ALTER TABLE `user` ADD INDEX `idx_name` (`name`) ALGORITHM=ONLINE, LOCK=NONE;这个语句表示，我们要给user表的name列增加一个索引，使用ONLINE算法，并且不加任何锁。
在线DDL的实践：加索引
咱们以最常见的加索引为例，来演示一下在线DDL的用法。
假设我们有一个user表，结构如下：
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`age` int(11) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;现在，我们需要给email列增加一个索引。
1. 使用ONLINE算法加索引
ALTER TABLE `user` ADD INDEX `idx_email` (`email`) ALGORITHM=ONLINE, LOCK=NONE;这条语句会使用ONLINE算法，并且尽量不加锁。 在执行期间，你可以继续对user表进行读写操作。
2. 使用INPLACE算法加索引
如果你的MySQL版本不支持ONLINE算法，或者MySQL认为ONLINE算法不适合当前的操作，你可以尝试使用INPLACE算法。
ALTER TABLE `user` ADD INDEX `idx_email` (`email`) ALGORITHM=INPLACE, LOCK=SHARED;这条语句会使用INPLACE算法，并且加一个共享锁。 共享锁允许多个事务同时读取表，但不允许写入。 因此，在执行期间，你可以继续读取user表，但不能写入。
3. 使用COPY算法加索引
如果以上两种算法都不适用，那就只能使用COPY算法了。
ALTER TABLE `user` ADD INDEX `idx_email` (`email`) ALGORITHM=COPY, LOCK=EXCLUSIVE;这条语句会使用COPY算法，并且加一个排他锁。 排他锁不允许任何事务对表进行读写操作。 因此，在执行期间，user表会被完全锁定。 除非万不得已，否则不要使用这种方式。
在线DDL的实践：修改列的数据类型
修改列的数据类型也是一个常见的DDL操作。 比如，我们需要将age列的数据类型从int(11)修改为bigint(20)。
ALTER TABLE `user` MODIFY COLUMN `age` bigint(20) DEFAULT NULL ALGORITHM=INPLACE, LOCK=NONE;这个操作通常可以使用INPLACE算法，因为只是修改了元数据，不需要复制数据。
在线DDL的实践：增加列
增加列也是一个常见的DDL操作。 比如，我们需要给user表增加一个address列。
ALTER TABLE `user` ADD COLUMN `address` varchar(255) DEFAULT NULL ALGORITHM=INPLACE, LOCK=NONE;如果增加的列允许NULL，并且没有默认值，那么通常可以使用INPLACE算法。 如果增加的列不允许NULL，或者有默认值，那么可能需要使用ONLINE算法，并且需要一段时间来填充数据。
在线DDL的注意事项
- 监控： 在执行在线DDL操作期间，一定要密切监控数据库的性能指标，比如CPU使用率，IO负载，锁等待等等。 一旦发现异常，立即停止DDL操作。
- 备份： 在执行任何DDL操作之前，一定要做好数据备份。 以防万一出现问题，可以及时恢复数据。
- 小步快跑： 尽量将大的DDL操作分解成小的DDL操作，逐步进行。 这样可以减少对线上业务的影响。
- 避开高峰期： 尽量选择在业务低峰期执行DDL操作。
- 测试： 在生产环境执行DDL操作之前，一定要在测试环境进行充分的测试。
innodb_online_alter_log_max_size参数： 这个参数决定了online DDL过程中，临时日志文件的大小。 如果这个值设置的太小，可能会导致DDL操作失败。 建议根据实际情况调整这个值。lock_wait_timeout参数： 这个参数决定了事务等待锁的时间。 如果这个值设置的太小，可能会导致DDL操作因为等待锁超时而失败。 建议根据实际情况调整这个值。- 合理使用
pt-online-schema-change：pt-online-schema-change是Percona Toolkit提供的一个非常强大的工具，可以用来执行各种在线DDL操作。 它可以自动处理很多细节，比如创建影子表，复制数据，切换表等等。 如果你觉得MySQL自带的在线DDL功能不够强大，可以考虑使用这个工具。
pt-online-schema-change 简单介绍
pt-online-schema-change 的核心思想是创建一个影子表，然后在影子表上进行DDL操作，最后将影子表切换成线上表。 整个过程如下：
- 创建影子表：
pt-online-schema-change会创建一个和原表结构相同的影子表。 - 复制数据：
pt-online-schema-change会将原表的数据复制到影子表中。 - 执行DDL操作：
pt-online-schema-change会在影子表上执行你指定的DDL操作。 - 同步数据： 在DDL操作执行期间，
pt-online-schema-change会不断地将原表上的数据变更同步到影子表中。 - 切换表： 当DDL操作完成，并且数据同步完成后，
pt-online-schema-change会将影子表切换成线上表。
总结
在线DDL是MySQL中一个非常重要的功能，它可以让你在不影响用户体验的情况下，优雅地修改表结构。 但是，在线DDL也是一个比较复杂的功能，需要你对MySQL的内部机制有一定的了解。 希望通过今天的讲座，你能够对MySQL在线DDL有一个更深入的理解，并且能够在实际工作中灵活运用。
好了，今天的讲座就到这里。 感谢各位的观看！ 如果大家有什么问题，欢迎在评论区留言。 我会尽力解答。 下次再见！