欄位型別與查詢效能：MySQL 儲存引擎差異下的選型實戰

核心原則

1. 越小越好：節省磁碟、記憶體、CPU 快取，索引更緊湊
2. 引擎匹配：MyISAM 適合固定長（CHAR），InnoDB 適合可變長（VARCHAR）
3. NOT NULL：省 1 位元組 NULL 標誌、優化器統計更準
4. ENUM 替代字串：固定集合狀態、類型，內部整數比較極快

引擎差異實測

PROCEDURE ANALYSE() 自動建議

SELECT * FROM tbl PROCEDURE ANALYSE();
SELECT * FROM tbl PROCEDURE ANALYSE(16, 256);  -- 限制：>16 個值或 >255 位元組不建議 ENUM

輸出包含：建議型別、最小/最大值、NULL 比例、ENUM 值列表等。

碎片整理策略

• MyISAM：OPTIMIZE TABLE tbl; 重建表、索引、統計
• 通用：mysqldump --opt db tbl > dump.sql; mysql db < dump.sql 重建全表
• InnoDB：ALTER TABLE tbl ENGINE=InnoDB; 線上重建（5.6+ Online DDL）

BLOB/TEXT 大物件處理

1. 剝離到獨立表：CREATE TABLE tbl_blob (id BIGINT PRIMARY KEY, data LONGBLOB)，主表保留固定長欄位
2. 散列值索引：ALTER TABLE tbl ADD hash CHAR(32), ADD INDEX idx_hash (hash); 以 MD5(data) 查詢
3. 避免 SELECT * 拖回大物件，明確列出欄位

散列值查詢實作

-- 插入時
INSERT INTO tbl (data, hash) VALUES (?, MD5(?));

-- 查詢
SELECT * FROM tbl WHERE hash = MD5(?);  -- 精確匹配極快

-- 篩選後再取大物件
SELECT id FROM tbl WHERE hash = MD5(?);
-- 應用層再按 id 取 data

適用：精確匹配、去重、大物件比對。不適用：範圍查詢、排序。