MySQL 動態表 (Dynamic Format) 特性深度解析：碎片管理、行長度計算、崩潰恢復難點

什麼是動態表？

MyISAM 表滿足任一條件即為動態格式：

• 包含任意可變長度列 (VARCHAR、BLOB、TEXT)
• 建表指定 ROW_FORMAT=DYNAMIC

對比：靜態表 (Fixed/Static) — 全定長列 (CHAR、INT 等) 且未指定 DYNAMIC。

動態表存儲結構特徵

1. 行頭部位圖

• 每行開頭有一個**位圖 (bitmap)**，標記哪些列為空值
• 字符串列：空字串 '' → 標記為空，不存儲內容
• 數字列：值為 0 → 標記為空，不存儲
• ⚠️ **NULL 值不在位圖中**，由單獨的 NULL 位圖管理

2. 非空值存儲格式

[長度字節] + [實際內容]
-- 例: VARCHAR(50) 存 'abc' → [3] + ['a','b','c']

3. 碎片化機制

• UPDATE 導致行變長 → 原位置不足 → **行分裂成多個片段**
• 片段間通過**指鏈**連接
• 新片段至少 20 字節開銷
• 後續擴展可能在同一鏈接內，否則建立新鏈接

動態表優缺點總結

碎片整理與維護

-- 方法一: OPTIMIZE TABLE (鎖表，重建表，整理碎片、重置 AUTO_INCREMENT)
OPTIMIZE TABLE `dynamic_table`;

-- 方法二: myisamchk -r (需停庫，速度更快)
myisamchk -r /path/to/table.MYI

-- 方法三: ALTER TABLE ENGINE=MyISAM (重建表，5.6+ 支援 ONLINE)
ALTER TABLE `dynamic_table` ENGINE=MyISAM;

-- 監控碎片
SELECT TABLE_NAME, Data_free 
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA='db' AND Data_free > 0;

動態記錄預期行長度計算公式

行長度 = 3                                    -- 基礎開銷
       + (列數 + 7) / 8                       -- 空值位圖 (位圖字節數)
       + 字符列數                             -- 每字符列 1 字節長度前綴
       + 數值列打包大小                       -- TINYINT=1, SMALLINT=2, MEDIUMINT=3, INT=4, BIGINT=8
       + 字符串實際長度總和                   -- 非空字符串內容長度
       + (NULL列數 + 7) / 8                   -- NULL位圖

每個片段鏈接額外開銷: 6 字節
每個新鏈接最小 20 字節

實例驗證

CREATE TABLE t (
  a VARCHAR(10),  -- 字符列
  b INT,          -- 數值列 4 字節
  c CHAR(5),      -- 定長字符列 (長度<4 視為靜態? 實際 CHAR 視為固定長度)
  d TEXT          -- 字符列 (動態)
) ROW_FORMAT=DYNAMIC;

-- 插入: a='hi', b=100, c='abc', d='long text...'
-- 行長度 ≈ 3 + (4+7)/8 + 2 + 4 + (2+9) + (0+7)/8 + 6 (若分片)
-- = 3 + 1 + 2 + 4 + 11 + 1 = 22 字節 (不含分片開銷)

崩潰恢復難點分析

1. 動態表行可能分佈在**多個不連續片段**
2. 片段間通過**指鏈**連接
3. 崩潰時若**鏈接指針損壞/丟失** → 行數據不完整、無法重組
4. 靜態表固定長度、連續存儲，恢復只需按位移讀取，極其簡單可靠

結論：關鍵業務表若極其重視崩潰恢復可靠性，可考慮將可變長列拆分到子表 (靜態主表 + 動態子表)，或遷移至 InnoDB (Redo/Undo Log 保證崩潰安全)。

統計信息獲取

# myisamchk 詳細統計
myisamchk -ei /path/to/table.MYI
# 輸出包含: 記錄數、平均行長、碎片數、鏈接數、空間利用率等

# 查看鏈接數 (碎片指標)
myisamchk -ed /path/to/table.MYI

最佳實踐建議

1. 頻繁 UPDATE 的可變長列拆表：核心定長列留主表，可變長列移子表，避免主表碎片化
2. 定期整理碎片：低峰期 OPTIMIZE TABLE 或 myisamchk -r
3. 監控 Data_free：information_schema.TABLES 定期巡檢
4. 關鍵表遷移 InnoDB：獲得崩潰安全、行鎖、MVCC、自適應哈希等現代特性
5. 避免大量 NULL 列：NULL 位圖開銷 + 存儲開銷，用預設值或空字串替代

動態表 vs 靜態表 vs InnoDB 對比

動態表是 MyISAM 時代為了節省空間支援可變長列的妥協產物。**碎片化、崩潰脆弱、維護成本高** 是其固有代價。現代架構下，**InnoDB 動態行格式 (ROW_FORMAT=DYNAMIC/COMPRESSED) 以 Redo/Undo Log 解決崩潰安全、Change Buffer 緩解碎片、行鎖+MVCC 實現高並發**，是動態表的最佳繼承者與替代者。