变更表结构的是 DBA 经常会遇到的问题之一,在 MySQL 的环境中,一般会直接使用 Alter 语句来完成这些操作,这些 Alter 语句对应的操作通常也称之为 DDL 操作。
DDL 操作分为很多种,比较常用的包括索引的添加、删除,列的添加、删除等。通常情况下,这些 DDL 操作都会对业务有很明显的影响,需要在业务空闲,或者是维护的时候做,否则非常容易出现 metadata lock 等待的现象,导致业务查询一直没有返回结果或 alter 操作一直处于等待状态。如果业务使用了 Master-Slave 架构,耗时较长的 DDL 操作还会导致 Slave 出现非常明显的延迟。
对业务影响比较大的 DDL 操作中,列的添加和删除是比较常见的操作之一,一般情况下,这种 DDL 操作会完全阻塞某张表的写入,而且还需要花费比较久的时间才能完成。
详细内容请参考专栏的文章: MySQL 5.7新特性:Online DDL
在 MySQL 5.5 与更老的版本中,对 Alter 操作做了较简单的实现,添加和删除列的操作使用的是 copy 算法,依靠临时表,把 old_table 的数据重新插入到 new_table,不仅耗时久,占用额外一倍的磁盘空间,还会阻塞表的写入。
在 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.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 的信息来组合出完整的行数据。
各类语句的实现方式也发生了一些变更:
当对包含 instant 列的表进行 rebuild 时,所有的数据在 rebuild 的过程中重新以旧的数据格式(包含所有列的内容)写入到表中,所以 rebuild 表之后,information_schema 中有关这个表的 instant 的信息会被重置。
MySQL 8.0.12 中,如下 Alter 操作已经默认使用了 instant 算法:
使用如下 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 列
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>
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。