您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 資料庫表設計與優化（一）：資料類型選擇原則、NULL 處理策略與效能影響深度解析

編輯：MySQL 資料庫相關知識

表設計優化的核心哲學

優化糟糕的表結構或索引，往往能帶來數量級的效能提升。高效能設計要求根據具體查詢模式精心規劃表結構與索引，這需要對各類查詢進行效能需求評估，因為任何變更都可能影響其他查詢路徑。優化本質是取捨：增加索引加速查詢但拖慢寫入；反正規化提升讀取但增加維護成本；匯總表優化聚合但引入資料同步複雜度。開發人員必須超越純代碼視角，理解資料庫系統原理，量化功能對硬體成本的影響，推動合理的需求取捨。

資料類型選擇三大黃金法則

1. 小而精：越小越好

在滿足正確性與未來擴展前提下，選擇佔用空間最小的類型
小類型優勢：磁碟空間少、記憶體/CPU 快取命中率高、CPU 處理週期少、網路傳輸快
實例：TINYINT (1B) vs INT (4B) vs BIGINT (8B)；DATE (3B) vs DATETIME (8B) vs VARCHAR(20) 存日期
警惕：不要低估資料範圍，後期修改類型極其痛苦（鎖表、重寫、應用改造）
決策建議：猶豫時選「滿足範圍的最小類型」；早期/簡單系統可優先靈活性

2. 簡單即強：簡單類型處理更快

整數運算 < 字串比較（字元集、排序規則增加複雜度）
典型應用：
- 日期時間：用 DATE/DATETIME/TIMESTAMP，禁用字串存儲
- IP 地址：用 INT UNSIGNED 存 INET_ATON() 轉換值，查詢用 INET_NTOA() 還原
- 布林值：用 TINYINT(1) 或 BIT(1)，不用 CHAR(1)/'Y'/'N'
- 枚舉/狀態：用 TINYINT + 應用層常數映射，或 ENUM（謹慎使用，修改結構需 DDL）

3. 拒絕 NULL：NOT NULL 是預設最佳實踐

NULL 影響面	具體危害	建議對策
查詢優化	優化器難以優化含 NULL 欄位的查詢、索引統計複雜、值比較邏輯特殊（`= NULL` 永假）	欄位定義為 `NOT NULL`，除非業務真正需要「未知」語義
索引儲存	每筆索引記錄額外 1 byte 標記 NULL；MyISAM 導致固定長度索引變為變長	需索引的欄位強制 NOT NULL
儲存空間	可為 NULL 欄位額外佔用位圖空間	預設值用 0、空字串、特殊標記值替代 NULL
CPU 處理	NULL 判斷、三值邏輯（TRUE/FALSE/UNKNOWN）增加分支預測失敗	應用層統一處理空值語義，資料庫層拒絕 NULL

實戰類型選擇決策樹

需要儲存什麼？
├─ 整數
│  ├─ 範圍 -128~127 / 0~255     → TINYINT (1B)
│  ├─ 範圍 -32K~32K / 0~65K     → SMALLINT (2B)
│  ├─ 範圍 -21億~21億 / 0~42億  → INT (4B)  【最常用】
│  └─ 範圍超過 INT               → BIGINT (8B)
├─ 定點數/金額/精確計算
│  └─ DECIMAL(p,s) / NUMERIC     → 精確儲存，指定精度與標度
├─ 浮點數/科學計算/近似值
│  ├─ 單精度 7 位有效數字        → FLOAT (4B)
│  └─ 雙精度 15 位有效數字       → DOUBLE (8B)
├─ 日期時間
│  ├─ 只要日期                    → DATE (3B)
│  ├─ 日期+時間（秒精度）         → DATETIME (8B) / TIMESTAMP (4B)
│  └─ 高精度（毫秒/微秒）         → DATETIME(3/6) (5.6.4+)
├─ 字串
│  ├─ 固定長度、極短 (1~10 字元)  → CHAR (按字元集編碼)
│  ├─ 可變長度、常規業務欄位      → VARCHAR(N) (1~2B 長度前綴 + 實際內容)
│  └─ 大文本/二進位               → TEXT / BLOB 系列（離頁儲存，慎用）
└─ 二進位/雜湊/UUID
   ├─ 固定 16 位元組 (UUID)       → BINARY(16) / CHAR(36)
   └─ 可變二進位                  → VARBINARY / BLOB

常見反模式與修正

反模式	問題	修正方案
用 `VARCHAR(20)` 存日期	無法日期運算、排序錯誤、索引效能差、格式不一致	`DATE` / `DATETIME`
用 `CHAR(15)` 存 IP	空間浪費、範圍查詢需字串轉換、索引大	`INT UNSIGNED` + `INET_ATON()/NTOA()`
所有欄位預設允許 NULL	上述所有 NULL 危害疊加	審視每欄位業務語義，預設 `NOT NULL`
`INT(11)` 誤以為限制長度	顯示寬度不限制範圍，`INT(1)` = `INT(20)`	理解 `(M)` 僅為顯示提示，不影響儲存
濫用 `TEXT` 存短字串	強制離頁儲存、無法完整索引、臨時表落盤	短字串用 `VARCHAR`，長度評估合理上限