MySQL擴展VARCHAR長度遭遇問題匯總分析
背景介紹
最近，業(yè)務反饋有個擴展 VARCHAR 改表需求失敗多次，需要干預處理一下。
擴容場景
經(jīng)過排查分析得出，這是由于改表系統(tǒng)解析改表需求得出錯誤的改表方案導致，即這類改表可以滿足快速改表操作（直接使用 ALTER TABLE），理論上任務下發(fā)后能馬上改完，但是工單結果是執(zhí)行觸發(fā) 10 秒超時，最終工單失敗。
原則上，VARCHAR 類型的擴展是可以滿足快速改表的，我們的改表工單針對這類需求也是支持的，但是實際結果與預期不符，這到底是工單系統(tǒng)的 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 章節(jié)中的一部分?？梢钥吹疥P于 VARCHAR 類型的字段的擴展是可以原地改表，且僅僅改了元數(shù)據(jù)，理論上敲完回車就執(zhí)行結束。
當然針對這種場景，還是有一些條件的，直接貼原話：
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-ope...
VARCHAR 是變長類型，實際存儲的內容不固定，需要 1 或者 2 個字節(jié)來表示實際長度，所以修改前和修改后，這個字節(jié)數(shù)要求是一致。
有了這個技術基礎，我們的改表系統(tǒng)就針對這類需求做了優(yōu)化，可以支持直接使用 ALTER TABLE 進行改表，如果是大表可以節(jié)省很多時間，提升效率，也因此遇到了很多問題，才有了這篇文章。
問題匯總
首先簡單介紹一下我們的改表系統(tǒng)的處理邏輯，我們會根據(jù)業(yè)務的改表需求去選擇最優(yōu)的改表方案：
滿足快速改表就直接使用
ALTER TABLE進行操作。比如，刪除索引，修改表名/列名，修改默認值/注釋，擴展 VARCHAR 長度，小表添加唯一索引以及 8.0 快速加列等等。
不滿足快速改表就優(yōu)先選擇 gh-ost 進行改表
binlog format不為 ROW 則不能使用 gh-ost，添加唯一索引必須使用 gh-ost。不滿足 gh-ost 都會選擇 pt-osc 進行改表。
其中添加唯一索引會直接失敗。
那么問題來了，我們是如何判斷業(yè)務改表需求是不是擴展 VARCHAR？
其實思路也很簡單，就是檢查改表前后的 information_schema.columns 記錄，用到的 SQL 如下：
select * from information_schema.columns where table_schema = 'db' and table_name = 'table' and column_name = 'col';
# 樣例數(shù)據(jù)
*************************** 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字段外的其余字段要求改表前后保持一致
問題一：默認值問題
我們發(fā)現(xiàn)，如果還改了字段名、注釋、默認值這種元數(shù)據(jù)信息，依舊是可以快速改表，于是乎就進行了優(yōu)化，不再比較這三個屬性 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 '網(wǎng)絡使用情況, 單位是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 數(shù)據(jù)的。
其實，關于默認值問題使用 IS_NULLABLE 的值就可以完美解決, 如果是 null 到 not null 這個值會從 yes 變成 no；如果是 not null 到 null，這個值會從 no變成 yes。
所以最終解決方案僅比較 IS_NULLABLE 即可，只要改表前后一致就認為默認值這個屬性滿足快速改表。
在測試這個問題的時候發(fā)現(xiàn)一個現(xiàn)象：not null 到 null 可以使用 inplace 算法，但是需要 copy 數(shù)據(jù)；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. > -- 可以使用下面的操作查看改表進度拷貝數(shù)據(jù)的情況，第一次使用需要開啟此功能 -- 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表之前可以先清空，切記不要對線上環(huán)境做此操作。 -- 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)
問題二：索引字段問題
過了一段時間又發(fā)現(xiàn)第二個問題，部分工單會觸發(fā)執(zhí)行 10 秒超時失敗。
工單系統(tǒng)判斷用戶的改表需求，滿足直接使用
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 '網(wǎng)絡使用情況, 單位是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';
其他問題
這個問題也是執(zhí)行 10 秒超時，也就是文章開頭提到的業(yè)務反饋的問題，其實跟 問題二 差不多同期，但在解決了 問題二 后還是一直找不到原因及解決方案。
關于這個問題甚至都沒法復現(xiàn)，不像 問題二 可以方便復現(xiàn)，當時在業(yè)務的線上庫做操作又能 100% 復現(xiàn)，但是將他們的表及數(shù)據(jù)單獨導出來放在測試環(huán)境就不行。
在業(yè)務庫上測試是選了一個從庫，不記錄 binlog 的方式（set sql_log_bin = 0）。雖然不建議這么做，但是實屬迫不得已，在測試環(huán)境復現(xiàn)不出來。
后來實在找不到原因，就跳過快速改表的方案使用改表工具進行處理，后來這個事情就算不了了之了。直到前幾天業(yè)務突然找我，說之前的那個表能快速改表了。我趕緊去查看了工單詳情，發(fā)現(xiàn)確實如業(yè)務所述，這回我就更加郁悶了，難不成是見鬼了？這玩意還自帶歇業(yè)窗口的嘛？
本著嚴謹?shù)膽B(tài)度，又去測了一下。確實是可以滿足快速改表了，但是原因還是找不到，這感覺真的很難受。
最后，靜下來認真梳理了一下，發(fā)現(xiàn)了一些貓膩。下面是我的測試思路：
1. 將線上的表導出并導入到測試環(huán)境
因為表本身就幾個 G，不算大就使用了 mysqldump 進行導出導入。這個操作并非 100% 復原線上的環(huán)境，有個隱藏的變量被修改了，那就是這個表被重建了，這個跟之前業(yè)務用改表工具進行修改后的操作有點類似，所以就猜想，會不會是因為這個表本身存在空洞導致的呢。
最后通過拉歷史備份，還原了一個環(huán)境進行了測試，果不其然不能快速改表。為了印證了想法，就去查了一下這個表的空洞。十分遺憾，這個表并沒有存在空洞（空洞只有幾 MB）。這回又郁悶了，還以為要破案了，但是不管怎么樣既然懷疑是重建表能解決，那就開搞。
2. 重建前的狀態(tài)
業(yè)務從 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. 重建后的狀態(tài)
>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活久見，還真是重建表后就能解決了！雖然很郁悶，終究是有一個解決方案了，后期我們決定對此做個優(yōu)化，將滿足快速改表的工單又觸發(fā)十秒超時的改為使用 gh-ost/pt-osc 重新執(zhí)行，以此避免業(yè)務反復提交工單，應該能大大提升好感度。
這個問題雖然知道解決方案，但是依舊不知道原因，我猜測可能是跟統(tǒng)計信息不準確有關系（或者約束），要是有大佬知道原因，請告知一下。
總結
MySQL Online DDL 特性給 DBA 帶來了很多的便利，提升了工作效率，我們可以基于官方的理論作為指導去優(yōu)化我們的系統(tǒng)。但是實際情況是理論知識很簡單，線上環(huán)境十分復雜，可能會遇到各種意料之外的事情，任何線上的操作都要給自己留好后路做好兜底，這是十分必要的。
我們的系統(tǒng)，如果沒有添加 10 秒超時的兜底，那勢必會因為解析錯誤導致選了錯誤的改表方案，然后導致從庫延遲，可能會影響線上業(yè)務，想想都有點心慌。
這里有個注意事項，針對執(zhí)行超時不能簡單的使用 timeout 等屬性進行控制，還需要添加檢查邏輯，要到數(shù)據(jù)庫里面去查一下任務是否真的已經(jīng)終止了。避免因為 timeout 異常導致終止信號沒有給到 MySQL，這種可能會引發(fā)一系列問題，切記切記。
以上就是MySQL擴展VARCHAR長度遭遇問題匯總分析的詳細內容，更多關于MySQL擴展VARCHAR問題的資料請關注腳本之家其它相關文章！
相關文章
在MySQL中,當存儲IPv4地址時,應該使用32位的無符號整數(shù)(UNSIGNED INT)來存儲IP地址，而不是使用字符串，下面就來詳細的介紹一下具體原因，感興趣的可以了解一下2023-05-05
mysql 實現(xiàn)互換表中兩列數(shù)據(jù)方法簡單實例
這篇文章主要介紹了mysql 實現(xiàn)互換表中兩列數(shù)據(jù)方法簡單實例的相關資料,需要的朋友可以參考下2016-10-10
InnoDB 事務鎖，事務鎖實現(xiàn)起來的代碼卻是又臭又硬的好大一坨，各種細節(jié)，今天小編在這里整理一個源碼閱讀筆記，把那一坨加鎖相關的代碼提煉出來，感興趣的小伙伴別忘記收藏奧2021-09-09
這篇文章主要介紹了MySQL的主從復制原理詳細分析，讀寫分離是基于主從復制來實現(xiàn)的。文章圍繞主題展開詳細的內容介紹，具有一定的參考價值，需要的小伙伴可以參考一下2022-11-11
mysql的日期和時間函數(shù) 這里是一個使用日期函數(shù)的例子。下面的查詢選擇所有 date_col 值在最后 30 天內的記錄。2008-04-04
大家好，本篇文章主要講的是全面盤點MySQL中的那些重要日志文件，感興趣的同學快來看一看吧，對你有用的話記得收藏，方便下次瀏覽2021-11-11
下面小編給大家介紹c++與mysql連接的思路詳解及遇到問題匯總，非常不錯，感興趣的朋友參考下吧2016-08-08
事務(Transaction)是并發(fā)控制的基本單位,所謂的事務呢,它是一個操作序列,這些操作要么都執(zhí)行,要么都不執(zhí)行,它是一個不可分割的工作單位,本文給大家詳細介紹了MySQL事務的ACID如何實現(xiàn),需要的朋友可以參考下2023-10-10
Mysql用戶創(chuàng)建以及權限賦予操作的實現(xiàn)
在MySQL中,創(chuàng)建新用戶并為其授予權限是一項常見的操作,本文主要介紹了Mysql用戶創(chuàng)建以及權限賦予操作的實現(xiàn),具有一定的參考價值,感興趣的可以了解一下2023-10-10
這篇文章主要介紹了MySQL中的RAND()函數(shù)使用詳解,是MySQL入門學習中的基礎知識,需要的朋友可以參考下2015-05-05