MySQL高可用与集群之：`MySQL`的`Online DDL`：其在`5.6`、`5.7`和`8.0`中的实现差异。

好的，现在我们开始本次关于MySQL Online DDL的讲座。

MySQL Online DDL: 5.6, 5.7, and 8.0 Differences

本次讲座将深入探讨MySQL Online DDL（在线数据定义语言）的演变，重点关注MySQL 5.6、5.7和8.0三个主要版本之间的实现差异。我们将研究每个版本中的关键特性、限制以及性能影响，并提供实际示例来演示这些差异。

什么是 Online DDL?

Online DDL 允许在修改表结构（例如添加索引、更改列类型）时，最大程度地减少对应用程序的影响。 在没有 Online DDL 的情况下，传统 DDL 操作通常需要锁定整个表，导致服务中断。 Online DDL 旨在通过允许在 DDL 操作期间继续进行读写操作来避免这种情况，从而提高可用性。

MySQL 5.6 的 Online DDL

MySQL 5.6 引入了第一个版本的 Online DDL，也称为“快速索引创建”。它主要集中在索引操作上，并引入了以下关键特性：

• 算法 (ALGORITHM)：引入了 ALGORITHM 子句，允许指定 DDL 操作的执行算法。 两个主要算法是 COPY 和 INPLACE。

  • COPY: 创建表的一个副本，执行更改，然后替换原始表。 此算法需要额外的磁盘空间，并且在整个操作期间表是锁定的。
  • INPLACE: 尝试就地修改表，避免创建副本。 这通常更快，并且对应用程序的影响更小，但可能并非所有操作都支持。

• 锁 (LOCK)：引入了 LOCK 子句，允许指定 DDL 操作期间允许的并发级别。 选项包括 NONE、SHARED、EXCLUSIVE 和 DEFAULT。

  • NONE: 尝试允许最大的并发性，但可能导致操作失败。
  • SHARED: 允许并发读取，但阻止写入。
  • EXCLUSIVE: 阻止所有并发读取和写入。
  • DEFAULT: 让服务器根据操作和存储引擎选择适当的锁定级别。

MySQL 5.6 Online DDL 的限制

尽管 MySQL 5.6 中的 Online DDL 是一个重要的进步，但它也存在一些限制：

• 有限的操作支持：并非所有 DDL 操作都支持 INPLACE 算法。 许多操作仍然需要 COPY 算法，这意味着表锁定和潜在的服务中断。
• 存储引擎依赖性：Online DDL 的行为取决于存储引擎。 例如，InnoDB 比 MyISAM 更好地支持 INPLACE 算法。
• 空间需求：COPY 算法需要额外的磁盘空间来创建表的副本。
• 性能影响：即使使用 INPLACE 算法，DDL 操作仍然会对性能产生影响，尤其是在繁忙的系统上。

MySQL 5.6 Online DDL 示例

以下示例演示了如何在 MySQL 5.6 中使用 Online DDL 创建索引：

ALTER TABLE mytable ADD INDEX idx_col1 (col1) ALGORITHM=INPLACE, LOCK=NONE;

此语句尝试使用 INPLACE 算法创建 idx_col1 索引，并允许最大的并发性（LOCK=NONE）。 如果 INPLACE 算法不可用，则操作将失败，除非指定了 ALGORITHM=DEFAULT。

MySQL 5.7 的 Online DDL

MySQL 5.7 通过引入以下改进来增强了 Online DDL：

• 改进的 INPLACE 算法支持：MySQL 5.7 扩展了支持 INPLACE 算法的 DDL 操作范围。 这减少了对 COPY 算法的需求，并提高了可用性。
• 在线列重命名：MySQL 5.7 引入了在线重命名列的功能。 这允许在不锁定表的情况下重命名列。
• 虚拟列支持：MySQL 5.7 增加了对虚拟列的支持，虚拟列可以添加到表中，而无需重新构建表。
• 性能改进：对 Online DDL 进行了各种性能改进，使其更快、更高效。

