一、前言

Github：https://github.com/HealerJean

博客：http://blog.healerjean.com

DDL（data definition language）数据库定义语言：

其实就是我们在创建表的时候用到的一些sql，比如说：CREATE、ALTER、DROP等。DDL主要是用在定义或改变表的结构，数据类型，表之间的链接和约束等初始化工作上

二、DDL 执行方式

MySQL DDL 表结构变更的方式多种多样，每种方式都有其独特的优势和局限性。在选择合适的 DDL 方式时，需要根据具体的业务场景、数据库版本和性能要求来综合考虑。对于大表或高并发场景，建议优先考虑 Online DDL 或 pt- osc 方式以减少对业务的影响。同时，随着 MySQL 版本的更新迭代，新的 DDL 执行方式（如 INSTANT DDL）也将为数据库管理带来更多便利和高效性。

2、Online DDL

Online DDL（在线数据定义语言）是一种在不中断数据库服务的情况下修改数据库结构的技术。这种技术能够支持 DDL（数据定义语言）操作与 DML（数据操作语言）操作同时进行，从而减少对业务产生的影响。以下是对 Online DDL 原理的详细说明：

1）原理

**1、数据控制 **

1、Online DDL 操作被封装在一个事务中，确保数据的一致性和完整性。如果操作成功，则提交事务；如果失败，则回滚事务。

2、在执行 Online DDL期间，MySQL 会记录 DDL 操作对表数据产生的增量变更（如新增、修改、删除的行）。这些变更被记录在内部日志中，以便在 DDL 操作完成后重放这些变更，以保持数据的一致性。

**2、锁表机制 **

1、虽然 Online DDL 减少了锁表时间，但在某些阶段（如准备阶段、提交阶段）仍然需要锁表。MySQL 会尽可能缩短这些锁表时间，并使用不同类型的锁（如共享锁、排他锁）来控制对表的访问。

2、MySQL 会根据需要选择合适的锁类型和级别。例如，在创建索引时，MySQL 可能会使用共享锁来允许读取操作继续进行，同时阻止写入操作；在需要修改表结构时，则可能会使用排他锁来确保数据的一致性。然而，这些锁都是在非常精细的控制下进行的，以避免对业务产生过大的影响

2、表重建的优化

1、在进行结构变更时，MySQL 会尽量减少对原表的影响。对于支持 Online DDL的操作，MySQL 会尝试使用一种称为 “ In-place ”的方式，即直接在原表上修改，而不需要复制整个表到临时表中。

2、对于某些不支持 In-place 的操作，MySQL 会采用一种称为“ Copy ”的方式，即创建一个新表，将原表数据复制到新表中，并在新表上执行 DDL 操作，然后替换原表。但在这个过程中，MySQL 会优化锁的使用，减少锁表时间。

2）优点：

1、减少系统停机时间：

1、传统的 DDL 操作通常需要锁定受影响的表，导致数据库在执行 DDL 操作期间无法对外提供服务，造成系统停机时间。而 Online DDL能够在不中断正常服务的情况下执行表结构的变更，从而显著减少系统停机时间，提高系统可用性。

2、减低业务风险： Online DDL可以避免由于长时间锁定表而导致的业务中断和故障，这对于对数据库连续性和可用性要求较高的业务尤为重要。通过减少停机时间和提高并发性能，Online DDL 能够降低业务运行的风险。

2、提高并发性能：

1、Online DDL 允许 DDL 操作与 DML（数据操作语言）操作并发执行，减少对表的锁定时间。这提高了数据库的并发性能，使得用户可以在不受影响的情况下继续进行数据操作，提升了系统的整体性能和响应速度。

3、降低磁盘和IO消耗：

1、相较于传统的 table - copy 方式，Online DDL 中的 In- place 算法直接在原表上进行结构修改，无需创建新的临时表来存储数据。这种方式减少了磁盘空间的消耗和 IO 开销，提高了资源利用效率。

3）缺点：

**1、性能影响 **

1、尽管 Online DDL 减少了锁表时间，但在某些阶段仍然需要锁表。如果此时有其他事务正在访问该表，则可能需要等待锁释放，从而影响性能。

