MySQL存储引擎之：`InnoDB`的`Online DDL`：其在`ALGORITHM=INSTANT`和`INPLACE`中的实现。 - 智猿学院-前后端，数据库，人工智能，云计算等领域前沿技术讲座

好的，我们开始。

MySQL InnoDB Online DDL：INSTANT 和 INPLACE 实现剖析

大家好，今天我们来深入探讨 MySQL InnoDB 存储引擎中的 Online DDL (Online Data Definition Language)，特别是 ALGORITHM=INSTANT 和 ALGORITHM=INPLACE 这两种算法的实现原理和使用场景。 Online DDL 允许在修改表结构的同时，尽可能减少甚至避免对现有读写操作的阻塞，从而最大程度地降低对业务的影响。

1. DDL 操作及其对数据库的影响

首先，我们需要理解什么是 DDL 以及它为什么会影响数据库的可用性。 DDL 语句用于定义和修改数据库的结构，例如：

CREATE TABLE：创建表
ALTER TABLE：修改表
DROP TABLE：删除表
CREATE INDEX：创建索引
DROP INDEX：删除索引

传统的 DDL 操作往往需要锁定整个表，阻止并发的读写操作，导致数据库服务中断。这种全局锁定在高并发场景下是不可接受的。 Online DDL 的目标就是减少这种锁定时间，甚至完全消除锁定，让 DDL 操作可以在后台进行，而不影响前台的业务。

2. Online DDL 的算法分类

MySQL 5.6 引入了 Online DDL，并在后续版本中不断改进。 InnoDB 支持多种 Online DDL 算法，主要分为以下几类：

COPY 算法 (ALGORITHM=COPY)：这是最原始的方式，它会创建一个新的表，将原始表的数据复制到新表，并在复制完成后替换原始表。在复制过程中，原始表仍然可以提供读操作，但写操作会被阻塞。这种算法的缺点是耗时较长，需要额外的磁盘空间。
INPLACE 算法 (ALGORITHM=INPLACE)：这种算法尝试在原始表上直接进行修改，避免数据复制。然而，并非所有的 DDL 操作都可以在原地进行。一些操作可能仍然需要构建临时表或进行表重建。 INPLACE 算法通常需要较短时间的表锁定，但也可能导致一定的性能影响。
INSTANT 算法 (ALGORITHM=INSTANT)：这是最理想的 Online DDL 算法，它几乎不需要锁定表，可以在瞬间完成 DDL 操作。但是，INSTANT 算法的适用范围非常有限，只能用于某些特定的操作。

LOCK 选项用于控制 DDL 操作期间的锁定行为，常见的选项包括：

LOCK=NONE：尽可能避免锁定，允许并发的读写操作。
LOCK=SHARED：允许并发的读操作，但阻塞写操作。
LOCK=EXCLUSIVE：独占锁定，阻止所有的读写操作。
LOCK=DEFAULT：由 MySQL 自动选择合适的锁定级别。

通常，我们希望尽可能使用 ALGORITHM=INSTANT 和 LOCK=NONE，但实际情况取决于 DDL 操作的类型和 MySQL 的版本。如果无法使用 INSTANT 算法，我们会尝试使用 INPLACE 算法，并选择合适的 LOCK 选项来平衡可用性和性能。

3. ALGORITHM=INSTANT 的实现原理和适用场景

ALGORITHM=INSTANT 是 Online DDL 的极致体现，它通过修改元数据来实现 DDL 操作，而无需修改表数据本身。这意味着 DDL 操作可以在瞬间完成，几乎不影响数据库的可用性。

ALGORITHM=INSTANT 的实现依赖于以下几个关键点：

只修改元数据： INSTANT 算法只修改数据字典中的元数据信息，例如列的定义、索引的定义等，而不涉及实际的数据页修改。
延迟生效：元数据的修改会立即生效，但数据的实际变化可能会延迟到后续的查询或写入操作时才发生。
版本控制： InnoDB 维护了表的版本信息，以便在数据发生变化时能够正确地处理元数据的变更。

ALGORITHM=INSTANT 主要适用于以下几种 DDL 操作：

添加只影响元数据的列：例如，添加一个允许 NULL 值的列，并且该列没有默认值。因为新列的默认值是 NULL，所以不需要修改现有数据。
删除只影响元数据的列：删除一个列只需要修改元数据，不需要修改数据本身。
重命名表：重命名表只需要修改元数据，不需要移动数据。

