101 條 MySQL 調優實戰技巧：硬件、配置、架構、SQL 全維度優化清單

MySQL 性能優化是系統工程，涉及硬件選型、操作系統調優、實例配置、架構設計、SQL 改寫等多個維度。本文彙總 101 條經驗法則，按類別結構化呈現，助你建立完整優化知識體系。

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

存儲子系統

1. 內存容納全量 InnoDB 數據與索引（價值最高的單一投資）。
2. 極力避免 Swap：vm.swappiness=1 或 0，啟用大頁內存。
3. RAID 10 首選，拒絕 RAID 5（寫入懲罰與重建風險）。
4. OS、數據、臨時目錄、binlog/relay log 物理隔離盤。
5. SAS/NVMe SSD 替代 SATA；小容量高轉速盤組 RAID 優於大容量盤。
6. 帶電池備寫緩存的 RAID 卡（BBWC/FBWC），開啟 Write Back。
7. 考慮 PCIe 固態卡（如 Intel Optane）作為 redo log / temp 專用盤。

文件系統與內核

8. XFS 或 ext4（noatime,nodiratime 掛載），避免雙緩衝問題。
9. XFS 調優：logbufs=8,logbsize=256k，大內存機器增加 inode64。
10. I/O 調度器：NVMe 用 none，SSD 用 noop 或 deadline，禁用 cfq。
11. 64 位 OS 強制；清理無用套件與服務，減少上下文切換。
12. /etc/hosts 預解析主從 IP，消除 DNS 延遲。
13. 嚴禁 kill -9 mysqld，導致崩潰恢復時間不可控。
14. 專機專用：關閉圖形界面、郵件、打印等非必要服務。

二、MySQL 實例配置層（25-39）

InnoDB 核心參數

25. innodb_flush_method=O_DIRECT 避免雙緩衝（ext4/XFS 安全）。
26. innodb_buffer_pool_size = 物理內存 60~80%，單實例專機可至 80%+。
27. innodb_log_file_size 1~4GB，過大延長崩潰恢復，過小頻繁 checkpoint。
28. innodb_flush_log_at_trx_commit=1 保證 ACID；副庫可設 0/2 提升寫入。
29. innodb_file_per_table=ON，便於表級空間回收與傳輸。
30. innodb_io_capacity / _max 根據磁盤 IOPS 實測值設置（SSD 5000~20000）。
31. innodb_read_io_threads/write_io_threads = 8~16（多核 NVMe 可至 32）。
32. innodb_page_cleaners = write_io_threads（5.7+）。

連接與緩存

33. max_connections 按業務峰值 + 余量設置，配合連接池避免爆發式連接。
34. thread_cache_size = 32~64，減少線程創建銷毀開銷。
35. skip_name_resolve=ON，禁用 DNS 反解。
36. 查詢緩存：讀多寫少、數據變化低 才開啟（query_cache_type=DEMAND + SQL_CACHE），否則關閉（query_cache_size=0）。
37. tmp_table_size/max_heap_table_size = 64~256MB，防止臨時表落盤。
38. sort_buffer_size = 256KB~1MB（會話級，過大易 OOM）。
39. key_buffer_size 僅對 MyISAM 生效；InnoDB 依賴 buffer_pool。

三、架構與模式設計層（40-50）

40. 定期歸檔冷數據（分區表/分表/外部存儲），保持熱數據集輕量。
41. 索引策略：EXPLAIN 驗證覆蓋索引、最左前綴、避免重複/冗餘索引。
42. 字符集統一 utf8mb4，latin1 存儲更緊湊但不支援表情符。
43. 謹慎使用觸發器/外鍵/存儲過程，業務邏輯上移應用層。
44. 垂直拆分：大字段（TEXT/BLOB/JSON）獨立副表，主表只存核心列。
45. 水平分表：按時間/用戶 ID/租戶 ID 分片，中間件路由（ShardingSphere/ProxySQL/Vitess）。
46. 最小化數據類型：TINYINT/SMALLINT/MEDIUMINT 代替 INT/BIGINT。
47. 避免 SELECT *，明確列出所需列，利用覆蓋索引。
48. 冗余計算列（如 total_price = qty * unit_price）替代實時計算。
49. 外鍵約束在高併發下成為熱點鎖，建議應用層保證完整性。

四、SQL 編寫與執行計劃優化（51-101 精選）

51. 慢查詢日志 long_query_time=0.5 + log_queries_not_using_indexes，配合 pt-query-digest 分析。
52. EXPLAIN 關注 type（system/const/eq_ref/ref/range/index/ALL）、rows、Extra（Using index/temporary/filesort）。
53. 範圍查詢列放在複合索引最後；ORDER BY/GROUP BY 列順序與索引一致可避免 filesort。
54. 大偏移量分頁：延遲關聯 / 書簽分頁 / 覆蓋索引掃描。
55. IN (subquery) 改為 JOIN 或 EXISTS（MySQL 5.6+ 半連接優化較好）。
56. OR 條件拆分為 UNION ALL，各分支走各自索引。
57. 避免隱式轉換：WHERE varchar_col = 123 導致全表掃描，用 '123'。
58. 函數列索引失效：WHERE DATE(created_at) = '2024-01-01' → WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'。
59. 批量寫入：INSERT ... VALUES (),(),() / LOAD DATA INFILE，事務包裹提升 10x+ 吞吐。
60. 大事務拆分：長事務持有鎖時間長，阻塞 purge、備庫延遲，拆為小事務。
61. 樂觀鎖（version 列）替代悲觀鎖，減少行鎖衝突。
62. 只讀查詢走從庫：SET SESSION transaction_read_only=ON 或中間件路由。
63. 定期 ANALYZE TABLE 更新統計信息，防止執行計劃劣化。
64. 在線 DDL（5.6+）：ALGORITHM=INPLACE, LOCK=NONE，避免鎖表停機。
65. 備庫 slave_parallel_workers / slave_parallel_type=LOGICAL_CLOCK 並行回放，消除延遲。
66. 監控指標：QPS/TPS、Innodb_row_lock_waits、Threads_running、Buffer_pool_read_requests 命中率、備庫 Seconds_Behind_Master。

五、總結

101 條技巧非孤立清單，而是「硬件→OS→實例→架構→SQL」全鏈路體系。優化順序遵循 ROI 原則：先解決明顯瓶頸（慢查詢、鎖等待、磁盤 IO），再精細調參；變更必須經過壓測驗證，建立基線對比。持續觀測、定期複盤，才能讓 MySQL 長期穩定高效運行。