写点什么

MySQL DDL 执行方式 -Online DDL 介绍

  • 2022-09-22
    北京
  • 本文字数:6097 字

    阅读完需:约 20 分钟

MySQL DDL执行方式-Online DDL介绍

1 引言

大家好,今天与大家一起分享一下 mysql DDL 执行方式。


一般来说 MySQL 分为 DDL(定义)和 DML(操作)。

  • DDL:Data Definition Language,即数据定义语言,那相关的定义操作就是 DDL,包括:新建、修改、删除等;相关的命令有:CREATE,ALTER,DROP,TRUNCATE 截断表内容(开发期,还是挺常用的),COMMENT 为数据字典添加备注。

  • DML:Data Manipulation Language,即数据操作语言,即处理数据库中数据的操作就是 DML,包括:选取,插入,更新,删除等;相关的命令有:SELECT,INSERT,UPDATE,DELETE,还有 LOCK TABLE,以及不常用的 CALL – 调用一个 PL/SQL 或 Java 子程序,EXPLAIN PLAN – 解析分析数据访问路径。


我们可以认为:

  • CREATE,ALTER ,DROP,TRUNCATE,定义相关的命令就是 DDL;

  • SELECT,INSERT,UPDATE,DELETE,操作处理数据的命令就是 DML;


DDL、DML 区别:

  • DML 操作是可以手动控制事务的开启、提交和回滚的。

  • DDL 操作是隐性提交的,不能 rollback,一定要谨慎哦!


日常开发我们对一条 DML 语句较为熟悉,很多开发人员都了解 sql 的执行过程,比较熟悉,但是 DDL 是如何执行的呢,大部分开发人员可能不太关心,也认为没必要了解,都交给 DBA 吧。 其实不然,了解一些能尽量避开一些 ddl 的坑,那么下面带大家一起了解一下 DDL 执行的方式,也算抛砖引玉吧。如有错误,还请各位大佬们指正。

2 概述

在 MySQL 使用过程中,根据业务的需求对表结构进行变更是个普遍的运维操作,这些称为 DDL 操作。常见的 DDL 操作有在表上增加新列或给某个列添加索引。


我们常用的易维平台提供了两种方式可执行 DDL,包括 MySQL 原生在线 DDL(online DDL)以及一种第三方工具 pt-osc。


下图是执行方式的性能对比及说明:


本文将对 DDL 的执行工具之 Online DDL 进行简要介绍及分析,pt-osc 会专门再进行介绍。

3 介绍

MySQL Online DDL 功能从 5.6 版本开始正式引入,发展到现在的 8.0 版本,经历了多次的调整和完善。其实早在 MySQL 5.5 版本中就加入了 INPLACE DDL 方式,但是因为实现的问题,依然会阻塞 INSERT、UPDATE、DELETE 操作,这也是 MySQL 早期版本长期被吐槽的原因之一。


在 MySQL 5.6 版本以前,最昂贵的数据库操作之一就是执行 DDL 语句,特别是 ALTER 语句,因为在修改表时,MySQL 会阻塞整个表的读写操作。例如,对表 A 进行 DDL 的具体过程如下:

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

  2. 对表 A 加写锁

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

  4. 将 A 中的数据拷贝到 B

  5. 释放 A 的写锁

  6. 删除表 A

  7. 将表 B 重命名为 A

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


如果遇到巨大的表,可能需要几个小时才能执行完成,势必会影响应用程序,因此需要对这些操作进行良好的规划,以避免在高峰时段执行这些更改。对于那些要提供全天候服务(24*7)或维护时间有限的人来说,在大表上执行 DDL 无疑是一场真正的噩梦。


因此,MySQL 官方不断对 DDL 语句进行增强,自 MySQL 5.6 起,开始支持更多的 ALTER TABLE 类型操作来避免数据拷贝,同时支持了在线上 DDL 的过程中不阻塞 DML 操作,真正意义上的实现了 Online DDL,即在执行 DDL 期间允许在不中断数据库服务的情况下执行 DML(insert、update、delete)。然而并不是所有的 DDL 操作都支持在线操作。到了 MySQL 5.7,在 5.6 的基础上又增加了一些新的特性,比如:增加了重命名索引支持,支持了数值类型长度的增大和减小,支持了 VARCHAR 类型的在线增大等。但是基本的实现逻辑和限制条件相比 5.6 并没有大的变化。

