MySQL 底層效能優化全攻略：硬體、系統核心、資料庫參數、Schema 設計四大維度

前言

MySQL 效能優化是系統工程，涵蓋硬體選型、OS 核心參數、MySQL 實例配置、Schema 設計四大維度。本文匯總生產環境驗證的最佳實踐，助你構建高效能、高可用的 MySQL 服務。

一、硬體層優化

1.1 CPU 設定（BIOS 層面）

• Performance Per Watt Optimized (DAPC)：發揮最大算力，資料庫不考慮省電
• 關閉 C1E / C States：避免 CPU 降頻導致延遲抖動
• Memory Frequency → Maximum Performance：記憶體最高頻運行
• 啟用 Node Interleaving：解決 NUMA 架構下的跨節點記憶體訪問懲罰

1.2 磁碟 I/O 提升策略（按效果排序）

1. SSD / PCIe SSD：IOPS 提升百倍~萬倍，首選方案
2. RAID 卡 + Cache + BBU：機械盤必備，Write Back 模式大幅提升寫入；定期檢查 BBU 健康度
3. RAID 策略：WB (Write Back) > FORCE WB > WT (Write Through)；關閉預讀（通常無效）
4. RAID-10 > RAID-5：寫入效能與重建安全性雙贏
5. 15K RPM > 7.2K RPM：機械盤選高轉速，成本差價小

二、系統層優化

2.1 檔案系統與 I/O 調度器

• I/O 調度器：deadline / noop（SSD 用 noop，機械盤用 deadline）；禁用 cfq（不適合 DB 工作負載）
• 檔案系統：XFS > ext4 > ext3；大並發、大文件場景 XFS 明顯優勢
• 掛載參數： noatime,nodiratime,nobarrier（nobarrier 僅 XFS 支援）

2.2 核心參數調優

# 減少 Swap 傾向
vm.swappiness = 5-10  # RHEL 7+ 慎設 0，否則觸發 OOM Killer

# 平滑髒頁刷新，避免寫入風暴
vm.dirty_background_ratio = 5-10
vm.dirty_ratio = 2×dirty_background_ratio

# TCP 連接復用，減少 TIME_WAIT
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_tw_reuse = 1

# 讀取優化（視負載測試決定）
# read_ahead_kb, nr_requests

三、MySQL 實例層優化

3.1 版本選擇建議

3.2 核心參數模板（Percona 5.7 為例）

[mysqld]
# 基礎
default-storage-engine = InnoDB
innodb_buffer_pool_size = 物理記憶體 50%-70%  # 單實例純InnoDB
innodb_file_per_table = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_log_file_size = 256M
innodb_log_files_in_group = 2

# 持久化權衡
# 強一致：innodb_flush_log_at_trx_commit=1, sync_binlog=1
# 平衡：  innodb_flush_log_at_trx_commit=2, sync_binlog=10
# 高性能：innodb_flush_log_at_trx_commit=0, sync_binlog=0

# 慢查詢
long_query_time = 0.05  # 50ms，5.5+ 支援毫秒級

# 連接相關
max_connections = 2000
max_connect_errors = 100000
open_files_limit = 20000
innodb_open_files = 20000
table_open_cache = 20000
table_definition_cache = 20000

# 會話級緩衝（慎設大值，每連接分配！）
tmp_table_size = 32M
max_heap_table_size = 32M
sort_buffer_size = 2M
join_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M

# 廢棄 MyISAM 相關
key_buffer_size = 32M
query_cache_type = 0
query_cache_size = 0

3.3 Schema 設計規範

1. 所有 InnoDB 表必須有無業務含義的自增主鍵（極少純只讀表除外，否則考慮 TokuDB）
2. 欄位長度夠用即可，盡量 NOT NULL（提升索引效能、節省空間）
3. 避免 TEXT/BLOB，必須用則拆子表，防 SELECT * 讀取觸發隨機 I/O
4. 禁止 SELECT *，顯式列出所需欄位
5. VARCHAR 索引建前綴索引（50% 長度通常覆蓋 80%+ 查詢），避免全長索引
6. 子查詢改 JOIN（MySQL 子查詢優化較弱）
7. 多表 JOIN：關聯欄位類型一致、均有索引；結果集小的表作驅動表
8. JOIN + ORDER BY：排序欄位必須來自驅動表，否則無法用索引
9. 多用複合索引，少用多單列索引；基數 < 255 的列勿建索引
10. 分頁優化：SELECT id FROM t WHERE ... ORDER BY id LIMIT 10000,20 → 回表查詳情

3.4 運維管理建議

• 單表 < 10GB、< 1 億行、行長 < 8KB 通常無壓力（主要考量 Online DDL 代價）
• 不必過度擔心 mysqld 佔用大量記憶體，只要無 OOM/Kill 與大量 Swap 即可
• 單實例已跑滿資源時，無需多實例部署
• 定期 pt-duplicate-key-checker 清理重複索引，pt-index-usage 清理低頻索引
• 慢查詢收集 → pt-query-digest 分析 → Anemometer 管理平台
• pt-kill 幹掉長事務；Percona 專屬 innodb_kill_idle_transaction
• 大表 DDL 用 pt-online-schema-change
• 主從一致性檢查：pt-table-checksum + pt-table-sync