MySQL5.7 InnoDB Online DDL原理和其他无锁变更工具(pt-osc，gh-ost)对比

MYSQL Online DDL官方文档：https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl.html

目录

Online DDL原理

MySQL5.6开始支持Online DDL操作。在这之前，对表的修改会阻塞整个表的读写操作。

在5.6之后，可以使用ALTER TABLE语句的ALGORITHM和LOCK子句来控制DDL操作的各个方面。这些子句位于语句的末尾，通过逗号与表和列规范分开。例如：

ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;

LOCK子句有助于微调对表的并发访问程度。ALGORITHM子句主要用于性能比较，以及作为在遇到任何问题时回落到旧的表复制行为的备用方案。

  • ALGORITHM=INPLACE 表示执行DDL的过程中不发生表拷贝，另一种是COPY，就是之前的拷贝方式。一般都是默认，让 MySQL 以尽量保证 DML 并发操作的原则选择执行方式。
  • LOCK=NONE 表示对 DML 操作不加锁，DDL 过程中允许所有的 DML 操作。此外还有 EXCLUSIVE（持有排它锁，阻塞所有的请求）、SHARED（允许SELECT，但是阻塞INSERT UPDATE DELETE）和 DEFAULT（根据DDL的类型，在保证最大并发的原则下来选择LOCK的取值）。

2种算法原理

  • COPY方式
    1. 按照原表定义创建一个新的临时表；
    2. 对原表加写锁（禁止DML，允许select）；
    3. 在步骤1 建立的临时表执行 DDL；
    4. 将原表中的数据 copy 到临时表；
    5. 释放原表的写锁；
    6. 将原表删除，并将临时表重命名为原表。

采用 copy 方式期间需要锁表，禁止DML，因此是非Online的

  • INPLACE方式
    在原表上进行更改，不需要生成临时表，不需要进行数据copy的过程。根据是否行记录格式，又可分为两类：

    • rebuild：需要重建表（重新组织聚簇索引）
    • no-rebuild：不需要重建表，只需要修改表的元数据

    对于 rebuild 方式实现 Online 是通过缓存 DDL 期间的 DML，待 DDL 完成之后，将 DML 应用到表上来实现的。例如，执行一个 alter table A engine=InnoDB; 重建表的 DDL 其大致流程如下：

    1. 建立一个临时文件，扫描表 A 主键的所有数据页；
    2. 用数据页中表 A 的记录生成 B+ 树，存储到临时文件中；
    3. 生成临时文件的过程中，将所有对 A 的操作记录在一个日志文件（row log）中；
    4. 临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 A 相同的数据文件；
    5. 用临时文件替换表 A 的数据文件。

说明：

  1. 在 copy 数据到新表期间，在原表上是加的 MDL 读锁（允许 DML，禁止 DDL）；

  2. 在应用增量期间对原表加 MDL 写锁（禁止 DML 和 DDL）；

  3. 根据表 A 重建出来的数据是放在 tmp_file 里的，这个临时文件是 InnoDB 在内部创建出来的，整个 DDL 过程都在 InnoDB 内部完成。对于 server 层来说，没有把数据挪动到临时表，是一个原地操作，这就是”inplace”名称的来源。

回滚

  • 使用Inplace方式执行的DDL，发生错误或被kill时，需要一定时间的回滚期，执行时间越长，回滚时间越长。

  • 使用Copy方式执行的DDL，需要记录过程中的undo和redo日志，同时会消耗buffer pool的资源，效率较低，优点是可以快速停止

执行流程
Online DDL主要包括3个阶段，prepare阶段，ddl执行阶段，commit阶段
image

  • prepare阶段：持有EXCLUSIVE-MDL锁，禁止读写（禁止DML，DDL）

  • ddl执行阶段：降级EXCLUSIVE-MDL锁，允许读写（copy不可写）（允许DML，禁止DDL）

  • commit阶段：升级EXCLUSIVE-MDL锁，禁止读写（禁止DML，DDL）

