MySQL擴(kuò)展VARCHAR長(zhǎng)度遭遇問(wèn)題匯總分析

更新時(shí)間：2024年02月01日 09:30:40 作者：愛可生開源社區(qū)

這篇文章主要為大家介紹了MySQL擴(kuò)展VARCHAR長(zhǎng)度遭遇問(wèn)題匯總分析,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪

背景介紹

最近，業(yè)務(wù)反饋有個(gè)擴(kuò)展 VARCHAR 改表需求失敗多次，需要干預(yù)處理一下。

擴(kuò)容場(chǎng)景

經(jīng)過(guò)排查分析得出，這是由于改表系統(tǒng)解析改表需求得出錯(cuò)誤的改表方案導(dǎo)致，即這類改表可以滿足快速改表操作（直接使用 ALTER TABLE），理論上任務(wù)下發(fā)后能馬上改完，但是工單結(jié)果是執(zhí)行觸發(fā) 10 秒超時(shí)，最終工單失敗。

原則上，VARCHAR 類型的擴(kuò)展是可以滿足快速改表的，我們的改表工單針對(duì)這類需求也是支持的，但是實(shí)際結(jié)果與預(yù)期不符，這到底是工單系統(tǒng)的 Bug？還是 MySQL 的坑呢？

本文就來(lái)總結(jié)一下 擴(kuò)展 VARCHAR 長(zhǎng)度可能會(huì)遇到的一些問(wèn)題，以及我們給出的解決方案，僅供參考。

僅討論 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 官方文檔中關(guān)于 Online DDL 章節(jié)中的一部分?？梢钥吹疥P(guān)于 VARCHAR 類型的字段的擴(kuò)展是可以原地改表，且僅僅改了元數(shù)據(jù)，理論上敲完回車就執(zhí)行結(jié)束。

當(dāng)然針對(duì)這種場(chǎng)景，還是有一些條件的，直接貼原話：

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 是變長(zhǎng)類型，實(shí)際存儲(chǔ)的內(nèi)容不固定，需要 1 或者 2 個(gè)字節(jié)來(lái)表示實(shí)際長(zhǎng)度，所以修改前和修改后，這個(gè)字節(jié)數(shù)要求是一致。

有了這個(gè)技術(shù)基礎(chǔ)，我們的改表系統(tǒng)就針對(duì)這類需求做了優(yōu)化，可以支持直接使用 ALTER TABLE 進(jìn)行改表，如果是大表可以節(jié)省很多時(shí)間，提升效率，也因此遇到了很多問(wèn)題，才有了這篇文章。

問(wèn)題匯總

首先簡(jiǎn)單介紹一下我們的改表系統(tǒng)的處理邏輯，我們會(huì)根據(jù)業(yè)務(wù)的改表需求去選擇最優(yōu)的改表方案：

滿足快速改表就直接使用 ALTER TABLE 進(jìn)行操作。

比如，刪除索引，修改表名/列名，修改默認(rèn)值/注釋，擴(kuò)展 VARCHAR 長(zhǎng)度，小表添加唯一索引以及 8.0 快速加列等等。
不滿足快速改表就優(yōu)先選擇 gh-ost 進(jìn)行改表

binlog format 不為 ROW 則不能使用 gh-ost，添加唯一索引必須使用 gh-ost。
不滿足 gh-ost 都會(huì)選擇 pt-osc 進(jìn)行改表。

其中添加唯一索引會(huì)直接失敗。

那么問(wèn)題來(lái)了，我們是如何判斷業(yè)務(wù)改表需求是不是擴(kuò)展 VARCHAR？

其實(shí)思路也很簡(jiǎn)單，就是檢查改表前后的 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 值是 VARCHAR
CHARACTER_MAXIMUM_LENGTH 的值，要求改表后要大于等于改表前的值
CHARACTER_OCTET_LENGTH 的值，要求改表前后這個(gè)值要么是都小于等于 255，要么是都大于 255
除 DATA_TYPE/COLUMN_TYPE/CHARACTER_MAXIMUM_LENGTH/CHARACTER_OCTET_LENGTH 字段外的其余字段要求改表前后保持一致

問(wèn)題一：默認(rèn)值問(wèn)題

我們發(fā)現(xiàn)，如果還改了字段名、注釋、默認(rèn)值這種元數(shù)據(jù)信息，依舊是可以快速改表，于是乎就進(jìn)行了優(yōu)化，不再比較這三個(gè)屬性 COLUMN_NAME|COLUMN_COMMENT|COLUMN_DEFAULT。

關(guān)于默認(rèn)值，看起來(lái)有點(diǎn)復(fù)雜，最開始也是想跑偏了，認(rèn)為判斷 COLUMN_DEFAULT 的值就行，比較這個(gè)值前后要么都是 null，要么都不是 null。都不是 null 的情況下可以是任意值，比如可以用下面的邏輯判斷改表前后是一致即可。

if(COLUMN_DEFAULT is null ,null,"")

