专栏首页腾讯云数据库专家服务MySQL 8.0 新特性:快速加列
原创

MySQL 8.0 新特性:快速加列

前言

变更表结构的是 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:

SELECT * FROM information_schema.innodb_tables

例如:

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 列,那么会有类似如下的结果:

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 与 之后)的执行效率

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 版本的实例,简单测试一下:

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>

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL 8.0 之 Online DDL快速加列

    前几天同事问了我一个问题:业务A从MySQL迁移到MongoDB的原因是什么?

    AsiaYe
  • Mysql 8.0 新增特性

    1. 数据字典 新增了事务型的数据字典,用来存储数据库对象信息 之前,字典数据是存储在元数据文件和非事务型表中的 2. 账号权限管理 添加了对 “角色” 的支持...

    dys
  • MySQL 8.0复制新特性

    截止2017年8月,MySQL 8.0 仍然是 beta 版本,复制功能有一些很棒的改进。最初,这些改进是为组复制(GR)开发的,但由于 GR 在底层使用常规复...

    wubx
  • MySQL 8.0复制新特性

    截止目前(2017年8月),MySQL 8.0 仍然是 beta 版本,复制功能有一些很棒的改进。最初,这些改进是为组复制(GR)开发的,但由于 GR 在底层使...

    wubx
  • MySQL 8.0新特性:InnoDB ReplicaSet

    InnoDB ReplicaSet在 MySQL 8.0.19 版本之后开始支持;本文将针对 InnoDB ReplicaSet这一新特性做一些测试,包括环境部...

    SEian.G
  • 新特性解读 | mysql 8.0 memcached api 新特性

    资深数据库专家,专研 MySQL 十余年。擅长 MySQL、PostgreSQL、MongoDB 等开源数据库相关的备份恢复、SQL 调优、监控运维、高可用架构...

    爱可生开源社区
  • MySQL 8.0新特性 — 备份锁

    在MySQL 8.0中,引入了一个轻量级的备份锁,这个锁可以保证备份一致性,而且阻塞的操作相对比较少,是一个非常重要的新特性,接下来我们就来了解一下。

    brightdeng@DBA
  • MySQL 8.0 新特性:WriteSet 复制

    MySQL 的主从同步应该是被各个 DBA 熟知的技术了,从 MySQL 3.23.15 开始一直迭代改进到 8.0 版本。经过这么多年的改进,目前 8.0 提...

    王文安@DBA
  • MySQL 8.0 新特性之Hash Join

    上周在公司做了针对MySQL 8.0新特性相关的分享,提到MySQL 8.0新特性,不得不提到的就是HashJoin,MySQL一直被人诟病没有实现HashJo...

    SEian.G
  • MySQL 8.0新特性 — 检查性约束

    在MySQL 8.0版本中,引入了一个非常有用的新特性 — 检查性约束,它可以提高对非法或不合理数据写入的控制能力;接下来我们就来详细了解一下。

    brightdeng@DBA
  • 新特性解读 | MySQL 8.0 新特性-副本集(replicaset)

    MySQL Innodb Cluster = MySQL Shell + MySQL Router + MySQL Group Replication(MGR)

    爱可生开源社区
  • MySQL 8.0新特性:降序索引

    上两篇文章分别介绍了MySQL8.0的相关的新特性《MySQL 8.0新特性:隐藏索引》和《MySQL 8.0新特性:隐藏字段》,本文继续介绍MySQL8.0的...

    SEian.G
  • MySQL 8.0新特性:隐藏字段

    上一篇介绍了MySQL8.0新特性之隐藏索引《MySQL 8.0新特性:隐藏索引》,这篇文章主要给大家介绍了关于MySQL 8.0新特性之隐藏字段;

    SEian.G
  • MySQL 8.0新特性:隐藏索引

    MySQL 8.0 支持隐藏索引(invisible index),也称为不可见索引。隐藏索引不会被优化器使用。它允许快速启用/禁用MySQL Optimize...

    SEian.G
  • MySQL 8.0新特性: 数据字典

    目前MySQL 8.0最新版本为8.0.23版本,针对8.0的新特性,从春节前开始做了一些相关学习和测试,后续会不阶段的分享一些8.0的新特性,供大家一起参考和...

    SEian.G
  • MySQL 8.0 新特性:NOWAIT and SKIP LOCKED

    在过去,出现秒杀,抢购等业务场景时,很多产品、程序、架构师都会优先考虑 redis,memcache 这类 NoSQL 数据库,或者是 zookeeper 这类...

    王文安@DBA
  • MySQL 8.0新特性 — CTE(Common Table Expressions)

    CTE,Common Table Expressions,是一个非常实用的功能,可以有效降低SQL的编写难度。CTE,也是大多数关系型数据库都支持的,包括DB2...

    brightdeng@DBA
  • MySQL 8.0新特性 — 降序索引

    相信大家都知道,索引是有序的;不过,在MySQL之前版本中,只支持升序索引,不支持降序索引,这会带来一些问题;在最新的MySQL 8.0版本中,终于引入了降序索...

    brightdeng@DBA
  • MySQL 8.0新特性 — 函数索引

    在MySQL之前版本中,一直不支持函数索引,这也是被不少人诟病的一点;虽然可以通过generated column实现类似功能,但始终是不太方便;不过,在最新的...

    brightdeng@DBA

扫码关注云+社区

领取腾讯云代金券