MySQL在线DDL几种方式

MySQL 的在线 DDL（数据定义语言）操作指的是在不阻塞读写操作的前提下修改表结构。在线 DDL 的实现可以避免长时间的表锁定问题，保证在执行诸如添加列、修改索引等操作时，应用能够继续正常运行。MySQL 支持几种常见的在线 DDL 方式，主要包括：

1. InnoDB Online DDL（MySQL 5.6+）

InnoDB 存储引擎从 MySQL 5.6 开始支持原生的在线 DDL 操作。这是 MySQL 官方支持的方式，适合处理大部分简单的表结构变更。它可以在不完全阻塞读写的情况下修改表结构，例如添加或删除列、修改索引等。

特点

• 读写并发：在大多数情况下，表的 DML（Data Manipulation Language）操作，如 SELECT、INSERT、UPDATE、DELETE 等可以与 DDL 操作并行。
• 锁的类型：对于大多数操作，MySQL 5.6+ 的 InnoDB 存储引擎会使用轻量的元数据锁（MDL），不会长时间锁住表。
• 临时表：对于复杂的操作（如添加非唯一索引），MySQL 可能会创建一张临时表，拷贝原表数据到临时表，再替换原表，这可能导致性能下降。

常见操作：

• 添加或删除列：

  ALTER TABLE my_table ADD COLUMN new_column INT;

• 添加索引：

  ALTER TABLE my_table ADD INDEX (new_column);

优缺点：

• 优点：内置支持，无需额外工具，大多数情况下性能较好。
• 缺点：对于较复杂的操作，如修改列类型，可能需要重建整张表，存在性能问题。

2. Percona Toolkit - pt-online-schema-change

Percona 提供的工具 pt-online-schema-change 是一个用于在线修改表结构的开源工具。它通过创建一张临时表并逐行复制原始表的数据来避免长时间的锁定。

特点

• 无锁的 DDL 操作：通过触发器来捕获 DML 操作，将其应用到临时表和原表，确保数据一致性。
• 逐行复制：该工具通过批量复制原表数据到临时表，且允许并发的读写操作。
• 灵活性：支持 MySQL 各种版本，并且能够处理非常大的表。

工作原理

1. 创建一个与原表结构相同的新表，并应用你想要的 DDL 修改。
2. 添加触发器到原表，用于捕获对原表的所有修改（INSERT、UPDATE、DELETE 操作）。
3. 逐行将原表的数据复制到新表中。
4. 完成复制后，切换表，将新表替换为原表。
5. 删除触发器。

使用示例

pt-online-schema-change --alter "ADD COLUMN new_column INT" D=my_database,t=my_table --execute

优缺点

• 优点：无锁表结构变更，对大表也能较好支持，适合大多数在线变更操作。
• 缺点：需要额外的资源（例如创建临时表），对于高并发和高负载的环境，可能会对性能产生影响。

3. Gh-ost（GitHub Online Schema Toolkit）

Gh-ost 是 GitHub 开发的一个高效、安全、无锁的在线 DDL 工具，适用于 MySQL 的表结构修改。它主要通过读取 MySQL binlog（binary log）来捕获对表的更新，并逐行迁移数据。

特点：

• 无锁的 DDL 操作：在执行表结构变更时几乎不会锁住表。
• 使用 binlog 复制：通过 binlog 复制的方式将数据迁移到新表，而不是使用触发器。这样减少了触发器带来的性能开销。
• 逐行迁移：逐行复制数据，并对表的所有 DML 操作进行实时跟踪和应用。

工作原理：

1. 创建一个新的表（带有修改后的结构）。
2. 从 MySQL binlog 中读取原表的所有变更操作，并实时将它们应用到新表。
3. 持续迁移原表中的数据到新表。
4. 复制完成后，将新表替换为原表。
5. 清理旧表。

使用示例：

gh-ost --user="root" --password="password" --host="127.0.0.1" \
--database="my_database" --table="my_table" \
--alter="ADD COLUMN new_column INT" \
--allow-on-master --execute

优缺点：

• 优点：完全无锁，适合高并发环境，并且对生产系统影响较小；使用 binlog 复制而非触发器，性能更高。
• 缺点：工具的使用门槛较高，部署相对复杂；适合具有一定规模和高可用性要求的系统。

4. Online DDL with Galera Cluster

如果你使用的是 Galera Cluster（例如 Percona XtraDB Cluster 或 MariaDB Cluster），集群自身具有一定的在线 DDL 支持。通过该集群架构，可以在多节点上并行执行 DDL 操作，并保持数据一致性。

特点：

• 多节点同步：DDL 操作会在集群的多个节点上同步执行，保持数据一致性。
• 并发控制：某些复杂的 DDL 操作可能需要暂时阻塞写操作。

使用方式：

与标准 MySQL 语法相同，但需要在集群的主节点或任何一个可写节点上执行。

优缺点：

• 优点：适用于高可用集群系统，不需要额外工具。
• 缺点：复杂的操作可能仍需要重建表，影响较大；对某些操作仍会锁定写入。

5. Vitess DDL

如果你的 MySQL 实例在 Vitess 环境中运行，Vitess 提供了在线 DDL 操作，它允许在分片（Sharding）的 MySQL 集群上执行 DDL 操作。

特点：

• Sharding 支持：适用于分布式的 MySQL 环境。
• 无锁操作：通过在线复制实现无锁表结构变更。

使用方式：

在 Vitess 中，DDL 操作可以通过 Vitess 控制面板或命令行工具执行，语法与 MySQL DDL 类似。

优缺点：

• 优点：适用于大规模分布式系统，具有强大的在线 DDL 支持。
• 缺点：需要 Vitess 环境，适用于超大规模数据库系统。

总结

MySQL 提供了多种在线 DDL 操作方式，具体使用哪种方式取决于你的业务需求和数据库规模：

• InnoDB 原生在线 DDL 适合大多数简单的表结构变更，是 MySQL 官方支持的方式。
• Percona Toolkit (pt-online-schema-change) 是一个非常灵活、稳定的工具，适合大规模表的复杂变更。
• Gh-ost 是 GitHub 的开源工具，性能更优，适合高并发环境下的无锁表结构修改。
• Galera Cluster 和 Vitess 则适合运行在集群和分布式环境下的大型系统。

选择合适的工具或方式可以让你的在线 DDL 操作更加高效和安全。