資料型別選擇直接決定磁碟 I/O、記憶體佔用、索引大小與查詢速度。本文系統闡述「越小越好」、「定長優於變長」、「NOT NULL 優於 NULL」、「ENUM 替代字串」四大核心原則，並深入分析 BLOB/TEXT 特性、碎片整理、多欄索引與 PROCEDURE ANALYSE() 智能建議，助你設計高效 Schema。

核心原則一：越小越好

• 磁碟讀取更少、記憶體佔用更低、CPU 快取命中率更高
• 整數型別選擇：TINYINT(1B) > SMALLINT(2B) > MEDIUMINT(3B) > INT(4B) > BIGINT(8B)
• 實例：用戶狀態用 TINYINT UNSIGNED 而非 VARCHAR(20)

核心原則二：定長優於變長（高頻更新表）

特性	CHAR / 定長	VARCHAR / 變長
儲存空間	固定，可能浪費	實際長度 + 1-2 位元組長度前綴
更新效能	極快（原地修改，無碎片）	可能導致行遷移、頁分裂、碎片
索引大小	固定	可變
適用場景	頻繁 UPDATE、長度固定（MD5、UUID、郵遞區號）	長度差異大、較少更新

建議：高頻寫入表優先用 CHAR、INT、DATE 等定長型別；可接受空間換時間。

核心原則三：宣告 NOT NULL

• 每欄省 1 bit（NULL 位圖）
• 查詢優化器可跳過 NULL 判斷，生成更優計畫
• 避免 WHERE col = NULL 寫錯成 WHERE col IS NULL 的邏輯 Bug
• 真正需要「無值」語義時再用 NULL

核心原則四：ENUM 替代固定集合字串

• 內部儲存為整數（1-2 位元組），比較/排序極快
• 適用：狀態碼、性別、支付方式、訂單階段等枚舉值
• 缺點：新增值需 ALTER TABLE、排序按定義順序而非字母序

BLOB/TEXT 型別深度解析

優點

• 單次查詢取回大型物件（圖片、文檔、JSON 文檔）
• 儲存結構多變、隨時間演進的半結構化資料

弊端與對策

網路傳輸大量 BLOB

問題	影響	對策
大量 DELETE/UPDATE 產生碎片	資料檔留下大洞，後續插入難填補	定期 OPTIMIZE TABLE；考慮歸檔策略
頻寬浪費、延遲高	查詢時 SELECT id, meta FROM t WHERE ... 先過濾，再單獨取 BLOB 無法直接建立完整索引（舊版）	全文搜尋/前綴匹配受限	建立前綴索引 INDEX (blob_col(255)) 或改用全文索引/外部搜尋引擎

必要準則

1. 易碎片表定期 OPTIMIZE：含可變長欄位、BLOB/TEXT、高頻刪改的表
2. 多欄索引/散列索引：對 BLOB 用 MD5(blob_col) 儲存雜湊值索引，精確查找用雜湊，範圍查詢無效
3. 將 BLOB 移至副表：主表轉為定長行格式，享受定長效能；副表存 BLOB，按需 JOIN

PROCEDURE ANALYSE() 智能型別建議

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

• max_elements（預設 256）：判斷是否建議 ENUM 的最大不同值數
• max_memory（預設 8192）：分析時每欄最大記憶體

範例

SELECT * FROM student PROCEDURE ANALYSE();
SELECT * FROM student PROCEDURE ANALYSE(16, 256);  -- 限制 ENUM 建議條件

輸出欄位 Optimal_fieldtype 給出建議型別（如 ENUM('a','b','c')、MEDIUMINT UNSIGNED NOT NULL），據此 ALTER TABLE 調整。

型別選擇決策樹（速查版）

1. 是否為數值？→ 整數用最小足夠的 TINYINT/SMALLINT/MEDIUMINT/INT/BIGINT；小數用 DECIMAL(p,s)（精確）或 FLOAT/DOUBLE（近似）
2. 是否固定長度字串？→ CHAR / BINARY
3. 是否有限枚舉值？→ ENUM / SET
4. 長度變動大？→ VARCHAR / VARBINARY
5. 超大物件？→ BLOB / TEXT（考慮副表隔離）
6. 時間？→ DATE/TIME/DATETIME(8B)/TIMESTAMP(4B, 時區感知)
7. 全加 NOT NULL，預設值用 DEFAULT

---

型別設計是 Schema 設計的地基。每次 CREATE TABLE 前走一遍決策樹，上線後跑 PROCEDURE ANALYSE() 驗證，能省下未來 80% 的調優成本。記住：磁碟便宜，但 I/O 昂貴；記憶體貴，但快取命中率無價。