新特性解读 | MySQL8.0 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;

而在 8.0.29 之前的版本，仅支持在表最后一列即时添加列，不支持在表任一位置即时添加列，如下 8.0.27 中操作：

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.27中操作报错：

3、添加列时评估行大小限制

例如我们添加一个超出行大小限制的列：

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)  ，可以添加成功。

上面的操作中我们对表 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

如下：

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)

ALTER TABLE … ALGORITHM=INSTANT 的新特性，可以在表的任一位置添加一列或多列，也可以快速的删除某列，极大的提高了在线 DDL 的效率。

相关推荐：

故障分析 | MySQL Router：服务器后端那么闲，为什么不让访问？

技术分享 | 误删表以及表中数据，该如何恢复？

技术分享 | kubernetes operator 简介

技术分享 | kubernetes 环境测试部署 MySQL 的随想

技术分享 | MongoDB 一次排序超过内存限制的排查

爱可生开源社区的 SQLE 是一款面向数据库使用者和管理者，支持多场景审核，支持标准化上线流程，原生支持 MySQL 审核且数据库类型可扩展的 SQL 审核工具。