专栏首页「3306 Pai」社区MySQL DDL为什么成本高?

MySQL DDL为什么成本高?

大家好,本期讨论MySQL的DDL, 讨论的背景是MySQL 8.0+InnoDB。

  • DDL(Data Definition Language)

众所周知, DDL定义了数据在数据库中的结构、关系以及权限等。比如CREATE,ALTER,DROP等等。

本期我们讨论MySQL 8.0(使用InnoDB存储引擎)在修改表结构时, 究竟会发生什么?

  • DDL与表结构

既然DDL的作用是改变表结构,那表结构在InnoDB引擎中是什么样的呢?如上图,逻辑上, InnoDB表中的数据 可以理解成 按照主键(聚簇索引)顺序存放的,每一行的数据依次排列 (物理上, InnoDB表中的数据按照InnoDB的数据结构B+树进行排列)。

当需要对表增加一列时,会涉及到每一行数据排列的变动,需要重建整张表的数据,可想而知这种变动的成本是高昂的。

  • 然而并不是每一种DDL都要付出这么大的成本,要看具体的分类。MySQL 8.0 将DDL用以下五个维度分类讨论:
    • Instant: 此变更可以"立刻"完成
    • In Place: 此变更由InnoDB引擎独立完成, 不需要使用Redo log等, 可以节省开销
    • Rebuild Table: 此变更会重建聚簇索引, 一般情况下, 涉及到数据变更时才需要重建聚簇索引
    • Permits Concurrent DML: 此变更进行时, 是否允许其他DML变更同一张表. 此特性关系到变更是否会长时间阻塞业务
    • Only Modifies Metadata: 此变更是否只变更元信息, 不涉及数据变更
  • 为了容易理解DDL的分类, 下图中, 我们穷举了MySQL DDL文档中的分类, 列出了这五个维度的每种组合情况, 每种情况中分别挑选一例典型进行讨论。以下分类是按照DDL的成本从低到高排序。
e.g. ALTER TABLE `t1`        ALTER COLUMN `c1` SET DEFAULT '1';

修改列的默认值不需要变动已有的数据页,仅需要修改表的元信息即可,所以这是成本最低的一种情况,可以"立刻"完成。

e.g. ALTER TABLE `t1`        DROP INDEX `idx1`;

删除二级索引除了修改表的元信息之外,需要将对应的二级索引标记为删除状态,因为不需要真的删除,仅仅设置标记量,所以这仍然是一种成本较低的情况。 但由于需要等待所有访问表的事务全部结束后才能成功,所以不算是"立刻"能完成的DDL。

e.g. ALTER TABLE `t1`        ADD INDEX `idx1` (`name`(10) ASC) ;

创建二级索引除了修改表元信息之外,还需要在存储引擎层建立相应的二级索引结构。 为了支持并发的DML操作,MySQL还需要额外维护一份DDL期间的数据变更日志,在DDL操作最后将并发的DML操作回放至新建的二级索引。不过由于二级索引是通过聚簇索引构造,不需要包含所有的行数据,所以这还不能算是一种较高成本的操作。

e.g. ALTER TABLE `t1`        DROP COLUMN `c1`;

删除列和我们之前提到的增加列情况类似,由于需要改动数据行,MySQL在InnoDB引擎内部需要重建聚簇索引 (按照聚簇索引生成临时表, 再取而代之)。同时,为了支持并发的DML操作,还需要维护DDL期间的数据变更日志。可见当数据量较大时,这是一种非常高成本的操作。

e.g. ALTER TABLE `t1`        MODIFY COLUMN `c1` INTEGER;

