概述

今天主要介绍一下MySQL 8.0.19 instant add column的新特性，基于亿级数据秒速增加字段，下面一起来看看吧~

一、MySQL DDL 的方法

MySQL 在大型表上的 DDL 会带来耗时较久、负载较高、额外空间占用、MDL、主从同步延时等情况。需要特别引起重视，而MySQL 的 DDL 有很多种方法。

MySQL 本身自带三种方法，分别是：copy、inplace、instant。

copy 算法为最古老的算法，在 MySQL 5.5 及以下为默认算法。
从 MySQL 5.6 开始，引入了 inplace 算法并且默认使用。inplace 算法还包含两种类型：rebuild-table 和 not-rebuild-table。MySQL 使用 inplace 算法时，会自动判断，能使用 not-rebuild-table 的情况下会尽量使用，不能的时候才会使用 rebuild-table。当 DDL 涉及到主键和全文索引相关的操作时，无法使用 not-rebuild-table，必须使用 rebuild-table。其他情况下都会使用 not-rebuild-table。
从 MySQL 8.0.12 开始，引入了 instant 算法并且默认使用。目前 instant 算法只支持增加列等少量 DDL 类型的操作，其他类型仍然会默认使用 inplace。

有一些第三方工具也可以实现 DDL 操作，最常见的是 percona 的 pt-online-schema-change 工具（简称为 pt-osc），和 github 的 gh-ost 工具，均支持 MySQL 5.5 以上的版本。

》》各类工具的对比

MySQL 8.0.19亿级数据如何秒速增加字段？

一般情况下的建议：

如果使用的是 MySQL 5.5 或者 MySQL 5.6，推荐使用 gh-ost
如果使用的是 MySQL 5.7，索引等不涉及修改数据的操作，建议使用默认的 inplace 算法。如果涉及到修改数据（例如增加列），不关心主从同步延时的情况下使用默认的 inplace 算法，关心主从同步延时的情况下使用 gh-ost
如果使用的是 MySQL 8.0，推荐使用 MySQL 默认的算法设置，在语句不支持 instant 算法并且在意主从同步延时的情况下使用 gh-ost

二、MySQL DDL 的原理简析

1、copy 算法

较简单的实现方法，MySQL 会建立一个新的临时表，把源表的所有数据写入到临时表，在此期间无法对源表进行数据写入。MySQL 在完成临时表的写入之后，用临时表替换掉源表。这个算法主要被早期（<=5.5）版本所使用。

2、inplace 算法

从 5.6 开始，常用的 DDL 都默认使用这个算法。inplace 算法包含两类：inplace-no-rebuild 和 inplace-rebuild，两者的主要差异在于是否需要重建源表。

inplace 算法的操作阶段主要分为三个：

Prepare阶段： - 创建新的临时 frm 文件(与 InnoDB 无关)。 - 持有 EXCLUSIVE-MDL 锁，禁止读写。 - 根据 alter 类型，确定执行方式（copy，online-rebuild，online-not-rebuild）。更新数据字典的内存对象。 - 分配 row_log 对象记录数据变更的增量（仅 rebuild 类型需要）。 - 生成新的临时ibd文件 new_table（仅rebuild类型需要）。
Execute 阶段：降级EXCLUSIVE-MDL锁，允许读写。扫描old_table聚集索引（主键）中的每一条记录 rec。遍历new_table的聚集索引和二级索引，逐一处理。根据 rec 构造对应的索引项。将构造索引项插入 sort_buffer 块排序。将 sort_buffer 块更新到 new_table 的索引上。记录 online-ddl 执行过程中产生的增量（仅 rebuild 类型需要）。重放 row_log 中的操作到 new_table 的索引上（not-rebuild 数据是在原表上更新）。重放 row_log 中的DML操作到 new_table 的数据行上。
Commit阶段：当前 Block 为 row_log 最后一个时，禁止读写，升级到 EXCLUSIVE-MDL 锁。重做 row_log 中最后一部分增量。更新 innodb 的数据字典表。提交事务（刷事务的 redo 日志）。修改统计信息。rename 临时 ibd 文件，frm文件。变更完成，释放 EXCLUSIVE-MDL 锁。

3、instant 算法

MySQL 8.0.12 才提出的新算法，目前只支持添加列等少量操作，利用 8.0 新的表结构设计，可以直接修改表的 metadata 数据，省掉了 rebuild 的过程，极大的缩短了 DDL 语句的执行时间。

4、pt-online-schema-change

借鉴了 copy 算法的思路，由外部工具来完成临时表的建立，数据同步，用临时表替换源表这三个步骤。其中数据同步是利用 MySQL 的触发器来实现的，会少量影响到线上业务的 QPS 及 SQL 响应时间。

三、mysql 8.0特性instant add column

1、instant add column原理

mysql数据库针对亿级别的大表加字段是痛苦的，需要对表进行重建，MySQL 5.7 支持 Online DDL，大部分 DDL 不影响对表的读取和写入，但是依然会消耗非常多的时间，且占用额外的磁盘空间，并会造成主从延迟。所以大表 DDL 仍是一件令 DBA 头痛的事。而mysql8.0使用instant ADD COLUMN特性，只需很短的时间，字段就加好了，享受MongoDB那样的非结构化存储的灵活方便,无形中减少了开发的工作量。