4 用法

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 的取值)。

5 两种算法

第一种 Copy:

  1. 按照原表定义创建一个新的临时表;

  2. 对原表加写锁(禁止 DML,允许 select);

  3. 在步骤 1 建立的临时表执行 DDL;

  4. 将原表中的数据 copy 到临时表;

  5. 释放原表的写锁;

  6. 将原表删除,并将临时表重命名为原表。

  7. 从上可见,采用 copy 方式期间需要锁表,禁止 DML,因此是非 Online 的。比如:删除主键、修改列类型、修改字符集,这些操作会导致行记录格式发生变化(无法通过全量 + 增量实现 Online)。

第二种 Inplace:

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

  • rebuild:需要重建表(重新组织聚簇索引)。比如 optimize table、添加索引、添加/删除列、修改列 NULL/NOT NULL 属性等;

  • 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 的资源,效率较低,优点是可以快速停止。


不过并不是所有的 DDL 操作都能用 INPLACE 的方式执行,具体的支持情况可以在(在线 DDL 操作) 中查看。

以下是常见 DDL 操作:


官网支持列表:


6 执行过程

Online DDL 主要包括 3 个阶段,prepare 阶段,ddl 执行阶段,commit 阶段。下面将主要介绍 ddl 执行过程中三个阶段的流程。


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

  • 创建新的临时 frm 文件(与 InnoDB 无关)。

  • 持有 EXCLUSIVE-MDL 锁,禁止读写。

  • 根据 alter 类型,确定执行方式(copy,online-rebuild,online-norebuild)。假如是 Add Index,则选择 online-norebuild 即 INPLACE 方式。

  • 更新数据字典的内存对象。

  • 分配 row_log 对象来记录增量(仅 rebuild 类型需要)。

  • 生成新的临时 ibd 文件(仅 rebuild 类型需要) 。

  • 数据字典上提交事务、释放锁。

注:Row log 是一种独占结构,它不是 redo log。它以 Block 的方式管理 DML 记录的存放,一个 Block 的大小为由参数 innodb_sort_buffer_size 控制,默认大小为 1M,初始化阶段会申请两个 Block。


2)DDL 执行阶段:执行期间的 shared metadata lock 保证了不会同时执行其他的 DDL,但 DML 能可以正常执行。

  • 降级 EXCLUSIVE-MDL 锁,允许读写(copy 不可写)。

  • 扫描 old_table 的聚集索引每一条记录 rec。

  • 遍历新表的聚集索引和二级索引,逐一处理。

  • 根据 rec 构造对应的索引项

  • 将构造索引项插入 sort_buffer 块排序。

  • 将 sort_buffer 块更新到新的索引上。

  • 记录 ddl 执行过程中产生的增量(仅 rebuild 类型需要)

  • 重放 row_log 中的操作到新索引上(no-rebuild 数据是在原表上更新的)。

  • 重放 row_log 间产生 dml 操作 append 到 row_log 最后一个 Block。


3)Commit 阶段:将 shared metadata lock 升级为 exclusive metadata lock,禁止 DML,然后删除旧的表定义,提交新的表定义。

  • 当前 Block 为 row_log 最后一个时,禁止读写,升级到 EXCLUSIVE-MDL 锁。

  • 重做 row_log 中最后一部分增量。

  • 更新 innodb 的数据字典表。

  • 提交事务(刷事务的 redo 日志)。

  • 修改统计信息。

  • rename 临时 idb 文件,frm 文件。

  • 变更完成。


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

7 踩坑

前面提到 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 只有在事务结束后才会被释放。

# Session 1> START TRANSACTION;> SELECT * FROM tbl_name;# 正常执行
复制代码

这时 Session 2 想要执行 DML 操作也只需要获取 shared MDL,仍然可以正常执行。

# Session 2> SELECT * FROM tbl_name;# 正常执行
复制代码

但如果 Session 3 想执行 DDL 操作就会阻塞,因为此时 Session 1 已经占用了 shared MDL,而 DDL 的执行需要先获取 exclusive MDL,因此无法正常执行。

# Session 3> ALTER TABLE tbl_name ADD COLUMN n INT;# 阻塞
复制代码

通过 show processlist 可以看到 ALTER 操作正在等待 MDL。

