MySQL 8.0 新特性：快速加列

2020-10-20 12:53:34 浏览数 (1)

前言

变更表结构的是 DBA 经常会遇到的问题之一，在 MySQL 的环境中，一般会直接使用 Alter 语句来完成这些操作，这些 Alter 语句对应的操作通常也称之为 DDL 操作。

DDL中的痛点

DDL 操作分为很多种，比较常用的包括索引的添加、删除，列的添加、删除等。通常情况下，这些 DDL 操作都会对业务有很明显的影响，需要在业务空闲，或者是维护的时候做，否则非常容易出现 metadata lock 等待的现象，导致业务查询一直没有返回结果或 alter 操作一直处于等待状态。如果业务使用了 Master-Slave 架构，耗时较长的 DDL 操作还会导致 Slave 出现非常明显的延迟。

对业务影响比较大的 DDL 操作中，列的添加和删除是比较常见的操作之一，一般情况下，这种 DDL 操作会完全阻塞某张表的写入，而且还需要花费比较久的时间才能完成。

可选的解决方案

详细内容请参考专栏的文章： MySQL 5.7新特性：Online DDL

MySQL 5.5 与 以前

在 MySQL 5.5 与更老的版本中，对 Alter 操作做了较简单的实现，添加和删除列的操作使用的是 copy 算法，依靠临时表，把 old_table 的数据重新插入到 new_table，不仅耗时久，占用额外一倍的磁盘空间，还会阻塞表的写入。

MySQL 5.6 与5.7

在 MySQL 5.6 与 5.7，官方提出 Online DDL 的功能，添加和删除列的操作从 copy 算法变成了 inplcae rebuild 算法，不再阻塞对表的写入。但是依然会消耗非常多的时间，且占用额外的磁盘空间。

第三方的方案

针对源生 DDL 操作的问题，有其他的团队开发了一些外部工具来完成，比较常用的包括 percona 的 pt-online-schema-change，facebook 开源的 online-schema-change 工具，以及 github 维护的 gh-ost 工具。这些工具自行完成了 old_table 和 new_table 的数据同步，再使用 rename 的方式进行表的替换，尽管耗时比较久，但是既不会阻塞写入，也不会引起 Slave 的延迟。

注意：第三方工具在最后切换 old_table 和 new_table 的时候，依旧会用到 alter 语句，因此也需要留意 metadata lock 引起的问题。

MySQL 8.0 的快速添加列

在 >=MySQL 8.0.12 的版本中，官方为 Online DDL 操作添加了 instant 算法，使得添加列时不再需要 rebuild 整个表，只需要在表的 metadata 中记录新增列的基本信息即可。

原理简析

新的算法依赖于 MySQL 8.0 对表 metadata 结构做出的一些变更。8.0 除了在表的 metadata 信息中新增了 instant 列的默认值以及非 instant 列的数量以外，还在数据的物理记录中加入了 info_bit，包括一个 flag 来标记这条记录是否为添加 instant 列之后才更新、插入的，以及 column_num，用来记录行数据总共有多少列。

当使用 instant 算法来添加列的时候，无需 rebuild 表，直接把列的信息记录到 metadata 中即可，对这些行进行操作时，可以读取 metadata 的信息来组合出完整的行数据。

各类语句的实现方式也发生了一些变更：

  • select：读取一行数据的物理记录时，会根据 flag 来判断是否需要去 metadata 中获取 instant 列的信息；如果需要，则根据 column_num 来读取实际的物理数据，再从 metadata 中补全缺少的 instant 列数据。
  • insert：额外记录语句执行时的 flag 和 column_num。
  • delete：与以前的版本保持一致。
  • update：如果表的 instant column 数量发生了变化，对旧数据的 update 会在内部转换成 delete 和 insert 操作。

当对包含 instant 列的表进行 rebuild 时，所有的数据在 rebuild 的过程中重新以旧的数据格式（包含所有列的内容）写入到表中，所以 rebuild 表之后，information_schema 中有关这个表的 instant 的信息会被重置。

使用方式

MySQL 8.0.12 中，如下 Alter 操作已经默认使用了 instant 算法：

  • 添加列
    • 不支持删除普通列
  • 添加或者删除一个虚拟列
  • 添加或者删除一个列的默认值
  • 修改 ENUM 或者 SET 列的定义
  • 变更索引的类型（B 树，哈希）
  • 使用 alter 语法重命名表

使用如下 sql 命令可以查看每个表通过 instant 算法加列前的非 instant 列的数量，如果该表没有添加过 instant 列，则 instant_cols 默认显示 0：

代码语言：txt复制
SELECT * FROM information_schema.innodb_tables

例如：

代码语言：txt复制
mysql>     

 ---------- --------- ------ -------- ------- ------------ --------------- ------------ -------------- 

