最近，業務反饋有個擴展 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 值是 VARCHAR
CHARACTER_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 審核和管理。支持主流的開源、商業、國產數據庫，為開發和運維提供流程自動化能力，提升上線效率，提高數據質量。

SQLE 獲取

類型	地址
版本庫	https://github.com/actiontech/sqle
文檔	https://actiontech.github.io/sqle-docs/
發佈信息	https://github.com/actiontech/sqle/releases
數據審核插件開發文檔	https://actiontech.github.io/sqle-docs/docs/dev-manual/plugins/howtouse

MySQL 擴展 VARCHAR 長度遭遇問題的總結