Online DDL 过程中占用 exclusive MDL 的步骤执行很快，所以几乎不会阻塞 DML 语句。
不过，在 DDL 执行前或执行时，其他事务可以获取 MDL（metadata lock）。由于需要用到 exclusive MDL，所以必须要等到其他占有 metadata lock 的事务提交或回滚后才能执行上面两个涉及到 MDL 的地方。

（MDL说明：在访问一个表的时候会被自动加上，以保证读写的正确性。当对一个表做 DML 操作的时候，加 MDL 读锁；当做 DDL 操作时候，加 MDL 写锁）

虽然Online DDL不阻塞DML，但是对大表做操作，还是涉及到数据库IO，CPU的消耗，尽量在业务低峰进行。

Online DDL支持情况

Index Operations（索引操作）

操作 In Place（就地更新） Rebuilds Table（重建表） Permits Concurrent DML（允许并发DML） Only Modifies Metadata（仅修改元数据）
Creating or adding a secondary index Yes No Yes No
Dropping an index Yes No Yes Yes
Renaming an index Yes No Yes Yes
Adding a FULLTEXT index Yes* No* No No
Adding a SPATIAL index Yes No No No
Changing the index type Yes No Yes Yes

创建二级索引，In Place的方式执行，整个过程只会涉及到拷贝二级索引列相关的数据用于创建索引

创建/删除二级索引，表仍然可以进行读写操作。这些操作只有在访问表的所有事务完成后才结束

如果服务器在创建二级索引时退出，在恢复时，MySQL会丢弃任何部分创建的索引。必须重新运行ALTER TABLE或CREATE INDEX语句

删除索引，只是元数据修改，实际索引占用的存储空间并不会立刻释放。需要执行optimize table才能释放出空间

Primary Key Operations（主键操作）

操作 In Place（就地更新） Rebuilds Table（重建表） Permits Concurrent DML（允许并发DML） Only Modifies Metadata（仅修改元数据）
Adding a primary key Yes* Yes* Yes No
Dropping a primary key No Yes No No
Dropping a primary key and adding another Yes Yes Yes No

主键的修改，都会重建表，一般也不会进行此类修改，就不讨论这种情况了。

Column Operations（列操作）

操作 In Place（就地更新） Rebuilds Table（重建表） Permits Concurrent DML（允许并发DML） Only Modifies Metadata（仅修改元数据）
Adding a column Yes Yes Yes* No
Dropping a column Yes Yes Yes No
Renaming a column Yes No Yes* Yes
Reordering columns Yes Yes Yes No
Setting a column default value Yes No Yes Yes
Changing the column data type No Yes No No
Extending VARCHAR column size Yes No Yes Yes
Dropping the column default value Yes No Yes Yes
Changing the auto-increment value Yes No Yes No*
Making a column NULL Yes Yes* Yes No
Making a column NOT NULL Yes* Yes* Yes No
Modifying the definition of an ENUM or SET column Yes No Yes Yes

对于列的修改（新增，删除，重新排序，变更列类型，修改列为空或非空）都会重建表

会锁表的情况（不支持并发DML）：

  • 新增自增列：数据被大量重新组织，使其成为一个昂贵的操作。至少需要ALGORITHM=INPLACE, LOCK=SHARED

  • 修改列类型

VARCHAR类型的修改：

  • VARCHAR列所需的长度字节数必须保持不变。对于大小为0到255字节的VARCHAR列，需要一个字节来编码值。对于大小为256字节或更大的VARCHAR列，需要两个字节。因此，In Place ALTER TABLE只支持将VARCHAR列大小从0到255字节增加，或从256字节增加到更大的大小。In Place ALTER TABLE不支持将VARCHAR列大小从小于256字节增加到等于或大于256字节。在这种情况下，所需的长度字节数从1变为2，这仅由表复制（ALGORITHM=COPY）支持。

  • 减少VARCHAR大小也需要表复制（ALGORITHM=COPY）支持

