MySQL 數據庫效能調優：系統層與實例層參數最佳實踐
一、作業系統層優化
1. 關閉 SELinux
vim /etc/selinux/config
# SELINUX=enforcing → SELINUX=disabled
# 重啟生效
2. I/O 調度器（機械硬碟有效，SSD 免調）
echo deadline > /sys/block/sda/queue/scheduler
# 永久生效需加入啟動腳本或 grub 配置
3. 檔案描述符限制
vim /etc/security/limits.conf
* soft nofile 65535
* hard nofile 65535
root soft nofile 65535
root hard nofile 65535
4. 核心網路/記憶體參數
vim /etc/sysctl.conf
net.core.netdev_max_backlog = 3000
net.core.optmem_max = 20480
net.core.rmem_default = 8388608
net.core.rmem_max = 8388608
net.core.wmem_default = 1048576
net.core.wmem_max = 1048576
net.ipv4.tcp_mem = 786432 1048576 1572864
net.ipv4.tcp_rmem = 32768 4194304 8388608
net.ipv4.tcp_wmem = 8192 4194304 8388608
net.ipv4.tcp_max_syn_backlog = 2048
net.ipv4.tcp_retries2 = 5
net.ipv4.tcp_fin_timeout = 30
net.ipv4.tcp_keepalive_time = 3600
net.ipv4.tcp_keepalive_intvl = 30
net.ipv4.tcp_keepalive_probes = 9
net.ipv4.tcp_max_tw_buckets = 6000
net.ipv4.ip_local_port_range = 10240 61000
fs.file-max = 6815744
vm.swappiness = 0
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.ctrl-alt-del = 1
# 生效
sysctl -p
二、MySQL 5.6.10 實例配置範例（24GB 記憶體專用主機）
[mysqld]
# 基礎連線
socket = /tmp/mysql.sock
max_connections = 1024
max_connect_errors = 10000
max_allowed_packet = 16M
skip-name-resolve
lower_case_table_names = 1
# 緩存與緩衝區
thread_cache = 128
table_open_cache = 1024
query_cache_type = 1
query_cache_size = 128M
join_buffer_size = 8M
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
max_heap_table_size = 128M
tmp_table_size = 128M
tmpdir = /dev/shm
# Binlog
binlog_cache_size = 12M
max_binlog_size = 512M
expire_logs_days = 3
# InnoDB 核心（佔總記憶體 60~70%）
innodb_buffer_pool_size = 16G
innodb_use_sys_malloc = 1
innodb_log_file_size = 128M # 25% of buffer pool
innodb_log_buffer_size = 32M
innodb_flush_log_at_trx_commit = 2 # 1 最安全，2 效能最佳（秒級丟失風險）
innodb_flush_method = O_DIRECT
innodb_lock_wait_timeout = 50
# SQL 模式
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
# 以下為常用調優項（視負載啟用）
# slow_query_log = 1
# slow_query_log_file = slow.log
# long_query_time = 1
# log_queries_not_using_indexes = 1
# log-bin = mysql-bin
# server-id = 1
# innodb_flush_log_at_trx_commit = 1
# sync_binlog = 1
三、MySQL 5.6.10 編譯安裝參數
tar zxvf mysql-5.6.10.tar.gz
cd mysql-5.6.10
cmake . \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DMYSQL_TCP_PORT=3306 \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_DEBUG=0 \
-DCURSES_LIBRARY=/usr/lib64/libncurses.so \
-DCURSES_INCLUDE_PATH=/usr/include
make && make install
四、關鍵參數解析
| 參數 | 建議值 | 說明 |
|---|
| innodb_buffer_pool_size | 物理記憶體 60~70% | InnoDB 核心緩存，數據+索引+鎖+自適應哈希，越大越好 |
| innodb_log_file_size | buffer_pool 25% | Redo Log 大小，過小導致頻繁 Checkpoint，過大恢復慢 |
| innodb_flush_log_at_trx_commit | 2 (高併發) / 1 (金融核心) | 1: 每次提交 fsync；2: 寫 OS Cache，每秒 fsync；0: 僅寫 Log Buffer |
| innodb_flush_method | O_DIRECT | 繞過 OS Cache，避免雙重緩存與 swap |
| query_cache_type/size | 1 / 128M | 高讀低寫場景有效；高寫入場景建議關閉（0） |
| join_buffer_size | 8M~32M (Session 級調大) | 無索引 Join 時的 Block Nested-Loop Buffer，每連線獨佔 |
| tmp_table_size/max_heap_table_size | 128M | 內部臨時表大小上限，超過轉磁碟 MyISAM |
| skip-name-resolve | ON | 禁用 DNS 反解，消除連線建立延遲 |
五、監控與持續優化
- 啟用
slow_query_log，long_query_time=1，定期分析 pt-query-digest。
- 監控
Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests 命中率（>99%）。
- 關注
Threads_connected、Threads_running、Aborted_connects。
- 定期
ANALYZE TABLE 更新統計資訊，避免優化器誤判。
- 大表分區、歸檔、冷熱分離，控制單表行數 < 2000 萬。