MySQL 5.7 Online DDL 的限制

尽管 MySQL 5.7 中的 Online DDL 比 5.6 更强大，但仍然存在一些限制：

• 某些操作仍然需要 COPY 算法：并非所有 DDL 操作都支持 INPLACE 算法。 例如，更改列的数据类型可能仍然需要 COPY 算法。
• 性能影响：Online DDL 操作仍然会对性能产生影响，尤其是在繁忙的系统上。
• 锁定问题：虽然 Online DDL 旨在减少锁定，但在某些情况下，仍然需要短时间的锁定。

MySQL 5.7 Online DDL 示例

以下示例演示了如何在 MySQL 5.7 中在线重命名列：

ALTER TABLE mytable CHANGE COLUMN old_col new_col INT;

此语句尝试在线重命名 old_col 列为 new_col。 如果操作无法在线完成，则服务器将返回错误。

MySQL 8.0 的 Online DDL

MySQL 8.0 引入了对 Online DDL 的重大改进，使其更加强大和灵活。 主要改进包括：

• Instant DDL: MySQL 8.0 引入了 "Instant DDL" 的概念，允许某些 DDL 操作在几秒钟内完成，而不会对应用程序产生任何影响。 这通过更新数据字典元数据而不修改表数据来实现。 Instant DDL 支持的操作包括：

  • 添加只包含 NOT NULL 的列，且有默认值。
  • 删除列。
  • 重命名表。
  • 更改 ENUM 或 SET 列的定义，只要不更改底层存储。
• 原子 DDL: MySQL 8.0 引入了原子 DDL，确保 DDL 操作要么完全成功，要么完全失败。 这消除了部分 DDL 操作的可能性，部分 DDL 操作可能会使数据库处于不一致的状态。
• 改进的性能: 对 Online DDL 进行了各种性能改进，使其更快、更高效。 例如，优化了索引创建过程。
• 隐藏索引: MySQL 8.0 允许创建隐藏索引。隐藏索引不会被查询优化器使用，但可以用于测试新索引的影响，而无需完全删除现有索引。

MySQL 8.0 Online DDL 的优势

MySQL 8.0 中的 Online DDL 具有以下优势：

• 更高的可用性：Instant DDL 减少了 DDL 操作所需的时间，从而提高了可用性。
• 改进的数据一致性：原子 DDL 确保 DDL 操作要么完全成功，要么完全失败，从而提高了数据一致性。
• 增强的灵活性：隐藏索引允许测试新索引的影响，而无需完全删除现有索引，从而提高了灵活性。
• 更好的性能：对 Online DDL 进行了各种性能改进，使其更快、更高效。

MySQL 8.0 Online DDL 示例

以下示例演示了如何在 MySQL 8.0 中使用 Instant DDL 添加列：

ALTER TABLE mytable ADD COLUMN new_col INT NOT NULL DEFAULT 0;

此语句使用 Instant DDL 添加 new_col 列。 由于该列具有 NOT NULL 约束和默认值，因此可以在几秒钟内完成操作。

以下示例演示了如何在 MySQL 8.0 中创建隐藏索引：

ALTER TABLE mytable ADD INDEX idx_col2 (col2) INVISIBLE;

此语句创建 idx_col2 索引，但将其标记为 INVISIBLE。 这意味着查询优化器不会使用该索引，但可以用于测试其性能影响。

版本差异总结

以下表格总结了 MySQL 5.6、5.7 和 8.0 之间的 Online DDL 差异：