以下是一些使用 ALGORITHM=INSTANT 的示例：

-- 添加一个允许 NULL 值的列
ALTER TABLE my_table ADD COLUMN new_column INT NULL, ALGORITHM=INSTANT;

-- 删除一个列
ALTER TABLE my_table DROP COLUMN old_column, ALGORITHM=INSTANT;

-- 重命名表
ALTER TABLE my_table RENAME TO new_table, ALGORITHM=INSTANT;

示例：添加允许NULL值的列

假设我们有一个名为 users 的表，其结构如下：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);

INSERT INTO users (id, name) VALUES
(1, 'Alice'),
(2, 'Bob');

现在，我们想要添加一个新的列 email，允许 NULL 值。我们可以使用以下语句：

ALTER TABLE users ADD COLUMN email VARCHAR(255) NULL, ALGORITHM=INSTANT;

这条语句会立即完成，而不会阻塞对 users 表的读写操作。当我们执行查询时，例如 SELECT * FROM users;，MySQL 会自动为 email 列返回 NULL 值。

示例：删除列

假设我们想要删除 email 列，可以使用以下语句：

ALTER TABLE users DROP COLUMN email, ALGORITHM=INSTANT;

这条语句也会立即完成，并且不会影响对 users 表的读写操作。需要注意的是，删除列是不可逆的操作，所以在执行之前一定要谨慎。

ALGORITHM=INSTANT 的限制

虽然 ALGORITHM=INSTANT 非常高效，但它的适用范围非常有限。以下是一些不能使用 ALGORITHM=INSTANT 的情况：

添加 NOT NULL 约束的列
修改列的数据类型
添加或删除索引
修改表的主键

这些操作都需要修改表数据本身，因此不能使用 ALGORITHM=INSTANT。

4. ALGORITHM=INPLACE 的实现原理和优化

ALGORITHM=INPLACE 尝试在原始表上直接进行修改，避免数据复制。但是，并非所有的 DDL 操作都可以在原地进行。一些操作可能仍然需要构建临时表或进行表重建。

ALGORITHM=INPLACE 的实现依赖于以下几个关键点：

原地修改：尽可能在原始表的数据页上直接进行修改，避免数据复制。
共享锁或独占锁：在修改过程中，可能需要获取共享锁或独占锁，以保证数据的一致性。
日志记录：对表结构的修改会记录到 redo log 中，以便在崩溃恢复时能够正确地应用 DDL 操作。
后台线程：一些耗时的操作，例如索引重建，可以在后台线程中进行，以减少对前台业务的影响。

ALGORITHM=INPLACE 适用于以下 DDL 操作：

添加索引：可以在原始表上直接添加索引，而无需复制数据。 MySQL 5.6 引入了 Online Indexing，允许在添加索引的同时进行读写操作。
修改列的默认值：可以修改列的默认值，而无需修改现有数据。
扩展 VARCHAR 列的长度：可以扩展 VARCHAR 列的长度，而无需复制数据。

以下是一些使用 ALGORITHM=INPLACE 的示例：

-- 添加索引
ALTER TABLE my_table ADD INDEX my_index (column1), ALGORITHM=INPLACE, LOCK=NONE;

-- 修改列的默认值
ALTER TABLE my_table ALTER COLUMN column1 SET DEFAULT 'new_value', ALGORITHM=INPLACE, LOCK=NONE;

-- 扩展 VARCHAR 列的长度
ALTER TABLE my_table MODIFY COLUMN column1 VARCHAR(255), ALGORITHM=INPLACE, LOCK=NONE;

示例：添加索引

假设我们有一个名为 orders 的表，其结构如下：

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE
);

INSERT INTO orders (id, customer_id, order_date) VALUES
(1, 101, '2023-01-01'),
(2, 102, '2023-01-02');

现在，我们想要添加一个针对 customer_id 列的索引。我们可以使用以下语句：

ALTER TABLE orders ADD INDEX customer_id_idx (customer_id), ALGORITHM=INPLACE, LOCK=NONE;

这条语句会在后台创建一个新的索引，而不会阻塞对 orders 表的读写操作。 LOCK=NONE 选项表示尽可能避免锁定。在索引创建过程中，MySQL 会使用共享锁来允许并发的读操作，但可能会阻塞写操作。

