MySQL 效能優化核心：選擇合適的資料行類型

前言

若資料表結構設計不良或索引規劃不當，查詢效能將大打折扣。優化表結構與給予適當索引，能讓查詢效能提升數個數量級——資料量越大，索引價值越明顯。提升效能需從三面著手：架構設計、索引設計、查詢效能評估。本文專注於資料行類型選擇，索引設計將於下一章詳述。

選擇資料類型的三大原則

1. 越小越好

使用能滿足需求的最小資料類型。較小的類型佔用較少磁碟空間、記憶體與 CPU 快取，處理時所需 CPU 週期也較少。

2. 越簡單越好

整數運算成本低於字串，因字符集與排序規則使字串比較更複雜。
• 內建類型（如 TIMESTAMP、DATE）優於字串儲存日期時間。
• IP 位址宜用整數儲存（配合 INET_ATON()/INET_NTOA()）。

3. 盡量避免 NULL

若計劃對欄位建立索引，應盡可能定義為 NOT NULL 並給予預設值（如空字串、0 或特殊字串）。原因如下：
(1) MySQL 難以優化 NULL 欄位，會使索引、索引統計與值比較變複雜。
(2) NULL 需額外儲存空間，且在內部需特殊處理。
(3) 索引時每筆記錄需多一個位元組，導致 MyISAM 固定大小索引變為可變大小。

決定資料類型的兩步驟

1. 大致決定類型：判斷是數值、字串、時間等，通常直觀易見。
2. 確定特定類型：許多類型可儲存同類資料，但儲存範圍、精度與物理空間不同。例如 DATETIME 與 TIMESTAMP 均可儲存日期時間，但 TIMESTAMP 僅佔一半空間（4 位元組），支援時區，並具自動更新特性。

常見資料類型深度解析

整數類型

• UNSIGNED 禁止負數，正數上限約增一倍。
• 顯示寬度（如 INT(11)）僅影響客戶端顯示，不影響儲存與計算。

實數類型

• FLOAT（4 位元組）、DOUBLE（8 位元組）：支援標準浮點運算，近似計算，速度較快（CPU 直接運算）。
• DECIMAL：精確儲存，計算時轉為 DOUBLE，CPU 不直接支援。僅在需精確計算（如金融資料）時使用。DECIMAL(18,9) 佔 9 位元組。

字串類型

VARCHAR

• 可變長度，僅佔用實際需要空間（例外：MyISAM ROW_FORMAT=FIXED 會固定長度）。
• 長度資訊：≤255 字元用 1 位元組，>255 用 2 位元組。
• 關鍵效能提示：VARCHAR(5) 與 VARCHAR(200) 儲存 'hello' 磁碟空間相同，但後者會分配更大固定記憶體塊（排序、臨時表時特別明顯），影響效能。

CHAR

• 固定長度，儲存時移除尾端空格。
• 適用場景：極短字串、長度近似固定（如 MD5 雜湊）、頻繁更新的欄位（固定長度不易產生碎片）。
• 單位元組字符集下 CHAR(1) 佔 1 位元組，VARCHAR(1) 佔 2 位元組（含長度資訊）。

TEXT / BLOB

• 大量資料儲存，InnoDB 大值時使用「外部」儲存區。
• 排序僅按 max_sort_length 指定前綴位元組，非完整長度。

ENUM

• 最多 65,535 個不同字串，以 1~2 位元組緊湊儲存，內部轉為整數表示位置。
• .frm 保留查找表映射整數與字串。
• 修改枚舉值需 ALTER TABLE。
• ORDER BY 依整數值排序，非字串順序。

日期時間類型

• DATETIME：範圍大，格式 YYYYMMDDHHMMSS，與時區無關，佔 8 位元組。
• TIMESTAMP：儲存自 1970-01-01 UTC 秒數，佔 4 位元組，支援時區，通常優先選用。

不建議用整數儲存 Unix 時間戳，處理不便且無額外優勢。

實戰經驗談

1. 選型不僅看儲存大小，還要考慮 MySQL 如何計算與比較（如 ENUM/SET 內部存整數但比較時轉字串）。
2. 關聯表欄位類型必須精確匹配，含 UNSIGNED 等屬性。
3. 混合類型導致隱式轉換，引發效能問題或難察覺錯誤。
4. 最小類型要預留成長空間（如中國省份用 TINYINT 即可，比 INT 少 3 位元組）。
5. 整數通常是最佳選擇：速度快、支援 AUTO_INCREMENT。
6. 盡量避免字串作為欄位類型，佔空間大且比整數慢；MyISAM 預設壓縮字串索引，查找更慢。

總結

合理選擇資料行類型是 MySQL 效能優化的基石。遵循「越小越好、越簡單越好、避免 NULL」原則，結合業務實際精確定型，能從根源上減少儲存開銷、加快計算比較、簡化索引維護，為後續索引優化與查詢調優打下堅實基礎。

參考文獻：《高效能 MySQL》