管理員優化核心原則
- 內存訪問 ≫ 磁碟訪問:納秒級 vs 毫秒級
- 盡量將熱數據、索引駐留內存
- 索引信息優於數據內容:索引小、命中率高、支援範圍查詢
關鍵系統變量調優
1. 表緩存 table_open_cache
# 監控指標
SHOW GLOBAL STATUS LIKE 'Opened_tables';
# 穩定/緩慢增長 = 適宜;快速增長 = 需調大
# 典型配置
table_open_cache = 4000
open_files_limit = 65535 # 系統級限制需同步調大
2. MyISAM 鍵緩存 key_buffer_size
# 專用 MyISAM:物理內存 25-50%
key_buffer_size = 2G
# 多鍵緩存(4.1+)針對熱表
SET GLOBAL hot_cache.key_buffer_size = 128M;
CACHE INDEX hot_table IN hot_cache;
LOAD INDEX INTO CACHE hot_table;
3. InnoDB 緩衝池 innodb_buffer_pool_size(核心)
# 專用 DB:60-80% 物理內存
innodb_buffer_pool_size = 16G
innodb_buffer_pool_instances = 8 # 減少鎖爭用
# 監控命中率
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
# (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) > 99%
4. InnoDB 日誌緩衝 innodb_log_buffer_size
innodb_log_buffer_size = 64M # 大事務、大 Blob 場景調大
5. 查詢緩存(5.7 建議關閉,8.0 已移除)
query_cache_type = 0 # 關閉避免鎖爭用
query_cache_size = 0
# 僅極低寫入、高重複查詢場景考慮開啟
系統級優化策略
禁用無用存儲引擎
- 源碼編譯:
--without-innodb --without-archive --without-blackhole
- 運行時:
--skip-innodb(不推薦,InnoDB 為核心)
簡化權限系統
- 清空
mysql.tables_priv、mysql.columns_priv
- 避免逐行/逐列權限檢查開銷
靜態編譯
./configure --with-mysqld-ldflags=-all-static --disable-shared
# 5-10% 效能提升,但不支援 UDF 動態加載
硬件配置優化指南
1. 內存(最高投資回報比)
- 越大越好,支援更大緩衝池、排序緩衝、連接緩衝
- 消除 Swap:
swapoff -a 或內存文件系統跑 Swap
2. 儲存子系統
| 方案 | 適用場景 |
| NVMe SSD | 高併發 OLTP、低延遲關鍵 |
| RAID 10 (4+ 磁碟) | 平衡讀寫、冗余、可用性 |
| RAID 5/6 | 讀多寫少、成本敏感 |
關鍵:innodb_flush_log_at_trx_commit=1 + sync_binlog=1 需電池備援寫入緩存 (BBU/WB)
3. I/O 隔離
- 數據文件、Redo Log、Binlog、備份分別掛載不同物理設備
- 同一物理盤不同分區**無並行優勢**(共享磁頭)
4. 多處理器/多核
- MySQL 多線程天然受益多核
innodb_read_io_threads/write_io_threads 配合核心數
- NUMA 架構需綁定
numactl --interleave=all
調優實施流程
- 建立基準線(SysBench、TPC-C、業務回放)
- 識別瓶頸(
SHOW ENGINE INNODB STATUS、perf、iostat、vmstat)
- 單一參數調整 → 壓測驗證 → 固化配置
- 持續監控:PMQ(Prometheus + MySQL Exporter + Grafana)
- 定期審視:Schema 變更、數據增長、查詢模式漂移
參考配置模板(專用 32G 內存 InnoDB 實例)
[mysqld]
innodb_buffer_pool_size = 24G
innodb_buffer_pool_instances = 16
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_log_buffer_size = 128M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity = 20000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
max_connections = 2000
table_open_cache = 8000
open_files_limit = 65535
query_cache_type = 0
query_cache_size = 0
log_bin = mysql-bin
sync_binlog = 1
binlog_format = ROW
expire_logs_days = 7
slow_query_log = 1
long_query_time = 0.5
log_queries_not_using_indexes = 1