特性	MySQL 5.6	MySQL 5.7	MySQL 8.0
主要关注点	快速索引创建	改进的 INPLACE 算法支持，在线列重命名	Instant DDL, 原子 DDL, 改进的性能，隐藏索引
INPLACE 支持	有限	扩展	显著扩展，包括 Instant DDL 支持的操作
COPY 算法需求	较高	降低	显著降低，尤其是在 Instant DDL 支持的操作中
锁定行为	较高	降低	显著降低，Instant DDL 操作几乎没有锁定
原子性	不支持	不支持	支持，确保 DDL 操作要么完全成功，要么完全失败
Instant DDL	不支持	不支持	支持，允许某些 DDL 操作在几秒钟内完成
隐藏索引	不支持	不支持	支持，允许创建查询优化器忽略的索引
示例 (添加索引)	ALTER TABLE mytable ADD INDEX idx_col1 (col1) ALGORITHM=INPLACE, LOCK=NONE;	ALTER TABLE mytable ADD INDEX idx_col1 (col1);	ALTER TABLE mytable ADD INDEX idx_col1 (col1); (如果满足 Instant DDL 的条件，例如该表为空) 如果需要显式指定算法，且是MySQL 8.0及以上版本，优先考虑ALGORITHM=INSTANT，如果不能，则考虑ALGORITHM=INPLACE。如果两者都不行，则考虑ALGORITHM=COPY（尽量避免）。
示例 (添加列)	不适用 (5.6 几乎没有在线添加列的功能)	不适用 (5.7 在线添加列的功能有限)	ALTER TABLE mytable ADD COLUMN new_col INT NOT NULL DEFAULT 0; (Instant DDL) ALTER TABLE mytable ADD COLUMN new_col INT; (非Instant DDL，但仍然会尝试在线完成)
示例 (重命名表)	极少用, 可能需要锁表	极少用, 可能需要锁表	ALTER TABLE mytable RENAME TO new_mytable; (Instant DDL)
示例 (删除列)	不适用 (5.6 几乎没有在线删除列的功能)	不适用 (5.7 在线删除列的功能有限)	ALTER TABLE mytable DROP COLUMN col_to_drop; (Instant DDL)

最佳实践

以下是使用 MySQL Online DDL 的一些最佳实践：

• 了解不同算法的影响：在执行 DDL 操作之前，请务必了解不同算法（例如 INPLACE、COPY 和 INSTANT）的影响。 选择最适合您需求的算法。
• 监控性能：在 DDL 操作期间监控系统性能。 如果性能受到严重影响，请考虑推迟操作或调整配置。
• 测试：在生产环境中执行 DDL 操作之前，请在测试环境中对其进行测试。 这将帮助您识别潜在的问题并验证操作是否按预期工作。
• 使用正确的工具：使用 pt-online-schema-change 等工具可以简化 Online DDL 操作并减少人为错误的可能性。
• 了解存储引擎的限制：不同的存储引擎对 Online DDL 的支持程度不同。 确保了解您使用的存储引擎的限制。

选择正确的算法

在选择 DDL 操作的算法时，需要考虑以下因素：

• 可用性要求：如果您需要最小化服务中断，请选择 INPLACE 或 INSTANT 算法。
• 性能要求：如果您需要尽快完成操作，请选择最快的算法。 但是，通常 INSTANT > INPLACE > COPY。
• 磁盘空间：如果您没有足够的磁盘空间来创建表的副本，请避免使用 COPY 算法。
• 操作类型：并非所有操作都支持所有算法。 请查阅 MySQL 文档以确定哪些算法适用于您的操作。

总结

MySQL Online DDL 在 MySQL 5.6、5.7 和 8.0 中发生了显著的演变。 每个版本都引入了改进，以提高可用性、性能和灵活性。 MySQL 8.0 中的 Instant DDL 是一个重大突破，允许某些 DDL 操作在几秒钟内完成，而不会对应用程序产生任何影响。 通过了解不同版本之间的差异并遵循最佳实践，您可以有效地使用 Online DDL 来管理 MySQL 数据库，而不会导致服务中断。

各个版本Online DDL的差异，以及如何选择合适的算法

本次讲座我们深入探讨了MySQL Online DDL在不同版本中的实现，以及如何根据实际需求选择合适的算法，以达到最佳的可用性和性能。