MySQL 語句優化 14 大原則：索引設計、查詢改寫與實測驗證

索引設計原則

1. 選對索引類型：
   範圍查詢（>, <, BETWEEN）、ORDER BY/GROUP BY 多 → 叢集索引
   多欄常一起查詢、各有重複值 → 覆合索引（前導欄為最高頻欄）
2. 索引非越多越好：每新增一索引，寫入都要維護，讀寫比低的表慎加。
3. 前綴索引節省空間：CREATE INDEX idx_name ON users(name(6))，只索引前 6 字元，適用前綴選擇性高的字串欄。
4. 索引建在 WHERE/JOIN/ORDER BY 條件欄，而非 SELECT 輸出欄。

查詢改寫鐵律

5. 海量查詢少用格式轉換（CAST、CONVERT、DATE_FORMAT 等）。
6. 任何欄位運算移至等號右側：
   WHERE F1/2=100 → WHERE F1=200
WHERE SUBSTRING(card,1,4)='5378' → WHERE card LIKE '5378%'
7. IN / OR 易失效，拆分為 UNION ALL（不產生大量重複值時）。
8. 用小型別替大型別：MEDIUMINT 代替 INT，節省空間與 I/O。
9. 欄位盡量 NOT NULL，NULL 需額外位元組標記，影響統計與索引。
10. 少用 VARCHAR/TEXT/BLOB，固定長度 CHAR 查詢更快。
11. 已知少量枚舉值用 ENUM，內部存儲為整數，極高效。

實測驗證：索引前後對比（37,365 筆會員資料）

結論：前綴匹配（LIKE '關鍵字%'）受益最大，寫 SQL 時優先考慮此模式。

多表查詢優化

• 索引建在 LEFT/RIGHT JOIN ... ON 條件欄 + WHERE 條件欄
• 多表查詢比單表更能體現索引優勢

索引權衡與監控

13. 寫入密集、讀取稀疏的表可不建索引，避免每次 INSERT 維護索引開銷。
14. 常用 EXPLAIN 追蹤：關注 type（目標 ref/range）、rows（掃描行數）、Extra（Using index、Using where、Using filesort）。