大家好,本期讨论MySQL的DDL, 讨论的背景是MySQL 8.0+InnoDB。
众所周知, DDL定义了数据在数据库中的结构、关系以及权限等。比如CREATE,ALTER,DROP等等。
本期我们讨论MySQL 8.0(使用InnoDB存储引擎)在修改表结构时, 究竟会发生什么?
既然DDL的作用是改变表结构,那表结构在InnoDB引擎中是什么样的呢?如上图,逻辑上, InnoDB表中的数据 可以理解成 按照主键(聚簇索引)顺序存放的,每一行的数据依次排列 (物理上, InnoDB表中的数据按照InnoDB的数据结构B+树进行排列)。
当需要对表增加一列时,会涉及到每一行数据排列的变动,需要重建整张表的数据,可想而知这种变动的成本是高昂的。
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期间的变更日志。即便如此,这仍然是一种高成本的操作。