MySQL之DDL高成本的有解之谜

发布时间:2020-05-12 浏览次数:245

MySQL的DDL, 讨论的背景是MySQL 8.0+InnoDB。

  • DDL(Data Definition Language)

  • 众所周知, DDL定义了数据在数据库中的结构、关系以及权限等。比如CREATE,ALTER,DROP等等。本期我们讨论MySQL 8.0(使用InnoDB存储引擎)在修改表结构时, 究竟会发生什么?

MySQL之DDL高成本的有解之谜-爱可生


  • 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的成本从低到高排序。

MySQL之DDL高成本的有解之谜-爱可生


e.g. ALTER TABLE `t1`        ALTER COLUMN `c1` SET DEFAULT '1';
  • 修改列的默认值不需要变动已有的数据页,仅需要修改表的元信息即可,所以这是成本最低的一种情况,可以"立刻"完成。

MySQL之DDL高成本的有解之谜-爱可生


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


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

MySQL之DDL高成本的有解之谜-爱可生


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


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

MySQL之DDL高成本的有解之谜-爱可生


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


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

MySQL之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如何不互相干扰?

上一篇: MySQL里的Table_map_log_event内容详解

下一篇: 当磁盘IO成瓶颈之后,数据库的性能急剧下降

产品试用 产品试用
400-820-6580 免费电话