MySQL 8.0 的魔法：即刻列操作——再也不用等待了

概述

在MySQL 8.0.12版本之后我们不用为大表添加列问题担心了，然后MySQL 8.0.28版本之后我们不用为大表删除列而担心了。

背景

在MySQL数据库中，行数据会被持久化到硬盘，当然每行的列支也被存储到硬盘上。当我们向已有的表中添加/删除列时，MySQL会修改物理硬盘中的行。但是随着我们业务表行数的增加，添加和删除行需要的时间也在增加。可能我们会提前设计一些方案来应对添加或者删除列，我们会有很对方案来解决这个问题：

1. copy-on-add/drop
2. 在线DDL工具
3. 直接修改
4. 新增表（避免老表DDL操作）
5. 应用层处理（开发需求，DBA不管）

copy-on-add/drop

这个是我自创的名字，哈哈。这种方式是我们在添加或删除列的时候，我们先根据开发的需求来创建一张新表，然后把现有表的数据复制到新表。在复制时我们可能会用:

insert into new_table select * from old_table

rename old_table

rename new_table

COPY

添加/删除列慢的根本原因

因为MySQL要根据新的表结构定义来修改所有记录。

MySQL 8.0.12版本

之所以会提这个版本是因为MySQL 8.0.12版本中,MySQL开发团队提出了一个核心思想: don't touch any row but update the metadata only。这个核心思想就是“不触碰任何行，仅更新元数据”。MySQL 8.0.12版本引入了ALGORITHM=INSTANT,使用这个语法可能在表中添加新的列时立即完成而不是等到昏天暗地，但是不支持删除列。

MySQL 8.0.29

这个版本的MySQL实现了INSTANT DROP，在我们删除列时也是使用ALGORITHM=INSTANT,删除操作可以即时完成。

底层原理是？

原理我们上面有提到过，就是只修改表的元数据定义，有哪些列被删除了，哪些列新增了，然后查询的时候根据表的定义来做一次转换，把老行转换为新行。

行版本概念。

限制

MySQL开发团队综合考虑了下这个即时添加和删除的维护成本后，它们决定把INSTANT添加/删除的次数设置为64。 这个意思就是对一张表只能进行64次INSTANT添加/删除，超过了的话就会回退到老版本的方式：重建表。

其实，在我看来64次还是挺多的，一般业务应该足够了。

其他的限制：

• 不支持有 FTS 索引的表。
• 不支持行格式为压缩的表。
• 不支持临时表。

表重建和截断： 其他导致表重建的 ALTER TABLE 操作（例如 OPTIMIZE TABLE）会清除即刻添加/删除列的元数据。TRUNCATE TABLE 也会清除，因为没有行了，所以与新建表没有区别。

查询行版本

select * from INFORMATION_SCHEMA.INNODB_TABLES where NAME='dbname/tablename'

COPY

我们来试试

先查询一下我们的行版本：

mysql> select * from INFORMATION_SCHEMA.INNODB_TABLES where NAME='trade_order/trade_user';
+----------+------------------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| TABLE_ID | NAME                   | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS |
+----------+------------------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
|     1072 | trade_order/trade_user |   33 |     10 |     6 | Dynamic    |             0 | Single     |            0 |                  0 |
+----------+------------------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
1 row in set (0.00 sec)

COPY

INSTANT添加列

我的MySQL版本为：

mysql> SELECT VERSION();
+-----------+
| VERSION() |
+-----------+
| 8.0.36    |
+-----------+
1 row in set (0.00 sec)

COPY

我们有一张表：trade_user，表有三百多万行数据，我们来给表添加一个列

mysql> use trade_order;
Database changed
mysql> select count(*) from trade_user;
+----------+
| count(*) |
+----------+
|  3536655 |
+----------+
1 row in set (0.14 sec)

mysql> desc trade_user;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | bigint unsigned  | NO   | PRI | NULL    | auto_increment |
| name       | varchar(20)      | NO   | MUL | NULL    |                |
| email      | longtext         | YES  |     | NULL    |                |
| age        | tinyint unsigned | YES  |     | NULL    |                |
| birthday   | datetime         | YES  |     | NULL    |                |
| created_at | datetime         | YES  |     | NULL    |                |
| updated_at | datetime         | YES  |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

COPY

添加身份证号

我们在300万多行的表上添加一列0.02秒完成。

mysql> alter table trade_user add column id_no char(18) not null default '';
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from INFORMATION_SCHEMA.INNODB_TABLES where NAME='trade_order/trade_user';
+----------+------------------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
| TABLE_ID | NAME                   | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | TOTAL_ROW_VERSIONS |
+----------+------------------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
|     1072 | trade_order/trade_user |   33 |     11 |     6 | Dynamic    |             0 | Single     |            0 |                  1 |
+----------+------------------------+------+--------+-------+------------+---------------+------------+--------------+--------------------+
1 row in set (0.00 sec)

COPY

添加完成后我们的行版本为1：

说明

MySQL官方建议使用 MySQL 8.0.32 或更高版本来利用这个即刻添加/删除列功能。

参考

MySQL 8 INSTANT 添加或者删除列（是否可以不用担心大表添加/删除列问题了？） – FOF编程网