MySQL 資料表類型選擇與儲存格式優化詳解

前言

選擇適合的資料表儲存格式，能有效減少資料碎片、提升 I/O 效率，進而最大化資料庫效能。本文詳細比較 MySQL 早期版本（3.23.x）提供的四種表格式——靜態 MyISAM、動態 MyISAM、壓縮 MyISAM 與記憶體 HEAP 表，並說明各自的適用場景與維護要點。

一、四大表格式總覽

二、靜態（定長）表特點

• 預設格式：表無 VARCHAR、BLOB、TEXT 欄位時自動使用。
• 所有 CHAR、NUMERIC、DECIMAL 自動補齊至定義寬度。
• 極快：記錄位置 = 行號 × 固定行長，單次磁碟讀取可取得固定筆數記錄。
• 易快取、崩潰後易修復（記錄位置固定）。
• 缺點：通常比動態表佔更多磁碟空間。

三、動態表特點與碎片管理

• 含 VARCHAR、BLOB、TEXT 時自動採用。
• 每筆記錄前置位圖標示空值/零值，實際資料僅存非空內容。
• 記錄變大時會分裂成多個片段（碎片），導致效能下降。
• 維護指令：
  • OPTIMIZE TABLE 或 myisamchk -r 整理碎片
  • myisamchk -ei 查看統計資訊
  • myisamchk -ed 檢查鏈結（片段）數量
• 預期行長度公式：
  3 + (欄位數+7)/8 + 字元欄位數 + 數值欄位壓縮大小 + 字串長度 + (NULL欄位數+7)/8

四、壓縮表（myisampack）

• 由 myisampack 工具產生，唯讀、極度省空間。
• 支援多種壓縮：Huffman 編碼、前/後綴空白壓縮、數值範圍自動縮減型別、ENUM 化等。
• 標頭固定 1~3 位元組，存取開銷極小。
• 不支援 BLOB/TEXT；可用 myisamchk 解壓還原。

五、HEAP 表加速多表聯結實戰

將常用查找表複製到 HEAP 表，可大幅加速多表聯結。範例：

-- 建立暫存 HEAP 表，預先聯結 tab2、tab3
CREATE TEMPORARY TABLE tmp_heap TYPE=HEAP
SELECT tab2.a AS a2, tab3.a AS a3
FROM tab2, tab3
WHERE tab2.a = tab3.a AND tab2.c != 0;

-- 主查詢改為聯結暫存表
SELECT tab1.a, tmp_heap.a3 FROM tab1, tmp_heap WHERE tab1.a = tmp_heap.a2;
SELECT tab1.b, tmp_heap.a3 FROM tab1, tmp_heap WHERE tab1.a = tmp_heap.a2 AND ...;

此技巧將原本需重複掃描的大表聯結，轉為記憶體級的單表查找，效能提升數倍。

六、索引檔案大小估算

MyISAM 使用 B-Tree 索引，最壞情況（索引鍵按序插入）索引檔大小約為：
Σ (key_length + 4) × 0.67
字串索引會自動進行空白壓縮（尾部空白、前綴相同），有效縮減索引體積。

七、總結建議

1. 無變長欄位 → 優先靜態 MyISAM，效能最佳。
2. 有變長欄位 → 動態 MyISAM，定期 OPTIMIZE TABLE。
3. 唯讀歸檔資料 → 壓縮 MyISAM，極致省空間。
4. 臨時查找/中間結果 → HEAP 表，記憶體速度。
5. 大表頻繁聯結 → 考慮將小表做成 HEAP 暫存表，減少磁碟 I/O。