变更数据列类型,按照文档描述这是一种无法Inplace的操作,即需要MySQL在server层完成一次表的复制,相比由InnoDB内部完成重建,这种操作需要记录Redo log,占用更多的buffer pool。不过由于在执行过程中,无法并发DML操作,不需要记录DDL期间的变更日志。即便如此,这仍然是一种高成本的操作。

  • 运维建议
    • DDL应显式指定ALGORITHM,从低成本(INSTANT)到高成本(COPY)逐一尝试,当不匹配时MySQL会报错。以防我们认为的一个低成本的DDL,因为认为失误而需要重建表,造成运维事故。
    • 在以前版本中,MySQL的DDL都需要重建表,所以会建议将一个表的多个变更写在同一句DDL中,用一次重建实施多个变更。 而现在,如果一句DDL中的多个变更的算法不同,那么会使用其中最高成本的算法。 运维中,需要仔细甄别情况,使得一部分变更可以更快完成上线。
    • DDL语句允许我们选择锁类型和DDL类型,给予我们更好的自由度。 比如当执行删除列时,MySQL默认使用的是Inplace Rebuild操作,锁级别是None (允许并发读写)。如果业务可以妥协,那么可以将锁级别设置为SHARED (允许并发读但阻塞写),这样DDL可以更快完成。
  • 思考题
    • 本期我们说添加列需要重建表,而MySQL引入的腾讯团队的Instant方案 目标就是 让添加列"立刻"完成。那么Instant版本的添加列是如何完成的?
    • Instant 是否完全不影响业务,是否是真正的"立刻"完成?
    • 对于Inplace Rebuild,重建数据的过程与并发的DML如何不互相干扰?
  • 本期思考题,我们将在之后几期文章中逐一解析。

本文分享自微信公众号 - 3306pai(pai3306),作者:黄炎 王悦 周海鸣

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-03-26

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 转载|MySQL Online DDL,还是要谨慎

    其他的DDL操作相对比较少,所以本文就不讨论了。 此外,本文也不讨论非InnoDB引擎以及非普通索引(如全文索引、空间索引)的场景。

    田帅萌
  • MySQL8.0.20 正式发行(GA)

    MySQL的最新版本8.0.20正式发行。与之前8.0的系列版本一样,这次的发行版,除了包含缺陷修复,也同样包扩新功能。让我们快速浏览一下。

    田帅萌
  • MySQL很慢... 怎么破??

    MySQL性能慢,多半是SQL引起的(慢查询日志会把执行慢的SQL,一五一十的记录下来,就像你的身体一样诚实..)需要根据慢查询日志的内容来优化SQL。

    田帅萌
  • MySQL 8.0 之原子DDL

    听到原子这个关键字大家是不是联想到事务的ACID的原子性?两者相似,事务/语句执行要么全部成功,要么全部失败。MySQL 8.0 之前的版本 DDL 是非原子性...

    用户1278550
  • MySQL DDL详情揭露

    MySQL中DDL语句,即数据定义语言,用于创建、删除、修改、库或表结构,对数据库或表的结构操作。常见的有create,alter,drop等。这类语句通常会耗...

    MySQL技术
  • 2016 RSA 大会,布展日抢先看

    2016 RSA 大会抢先看 美国当地时间2月28日,RSA大会迎来新的进程,如火如荼的进场搭建工作进入到收尾阶段。清晨的莫斯科尼中心彩旗飘扬,小安早早地就来到...

    安恒信息
  • 【小安看会】RSA2016全程全景直播之—— 偷窥布展日

    2016 RSA 大会抢先看 美国当地时间2月28日,RSA大会迎来新的进程,如火如荼的进场搭建工作进入到收尾阶段。清晨的莫斯科尼中心彩旗飘扬,小安早早地就来到...

    安恒信息
  • Hadoop机架感知

    HDFS作为Hadoop中的一个分布式文件系统,而且是专门为它的 MapReduce设计,所以HDFS除了必须满足自己作为分布式文件系统的高可靠性外,还必须为M...

    大数据技术与架构
  • 网易OpenStack部署运维实战

    糖豆贴心提醒,本文阅读时间8分钟 OpenStack简介 OpenStack 是一个开源的 IaaS 实现,它由一些相互关联的子项目组成,主要包括计算、存储、...

    小小科
  • HBase伪分布式安装(HDFS)+ZooKeeper安装+HBase数据操作+HBase架构体系

    HBase1.2.2伪分布式安装(HDFS)+ZooKeeper-3.4.8安装配置+HBase表和数据操作+HBase的架构体系+单例安装,记录了在Ubunt...

    王小雷

扫码关注云+社区

领取腾讯云代金券