MySQL擴展VARCHAR長度遭遇問題匯總分析

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

這篇文章主要為大家介紹了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 是變長類型，實際存儲的內(nèi)容不固定，需要 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 值是 VARCHAR
CHARACTER_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，但是實際上很慢，內(nèi)部應該是重建索引了，后來將索引刪除后就秒改了。

針對這個場景，我們的解決方案是使用 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長度遭遇問題匯總分析的詳細內(nèi)容，更多關于MySQL擴展VARCHAR問題的資料請關注腳本之家其它相關文章！

您可能感興趣的文章:

MySql 5.7.14 解壓版安裝步驟詳解
本文給大家介紹MySql 5.7.14 解壓版安裝步驟詳解，本文介紹的非常詳細，具有參考借鑒價值，感興趣的朋友一起看下吧
2016-08-08
navicat如何利用sql語句查詢表所有字段的字段名、類型及長度
Navicat使用了極好的圖形用戶界面(GUI),可以讓你用一種安全和更為容易的方式快速和容易地創(chuàng)建、組織、存取和共享信息,下面這篇文章主要給大家介紹了關于navicat如何利用sql語句查詢表所有字段的字段名、類型及長度的相關資料,需要的朋友可以參考下
2023-05-05
MySQL實現(xiàn)批量插入以優(yōu)化性能的教程
這篇文章主要介紹了MySQL實現(xiàn)批量插入以優(yōu)化性能的教程,文中給出了運行時間來表示性能優(yōu)化后的對比,需要的朋友可以參考下
2015-04-04
mysql給一張表添加外鍵的4種方法
這篇文章主要給大家介紹了關于mysql給一張表添加外鍵的4種方法,MySQL是一種常用的關系型數(shù)據(jù)庫管理系統(tǒng),它支持外鍵約束以保證數(shù)據(jù)庫的數(shù)據(jù)完整性,需要的朋友可以參考下
2023-08-08
MySQL8.0 Redo Log 歸檔與禁用實戰(zhàn)指南
MySQL8.0新增的Redo Log歸檔與禁用功能,分別解決了備份時日志丟失問題和數(shù)據(jù)導入時 IO 開銷問題,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧
2026-04-04
mysql隔離級別詳解及示例
經(jīng)常提到數(shù)據(jù)庫的事務，那你知道數(shù)據(jù)庫還有事務隔離的說法嗎，本文主要介紹了mysql的四種隔離級別，具有一定的參考價值，感興趣的可以了解一下
2021-09-09
MySQL壓縮版zip安裝問題的解決方法
這篇文章主要給大家介紹了關于MySQL壓縮版zip安裝問題的解決方法，文中介紹的非常詳細，對大家的學習或者使用mysql具有一定的參考學習價值，需要的朋友們下面來一起學習學習吧
2019-03-03
MySQL安裝與創(chuàng)建用戶操作(新手入門指南)
這篇文章主要為大家介紹了MySQL安裝與創(chuàng)建用戶的使用講解是非常適合小白新手的入門學習，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪
2023-05-05
虛擬主機中phpMyAdmin的安裝配置方法
phpMyAdmin 是一套可以通過WEB來管理 MySQL-server 以及單一數(shù)據(jù)庫的 PHP 程序。對于一些虛擬空間的站點來說,應該是不可缺少的吧!!!
2010-06-06
MySQL觸發(fā)器Trigger加載及目前局限性
這篇文章主要為大家介紹了MySQL觸發(fā)器Trigger加載以及目前局限性詳解，有需要的朋友可以借鑒參考下，希望能夠有所幫助，祝大家多多進步，早日升職加薪
2023-05-05