但是有個(gè)問(wèn)題，如果一個(gè)字段從 允許為 null 默認(rèn)值為 1 變成 不允許為null 默認(rèn)值也是 1，該值改表前后也是一致的，具體測(cè)試如下：

CREATE TABLE `tb_test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `rshost` varchar(20) DEFAULT '1' COMMENT '主機(jī)地址',
  `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)絡(luò)使用情況, 單位是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: 主機(jī)地址
   GENERATION_EXPRESSION: 
1 row in set (0.00 sec)
>alter table tb_test modify `rshost` varchar(30) not null DEFAULT '1' COMMENT '主機(jī)地址';
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: 主機(jī)地址
   GENERATION_EXPRESSION: 
1 row in set (0.00 sec)

可以看到 COLUMN_DEFAULT 這個(gè)列的值是非 null 且不變，按照上面的判斷邏輯會(huì)認(rèn)為可以快速改表，但是我們知道實(shí)際上這個(gè)需求是需要 copy 數(shù)據(jù)的。

其實(shí)，關(guān)于默認(rèn)值問(wèn)題使用 IS_NULLABLE 的值就可以完美解決, 如果是 null 到 not null 這個(gè)值會(huì)從 yes 變成 no；如果是 not null 到 null，這個(gè)值會(huì)從 no變成 yes。

所以最終解決方案僅比較 IS_NULLABLE 即可，只要改表前后一致就認(rèn)為默認(rèn)值這個(gè)屬性滿足快速改表。

在測(cè)試這個(gè)問(wèn)題的時(shí)候發(fā)現(xiàn)一個(gè)現(xiàn)象：not null 到 null 可以使用 inplace 算法，但是需要 copy 數(shù)據(jù)；null 到 not null 不能使用 inplace，請(qǐng)看下面的用例：

-- not null --> null可以使用inplace
>alter table tb_test modify `rshost` varchar(30) DEFAULT '1' COMMENT '主機(jī)地址' ,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 '主機(jī)地址' ,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.
>
-- 可以使用下面的操作查看改表進(jìn)度拷貝數(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)
-- 為了避免測(cè)試干擾，檢查events_stages_history表之前可以先清空，切記不要對(duì)線上環(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)

問(wèn)題二：索引字段問(wèn)題

過(guò)了一段時(shí)間又發(fā)現(xiàn)第二個(gè)問(wèn)題，部分工單會(huì)觸發(fā)執(zhí)行 10 秒超時(shí)失敗。

工單系統(tǒng)判斷用戶的改表需求，滿足直接使用 ALTER TABLE 進(jìn)行操作會(huì)有個(gè) 10 秒超時(shí)的兜底策略，來(lái)避免因?yàn)榻馕鲥e(cuò)誤導(dǎo)致方案選擇錯(cuò)誤最終影響主從延遲。

另外，也建議帶上 ALGORITHM=INPLACE, LOCK=NONE ，避免因?yàn)椴皇鞘褂?inplace 導(dǎo)致 DML 阻塞。

這個(gè)問(wèn)題排查了很久都沒什么眉目，反反復(fù)復(fù)的查閱文檔及測(cè)試，始終都認(rèn)為這個(gè)需求一定是滿足快速改表的方案。實(shí)在是想不明白到底是哪里的問(wèn)題，還一度認(rèn)為是 MySQL 的 Bug。

下面是一張 100w 記錄表的測(cè)試用例：

