专栏首页「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

本文参与 腾讯云自媒体分享计划 ,欢迎热爱写作的你一起参与!

如有侵权,请联系 yunjia_community@tencent.com 删除。
登录 后参与评论
0 条评论

相关文章

  • Mysql中DDL, DML, DCL, 和TCL是什么?

    在一些公司中提交给测试团队的SQL脚本会划分为DDL、DML等,但这些概念到底是如何定义的呢?

    猿人谷
  • MySQL 8.0 之 Online DDL快速加列

    前几天同事问了我一个问题:业务A从MySQL迁移到MongoDB的原因是什么?

    AsiaYe
  • MySQL 8.0 数据字典有哪些变化?

    墨墨导读:MySQL8.0 数据字典(Data Dictionary)也在进化中。MyISAM系统表全部换成InnoDB表 ,支持原子DDL。复杂度增加了。考虑...

    数据和云
  • MySQL 8.0新特性 — 事务性数据字典与原子DDL

    事务性数据字典与原子DDL,是MySQL 8.0推出的两个非常重要的新特性,之所以将这两个新特性放在一起,是因为两者密切相关,事务性数据字典是前提,原子DDL是...

    brightdeng@DBA
  • 如何准确判断什么时候可以给大表加索引 - 崔笑颜的博客

    以社交平台的用户表为例,随着业务的快速增长,用户表user单表数据量越来越大,此时,如果我们想给user表添加索引,数据规模对添加过程的影响势必要考虑在内,但是...

    崔笑颜
  • 深入理解MDL元数据锁

    当你在MySQL中执行一条SQL时,语句并没有在你预期的时间内执行完成,这时候我们通常会登陆到MySQL数据库上查看是不是出了什么问题,通常会使用的一个命令就是...

    MySQL技术
  • mysql 主从同步详解分析

    Mysql为了解决这个风险并提高复制的性能,将Slave端的复制改为两个进程来完成。提出这个改进方案的人是Yahoo!的一位工程师“Jeremy Zawodny...

    后端技术探索
  • 我就想加个索引,怎么就这么难?

    今天,我们通过模拟案例以及原理分析,去弄清楚MySQL中DDL的风险,以及如何避免事故发生。

    用户4172423
  • MySQL中的Online DDL(第一篇)(r11笔记第3天)

    记得有一天快下班的时候,一位开发同事找到我说,需要对一个表做变更,数据量据说有上千万,而当时是使用的MySQL版本是5.5,这可如何是好,对于在线业务要求高的情...

    jeanron100
  • 2020-01-26:mysql8.0做了什么改进?

    在MySQL5.7中,所有的临时表都被创建在一个叫“ibtmp1”的表空间中。另外,临时表的元数据也将存储在内存中(不再存储在frm文件中)。

    福大大架构师每日一题
  • MYSQL 的老大难,instant ,inplace,copy, DDL 怎么弄, 我不想迷迷糊糊

    DDL 操作一直是我们的 MYSQL 的一个软肋,从MYSQL 5.6 其实相关的alter 语句已经有了改变,也就是题目的的inplace 和 copy 。其...

    AustinDatabases
  • 技术分享 | MySQL 改表工单后台逻辑实现

    DDL 操作可能是 DBA 最头疼的一项工作之一,也是最日常的一项工作了。动不动就要加个字段,扩个长度。如果不幸前期设计不合理的系统,那后期维护起来就真的是叫爹...

    爱可生开源社区
  • MySQL 小览

    create table xxx alter table xxx drop table xxx

    MickyInvQ
  • 面试官:数据库delete表数据,为啥磁盘空间还是被一直占用

    最近有个上位机获取下位机上报数据的项目,由于上报频率比较频繁且数据量大,导致数据增长过快,磁盘占用多。

    Java_老男孩
  • 面试官问:数据库 delete 表数据,磁盘空间还是被一直占用,为什么?

    最近有个上位机获取下位机上报数据的项目,由于上报频率比较频繁且数据量大,导致数据增长过快,磁盘占用多。

    架构师修行之路
  • Mysql DDL出现长时间等待MDL问题分析

    给表新增字段时,发现锁表了,查看进程,提示Waiting for table metadata lock,等待锁释放;然而蛋疼的是几分钟过去了,依然没有任何的进...

    一灰灰blog
  • MySQL深入学习第十三篇-为什么表数据删掉一半,表文件大小不变?

    经常会有同学来问我,我的数据库占用空间太大,我把一个最大的表删掉了一半的数据,怎么表文件的大小还是没变?

    越陌度阡
  • MYSQL 8 Serialized Dictionary Information

    随着MYSQL 8 越来越稳定,并且开始使用的人和公司越来越多起来,掌握MYSQL 8 的工具变得越来越重要。不赶到别人前头,那就只能follower.

    AustinDatabases
  • MySQL Cases-MySQL CTAS的变化create table as

    但是再另外一个环境执行却没有任何问题,GTID模式和强一致性也开启了,那么是为什么?

    姚崇

扫码关注云+社区

领取腾讯云代金券