大表在线DDL变更，MySQL Online DDL 原理和踩坑

本文讲述了大表在线DDL变更，MySQL Online DDL 原理和踩坑

1.背景描述

首先，抛出一个问题：

假设线上数据库有1T数据，现在需要进行索引变更，可能需要加3个索引、5个索引不等，请问执行DDL变更可能会带来哪些风险，以及如何应对？

和传统企业级开发不同，互联网需求变更比较频繁，基本每周甚至每天都有上线、3周一次大的上线是很常见的事，对应着表结构变更也是经常需要操作的，比如给表添加字段、给字段加索引。当数据量较大的表（简称大表）需要表更时，可能会由于锁表而阻塞读写请求（DML操作阻塞），影响系统的可用性。

虽然也有由于系统表结构重大变更对外公共系统维护的做法（听说豆瓣就这样干过，网上的消息，未证实），但系统可用性确实是互联网应用的底线、除非特殊原因，身为技术人员一般不会选择该方案。那么如果系统不能停机、数据量又非常大，如何实施在线DDL变更而不阻塞数据库DML操作呢？

2.在线DDL变更过程

大致过程：先复制结构出来，加索引，然后把数据导过来，具体过程如下：

对表加锁。

复制原表物理结构到中间表。

修改中间表的物理结构，并将原表数据导入到中间表。

数据同步完成之后，锁定中间表，删除原表，rename中间表为原表。

刷新数据字典，并释放锁。

从中我们看到这个变更只能读取不能写入，如果表的数据量很大，等待的时间就很长。

3.在线DDL变更可能带来的风险

修改大表结构执行时间往往不能预估，一般时间较长，可能带来的风险有：

修改表结构是表级锁，影响DML写入操作。

修改大表耗时较长，中途写入失败需要进行回滚，回滚这段时间也是不可写入。

修改大表结构容易导致数据库CPU、IO性能损耗，降低MySQL服务性能。

修改大表结构容易造成主从延迟加大，影响业务读取。

4.在线DDL变更应对策略

简单来说就是创建一个中间表，给中间表加索引，然后同步原表和中间表，不用加索引，中间表同步后，禁止原表写入，rename中间表，干掉原表。中间存在短暂的原表DML不可用情况。

回到开始提出的那个问题，这个问题要看场景，当并发量不大时，可以采用中间表-rename的方案，即使存在短暂不可用，其实影响非常小，而且应该很多人也这样干过。但如果是高并发场景下，策略就略有不同，如下：

首先，DDL变更应该尽可能选择业务相对空闲时，以免影响服务。

其次，如果是MySQL5.6以前的版本，推荐使用pt-osc(pt-online-schema-change)工具实时DDL变更，原理是：通过创建表的拷贝来进行，期间支持DML变更。如果是MySQL5.5以及以上，由于MySQL原生支持Online DDL特性，因此推荐使用原生Onlie DDL，但是如果DDL变更需要进行COPY TABLE操作，则还是推荐使用online-schema-change工具。如果涉及外键关联的表，优先使用Online DDL。

因此，回答开头那个问题需要了解数据库类型、流量峰值、机器负载情况在给出方案。

5.pt-online-schema-change

pt-online-schema-change可以在不阻塞读写的情况下在线整理表结构、收集碎片、给大表加字段和索引，并实时查询执行进度。

工作过程：首先会根据要修改的表创建一张要修改的空表、在空表上执行alter语句，然后在原始表中同步数据到新表，最后用新表替换原表。

数据同步过程中，数据以一定的块大小从原表同步到新表，在数据同步前，会在原表上创建delete触发器、update触发器、insert触发器。当同步完成后，会用rename table的方式交换原表和临时表的表名。

最后会删除触发器以及原表。

pt-osc使用限制

原表必须有主键或唯一索引，否则工具会执行失败。

如果表有外键约束，工具除非选项--alter-foreign-keys-method，否则不会执行。

原表不能有触发器。

创建临时表前，先了解待同步的数据量和磁盘剩余空间，避免由于磁盘空间不足导致变更失败。

一些思考

我们知道在执行过程中会在原表上建立触发器，当有业务update原表时，触发器会在临时表上同样进行update，那如果此时原表上要update的数据还没有copy到临时表该怎么办呢？

答案是：原表update时，临时表上采用的是replace into的方式调整数据，replace into和insert功能不太一样，replace into会先尝试插入数据到表中，如果发现表中已经有此行数据，则先删除再插入，否则直接插入。所以这也要求每张表必须有主键或者唯一索引，不然有可能会出现数据重复的情况。

使用示例

MySQL 的 DDL(Data Definition Language) 包括增减字段、增减索引等操作。在 MySQL 5.6 之前，MySQL 的 DDL 操作会按照原来的表复制一份，并做相应的修改，例如，对表 A 进行 DDL 的具体过程如下：

按照表 A 的定义新建一个表 B

对表 A 加写锁

在表 B 上执行 DDL 指定的操作

将 A 中的数据拷贝到 B

释放 A 的写锁

删除表 A

将表 B 重命名为 A

