101 條 MySQL 調優黃金法則：硬體、配置、架構、查詢、備份全維度精華版

前言

本文精選自 Monitis 經典「101 Tips for MySQL Tuning and Optimization」，結合 MySQL 5.7/8.0 現代特性重構分類，去除過時建議，補充生產實踐精華，形成可落地的調優清單。

一、硬體與作業系統層 (打鐵需自身硬)

1. 內存夠大裝下熱數據：innodb_buffer_pool_size ≥ 熱數據量 (含索引)，目標命中率 > 99%。
2. 禁用 Swap：vm.swappiness=1，或 swapoff -a。Swap 觸發 = 性能雪崩。
3. NVMe SSD + RAID 10：隨機 I/O 吞吐 > SATA SSD > SAS HDD。避開 RAID 5/6 (寫入懲罰)。
4. 分離掛載點：數據目錄、Redo Log、Binlog、臨時目錄、OS 根分區**物理分盤**，消除 I/O 爭用。
5. XFS / ext4 (noatime,nodiratime)：掛載選項減少元數據寫入。XFS 大文件/高併發優勢明顯。
6. I/O 調度器：noop / none / mq-deadline：SSD/NVMe 禁用 CFQ/Deadline，降低延遲抖動。
7. 64 位 OS + 透明大頁 (THP) 關閉：echo never > /sys/kernel/mm/transparent_hugepage/enabled，防止內存分配延遲抖動。
8. 網絡調優：net.core.somaxconn=65535, net.ipv4.tcp_max_syn_backlog=65535, 關閉 DNS 反解 (skip_name_resolve=ON)。
9. 專機專用：DB 服務器不跑應用、監控 Agent、備份客戶端等雜項進程。
10. NTP 時間同步：chronyd，時間漂移導致 Binlog/GTID/審計混亂。

二、MySQL 核心配置參數 (my.cnf 精華版)

11. innodb_buffer_pool_size = 物理內存 60-75% (專機)。
12. innodb_buffer_pool_instances = 8 (或 1GB/實例)，減少互斥鎖爭用。
13. innodb_log_file_size = 1-4GB，innodb_log_files_in_group=2，大 Redo Log 減少 Checkpoint 衝刷。
14. innodb_flush_log_at_trx_commit = 1 (主庫) / 2 (從庫)。
15. innodb_flush_method = O_DIRECT (配合 XFS/ext4)，避免雙緩衝。
16. innodb_io_capacity = 2000 (SATA SSD) / 5000+ (NVMe)，innodb_io_capacity_max = 2x。
17. innodb_read_io_threads = 8, innodb_write_io_threads = 8 (高併發可 16/32)。
18. innodb_thread_concurrency = 0 (無限制，現代版本自適應優於手動限制)。
19. max_connections = 2000-5000，thread_cache_size = 100，back_log = 1024。
20. table_open_cache = 4000, table_definition_cache = 2000 (監控 Opened_tables 調整)。
21. query_cache_type = OFF, query_cache_size = 0 (8.0 已移除 QC，5.7 必關，寫入失效開銷極大)。
22. tmp_table_size = 64M, max_heap_table_size = 64M，防止內部臨時表落盤。
23. sort_buffer_size = 2M, join_buffer_size = 2M，**會話級**，不可設過大 (併發 × 大小 = OOM 風險)。
24. binlog_format = ROW, binlog_row_image = FULL, sync_binlog = 1 (主庫)，expire_logs_days = 7 (或 binlog_expire_logs_seconds 8.0)。
25. gtid_mode = ON, enforce_gtid_consistency = ON (主從高可用基石)。
26. sql_mode = "STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO"。
27. skip_name_resolve = ON (禁用 DNS 反解)。
28. explicit_defaults_for_timestamp = ON (5.6+ 默認 ON)。

三、架構與模式設計 (Schema Design)

28. 範式化 vs 反範式化：核心業務 3NF，讀多寫少/分析型適度反範式化 (冗余字段/物化視圖)。
29. 選對數據類型：INT 而非 BIGINT、CHAR 定長代碼、DECIMAL 金額、DATETIME(3) 毫秒、ENUM 低基數狀態 (慎用，修改結構鎖表)。
30. 主鍵設計：單調遞增 (自增/雪花算法)，避免 UUID/字符串主鍵導致頁分裂、二級索引膨脹。
31. 索引策略：覆蓋索引、最左前綴、避免冗余/重複索引、pt-duplicate-key-checker 定期掃描。
32. 大字段拆分：BLOB/TEXT/JSON/長 VARCHAR 移至副表，主表僅存主鍵+高頻字段，減少頁碎片、提升緩存命中。
33. 分區表：按時間/範圍分區 (RANGE/LIST)，配合分區裁剪加速歷史數據查詢/歸檔。
34. 選對存儲引擎：OLTP → InnoDB；日誌/審計/只寫 → Archive/CSV；全文搜索 → InnoDB Fulltext / Elasticsearch。
35. 會話數據外移：Session/Token/購物車 → Redis/Memcached，DB 只存持久化業務數據。
36. DDL 線上變更：pt-online-schema-change / gh-ost，零鎖表變更結構。
37. 統計信息維護：ANALYZE TABLE 定期/自動 (8.0 innodb_stats_persistent)，防止執行計劃劣化。

