MySQL 扩展 VARCHAR 长度遭遇问题的总结
最近，业务反馈有个扩展 VARCHAR 改表需求失败多次，需要干预处理一下。
作者：莫善，某互联网公司高级 DBA。
爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。
本文约 3600 字，预计阅读需要 12 分钟。
背景介绍
最近，业务反馈有个扩展 VARCHAR 改表需求失败多次，需要干预处理一下。
经过排查分析得出，这是由于改表系统解析改表需求得出错误的改表方案导致，即这类改表可以满足快速改表操作（直接使用 ALTER TABLE），理论上任务下发后能马上改完，但是工单结果是执行触发 10 秒超时，最终工单失败。
原则上，VARCHAR 类型的扩展是可以满足快速改表的，我们的改表工单针对这类需求也是支持的，但是实际结果与预期不符，这到底是工单系统的 Bug？还是 MySQL 的坑呢？
本文就来总结一下 扩展 VARCHAR 长度可能会遇到的一些问题，以及我们给出的解决方案，仅供参考。
仅讨论 MySQL 5.7 及以后的版本。
MySQL Online DDL
| Operation | Extending VARCHAR column size |
|---|---|
| In Place | Yes |
| Rebuilds Table | No |
| Permits Concurrent DML | Yes |
| Only Modifies Metadata | Yes |
上表是 MySQL 官方文档中关于 Online DDL 章节中的一部分。可以看到关于 VARCHAR 类型的字段的扩展是可以原地改表，且仅仅改了元数据，理论上敲完回车就执行结束。
当然针对这种场景，还是有一些条件的，直接贴原话：
The number of length bytes required by a VARCHAR column must remain the same. For VARCHAR columns of 0 to 255 bytes in size, one length byte is required to encode the value. For VARCHAR columns of 256 bytes in size or more, two length bytes are required. As a result, in-place ALTER TABLE only supports increasing VARCHAR column size from 0 to 255 bytes, or from 256 bytes to a greater size. In-place ALTER TABLE does not support increasing the size of a VARCHAR column from less than 256 bytes to a size equal to or greater than 256 bytes. In this case, the number of required length bytes changes from 1 to 2, which is only supported by a table copy (ALGORITHM=COPY).
https://dev.mysql.com/doc/refman/5.7/en/innodb-online-ddl-operations.html
VARCHAR 是变长类型，实际存储的内容不固定，需要 1 或者 2 个字节来表示实际长度，所以修改前和修改后，这个字节数要求是一致。
有了这个技术基础，我们的改表系统就针对这类需求做了优化，可以支持直接使用 ALTER TABLE 进行改表，如果是大表可以节省很多时间，提升效率，也因此遇到了很多问题，才有了这篇文章。
问题汇总
首先简单介绍一下我们的改表系统的处理逻辑，我们会根据业务的改表需求去选择最优的改表方案：
-
满足快速改表就直接使用
ALTER TABLE进行操作。比如，删除索引，修改表名/列名，修改默认值/注释，扩展 VARCHAR 长度，小表添加唯一索引以及 8.0 快速加列等等。
-
不满足快速改表就优先选择 gh-ost 进行改表
binlog format不为 ROW 则不能使用 gh-ost，添加唯一索引必须使用 gh-ost。 -
不满足 gh-ost 都会选择 pt-osc 进行改表。
其中添加唯一索引会直接失败。
那么问题来了，我们是如何判断业务改表需求是不是扩展 VARCHAR？
其实思路也很简单，就是检查改表前后的 information_schema.columns 记录，用到的 SQL 如下：
select * from information_schema.columns where table_schema = 'db' and table_name = 'table' and column_name = 'col';
# 样例数据
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: information_schema
TABLE_NAME: CHARACTER_SETS
COLUMN_NAME: CHARACTER_SET_NAME
ORDINAL_POSITION: 1
COLUMN_DEFAULT:
IS_NULLABLE: NO
DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 32
CHARACTER_OCTET_LENGTH: 96
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: utf8
COLLATION_NAME: utf8_general_ci
COLUMN_TYPE: varchar(32)
COLUMN_KEY:
EXTRA:
PRIVILEGES: select
COLUMN_COMMENT:
GENERATION_EXPRESSION:
DATA_TYPE值是 VARCHARCHARACTER_MAXIMUM_LENGTH的值，要求改表后要大于等于改表前的值CHARACTER_OCTET_LENGTH的值，要求改表前后这个值要么是都小于等于 255，要么是都大于 255- 除
DATA_TYPE/COLUMN_TYPE/CHARACTER_MAXIMUM_LENGTH/CHARACTER_OCTET_LENGTH字段外的其余字段要求改表前后保持一致
问题一：默认值问题
我们发现，如果还改了字段名、注释、默认值这种元数据信息，依旧是可以快速改表，于是乎就进行了优化，不再比较这三个属性 COLUMN_NAME|COLUMN_COMMENT|COLUMN_DEFAULT。
关于默认值，看起来有点复杂，最开始也是想跑偏了，认为判断 COLUMN_DEFAULT 的值就行，比较这个值前后要么都是 null，要么都不是 null。都不是 null 的情况下可以是任意值，比如可以用下面的逻辑判断改表前后是一致即可。
if(COLUMN_DEFAULT is null ,null,"")
但是有个问题，如果一个字段从 允许为 null 默认值为 1 变成 不允许为null 默认值也是 1，该值改表前后也是一致的，具体测试如下：
CREATE TABLE `tb_test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`rshost` varchar(20) DEFAULT '1' COMMENT '主机地址',
`cpu_info` json DEFAULT NULL COMMENT 'cpu信息 json串',
`mem_info` json DEFAULT NULL COMMENT 'mem信息 json串, 单位是GB',
`io_info` json DEFAULT NULL COMMENT '磁盘io使用情况, 单位是KB',
`net` json DEFAULT NULL COMMENT '网络使用情况, 单位是KB(speed单位是MB/S)',
`a_time` datetime NOT NULL DEFAULT '2022-01-01 00:00:00',
PRIMARY KEY (`id`),
KEY `idx_a_time` (`a_time`),
KEY `idx_rshost` (`rshost`)
) ENGINE=InnoDB AUTO_INCREMENT=623506728 DEFAULT CHARSET=utf8mb4
>select * from information_schema.columns where table_name = 'tb_test' and column_name = 'rshost'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: dbzz_monitor
TABLE_NAME: tb_test
COLUMN_NAME: rshost
ORDINAL_POSITION: 2
COLUMN_DEFAULT: 1
IS_NULLABLE: YES
DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 30
CHARACTER_OCTET_LENGTH: 120
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: utf8mb4
COLLATION_NAME: utf8mb4_general_ci
COLUMN_TYPE: varchar(30)
COLUMN_KEY:
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT: 主机地址
GENERATION_EXPRESSION:
1 row in set (0.00 sec)
>alter table tb_test modify `rshost` varchar(30) not null DEFAULT '1' COMMENT '主机地址';
Query OK, 1000000 rows affected (13.68 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
>select * from information_schema.columns where table_name = 'tb_test' and column_name = 'rshost'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: dbzz_monitor
TABLE_NAME: tb_test
COLUMN_NAME: rshost
ORDINAL_POSITION: 2
COLUMN_DEFAULT: 1
IS_NULLABLE: NO
DATA_TYPE: varchar
CHARACTER_MAXIMUM_LENGTH: 30
CHARACTER_OCTET_LENGTH: 120
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: utf8mb4
COLLATION_NAME: utf8mb4_general_ci
COLUMN_TYPE: varchar(30)
COLUMN_KEY:
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT: 主机地址
GENERATION_EXPRESSION:
1 row in set (0.00 sec)
可以看到 COLUMN_DEFAULT 这个列的值是非 null 且不变，按照上面的判断逻辑会认为可以快速改表，但是我们知道实际上这个需求是需要 copy 数据的。
其实，关于默认值问题使用 IS_NULLABLE 的值就可以完美解决, 如果是 null 到 not null 这个值会从 yes 变成 no；如果是 not null 到 null，这个值会从 no变成 yes。
所以最终解决方案仅比较 IS_NULLABLE 即可，只要改表前后一致就认为默认值这个属性满足快速改表。
在测试这个问题的时候发现一个现象：not null 到 null 可以使用 inplace 算法，但是需要 copy 数据；null 到 not null 不能使用 inplace，请看下面的用例：
-- not null --> null可以使用inplace
>alter table tb_test modify `rshost` varchar(30) DEFAULT '1' COMMENT '主机地址' ,ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (3.45 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- null --> not null不可以使用inplace
>alter table tb_test modify `rshost` varchar(30) not null DEFAULT '1' COMMENT '主机地址' ,ALGORITHM=INPLACE, LOCK=NONE;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: cannot silently convert NULL values, as required in this SQL_MODE. Try ALGORITHM=COPY.
>
-- 可以使用下面的操作查看改表进度拷贝数据的情况，第一次使用需要开启此功能
-- UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'stage/innodb/alter%';
-- UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%stages%';
>SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_current;
+-----------------------------+----------------+----------------+
| EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
+-----------------------------+----------------+----------------+
| stage/sql/copy to tmp table | 272289 | 978903 |
+-----------------------------+----------------+----------------+
1 row in set (0.00 sec)
-- 为了避免测试干扰，检查events_stages_history表之前可以先清空，切记不要对线上环境做此操作。
-- TRUNCATE TABLE performance_schema.events_stages_history;
>SELECT EVENT_NAME, WORK_COMPLETED, WORK_ESTIMATED FROM performance_schema.events_stages_history;
+-----------------------------+----------------+----------------+
| EVENT_NAME | WORK_COMPLETED | WORK_ESTIMATED |
+-----------------------------+----------------+----------------+
| stage/sql/copy to tmp table | 1000000 | 978903 |
+-----------------------------+----------------+----------------+
1 row in set (0.00 sec)
问题二：索引字段问题
过了一段时间又发现第二个问题，部分工单会触发执行 10 秒超时失败。
工单系统判断用户的改表需求，满足直接使用
ALTER TABLE进行操作会有个 10 秒超时的兜底策略，来避免因为解析错误导致方案选择错误最终影响主从延迟。另外，也建议带上
ALGORITHM=INPLACE, LOCK=NONE，避免因为不是使用 inplace 导致 DML 阻塞。
这个问题排查了很久都没什么眉目，反反复复的查阅文档及测试，始终都认为这个需求一定是满足快速改表的方案。实在是想不明白到底是哪里的问题，还一度认为是 MySQL 的 Bug。
下面是一张 100w 记录表的测试用例：
> show create table tb_test\G
*************************** 1. row ***************************
Table: tb_test
Create Table: CREATE TABLE `tb_test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`rshost` varchar(30) NOT NULL DEFAULT '1' COMMENT '主机地址',
`cpu_info` json DEFAULT NULL COMMENT 'cpu信息 json串',
`mem_info` json DEFAULT NULL COMMENT 'mem信息 json串, 单位是GB',
`io_info` json DEFAULT NULL COMMENT '磁盘io使用情况, 单位是KB',
`net` json DEFAULT NULL COMMENT '网络使用情况, 单位是KB(speed单位是MB/S)',
`a_time` datetime NOT NULL DEFAULT '2022-01-01 00:00:00',
PRIMARY KEY (`id`),
KEY `idx_a_time` (`a_time`),
KEY `idx_rshost` (`rshost`)
) ENGINE=InnoDB AUTO_INCREMENT=623506728 DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
> select count(*) from tb_test;
+----------+
| count(*) |
+----------+
| 1000000 |
+----------+
1 row in set (0.15 sec)
> alter table tb_test modify `rshost` varchar(31) NOT NULL DEFAULT '1' COMMENT '主机地址';
Query OK, 0 rows affected (3.61 sec)
Records: 0 Duplicates: 0 Warnings: 0
> alter table tb_test modify `rshost` varchar(32) NOT NULL DEFAULT '1' COMMENT '主机地址', ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (3.66 sec)
Records: 0 Duplicates: 0 Warnings: 0
> alter table tb_test drop index idx_rshost;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
> alter table tb_test modify `rshost` varchar(33) NOT NULL DEFAULT '1' COMMENT '主机地址', ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
> alter table tb_test modify `rshost` varchar(34) NOT NULL DEFAULT '1' COMMENT '主机地址', ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
>
可以看到 rshost 字段有一个索引，在扩展字段的时候虽然支持 inplace，但是实际上很慢，内部应该是重建索引了，后来将索引删除后就秒改了。
针对这个场景，我们的解决方案是使用 gh-ost/pt-osc 进行改表，那么问题来了，我们应该怎么判断目标字段是否是被索引了呢？
请看下面的 SQL，information_schema.STATISTICS 记录了一个表的所有索引字段信息，可以很方便的判断某个字段是否被索引。
select * from information_schema.STATISTICS where table_schema = 'db' and table_name = 'table' and column_name = 'col';
其他问题
这个问题也是执行 10 秒超时，也就是文章开头提到的业务反馈的问题，其实跟 问题二 差不多同期，但在解决了 问题二 后还是一直找不到原因及解决方案。
关于这个问题甚至都没法复现，不像 问题二 可以方便复现，当时在业务的线上库做操作又能 100% 复现，但是将他们的表及数据单独导出来放在测试环境就不行。
在业务库上测试是选了一个从库，不记录 binlog 的方式（
set sql_log_bin = 0）。虽然不建议这么做，但是实属迫不得已，在测试环境复现不出来。
后来实在找不到原因，就跳过快速改表的方案使用改表工具进行处理，后来这个事情就算不了了之了。直到前几天业务突然找我，说之前的那个表能快速改表了。我赶紧去查看了工单详情，发现确实如业务所述，这回我就更加郁闷了，难不成是见鬼了？这玩意还自带歇业窗口的嘛？
本着严谨的态度，又去测了一下。确实是可以满足快速改表了，但是原因还是找不到，这感觉真的很难受。
最后，静下来认真梳理了一下，发现了一些猫腻。下面是我的测试思路：
1. 将线上的表导出并导入到测试环境
因为表本身就几个 G，不算大就使用了 mysqldump 进行导出导入。这个操作并非 100% 复原线上的环境，有个隐藏的变量被修改了，那就是这个表被重建了，这个跟之前业务用改表工具进行修改后的操作有点类似，所以就猜想，会不会是因为这个表本身存在空洞导致的呢。
最后通过拉历史备份，还原了一个环境进行了测试，果不其然不能快速改表。为了印证了想法，就去查了一下这个表的空洞。十分遗憾，这个表并没有存在空洞（空洞只有几 MB）。这回又郁闷了，还以为要破案了，但是不管怎么样既然怀疑是重建表能解决，那就开搞。
2. 重建前的状态
业务从 varchar(300) 扩展到 varchar(500)，其他属性没变更。
| 1170930999 | dba | 192.168.1.100:47522 | dbzz_dbreport | Query | 45 | altering table | ALTER TABLE t_recycle_express MODIFY address VARCHAR(500) NOT NULL DEFAULT '' COMMENT '地址', ALGORITHM=INPLACE, LOCK=NONE; |
3. 重建后的状态
>ALTER TABLE t_recycle_express engine = innodb;
Query OK, 0 rows affected (18 min 52.60 sec)
Records: 0 Duplicates: 0 Warnings: 0
>ALTER TABLE
-> t_recycle_express
-> MODIFY
-> address VARCHAR(500) NOT NULL DEFAULT '' COMMENT '地址';
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
活久见，还真是重建表后就能解决了！虽然很郁闷，终究是有一个解决方案了，后期我们决定对此做个优化，将满足快速改表的工单又触发十秒超时的改为使用 gh-ost/pt-osc 重新执行，以此避免业务反复提交工单，应该能大大提升好感度。
这个问题虽然知道解决方案，但是依旧不知道原因，我猜测可能是跟统计信息不准确有关系（或者约束），要是有大佬知道原因，请告知一下。
总结
MySQL Online DDL 特性给 DBA 带来了很多的便利，提升了工作效率，我们可以基于官方的理论作为指导去优化我们的系统。但是实际情况是理论知识很简单，线上环境十分复杂，可能会遇到各种意料之外的事情，任何线上的操作都要给自己留好后路做好兜底，这是十分必要的。
我们的系统，如果没有添加 10 秒超时的兜底，那势必会因为解析错误导致选了错误的改表方案，然后导致从库延迟，可能会影响线上业务，想想都有点心慌。
这里有个注意事项，针对执行超时不能简单的使用 timeout 等属性进行控制，还需要添加检查逻辑，要到数据库里面去查一下任务是否真的已经终止了。避免因为 timeout 异常导致终止信号没有给到 MySQL，这种可能会引发一系列问题，切记切记。
以上，仅供参考。
更多技术文章，请访问：https://opensource.actionsky.com/
关于 SQLE
SQLE 是一款全方位的 SQL 质量管理平台，覆盖开发至生产环境的 SQL 审核和管理。支持主流的开源、商业、国产数据库，为开发和运维提供流程自动化能力，提升上线效率，提高数据质量。
