MySQL ALTER TABLE 深度解析：基本語法、執行機制與大表優化策略

ALTER TABLE 基本語法速查

MySQL ALTER TABLE 執行機制：Copy vs Inplace

表拷貝重建流程

1. 創建新表結構（目標結構）
2. INSERT INTO new_tbl SELECT * FROM old_tbl 全量拷貝數據
3. 重建所有索引
4. 原子重命名：RENAME TABLE old_tbl TO tmp, new_tbl TO old_tbl
5. 刪除舊表

特點：支持所有 ALTER 操作、需鎖表阻塞寫入、臨時佔用 2x 磁盤空間、耗時與數據量線性相關。

Inplace 原地操作 (Online DDL)

MySQL 5.6+ InnoDB 支持部分操作無需拷貝數據，僅修改元數據或局部重組：

指定算法：ALTER TABLE t ADD INDEX idx_name(name), ALGORITHM=INPLACE, LOCK=NONE;

優化核心：避免不必要的表重建

案例：修改默認值

-- 慢：觸發表重建 (MODIFY COLUMN)
ALTER TABLE user MODIFY COLUMN pwd VARCHAR(20) NOT NULL DEFAULT '666666';
-- SHOW STATUS 顯示 Handler_write = 行數

-- 快：僅修改 .frm 元數據 (ALTER COLUMN)
ALTER TABLE user ALTER COLUMN pwd SET DEFAULT '666666';
-- Handler_write = 0，毫秒級完成

原理：默認值存儲在 .frm 文件，ALTER COLUMN ... SET DEFAULT 僅更新元數據；MODIFY COLUMN 強制重建表以驗證現有數據符合新定義。

可通過 .frm 修改實現秒級變更的操作

1. 修改列默認值
2. 增刪 AUTO_INCREMENT 屬性
3. 增刪修改 ENUM/SET 枚舉值（刪除被引用值會導致查詢返回空串）

風險警告：直接操作 .frm 繞過 Server 層校驗，可能導致數據字典不一致、崩潰恢復失敗。僅建議在極端維護窗口、已全備、可接受風險時由專家操作。

.frm 手工修改流程（專家級）

-- 1. 備份數據目錄
-- 2. 創建目標結構臨時表
CREATE TABLE t_new LIKE t;
ALTER TABLE t_new MODIFY col VARCHAR(30) DEFAULT 'new';

-- 3. 全局讀鎖刷盤
FLUSH TABLES WITH READ LOCK;

-- 4. 系統層替換 .frm
mv t_new.frm t.frm

-- 5. 釋放鎖
UNLOCK TABLES;

-- 6. 驗證
INSERT INTO t(col) VALUES(DEFAULT);
SELECT * FROM t WHERE col='new';

-- 7. 清理
DROP TABLE t_new;

大表 ALTER TABLE 生產環境最佳實踐

1. 評估空間：預留 2.5x 表大小磁盤空間（含臨時表、索引、Binlog）
2. 低峰期執行：配合 pt-online-schema-change (Percona Toolkit) 實現真正無鎖變更
3. 分批次策略：大表拆分分區，逐分區 ALTER
4. 監控進度：SHOW PROCESSLIST 觀察 copy to tmp table 狀態；SHOW ENGINE INNODB STATUS 看刷盤進度
5. 調優參數：
   • innodb_online_alter_log_max_size：控制行日志緩衝區大小
   • innodb_sort_buffer_size：索引構建排序緩衝
   • tmpdir：指向高性能存儲（NVMe/內存盤）

常見坑點避雷

• CHANGE COLUMN 不指定類型會報錯，必須完整列定義
• 修改主鍵列類型必須重建表，不可 Inplace
• 外鍵約束列變更需先 DROP FOREIGN KEY 再 ALTER 再 ADD FOREIGN KEY
• 5.7 以前 ADD COLUMN ... DEFAULT 'x' 會重建表，8.0 即時生效
• ALGORITHM=COPY 強制拷貝模式，可用於規避 Inplace Bug