2、在执行 Online DDL 期间，由于需要记录增量日志、处理锁等操作，可能会对数据库性能产生一定影响。特别是在处理大表或复杂 DDL 操作时，性能影响可能更加明显。

b、增量日志大小有限制

1、在执行 Online DDL 时，MySQL 会记录增量日志。但这些日志的大小是有限制的，如果表数据变更量非常大，可能会超出日志的限制，从而影响操作的进行。

3、有可能造成主从延迟

1、在复制环境中，Online DDL 操作可能会在主库上产生大量的增量日志，这些日志需要同步到从库。如果网络延迟或从库处理能力不足，可能会导致主从延迟。

4、无法暂停，只能中断

一旦 Online DDL 操作开始，通常无法暂停。如果需要取消操作，只能中断并回滚事务。这可能会导致已经执行的部分变更被撤销，影响数据的一致性。

2、pt-osc

Percona Toolkit的pt-online-schema-change

1）原理

1、创建新表：根据原表结构创建一个新表，并在新表上执行所需的 DDL 操作（如添加列、修改列类型等）。

2、创建触发器：在原表上创建三个触发器，分别对应 INSERT、UPDATE、DELETE 操作，以捕获这些操作期间的数据变更，并将这些变更应用到新表中。

3、拷贝数据：将原表中的数据分块（chunk）拷贝到新表中。在拷贝过程中，原表上的触发器会确保任何新的 DML 操作都被同步到新表中。

4、切换表名：完成数据拷贝后，将原表重命名为一个临时名称，然后将新表重命名为原表的名称，最后删除原表的临时名称。

2）优点

1、减少业务影响：pt-osc 在执行 DDL 操作时，通过创建新表和触发器的方式，避免了直接对原表加锁，从而减少了对业务读写操作的影响。

2、数据一致性：通过触发器和分块拷贝数据的方式，pt-osc 确保了原表和新表在数据变更过程中的一致性。

3、灵活性：pt-osc 支持多种 DDL 操作，如添加列、修改列类型、添加索引等，提供了较高的灵活性。

4、易于使用：pt-osc 以命令行方式执行，操作简单，易于集成到自动化脚本中。

3）缺点

1、对存储不友好

答案：新临时表存放数据也需要空间（最大空间需求可能和原表一样），拷贝数据时还会产生大量 binlog，所以对于本来空间就紧张的实例而言，这方式真的是雪上加霜

2、执行时间长&磁盘 IO可能较高

原因：要拷贝全量数据，所以执行时间也会很长，需要将老表的所有数据都拷贝到新表上，这就意味着拷贝期间，磁盘IO可能较高

3、对表结构有要求

原因：pt - osc 要求表必须有主键或唯一索引，以确保触发器能够正确地同步数据变更。如果表不满足这些要求，pt - osc将无法执行。

4、如果运行过程中报错，无法从上一个位置继续

原因：pt - osc 在执行过程中，如果遇到错误导致中断，通常需要从头开始重新执行，因为它没有内置的断点续传机制。

5、在高负载或主从延迟过大时可能暂停操作

原因：为了减少对业务的影响，pt - osc 会检测服务器的负载和主从延迟情况。如果检测到负载过高或延迟过大，pt-osc 可能会暂停操作，等待条件改善后再继续。

3、COPY 方式（传统 DDL ）

1）原理：

1、在执行 DDL 操作前，MySQL 会创建一个临时表来存储变更后的表结构。

2、锁定原表，禁止 DML 操作（但允许查询）。

3、将原表数据复制到临时表。

4、释放锁，并将临时表重命名为原表名，原表被删除。

2）优点：

1、实现简单，适用于所有 MySQL 版本。

3）缺点：

1、DDL 操作期间，表被锁定，无法进行 DML 操作，对业务影响大。

2、复制全量数据可能导致执行时间较长，且磁盘 IO 较高。

2、INSTANT 方式（ MySQL 8.0及以上版本）

1）原理：

1、INSTANT DDL 是 MySQL 8.0 引入的一种 DDL 执行方式，它仅修改数据字典中的元数据，而不涉及数据复制或表重建。

2、因此，INSTANT DDL 几乎可以瞬间完成，且不会阻塞 DML 操作。

2）优点：

1、执行速度极快，几乎不影响业务。

