ONLINE DDL 的要求和限制

00后DBA实录 2024-10-21

前言

有小伙伴不太懂 DDL 算法的实现，并在 ONLINE DDL 期间触发 DB_ONLINE_LOG_TOO_BIG 错误。

我这里简单讲讲 DDL 算法各阶段过程，并讲讲限制和要求。

首先清楚一下概念：

ONLINE DDL 是对于 DML 而言，是否并发

COPY 算法是需要临时表的，且不允许并发DML，不属于 ONLINE DDL。

INPLACE 算法并不是所有操作都允许并发 DML，就像上一篇写的 INPLACE 降级 COPY的案例，部分操作需要临时数据文件，当然这需要要从 INSTANT 降级为 INPLACE ，INSTANT 只需要修改元数据就可以了。

DDL 算法简介

COPY 算法

我们先看看 COPY 算法吧

让先我们看看官方文档对此的解释：

这里大概描述了一下 COPY 算法的过程。

我总结一下 COPY 实现方式叭：

1.等待当前修改操作完成：在使用 COPY 算法的 ALTER TABLE 操作开始之前，它会等待其他正在修改该表的操作完成。

2.创建表的副本：一旦确认表没有其他正在进行的修改操作，MySQL 会在数据库目录中创建一个表的副本，用于存储修改后的表结构和数据。

3.应用更改到副本表：所有的表结构更改都首先应用到这个表的副本中。

4.数据复制：将原始表中的数据复制到副本表中。

5.重命名并替换表：数据复制完成后，MySQL 会删除原始表，并将副本表重命名为原始表的名字。

6.数据重定向：在执行 ALTER TABLE 操作时，原始表仍然允许读取操作，但在操作开始后产生的更新和写入会被阻塞，直到副本表准备就绪。然后这些操作会自动重定向到新的副本表。

7.独占锁定点：在操作的最后阶段，MySQL 需要清除表和表定义缓存中过时的结构信息。在这一点上，它会阻塞所有新的读取和写入操作，并等待当前的读取操作完成，直到获取独占锁。

ALTER TABLE 操作使用 COPY 算法时，会阻止同时进行的 DML 操作（如 INSERT、UPDATE 等）。虽然查询操作仍然允许，但这些操作相当于在 LOCK=SHARED 的限制下运行（允许查询，但禁止 DML）。如果使用 LOCK=EXCLUSIVE，则会阻止所有查询和 DML 操作。

对于 InnoDB 表，使用 COPY 算法的操作会增加表空间使用，所需额外空间相当于数据和索引的大小。在共享表空间中占用更多的空间，且这些空间不会像独立表空间那样在操作完成后释放。

所以总结来说，COPY 算法全程阻塞 DML ，在最后阶段之前允许 DQL，占用空间为数据+索引。

ONLINE DDL 算法

•阶段 1：

在初始化阶段，服务器将根据存储引擎功能、语句中指定的操作以及用户指定的 ALGORITHM 和 LOCK 选项来确定操作期间允许的并发量。在此阶段，将使用共享的可升级元数据锁来保护当前表定义。

•阶段 2：

在此阶段，语句已准备好并执行。元数据锁是否升级为排他锁取决于初始化阶段评估的因素。如果需要排他元数据锁，则只会在语句准备期间短暂使用。

•阶段 3：提交表定义

在提交表定义阶段，元数据锁升级为排他锁，以驱逐旧表定义并提交新表定义。一旦授予，排他元数据锁的持续时间很短。

由于上述独占元数据锁要求，在线 DDL 操作可能必须等待持有表上元数据锁的并发事务提交或回滚。

在 DDL 操作之前或期间启动的事务可以持有正在更改的表上的元数据锁。对于长时间运行或不活动的事务，在线 DDL 操作可能会因等待独占元数据锁而超时。此外，在线 DDL 操作请求的待处理独占元数据锁会阻止表上的后续事务。

官方文档给出的非常的简单，我们概括一下过程叭：

DDL 语句的执行分为以下几个阶段，有时候 prepare 和 perform 阶段可以在 commit 之前反复执行：

1.prepare：拿到对应表的 MDL X 锁，创建所需的对象，并把 DDL log 写入 mysql.innodb_ddl_log ；

