MySQL 效能優化 30+ 實戰技巧：索引、查詢、架構、維護全方位指南

MySQL 效能優化 30+ 實戰技巧：索引、查詢、架構、維護全方位指南

針對千萬級資料量查詢優化，從索引設計、SQL 改寫、架構調整到日常維護，系統整理 30 多條實戰優化原則。

一、查詢與索引優化 (1-15)

1. 避免全表掃描：WHERE、ORDER BY 欄位建立索引
2. 避免 NULL 判斷：IS NULL 導致索引失效，改用預設值 0 查詢 =0
3. 避免 != / <> 操作符：導致全表掃描
4. 避免 OR 連接條件：改用 UNION ALL 分開查詢合併
5. 慎用 IN / NOT IN：連續值用 BETWEEN 替代
6. 避免 LIKE '%關鍵字%'：前導萬用字元無法用索引，考慮全文索引
7. 參數化查詢導致全掃：編譯時未知值，改用 FORCE INDEX 或動態 SQL
8. 避免 WHERE 欄位運算：num/2=100 → num=200
9. 避免 WHERE 欄位函數：SUBSTRING(name,1,3)='abc' → name LIKE 'abc%'
10. 不在 = 左側運算：破壞索引使用
11. 複合索引最左前綴：必須包含第一欄位才生效
12. 避免無意義查詢：SELECT ... WHERE 1=0 改用 CREATE TABLE ... LIKE
13. EXISTS 替代 IN：EXISTS (SELECT 1 FROM b WHERE b.num=a.num)
14. 索引非越多越好：單表 ≤6 個，權衡 INSERT/UPDATE 開銷
15. 避免更新叢集索引鍵：導致資料重排，評估是否改為非叢集

二、資料型別與結構優化 (16-22)

16. 優先使用數值型別，字串比較逐字元，數值單次比較
17. 使用 VARCHAR 替代 CHAR：節省空間、縮小掃描範圍
18. 禁止 SELECT *：明確列出需要欄位
19. 適度使用表變數替代臨時表：注意索引限制 (僅主鍵)
20. 避免頻繁建刪臨時表：減少系統表負載
21. 大量資料匯入臨時表用 SELECT INTO，小量用 CREATE + INSERT
22. 程序結束顯式清理臨時表：TRUNCATE 後 DROP

三、游標與集合操作 (23-29)

23. 盡量避免游標：>1萬行必須改寫
24. 優先尋找集合解法 (SET-based) 而非逐行處理
25. 小結果集可用 FAST_FORWARD 游標
26. 存儲程序/觸發器開頭 SET NOCOUNT ON 減少網路往返
27. 避免大事務：拆分提升並發

四、索引設計進階 (30-33)

30. 大量重複值 + 範圍查詢 + ORDER BY/GROUP BY → 考慮叢集索引
31. 常同時存取多欄位且皆有重複值 → 組合索引
32. 組合索引首欄放最高頻查詢欄位，形成索引覆蓋
33. 定期 ANALYZE TABLE 更新統計資訊，修正執行計畫偏差

五、日常維護指令 (34-36)

34. ANALYZE TABLE tbl_name：分析關鍵字分布，鎖定讀取
35. CHECK TABLE tbl_name [QUICK|FAST|MEDIUM|EXTENDED|CHANGED]：檢查錯誤，更新統計
36. OPTIMIZE TABLE tbl_name：整理碎片、回收空間 (MyISAM/InnoDB/BDB)

注意：上述三指令執行期間鎖表，請在低峰期執行。

六、補充技巧

• 海量查詢減少格式轉換
• ORDER BY/GROUP BY 受益於索引
• 列運算移至等號右側
• IN/OR 產生工作表導致索引失效，拆分子句並保留索引欄位
• 滿足需求前提下用更小型別：MEDIUMINT 代替 INT
• 欄位預設 NOT NULL，NULL 明確指定
• 少用 VARCHAR/TEXT/BLOB
• 已知枚舉值少用 ENUM
• 合理分表分區提升存取速度