21 條 MySQL 優化建議：從硬件到參數的經驗總結

硬件層優化

1. 內存：數據庫生命線

• 推薦內存 ≥ 數據量 15~25%
• 熱點業務：內存 ≥ 數據量 80%（全內存運作）
• InnoDB Buffer Pool 設為物理內存 60~80%

2. CPU：核心數與主頻雙重奏

• MySQL 5.6+ 支持 64 核擴展
• 單 Query 單線程 → 高主頻 > 多核心
• 高並發場景：多核心 + 高主頻

3. 作業系統選型

• 首選：CentOS 7+/RHEL 7+/Oracle Linux 7+
• 避坑：Windows 高並發（IO/線程模型劣勢）
• 文件系統：XFS > Ext4 > Ext3/Ext2

4. 系統參數調優

# 文件句柄
ulimit -n 65535

# 進程數
ulimit -u 65535

# 禁用 NUMA（跨節點內存訪問延遲）
numactl --interleave=all mysqld_safe ...

5. 內存分配器升級

[mysqld_safe]
malloc-lib = /usr/local/lib/libtcmalloc_minimal.so
# 或 jemalloc
malloc-lib = /usr/lib/libjemalloc.so

顯著降低高並發下內存碎片與鎖競爭。

6. 存儲介質：NVMe SSD / 預留卡

• 隨機 IOPS 決定 MySQL 寫入性能上限
• RAID 卡啟用 Write Back + 閃電保護（BBU/FBWC）

7. 文件系統掛載參數

# XFS
/dev/nvme0n1 /data xfs rw,noatime,nodiratime,nobarrier 0 0

# Ext4
/dev/sda1 /data ext4 rw,noatime,nodiratime,nobarrier,data=ordered 0 0

8. I/O 調度器

echo deadline > /sys/block/nvme0n1/queue/scheduler

SSD 推薦 deadline 或 none（NVMe 通常無需調度）。

9. RAID 卡緩存策略

• Write Back + BBU：Redo Log、Binlog、Data File 全受益
• 定期檢查 BBU 健康狀態

MySQL 核心參數優化

10. 禁用 Query Cache

InnoDB Buffer Pool 已緩存數據頁，Query Cache 反而增加寫入開銷（失效掃描）。MySQL 5.6+ 默認關閉，5.7 移除。

11. 啟用 Thread Pool

• 連接數 > 200 時必開
• Percona Server / MariaDB 內建；官方版需插件
• 將「線程=連接」改為「線程池=CPU核心數」

12. 連接內存控制

-- 每連接內存 = 
read_buffer_size + read_rnd_buffer_size + 
join_buffer_size + sort_buffer_size + 
binlog_cache_size + thread_stack + ...
-- 建議值（按需調整，單位字節）
read_buffer_size = 2M
sort_buffer_size = 4M
join_buffer_size = 4M

13. InnoDB Buffer Pool 科學配置

• 大小：物理內存 60~80%，不超數據總量
• 實例數：innodb_buffer_pool_instances = 8（>1GB 時）
• 預熱：innodb_buffer_pool_dump_at_shutdown=ON

14. Redo Log 刷盤策略

建議：核心金融=1，一般業務=2，緩存/日誌=0。

15. Binlog 同步策略

sync_binlog = 1          # 配合 Group Commit（5.6+）最佳
# 无 Group Commit 時可設 0 獲取性能

16. 數據文件刷盤

innodb_flush_method = O_DIRECT  # 繞過 OS 緩存，減少雙重緩存

17. 僅使用 InnoDB

• 事務、行鎖、MVCC、在線 DDL、Fulltext、Memcached API
• MyISAM 僅適合只讀/歸檔表

18. Redo Log 足夠大

• 5.6+ 支持 > 4GB，建議總量 > 500MB
• 目標：容納 1 小時寫入量（觀察 Innodb_os_log_written 速率）

19. IO 容量配置

# SAS 15K RPM
innodb_io_capacity = 800

# NVMe SSD
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# 5.6+ 關聯參數
innodb_lru_scan_depth = innodb_io_capacity / innodb_buffer_pool_instances

20. 獨立表空間

innodb_file_per_table = ON

• 支持 TRUNCATE TABLE 瞬間回收空間
• 支持表空間傳輸（TTS）跨實例遷移
• 碎片整理粒度細

21. 並發控制

# 有 Thread Pool
innodb_thread_concurrency = 0

# 無 Thread Pool
# 5.5: 16~32
# 5.6/5.7: 36~64
# 8.0: 0 (自適應)

事務隔離級別調優

-- 默認 REPEATABLE-READ
-- 建議讀已提交 + Row 格式 Binlog
SET GLOBAL transaction_isolation = 'READ-COMMITTED';
SET GLOBAL binlog_format = 'ROW';

• 降低間隙鎖衝突，提升並發
• Row 格式 Binlog 保證主從一致性

監控體系建設

• 指標：QPS、TPS、延遲分位、連接數、緩存命中率、IO 使用率、鎖等待
• 工具：Prometheus + Grafana / Zabbix + Percona Monitoring Plugins
• 告警：慢查詢增長、連接數飆升、磁盤空間、主從延遲

總結：優化優先級金字塔

        ┌─────────────┐
        │  SQL/索引   │  ← 80% 收益來源
        ├─────────────┤
        │  架構/分庫  │
        ├─────────────┤
        │  核心參數   │
        ├─────────────┤
        │  硬件/OS    │  ← 基石，不可忽視
        └─────────────┘