详解MySQL原生Online DDL:从历史演进到原理及使用

这篇具有很好参考价值的文章主要介绍了详解MySQL原生Online DDL:从历史演进到原理及使用。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

MySQL 中的在线DDL(OnLine DDL)功能是一种强大的工具,可以在不中断表或数据库的情况下进行数据定义语言(DDL)操作。通过在线DDL,使得在对表进行结构变更时,仍然能够进行读写操作,避免了整个表的锁定和阻塞。

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

在 MySQL 5.6 中,官方开始支持更多的 ALTER TABLE 类型操作来避免数据拷贝,同时支持了在线上 DDL 的过程中不阻塞 DML 操作,真正意义上的实现了 Online DDL。然而并不是所有的 DDL 操作都支持在线操作,后面会附上 MySQL 官方文档对于 DDL 操作的总结。到了 MySQL 5.7,在 5.6 的基础上又增加了一些新的特性,比如:增加了重命名索引支持,支持了数值类型长度的增大和减小,支持了 VARCHAR 类型的在线增大等。但是基本的实现逻辑和限制条件相比 5.6 并没有大的变化。MySQL 8.0 对 DDL 的实现重新进行了设计,其中一个最大的改进是 DDL 操作支持了原子特性。另外,Online DDL 的 ALGORITHM 参数增加了一个新的选项:INSTANT,只需修改数据字典中的元数据,无需拷贝数据也无需重建表,同样也无需加排他 MDL 锁,原表数据也不受影响。整个 DDL 过程几乎是瞬间完成的,也不会阻塞 DML。

了解 Online DDL 先了解一下之前 DDL 的 2 种算法 copy 和 inplace。

Copy算法:

  1. 按照原表定义创建一个新的临时表
  2. 对原表加写锁(禁止 DML,允许 select)
  3. 步骤 1)建立的临时表执行 DDL
  4. 将原表中的数据 copy 到临时表
  5. 释放原表的写锁
  6. 将原表删除,并将临时表重命名为原表
    可见,采用 copy 方式期间需要锁表,禁止 DML,因此是非 Online 的。比如:删除主键、修改列类型、修改字符集,这些操作会导致行记录格式发生变化(无法通过全量 + 增量实现 Online)。

Inplace算法:

在原表上进行更改,不需要生成临时表,不需要进行数据 copy 的过程。根据是否变更行记录格式,分为两类:
1)rebuild:需要重建表(重新组织聚簇索引)。比如 optimize table、添加索引、添加/删除列、修改列 NULL/NOT NULL 属性等;
2)no-rebuild:不需要重建表,只需要修改表的元数据,比如删除索引、修改列名、修改列默认值、修改列自增值等。

对于 rebuild 方式实现 Online 是通过缓存 DDL 期间的 DML,待 DDL 完成之后,将 DML 应用到表上来实现的。
说明:

  1. 在 copy 数据到新表期间,在原表上是加的 MDL 读锁(允许 DML,禁止 DDL)
  2. 在应用增量期间对原表加 MDL 写锁(禁止 DML 和 DDL)
  3. 根据表A重建出来的数据是放在 tmp_file 里的,这个临时文件是 InnoDB 在内部创建出来的,整个 DDL 过程都在 InnoDB 内部完成。对于 server 层来说,没有把数据挪动到临时表,是一个原地操作,这就是“inplace”名称的来源。

MySQL中,表级别的锁有2种
一种是我们通常说的表锁,由InnoDB引擎实现,如lock tables …
read/write,表锁影响较大,不常用。另一种表级别的锁是MDL( metadata lock
),由Server层实现,MDL我们不显式使用,是在访问一个表时由数据库自动加的,对表记录增删改查时,加MDL读锁;对表结构进行变更时,加MDL写锁。MDL锁,读读不互斥,读写、写写互斥。
哪些常用操作“锁表”
创建二级索引(二级索引是指除主键索引之外的索引)、删除索引、重命名索引、改变索引类型——不“锁表”。
添加字段、删除字段、重命名字段、调整字段顺序、设置字段默认值、删除字段默认值、修改auto-increment值、调整字段允许NULL、调整字段不允许NULL
—— 不“锁表”。
扩展Varchar字段大小——不“锁表”。
更改字段数据类型,如varchar改成text——“锁表”