2.perform：然后将 MDL X 锁降级为 MDL S 锁，执行 DDL 操作；

3.commit：再次将 MDL S 锁升级为 MDL X 锁，更新数据字典并提交数据字典事务，释放 MDL 锁；

4.Post-DDL：当 DDL 事务提交或者回滚的时候，会调用 post_ddl 进行日志重放或从 mysql.innodb_ddl_log 中删除 DDL log

创建或重建二级索引的在线 DDL 操作的工作流程包括：

•扫描聚集索引并将数据写入临时排序文件

•对数据进行排序

•将排序后的数据从临时排序文件加载到二级索引中

可用于扫描聚集索引的并行线程数由 innodb_parallel_read_threads 变量定义。默认设置为 4。最大设置为 256，这是所有会话的最大数量。

扫描聚集索引的实际线程数是 innodb_parallel_read_threads 设置定义的线程数或要扫描的索引子树的数量（以较小者为准）。如果达到线程限制，会话将回退到使用单线程

排序和加载数据的并行线程数由 MySQL 8.0.27 中引入的 innodb_ddl_threads 变量控制。默认设置为 4。在 MySQL 8.0.27 之前，排序和加载操作是单线程的。

DDL log

在 MySQL8.0 之前，元数据在 Server layer（服务层）是存储在 MyISAM 引擎的系统表里，然而事务存储引擎 innodb 是自己存储一份元数据。这就会出现不一致性的问题的诸多问题。

MySQL8.0 为了解决这些问题，就引入了事务型 DDL。为了事务型 DDL 的提交和回滚，InnoDB 存储引擎引入了一个表 ddl_log（充当 redolog 或 undolog，或二者兼有），当然我们无法修改。

这里不做过多介绍，我们知道有这个日志就知道了。

算法执行过程详解

测试表

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

mysql提供了选项将 ddl log 写入 stderr，因为我们已经设置了error_log，所以会导向 errorlog，用 errorlog 查看

innodb_print_ddl_logs

启用此选项会导致 MySQL 将 DDL 日志写入 stderr。

再将日志详细级别设置为最高，方便查看

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

COPY 算法执行过程

在开始前先看看 al_ddl 表和索引的 id，等会有用

select * from  information_schema.innodb_tables  t join information_schema.innodb_indexes i on t.table_id=i.table_id  where t.name like 'ddl/al\_ddl';                 
+----------+------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+----------+---------+----------+------+----------+---------+-------+-----------------+                 
| TABLE_ID | NAME       | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS | INDEX_ID | NAME    | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |                 
+----------+------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+----------+---------+----------+------+----------+---------+-------+-----------------+                 
|     1233 | ddl/al_ddl |   33 |      5 |   171 | Dynamic    |             0 | Single     |            0 |                  0 |      404 | PRIMARY |     1233 |    3 |        4 |       4 |   171 |              50 |                 
|     1233 | ddl/al_ddl |   33 |      5 |   171 | Dynamic    |             0 | Single     |            0 |                  0 |      405 | info    |     1233 |    0 |        2 |       5 |   171 |              50 |                 
+----------+------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+----------+---------+----------+------+----------+---------+-------+-----------------+                 
2 rows in set (0.01 sec)

TABLE_ID:1233

SPACE：171

INDEX_ID: primary 是 404 ，info 是 405

其他的不管。

我们直接使用昨天的 varchar(64) ，这是必定重建表的。

root@localhost [ddl] 18:30:37 > alter table al_ddl modify info varchar(64),ALGORITHM=copy;                 
Query OK, 2 rows affected (0.13 sec)                 
Records: 2  Duplicates: 0  Warnings: 0

再看看重建后的内容

root@localhost [ddl] 18:57:28 > select * from  information_schema.innodb_tables  t join information_schema.innodb_indexes i on t.table_id=i.table_id  where t.name like 'ddl/al\_ddl';                 
+----------+------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+----------+---------+----------+------+----------+---------+-------+-----------------+                 
| TABLE_ID | NAME       | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS | INDEX_ID | NAME    | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |                 
+----------+------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+----------+---------+----------+------+----------+---------+-------+-----------------+                 
|     1234 | ddl/al_ddl |   33 |      5 |   172 | Dynamic    |             0 | Single     |            0 |                  0 |      406 | PRIMARY |     1234 |    3 |        4 |       4 |   172 |              50 |                 
|     1234 | ddl/al_ddl |   33 |      5 |   172 | Dynamic    |             0 | Single     |            0 |                  0 |      407 | info    |     1234 |    0 |        2 |       5 |   172 |              50 |                 
+----------+------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+----------+---------+----------+------+----------+---------+-------+-----------------+                 
2 rows in set (0.01 sec)