2、无需额外的磁盘空间或复杂的操作过程。

3）缺点：

1、支持的 DDL 操作类型有限，目前主要包括添加字段、添加/删除生成列等。

三、DDL 操作

1、索引操作

Online DDL 索引操作

1）创建普通二级索引

⬤ Online DDL：上图图，可以看出，这会选择In Place的方式执行，整个过程，只会涉及到拷贝二级索引列相关的数据用于创建索引，所以需要拷贝的数据，相对于 pt - osc 而已，肯定会少很多，反过来说，执行需要的时间也相对会少。如果没从库，不存在复制延迟的问题，那选择 Online DDL 显然会比pt - osc 更优；但如果有从库，那复制延迟的问题，自然是需要考虑的，而且大表复制延迟的时间，当然也会较长，如果接受不了延迟，那直接选 pt - osc 就好。

⬤ pt - osc：复制整个表的数据用于建新表，优势是有从库时，几乎不存在复制延迟的问题；劣势也很明显，因为拷贝整个表的数据，所以时间长，同时磁盘 IO 也会变高。

2）删除索引、索引重命名

⬤ Online DDL：从图可以看到 Only Modifies Metadata对应的是 YES，也就意味着仅修改元数据，速度非常快，几乎瞬间完成，必选Online DDL。

⬤ pt - osc：直接无视

3）唯一索引及主键操作

Online DDL 索引操作

因为 pt - osc 拷贝数据的过程，会依赖于唯一键（主键或者唯一索引）来校验数据的一致性，对唯一键进行相关的操作可能会引发各种各样的问题，所以不管 pt - osc 实际支持或不支持这类操作，咱都直接默认为不支持就好。也就是涉及主键及唯一索引相关的操作，都直接选 Online DDL。

但是，需要注意的是，单独删除主键的操作，会引发锁表，导致不允许对表进行其他增删改的操作，也就是增删改会被堵塞住，这操作需要慎重考虑。而同一个 DDL 里面，删除老主键的同时又加上新主键，是不会引起堵塞的。

2、列操作

Online DDL 索引操作

1）添加列、删除列、重排列顺序、变更列类型、修改列为空或非空

Online DDL：从图可以看出，这 6 种 DDL 操作，在选择 Online 方式时，都会重建表，效果上与 pt - osc 并无太大差别，还得担忧从库复制延迟的问题，那既然如此，直接选择 pt- osc 的方式更省事。

Online DDL 以下这几种情况会锁表，堵塞其他增删改操作，需要注意：

1）增加一个自增列。

2）单纯修改列类型。

3）修改列名，同时修改了列类型（该情形应该算修改列类型的一个特殊例子）（只支持 Online DDL）。



pt - osc：首选。需要注意的是，因为 pt - osc 不支持修改列名，所以上述的第三点，只能选择 Online DDL的方式执行，但是选择Online DDL，又会出现锁表导致堵塞其他增删改的操作，所以慎重。

2）VARCHAR 列增加列大小

MySQL 底层在存储变长列 VARCHAR 列的内容时，还会额外记录内容占用字节数的大小，记录这个大小，也是需要空间的。另外，还有个东西咱需要了解下，VARCHAR 列存储一个字符，使用 utf8 字符集时，最大需要 3 字节（比如存储一个中文字符），而 utf8mb4最大需要 4 字节（比如存储一个表情符）。知道了使用什么字符集，咱就可以计算出存储一个 VARCHAR变 长列最大需要多少字节了。

记录列占用字节数大小，所需的空间，会根据 列存储最大需要字节数 细分出两种情况：

1）列存储最大需要字节数为 0 - 255 时，记录列占用字节数大小需要1字节。

2）列存储最大需要字节数为 256 - 65535 时，记录列占用字节数大小需要2字节。

3）因表数据行，非大对象的列，总的存储内容长度限制就是 65535，所以，单列VARCHAR存储需求自然也不能超过这个限制，也就是不存在超过 65535的情况。



回归正题，VARCHAR 列增加大小：

1）如果列长度增加后，记录列占用字节数大小所需字节数不变，也就是列存储最大需要字节数依然在同一个范围内：0-255 或 256-65535，那这类操作，ALGORITHM 支持使用 ` In Place 算法，只会修改表的元数据信息，瞬间完成，此情形，直接选 Online DDL`即可。

