MySQL8.0之快速加列

2022-05-05 03:42:51

在正式介绍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的支持：

图片来源：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

码农公寓