> 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 '主機(jī)地址',
  `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)絡(luò)使用情況, 單位是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 '主機(jī)地址';
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 '主機(jī)地址', 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 '主機(jī)地址', 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 '主機(jī)地址', ALGORITHM=INPLACE, LOCK=NONE;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0
>

可以看到 rshost 字段有一個(gè)索引，在擴(kuò)展字段的時(shí)候雖然支持 inplace，但是實(shí)際上很慢，內(nèi)部應(yīng)該是重建索引了，后來(lái)將索引刪除后就秒改了。

針對(duì)這個(gè)場(chǎng)景，我們的解決方案是使用 gh-ost/pt-osc 進(jìn)行改表，那么問(wèn)題來(lái)了，我們應(yīng)該怎么判斷目標(biāo)字段是否是被索引了呢？

請(qǐng)看下面的 SQL，information_schema.STATISTICS 記錄了一個(gè)表的所有索引字段信息，可以很方便的判斷某個(gè)字段是否被索引。

select * from information_schema.STATISTICS where table_schema = 'db' and table_name = 'table' and column_name = 'col';

其他問(wèn)題

這個(gè)問(wèn)題也是執(zhí)行 10 秒超時(shí)，也就是文章開頭提到的業(yè)務(wù)反饋的問(wèn)題，其實(shí)跟 問(wèn)題二 差不多同期，但在解決了 問(wèn)題二 后還是一直找不到原因及解決方案。

關(guān)于這個(gè)問(wèn)題甚至都沒法復(fù)現(xiàn)，不像 問(wèn)題二 可以方便復(fù)現(xiàn)，當(dāng)時(shí)在業(yè)務(wù)的線上庫(kù)做操作又能 100% 復(fù)現(xiàn)，但是將他們的表及數(shù)據(jù)單獨(dú)導(dǎo)出來(lái)放在測(cè)試環(huán)境就不行。

在業(yè)務(wù)庫(kù)上測(cè)試是選了一個(gè)從庫(kù)，不記錄 binlog 的方式（set sql_log_bin = 0）。雖然不建議這么做，但是實(shí)屬迫不得已，在測(cè)試環(huán)境復(fù)現(xiàn)不出來(lái)。

后來(lái)實(shí)在找不到原因，就跳過(guò)快速改表的方案使用改表工具進(jìn)行處理，后來(lái)這個(gè)事情就算不了了之了。直到前幾天業(yè)務(wù)突然找我，說(shuō)之前的那個(gè)表能快速改表了。我趕緊去查看了工單詳情，發(fā)現(xiàn)確實(shí)如業(yè)務(wù)所述，這回我就更加郁悶了，難不成是見鬼了？這玩意還自帶歇業(yè)窗口的嘛？

本著嚴(yán)謹(jǐn)?shù)膽B(tài)度，又去測(cè)了一下。確實(shí)是可以滿足快速改表了，但是原因還是找不到，這感覺真的很難受。

最后，靜下來(lái)認(rèn)真梳理了一下，發(fā)現(xiàn)了一些貓膩。下面是我的測(cè)試思路：

1. 將線上的表導(dǎo)出并導(dǎo)入到測(cè)試環(huán)境

因?yàn)楸肀旧砭蛶讉€(gè) G，不算大就使用了 mysqldump 進(jìn)行導(dǎo)出導(dǎo)入。這個(gè)操作并非 100% 復(fù)原線上的環(huán)境，有個(gè)隱藏的變量被修改了，那就是這個(gè)表被重建了，這個(gè)跟之前業(yè)務(wù)用改表工具進(jìn)行修改后的操作有點(diǎn)類似，所以就猜想，會(huì)不會(huì)是因?yàn)檫@個(gè)表本身存在空洞導(dǎo)致的呢。

最后通過(guò)拉歷史備份，還原了一個(gè)環(huán)境進(jìn)行了測(cè)試，果不其然不能快速改表。為了印證了想法，就去查了一下這個(gè)表的空洞。十分遺憾，這個(gè)表并沒有存在空洞（空洞只有幾 MB）。這回又郁悶了，還以為要破案了，但是不管怎么樣既然懷疑是重建表能解決，那就開搞。

2. 重建前的狀態(tài)

業(yè)務(wù)從 varchar(300) 擴(kuò)展到 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

活久見，還真是重建表后就能解決了！雖然很郁悶，終究是有一個(gè)解決方案了，后期我們決定對(duì)此做個(gè)優(yōu)化，將滿足快速改表的工單又觸發(fā)十秒超時(shí)的改為使用 gh-ost/pt-osc 重新執(zhí)行，以此避免業(yè)務(wù)反復(fù)提交工單，應(yīng)該能大大提升好感度。

這個(gè)問(wèn)題雖然知道解決方案，但是依舊不知道原因，我猜測(cè)可能是跟統(tǒng)計(jì)信息不準(zhǔn)確有關(guān)系（或者約束），要是有大佬知道原因，請(qǐng)告知一下。

總結(jié)

MySQL Online DDL 特性給 DBA 帶來(lái)了很多的便利，提升了工作效率，我們可以基于官方的理論作為指導(dǎo)去優(yōu)化我們的系統(tǒng)。但是實(shí)際情況是理論知識(shí)很簡(jiǎn)單，線上環(huán)境十分復(fù)雜，可能會(huì)遇到各種意料之外的事情，任何線上的操作都要給自己留好后路做好兜底，這是十分必要的。

我們的系統(tǒng)，如果沒有添加 10 秒超時(shí)的兜底，那勢(shì)必會(huì)因?yàn)榻馕鲥e(cuò)誤導(dǎo)致選了錯(cuò)誤的改表方案，然后導(dǎo)致從庫(kù)延遲，可能會(huì)影響線上業(yè)務(wù)，想想都有點(diǎn)心慌。

這里有個(gè)注意事項(xiàng)，針對(duì)執(zhí)行超時(shí)不能簡(jiǎn)單的使用 timeout 等屬性進(jìn)行控制，還需要添加檢查邏輯，要到數(shù)據(jù)庫(kù)里面去查一下任務(wù)是否真的已經(jīng)終止了。避免因?yàn)?timeout 異常導(dǎo)致終止信號(hào)沒有給到 MySQL，這種可能會(huì)引發(fā)一系列問(wèn)題，切記切記。

以上就是MySQL擴(kuò)展VARCHAR長(zhǎng)度遭遇問(wèn)題匯總分析的詳細(xì)內(nèi)容，更多關(guān)于MySQL擴(kuò)展VARCHAR問(wèn)題的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: