无论Oracle还是MySQL,都支持这种varchar/varchar2的数据类型,但是不当的使用可能产生相应的问题,值得关注,相关历史文章,
《Oracle 19c对VARCHAR2的限制》
《小白学习MySQL - varchar类型字段为什么经常定义成255?》
《为什么日期不建议使用VARCHAR2或者NUMBER?》
《支持超过4000字节的varchar2类型》
技术社群的这篇文章《故障分析 | MySQL 扩展 VARCHAR 长度遭遇问题的总结》讲解了和MySQL中varchar类型相关的问题,学习和了解下。
1背景介绍
最近,业务反馈有个扩展 VARCHAR 改表需求失败多次,需要干预处理一下。
经过排查分析得出,这是由于改表系统解析改表需求得出错误的改表方案导致,即这类改表可以满足快速改表操作(直接使用 ALTER TABLE
),理论上任务下发后能马上改完,但是工单结果是执行触发 10 秒超时,最终工单失败。
原则上,VARCHAR 类型的扩展是可以满足快速改表的,我们的改表工单针对这类需求也是支持的,但是实际结果与预期不符,这到底是工单系统的 Bug?还是 MySQL 的坑呢?
本文就来总结一下 扩展 VARCHAR 长度可能会遇到的一些问题,以及我们给出的解决方案,仅供参考。
仅讨论 MySQL 5.7 及以后的版本。
2MySQL 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
进行改表,如果是大表可以节省很多时间,提升效率,也因此遇到了很多问题,才有了这篇文章。
3问题汇总
首先简单介绍一下我们的改表系统的处理逻辑,我们会根据业务的改表需求去选择最优的改表方案:
-
满足快速改表就直接使用
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_testG
*************************** 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 重新执行,以此避免业务反复提交工单,应该能大大提升好感度。
这个问题虽然知道解决方案,但是依旧不知道原因,我猜测可能是跟统计信息不准确有关系(或者约束),要是有大佬知道原因,请告知一下。
4因此
MySQL Online DDL 特性给 DBA 带来了很多的便利,提升了工作效率,我们可以基于官方的理论作为指导去优化我们的系统。但是实际情况是理论知识很简单,线上环境十分复杂,可能会遇到各种意料之外的事情,任何线上的操作都要给自己留好后路做好兜底,这是十分必要的。
我们的系统,如果没有添加 10 秒超时的兜底,那势必会因为解析错误导致选了错误的改表方案,然后导致从库延迟,可能会影响线上业务,想想都有点心慌。
这里有个注意事项,针对执行超时不能简单的使用 timeout 等属性进行控制,还需要添加检查逻辑,要到数据库里面去查一下任务是否真的已经终止了。避免因为 timeout 异常导致终止信号没有给到 MySQL,这种可能会引发一系列问题,切记切记。
以上,仅供参考。
如果您认为这篇文章有些帮助,还请不吝点下文章末尾的"点赞"和"在看",或者直接转发pyq,
近期更新的文章:《MySQL用户管理中的权限系统介绍》
《全面提升思考、表达和解决问题能力的一个神奇原理介绍》《MySQL中Performance Schema和锁》《Oracle备份与恢复常见但难解决的15个问题》
《MySQL常用监控指标学习了解》
近期的热文:《推荐一篇Oracle RAC Cache Fusion的经典论文》
《"红警"游戏开源代码带给我们的震撼》
文章分类和索引:《公众号1300篇文章分类和索引》