MySQL 欄位類型選擇與查詢效率：從存儲優化到索引設計

選型五大原則

1. 越小越好：節省磁碟、記憶體、CPU 快取；索引更窄、命中率更高
2. 匹配引擎特性：
   MyISAM/ISAM：固定長 CHAR 最快
   InnoDB：VARCHAR 省空間減 I/O
   BDB：定長/變長差異小
3. 盡量 NOT NULL：省 1 bit 空間、避免 NULL 判斷開銷、索引更簡單
4. 枚舉值用 ENUM：內部存整數、比較極快、適用狀態碼、性別、類別等
5. 用 PROCEDURE ANALYSE() 讓數據說話
   SELECT * FROM t PROCEDURE ANALYSE(16,256); — 取值 ≤16 且長度 ≤256 才建議 ENUM

碎片整理

• 變長欄位 (VARCHAR/BLOB/TEXT) 產生碎片 → 定期 OPTIMIZE TABLE (僅 MyISAM 有效)
• 通用整理法：mysqldump → DROP TABLE → 重建 → 導入

BLOB/TEXT 大物件處理

• 非結構化、變化大 → 放 BLOB，定期 OPTIMIZE
• 查詢大 BLOB 必經散列值過濾：
  新增 hash_val CHAR(32) 存 MD5(blob_col)，索引該欄位
  SELECT * FROM t WHERE hash_val=MD5(?) AND blob_col=?
• 垂直拆分：將 BLOB 移至獨立表，主表變全定長 → 讀取主表無碎片、SELECT * 不傳大物件

人工索引欄位 (散列索引)

對超長字串/BLOB 精確匹配：

1. 計算 CRC32()/MD5()/SHA1() 存入 INT UNSIGNED/CHAR(32)/CHAR(40)
2. 建立索引查詢：WHERE hash_col=CRC32('long_string')
3. ⚠ 僅適用 =、IN，不適用範圍查詢 (<、>、BETWEEN)

快速參考表