MySQL8.0之快速加列-阿里云开发者社区

开发者社区> 数据库> 正文

MySQL8.0之快速加列

简介: MySQL8.0 Online DDL

  在正式介绍MySQL8.0的快速加列特性前,先来了解下什么是Online DDL,为什么需要快速加列这一特性。

一、什么是Online DDL

1.1 基本概念

  Online DDL是在MySQL5.6版本后加入的特性,在5.7得到增强,Online DDL提供了在表更改过程中并发执行DML的功能。

1.2 Online DDL语法

  具体语法如下:

alter table
  ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
  ADD   [COLUMN] col_name  column_definition [FIRST|AFTER col_name]
  CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]
  MODIFY [COLUMN] col_name column_definition
 [FIRST | AFTER col_name],
ALGORITHM [=] {DEFAULT|INSTANT|INPLACE|COPY}  LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE};

LOCK [=] {DEFAULT|NONE|SHARED|EXCLUSIVE}

 * DEFAULT:MySQL自己选择锁定资源最少的方式
 * NONE:支持SELECT和DML
 * SHARED:支持SELECT、不支持DML
 * EXCLUSIVE:不支持SELECT和DML

ALGORITHM [=] {DEFAULT|INSTANT|INPLACE|COPY}

 * DEFAULT:MySQL自己选择锁定资源最少的方式
 * INSTANT:只需要更新数据字典中的元数据,更改很快(>=8.0.12)
 * INPLACE:表示不需要创建临时表
 * COPY:执行DDL的时候会创建临时表

  如果选项不支持此DDL类型,操作立即停止。默认情况下,MySQL会在允许的情况下使用尽可能少的锁定来执行操作。

1.3 Online DDL ALGORITHM选项详解

copy:
  这部分是offline的,在DDL执行期间其他DML不能并行,也是5.6版本前的DDL执行方法。其间生成临时表(server层的操作支持所有引擎),用于写入原表修改过的数据,同时在原表路径下会生成临时表的.frm和.ibd文件。在innodb中不支持使用inplace的操作都会自动使用copy方式执行,而MyISAM表只能使用copy方式。
inplace:
  所有操作在innodb引擎层完成,不需要经过临时表的中转。除全文索引、空间索引两种特殊索引创建外,其他以inplace方式执行的操作都是online的,执行期间其他DML操作可以并行,其中又以是否重建表又分为两个部分rebuild和no-rebuild。
  rebuild部分涉及表的重建,在原表路径下创建新的.frm和.ibd文件,消耗的IO会较多。期间(原表可以修改)会申请row log空间记录DDL执行期间的DML操作,这部分操作会在DDL提交阶段应用新的表空间中。
  no-rebuild部分由于不涉及表的重建,除创建添加索引,会产生部分二级索引的写入操作外,其余操作均只修改元数据项,即只在原表路径下产生.frm文件,不会申请row log,不会消耗过多的IO,速度通常很快。
instant:
  仅修改数据字典中的元数据。在准备和执行期间,不会在表上采用独占元数据锁,并且表数据不受影响,从而使操作立即生效,允许并发DML。

1.4 Online DDL基本流程

inplace:
  准备阶段:
1、对表加元数据共享升级锁,并升级为排他锁;(此时DML不能并行)
2、在原表所在的路径下创建.frm和.ibd临时中转文件;(no-rebuild除创建二级索引外只创建.frm文件,其中添加二级索引操作最为特殊,该操作属于no-rebuild不会生成.ibd,但实际上对.ibd文件却做了修改,该操作会在参数tmpdir指定路径下生成临时文件,用于存储索引排序结果,然后再合并到.ibd文件中)
3、申请row log空间,用于存放DDL执行阶段产生的DML操作。(no-rebuild不需要)
  执行阶段:
1、释放排他锁,保留元数据共享升级锁;(此时DML可以并行)
2、扫描原表主键以及二级索引的所有数据页,生成 B+ 树,存储到临时文件中;
3、将所有对原表的DML操作记录在日志文件row log中。
  提交阶段:
1、升级元数据共享升级锁,产生排他锁锁表;(此时DML不能并行)
2、重做row log中的内容;(no-rebuild不需要)
3、重命名原表文件,将临时文件改名为原表文件名,删除原表文件;
4、提交事务,变更完成。
  在DDL期间产生的数据,会按照正常操作一样,写入原表,记redolog、undolog、binlog,并同步到从库去执行,只是额外会记录在row log中,并且写入row log的操作本身也会记录redolog,而在提交阶段才进行row log重做,此阶段会锁表,此时主库(新表空间+row log)和从库(表空间)数据是一致的,在主库DDL操作执行完成并提交,这个DDL才会写入binlog传到从库执行,在从库执行该DDL时,这个DDL对于从库本地来讲仍然是online的,也就是在从库本地直接写入数据是不会阻塞的,也会像主库一样产生row log。但是对于主库同步过来DML,此时会被阻塞,是offline的,DDL是排他锁的在复制线程中也是一样,所以不只会阻塞该表,而是后续所有从主库同步过来的操作(主要是在复制线程并行时会排他,同一时间只有他自己在执行)。所以大表的DDL操作,会造成同步延迟。
copy:
1、锁表,期间DML不可并行执行
2、生成临时表以及临时表文件(.frm .ibd)
3、拷贝原表数据到临时表
4、重命令临时表及文件
5、删除原表及文件
6、提交事务,释放锁

1.5 Online DDL空间要求