TABLE_ID:1234

SPACE：172

INDEX_ID: primary 是 406 ，info 是 407

即使我们不懂源码内容，我们也可以利用这些信息反推过程。

看看日志输出内容：

2024-10-21T18:58:43.501184+08:00 27 [Note] [MY-012473] [InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=887, thread_id=27, space_id=172, old_file_path=./ddl/#sql-2777_1b.ibd]                 
2024-10-21T18:58:43.501420+08:00 27 [Note] [MY-012478] [InnoDB] DDL log delete : 887                 
2024-10-21T18:58:43.545452+08:00 27 [Note] [MY-012477] [InnoDB] DDL log insert : [DDL record: REMOVE CACHE, id=888, thread_id=27, table_id=1234, new_file_path=ddl/#sql-2777_1b]                 
2024-10-21T18:58:43.545593+08:00 27 [Note] [MY-012478] [InnoDB] DDL log delete : 888                 
2024-10-21T18:58:43.558453+08:00 27 [Note] [MY-012472] [InnoDB] DDL log insert : [DDL record: FREE, id=889, thread_id=27, space_id=172, index_id=406, page_no=4]                 
2024-10-21T18:58:43.558561+08:00 27 [Note] [MY-012478] [InnoDB] DDL log delete : 889                 
2024-10-21T18:58:43.563052+08:00 27 [Note] [MY-012472] [InnoDB] DDL log insert : [DDL record: FREE, id=890, thread_id=27, space_id=172, index_id=407, page_no=5]                 
2024-10-21T18:58:43.563181+08:00 27 [Note] [MY-012478] [InnoDB] DDL log delete : 890                 
2024-10-21T18:58:43.574793+08:00 27 [Note] [MY-012474] [InnoDB] DDL log insert : [DDL record: RENAME SPACE, id=891, thread_id=27, space_id=171, old_file_path=./ddl/#sql2-2777-1b.ibd, new_file_path=./ddl/al_ddl.ibd]                 
2024-10-21T18:58:43.574914+08:00 27 [Note] [MY-012478] [InnoDB] DDL log delete : 891                 
2024-10-21T18:58:43.578011+08:00 27 [Note] [MY-012476] [InnoDB] DDL log insert : [DDL record: RENAME TABLE, id=892, thread_id=27, table_id=1233, old_file_path=ddl/#sql2-2777-1b, new_file_path=ddl/al_ddl]                 
2024-10-21T18:58:43.578120+08:00 27 [Note] [MY-012478] [InnoDB] DDL log delete : 892                 
2024-10-21T18:58:43.593940+08:00 27 [Note] [MY-012474] [InnoDB] DDL log insert : [DDL record: RENAME SPACE, id=893, thread_id=27, space_id=172, old_file_path=./ddl/al_ddl.ibd, new_file_path=./ddl/#sql-2777_1b.ibd]                 
2024-10-21T18:58:43.594042+08:00 27 [Note] [MY-012478] [InnoDB] DDL log delete : 893                 
2024-10-21T18:58:43.595969+08:00 27 [Note] [MY-012476] [InnoDB] DDL log insert : [DDL record: RENAME TABLE, id=894, thread_id=27, table_id=1234, old_file_path=ddl/al_ddl, new_file_path=ddl/#sql-2777_1b]                 
2024-10-21T18:58:43.596017+08:00 27 [Note] [MY-012478] [InnoDB] DDL log delete : 894                 
2024-10-21T18:58:43.607872+08:00 27 [Note] [MY-012475] [InnoDB] DDL log insert : [DDL record: DROP, id=895, thread_id=27, table_id=1233]                 
2024-10-21T18:58:43.607955+08:00 27 [Note] [MY-012473] [InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=896, thread_id=27, space_id=171, old_file_path=./ddl/#sql2-2777-1b.ibd]                 
2024-10-21T18:58:43.612935+08:00 27 [Note] [MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 27                 
2024-10-21T18:58:43.612995+08:00 27 [Note] [MY-012479] [InnoDB] DDL log replay : [DDL record: DELETE SPACE, id=896, thread_id=27, space_id=171, old_file_path=./ddl/#sql2-2777-1b.ibd]                 
2024-10-21T18:58:43.614730+08:00 27 [Note] [MY-012479] [InnoDB] DDL log replay : [DDL record: DROP, id=895, thread_id=27, table_id=1233]                 
2024-10-21T18:58:43.616737+08:00 27 [Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 27                 
2024-10-21T18:58:43.616788+08:00 27 [Note] [MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 27                 
2024-10-21T18:58:43.616822+08:00 27 [Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 27

让我们来逐一讲解：

我们前面讲到，ddllog有的时候是redo，有的时候是undo，这里是 undo

先要复制一个一模一样的表（加上 alter 更改内容）：

•[DDL record: DELETE SPACE, id=887, thread_id=27, space_id=172, old_file_path=./ddl/#sql-2777_1b.ibd] ：

–DELETE SPACE：写入删除创建的临时表的表空间信息

–thread_id=27：ddl 线程 id

–id=887：插入到 ddl log 的记录 id

–space_id=172：前面说到的流程里面 COPY 是不是写了要建临时表，这就是那个建的临时表的 SPACE id

–old_file_path=./ddl/#sql-2777_1b.ibd：表空间物理文件的物理位置

•DDL log delete : 887：记录一条删除 ddl log，方便后面 DDL 事务提交后删除 ddllog，里面的内容，如果失败了就会回滚 delete 的条目，保留 insert 条目，下面这些相同的 delete 不做介绍

•[DDL record: REMOVE CACHE, id=888, thread_id=27, table_id=1234, new_file_path=ddl/#sql-2777_1b]

–REMOVE CACHE：写入移除 dictionary cache 的 undo ddllog

–table_id=1234

–new_file_path=ddl/#sql-2777_1b：新临时表的名称

•[DDL record: FREE, id=889, thread_id=27, space_id=172, index_id=406, page_no=4]

–FREE：记录一条删除操作，这应该指的是在临时表的表空间建立索引的操作

–space_id=172：临时表空间的 id

–index_id=406：这是索引的 id，对应于 PRIMARY KEY

–page_no=4：B树根节点的页 id

•[DDL record: FREE, id=890, thread_id=27, space_id=172, index_id=407, page_no=5]

–FREE：记录一条删除操作，这应该指的是在临时表的表空间建立索引的操作

–space_id=172：临时表空间的 id

–index_id=407：这是索引的 id，对应于 info 的普通索引

–page_no=5：B树根节点的页 id

重命名操作：

•[DDL record: RENAME SPACE, id=891, thread_id=27, space_id=171, old_file_path=./ddl/#sql2-2777-1b.ibd, new_file_path=./ddl/al_ddl.ibd]

[DDL record: RENAME TABLE, id=892, thread_id=27, table_id=1233, old_file_path=ddl/#sql2-2777-1b, new_file_path=ddl/al_ddl]

[DDL record: RENAME SPACE, id=893, thread_id=27, space_id=172, old_file_path=./ddl/al_ddl.ibd, new_file_path=./ddl/#sql-2777_1b.ibd]

[DDL record: RENAME TABLE, id=894, thread_id=27, table_id=1234, old_file_path=ddl/al_ddl, new_file_path=ddl/#sql-2777_1b]

注意看这是一个 ddl/#sql-2777_1b 表和表空间文件与 ddl/al_ddl 表和表空间文件的互换名字操作（使用 ddl/#sql2-2777-1b 作为中间表）

因为是 undo 嘛，所以是反的，实际上是这么一个操作流程

类似于一下操作：

想要交换 al_ddl 和 #sql-2777_1b 的表和表空间名字就需要借 #sql2-2777-1b 作为中间表

create table al_ddl(···);                 
create table #sql-2777_1b(···);                 
create table #sql2-2777-1b(···);                 
rename table al_ddl to #sql2-2777-1b,#sql-2777_1b to al_ddl,#sql2-2777-1b to #sql-2777_1b;

我们顺一下逻辑叭，只需要将 #sql2-2777-1b（原 al_ddl）重命名为 al_ddl，然后将 al_ddl（原 #sql-2777_1b）重命名为 sql-2777_1b 不就回来了嘛。

对不对嘛，我是不是很聪明，反推逻辑！！！！

DDL log insert : [DDL record: DROP, id=895, thread_id=27, table_id=1233]
DDL log insert : [DDL record: DELETE SPACE, id=896, thread_id=27, space_id=171, old_file_path=./ddl/#sql2-2777-1b.ibd]

DROP/DELETE SPACE：记录一条删除操作，删表和表空间

table_id：1233

space_id=171：原表

old_file_path=./ddl/#sql2-2777-1b.ibd：原表空间

然后是在 Post-DDL 阶段

DDL log post ddl : begin for thread id : 27
DDL log replay : [DDL record: DELETE SPACE, id=896, thread_id=27, space_id=171, old_file_path=./ddl/#sql2-2777-1b.ibd]
DDL log replay : [DDL record: DROP, id=895, thread_id=27, table_id=1233]

DELETE SPACE/DROP：真正删除表空间/表

replay ：重放

space_id：原表

old_file_path=./ddl/#sql2-2777-1b.ibd：原 al_ddl，这是重命名之后的表，刚才重命名逻辑那写了

table_id=1233：表 id 1233，对于刚查到的表id，没问题

至此 DDL 事务提交

下面就是 post ddl 去清空 ddllog了

[MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 27
[MY-012486] [InnoDB] DDL log post ddl : end for thread id : 27

INPLACE 算法执行过程

下面看看 INPLACE 的执行过程，INPLACE 算法分为两种，一种是需要临时数据文件，一种是不需要临时数据文件

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 降级为 InPlace，Rebuilds Table 要为 yes，Only Modifies Metadata 要为 no

先查表空间文件

root@localhost [ddl] 20:45:08 > select * from  information_schema.innodb_tables  t join information_schema.innodb_indexes i on t.table_id=i.table_id  where t.name like 'ddl/al\_ddl';                 
+----------+------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+----------+---------+----------+------+----------+---------+-------+-----------------+                 
| TABLE_ID | NAME       | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS | INDEX_ID | NAME    | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |                 
+----------+------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+----------+---------+----------+------+----------+---------+-------+-----------------+                 
|     1239 | ddl/al_ddl |   33 |      5 |   177 | Dynamic    |             0 | Single     |            0 |                  0 |      416 | PRIMARY |     1239 |    3 |        4 |       4 |   177 |              50 |                 
|     1239 | ddl/al_ddl |   33 |      5 |   177 | Dynamic    |             0 | Single     |            0 |                  0 |      417 | info    |     1239 |    0 |        2 |       5 |   177 |              50 |                 
+----------+------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+----------+---------+----------+------+----------+---------+-------+-----------------+                 
2 rows in set (0.01 sec)

TABLE_ID：1239

INDEX_ID：416、417

SPACE：177

我们就测试一下设置字段 not null 吧

root@localhost [ddl] 20:32:10 > alter table al_ddl modify info varchar(64) not null, ALGORITHM=INPLACE, LOCK=NONE;                 
Query OK, 0 rows affected (0.07 sec)                 
Records: 0  Duplicates: 0  Warnings: 0

可以看到没有重建表，rows affected 为 0

Query OK, 0 rows affected (0.07 sec)

再看一下执行后的结果

root@localhost [ddl] 20:45:30 > select * from  information_schema.innodb_tables  t join information_schema.innodb_indexes i on t.table_id=i.table_id  where t.name like 'ddl/al\_ddl';                 
+----------+------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+----------+---------+----------+------+----------+---------+-------+-----------------+                 
| TABLE_ID | NAME       | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS | INDEX_ID | NAME    | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |                 
+----------+------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+----------+---------+----------+------+----------+---------+-------+-----------------+                 
|     1240 | ddl/al_ddl |   33 |      5 |   178 | Dynamic    |             0 | Single     |            0 |                  0 |      418 | PRIMARY |     1240 |    3 |        4 |       4 |   178 |              50 |                 
|     1240 | ddl/al_ddl |   33 |      5 |   178 | Dynamic    |             0 | Single     |            0 |                  0 |      419 | info    |     1240 |    0 |        2 |       5 |   178 |              50 |                 
+----------+------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+----------+---------+----------+------+----------+---------+-------+-----------------+                 
2 rows in set (0.01 sec)

TABLE_ID：1240

INDEX_ID：418、419

SPACE：178

看一下 ddl log

2024-10-21T20:45:30.097028+08:00 26 [Note] [MY-012473] [InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=951, thread_id=26, space_id=178, old_file_path=./ddl/#sql-ib1239-1822244020.ibd]                 
2024-10-21T20:45:30.097159+08:00 26 [Note] [MY-012478] [InnoDB] DDL log delete : 951                 
2024-10-21T20:45:30.125997+08:00 26 [Note] [MY-012477] [InnoDB] DDL log insert : [DDL record: REMOVE CACHE, id=952, thread_id=26, table_id=1240, new_file_path=ddl/#sql-ib1239-1822244020]                 
2024-10-21T20:45:30.126057+08:00 26 [Note] [MY-012478] [InnoDB] DDL log delete : 952                 
2024-10-21T20:45:30.131892+08:00 26 [Note] [MY-012472] [InnoDB] DDL log insert : [DDL record: FREE, id=953, thread_id=26, space_id=178, index_id=418, page_no=4]                 
2024-10-21T20:45:30.132002+08:00 26 [Note] [MY-012478] [InnoDB] DDL log delete : 953                 
2024-10-21T20:45:30.149554+08:00 26 [Note] [MY-012472] [InnoDB] DDL log insert : [DDL record: FREE, id=954, thread_id=26, space_id=178, index_id=419, page_no=5]                 
2024-10-21T20:45:30.149672+08:00 26 [Note] [MY-012478] [InnoDB] DDL log delete : 954                 
2024-10-21T20:45:30.185247+08:00 26 [Note] [MY-012475] [InnoDB] DDL log insert : [DDL record: DROP, id=955, thread_id=26, table_id=1239]                 
2024-10-21T20:45:30.210165+08:00 26 [Note] [MY-012474] [InnoDB] DDL log insert : [DDL record: RENAME SPACE, id=956, thread_id=26, space_id=177, old_file_path=./ddl/#sql-ib1240-1822244021.ibd, new_file_path=./ddl/al_ddl.ibd]                 
2024-10-21T20:45:30.210332+08:00 26 [Note] [MY-012478] [InnoDB] DDL log delete : 956                 
2024-10-21T20:45:30.214943+08:00 26 [Note] [MY-012476] [InnoDB] DDL log insert : [DDL record: RENAME TABLE, id=957, thread_id=26, table_id=1239, old_file_path=ddl/#sql-ib1240-1822244021, new_file_path=ddl/al_ddl]                 
2024-10-21T20:45:30.215038+08:00 26 [Note] [MY-012478] [InnoDB] DDL log delete : 957                 
2024-10-21T20:45:30.226149+08:00 26 [Note] [MY-012474] [InnoDB] DDL log insert : [DDL record: RENAME SPACE, id=958, thread_id=26, space_id=178, old_file_path=./ddl/al_ddl.ibd, new_file_path=./ddl/#sql-ib1239-1822244020.ibd]                 
2024-10-21T20:45:30.270662+08:00 26 [Note] [MY-012478] [InnoDB] DDL log delete : 958                 
2024-10-21T20:45:30.275860+08:00 26 [Note] [MY-012476] [InnoDB] DDL log insert : [DDL record: RENAME TABLE, id=959, thread_id=26, table_id=1240, old_file_path=ddl/al_ddl, new_file_path=ddl/#sql-ib1239-1822244020]                 
2024-10-21T20:45:30.275975+08:00 26 [Note] [MY-012478] [InnoDB] DDL log delete : 959                 
2024-10-21T20:45:30.284182+08:00 26 [Note] [MY-012475] [InnoDB] DDL log insert : [DDL record: DROP, id=960, thread_id=26, table_id=1239]                 
2024-10-21T20:45:30.284268+08:00 26 [Note] [MY-012473] [InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=961, thread_id=26, space_id=177, old_file_path=./ddl/#sql-ib1240-1822244021.ibd]                 
2024-10-21T20:45:30.344855+08:00 26 [Note] [MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 26                 
2024-10-21T20:45:30.344912+08:00 26 [Note] [MY-012479] [InnoDB] DDL log replay : [DDL record: DELETE SPACE, id=961, thread_id=26, space_id=177, old_file_path=./ddl/#sql-ib1240-1822244021.ibd]                 
2024-10-21T20:45:30.352577+08:00 26 [Note] [MY-012479] [InnoDB] DDL log replay : [DDL record: DROP, id=960, thread_id=26, table_id=1239]                 
2024-10-21T20:45:30.352611+08:00 26 [Note] [MY-012479] [InnoDB] DDL log replay : [DDL record: DROP, id=955, thread_id=26, table_id=1239]                 
2024-10-21T20:45:30.355508+08:00 26 [Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 26

这次不用 undo 格式了，我正向看：

1.新建 ibd 文件 ./ddl/#sql-ib1239-1822244020.ibd，表空间文件 id 为 178

2.在 schema ddl 新建表 #sql-ib1239-1822244020 ，表 id 为 1240

3.创建索引 PRIMARY，表空间 178 索引 id 418

4.创建索引 info，表空间 178 索引 id 419

5.复制表 1239 建一个中间表

6.al_ddl 重命名为 #sql-ib1240-1822244021，#sql-ib1239-1822244020 重命名为 al_ddl.ibd，

7.下面两条正向就不需要了：

2024-10-21T20:45:30.284182+08:00 26 [Note] [MY-012475] [InnoDB] DDL log insert : [DDL record: DROP, id=960, thread_id=26, table_id=1239]
2024-10-21T20:45:30.284268+08:00 26 [Note] [MY-012473] [InnoDB] DDL log insert : [DDL record: DELETE SPACE, id=961, thread_id=26, space_id=177, old_file_path=./ddl/#sql-ib1240-1822244021.ibd]

8.然后开启 post ddl

删除 id=961 的 #sql-ib1240-1822244021.ibd

删除 id=960 的 table_id=1239

删除 id=955 的中间表

这时候小伙伴们就要问了，诶，这有什么区别吗？？

当然有喽，COPY 算法执行过程是在 Server 层，而 INPLACE 算法是在存储引擎层（仅 INNODB），是存储引擎自己的逻辑，INPLACE 算法允许DML实现了 row log 的增量日志。

下面看看不重建表的 INPLACE 算法执行过程

先看一下 table id 和 space id

root@localhost [ddl] 21:05:21 > select * from  information_schema.innodb_tables  t join information_schema.innodb_indexes i on t.table_id=i.table_id  where t.name like 'ddl/al\_ddl';                 
+----------+------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+----------+---------+----------+------+----------+---------+-------+-----------------+                 
| TABLE_ID | NAME       | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS | INDEX_ID | NAME    | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |                 
+----------+------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+----------+---------+----------+------+----------+---------+-------+-----------------+                 
|     1241 | ddl/al_ddl |   33 |      5 |   179 | Dynamic    |             0 | Single     |            0 |                  0 |      420 | PRIMARY |     1241 |    3 |        4 |       4 |   179 |              50 |                 
|     1241 | ddl/al_ddl |   33 |      5 |   179 | Dynamic    |             0 | Single     |            0 |                  0 |      421 | info    |     1241 |    0 |        2 |       5 |   179 |              50 |                 
+----------+------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+----------+---------+----------+------+----------+---------+-------+-----------------+                 
2 rows in set (0.01 sec)

这次执行仅更新元数据不重建表，却不是 INSTANT 算法的扩展 varchar 宽度

root@localhost [ddl] 22:29:17 > show create table al_ddl;                 
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+                 
| Table  | Create Table                                                                                                                                                                                   |                 
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+                 
| al_ddl | CREATE TABLE `al_ddl` (                 
  `id` int NOT NULL,                 
  `info` varchar(64) DEFAULT NULL,                 
  PRIMARY KEY (`id`),                 
  KEY `info` (`info`)                 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |                 
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+                 
1 row in set (0.00 sec)                 
                 
root@localhost [ddl] 22:32:10 > alter table al_ddl modify info varchar(100), ALGORITHM=INPLACE, LOCK=NONE;                 
Query OK, 0 rows affected (0.02 sec)                 
Records: 0  Duplicates: 0  Warnings: 0

可以看到 ddllog 只有调用一下 post ddl

2024-10-21T22:32:57.606867+08:00 26 [Note] [MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 26                 
2024-10-21T22:32:57.606954+08:00 26 [Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 26

看看表结构

root@localhost [ddl] 22:32:57 > select * from  information_schema.innodb_tables  t join information_schema.innodb_indexes i on t.table_id=i.table_id  where t.name like 'ddl/al\_ddl';                 
+----------+------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+----------+---------+----------+------+----------+---------+-------+-----------------+                 
| TABLE_ID | NAME       | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS | INDEX_ID | NAME    | TABLE_ID | TYPE | N_FIELDS | PAGE_NO | SPACE | MERGE_THRESHOLD |                 
+----------+------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+----------+---------+----------+------+----------+---------+-------+-----------------+                 
|     1241 | ddl/al_ddl |   33 |      5 |   179 | Dynamic    |             0 | Single     |            0 |                  0 |      420 | PRIMARY |     1241 |    3 |        4 |       4 |   179 |              50 |                 
|     1241 | ddl/al_ddl |   33 |      5 |   179 | Dynamic    |             0 | Single     |            0 |                  0 |      421 | info    |     1241 |    0 |        2 |       5 |   179 |              50 |                 
+----------+------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+----------+---------+----------+------+----------+---------+-------+-----------------+                 
2 rows in set (0.01 sec)

没有变化

INSTANT 算法执行过程

下面看看 INSTANT 算法叭

增加字段是不需要重建表的

Adding a column Yes* Yes No* Yes* Yes

我们先看看增加字段的算法过程：

root@localhost [ddl] 22:37:34 > alter table al_ddl add age int, ALGORITHM=INSTANT;                 
Query OK, 0 rows affected (0.11 sec)                 
Records: 0  Duplicates: 0  Warnings: 0

可以看到 DDL log 为

2024-10-21T22:38:33.754442+08:00 26 [Note] [MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 26                 
2024-10-21T22:38:33.754625+08:00 26 [Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 26

再看看删除字段的算法过程

Dropping a column Yes* Yes Yes Yes Yes

root@localhost [ddl] 22:38:33 > alter table al_ddl drop age, ALGORITHM=INSTANT;                 
Query OK, 0 rows affected (0.02 sec)                 
Records: 0  Duplicates: 0  Warnings: 0

可以看到虽然需要重建表，但是并没有使用临时表或临时数据文件，但是该算法依然很快

2024-10-21T22:42:10.850513+08:00 26 [Note] [MY-012485] [InnoDB] DDL log post ddl : begin for thread id : 26                 
2024-10-21T22:42:10.850624+08:00 26 [Note] [MY-012486] [InnoDB] DDL log post ddl : end for thread id : 26

DDL 操作失败原因

在线 DDL 操作失败通常是由于以下情况之一造成的：

•ALGORITHM 子句指定了与特定 DDL 操作或存储引擎不兼容的算法。

•LOCK 子句指定了与特定 DDL 操作不兼容的低级锁定（SHARED或NONE）。

•在等待对表的独占锁期间发生超时，在 DDL 操作的初始和最后阶段可能短暂需要该锁。

•tmpdir 或 innodb_tmpdir 文件系统磁盘空间不足，而 MySQL 在索引创建期间将临时排序文件写入磁盘。

•操作耗时较长，且并发 DML 对表的修改过多，导致临时联机日志的大小超出 innodb_online_alter_log_max_size 配置选项的值。这种情况会导致 DB_ONLINE_LOG_TOO_BIG错误。

•并发 DML 对表进行原始表定义允许的更改，但新表定义不允许的更改。当 MySQL 尝试应用并发 DML 语句的所有更改时，操作只会在最后失败。例如，您可能在创建唯一索引时将重复值插入列中，或者您可能在为该列创建主键索引时将 NULL 值插入列中。并发 DML 所做的更改优先，ALTER TABLE 操作将有效回滚。

参考

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

https://dev.mysql.com/doc/refman/8.0/en/alter-table.html

http://mysql.taobao.org/monthly/2021/07/05/

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

ONLINE DDL 的要求和限制

评论