默认情况下,MySQL就是支持online的DDL操作的,在online的DDL语句执行的过程中,MySQL会尽量少使用锁的限制,我们不需要特殊的操作来启用它。

MySQL在选择的时候,尽量少使用锁,但是不排除它会选择使用锁。而如果我担心它选择了锁而导致我们的表不能读也不能写,显然这不是我们想要的结果,我们希望:如果选择了锁就不要执行,直接退出执行;如果没有选择锁就执行。想要达到我们希望的这个效果,该怎么做呢?

可以在执行我们的online DDL语句的时候,使用ALGORITHM和LOCK关键字,这两个关键字在我们的DDL语句的最后面,用逗号隔开即可。示例如下:

ALTER TABLE tbl_name ADD COLUMN col_name col_type, ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM有如下选项
INPLACE:替换:直接在原表上面执行DDL的操作。
COPY:复制:使用一种临时表的方式,克隆出一个临时表,在临时表上执行DDL,然后再把数据导入到临时表中,在重命名等。这期间需要多出一倍的磁盘空间来支撑这样的 操作。执行期间,表不允许DML的操作。
DEFAULT:默认方式,有MySQL自己选择,优先使用INPLACE的方式。

LOCK有如下选项
SHARE:共享锁,执行DDL的表可以读,但是不可以写。
NONE:没有任何限制,执行DDL的表可读可写。
EXCLUSIVE:排它锁,执行DDL的表不可以读,也不可以写。
DEFAULT:默认值,也就是在DDL语句中不指定LOCK子句的时候使用的默认值。如果指定LOCK的值为DEFAULT,那就是交给MySQL子句去觉得锁还是不锁表。不建议使用,如果你确定你的DDL语句不会锁表,你可以不指定lock或者指定它的值为default,否则建议指定它的锁类型。

执行DDL操作时,ALGORITHM选项可以不指定,这时候MySQL按照INSTANT、INPLACE、COPY的顺序自动选择合适的模式。也可以指定ALGORITHM=DEFAULT,也是同样的效果。如果指定了ALGORITHM选项,但不支持的话,会直接报错。

注意:在执行OnlineDDL之前,要在非业务高峰期去执行,并要确认待执行的表上面没有未提交的事务、锁等信息。可以通过如下的SQL语句查看是否有事务和锁等信息。

select * from information_schema.innodb_locks;
select * from information_schema.innodb_trx;
select * from information_schema.innodb_lock_waits;
select * from information_schema.processlist;

理解DDL操作的需求和挑战
DDL操作涉及对数据库表结构的修改,例如添加/删除列、修改列定义、添加/删除索引等。在以往的版本中,执行这些DDL操作时需要锁定整个表,对数据库的可用性产生了负面影响。因此,实现在线DDL成为了提高系统灵活性和性能的重要需求。

MySQL 5.7的在线DDL功能特点
MySQL 5.7通过InnoDB存储引擎实现了在线DDL功能的改进。以下是该功能的主要特点:

支持添加辅助索引:可以在运行中的表上添加辅助索引,而不会对整个表进行锁定。
支持修改列定义:可以在线修改列的数据类型、长度等定义。
支持修改字符集和排序规则:可以在线修改表的字符集和排序规则设置。
支持重命名列:可以在不影响正在进行的读写操作的情况下,对表中的列进行重命名。
实现原理和优化
在线DDL功能的实现涉及以下关键步骤和优化:
1 创建临时表:通过创建临时表来存储将要进行的DDL操作所需的新结构。这样,旧表仍然可用于读写操作。
2 数据复制和同步:将旧表中的数据逐步复制到临时表中,并保持旧表数据与临时表数据的同步。这一过程确保了数据在DDL操作期间的完整性和一致性。
3 变更捕获和重放:通过使用日志和重做日志等机制,捕获在执行DDL操作期间发生的数据变更,并将其重放到临时表中。这确保了DDL操作完成后数据的一致性。
4 最终切换:当DDL操作完成时,数据库引擎将在适当的时机切换到临时表,使其成为新的表结构,并且对新表进行后续的读写操作。
使用限制和注意事项
尽管MySQL 5.7的在线DDL功能提供了一种近似在线的体验,但仍然有一些限制和注意事项:
并非所有DDL操作都支持在线执行,某些操作仍然需要锁定整个表。
在进行DDL操作期间,可能会占用较多的系统资源,因此在高负载时应谨慎使用。
进行在线DDL操作时,需要对操作进行充分的评估和测试,以确保数据的完整性和一致性。

