MySQL 性能調優 100 條鐵律：硬體、配置、架構、查詢、備份全維度最佳實踐

硬體與作業系統層 (1-24)

1. 記憶體容納熱數據： 物理記憶體 ≥ InnoDB 數據+索引總量，避免 Swap
2. 禁用 Swap： vm.swappiness=0，Swap 讀寫延遲是記憶體 10 萬倍
3. 電池備援 RAID 卡： BBWC/FBWC 保護寫入緩衝，掉電不丟數據
4. RAID 10 首選： 讀寫均衡、重建快、無校驗開銷；避開 RAID 5 (寫入懲罰)
5. OS 與數據分離： 物理磁碟分離，避免系統 IO 干擾資料庫
6. 臨時空間/Relay Log 獨立磁碟： 分散 IO 熱點
7. 更多磁碟 = 更高 IOPS： 同規格下，多盤條帶化吞吐線性增長
8. SAS/NVMe > SATA： 更高 IOPS、更低延遲、雙埠冗余
9. 小容量高轉速 > 大容量低轉速： 同價位下，IOPS 密度更高
10. 固態 IO 卡 (非 SSD)： 如 Intel Optane、Fusion-io，微秒級延遲、2GB/s+ 寫入
11. Linux I/O 調度器： noop 或 deadline (SSD/NVMe)；避開 cfq/anticipatory
12. XFS 文件系統： 並行 IO 能力強、無雙緩衝問題、支援大文件
13. 掛載參數： noatime,nodiratime 關閉訪問時間更新
14. 64 位 OS 強制： 突破 4GB 記憶體限制、支援大頁面
15. 精簡系統服務： 關閉無用 daemon、網卡多隊列、IRQ 綁核
16. 專機專用： 無 Web、無監控 Agent、無備份客戶端佔用 CPU
17. /etc/hosts 直解： 免 DNS 查詢延遲
18. 嚴禁 kill -9 mysqld： 導致崩潰恢復、備份不一致、數據損壞
19. NTP 時間同步： 複制、審計、日誌分析依賴精準時間
20. 透明大頁 (THP) 關閉： echo never > /sys/kernel/mm/transparent_hugepage/enabled，避免記憶體碎片、延遲抖動
21. ulimit -n 655350： 足夠文件描述符
22. innodb_buffer_pool_size = 70% 物理記憶體 (專用機)
23. innodb_log_file_size = 1~4GB： 減少 Checkpoint 壓力、加快崩潰恢復
24. innodb_flush_method = O_DIRECT： 避免雙緩衝 (需 XFS/EXT4，避開 EXT3)

MySQL 配置層 (25-62)

25. innodb_buffer_pool_instances = 8 (大於 1GB 時)，減少鎖競爭
26. innodb_read_io_threads = 16, innodb_write_io_threads = 16 (NVMe 可調大)
27. innodb_io_capacity = 20000 (NVMe)、innodb_io_capacity_max = 40000
28. max_connections = 1000~5000： 依業務評估，過大耗盡記憶體
29. thread_cache_size = 100~200： 減少線程創建開銷
30. table_open_cache = 4000~10000： 避免頻繁開關表
31. skip-name-resolve： 禁用 DNS 反解
32. 查詢緩存 (QC) 慎用： 5.7 已廢棄，8.0 移除；高寫入場景 QC 失效率極高
33. tmp_table_size = max_heap_table_size = 64M~256M： 減少臨時表落磁碟
34. sort_buffer_size = 2M~4M (會話級)：**勿設過大**，併發時記憶體爆炸
35. join_buffer_size = 2M~8M (會話級)：**同上**
36. read_rnd_buffer_size = 2M~4M： 排序後隨機讀優化
37. key_buffer_size (MyISAM)：**僅用於系統表/舊表**，按 Key_reads/Key_read_requests < 0.01 調整
38. innodb_flush_log_at_trx_commit = 1 (安全) / 2 (高寫入犧牲 1 秒) / 0 (極致速度)
39. sync_binlog = 1 (安全) / 100~1000 (高寫入)
40. binlog_format = ROW： 複制最安全、支援非確定性函數
41. gtid_mode = ON, enforce_gtid_consistency = ON： 5.6+ 現代複制基石
42. slave_parallel_workers = 4~16, slave_parallel_type = LOGICAL_CLOCK (5.7+)： 並行複制消除延遲
43. master_info_repository = TABLE, relay_log_info_repository = TABLE： 避免文件損壞
44. log_slave_updates = 1： 鏈式複制/雙主必開
45. expire_logs_days = 7~14： 防止 Binlog 撐爆磁碟
46. slow_query_log = 1, long_query_time = 1： 全量慢查詢審計
47. log_queries_not_using_indexes = 1： 發現全掃描隱患
48. sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO
49. 測試環境驗證所有配置變更，再推生產
50. 保留最小可用配置集，避免參數相互抵消
51. 疑難雜症回歸官方預設，逐個調整驗證

