MySQL 百萬級數據查詢優化 28 條軍規：從全表掃描到索引覆蓋的完整避坑指南

面對百萬級甚至千萬級表，查詢性能優劣全取決於「能否避開全表掃描、用上索引、減少回表」。本文匯總 28 條經驗法則，涵蓋 WHERE 條件寫法、索引設計、類型選擇、臨時表、游標、存儲過程等，每條附反面教材與正確範例，可直接作為 Code Review 清單。

一、 WHERE 條件寫法（1-10）

1. 避免全表掃描：WHERE/ORDER BY 列必須有索引。
2. 拒絕 IS NULL 判斷：WHERE num IS NULL → 設置默認值 0，改 WHERE num = 0。
3. 拒絕 != / <>：導致索引失效，改用 IN/UNION ALL 或業務層過濾。
4. 拒絕 OR 連接條件：num=10 OR num=20 → UNION ALL 兩條索引查詢。
5. 慎用 IN / NOT IN：連續值用 BETWEEN；大列表改 JOIN 衍生表或臨時表。
6. 拒絕 LIKE '%abc%'：前導通配符不可用索引，改全文索引或 Elasticsearch。
7. 變量參數化導致全掃：WHERE num = @num 編譯時未知值 → 強制索引 FORCE INDEX 或應用層拼接常量。
8. 拒絕列上做表達式：WHERE num/2 = 100 → WHERE num = 200。
9. 拒絕列上做函數：WHERE SUBSTRING(name,1,3)='abc' → WHERE name LIKE 'abc%'；WHERE DATEDIFF(day, dt, '2024-01-01')=0 → WHERE dt >= '2024-01-01' AND dt < '2024-01-02'。
10. 拒絕 = 左邊運算：同第 8、9 條，保持列「裸露」在比較運算符右側。

二、 索引設計與使用（11-16）

11. 聯合索引最左前綴：INDEX(a,b,c) 必須 WHERE a=? 才能用上；WHERE b=? 無效。
12. 無意義查詢別寫：SELECT ... WHERE 1=0 建空表 → CREATE TABLE ... LIKE ...。
13. EXISTS 替代 IN：WHERE num IN (SELECT num FROM b) → WHERE EXISTS (SELECT 1 FROM b WHERE num=a.num)，半連接優化。
14. 索引非萬能：低選擇性列（如性別男/女各半）建索引無效，優化器會放棄。
15. 索引不可過多：單表 ≤ 6 個，寫入需維護所有索引，權衡讀寫比。
16. 慎更新聚簇索引列：主鍵值變更會導致整行物理移動，極高開銷；業務主鍵建議用無意義自增 ID。

三、 字段類型與 Schema 設計（17-19）

17. 優先數字類型：純數值業務用 INT/BIGINT/DECIMAL，字符串比較逐字符、佔空間大。
18. VARCHAR 替代 CHAR：變長節省空間、減少 I/O、緩存命中率高。
19. 禁用 SELECT *：顯式列出列，利於覆蓋索引、減少網絡傳輸、屏蔽 Schema 變更影響。

四、 臨時表與表變量（20-24）

20. 優先表變量：DECLARE @t TABLE(...)，僅主鍵索引，適合小結果集。
21. 避免頻繁建刪臨時表：消耗系統表資源，鎖爭用。
22. 適度用臨時表：重複引用大表子集時物化加速；一次性用導出表 SELECT ... FROM (SELECT ...) dt。
23. 大數據建臨時表用 SELECT INTO：SELECT * INTO #tmp FROM big_table 少日誌、快；小數據用 CREATE + INSERT 緩解系統表壓力。
24. 存儲過程末尾顯式清理：TRUNCATE #tmp; DROP TABLE #tmp; 避免長鎖系統表。

五、 游標與集合思維（25-28）

25. 拒絕游標：> 1 萬行必改寫；集合操作（JOIN/UNION/窗口函數）替代逐行邏輯。
26. 先找集合解：SQL 本質是集合運算，游標是退化的程序化補丁。
27. 小結果集可用 FAST_FORWARD 游標：只讀、單向、唯讀，開銷最低。
28. 存儲過程首尾 SET NOCOUNT ON/OFF：抑制 DONE_IN_PROC 網絡包，減少往返。

六、 一頁速查卡

✅ 索引列裸露、最左前綴、覆蓋索引、選擇性高
✅ 範圍查詢常量化、LIKE 只用後綴、NULL 改默認值
✅ 數字優先、VARCHAR優先、禁SELECT*、單表索引≤6
✅ 大表分批、影子表RENAME、DECIMAL存金額
✅ 集合思維、拒游標、NOCOUNT、慢查詢日誌常開

打印貼在工位，Code Review 逐條核對，百萬級查詢不再慌。