MySQL 效能調優 101 條建議：硬體、配置、架構、查詢與備份完整清單

MySQL 效能調優 101 條建議：硬體、配置、架構、查詢與備份完整清單

本文整理自實戰經驗與社區最佳實踐，涵蓋 101 條調優建議，分為五大類。部分建議針對特定環境，但核心思路通用。建議配合上一篇《MySQL 效能優化 100 條實戰建議》對照閱讀，形成完整知識體系。

一、伺服器硬體與作業系統調校

1. 足夠實體記憶體裝下整個 InnoDB 資料集
2. 不惜一切代價避免 Swap
3. 使用電池備援記憶體（BBWC）
4. 採用 RAID 10+，避免 RAID 5 校驗開銷
5. OS 與資料分區物理隔離
6. 臨時空間、Relay Log、資料三分離
7. 磁碟空間越大越快
8. 選用更快磁碟
9. SAS/SSD 替代 SATA
10. 小容量磁碟組 RAID 優於大容量單盤
11. 啟用 BBWC RAID 控制器
12. 拒絕軟體 RAID
13. 考慮 PCIe NVMe/固態 IO 卡（2GB/s+ 寫入）
14. Linux vm.swappiness=0
15. 掛載加 noatime,nodiratime
16. 優先 XFS，避免 ext3 雙重緩衝問題
17. 調整 XFS 日誌與緩衝參數
18. I/O 排程器用 NOOP/DEADLINE
19. 強制 64 位元 OS
20. 精簡系統服務與套件
21. /etc/hosts 預解析主機名
22. 嚴禁 kill -9 mysqld
23. 專機專用，減少背景進程爭搶

二、MySQL 核心參數配置

25. 寫入時用 innodb_flush_method=O_DIRECT 避免雙重緩衝
26. 避免 O_DIRECT 搭配 EXT3
27. innodb_buffer_pool_size 覆蓋熱資料集
28. innodb_log_file_size 不宜過大，加速崩潰恢復
29. 勿混用 innodb_thread_concurrency 與 thread_concurrency
30. max_connections 設定保守值
31. thread_cache_size ≥ 16
32. skip-name-resolve 跳過 DNS
33. 查詢快取視場景啟用（高寫入慎用）
34. 增大 tmp_table_size 防臨時表落盤
35. 增大 max_heap_table_size 防臨時表落盤
36. sort_buffer_size 不宜過大
37. 依 Key_read_requests/Key_reads 調 key_buffer_size
38. innodb_flush_log_at_trx_commit=0/2 提升寫入但犧牲持久性
39. 建立獨立測試環境驗證配置

三、Schema 設計與索引優化

40. 保持資料模型整潔
41. 歸檔冷資料
42. 高頻查詢欄位加索引
43. 避免過度索引
44. 壓縮 TEXT/BLOB
45. UTF8/UTF16 效能低於 latin1
46. 謹慎使用 Trigger
47. 去除冗余欄位
48. 多對多用關聯表
49. 選用最小足夠資料型別
50. 大欄位低頻存取時垂直拆分
51. 定期 ANALYZE/OPTIMIZE TABLE
52. InnoDB 表定期重建聚簇索引
53. 大批量加欄位：Drop 索引 → 加欄位 → 建索引
54. 業務選擇儲存引擎
55. Archive 引擎存日誌/審計表
56. Session 資料放 Redis/Memcached
57. 可變長字串優先 VARCHAR
58. Schema 變更小步快跑
59. 測試環境模擬生產數據量驗證
60. 勿隨意改動配置檔
61. 配置檔「少即是多」
62. 無把握時用官方通用配置模板

四、查詢優化實戰技巧

63. Slow Query Log 捕捉慢查詢
64. EXPLAIN 分析執行計畫
65. 定期回歸測試核心 SQL
66. 避免 COUNT(*) 全表掃描
67. SQL 寫法一致性利用 Query Cache
68. GROUP BY 替代 DISTINCT
69. WHERE/GROUP BY/ORDER BY 欄位加索引
70. 索引保持簡潔
71. 優化器選錯索引用 USE INDEX
72. 啟用 SQL_MODE=STRICT
73. 低基數欄位 UNION 用 LIMIT
74. Upsert 用 INSERT ... ON DUPLICATE KEY UPDATE
75. 求最大值用 ORDER BY desc LIMIT 1
76. 禁用 ORDER BY RAND()
77. LIMIT M,N 深分頁慎用
78. 子查詢改 JOIN/UNION
79. 更新用 LOCK IN SHARE MODE 減少排他鎖
80. 重啟後預熱 Buffer Pool
81. 清空大表用 TRUNCATE 或 DROP+CREATE
82. 嚴禁 SELECT *
83. 使用長連接/連線池
84. 壓測含背景負載
85. 高負載時 SHOW PROCESSLIST 定位問題
86. 可疑 SQL 在鏡像資料驗證

五、備份與災難恢復策略

87. 從從庫備份
88. 備份期間停止 SQL 執行緒
89. 冷備份：停庫複製資料檔
90. mysqldump 同步備份 Binlog
91. 不信任 LVM 快照
92. 單表導出加速單表恢復
93. mysqldump 加 --opt
94. 備份前 CHECK/OPTIMIZE TABLE
95. 匯入前關閉 FK 與唯一性檢查
96. 記錄庫/表/索引大小趨勢
97. 自動化監控主從延遲與錯誤
98. 定期排程備份
99. 定期演練恢復