+----+-----------------+------------------+------+---------+------+---------------------------------+-----------------+| Id | User            | Host             | db   | Command | Time | State                           | Info            |│----+-----------------+------------------+------+---------+------+---------------------------------+-----------------+| 11 | root            | 172.17.0.1:53048 | demo | Query   |    3 | Waiting for table metadata lock | alter table ... |+----+-----------------+------------------+------+---------+------+---------------------------------+-----------------+
复制代码

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

# Session 4> SELECT * FROM tbl_name;# 阻塞
复制代码

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


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

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

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


对于问题 1,有些 ORM 框架默认将用户语句封装成事务执行,如果客户端程序中断退出,还没来得及提交或者回滚事务,就会出现 Session 1 中的情况。那么此时可以在 infomation_schema.innodb_trx 中找出未完成的事务对应的线程,并强制退出。

> SELECT * FROM information_schema.innodb_trx\G*************************** 1. row ***************************trx_id: 421564480355704trx_state: RUNNINGtrx_started: 2022-05-01 014:49:41trx_requested_lock_id: NULLtrx_wait_started: NULLtrx_weight: 0trx_mysql_thread_id: 9trx_query: NULLtrx_operation_state: NULLtrx_tables_in_use: 0trx_tables_locked: 0trx_lock_structs: 0trx_lock_memory_bytes: 1136trx_rows_locked: 0trx_rows_modified: 0trx_concurrency_tickets: 0trx_isolation_level: REPEATABLE READtrx_unique_checks: 1trx_foreign_key_checks: 1trx_last_foreign_key_error: NULLtrx_adaptive_hash_latched: 0trx_adaptive_hash_timeout: 0trx_is_read_only: 0trx_autocommit_non_locking: 0trx_schedule_weight: NULL1 row in set (0.0025 sec)
复制代码

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

8 限制

  • 仅适用于 InnoDB(语法上它可以与其他存储引擎一起使用,如 MyISAM,但 MyISAM 只允许 algorithm = copy,与传统方法相同);

  • 无论使用何种锁(NONE,共享或排它),在开始和结束时都需要一个短暂的时间来锁表(排它锁);

  • 在添加/删除外键时,应该禁用 foreign_key_checks 以避免表复制;

  • 仍然有一些 alter 操作需要 copy 或 lock 表(老方法),有关哪些表更改需要表复制或表锁定,请查看官网;

  • 如果在表上有 ON … CASCADE 或 ON … SET NULL 约束,则在 alter table 语句中不允许 LOCK = NONE;

  • Online DDL 会被复制到从库(同主库一样,如果 LOCK = NONE,从库也不会加锁),但复制本身将被阻止,因为 alter 在从库以单线程执行,这将导致主从延迟问题。

官方参考资料:https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-limitations.html

9 总结

本次和大家一起了解 SQL 的 DDL、DML 及区别,也介绍了 Online DDL 的执行方式。


目前可用的 DDL 操作工具包括 pt-osc,github 的 gh-ost,以及 MySQL 提供的在线修改表结构命令 Online DDL。pt-osc 和 gh-ost 均采用拷表方式实现,即创建个空的新表,通过 select+insert 将旧表中的记录逐次读取并插入到新表中,不同之处在于处理 DDL 期间业务对表的 DML 操作。


到了 MySQL 8.0 官方也对 DDL 的实现重新进行了设计,其中一个最大的改进是 DDL 操作支持了原子特性。另外,Online DDL 的 ALGORITHM 参数增加了一个新的选项:INSTANT,只需修改数据字典中的元数据,无需拷贝数据也无需重建表,同样也无需加排他 MDL 锁,原表数据也不受影响。整个 DDL 过程几乎是瞬间完成的,也不会阻塞 DML,不过目前 8.0 的 INSTANT 使用范围较小,后续再对 8.0 的 INSTANT 做详细介绍吧。


另外,易维平台也提供了 pt-osc 的执行方式,下次再与大家一起分享 pt-osc 的执行方式吧,敬请期待!


作者:刘邓忠

发布于: 2022-09-22阅读数: 4
用户头像

拥抱技术,与开发者携手创造未来! 2018-11-20 加入

我们将持续为人工智能、大数据、云计算、物联网等相关领域的开发者,提供技术干货、行业技术内容、技术落地实践等文章内容。京东云开发者社区官方网站【https://developer.jdcloud.com/】,欢迎大家来玩

评论

发布
暂无评论
MySQL DDL执行方式-Online DDL介绍_Java_京东科技开发者_InfoQ写作社区