乐趣区

关于mysql:MySQL80大表秒加字段是真的吗

jiezi

前言： 

很早就据说 MySQL8.0 反对疾速加列，能够实现大表秒级加字段。笔者本人本地也有 8.0 环境，但始终未进行测试。本篇文章咱们就一起来看下 MySQL8.0 疾速加列到底要如何操作。

1. 理解背景信息

表构造的变更是业务运行过程中比拟常见的需要之一，在 MySQL 的环境中，能够应用 Alter 语句来实现这些操作，这些 Alter 语句对应的操作通常也称之为 DDL 操作。通常状况下大表的 DDL 操作都会对业务有很显著的影响，须要在业务闲暇，或者是保护的时候做。MySQL 5.7 反对 Online DDL，大部分 DDL 不影响对表的读取和写入，然而仍然会耗费十分多的工夫，且占用额定的磁盘空间，并会造成主从提早。所以大表 DDL 仍是一件令 DBA 头痛的事。

听闻 MySQL 8.0 解决了这件令 DBA 头痛的事，那让咱们来具体理解下吧。想理解新性能，最简略的办法就是查阅官网文档。查阅官网文档得悉，疾速加列即 Instant Add Column，该性能自 MySQL 8.0.12 版本引入，是由腾讯游戏 DBA 团队奉献。留神一下，此性能只实用于 InnoDB 表。

2. 疾速加列测试

疾速加列采纳的是 instant 算法，使得增加列时不再须要 rebuild 整个表，只须要在表的 metadata 中记录新增列的根本信息即可。在 alter 语句后减少 ALGORITHM=INSTANT 即代表应用 instant 算法，如果未明确指定，则反对 instant 算法的操作会默认应用。如果 ALGORITHM=INSTANT 指定但不反对，则操作立刻失败并显示谬误。

对于列的 DDL 操作，是否反对 instant 等算法，官网文档给出了一个表格，现整顿如下，星号示意不是全副反对，有依赖项。

操作 Instant In Place Rebuilds Table 容许并发 DML 仅批改元数据
增加列 Yes* Yes No* Yes* No
删除列 No Yes Yes Yes No
重命名列 No Yes No Yes* Yes
更改列程序 No Yes Yes Yes No
设置列默认值 Yes Yes No Yes Yes
更改列数据类型 No No Yes No No
扩大 VARCHAR 列大小 No Yes No Yes Yes
删除列默认值 Yes Yes No Yes Yes
更改主动增量值 No Yes No Yes No*
设置列为 null No Yes Yes* Yes No
设置列 not null No Yes* Yes* Yes No
批改 ENUM/SET 列的定义 Yes Yes No Yes Yes

 
instant 算法应用最宽泛的应该是增加列了，能够看到应用该算法还是有些限度的，一些限度如下：

  • 如果 alter 语句蕴含了 add column 和其余的操作，其中有操作不反对 instant 算法的，那么 alter 语句会报错，所有的操作都不会执行。
  • 只能程序加列, 仅反对在最初增加列，而不反对在现有列的两头增加列。
  • 不反对压缩表，即该表行格局不能是 COMPRESSED。
  • 不反对蕴含全文索引的表。
  • 不反对长期表。
  • 不反对那些在数据字典表空间中创立的表。

说的再多不如理论来测下，上面咱们以 8.0.19 版本为例来理论验证下：

# 利用 sysbench 生成一张 1000W 的大表
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.19    |
+-----------+
1 row in set (0.00 sec)

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+

# 减少无默认值的列
mysql> alter table sbtest1 add column col1 varchar(20), algorithm=instant;
Query OK, 0 rows affected (0.63 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 减少有默认值的列
mysql> alter table sbtest1 add column create_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创立工夫', algorithm=instant;
Query OK, 0 rows affected (0.58 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 不显式指定 instant 算法
mysql> alter table sbtest1 add column col2 varchar(20);
Query OK, 0 rows affected (0.55 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 设置列的默认值
mysql> alter table sbtest1 alter column col1 set default 'sql',algorithm=instant;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 指定 In Place 算法增加列，(5.7 版本增加列应用该算法)
mysql> alter table sbtest1 add column col_inplace varchar(20),algorithm=inplace;
Query OK, 0 rows affected (1 min 23.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

通过以上测试，咱们能够发现，应用 instant 算法增加列根本都在 1s 内实现，对于大表来说这个速度是十分快的，业务根本无感知。当应用 5.7 版本的 inplace 算法时，则增加列的工夫回升至数分钟。比照看来 8.0 版本的疾速加列性能的确十分实用！

总结： 

尽管疾速加列存在一些限度，instant 算法也只实用于局部 DDL 操作，但 8.0 的这项新性能曾经足以令人兴奋，很大水平上解决了大表加字段的大难题。通过这篇文章，心愿各位能理解到这项新性能，是不是想降级到 8.0 了呢，能够着手精确起来了。

退出移动版