MySQL 資料型別優化實戰：讓查詢跑得更快

核心原則：越小越好

資料型別直接影響儲存空間、記憶體使用、磁碟 I/O 與索引效能。選擇「剛好夠用」的最小型別，是效能調優的第一步。

1. 整數型別選擇指南

實戰建議：若最大值不超過 1600 萬，MEDIUMINT 比 INT 節省 25% 空間；狀態欄位用 TINYINT 而非 INT。

2. 字串型別：定長 vs 變長

決策原則：頻繁 UPDATE 的表優先用 CHAR；長度差異大、讀多寫少用 VARCHAR。

3. NOT NULL 的隱藏紅利

• 查詢優化器可簡化判斷邏輯，不需檢查 NULL 特例
• 索引效能更佳（MyISAM 每欄省 1 bit，InnoDB 更顯著）
• 儲存空間微幅減少

建議：除非業務真正需要「未知/不存在」語意，否則一律 NOT NULL DEFAULT '' 或 DEFAULT 0。

4. ENUM：低基數欄位的加速器

適用於固定值集合：狀態、性別、分類、優先級等。

status ENUM('pending','processing','completed','cancelled') NOT NULL DEFAULT 'pending'

• 內部以整數儲存（1 Byte 支援 255 個值），比較極快
• ORDER BY status 依定義順序排序，非字母順序
• 新增值需 ALTER TABLE，不如查詢表靈活

5. BLOB/TEXT 大物件型別策略

優點

• 單次查詢取回完整物件，避免多次關聯
• 儲存非結構化或結構頻變資料（JSON、日誌、文件）

缺點與風險

• 大量 DELETE/UPDATE 產生嚴重碎片，留下難以填補的「洞」
• SELECT * 會無差別拖回巨大欄位，佔用網路與記憶體
• 索引長度限制（InnoDB 768 Bytes/3072 Bytes），無法全文索引

最佳實踐

1. 拆分表：將 BLOB/TEXT 移至獨立副表，主表保持定長行格式
2. 延遲載入：列表查詢排除大欄位，詳情頁再單獨讀取
3. 定期 OPTIMIZE TABLE：重建表空間，消除碎片
4. 哈希索引替代：對 BLOB 欄位計算 MD5/SHA1 存入獨立欄位建索引，精確查詢時比對哈希值

6. 多欄索引與覆蓋索引

-- 查詢：WHERE a=1 AND b=2 ORDER BY c
-- 最佳索引
INDEX idx_abc (a, b, c)

• 最左前綴原則：索引欄位順序 = WHERE 條件順序 + ORDER BY 順序
• 覆蓋索引：SELECT a,b,c FROM t WHERE a=1 僅掃描索引，不回表
• 避免 SELECT * 破壞覆蓋索引效果

7. PROCEDURE ANALYSE()：讓 MySQL 幫你診斷

SELECT * FROM table_name PROCEDURE ANALYSE(16, 256);

• max_elements=16：建議 ENUM 最大值數
• max_memory=256：分析時每欄最大記憶體
• 輸出包含建議的最佳型別、最小/最大值、NULL 比例等

根據建議配合 ALTER TABLE 調整型別，可顯著縮減表體積。

總結：型優優化檢查清單

1. 所有整數欄位是否用最小足夠型別？
2. 高頻更新表是否用 CHAR 替代 VARCHAR？
3. 非業務必要欄位是否設 NOT NULL？
4. 低基數枚舉欄位是否改用 ENUM？
5. BLOB/TEXT 是否拆表或延遲載入？
6. 是否定期執行 ANALYSE 與 OPTIMIZE？