row log空间:
  row log空间每次申请的大小由 innodb_sort_buffer_size决定,最大值由innodb_online_alter_log_max_size,该值默认为128M,支持动态修改。对于更新频繁的表来讲,如果预计在DDL期间对表的更新操作存储可能超过128M时,需要为本次操作增大该值。当然如果不涉及rebuild操作时,不需要考虑该值。如果提示DB_ONLINE_LOG_TOO_BIG错误,则是由innodb_online_alter_log_max_size空间不足造成的。
索引排序空间:
  如果DDL操作涉及二级索引的创建,会在MySQL临时目录产生临时排序文件,将中间的排序结果写入文件,最终将内容合并到最终表或索引中,然后自动删除临时排序文件。这个路径默认为mysql全局参数tmpdir指定(默认值为/tmp,如果手动指定了innodb_tmpdir参数的路径,则tmpdir会被覆盖),且不会在原始表的目录中创建临时排序文件。tmpdir需要保证能够容纳要创建的二级索引,临时排序文件最大可能需要的空间等于表中的数据量加上索引,否则执行将报错。
中间表空间:
  如果DDL操作涉及rebuild表,则会在原表所在目录创建临时表空间文件(以#sql开头),临时表空间大小需要等于原表大小,重建完成后会自动重命名临时表空间,删除原表空间。所以执行rebuild操作时需要保证原表所在路径下有足够空间。

1.6 Online DDL存在的问题

  Online DDL虽然可以在DDL期间提高响应能力和可用性,但是还是存在以下问题:

1、对超级大表的加列等操作通常可能耗时几个小时甚至数天的时间
2、在DDL的过程中产生的临时表会占用磁盘空间
3、DDL带来的复制延迟问题

二、类型支持

  下面是各版本对Online DDL的支持:
d13349f6c13c953ad09df30dbe2bf63e
图片来源:http://blog.51cto.com/wangwei007/2155574
  在图中DDL被分为五个维度:

  • Instant:此变更可以"立刻"完成;
  • In Place:此变更由InnoDB引擎独立完成,不需要使用Redo log等,可以节省开销,否则使用copy;
  • Rebuild Table:此变更会重建聚簇索引,一般情况下,涉及到数据变更时才需要重建聚簇索引;
  • Permits Concurrent DML:此变更进行时,是否允许其他DML变更同一张表。此特性关系到变更是否会长时间阻塞业务;
  • Only Modifies:此变更是否只变更元信息,不涉及数据变更;

三、快速加列

3.1 快速加列支持类型

  官方文档列出了一些可以快速DDL的操作,大体包括:

修改索引类型
Add column 
  当一条alter语句中同时存在不支持instant的ddl时,则无法使用
  只能顺序加列
  不支持压缩表、不支持包含全文索引的表
  不支持临时表,临时表只能使用copy的方式执行DDL
  不支持那些在数据词典表空间中创建的表
修改/删除列的默认值、修改索引类型
修改ENUM/SET类型的定义
  存储的大小不变时
  向后追加成员
增加或删除类型为virtual的generated column
RENAME TABLE操作

3.2 立刻加列的限制

  虽然立刻加列这一特性十分好用,但也存在着一些限制:

1、当一条alter语句中同时存在不支持instant的ddl时,则无法使用
2、只能顺序加列
3、不支持压缩表、不支持包含全文索引的表,不支持临时表
4、不支持那些在数据词典表空间中创建的表
5、修改ENUM/SET类型的定义时,存储的大小不变,向后追加成员

3.3 立刻加列的实现

在8.0版本之前:
  进行加列操作时,所有的数据行都必须要增加一段数据;当改变数据行的长度,就需要重建表空间,数据字典中的列定义也会被更新。以上操作的问题在于每次加列操作都需要重建表空间,这就需要大量IO以及大量的时间。
在8.0版本中:
  立刻加列时,只会变更数据字典中的内容:在列定义中增加新列的定义,增加新列的默认值。(information_schema.INNODB_TABLES,information_schema.INNODB_COLUMNS)
  立刻加列后,当要读取表中的数据时:由于立刻加列没有变更行数据,读取的行数据为原列数对应的数据;MySQL会将新增的列的默认值,追加到读取的数据后面。
  当读取数据行时,通过判断数据行的头信息中的instant 标志位,可以知道该行的格式是 "新格式":该行头信息后有一个新字段 "列数"通过读取数据行的 "列数" 字段,可以知道该行数据中多少列有"真实"的数据,从而按列数读取数据。
  快速加列特性,在增加列时,实际上只是修改了元数据,原来存储在文件中的行记录并没有被修改。当行格式为redundent类型时,记录解析是不依赖元数据的,可以自解析,但如果行格式是dynamic或者compact类型,由于行内不存储元数据,尤其是列的个数信息,其记录的解析需要依赖元数据的辅助。因此为了支持动态加列功能,会对行格式做一定的修改。

大体思路如下:
  如果表上从未发生过instant add column, 则行格式维持不变;如果发生过instant ddl, 那么所有新的记录上都被特殊标记了一个flag, 同时在行内存储了列的个数;由于只支持往后顺序加列,通过列的个数就可以知道这个行记录中包含了哪些列的信息。

PS:重建表时,information_schema.innodb_table中TABLE_ID会改变,INSTANT_COLS也会归0。

四、总结

  MySQL从5.6版本开始支持Online DDL,在5.7得到更大的支持,在8.0实现了部分操作的高效性!
本文参考文章:https://blog.csdn.net/finalkof1983/article/details/88355314

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章