高效能 MySQL Schema 與數據類型優化精華筆記

高效能 MySQL Schema 與數據類型優化精華筆記

基於《高效能 MySQL》核心章節整理，聚焦邏輯/物理設計基石：選對類型、拒絕 NULL、善用緩存表與匯總表。

一、選擇優化的數據類型：三大鐵律

1. 更小通常更好：佔用更少磁碟、記憶體、CPU 快取、CPU 週期。
2. 簡單就好：整數運算比字串快（字符集/校對規則增加複雜度）；用內建類型存日期時間、用無號整數存 IP。
3. 盡量避免 NULL：查詢含 NULL 欄位難優化；索引統計/值比較更複雜；可為 NULL 的索引記錄額外佔 1 位元組（MyISAM 甚至導致固定長度索引變可變長）。
   例外：InnoDB 用位圖存 NULL，稀疏資料（多 NULL）空間效率高。

二、具體類型選擇指南

1. 整數類型

INT(1) 與 INT(20) 儲存計算完全相同，顯示寬度不影響範圍。

2. 實數類型

• DECIMAL：精確小數，金額必用；但佔用空間大、運算慢。
• FLOAT (4B) / DOUBLE (8B)：近似值，科學計算適用。
• 最佳實踐：固定精度小數 × 固定倍數 → 整數存儲（如分存分，元存元），兼顧精度與效能。

3. 字串類型

字符數 ≠ 位元組數：CHAR(1) UTF-8 佔 1-3 位元組；VARCHAR(1) 需 2 位元組（長度前綴+數據）。

4. 日期時間類型

5. 位數據類型

• BIT(N)：最多 64 位，實際較少用。
• SET：多布林值合併存儲的替代方案。

6. 標識符（主鍵/外鍵）選型

• 優先整數類型，滿足範圍並預留增長空間下選最小。
• 避免字串類型（佔空間、比較慢）。
• UUID 儲存：移除 - 或用 UNHEX() 轉 BINARY(16)，讀取用 HEX() 還原。

7. 特殊類型：IPv4 地址

• 本質是 32 位無號整數，非字串。
• 用 INET_ATON() 存、INET_NTOA() 取，佔 4 位元組 vs VARCHAR(15) 15 位元組。

三、緩存表與匯總表

• 緩存表：儲存可從其他表輕易衍生的資料（如物化視圖）。
• 匯總表：定期聚合統計（如每小時統計過去 24 小時訊息數），替代即時計數器，大幅降低寫入壓力。
• 適用：資料量大、即時性要求可延遲、統計維度固定的場景。