快速加列采用的是 instant 算法，使得添加列时不再需要 rebuild 整个表，只需要在表的 metadata 中记录新增列的基本信息即可。在 alter 语句后增加 ALGORITHM=INSTANT 即代表使用 instant 算法，如果未明确指定，则支持 instant 算法的操作会默认使用。如果 ALGORITHM=INSTANT 指定但不支持，则操作立即失败并显示错误。

关于列的 DDL 操作，是否支持 instant 等算法，官方文档给出了一个表格，整理如下，星号表示不是全部支持，有依赖项。

MySQL 8.0.19亿级数据如何秒速增加字段？

instant 算法使用最广泛的应该是添加列了，可以看到使用该算法还是有些限制的，一些限制如下：

如果 alter 语句包含了 add column 和其他的操作，其中有操作不支持 instant 算法的，那么 alter 语句会报错，所有的操作都不会执行。
如果指定了AFTER，字段必须是在最后一列，否则需要重建表；
只能顺序加列, 仅支持在最后添加列，而不支持在现有列的中间添加列。
不支持压缩表，即该表行格式不能是 COMPRESSED。
不支持包含全文索引的表。
不支持临时表。
不支持那些在数据字典表空间中创建的表。
DROP COLUMN需要重建表；
modify修改字段属性需要重建表。

2、实验测试

2.1、环境准备

2.1.1、安装sysbench

wget https://codeload.github.com/akopytov/sysbench/tar.gz/1.0.18 yum -y install gcc gcc-c++  make automake libtool pkgconfig libaio-devel  tar -xvf sysbench-1.0.18.tar.gz cd sysbench-1.0.18/ ./autogen.sh ./configure --prefix=/usr/local/sysbench/ --with-mysql  --with-mysql-includes=/usr/include/mysql \ -with-mysql-libs=/usr/lib64/mysql && make && make install echo "export PATH=$PATH:/usr/local/sysbench/bin" >> /etc/profile source /etc/profile

2.1.2、数据准备

--准备2张表，每张表1亿数据 >create database sbtest; nohup sysbench --mysql-host=localhost --mysql-port=53306 --mysql-user=root --mysql-password=axxxpx \ --test=/usr/local/sysbench/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp_tables_count=2 \ --oltp-table-size=100000000 --num-threads=50 --rand-init=on prepare &

MySQL 8.0.19亿级数据如何秒速增加字段？

2.2、添加字段

mysql 5.7 用inplace算法去对一亿的表添加字段，耗时接近10分钟，MySQL8.0 用instant算法对一亿的表添加字段，耗时0.12s

--指定InPlace算法添加列，(5.7版本添加列使用该算法) alter table sbtest1 add column cityname1 varchar(10) , algorithm=inplace; --指定 instant 算法添加列（8.0版本添加列使用该算法） alter table sbtest2 add column cityname2 varchar(10) , algorithm=instant;

MySQL 8.0.19亿级数据如何秒速增加字段？

2.3、设置默认值和删除默认值

alter table sbtest1 alter column cityname1 set default 'wuhan' ,algorithm=inplace,lock=default; alter table sbtest2 alter column cityname2 set default 'beijing',algorithm=instant,lock=default;  alter table sbtest1 alter column cityname1  drop default ,algorithm=inplace; alter table sbtest2 alter column cityname2  drop default,algorithm=instant;

MySQL 8.0.19亿级数据如何秒速增加字段？

2.4、修改列操作

--inplace算法和instant算法均不支持 alter table sbtest1 modify cityname1 datetime;

MySQL 8.0.19亿级数据如何秒速增加字段？

2.5、虚拟列的增加和删除

alter table sbtest1 add column (d int generated always as (k+1) virtual),algorithm=inplace; alter table sbtest2 add column (d int generated always as (k+1) virtual),algorithm=instant; alter table sbtest1 drop column d,algorithm=inplace; alter table sbtest2 drop column d,algorithm=instant;

MySQL 8.0.19亿级数据如何秒速增加字段？

2.6、增加带有外键的列

--设置ON UPDATE CURRENT_TIMESTAMP，表示在数据库数据有更新的时候createtime的时间会自动更新 alter table sbtest1 add column createtime datetime not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,algorithm=inplace; alter table sbtest2 add column createtime datetime not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,algorithm=instant;

MySQL 8.0.19亿级数据如何秒速增加字段？

2.7、修改表名

alter table sbtest1 rename to sbtest11, algorithm=inplace; alter table sbtest2 rename to sbtest22, algorithm=instant;

MySQL 8.0.19亿级数据如何秒速增加字段？

基于上面的测试可以发现，在快速加列功能上使用 instant 算法添加列基本都在 1s 内完成，而使用 5.7 版本的 inplace 算法时，则添加列的时间上升至数分钟。对比看来 8.0 版本的这个特性确实很实用！

MySQL 8.0.19亿级数据如何秒速增加字段？

概述

一、MySQL DDL 的方法

》》各类工具的对比

二、MySQL DDL 的原理简析

1、copy 算法

2、inplace 算法

3、instant 算法

4、pt-online-schema-change

三、mysql 8.0特性instant add column

相关推荐