ONLINE DDL 收尾篇|如何有效实现 ONLINE

00后DBA实录 2024-10-23

150

前言

前面两篇文章中讲了一个 INPLACE 算法降级为 COPY 算法导致重建表，默认 lock=shared 模式禁止 DML 导致一段时间内阻塞生产降低 TPS 的案例。

ONLINE DDL 不 ONLINE？

并在第二篇中讲解了 DDL 算法过程。

ONLINE DDL 的要求和限制

这一篇主要对前面的内容做一个总结，并提出 ONLINE DDL 的最佳实践。

操作生产变更 DDL 前

首先，要明确知道的是，保持清晰的头脑，一切生产操作都需要谨慎，生产库表结构变更需要对相对的表有一定的自主判断：

表数据大小，这关系到 DDL 变更的时长（对于 COPY 和 INPLACE）算法。
一切 DDL 操作的生产变更都需要到低峰期进行，不要小看一条 DDL 语句的影响面，除非你明确知道该更改的具体内部流程不会对生产系统造成 TPS 上的影响，最好一切从严吧。
做变更前，了解该操作使用的 DDL 算法，并在测试库做出实践，判断它一定会使用这个算法。
明确指定 ALGORITHM 子句，如果 DDL 变更导致算法降级，会直接返回错误，避免更多问题。

如何判断该 DDL 变更会使用什么算法

宝宝教学开始：

先从官方文档判断

首先打开官方文档：https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

在此页面

找到对应的操作

比如说，我想要做 Column Operations 字段变更操作

这里会有个图表

Table 17.18 Online DDL Support for Column Operations

Operation	Instant	In Place	Rebuilds Table	Permits Concurrent DML	Only Modifies Metadata
Adding a column	Yes*	Yes	No*	Yes*	Yes
Dropping a column	Yes*	Yes	Yes	Yes	Yes
Renaming a column	Yes*	Yes	No	Yes*	Yes
Reordering columns	No	Yes	Yes	Yes	No
Setting a column default value	Yes	Yes	No	Yes	Yes
Changing the column data type	No	No	Yes	No	No
Extending `VARCHAR` column size	No	Yes	No	Yes	Yes
Dropping the column default value	Yes	Yes	No	Yes	Yes
Changing the auto-increment value	No	Yes	No	Yes	No*
Making a column `NULL`	No	Yes	Yes*	Yes	No
Making a column `NOT NULL`	No	Yes*	Yes*	Yes	No
Modifying the definition of an `ENUM` or `SET` column	Yes	Yes	No	Yes	Yes

Instant、In Place ：算法，这里你可能要问为啥 COPY 不在这里，一看你就是没好好听讲的坏宝宝，这里是 ONLINE DDL 的操作，COPY 不属于 ONLINE DDL，它会阻塞 DML。

那要怎么从表上判断是否是 COPY 呢？Instant、In Place 都是 NO 不就是 COPY 了，简单吧。

Rebuilds Table ：标识是否重建表的。

Permits Concurrent DML：标识是否允许并发 DML。

Only Modifies Metadata：标识是否仅修改元数据。

怎么看懂这个表格呢？

Instant 为 YES，后面的都可以不看，只要为 YES 就是仅修改元数据，并且并发 DML ，只有在 prepare 的时候要获取一下 DML-X 锁，做比如创建数据字典对象啊、判断执行方式啊（就是上面表格的算法，是否重建），然后真正执行 DDL 操作时就是修改一下数据字典，最后提交变更的时还需要升级到 DML-X 其他时间都是允许并发 DML 的
Instant 为 NO，In Place 为 YES，这是必定走 inplace 算法的，这时候就要注意了：

Rebuilds Table 为 YES，Only Modifies Metadata 为 NO，需要物理重建
Rebuilds Table 为 NO，Only Modifies Metadata 为 YES：直接 alter，肯定要修改元数据了。
Rebuilds Table 为 NO，Only Modifies Metadata 为 NO：物理添加。

Instant 为 NO，In Place 为 NO，用的 COPY 算法，如果不幸使用了这个，只能说，希望你的 datadir 和 innodb_tmpdir 空间足够

忠告：这篇官方文档往下滑会有一些实例，可能会指出一些额外的定义，一定要看，没看的话，如果出了故障可就是自己的责任哦

实践

与上一篇相同，先开启 DDL log 打印

 set global innodb_print_ddl_logs = 1;
 SET global log_error_verbosity = 3;

注意：这里不讲具体 ddllog 实际操作，具体看上一篇

Instant

Instant 就测试：

Adding a column，不需要重建表

Dropping a column，需要重建表

create table tddl(id int primary key,info varchar(20));
 insert into tddl values(1,'aa'),(2,'bb');

