引言

评估指定 DDL 的执行用时是 DBA 经常会被研发咨询的一个问题，通常可以结合操作类型与表大小进行评估，尤其是使用 online ddl 时从库需要在主库执行完成后才开始执行，因此可能导致主从延迟。

MySQL 5.7 官方文档中提到 extend varchar 在长度字节不变时可以在瞬间完成，直到有一天踩坑发现这也是有条件的，即使长度字节不变，也有可能导致重建表进而造成主从延迟。具体涉及到两个 BUG，索引字段扩容导致重建表，本文中将详细介绍。

备注：本文中如果没有特殊说明，测试数据库的版本为 5.7.24。

介绍

业务反馈多个园区出现主从延迟，经确认原因是执行 varchar 扩容的 DDL，具体 SQL 如下所示。

ALTER TABLE st_stock_stream_m MODIFY COLUMN UUID varchar(85) NOT NULL COMMENT 'xxx';
ALTER TABLE st_stock_stream_d MODIFY COLUMN M_UUID varchar(85) DEFAULT NULL COMMENT 'xxx';

每个园区部署一个实例，表结构相同，数据不同，通过多实例部署间接实现类似分库分表的功能。

mysql> select job_uuid,create_time,database_name,execute_result from t_inception_job where sql_commands like '%ALTER TABLE st_stock_strream_m MODIFY COLUMN UUID varchar(85)%';
+--------------------------------------+---------------------+---------------+----------------------------------------------------------------------------------+
| job_uuid                             | create_time         | database_name | execute_result                                                                   |
+--------------------------------------+---------------------+---------------+----------------------------------------------------------------------------------+
| 37f751a9-3045-4794-9792-64f4148b4bc3 | 2022-03-09 20:05:18 | report        | 执行成功13台,执行失败0台,执行中0台,未执行0台,总计13台。                          |
| 6c7ea7a2-f252-4fc3-b384-458b747d3105 | 2022-03-09 20:06:01 | report        | 执行成功178台,执行失败0台,执行中0台,未执行0台,总计178台。                        |
| b219dc81-785c-4d67-ab9d-505c42b34e16 | 2022-03-09 20:06:29 | masterbasic   | 执行成功17台,执行失败0台,执行中0台,未执行0台,总计17台。                          |
| e21f56c9-2f42-4d98-99ac-0ff47db16287 | 2022-03-09 20:06:56 | masterbasic   | 执行成功276台,执行失败0台,执行中0台,未执行0台,总计276台。                        |
+--------------------------------------+---------------------+---------------+----------------------------------------------------------------------------------+
4 rows in set (3.36 sec)

查看其中一个工单的执行用时，按照执行用时倒排，其中如下实例很有代表性，两张表的执行用时分别为 0.01s 与 1 hour 17 min 46.22 s，不过受影响行数都等于 0。

select TIMESTAMPDIFF(minute, execute_start_time, execute_end_time) as min, execute_start_time, execute_end_time, mysql_ip, runner_execute_data from t_inception_task where job_id=48783 order by min desc limit 3 G
*************************** 2. row ***************************
                min: 77
 execute_start_time: 2022-03-09 21:31:17
   execute_end_time: 2022-03-09 22:49:08
           mysql_ip: x.x.x.x