各版本支持

本文数据全部来自 MySQL 官方文档,此处进行一个集中的整理和总结:
https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html
https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html

mysql在线ddl,DB,mysql,数据库,java,spring cloud,sql,大数据,spring
mysql在线ddl,DB,mysql,数据库,java,spring cloud,sql,大数据,spring
mysql在线ddl,DB,mysql,数据库,java,spring cloud,sql,大数据,spring
mysql在线ddl,DB,mysql,数据库,java,spring cloud,sql,大数据,spring
结合上面的表格,对 MySQL 当前 DDL 的执行模式总结如下:

INSTANT DDL 是 MySQL 8.0 引入的新功能,当前支持的范围较小,包括:
修改二级索引类型
新增列
修改列默认值
修改列 ENUM 值
重命名表

在执行 DDL 操作时,MySQL 内部对于 ALGORITHM 的选择策略是:如果用户显式指定了 ALGORITHM,那么使用用户指定的选项;如果用户未指定,那么如果该操作支持 INPLACE 则优先选择 INPLACE,否则选择 COPY;当前不支持 INPLACE 的操作主要有:
删除主键
修改列数据类型
修改表字符集

我们常说的 Online DDL,其实是从 DML 操作的角度描述的,如果 DDL 操作不阻塞 DML 操作,那么这个 DDL 就是 Online 的。当前非 Online 的 DDL 其实已经比较少了,主要有:
新增全文索引
新增空间索引
删除主键
修改列数据类型
指定表字符集
修改表字符集

更多详细的示例请参考上面的官方文档的地址。

几个问题
最后讨论几个非常容易混淆的问题:

Online DDL 不会锁表,可以随意的执行。
支持 INPLACE 算法的 DDL 一定是 Online 的。
对于支持 INPLACE 算法的 DDL,DDL 操作是原地修改数据,不需要额外的数据空间。

Q1: Online DDL 会不会锁表
Online DDL 会不会锁表?要回答这个问题,首先要明确“锁表”的含义。很多 MySQL 用户经常在表无法正常的进行 DML 时就觉得是锁表了,这种说法其实过于宽泛,实际上能够影响 DML 操作的锁至少包括以下几种(默认为 InnoDB 表):

MDL 锁
表锁
行锁
GAP 锁

其中除了 MDL 锁是在 Server 层加的之外,其它三种都是在 InnoDB 层加的。具体的加锁逻辑不在此进行展开,但是需要明确一点:所有的操作(不管是 DDL 还是 DML 还是查询语句)都需要先拿 Server 层的 MDL 锁,然后再去拿 InnoDB 层的某个需要的锁。一个 DDL 的基本过程是这样的:

首选,在开始进行 DDL 时,需要拿到对应表的 MDL X 锁,然后进行一系列的准备工作;
然后将 MDL X 锁降级为 MDL S 锁,进行真正的 DDL 操作;
最后再次将 MDL S 锁升级为 MDL X 锁,完成 DDL 操作,释放 MDL 锁;
所以在真正执行 DDL 操作期间,确实是不会“锁表”的,但是如果在第一阶段拿 MDL X 锁时无法正常获取,那就可能真的会“锁表了”。一个简单的例子如下:

# session 1
select sleep(500) from mytest.t1;

# session 2
optimize table mytest.t1;

# session 3
select * from mytest.t1;

