MySQL SQL 優化 30 條鐵律：從全表掃描到索引設計、查詢重寫與架構調優

1. 避免全表掃描：WHERE/ORDER BY 欄位建立索引
2. 拒絕 != / <>：導致索引失效，改用 UNION 或 NOT IN (謹慎)
3. 避免 NULL 判斷：IS NULL 失效索引，設定 DEFAULT 0 改用 = 0
4. 拒絕 OR 連接條件：改用 UNION ALL 分開查詢合併
5. 慎用 LIKE '%abc%'：前導萬用字元無法用索引，改全文索引或倒排
6. 慎用 IN / NOT IN：連續值用 BETWEEN，大集合用 EXISTS/JOIN
7. 參數化查詢導致全掃：優化器編譯時未知值，加 FORCE INDEX 或重寫
8. WHERE 避免欄位運算：num/2=100 → num=200，保持索引列乾淨
9. WHERE 避免欄位函數：substring(name,1,3)='abc' → name LIKE 'abc%'
10. 不在 = 左側做運算：同 (8)(9)，系統無法正確使用索引
11. 複合索引遵循最左前綴：條件必須包含索引第一列，順序一致
12. 拒絕無意義查詢：WHERE 1=0 建空表 → 用 CREATE TABLE ... LIKE
13. EXISTS 替代 IN：EXISTS (SELECT 1 FROM b WHERE b.id=a.id) 通常更優
14. 索引非越多越好：單表 ≤ 6 個，評估寫入開銷、選擇性、維護成本
15. 低選擇性索引無效：性別/狀態類基數極低，優化器會放棄索引
16. 避免更新聚簇索引鍵：導致頁分裂/重排，極大開銷，慎設為 PK
17. 優先數字型別：比較/連接只需一次 CPU 指令，字串需逐字元
18. VARCHAR 優於 CHAR：節省空間、較小範圍掃描更快
19. 禁用 SELECT *：明確列出欄位，減少網路/IO/記憶體
20. 表變數替代臨時表：輕量、自動清理，大數據注意僅有 PK 索引
21. 減少臨時表建刪頻率：降低系統表鎖定競爭
22. 臨時表適度使用：重複引用大表集合時有效，一次性用導出表
23. 大量導入用 SELECT INTO：避免大量 Log，小量用 CREATE+INSERT 緩和系統表
24. 存儲過程結尾顯式清理臨時表：TRUNCATE → DROP，避免長鎖定
25. 避免游標：>10k 行必須改寫集合操作
26. 優先集合思維：JOIN/EXISTS/窗口函數替代逐行處理
27. 小結果集可用 FAST_FORWARD 游標：引用多表時有時優於集合
28. 存儲過程/觸發器首尾 SET NOCOUNT ON/OFF：減少 DONE_IN_PROC 網路封包
29. 避免大結果集直傳客戶端：分頁、流式處理、評估需求合理性
30. 拆分大事務：縮短鎖持有時間，提升併發吞吐

實戰檢查清單

• EXPLAIN 每條核心 SQL，關注 type (目標 ref/range/eq_ref)、rows、Extra (Using index/where/filesort)
• SHOW INDEX FROM tbl 檢查基數 Cardinality
• 慢查詢日誌 long_query_time=0 全量分析 (pt-query-digest)
• 定期 ANALYZE TABLE 更新統計資訊