2）如果列长度增加后，记录列占用字节数大小所需字节数变了，从 1 字节变成 2 字节，ALGORITHM 则只支持 COPY 算法，这就意味着会出现数据拷贝的情况，同时会堵塞其他增删改的操作，这情形选 pt - osc。

# 建表
create table t1(name varchar(10) null) charset=utf8mb4;

# 列存储最大需要字节数计算：长度 10，utf8mb4字符集存储单字符最大需要字节数 4
# 列存储最大需要字节数 = 10 * 4 = 40
# 记录列占用字节数大小所需空间为1字节


# 表结构变更一
alter table t1 modify name varchar(63) null;

# 列存储最大需要字节数计算：长度 63，utf8mb4字符集存储单字符最大需要字节数 4
# 列存储最大需要字节数 = 63 * 4 = 252
# 记录列占用字节数大小所需空间依然为1字节，ALGORITHM默认选用In Place，Online DDL执行瞬间完成


# 表结构变更二
alter table t1 modify name varchar(64) null;

# 列存储最大需要字节数计算：长度 64，utf8mb4字符集存储单字符最大需要字节数 4
# 列存储最大需要字节数 = 64 * 4 = 256
# 记录列占用字节数大小所需空间变为2字节，ALGORITHM只能使用COPY，引发数据拷贝，堵塞其他增删改操作，选择pt-osc

3）修改列名（只改列名）、设置/删除默认值

⬤ Online DDL：从图三可以看出，这类操作会只修改表元数据信息，速度极快，直接选 Online DDL方式即可

⬤ pt - osc ：直接无视

4）修改自增列的自增值

⬤ Online DDL：在 MySQL 8.0 版本前，自增值不存在持久化的概念，修改这个值，只会在内存中修改，更不涉及数据的拷贝及变动，所以直接使用 Online DDL 方式即可。 

⬤ pt - osc ：直接无视

2、表操作

1）修改表名

⬤ Online DDL：从图看出，Online DDL 修改表名，只会涉及到修改表的元数据信息，瞬间完成。

⬤ pt - osc ：不支持

2） 表碎片整理、更改行格式、修改字符集、收集统计信息

⬤ Online DDL：咱对表的操作，常用到的，可能就是表碎片整理、更改行格式（比如改成压缩模式），修改字符集（含内容转换）及收集统计信息，这些操作，从图看到了，基本都是需要重建表，建议首选 pt - osc 。

⬤ pt - osc ：首选

3、表分区

1）普通表转分区表

⬤ Online DDL：他的本质是新建一个临时表，每个分区对应一个数据文件，然后进行拷贝，拷贝完毕后，表名互换，删除老表。眼熟不？从某种程度上讲，这个过程与 pt- osc 是相似的，但是，Online DDL 方式会锁表，堵塞其他增删改操作，所以，直接选择pt-osc方式即可。

⬤ pt - osc ：必选

2）新增分区、删除分区、TRUNCATE 分区

⬤ Online DDL：

新增分区，只分析常用的 RANGE 及 LIST 分区。RANGE 分区新增分区，有个严格的限制，新分区 less than 的值必须是递增的，换句话讲就是不存在数据拷贝的问题。LIST 分区，这个更直接，相关内容如果在 LIST 分区中不存在，直接不允许插入，新增分区也不存在数据拷贝的问题。所以，这两种选用 Online DDL 时，操作几乎都是瞬时完成的，直接使用 Online DDL 即可。

删除分区时，会对当前分区上锁，堵塞该分区的其他增删改操作，但既然你都打算删除分区了，想必自然也不会再对该分区有其他操作。 其对应系统底层的操作，类似于直接将分区对应的物理文件进行删除，操作时，如果文件足够大，系统 IO 会瞬间暴涨，继而影响业务，所以建议在业务低峰期间进行。

TRUNCATE 分区操作，对应系统底层的操作，类似于直接将分区对应的物理文件进行清空，操作时，如果文件足够大，系 统 IO会 瞬间暴涨，继而影响业务，所以建议在业务低峰期间进行。



pt - osc ：直接无视