session 1 模拟了一个慢查询,然后 session 2 开始进行 DDL 操作,无法拿到 MDL X 锁,处于等到中。此时 session 3 需要执行一个查询,发现无法执行。实际上,在 session 1 结束前,表 t1 的所有操作都无法进行了,也可以说表 t1 “锁表”了。MySQL 5.7/8.0 可以在开启 performance_schema 的情况下直接查询 metadata_locks 表。阿里云 RDS 5.6 版本新增了 I_S.MDL_INFO 表,提供 MDL 的查询。

MySQL [performance_schema]> select * from metadata_locks where OBJECT_NAME = 't1';
+-------------+---------------+-------------+-------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE            | LOCK_DURATION | LOCK_STATUS | SOURCE            | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+---------------+-------------+-------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
| TABLE       | mytest        | t1          | NULL        |       140730442220576 | SHARED_READ          | TRANSACTION   | GRANTED     | sql_parse.cc:5916 |            1083 |             24 |
| TABLE       | mytest        | t1          | NULL        |       140730576178368 | SHARED_NO_READ_WRITE | TRANSACTION   | PENDING     | sql_parse.cc:5916 |            1091 |              3 |
| TABLE       | mytest        | t1          | NULL        |       140730374843168 | SHARED_READ          | TRANSACTION   | PENDING     | sql_parse.cc:5916 |            1092 |              3 |
+-------------+---------------+-------------+-------------+-----------------------+----------------------+---------------+-------------+-------------------+-----------------+----------------+
3 rows in set (0.00 sec)

明确了上面的概念之后,再回到我们的问题,Online DDL 是不是不锁表?如果非要回答,那么只能说,Online DDL 并不是绝对安全,更不是可以随意的执行。线上操作还是需要在业务低峰期谨慎操作。

Q2: 支持 INPLACE 算法的 DDL 一定是 Online 的

从概念上来说,INPLACE 和 Online 是两个不同维度的事情。COPY 和 INPLACE 指的是 DDL 内部的执行逻辑,可以简单的理解成:COPY 是在 Server 层的操作,INPLACE 是在 InnoDB 层的操作。
而用户更加关心 Online 与否,通常只与一个问题有关:是否允许并发 DML。两个基本结论:
COPY 算法执行的 DDL 肯定不是 Online 的;
INPLACE 算法执行的 DDL 不一定是 Online 的;

Q3: INPLACE DDL 需不需要额外的数据空间
前面我们提到过,MySQL 内部对于 DDL 的 ALGORITHM 有两种选择:INPLACE 和 COPY(8.0 新增了 INSTANT,但是使用范围较小)。COPY 算法理解起来相对简单一点:创建一张临时表,然后将原表的数据拷贝到临时表中,最后再用临时表替换原表。对于上面的步骤,由于需要将原表的数据拷贝到临时表中,所以肯定需要消耗额外的数据空间。

那么对于支持 INPLACE 算法的 DDL,是不是不需要额外的数据空间?
答案是:需要。其实之所以会问这个问题,还是因为对 INPLACE 本身的理解出现了偏差。简单来说:INPLACE 描述的是表,而不是数据文件。只要不创建临时表,那么都是 INPLACE 的。

实际上,很多 INPLACE DDL 都会重建表(会创建临时数据文件),所以都会需要额外的数据空间,例如:

增加主键
重建主键
新增列(8.0 支持 INSTANT DDL,不需要)
删除列
调整列顺序
删除列默认值
增加列默认值
修改表的 ROW_FORMAT
OPTIMIZE 表
文章来源地址https://www.toymoban.com/news/detail-823788.html

