大數據量 MySQL 優化 30 條軍規：從全表掃描到索引設計的系統級避坑指南

查詢優化篇

1. 避免全表掃描：WHERE、ORDER BY 列必須建索引。
2. 拒絕 IS NULL 判斷：導致索引失效。設置默認值 0，改 WHERE num=0。
3. 拒絕 !=/<>：導致全表掃描，改用 IN/UNION ALL。
4. 拒絕 OR 連接條件：導致索引失效。改 UNION ALL 分開查再合併。
5. 慎用 IN/NOT IN：連續值用 BETWEEN，子查詢用 EXISTS。
6. 拒絕 LIKE '%abc%' 前導模糊：全表掃描。改全文索引或 LIKE 'abc%'。
7. 變量參數化導致全掃：優化器編譯時不知值。強制索引 FORCE INDEX(idx) 或應用層拼接 SQL。
8. 拒絕列上運算：WHERE num/2=100 → WHERE num=200；WHERE SUBSTR(name,1,3)='abc' → WHERE name LIKE 'abc%'。
9. 拒絕函數包裹列：WHERE YEAR(dt)=2023 → WHERE dt>='2023-01-01' AND dt<'2024-01-01'。
10. 複合索引最左前綴：INDEX(a,b,c) 必須用 a 才生效，順序要與索引一致。
11. 無意義查詢：SELECT ... WHERE 1=0 建空表 → 用 CREATE TABLE ... LIKE。
12. EXISTS 替代 IN：SELECT * FROM a WHERE EXISTS (SELECT 1 FROM b WHERE b.id=a.id)。
13. 低區分度索引無效：性別、狀態碼區分度 ~0.5，建索引優化器不走。
14. 索引不是越多越好：單表 ≤ 6 個，寫入需維護 B+Tree，定期評估低頻索引刪除。
15. 避免更新聚簇索引列：主鍵物理順序變更導致頁分裂，業務主鍵不可更新。

數據類型篇

15. 優先數字型：字符串比較逐字符，數字一次比較，Join 性能差數倍。
16. VARCHAR 代替 CHAR：變長省空間，小字段內存排序快。但定義長度不可慷慨 (見第 19 條)。
17. 拒絕 SELECT *：顯式列出列，減少網絡、內存、IO、覆蓋索引機會增加。

臨時表與游標篇

18. 表變量代替臨時表：僅主鍵索引，數據量大慎用。
19. 避免頻繁建刪臨時表：消耗系統表資源。
20. 一次性大數據導入臨時表：SELECT INTO 避免大量 Redo Log；小數據 CREATE TABLE + INSERT 緩解系統表壓力。
21. 存儲過程結束顯式清理：TRUNCATE 後 DROP，避免長時間鎖定系統表。
22. 拒絕游標：>1 萬行必改寫。優先集合操作 (JOIN/子查詢/窗口函數)。
23. 小結果集可用 FAST_FORWARD 游標：唯讀、單向、只讀一次，性能優於 WHILE 循環。
24. 集合思維優先：SQL 本質是集合運算，游標是行級迭代，能用集合解決的不用游標。

事務與並發篇

25. SET NOCOUNT ON：存儲過程/觸發器首尾加，減少 DONE_IN_PROC 網絡往返 (SQL Server 語法，MySQL 無此問題，但原理同：減少客戶端交互)。
26. 拒絕大事務：長鎖定、長持有 Undo、複製延遲，拆分小事務或異步處理。
27. 拒絕大結果集返回客戶端：分頁、流式處理、導出文件下載。

MySQL 專項補充 (原文未覆蓋但關鍵)

28. InnoDB 主鍵自增整型：UUID/雪花 ID 導致頁分裂、二級索引膨脹。
29. 大事務拆分利器：pt-archiver 分批歸檔、pt-online-schema-change 在線 DDL。
30. 慢查詢常態化：long_query_time=1、log_queries_not_using_indexes=ON、pt-query-digest 定期分析。
31. 統計信息維護：ANALYZE TABLE 定期更新 Cardinality，優化器才能做對選擇。
32. 讀寫分離延遲監控：Seconds_Behind_Master 告警、pt-heartbeat 高精度監控。

總結：優化優先級金字塔

Level 1 (ROI 最高) : SQL 改寫 + 索引設計 (解決 80% 問題)
Level 2            : 架構調整 (讀寫分離、分庫分表、緩存層)
Level 3            : 參數調優 (innodb_buffer_pool_size, tmp_table_size 等)
Level 4            : 硬件升級 (SSD、更大內存、更多 CPU)
Level 5 (最後手段) : 分佈式中間件、異構同步、重寫業務

金句：優化不是調參數，是改 SQL、設計索引、懂業務、控架構。 參數只能榨乾最後 10% 性能，前 90% 在代碼和設計裡。