面试题：MySQL的Online DDL对加速ALTER TABLE操作的原理及适用场景

Question

MySQL的Online DDL是一种加速ALTER TABLE操作的方法，请详细说明它的工作原理，以及在实际应用中，有哪些场景使用Online DDL最为合适，同时分析可能存在的限制。

Answer 1

Online DDL工作原理

1. Copy方式：
   • 当执行 ALTER TABLE 操作，如添加列等操作时，MySQL会创建一个临时表。
   • 按照新表结构将原表数据逐行复制到临时表，复制完成后，重命名临时表替换原表。在复制过程中，原表会加读锁，允许并发读，但不允许写操作。
2. In - Place方式：
   • 对于某些操作（如添加索引、删除索引等），MySQL直接在原表上进行操作。
   • 以添加索引为例，MySQL会在原表上构建索引树，在构建过程中，原表依然可以正常读写。通过对数据页的操作，逐步完成索引的创建，而不需要复制整个表的数据。这是因为索引相关操作通常不需要改变表数据的存储格式和布局。

适用场景

1. 添加索引：
   • 当业务系统需要提高查询性能，需要给现有表添加索引时，Online DDL的In - Place方式可以在不影响业务读写的情况下完成索引添加。例如，一个电商网站的商品表，在高并发访问的情况下，添加商品名称索引来加速商品搜索，就可以使用Online DDL。
2. 删除索引：
   • 当发现某个索引不再被使用，需要删除以减少存储开销和维护成本时，Online DDL同样可以在不影响业务的情况下删除索引。比如，随着业务发展，某个特定历史时期的查询条件不再使用，对应的索引可以在线删除。
3. 添加列（部分情况）：
   • 如果添加的列允许为 NULL 且没有默认值，MySQL 8.0 及以上版本支持使用In - Place方式在线添加列。例如，在用户表中添加一个预留字段，用于未来可能的业务扩展，且该字段可以为空，就可以使用Online DDL在线添加。

限制

1. 操作类型限制：
   • 不是所有的 ALTER TABLE 操作都支持Online DDL。例如，改变列的数据类型（如从 INT 改为 BIGINT），在大多数情况下不支持Online DDL，因为这通常需要改变数据的存储格式，需要对表数据进行重写，一般采用Copy方式，会锁表。
2. 存储引擎限制：
   • 主要适用于InnoDB存储引擎，MyISAM存储引擎对Online DDL支持较差。InnoDB存储引擎的特性（如行级锁、MVCC机制等）使得Online DDL能够更好地实现并发操作，而MyISAM存储引擎使用表级锁，在进行 ALTER TABLE 操作时，会锁表，影响并发读写。
3. 性能影响：
   • 即使是Online DDL，在操作过程中也会对系统性能产生一定影响。例如，添加索引时，构建索引树会占用额外的CPU和I/O资源，可能导致系统整体性能下降，特别是在高并发场景下，可能会与业务操作竞争资源。
4. 版本限制：
   • 不同版本的MySQL对Online DDL的支持程度和实现方式有所差异。例如，MySQL 5.6版本开始支持部分Online DDL操作，而MySQL 8.0在Online DDL方面有了更多改进和扩展，如对更多操作支持In - Place方式等。如果使用较旧版本，可能无法享受完整的Online DDL功能。