MySQL 優化核心：資料型別選擇、定長與變長、NULL 處理、ENUM、BLOB 隔離與 PROCEDURE ANALYSE 實戰

資料型別是效能優化的地基。本文系統講解「越小越好、定長優於變長、拒絕 NULL、善用 ENUM、隔離 BLOB、ANALYSE 輔助決策」六大原則。

1. 越小越好

• 整數優先 TINYINT/SMALLINT/MEDIUMINT，滿足範圍即可，INT 佔 4 bytes，MEDIUMINT 3 bytes 省 25%。
• 定長 CHAR vs 變長 VARCHAR：CHAR 適合長度固定、頻繁更新的欄位 (避免碎片)；VARCHAR 適合長度差異大、讀多寫少。
• 浮點數：FLOAT 4B、DOUBLE 8B、DECIMAL 精確計算 (金融必用)。

2. 定長表 (Fixed-Row Format) 效能優勢

• MyISAM 滿足「無變長欄位、無 NULL、無 BLOB/TEXT」即採用定長行格式。
• 定長行：單行大小固定，OFFSET = 行號 × 行長 直接定位，讀取速度比變長行快 20~30%。
• 代價：空間浪費 (如 CHAR(100) 實存 5 字元仍佔 100B)。權衡：空間換時間，記憶體/磁碟足夠時優先定長。

3. 拒絕預設 NULL

• NOT NULL 節省每欄 1 bit 空間，消除 IS NULL/IS NOT NULL 判斷開銷，簡化查詢優化器統計。
• 業務真正需要「無值」語義時才用 NULL，並建立函數索引 或分區優化。

4. 善用 ENUM 替代字串

• 狀態碼、類別、性別等有限離散值，ENUM('A','B','C') 內部存儲為 1/2/3 整數，比較極快、佔 1~2 bytes。
• ALTER TABLE t MODIFY col ENUM(...) 可平滑遷移。

5. BLOB/TEXT 類型深度解析

優點

• 單次查詢取回大物件，減少應用層多次往返。
• 儲存非結構化/半結構化資料 (JSON、XML、圖片二進位)。

弊端

• 大量 DELETE/UPDATE 產生嚴重碎片 (留下大洞，後續難填補)。
• SELECT * 會無謂拖回巨大欄位，佔用網路/記憶體。

最佳實踐

1. 定期 OPTIMIZE TABLE 整理碎片 (含 BLOB 表必做)。
2. 查詢時禁止 SELECT *，改列出所需欄位；BLOB 欄位單獨查詢。
3. 建立「標識欄位」(如 md5_hash) 索引，先查標識再取 BLOB。
4. 多列索引技巧： 對 BLOB 計算 MD5() 或 CRC32() 存入獨立 CHAR(32) 欄位建索引，精確匹配極快。
5. 垂直拆表： 將 BLOB 移至獨立副表，主表保持定長行格式，JOIN 時才關聯。

6. PROCEDURE ANALYSE() 智能建議

SELECT * FROM tbl_name PROCEDURE ANALYSE([max_elements[, max_memory]]);

• max_elements (預設 256)：判斷是否建議 ENUM 的最大離散值數。
• max_memory (預設 8192)：分析時每欄最大記憶體。
• 輸出含 Optimal_fieldtype 欄，直接給出建議型別 (含 ENUM 定義)。

-- 實戰
SELECT * FROM students PROCEDURE ANALYSE(16, 256);
-- 若 gender 僅 'M','F' → 建議 ENUM('M','F')

總結清單

1. 型別最小化
2. 高頻更新表轉定長
3. 全域 NOT NULL
4. 離散值用 ENUM
5. BLOB 垂直拆表 + 標識索引
6. 定期 OPTIMIZE TABLE
7. 上線前跑 PROCEDURE ANALYSE() 驗收

型別選對，索引才有效，查詢才快，儲存才省。