前言：DDL 是運維的「心臟手術」
ALTER TABLE 是 MySQL 結構變更的核心命令，涉及列增刪改、索引管理、主鍵調整、字符集遷移、表重命名等高危操作。本文系統梳理全操作命令、MySQL 版本演進下的鎖機制差異、ALGORITHM/LOCK 參數精準控制、大表變更實戰策略與回滾預案，助您在「零停機/極小停機」窗口期安全完成結構演進。
一、基礎表結構變更命令全集
1. 列操作
-- 刪除列
ALTER TABLE person DROP COLUMN birthday;
-- 新增列（指定位置）
ALTER TABLE person ADD COLUMN birthday DATETIME FIRST; -- 首列
ALTER TABLE person ADD COLUMN birthday DATETIME AFTER name; -- 指定列後
ALTER TABLE person ADD COLUMN birthday DATETIME; -- 尾部（默認）
-- 修改列類型/屬性（保留列名）
ALTER TABLE person MODIFY number BIGINT NOT NULL;
-- 修改列名+類型/屬性
ALTER TABLE person CHANGE number id BIGINT;
MODIFY：僅改類型/屬性，列名不變。CHANGE：可改列名，必須重新聲明完整類型定義。
2. 主鍵操作
-- 新增主鍵
ALTER TABLE person ADD PRIMARY KEY (id);
-- 刪除主鍵
ALTER TABLE person DROP PRIMARY KEY;
- InnoDB 表**必須有主鍵**，刪除 PK 會自動提升第一個非空唯一索引為 PK，否則隱式生成 6 字節 Row ID（性能差）。
3. 索引操作
-- 新增唯一索引
ALTER TABLE person ADD UNIQUE name_unique_index (name);
-- 新增普通索引
ALTER TABLE person ADD INDEX birthday_index (birthday);
-- 刪除索引
ALTER TABLE person DROP INDEX birthday_index;
ALTER TABLE person DROP INDEX name_unique_index;
4. 非唯一索引啟用/禁用（MyISAM 專用）
ALTER TABLE person DISABLE KEYS; -- 停止更新非唯一索引，加速批量導入
ALTER TABLE person ENABLE KEYS; -- 重建所有非唯一索引
- InnoDB **不支援**，二級索引隨數據實時維護。
- 適用場景：MyISAM 大批量
LOAD DATA/INSERT 前後。
5. 字符集遷移：三種語義截然不同
-- 1. 轉換表默認字符集 + 所有字符列字符集（數據實際轉碼）
ALTER TABLE person CONVERT TO CHARACTER SET utf8mb4;
-- 2. 僅修改單列字符集（數據實際轉碼）
ALTER TABLE person CHANGE name name VARCHAR(255) CHARACTER SET utf8mb4;
-- 3. 僅修改表默認字符集（僅影響後續新增列，現有列不變）
ALTER TABLE person DEFAULT CHARACTER SET utf8mb4;
避坑：CONVERT TO 會鎖表重建全表，大表極慢。建議：pt-online-schema-change / gh-ost 線上遷移。
6. 表重命名與跨庫移動
-- 同庫重命名
RENAME TABLE person TO person_other;
-- 跨庫移動（原子操作，元數據級，極快）
RENAME TABLE current_db.tbl_name TO other_db.tbl_name;
RENAME TABLE 獲得元數據鎖，阻塞進行中的 DML/DDL，但耗時極短（毫秒級）。- 跨庫移動**不複製數據**，僅修改數據字典指向，極適合分庫遷移。
二、MySQL 版本演進與 DDL 鎖機制：從「全鎖」到「線上」
| 版本/引擎 | 算法 | 鎖模式 | 說明 |
|---|
| ≤ 5.5 / MyISAM | COPY | 表級排他鎖 | 全表拷貝，全程阻塞讀寫 |
| 5.6+ InnoDB | INPLACE | 輕量元數據鎖 | 大多數操作線上執行，僅準備/提交階段短暫阻塞 |
| 8.0+ InnoDB | INSTANT | 無鎖/極輕量 | 新增列(尾部)、修改默認值、刪除虛擬列等**秒級完成，零拷貝** |
核心參數精準控制
ALTER TABLE tbl_name
ADD COLUMN col INT,
ALGORITHM=INPLACE, -- DEFAULT/INPLACE/INSTANT/COPY
LOCK=NONE; -- DEFAULT/NONE/SHARED/EXCLUSIVE
| ALGORITHM | 含義 | 失敗行為 |
|---|
DEFAULT | 優先 INPLACE，不支援則 COPY | 報錯 |
INPLACE | 強制線上算法 | 不支援報錯 |
COPY | 強制拷貝算法（老方式） | 報錯 |
INSTANT | 強制元數據級秒級算法 | 不支援報錯 |
| LOCK | 含義 | 適用場景 |
|---|
DEFAULT | 自動選擇最低鎖級別 | 常規 |
NONE | **禁止任何鎖**，不支援則報錯 | 核心業務零停機 |
SHARED | 允許共享讀鎖，阻塞寫 | 可接受短暫寫阻塞 |
EXCLUSIVE | 排他鎖，阻塞讀寫 | 維護窗口/非核心表 |
三、常見操作的算法/鎖支援矩陣
| 操作 | InnoDB 算法 | 最低鎖 | 備註 |
|---|
| 新增列 (尾部) | INSTANT (8.0+) | NONE | 元數據修改，極快 |
| 新增列 (首部/中間) | INPLACE | SHARED | 需重建表 |
| 刪除列 | INPLACE | SHARED | 需重建表 |
| 修改列類型 | INPLACE | SHARED | 兼容性轉換(如 INT→BIGINT) 支援 INSTANT |
| 修改列名 | INPLACE | NONE | 8.0+ 純元數據 |
| 新增/刪除二級索引 | INPLACE | NONE | 構建索引過程不阻塞 DML |
| 新增/刪除主鍵 | INPLACE | SHARED | 涉及聚簇索引重建 |
| 字符集轉換 | COPY | EXCLUSIVE | 全表重建，**極慢**，大表禁用 |
| 修改默認值 | INSTANT (8.0+) | NONE | 純元數據 |
| RENAME TABLE | 元數據 | 短暫 EXCLUSIVE | 毫秒級，跨庫同效 |
四、大表線上變更實戰策略
1. 原生 Online DDL (5.6+)
- 適用：新增/刪除二級索引、修改列名/默認值、兼容性類型變更、新增尾部列 (8.0)。
- 強制參數：
ALGORITHM=INPLACE, LOCK=NONE（不支援則報錯，不靜默降級 COPY）。
2. 工具級方案：不支援 INPLACE 的操作（字符集遷移、修改主鍵、刪除列等）
| 工具 | 原理 | 優點 | 缺點 |
|---|
pt-online-schema-change (Percona) | 觸發器+影子表同步 | 成熟穩定、支援複雜變更、可暫停/恢復 | 觸發器開銷、從庫延遲風險 |
gh-ost (GitHub) | Binlog 異步回放+影子表 | 無觸發器、主庫無寫入壓力、可控流量限流 | 需開啟 Row 格式 Binlog、主鍵必須存在 |
# gh-ost 典型用法
gh-ost --host=127.0.0.1 --user=root --password=xxx \
--database=db --table=person \
--alter="MODIFY COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT" \
--max-load=Threads_running=200 --critical-load=Threads_running=500 \
--chunk-size=1000 --execute
3. 變更前檢查清單
- 備份驗證：還原演練確保可回滾。
- 兼容性測試：應用代碼對新結構兼容（雙寫/版本兼容）。
- 從庫先行：從庫先變更，驗證無誤後主庫變更（利用複製延遲窗口）。
- 監控指標：
Threads_running、Innodb_row_lock_waits、複製延遲、磁碟空間（影子表佔用 1x）。
- 回滾預案：
pt-osc 支援 --drop-old-table/--no-drop-old-table 保留舊表便於回滾。
五、實戰案例：大表字符集 utf8 → utf8mb4 遷移
-- 方案：gh-ost 線上遷移（避免 COPY 算法鎖表數小時）
gh-ost --host=... --database=prod --table=big_table \
--alter="CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci" \
--max-load=Threads_running=100 --critical-load=Threads_running=300 \
--chunk-size=2000 --execute --panic-flag-file=/tmp/gh-ost.panic
- 分批遷移，每批 2000 行，根據主庫負載自動限流。
- 恐慌文件機制：觸發即暫停，人工介入後恢復。
- 完成後驗證：
SHOW CREATE TABLE、抽樣數據校對、應用煙霧測試。
結語
表結構變更是數據庫演進的必經之路。從理解 ALGORITHM/LOCK 的精準語義，到選擇原生 Online DDL 或 gh-ost/pt-osc 工具級方案；從「從庫先行」的架構級策略，到「恐慌文件」的戰術級熔斷——每一個決策都在為**業務連續性**與**數據安全**投票。願本文指南助您在大表變更的「手術台」上，穩健落刀、精準縫合、零事故交付。
