乐趣区

关于mysql: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 其大抵流程如下:
  • 建设一个临时文件,扫描表 A 主键的所有数据页;
  • 用数据页中表 A 的记录生成 B+ 树,存储到临时文件中;
  • 生成临时文件的过程中,将所有对 A 的操作记录在一个日志文件(row log)中;
  • 临时文件生成后,将日志文件中的操作利用到临时文件,失去一个逻辑数据上与表 A 雷同的数据文件;
  • 用临时文件替换表 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。

因为 exclusive MDL 的获取优先于 shared MDL,后续尝试获取 shared 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 ... |+----+-----------------+------------------+------+---------+------+---------------------------------+-----------------+

到这一步,后续无论是 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 在从库以单线程执行,这将导致主从提早问题。

官网参考资料:MySQL :: MySQL 5.7 Reference Manual :: 14.13.6 Online DDL Limitations

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 的执行形式吧,敬请期待!

作者:刘邓忠

退出移动版