四、查詢與索引優化 (SQL Tuning)

38. 慢查詢日誌全開：long_query_time=1, log_queries_not_using_indexes=OFF, log_slow_admin_statements=OFF。
39. EXPLAIN 讀懂執行計劃：關注 type (system/const/eq_ref/ref/range/index/all)、key、rows、Extra (Using index/where/temporary/filesort)。
40. 避免 SELECT *：只取需要列，減少 I/O、網絡、緩存壓力，利於覆蓋索引。
41. COUNT(*) 陷阱：InnoDB 無觸發器/外鍵時 COUNT(*) 掃描聚簇索引，大表極慢。用計數表/Redis/近似值 (SHOW TABLE STATUS)。
42. LIMIT M, N 深分頁優化：子查詢回表 / 遞增 ID 範圍 / 游標分頁 (見專題文章)。
43. OR 改 UNION ALL：OR 導致索引失效，UNION ALL 分別走索引再合併。
44. 子查詢改 JOIN：MySQL 子查詢優化器歷史較弱，衍生表物化開銷大。
45. 隱式轉換殺手：WHERE varchar_col = 123 導致全表掃描，應用層/ORM 必須保證類型一致。
46. 範圍查詢優化：WHERE dt >= '2023-01-01' AND dt < '2023-02-01' 而非 BETWEEN (含邊界歧義) 或函數 DATE(dt)。
47. 適度反範式化：冗余高頻 JOIN 字段，消除關聯查詢。

五、備份與高可用 (Backup & HA)

48. 從庫備份：主庫零壓力，STOP SLAVE SQL_THREAD 靜默備份 (邏輯/物理)，備份後 START SLAVE。
49. 物理備份首選 XtraBackup：熱備、增量、流式、壓縮、驗證、並行恢復。
50. LVM/存儲快照輔助：秒級凍結，配合 Binlog PITR，但**不可作為唯一備份** (一致性風險)。
51. 備份加密與校驗：openssl enc -aes-256-gcm 加密存儲，sha256sum 校驗傳輸完整性。
52. 單表恢復能力：邏輯備份按表切分文件，或備份後解析切分。
53. 外鍵/唯一約束暫停導入：SET FOREIGN_KEY_CHECKS=0; SET UNIQUE_CHECKS=0; 加速恢復。
54. 監控主從延遲：Seconds_Behind_Master / Replica_Lag (8.0)，告警閾值 30s/300s。
55. 定期恢復演練：月度全量恢復測試，季度 PITR 測試，產出報告。
56. 多層備份體系：邏輯備份 (天) + 物理備份 (週) + Binlog 歸檔 (實時) + 延遲從庫 (24h)。
57. 監控 MySQL 核心指標：QPS/TPS、緩存命中率、InnoDB 行鎖等待、磁碟 I/O 利用率、線程運行數、錯誤計數器。推薦 Prometheus + mysqld_exporter + Grafana。

六、進階與避坑 (Advanced & Pitfalls)

58. 不要盲目複製網上的配置：每個參數都要懂原理、測試效果、監控副作用。
59. 少即是多：只設置偏離默認值的參數，保持配置文件乾淨可讀。
60. 保留測試環境：生產配置變更必須先在鏡像測試環境驗證 (含壓測/故障注入)。
61. 警惕「銀彈」工具：自動索引推薦、自動 SQL 改寫、自動參數調優工具僅供參考，人工審核上線。
62. 版本升級測試：主版本升級 (5.7→8.0) 必須全量回歸測試，關注保留字、優化器行為、認證插件、字符集變化。
63. 審計合規：開啟審計日誌 (MariaDB Audit Plugin / MySQL Enterprise Audit)，記錄 DDL/DML/登錄/錯誤。
64. 安全基線：最小權限賬號、SSL 傳輸、密碼策略、禁用遠程 root、定期滾動密鑰。
65. 容量規劃：監控數據增長趨勢，預留 30% 磁碟/內存餘量，提前擴容而非緊急擴容。
66. 文檔化 SOP：故障處理手冊、變更流程、備份恢復步驟、聯繫矩陣，新人可獨立執行。
67. 持續學習：關注 MySQL Release Notes、Percona Blog、MySQL Server Team Blog、VividCortex、Planet MySQL。

結語

調優無終點，只有在特定業務、數據量、硬體、版本下的「當前最優解」。建立**監控基線 → 發現瓶頸 → 理論分析 → 受控變更 → 效果驗證 → 固化基線**的閉環流程，比背誦 101 條法則更有價值。願你的 MySQL 穩如磐石，快如閃電。