示例：修改列的默认值

假设我们想要将 order_date 列的默认值设置为当前日期。我们可以使用以下语句：

ALTER TABLE orders ALTER COLUMN order_date SET DEFAULT CURRENT_DATE, ALGORITHM=INPLACE, LOCK=NONE;

这条语句会修改 order_date 列的默认值，而不会修改现有数据。 LOCK=NONE 选项表示尽可能避免锁定。

ALGORITHM=INPLACE 的优化

虽然 ALGORITHM=INPLACE 避免了数据复制，但它仍然可能对性能产生影响。以下是一些优化 ALGORITHM=INPLACE 操作的建议：

选择合适的 LOCK 选项：根据实际情况选择合适的 LOCK 选项，以平衡可用性和性能。如果可以接受短暂的锁定，可以选择 LOCK=SHARED 或 LOCK=EXCLUSIVE。如果需要尽可能避免锁定，可以选择 LOCK=NONE。
监控 DDL 操作的进度：可以使用 SHOW PROCESSLIST 命令来监控 DDL 操作的进度。
调整 innodb_online_alter_log_max_size 参数：这个参数控制 Online DDL 日志的最大大小。如果 DDL 操作需要大量的日志空间，可以适当增加这个参数的值。
在业务低峰期执行 DDL 操作：在业务低峰期执行 DDL 操作可以减少对前台业务的影响。
使用 MySQL 5.7 或更高版本： MySQL 5.7 和更高版本对 Online DDL 进行了改进，提供了更好的性能和可用性。

5. ALGORITHM 选择策略

选择合适的 ALGORITHM 是 Online DDL 的关键。以下是一些选择 ALGORITHM 的建议：

DDL 操作	推荐的 ALGORITHM	说明
添加允许 NULL 值的列	INSTANT	速度最快，对业务影响最小。
删除列	INSTANT	速度最快，对业务影响最小。
重命名表	INSTANT	速度最快，对业务影响最小。
添加索引	INPLACE	避免数据复制，但可能需要锁定表一段时间。
修改列的默认值	INPLACE	避免数据复制，但可能需要锁定表一段时间。
扩展 VARCHAR 列的长度	INPLACE	避免数据复制，但可能需要锁定表一段时间。
添加 NOT NULL 约束的列	COPY	需要复制数据，耗时较长，对业务影响较大。
修改列的数据类型	COPY	需要复制数据，耗时较长，对业务影响较大。
修改表的主键	COPY	需要复制数据，耗时较长，对业务影响较大。
所有其他的 DDL 操作，无法使用 INSTANT 或 INPLACE	COPY	默认情况下，MySQL 会选择 COPY 算法。

如果可能，优先选择 ALGORITHM=INSTANT。如果 ALGORITHM=INSTANT 不可用，则尝试使用 ALGORITHM=INPLACE。如果 ALGORITHM=INPLACE 也不可用，则只能使用 ALGORITHM=COPY。

6. Online DDL 的注意事项

备份数据：在执行 DDL 操作之前，务必备份数据，以防止意外情况发生。
在测试环境中验证 DDL 操作：在生产环境执行 DDL 操作之前，务必在测试环境中验证 DDL 操作的正确性和性能。
监控数据库的性能：在执行 DDL 操作期间，务必监控数据库的性能，例如 CPU 使用率、磁盘 I/O、锁等待等。
避免长时间运行的 DDL 操作：尽量避免执行长时间运行的 DDL 操作，以免影响数据库的可用性。可以将大型的 DDL 操作分解成多个小的 DDL 操作。
了解 MySQL 版本的特性：不同版本的 MySQL 对 Online DDL 的支持程度不同，务必了解所使用的 MySQL 版本的特性。

7. 总结：选择合适的 Online DDL 策略

Online DDL 是 MySQL 中一项重要的特性，它可以帮助我们在修改表结构的同时，尽可能减少甚至避免对现有读写操作的阻塞。 ALGORITHM=INSTANT 和 ALGORITHM=INPLACE 是两种常用的 Online DDL 算法，它们各有优缺点，适用于不同的 DDL 操作。通过选择合适的 ALGORITHM 和 LOCK 选项，我们可以最大限度地提高数据库的可用性和性能。