索引與訪問路徑優化

1. 避免全表掃描： WHERE、ORDER BY、JOIN ON 涉及列優先建立索引
2. 拒絕 != / <>： 導致索引失效，改用 IN、UNION 或業務邏輯規避
3. 慎用 IS NULL： 設置默認值 0/''，改查 col = 0 走索引
4. OR 條件拆分： WHERE a=1 OR b=2 → UNION ALL 兩條索引查詢
5. 前綴模糊查詢： LIKE 'abc%' 走索引，LIKE '%abc%' 全掃 → 上全文索引
6. IN/NOT IN 謹慎： 連續值用 BETWEEN，大集合用 EXISTS/JOIN
7. 參數化查詢陷阱： 變量值編譯時未知 → 無法用索引 → FORCE INDEX 或動態 SQL
8. 列運算殺索引： WHERE col/2=100 → WHERE col=200；函數同理 SUBSTRING(name,1,3)='abc' → name LIKE 'abc%'
9. 復合索引最左前綴： 必須包含第一列，順序一致，否則索引失效
10. 無意義查詢： SELECT ... WHERE 1=0 建空表 → 用 CREATE TABLE LIKE 或 CREATE TABLE ... SELECT ... LIMIT 0

查詢重寫與邏輯優化

11. EXISTS 替代 IN： EXISTS (SELECT 1 FROM b WHERE b.id=a.id) 通常優於 IN (SELECT id FROM b)
12. 索引非萬能： 低選擇性列（性別、狀態）建索引無效，優化器會選全掃
13. 索引數量適度： 單表 ≤ 6 個，過多拖慢寫入（維護開銷），定期 ANALYZE TABLE 更新統計信息
14. 避免更新聚簇索引列： 主鍵值變更導致行物理移動，極高開銷
15. 優先數字類型： 整型比較單次 CPU 指令，字符串逐字符比較
16. VARCHAR 代替 CHAR： 變長節省空間，小字段查詢掃描更少頁
17. 禁用 SELECT *： 顯式列出列，減少網絡/IO/內存，利於覆蓋索引

臨時表與表變量策略

18. 表變量優於臨時表： 少量數據、無統計信息、無鎖升級、事務外可見
19. 慎建慎刪臨時表： 減少系統表鎖定，重用優於頻繁創建
20. 大數據用 SELECT INTO： 避免大量 Log，速度快；小數據 CREATE + INSERT 緩和系統表壓力
21. 顯式清理： 存儲過程結尾 TRUNCATE → DROP，避免長時間鎖定系統表

游標與集合操作

22. 能用集合別用游標： 集合操作（JOIN/UNION/窗口函數）並行度高、優化器友好
23. 小結果集 FAST_FORWARD 游標可接受： 僅前向只讀、需引用多表獲取詳情
24. 聚合邏輯優於游標循環： SUM/COUNT OVER() 替代逐行累加

事務與並發控制

25. SET NOCOUNT ON： 存儲過程/觸發器首尾包裹，減少 DONE_IN_PROC 網絡往返
26. 拒絕大結果集直輸客戶端： 分頁、流式處理、異步導出
27. 拆分大事務： 長事務阻塞鎖、膨脹 Undo、延遲 Purge，拆小提高並發
28. 死鎖預防： 統一加鎖順序、縮短事務、避免用戶交互在事務中
29. 隔離級別權衡： RR 可重複讀（默認）、RC 讀已提交（鎖少、需 Binlog ROW 格式）

工具輔助優化

• EXPLAIN / EXPLAIN ANALYZE (8.0+) 分析執行計劃
• SHOW PROFILE / performance_schema 定位耗時
• pt-query-digest 分析慢查詢日誌
• sys.schema_table_statistics 表級訪問統計