runner_execute_data: EXECUTE START AT 2022-03-09 21:31:16
--------------
ALTER TABLE st_stock_stream_m MODIFY COLUMN UUID varchar(85) NOT NULL COMMENT 'xxx'
--------------
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
--------------
ALTER TABLE st_stock_stream_d MODIFY COLUMN M_UUID varchar(85) DEFAULT NULL COMMENT 'xxx'
--------------
Query OK, 0 rows affected (1 hour 17 min 46.22 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql: [Warning] Using a password on the command line interface can be insecure.
EXECUTE DONE AT 2022-03-09 22:49:03

查看表大小，两张表的大小基本相同。

MySQL BUG: varchar 扩容导致重建表 — image-20221112103050956

那么，为什么两张基本大小相同的表执行用时差异这么大呢？

原理

Online VS INPLACE

重点区分两个不同维度上的概念，online 与 inplace。

online，根据 DDL 执行过程中是否阻塞 DML 进行判断；
inplace，根据 DDL 执行过程中是否创建临时表进行判断。

首先介绍下 online DDL。

MySQL 5.6 之前，DDL 过程中阻塞 DML，表无法对外提供服务。

MySQL 5.6 中引入 Online DDL，DDL 过程中不再阻塞 DML。

ALTER TABLE 语法中支持以下两个参数：

ALGORITHM，算法
LOCK，锁类型

其中，ALGORITHM 参数支持以下选项：

COPY，MySQL 5.5 及之前的方式
INPLACE，MySQL 5.6 引入
INSTANT，MySQL 8.0.12 引入

简单对比以上三种算法，理论上性能依次提高。

ALGORITHM	Permits Concurrent DML	DESCRIPTION
COPY	NO	copy original table data to new table
INPLACE	YES	not copy data, but may rebuild the table in place
INSTANT	YES	only modify metadata, table data is unaffected

注意所有 DDL 操作都不阻塞读请求，因此这里所说的 DML 不包括 select。

下面，通过几个问题理解 online 与 inplace 的区别。

1）inplace 是否一定不需要额外的数据空间？答案为否。

首先，inplace 可能需要额外的数据空间，仅代表不会创建临时表（server 层），但是仍有可能创建临时文件（存储引擎层）。

其次，inplace 有 rebuild table 和 no-rebuild table 两种方式，前者依然需要拷贝数据，后者仅修改元数据（.frm 文件）。

那么，创建临时表与临时文件的区别是什么呢？简单理解的话一个是 server 层，一个是存储引擎层，具体待后续研究。

2）inplace 一定 online 吗？答案为否。

截止到 MySQL 8.0，以下两个操作 inplace 但依然阻塞 DML：

创建全文索引（FULLTEXT index）
创建空间索引 (SPATIAL index)

3）online 一定 inplace 吗？答案为是。

因此两者的关系如下所示：

online 一定 inplace
inplace 不一定 online

COPY VS INPLACE

COPY 算法对数据库的性能影响最大，如何判断使用的算法是 COPY 还是 INPLACE 呢？

可以通过 DDL 执行完成的返回值中受影响行数 rows affected 判断是否发生数据拷贝，如果值大于 0，表明 ALGORITHM=COPY，否则 ALGORITHM=INPLACE。

For DDL operations that modify table data, you can determine whether a DDL operation performs changes in place or performs a table copy by looking at the “rows affected” value displayed after the command finishes.

下面分别展示四种操作，Rebuilds Table=Yes，其中前两者 In Place=Yes，后两者 In Place=No。

新增列，In Place=Yes，Rebuilds Table=Yes。

mysql> alter table t_ddl add column b varchar(20);
Query OK, 0 rows affected (3.94 sec)
Records: 0  Duplicates: 0  Warnings: 0

创建索引，In Place=Yes，Rebuilds Table=Yes。

mysql> alter table t_ddl add index idx_b(b);
Query OK, 0 rows affected (1.56 sec)
Records: 0  Duplicates: 0  Warnings: 0

修改列的数据类型，In Place=No，Rebuilds Table=Yes。

mysql> alter table t_ddl modify column b int(10);
Query OK, 1000000 rows affected (4.80 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

修改表的字符集，In Place=No，Rebuilds Table=Yes。

mysql> alter table t_ddl CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 1000000 rows affected (4.86 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

指定 ALGORITHM 测试，表明默认 INPLACE，如果不支持该算法，将直接报错。

mysql> alter table t_ddl add column b varchar(20), ALGORITHM=COPY;
Query OK, 1000000 rows affected (4.79 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

mysql> alter table t_ddl drop column b;
Query OK, 0 rows affected (4.58 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t_ddl add column b varchar(20), ALGORITHM=INPLACE;
Query OK, 0 rows affected (4.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

可是，如何判断 inplace 是 rebuild table 还是 no-rebuild table 呢？

实际上，从官方文档中并没有找到判断方法，因此可以简单地根据执行时间判断。

Extending VARCHAR

字段扩容是很常见的业务需求，MySQL 5.7 中针对列操作 Extending VARCHAR 进行了增强。

当 varchar 长度字节的字节数不变时支持 INPLACE，不过在字段缩容时依然是 COPY。

可是长度字节（length bytes）是什么概念呢？

长度字节指的是 Compact 行记录格式的首部，是一个非 NULL 变长字段长度列表，其中按照列的顺序逆序放置，长度规则如下所示：

如果列的最大长度小于等于 255 字节，用 1 字节表示；
如果列的最大长度大于 255 字节，用 2 字节表示。

变长字段的长度最大不可以超过 2 字节，原因是 MySQL 中 varchar 类型的最大长度限制为 65535。

根据临界值可以将 varchar 类型字段长度分为两个区间，0-255 字节与大于 256 字节。如果将字段长度从 255 字节扩容到 256 字节时，长度字节也会从 1 字节扩容至 2 字节。

In contrast to CHAR, VARCHAR values are stored as a 1-byte or 2-byte length prefix plus data.

由于 varchar 数据类型中指定长度的单位是字符，因此需要将字节转换成字符。

MySQL 中最常见的两种字符集 utf8 与 utf8mb4 中每个字符最多分别占用 3 与 4 字节。

比如对于 utf8，中文占 3 字节，数字、英文、符号占 1 字节。

MySQL supports these Unicode character sets:

utf8mb4: A UTF-8 encoding of the Unicode character set using one to four bytes per character.

utf8mb3: A UTF-8 encoding of the Unicode character set using one to three bytes per character.

utf8: An alias for utf8mb3.

因此 varchar 的长度字节与字符集有关。如下表所示，根据不同字符集中每个字符最多占用的字节数可以计算出 255 字节对应的字符数临界值。

The byte length of a VARCHAR column is dependant on the byte length of the character set.

charset	number of character	length bytes
utf8	0-85 / 86+	0 / 1
utf8mb4	0-63 / 64+	0 / 1

可是，1 字节为什么可以表示 0-255 字节共 256 种长度？

原因是 1 字节有 8 位，每位可以是 0 或 1，对应数据范围 0-255，可以表示 256 种不同的码。

编码用于规定每个字符用 1 字节还是多字节存储，用哪些字节存储，简单说就是字符与二进制的对应关系。

如标准 ASCII 字符集中每个字符用 7 位二进制数编码，最高位用 0 替代。对应数据范围 0-127，用于表示 96 个打印字符与 32 个控制字符。比如英文字符 “A” 对应二进制 0100 0001，对应十进制 65。

如下所示，创建测试表 t_varchar，其中字符集是 utf8，name 字段类型是 varchar(3)，表中有两行数据。

mysql> show create table t_varchar G
*************************** 1. row ***************************
       Table: t_varchar
Create Table: CREATE TABLE `t_varchar` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(3) DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> select * from t_varchar;
+----+--------+
| id | name   |
+----+--------+
|  1 | abc    |
|  2 | 中文   |
+----+--------+
2 rows in set (0.00 sec)

测试字段扩容，其中根据受影响行数判断 DDL 算法。

从 varchar(3) 扩容至 varchar(85) 时使用 INPLACE，从 varchar(85) 扩容至 varchar(86) 时使用 COPY，如果指定使用 INPLACE，执行报错。

mysql> alter table t_varchar modify column name varchar(85) DEFAULT '';
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t_varchar modify column name varchar(86) DEFAULT '';
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> alter table t_varchar modify column name varchar(86) DEFAULT '', ALGORITHM=INPLACE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

因此，当长度字节不变时，varchar 类型字段扩容时 In Place=Yes，Rebuilds Table=No，仅修改元数据，瞬间完成。

可是，当字节数不变时，字段扩容一定都是仅修改元数据吗？

实际上并不是这样，工作中发现了如下两个 BUG，都会导致重建表：

索引字段扩容，修复版本：>=5.7.23、>=8.0.12
5.7.23 之前的低版本升级到 5.7.23 及以后索引字段扩容，>=5.7.27、>=8.0.xx

复现

准备数据

创建表并初始化数据。

mysql> show create table t_varchar_ddl G
*************************** 1. row ***************************
       Table: t_varchar_ddl
Create Table: CREATE TABLE `t_varchar_ddl` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `a` int(10) DEFAULT NULL,
  `name` varchar(30) DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

BUG1-字段有索引

5.7.21

如下所示，varchar 扩容在 <= 85 范围内都是 INPLACE，修改元数据，瞬间完成，与官方文档保持一致。

mysql> alter table t_varchar_ddl modify column name varchar(85) default '';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

由于没有复现，因此怀疑是 BUG，因此查看官方文档 Changes in MySQL 中的 BUG FIX。

如果扩容字段上有索引，执行相同的 DDL 时与官方文档不一致。

mysql> show create table t_varchar_ddl G
*************************** 1. row ***************************
       Table: t_varchar_ddl
Create Table: CREATE TABLE `t_varchar_ddl` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `a` int(10) DEFAULT NULL,
  `name` varchar(30) DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_a` (`a`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

执行用时从 0.01s 变成了 2.63s，而返回行数为 0，表明 In Place=Yes，Rebuilds Table=Yes。

mysql> alter table t_varchar_ddl modify column name varchar(85) default '';
Query OK, 0 rows affected (2.63 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看系统表 performance_schema.events_stages_current，可以获取执行中 DDL 语句的执行状态。

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED,(WORK_COMPLETED/WORK_ESTIMATED)*100 as percent  FROM performance_schema.events_stages_current;
+-----------------------------------+----------------+----------------+---------+
| EVENT_NAME                        | WORK_COMPLETED | WORK_ESTIMATED | percent |
+-----------------------------------+----------------+----------------+---------+
| stage/innodb/alter table (insert) |           7824 |           9621 | 81.3221 |
+-----------------------------------+----------------+----------------+---------+
1 row in set (0.00 sec)

根据官方文档 Changes in MySQL 5.7.23，<5.7.23 版本中索引字段扩容时 INPLACE 算法失效，需要重建表。

For attempts to increase the length of a VARCHAR column of an InnoDB table using ALTER TABLE with the INPLACE algorithm, the attempt failed if the column was indexed.

5.7.24

5.7.24 版本中执行相同的字段扩容 SQL 时瞬间完成。

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.24-log |
+------------+
1 row in set (0.00 sec)

mysql> alter table t_varchar_ddl modify column name varchar(85) default '';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

那么，是不是可以认为 >=5.7.23 版本中字段扩容就没有风险了呢？

实际上也有风险，下面介绍另外一个 BUG。

BUG2-原地升级

替换 server

首先将刚才的 5.7.21 原地升级到 5.7.24。

替换 server。

[root@test ~]# /export/servers/mysql/bin/mysqld_safe --defaults-file=/export/servers/mysql/etc/my_3361.cnf &

查看数据库版本。

mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.24-log |
+------------+
1 row in set (0.00 sec)

重新执行，重建表，复现该问题。

mysql> alter table t_varchar_ddl modify column name varchar(85) default '';
Query OK, 0 rows affected (2.40 sec)
Records: 0  Duplicates: 0  Warnings: 0

upgrade

调用官方提供的升级工具 mysql_upgrade 升级表结构，输出信息如下所示。

[root@test ~]# /export/servers/mysql/bin/mysql_upgrade -uadmin -p3361 -h 127.0.0.1 -P 3361
mysql_upgrade: [Warning] Using a password on the command line interface can be insecure.
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
The sys schema is already up to date (version 1.5.1).
Checking databases.
cctest.t_varchar_ddl                               OK
sys.sys_config                                     OK
Upgrade process completed successfully.
Checking if update is needed.

再次扩容时没有重建表，没有复现。

mysql> alter table t_varchar_ddl modify column name varchar(85) default '';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

根据官方文档 Changes in MySQL 5.7.27，<5.7.23 版本中索引字段扩容，即使升级到 5.7.23 或更高版本，字段扩容时 INPLACE 算法依然失效，需要重建表。

For InnoDB tables that contained an index on a VARCHAR column and were created prior to MySQL 5.7.23, some simple ALTER TABLE statements that should have been done in place were performed with a table rebuild after an upgrade to MySQL 5.7.23 or higher. (Bug #29375764, Bug #94383)

因此，线上 varchar 扩容导致重建表的原因分两种：

<5.7.23，索引字段扩容；
>=5.7.23，线上有部分数据库是从 5.7.19 原地升级到了 5.7.24。升级过程中替换了 server，但可能没有调用 mysql_upgrade。

8.0-INSTANT DDL

最后，体验下 8.0 中的 INSTANT DDL。

实际上 INSTANT 算法的应用场景一开始比较有限，到目前已经越来越广了，如：

add column，8.0.12
rename column，>=8.0.28
add column to any position in the table，>=8.0.29

下面进行测试，数据库版本是 8.0.31。

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.31    |
+-----------+
1 row in set (0.00 sec)

extend varchar

在 8.0 中同样建表并初始化然后字段扩容，结果发现 ALGORITHM=COPY。

mysql> alter table t_varchar_ddl modify column name varchar(85) default '';
Query OK, 1000000 rows affected (9.67 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

原因是 5.7 中默认字符集为 utf8，而 8.0 中默认字符集为 utf8mb4。

mysql> show create table t_varchar_ddl G
*************************** 1. row ***************************
       Table: t_varchar_ddl
Create Table: CREATE TABLE `t_varchar_ddl` (
  `id` int NOT NULL AUTO_INCREMENT,
  `a` int DEFAULT NULL,
  `name` varchar(30) DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_a` (`a`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

因此，字段扩容的临界值等于 63 字符。

mysql> alter table t_varchar_ddl modify column name varchar(63) default '';
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table t_varchar_ddl modify column name varchar(64) default '';
Query OK, 1000000 rows affected (9.90 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

add column

对比 5.7 与 8.0 中新增字段的性能差异，执行用时分别为 6.50s 与 0.21s。

# 5.7.24
mysql> alter table t_varchar_ddl add column age int(10) default 0;
Query OK, 0 rows affected (6.50 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 8.0.31
mysql> alter table t_varchar_ddl add column age int(10) default 0;
Query OK, 0 rows affected, 1 warning (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 1

通过指定算法可以确认前者，后者 ALGORITHM=INSTANT。

mysql> alter table t_varchar_ddl add column age int(10) default 0, ALGORITHM=INSTANT;
Query OK, 0 rows affected, 1 warning (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 1

秒级加列用时 0.2 s 左右，而仅修改元数据的字段扩容仅用时 0.01s 左右，可见 INSTANT 算法并不是仅修改元数据这么简单，具体将新开一篇讲解。

表中加列时默认是在表的最后添加列，不过从 8.0.29 开始也支持在现有列中间秒级加列。

mysql> alter table t_varchar_ddl add column age int(10) default 0 after a;
Query OK, 0 rows affected, 1 warning (0.23 sec)
Records: 0  Duplicates: 0  Warnings: 1

小技巧

varchar 字符

char 与 varchar 数据类型中指定长度的单位是什么，是字符还是字节？答案是字符，下面测试验证。

The CHAR and VARCHAR types are declared with a length that indicates the maximum number of characters you want to store. For example, CHAR(30) can hold up to 30 characters.

创建测试表 t_varchar，其中 name 字段的数据类型是 varchar(3)。

mysql> create table t_varchar(id int primary key auto_increment, t_varchar varchar(3) default '');
Query OK, 0 rows affected (0.02 sec)

如果插入数据的长度小于3个字符，插入正常。

mysql> insert into t_varchar(name) values('abc'),('中文');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t_varchar;
+----+--------+
| id | name   |
+----+--------+
|  1 | abc    |
|  2 | 中文   |
+----+--------+
2 rows in set (0.00 sec)

如果插入数据的长度大于3个字符，插入报错。

mysql> insert into t_varchar(name) values('abcde');
ERROR 1406 (22001): Data too long for column 'name' at row 1
mysql> 
mysql> insert into t_varchar(name) values('中文超长');
ERROR 1406 (22001): Data too long for column 'name' at row 1

原因是1个中文字符占用3个字节，如下所示，其中 char_length 函数用于计算字符长度，length 函数用于计算字节长度。

mysql> select id, char_length(name), length(name) as byte_length from t_varchar;
+----+-------------------+-------------+
| id | char_length(name) | byte_length |
+----+-------------------+-------------+
|  1 |                 3 |           3 |
|  2 |                 2 |           6 |
+----+-------------------+-------------+
2 rows in set (0.00 sec)

Release Notes

上面提到，在没有复现后怀疑是 BUG，但是查找信息浪费了很多时间，后来发现可以从官方文档中的 Release Notes 中查找到每个版本的更新。

具体要确认 BUG 可以小范围内逐一查看更新文档，比如查找索引字段扩容导致重建表的 BUG 时，可以使用 varchar 关键词查询，最终发现 5.7 与 8.0 中的修复版本是同一天发布。

查看 DDL 执行状态

开启 events 统计，重启后失效。

update performance_schema.setup_consumers set ENABLED='YES' where NAME in ('events_stages_current','events_waits_current');

开启后立刻生效，可以通过系统表查看执行中 DDL 的执行状态，包括执行进度。

mysql> SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED,(WORK_COMPLETED/WORK_ESTIMATED)*100 as percent  FROM performance_schema.events_stages_current;
+-----------------------------------+----------------+----------------+---------+
| EVENT_NAME                        | WORK_COMPLETED | WORK_ESTIMATED | percent |
+-----------------------------------+----------------+----------------+---------+
| stage/innodb/alter table (insert) |           7824 |           9621 | 81.3221 |
+-----------------------------------+----------------+----------------+---------+
1 row in set (0.00 sec)

处理

针对该问题，提出以下两点建议作为优化方案：

建议升级版本，高版本中修复了很多已知 BUG，避免踩坑
大表字段扩容建议使用 pt-osc，避免导致较大主从延迟

查看线上 MySQL 实例的版本分布，大版本以 5.7 为主，小版本以 5.7.24 与 5.7.21 为主。

mysql> select instance_version, count(*) as number from mysql_instance group by instance_version order by number desc;
+------------------+--------+
| instance_version | number |
+------------------+--------+
| MySQL5.7.24      |   1965 |
| MySQL5.7.21      |   1631 |
| MySQL5.6.39      |    630 |
| MySQL5.7.33      |    264 |
| MySQL5.7.19      |    206 |
| MySQL5.5.14      |     24 |
| MySQL5.7.17      |      2 |
| MySQL5.7.21-20   |      1 |
+------------------+--------+
8 rows in set (0.01 sec)

结论

根据官方文档，extend varchar 在长度字节（length bytes）不变时仅修改元数据，因此理论上可以在瞬间完成。如果字段扩容导致长度字节也从 1 字节扩容至 2 字节，就会使用 COPY 算法。

但实际上，即使长度字节不变，也有可能导致重建表进而造成主从延迟。具体涉及到以下两个 BUG，索引字段扩容导致重建表：

索引字段扩容，修复版本：>=5.7.23、>=8.0.12
5.7.23 之前的低版本升级到 5.7.23 及以后索引字段扩容，>=5.7.27、>=8.0.xx

简单对比 DDL 的三种算法，理论上性能依次提高。

版本	ALGORITHM	Permits Concurrent DML	DESCRIPTION
<5.6	COPY	NO	copy original table data to new table
>=5.6	INPLACE	YES	not copy data, but may rebuild the table in place
>=8.0.12	INSTANT	YES	only modify metadata, table data is unaffected