架構與 Schema 設計層 (63-86)

63. 範式化優先，反範式化按需： 讀多寫少才冗余
64. 歸檔冷數據： 歷史表分庫/分表/歸檔存儲，主表保持精簡
65. 索引策略： 覆蓋索引 > 複合索引 > 單列索引；單表索引 ≤ 5 個
66. 避免過度索引： 寫入放大、優化器混淆、統計信息偏差
67. 大字段拆表： BLOB/TEXT/JSON 低頻讀寫 → 垂直拆分
68. 字符集統一 utf8mb4： 避免隱式轉換、支援 Emoji
69. 數值優於字串： 狀態、類型、枚舉用 TINYINT/SMALLINT
70. NOT NULL 默認： 除非業務明確需 NULL
71. ENUM 替代狀態字串： 內部整數、自動驗證合法值
72. VARCHAR 而非 CHAR (變長場景)： 節省空間、減少 IO (utf8mb4 除外)
73. 最小長度原則： VARCHAR(32) 夠用別設 255
74. 時間戳用 DATETIME/TIMESTAMP： 避免 INT 存 Unix 時間 (時區、可讀性)
75. 外鍵謹慎用： 高併發鎖競爭、跨庫不支援；應用層保證一致性
76. 觸發器慎用： 隱藏邏輯、除錯難、複制風險
77. 分區表僅限時間序列/歸檔場景： 分區鍵在 PK、整數鍵、無全局索引
78. 定期 OPTIMIZE TABLE / ANALYZE TABLE
79. DDL 走線上工具： pt-online-schema-change / gh-ost
80. 會話數據存 Redis/Memcached，不存 MySQL
81. Schema 變更小步快跑、灰度驗證、可回滾

查詢優化層 (87-100)

87. 慢查詢日誌 + pt-query-digest 例行分析
88. EXPLAIN 驗證每個上線 SQL
89. 持續基準測試： sysbench、tpcc-mysql、真實流量回放
90. 避免 COUNT(*) 無條件全掃
91. SQL 模板化： 利用 Query Cache (舊版) / Prepared Statement 復用計畫
92. GROUP BY 優於 DISTINCT (視版本)
93. WHERE/GROUP BY/ORDER BY 走索引列
94. 索引列不參與運算/函數/隱式轉換
95. 優化器選錯索引 → USE INDEX
96. SQL_MODE=STRICT 暴露數據質量問題
97. 小結果集 UNION 替代 OR
98. INSERT ... ON DUPLICATE KEY UPDATE 替代 SELECT+UPDATE
99. 避免 MAX() 無索引 → 索引列 ORDER BY DESC LIMIT 1
100. 禁用 ORDER BY RAND()
101. LIMIT M,N 深分頁 → 延遲關聯/游標/ID 游標
102. 子查詢轉 JOIN/EXISTS
103. UPDATE 加 LOCK IN SHARE MODE 減少排他鎖
104. 重啟後預熱： SELECT * FROM t WHERE pk > 0 LIMIT 1 等觸發 Buffer Pool 載入
105. 清空表用 TRUNCATE 而非 DELETE
106. SELECT 只取需要列，拒絕 *
107. 持久連接 / 連接池 (HikariCP/Druid) 減少握手
108. 負載測試驗證併發下的查詢干擾
109. SHOW PROCESSLIST / Performance Schema 實時診斷
110. 鏡像生產數據到測試環境驗證疑難 SQL

備份與災備層 (101-120 附贈)

101. 從庫備份不影響主庫
102. 備份前 STOP SLAVE 保證一致性
103. 物理備份 (xtrabackup) 秒級、無鎖、支援增量
104. 邏輯備份 (mysqldump) 兼容性好、跨版本、單表恢復易
105. mysqldump 必加 --single-transaction --master-data=2 --routines --triggers --events
106. 不信任 LVM 快照 (凍結 IO 風險)
107. 按表導出便於單表恢復
108. 導入前 SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0;
109. 每備份統計表/索引大小，監控增長趨勢
110. 自動化腳本監控複制延遲、錯誤、Binlog 積壓
111. 定時全備 + 增量 Binlog，RPO < 5 分鐘
112. 季度恢復演練，驗證 RTO、數據完整性
113. 異地多副本：本地 + 異地 + 雲端
114. 備份加密傳輸、存儲加密、金鑰輪換
115. 文檔化：備份策略、恢復步驟、責任人、SLA

結語

這 100+ 條鐵律涵蓋了 MySQL 生命週期的全鏈路。調優無銀彈，只有「監控發現 → 根因定位 → 針對性調整 → 效果驗證 → 固化標準」的工程化閉環。建議團隊將核心 30 條製成「上線檢查清單」，其餘納入「架構評審/事故複盤/季度體檢」機制，讓最佳實踐內化為肌肉記憶。