| TABLE_ID | NAME    | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |

 ---------- --------- ------ -------- ------- ------------ --------------- ------------ -------------- 

|     1192 | test/t1 |   33 |      7 |    41 | Dynamic    |             0 | Single     |            0 |

 ---------- --------- ------ -------- ------- ------------ --------------- ------------ -------------- 

1 row in set (0.00 sec)

如果添加过 instant 列，那么会有类似如下的结果：

代码语言：txt复制
mysql> alter table t1 add column c4 int unsigned not null default 1, algorithm=instant;

Query OK, 0 rows affected (0.03 sec)

Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from information_schema.innodb_tables where table_id = 1192;

 ---------- --------- ------ -------- ------- ------------ --------------- ------------ -------------- 

| TABLE_ID | NAME    | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS |

 ---------- --------- ------ -------- ------- ------------ --------------- ------------ -------------- 

|     1192 | test/t1 |   33 |      8 |    41 | Dynamic    |             0 | Single     |            4 |

 ---------- --------- ------ -------- ------- ------------ --------------- ------------ -------------- 

1 row in set (0.00 sec)

添加 instant 列之后，instant_col 变为了 4，代表这个表添加过 instant 列，且 instant 列是第五列

PS：由于 instant 列无法使用 after 关键字，所以只能添加在表的尾端，因此 instant_col 等于 4 代表了前四列为普通列，第五列开始为 instant 列

使用限制

  • 如果 alter 语句包含了 add column 和其他的操作，其中有操作不支持 instant 算法的，那么 alter 语句会报错，所有的操作都不会执行。
  • 添加列时，不能使用 after 关键字控制列的位置，只能添加在表的末尾（最后一列）。
  • 开启压缩的 innodb 表无法使用 instant 算法。
  • 不支持包含全文索引的表。
  • 仅支持使用 MySQL 8.0 新表空间格式的表。
  • 不支持临时表。
  • 包含 instant 列的表无法在旧版本的 MySQL 上使用（即物理备份无法恢复）。
  • 在旧版本上，如果表或者表的索引已经 corrupt，除非已经执行 fix 或者 rebuild，否则升级到新版本后无法添加 instant 列。

各版本的差异

MDL 代表是否需要 metadata lock，下表仅对比添加列的操作。

copy

inplace

pt-osc

gh-ost

instant

数据读取

允许

允许

允许

允许

允许

数据写入

不允许

允许

允许

允许

允许

MDL

需要

需要

需要

需要

需要

执行时间

非常长

长

长

长

短

同步延迟

非常大

大

小

小

小

测试一下

架构：单实例

机器配置：4C/8G/SSD云盘，腾讯云 CVM

数据量：1 张表，共 1000w 行数据，事先执行多次 alter 操作与 select 操作预热数据

工具：无

对象：MySQL 8.0.22，对比 copy（5.5 与 以前），inplace-rebuild（5.6 与 5.7），instant （8.0.12 与 之后）的执行效率

代码语言：txt复制
mysql> select count(*) from sbtest1;
 ---------- 
| count(*) |
 ---------- 
| 10000000 |
 ---------- 
1 row in set (0.26 sec)

mysql> alter table sbtest1 add column test varchar(128),algorithm=copy;
Query OK, 10000000 rows affected (7 min 1.23 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

mysql> alter table sbtest1 drop column test,algorithm=copy;
Query OK, 10000000 rows affected (6 min 27.21 sec)
Records: 10000000  Duplicates: 0  Warnings: 0

mysql> alter table sbtest1 add column test varchar(128),algorithm=inplace;
Query OK, 0 rows affected (7 min 1.51 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table sbtest1 drop column test,algorithm=inplace;
Query OK, 0 rows affected (6 min 36.31 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql> alter table sbtest1 add column test varchar(128),algorithm=instant;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table sbtest1 drop column test varchar(128),algorithm=instant;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'varchar(128),algorithm=instant' at line 1
mysql>

从执行时间上看，instant 算法几乎是马上完成，而 copy 算法和 inplace rebuild 在执行时间上基本没什么差别。

总结一下

实际上快速加列的 patch 是腾讯提交给官方，然后由官方自己重新实现的特性，腾讯云数据库 MySQL 自然也集成了这个功能，使用 5.7 版本的实例，简单测试一下：

代码语言：txt复制
mysql> CREATE TABLE `sbtest1` (
    ->   `id` int NOT NULL AUTO_INCREMENT,
    ->   `k` int NOT NULL DEFAULT '0',
    ->   `c` char(120) NOT NULL DEFAULT '',
    ->   `pad` char(60) NOT NULL DEFAULT '',
    ->   PRIMARY KEY (`id`),
    ->   KEY `k_1` (`k`)
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.03 sec)

mysql>  alter table sbtest1 add column test varchar(128),algorithm=instant;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
云数据库SQLServer 云数据库MySQL 数据库

0 人点赞