仅修改列名，设置/删除默认值，仅修改元数据信息

Table Operations（表操作）

操作 In Place（就地更新） Rebuilds Table（重建表） Permits Concurrent DML（允许并发DML） Only Modifies Metadata（仅修改元数据）
Changing the ROW_FORMAT Yes Yes Yes No
Changing the KEY_BLOCK_SIZE Yes Yes Yes No
Setting persistent table statistics Yes No Yes Yes
Specifying a character set Yes Yes* Yes No
Converting a character set No Yes* No No
Optimizing a table Yes* Yes Yes No
Rebuilding with the FORCE option Yes* Yes Yes No
Performing a null rebuild Yes* Yes Yes No
Renaming a table Yes No Yes Yes

一般常用的就Optimizing a table，对于具有FULLTEXT索引的表，不支持原地操作。操作使用INPLACE算法，但不允许使用ALGORITHM和LOCK语法。

表的重命名，也不需会重建表。（您也可以使用RENAME TABLE语句重命名表。）但重命名的表授予的权限不会迁移到新名称。必须手动更改

pt-osc方式

pt-osc 用于修改表时不锁表，简单地说，这个工具创建一个与原始表一样的新的空表，并根据需要更改表结构，然后将原始表中的数据以小块形式复制到新表中，然后删除原始表，然后将新表重命名为原始名称。在复制过程中，对原始表的所有新的更改（insert，delete，update）都将应用于新表，因为在原始表上创建了一个触发器，以确保所有新的更改都将应用于新表。

流程如下：

  1. 创建一个和要执行 alter 操作的表一样的新的空表结构 (是 alter 之前的结构)；

  2. 在新表执行 alter table 语句（速度应该很快）；

  3. 在原表中创建触发器 3 个触发器分别对应 insert,update,delete 操作，如果表中已经定义了触发器这个工具就不能工作了；

  4. 以一定块大小从原表拷贝数据到临时表，拷贝过程中通过原表上的触发器在原表进行的写操作都会更新到新建的临时表，保证数据不会丢失（会限制每次拷贝数据的行数以保证拷贝不会过多消耗服务器资源，采用 LOCK IN SHARE MODE 来获取要拷贝数据段的最新数据并对数据加共享锁阻止其他会话修改数据，不过每次加 S 锁的行数不多，很快就会被释放）；

  5. 将原表 Rename 为 old 表，再把新表 Rename 为原表（整个过程只在 rename 表的时间会锁一下表，其他时候不锁表）；

  6. 如果有参考该表的外键，根据 alter-foreign-keys-method 参数的值，检测外键相关的表，做相应设置的处理（根据修改后的数据，修改外键关联的子表），如果被修改表存在外键定义但没有使用 --alter-foreign-keys-method 指定特定的值，该工具不予执行；

  7. 默认最后将旧原表删除、触发器删除。

限制

  1. 在使用此工具之前，应为表定义 PRIMARY KEY 或唯一索引，因为它是 DELETE 触发器所必需的；

  2. 如果表已经定义了触发器，则不支持 pt-osc ；（注：不是不能有任何触发器，只是不能有针对 insert、update、delete 的触发器存在，因为一个表上不能有两个相同类型的触发器）；

  3. 如果表具有外键约束，需要使用选项 --alter-foreign-keys-method，如果被修改表存在外键定义但没有使用 --alter-foreign-keys-method 指定特定的值，该工具不予执行；

  4. 还是因为外键，对象名称可能会改变（indexes names 等）；

  5. 在 Galera 集群环境中，不支持更改 MyISAM 表，系统变量 wsrep_OSU_method 必须设置为总序隔离（Total Order Isolation，TOI）；

  6. 此工具仅适用于 MySQL 5.0.2 及更新版本（因为早期版本不支持触发器）；

  7. 需要给执行的账户在 MySQL 上授权，才能正确运行。（应在服务器上授予 PROCESS、SUPER、REPLICATION SLAVE 全局权限以及 SELECT、INSERT、UPDATE、DELETE、CREATE、DROP、ALTER 和 TRIGGER 表权限。Slave 只需要 REPLICATION SLAVE 和 REPLICATION CLIENT 权限。）