到了这里,关于详解MySQL原生Online DDL:从历史演进到原理及使用的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • MySQL 8.0 Reference Manual(读书笔记84节-- InnoDB and Online DDL (4))

    Disk space requirements for online DDL operations are outlined【ˈaʊtlaɪnd 概述;略述;显示…的轮廓;勾勒…的外形;】 below. The requirements do not apply to operations that are performed instantly. • Temporary log files: A temporary log file records concurrent DML when an online DDL operation creates an index or alters a table. The tempora

    2024年04月08日
    浏览(51)
  • 云原生演进中的AI算力高效使用

    0 1  云原生技术的普及与发展 云原生技术是一种基于容器技术的轻量级、高可用的应用架构,具有弹性扩展、快速部署、统一管理等特点。随着企业对敏捷开发和快速迭代的需求不断增加,云原生技术的普及与发展已成为不可逆转的趋势。 图1. 云原生技术发展之路 云原生技

    2024年01月19日
    浏览(42)
  • 开始MySQL之路—— DDL语法、DML语法、DQL语法基本操作详解

    DDL(Data Definition Language) 数据定义语言,该语言部分包括以下内容。 对数据库的常用操作 对表结构的常用操作 修改表结构 1: 查看当前所有的数据库 show databases; 2:创建数据库 create database if not exists 数据库名称; create database 数据库名称; 3:选择使用哪一个数据库 use 数据库

    2024年02月10日
    浏览(44)
  • 华为云CodeArts IDE Online快速入门和使用详解

    文档参考: CodeArts IDE Online CodeArts IDE Online是云端开发环境服务,向开发者提供按需配置、快速获取的工作空间(包含编辑器和运行环境),支持完成环境配置、代码阅读、编写代码、构建、运行、调试、预览等操作,并支持对接多种代码仓库。 云化和轻量化 : 依托华为云的计

    2024年02月08日
    浏览(92)
  • 干货 | Elasticsearch Java 客户端演进历史和选型指南

    Elasticsearch 官方提供了很多版本的 Java 客户端,包含但不限于: Transport 客户端 Java REST 客户端 Low Level REST 客户端 High Level REST 客户端 Java API 客户端 非官方的 Java 客户端,包含但不限于: Jest 客户端 BBoss 客户端 Spring Data Elasticsearch 客户端 ...... 写出来的就接近十款客户端! El

    2023年04月11日
    浏览(51)
  • 浅析云原生时代的服务架构演进

    摘要: 相比于传统的微服务架构,云原生和 serverless 技术更加灵活、高效,能够更好地满足用户的需求。 本文分享自华为云社区《《凤凰架构》学习和思考——云原生时代的服务架构演进史》,作者:breakDawn。 随着云原生的概念越来越火,服务的架构应该如何发展和演进,

    2023年04月10日
    浏览(76)
  • 云原生:从基本概念到实践,解析演进与现状

    本文仅用于简单普及,达到的目的是给没接触过或者很少接触过这方面的人一点感觉,阅读起来会比较轻松,作者深知短篇幅文章是不可能真正教会什么的,所以也不会出现 RTFM 的内容。 提到云原生(Cloud Native)可能部分人会陌生,但是如果说 Serverless 相信很多人就知道了,

    2024年02月05日
    浏览(38)
  • 【云原生】软件架构的演进以及各个架构的优缺点

    软件架构是指在设计和构建软件系统时,对系统的组织结构、组件、模块、接口以及它们之间的关系和行为进行规划和定义的过程。它描述了软件系统的整体结构和组成部分之间的关系,以及系统的行为和功能。 软件架构的演进大致可以分为: 单机架构 应用数据分离架构 应

    2024年02月11日
    浏览(46)
  • 从传统云架构到云原生生态体系架构的演进

    随着科技的不断发展,云计算领域也经历了巨大的变革。这一演进的核心焦点是从传统云架构过渡到云原生生态体系架构,这个过程在过去的几年里已经发生了显著变化。本文将深入探讨这一演进过程,以及它对企业和技术生态系统的影响。 在云计算兴起之初,虚拟化技术是

    2024年02月08日
    浏览(40)
  • 【云原生】Docker 详解(二):Docker 架构及工作原理

    Docker 在运行时分为 Docker 引擎 (服务端守护进程) 和 客户端工具 ,我们日常使用各种 docker 命令 ,其实就是在使用 客户端工具 与 Docker 引擎 进行交互。 Docker 是一个客户端-服务器(C/S)架构程序。Docker 客户端只需要向 Docker 服务器或者守护进程发出请求,服务器或者守护

    2024年02月13日
    浏览(41)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包