关于算法:新特性解读-MySQL80-ALTER-TABLE-…-ALGORITHMINSTANT

作者：张娜

爱可生南区 DBA 组成员，负责 MySQL、TiDB 日常保护、故障解决。

本文起源：原创投稿

* 爱可生开源社区出品，原创内容未经受权不得随便应用，转载请分割小编并注明起源。

一、前言

MySQL 8.0.29 之前，在线 DDL 操作中即时增加列只能增加在表的最初一列，对于在某个具体列前面疾速增加列很不不便，MySQL 8.0.29 扩大了对 ALTER TABLE … ALGORITHM=INSTANT 的反对：用户能够在表的任何地位即时增加列、即时删除列、增加列时评估行大小限度。

每次即时增加或删除列都会创立一个新的行版本。MySQL 8.0.29 在 INFORMATION_SCHEMA.INNODB_TABLES 表中增加了一个新的列 TOTAL_ROW_VERSIONS 列来跟踪行版本的数量，每个表最多容许 64 行版本。

另外 XtraBackup 8.0.29 在备份 MySQL 8.0.29 时会有个小插曲，这个小插曲就跟 ALTER TABLE … ALGORITHM=INSTANT 无关。接下来咱们来一一体验一下。

二、新个性体验：

首先创立一张 2 千万的表 sbtest1：

sysbench ./oltp_read_write.lua --mysql-host=10.186.61.168  
--mysql-user=root 
--mysql-password='XXXXXX' 
--mysql-port=3388 
--mysql-socket=/data/mysql8.0.29/data/mysqld.sock 
--mysql-db=test_a --tables=1 
--table-size=20000000 
--report-interval=2 
--threads=10 prepare

1、任一地位即时增加列

原始表构造如下：

在表 sbtest1 任一地位即时增加列：k 列前面增加 k2 列：

mysql> ALTER TABLE sbtest1 ADD COLUMN k2 int(10) AFTER k,ALGORITHM=INSTANT;

能够看到，2 千万的表在任一地位即时增加列在秒级内实现。

而在 8.0.29 之前的版本，仅反对在表最初一列即时增加列，不反对在表任一地位即时增加列，如下 8.0.27 中操作：

能够看到，同样 2 千万行的表通过默认的 ALGORITHM 在任一地位添列耗时 7 分 22 秒，而 ALGORITHM=INSTANT 仅反对默认在最初一列即时增加列。

2、即时删除列

MySQL 8.0.29 开始，ALTER TABLE … ALGORITHM=INSTANT 反对删除某列。如下先增加两列，再删除两列：

mysql> ALTER TABLE sbtest1 ADD COLUMN c4 int(10) ,ADD COLUMN c5 int(10),ALGORITHM=INSTANT;
Query OK, 0 rows affected, 2 warnings (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 2

mysql> alter table sbtest1 DROP COLUMN c4,DROP COLUMN c5,ALGORITHM=INSTANT;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

而在 8.0.29 之前的版本，ALTER TABLE … ALGORITHM=INSTANT 不反对反对删除某列，否则会有报错提醒 ERROR 1845 (0A000): ALGORITHM=INSTANT is not supported for this operation. Try ALGORITHM=COPY/INPLACE.

8.0.27 中操作报错：

3、增加列时评估行大小限度

在 MySQL 8.0.29 之前，增加列时不会评估行大小限度。然而，在插入和更新表中的行的 DML 操作期间会查看行大小限度。从 8.0.29 开始，增加列时会查看行大小限度。如果超出限度，则会报错。

例如咱们增加一个超出行大小限度的列：

mysql> ALTER TABLE sbtest1 ADD COLUMN pad6 varchar(4990),ALGORITHM=INSTANT;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs

在 8.0.29 之前中操作，增加列时不会评估行大小限度，如下 8.0.27 中同样 varchar(4990)，能够增加胜利。

这种状况下会给日后更新数据时埋坑。

ALTER TABLE … ALGORITHM=INSTANT 在每次增加一或多列、删除一或多列或在同一操作中增加和删除一或多列的操作之后，都会创立一个新的行版本。

MySQL 8.0.29 在 INFORMATION_SCHEMA.INNODB_TABLES 表中新增加了 TOTAL_ROW_VERSIONS 列来跟踪表的行版本数。每次立刻增加或删除列时，该值都会减少。初始值为 0。

下面的操作中咱们对表 sbtest1 进行了屡次 ALTER TABLE … ALGORITHM=INSTANT。INFORMATION_SCHEMA.INNODB_TABLES 曾经记录了 sbtest1 的行版本数。

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE TOTAL_ROW_VERSIONS > 0;

4、XtraBackup 8.0.29 备份社区版 MySQL 8.0.29

XtraBackup 8.0.29 备份社区版 MySQL 8.0.29 中具备 INSTANT ADD/DROP COLUMNS 的表，会有如下的报错提醒。

[root@node168 ~]# xtrabackup --version
2022-08-02T17:34:33.011020+08:00 0 [Note] [MY-011825] [Xtrabackup] recognized server arguments: --datadir=/var/lib/mysql
xtrabackup version 8.0.29-22 based on MySQL server 8.0.29 Linux (x86_64) (revision id: c31e7ddcce3)
[root@node168 ~]# xtrabackup 
--defaults-file=/data/mysql8.0.29/etc/my.cnf 
--target-dir=/data/backup8029 
--uer=root 
--password='xxxxxxxxx' 
--socket=/data/mysql8.0.29/data/mysqld.sock 
--backup

这是因为为了反对 ALTER TABLE … ALGORITHM=INSTANT 的新个性，InnoDB redo log 格局对于所有 DML 操作都产生了变动。新的 redo 日志格局引入了一个设计缺点，会导致 instant add/drop columns 的表数据损坏。据说这个缺点已在 Percona 版 MySQL 8.0.29 中已修复，但在以后的社区版本 MySQL 8.0.29 依然有缺点。

因为 XtraBackup 无奈解决社区版 MySQL 8.0.29 生成的损坏的 redo log，因而，如果 XtraBackup 8.0.29 版本检测到具备 INSTANT ADD/DROP 列的表，它将不会进行备份，并且会生成错误信息列出受影响表的列表并提供将它们转换为惯例表的阐明。

因而在备份之前能够通过 INFORMATION_SCHEMA.INNODB_TABLES 表查看是否有 INSTANT ADD/DROP 列的表，如果有，能够先执行 optimize table 操作，再去备份。

如下：

mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE TOTAL_ROW_VERSIONS > 0;
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| TABLE_ID | NAME           | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS |
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
|     1190 | test_a/sbtest1 |   33 |     11 |    64 | Dynamic    |             0 | Single     |            0 |                 27 |
+----------+----------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
1 row in set (0.09 sec)
mysql> OPTIMIZE TABLE test_a.sbtest1;
+----------------+----------+----------+-------------------------------------------------------------------+
| Table          | Op       | Msg_type | Msg_text                                                          |
+----------------+----------+----------+-------------------------------------------------------------------+
| test_a.sbtest1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test_a.sbtest1 | optimize | status   | OK                                                                |
+----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (7 min 12.49 sec)

之后就能够用 XtraBackup 备份社区版 MySQL 8.0.29 了。

三、小结

ALTER TABLE … ALGORITHM=INSTANT 的新个性，能够在表的任一地位增加一列或多列，也能够疾速的删除某列，极大的进步了在线 DDL 的效率。