在 2-4 的过程中，如果表 A 数据量比较大，拷贝到表 B 的过程会消耗大量时间，并占用额外的存储空间。此外，由于 DDL 操作占用了表 A 的写锁，所以表 A 上的 DDL 和 DML 都将阻塞无法提供服务。

因此，MySQL 5.6 增加了 Online DDL，允许在不中断数据库服务的情况下进行 DDL 操作。

用法

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

ALTER 语句中可以指定参数 ALGORITHM 和 LOCK 分别指定 DDL 执行的方式和 DDL 期间 DML 的兵法控制

ALGORITHM=INPLACE 表示执行DDL的过程中不发生表拷贝，过程中允许并发执行DML（INPLACE不需要像COPY一样占用大量的磁盘I/O和CPU，减少了数据库负载。同时减少了buffer pool的使用，避免 buffer pool 中原有的查询缓存被大量删除而导致的性能问题）。

如果设置 ALGORITHM=COPY，DDL 就会按 MySQL 5.6 之前的方式，采用表拷贝的方式进行，过程中会阻塞所有的DML。另外也可以设置 ALGORITHEM=DAFAULT，让 MySQL 以尽量保证 DML 并发操作的原则选择执行方式。

LOCK=NONE 表示对 DML 操作不加锁，DDL 过程中允许所有的 DML 操作。此外还有 EXCLUSIVE（持有排它锁，阻塞所有的请求，适用于需要尽快完成DDL或者服务库空闲的场景）、SHARED（允许SELECT，但是阻塞INSERT UPDATE DELETE，适用于数据仓库等可以允许数据写入延迟的场景）和 DEFAULT（根据DDL的类型，在保证最大并发的原则下来选择LOCK的取值）

不过并不是所有的 DDL 操作都能用 INPLACE 的方式执行，具体的支持情况可以在 MySQL Reference Manual — Online DDL Operations) 中查看。

例如 Table 14.10 中显示修改列的数据类型不支持 INPLACE

|Operation |In Place| Rebuilds Table| Permits Concurrent DML| Only Modifies Metadata| |-|-|-|-|-| |Changing the column data type| No |Yes| No |No|

这时尝试将原类型为 FLOAT 的 column_name 改为 INT

执行过程

初始化：根据存储引擎、用户指定的操作、用户指定的 ALGORITHM 和 LOCK 计算 DDL 过程中允许的并发量，这个过程中会获取一个 shared metadata lock，用来保护表的结构定义

执行 DDL：根据第一步的情况决定是否将 shared metadata lock 升级为 exclusive metadata lock（仅在语句准备阶段），然后生成语句并执行。执行期间的 shared metadata lock 保证了不会同时执行其他的 DDL，但 DML 能可以正常执行

提交：将 shared metadata lock 升级为 exclusive metadata lock，然后删除旧的表定义，提交新的表定义

Online DDL 过程中占用 exclusive MDL 的步骤执行很快，所以几乎不会阻塞 DML 语句。

不过，在 DDL 执行前或执行时，其他事务可以获取 MDL。由于需要用到 exclusive MDL，所以必须要等到其他占有 metadata lock 的事务提交或回滚后才能执行上面两个涉及到 MDL 的地方。

踩坑

前面提到 Online DDL 执行过程中需要获取 MDL，MDL (metadata lock) 是 MySQL 5.5 引入的表级锁，在访问一个表的时候会被自动加上，以保证读写的正确性。当对一个表做 DML 操作的时候，加 MDL 读锁；当做 DDL 操作时候，加 MDL 写锁。

为了在大表执行 DDL 的过程中同时保证 DML 能并发执行，前面使用了 ALGORITHM=INPLACE 的 Online DDL，但这里仍然存在死锁的风险，问题就出在 Online DDL 过程中需要 exclusive MDL 的地方。

例如，Session 1 在事务中执行 SELECT 操作，此时会获取 shared MDL。由于是在事务中执行，所以这个 shared MDL 只有在事务结束后才会被释放。

由于 exclusive MDL 的获取优先于 shared MDL，后续尝试获取 shared MDL 的操作也将会全部阻塞

到这一步，后续无论是 DML 和 DDL 都将阻塞，直到 Session 1 提交或者回滚，Session 1 占用的 shared MDL 被释放，后面的操作才能继续执行。

上面这个问题主要有两个原因：

Session 1 中的事务没有及时提交，因此阻塞了 Session 3 的 DDL

Session 3 Online DDL 阻塞了后续的 DML 和 DDL

对于问题 1，不少 ORM（例如 pymysql）都默认将用户语句封装成事务执行，如果客户端程序中断退出，还没来得及提交或者回滚事务，就会出现 Session 1 中的情况。这时可以在 infomation_schema.innodb_trx 中找出未完成的事务对应的线程，并强制退出

可以看到 Session 1 正在执行的事务对应的 trx_mysql_thread_id 为 9，然后执行 KILL 9 即可中断 Session 1 中的事务。

对于问题 2，在查询很多的情况下，会导致阻塞的 session 迅速增多，对于这种情况，可以先中断 DDL 操作，防止对服务造成过大的影响。也可以尝试在从库上修改表结构后进行主从切换或者使用 pt-osc 等第三方工具。

上文就是小编为大家整理的大表在线DDL变更，MySQL Online DDL 原理和踩坑。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。