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

概述

今天主要介绍一下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 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 等算法，官方文档给出了一个表格，整理如下，星号表示不是全部支持，有依赖项。

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 &

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;

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;

2.4、修改列操作

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

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;

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;

2.7、修改表名

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

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

来源：

https://www.toutiao.com/i6933566079608439308/