gh-ost方式

和pt-osc类似，都是使用临时表加复制的方式，不同之处在于，增量数据同步，gh-ost不使用触发器，使用binlog同步。原理如下：

  1. 创建幽灵表：_xxx_gho（和原表结构一致），_xxx_ghc（用于记录变更日志）

  2. 在步骤 1 中创建的幽灵表上执行 DDL 语句

  3. 模拟成备库连接到真正的主库或备库

  4. 将数据从原表拷贝到幽灵表

  5. 应用 binlog events 到幽灵表

  6. 切换新旧表

无触发器设计最大的优点是和数据库的工作负载解耦。触发器的设计无论何种情况下，源表的DML都会同时在另外一张表上同步操作。非触发器设计将这个过程解耦，即新表的写入和源表的写入不存在直接依赖

总结

指标 Online DDL pt-osc / gh-ost
锁表风险 较低 极低
执行时间 较长 长
主从延迟 较大 小
需要额外空间 中 大
IO负载 中 大

不同无锁变更的方案对比可以参考阿里云文档：https://help.aliyun.com/zh/dms/comparison-of-lock-free-schema-change-solutions?spm=a2c4g.11186623.0.0.70654f28AtROUO

对于常见的DDL选择执行方式（MYSQL5.7）

  • 创建二级索引：Online DDL使用InPlace方式执行，整个过程，只涉及到拷贝二级索引列相关的数据用于创建索引，相对于pt-osc/gh-ost，复制数据量会少很多，磁盘IO相对也少。但是选择Online DDL，会有主从延迟的问题

  • 删除索引：Online DDL仅修改元数据，选择Online DDL就好（被删除的索引，实际占用空间并不会释放）

  • 新增列，删除列，变更列类型，修改列为空或非空：Online DDL都会执行Rebuilds Table，而且还有主从延迟的问题。建议选择pt-osc/gh-ost

  • 修改列名，设置/删除默认值：Online DDL仅修改元数据，选择Online DDL就好

  • 增加/减少varchar字段长度：参见上述Column Operations（列操作）。如果所需的长度字节数从1变为2，建议选择pt-osc/gh-ost

  • 执行Optimize table：Online DDL会执行Rebuilds Table，建议选择pt-osc/gh-ost

  • 修改表名：Online DDL仅修改元数据，选择Online DDL就好

对于MYSQL8

  • 在 8.0.12版本，新增了ALGORITHM=INSTANT算法，添加列时，不再修改用户原有数据，只需对表元信息进行修改。因此，操作时间不在依赖表的大小，可以达到秒级。
    但有一些限制：

    • 只支持INSTANT ADD COLUMN，不支持INSTANT DROP COLUMN
    • INSTANT ADD COLUMN位置只能在已有列的最后，不能指定其他位置

    MySQL 8.0.29版本中推出了INSTANT算法新的实现方式，解决了上述的2个问题

  • 大致的实现原理是MYSQL新引入了一个元数据version的概念，每行对应一个元数据版本。新增/删除列，会导致版本加1，后续插入的数据对应的就是新版本的元数据。在查询时，根据每行的元数据版本号，获取到当时列版本，再转换为最新版本的格式返回

参考

https://zhuanlan.zhihu.com/p/567083306
https://zhuanlan.zhihu.com/p/633326199
https://zhuanlan.zhihu.com/p/946510015
https://blog.csdn.net/jdcdev_/article/details/140521811

posted @ 2024-10-20 21:46  wuworker  阅读(86)  评论(0)  编辑  收藏  举报