101 個 MySQL 調優與優化技巧大全（硬件、配置、架構）

101 個 MySQL 調優與優化技巧大全（硬件、配置、架構）

MySQL 作為主流開源數據庫，性能調優涉及硬件、作業系統、配置參數、架構設計、SQL 編寫等多個維度。本文整理 101 條實戰技巧，分類匯總供參考。

一、服務器硬件與作業系統（1-24）

1. 內存夠大：讓整個 InnoDB 數據文件載入內存，內存訪問比磁盤快數量級。
2. 避免 Swap：交換分區極慢，vm.swappiness=0 或關閉 Swap。
3. 電池備援 RAID 卡：掉電不丟寫緩存，RAID10 首選，避免 RAID5 寫懲罰。
4. 分離分區：OS、數據、臨時目錄、Binlog 物理分離，減少 I/O 爭用。
5. SAS/SSD 優於 SATA：隨機 I/O 能力強，小容量高轉速盤在 RAID 下更快。
6. XFS 文件系統：比 ext3/4 更適合大文件、高並發，避免雙緩衝問題。
7. I/O 調度器：Deadline 或 NOOP，避免 CFQ。
8. 64 位 OS：突破 4GB 內存限制。
9. 精簡服務：關閉無關進程，降低上下文切換。
10. hosts 文件：避免 DNS 查詢延遲。
11. ...（其餘硬件層技巧涵蓋 NUMA 綁定、大頁內存、網卡多隊列、NTP 同步等）

二、MySQL 配置參數（25-39）

25. innodb_flush_method=O_DIRECT：避免雙緩衝，配合 XFS/ext4。
26. innodb_buffer_pool_size：設為物理內存 70-80%，載熱數據。
27. innodb_log_file_size：適中（1-4GB），過大延長崩潰恢復時間。
28. innodb_flush_log_at_trx_commit=1：ACID 嚴格；若允許秒級丟失可設 0/2 提升寫入。
29. max_connections：按業務峰值設定，過大導致 OOM。
30. thread_cache_size：~16，減少線程創建開銷。
31. skip-name-resolve：禁用 DNS 反解。
32. query_cache_type=0：高寫入場景關閉 QC，避免鎖爭用。
33. tmp_table_size/max_heap_table_size：防止內存臨時表溢寫磁盤。
34. sort_buffer_size：按會話分配，勿設過大（如 256KB-2MB）。
35. key_buffer_size：MyISAM 專用，InnoDB 主看 buffer_pool。
36. ...（含 replication、GTID、group replication 相關參數）

三、Schema 設計優化（40-50）

40. 歸檔冷數據，縮小活躍表體積。
41. 精準索引：覆蓋索引、最左前綴、避免過度索引。
42. 壓縮 TEXT/BLOB 或移出主表。
43. 慎用觸發器、外鍵，改由應用層保證一致性。
44. 選擇最小足夠類型：TINYINT 代替 INT，CHAR 代替 VARCHAR（定長）。
45. 字符集優先 utf8mb4，避免 latin1 混用導致隱式轉換。
46. ...（含分區表、分庫分表、Online DDL 策略）

四、SQL 與查詢優化（51-70）

• EXPLAIN 分析執行計劃，關注 type、key、rows、Extra。
• 避免 SELECT *，減少網絡與 I/O。
• 優化 LIMIT 深分頁：延遲關聯或游標分頁。
• 使用 UNION ALL 代替 OR、IN 子查詢。
• 覆蓋索引實現 Using index，避免回表。
• ...

五、架構與運維（71-101）

• 主從複製、MHA/Orchestrator 故障切換。
• 讀寫分離、ProxySQL 路由。
• 備份策略：XtraBackup 物理熱備 + Binlog 增量。
• 監控：PMM、Grafana + Prometheus，關鍵指標告警。
• 慢查詢審計、pt-query-digest 定期分析。
• ...

提示：調優無銀彈，需根據具體業務負載、數據量級、硬件條件綜合評估，切忌盲目複製參數。