先增加一个字段

 root@localhost [ddl] 15:02:47 > alter table tddl add age int,ALGORITHM=instant;
 Query OK, 0 rows affected (0.04 sec)
 Records: 0  Duplicates: 0  Warnings: 0

ddllog 输出

 2024-10-23T15:04:25.059576+08:00 40 [Note] [MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 40
 2024-10-23T15:04:25.059713+08:00 40 [Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 40

再删掉

 root@localhost [ddl] 15:04:25 > alter table tddl drop age,ALGORITHM=instant;
 Query OK, 0 rows affected (0.03 sec)
 Records: 0  Duplicates: 0  Warnings: 0

ddllog

 2024-10-23T15:05:10.435661+08:00 40 [Note] [MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 40
 2024-10-23T15:05:10.435840+08:00 40 [Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 40

可以看到这两个都没有实际重建数据文件的操作，只是逻辑重建（修改数据字典）。

In Place

Reordering columns，Rebuilds Table 为 YES，Only Modifies Metadata 为 NO

 root@localhost [ddl] 15:11:22 > alter table tddl modify info varchar(20) first,ALGORITHM=INPLACE, LOCK=NONE;
 Query OK, 0 rows affected (0.17 sec)
 Records: 0  Duplicates: 0  Warnings: 0

这里可以看到在物理层面重建了

 2024-10-23T15:11:31.736339+08:00 40 [Note] [MY-012473] [InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=1077, thread_id=40, space_id=200, old_file_path=./ddl/#sql-ib1261-1822244024.ibd]
 2024-10-23T15:11:31.736830+08:00 40 [Note] [MY-012478] [InnoDB] DDL log delete : 1077
 2024-10-23T15:11:31.792079+08:00 40 [Note] [MY-012477] [InnoDB] DDL log insert : [DDL record: REMOVE CACHE, id=1078, thread_id=40, table_id=1262, new_file_path=ddl/#sql-ib1261-1822244024]
 2024-10-23T15:11:31.792287+08:00 40 [Note] [MY-012478] [InnoDB] DDL log delete : 1078
 2024-10-23T15:11:31.795997+08:00 40 [Note] [MY-012472] [InnoDB] DDL log insert : [DDL record: FREE, id=1079, thread_id=40, space_id=200, index_id=451, page_no=4]
 2024-10-23T15:11:31.796159+08:00 40 [Note] [MY-012478] [InnoDB] DDL log delete : 1079
 2024-10-23T15:11:31.854311+08:00 40 [Note] [MY-012475] [InnoDB] DDL log insert : [DDL record: DROP, id=1080, thread_id=40, table_id=1261]
 2024-10-23T15:11:31.857091+08:00 40 [Note] [MY-012474] [InnoDB] DDL log insert : [DDL record: RENAME SPACE, id=1081, thread_id=40, space_id=199, old_file_path=./ddl/#sql-ib1262-1822244025.ibd, new_file_path=./ddl/tddl.ibd]
 2024-10-23T15:11:31.857265+08:00 40 [Note] [MY-012478] [InnoDB] DDL log delete : 1081
 2024-10-23T15:11:31.860122+08:00 40 [Note] [MY-012476] [InnoDB] DDL log insert : [DDL record: RENAME TABLE, id=1082, thread_id=40, table_id=1261, old_file_path=ddl/#sql-ib1262-1822244025, new_file_path=ddl/tddl]
 2024-10-23T15:11:31.860321+08:00 40 [Note] [MY-012478] [InnoDB] DDL log delete : 1082
 2024-10-23T15:11:31.863445+08:00 40 [Note] [MY-012474] [InnoDB] DDL log insert : [DDL record: RENAME SPACE, id=1083, thread_id=40, space_id=200, old_file_path=./ddl/tddl.ibd, new_file_path=./ddl/#sql-ib1261-1822244024.ibd]
 2024-10-23T15:11:31.863614+08:00 40 [Note] [MY-012478] [InnoDB] DDL log delete : 1083
 2024-10-23T15:11:31.866190+08:00 40 [Note] [MY-012476] [InnoDB] DDL log insert : [DDL record: RENAME TABLE, id=1084, thread_id=40, table_id=1262, old_file_path=ddl/tddl, new_file_path=ddl/#sql-ib1261-1822244024]
 2024-10-23T15:11:31.866347+08:00 40 [Note] [MY-012478] [InnoDB] DDL log delete : 1084
 2024-10-23T15:11:31.872676+08:00 40 [Note] [MY-012475] [InnoDB] DDL log insert : [DDL record: DROP, id=1085, thread_id=40, table_id=1261]
 2024-10-23T15:11:31.872872+08:00 40 [Note] [MY-012473] [InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=1086, thread_id=40, space_id=199, old_file_path=./ddl/#sql-ib1262-1822244025.ibd]
 2024-10-23T15:11:31.893423+08:00 40 [Note] [MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 40
 2024-10-23T15:11:31.893595+08:00 40 [Note] [MY-012479] [InnoDB] DDL log replay : [DDL record: DELETE SPACE, id=1086, thread_id=40, space_id=199, old_file_path=./ddl/#sql-ib1262-1822244025.ibd]
 2024-10-23T15:11:31.896818+08:00 40 [Note] [MY-012479] [InnoDB] DDL log replay : [DDL record: DROP, id=1085, thread_id=40, table_id=1261]
 2024-10-23T15:11:31.896957+08:00 40 [Note] [MY-012479] [InnoDB] DDL log replay : [DDL record: DROP, id=1080, thread_id=40, table_id=1261]
 2024-10-23T15:11:31.899416+08:00 40 [Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 40

我们先看 NO NO 的创建二级索引（哎呀太麻烦了，我先新建再删多好）

Creating or adding a secondary index，Rebuilds Table 为 NO，Only Modifies Metadata 为 NO

root@localhost [ddl] 15:20:05 > create index tddl_info on tddl(info) ALGORITHM=INPLACE LOCK=NONE;
 Query OK, 0 rows affected (0.05 sec)
 Records: 0  Duplicates: 0  Warnings: 0

ddllog 输出

2024-10-23T15:20:16.036841+08:00 40 [Note] [MY-012472] [InnoDB] DDL log insert : [DDL record: FREE, id=1087, thread_id=40, space_id=200, index_id=452, page_no=5]
2024-10-23T15:20:16.037223+08:00 40 [Note] [MY-012478] [InnoDB] DDL log delete : 1087
2024-10-23T15:20:16.291863+08:00 40 [Note] [MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 40
2024-10-23T15:20:16.291958+08:00 40 [Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 40

Dropping an index，Rebuilds Table 为 NO，Only Modifies Metadata 为 YES

 root@localhost [ddl] 15:20:16 > drop index tddl_info on tddl ALGORITHM=INPLACE LOCK=NONE;
 Query OK, 0 rows affected (0.04 sec)
 Records: 0  Duplicates: 0  Warnings: 0

ddllog 输出

2024-10-23T15:21:00.950325+08:00 40 [Note] [MY-012472] [InnoDB] DDL log insert : [DDL record: FREE, id=1088, thread_id=40, space_id=200, index_id=452, page_no=5]
2024-10-23T15:21:00.976313+08:00 40 [Note] [MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 40
2024-10-23T15:21:00.976461+08:00 40 [Note] [MY-012479] [InnoDB] DDL log replay : [DDL record: FREE, id=1088, thread_id=40, space_id=200, index_id=452, page_no=5]
2024-10-23T15:21:00.980382+08:00 40 [Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 40

吐槽，create index/drop index 语法不一样，不让用逗号，搞得都报错好几轮。有杀气！！！

COPY

COPY 就不介绍啦，反正就是重建啦，上一篇有写。

 Query OK, 2 rows affected (0.04 sec)

这个 rows affected 有数字就是 COPY 啦，没数字，然后重建就是 INPLACE 啦

空间要求

仅修改元数据的不解释（比如 Instant）。

inplace 算法：Rebuilds Table 为 YES，Only Modifies Metadata 为 NO 的情况下要自身 ibd + 复制 ibd 文件（完整数据） + 中间表（空） + row log 的大小

COPY 算法：自身 ibd + 复制 ibd 文件（完整数据） + 中间表（空）

8.0.27 及以后 innodb_ddl_buffer_size 变量定义了在线 DDL 操作的最大缓冲区大小（创建和重建二级索引用哒！！不要搞错啦）

8.0.27 前是 innodb_sort_buffer_size 定义

row log 大小

根据 innodb_sort_buffer_size
的值，临时日志文件会按需扩展，最大值由innodb_online_alter_log_max_size
（超过这个报错 DB_ONLINE_LOG_TOO_BIG）指定。

总结

哎呀，上面写啦，自己看吧！！

一定要注意使用的算法哦！

就这样啦，ONLINE DDL 就此讲完啦，下一个系列就讲别的啦。

感谢您的观看！！！

参考

https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

文章转载自00后DBA实录，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

ONLINE DDL 收尾篇|如何有效实现 ONLINE

前言

操作生产变更 DDL 前

如何判断该 DDL 变更会使用什么算法

先从官方文档判断

实践

Instant

In Place

COPY

空间要求

row log 大小

总结

参考

评论