Linux 環境下 MySQL 伺服器效能優化全攻略：磁碟 I/O、硬體配置、核心參數調校

MySQL 效能優化不僅止於 SQL 語句與索引，作為 DBA/運維，必須掌握**作業系統層面、硬體資源、MySQL 核心參數**三大維度的系統級調優。本文針對 Linux 環境 (ext4/xfs、SSD/NVMe/HDD、NUMA 架構) 系統化整理 MySQL 伺服器優化實戰指南。

一、磁碟 I/O 子系統優化 (效能瓶頸首位)

1. 選擇正確的檔案系統與掛載參數

• 推薦： XFS (大檔案/高並發優勢) 或 ext4 (成熟穩定)
• 掛載選項 (關鍵)： noatime,nodiratime —— **關閉 atime 更新**，大幅減少元資料寫入 I/O

  /dev/nvme0n1p1 /var/lib/mysql xfs noatime,nodiratime 0 0

• 避免在資料目錄啟用 discard (即時 TRIM)，改用 fstrim 定時任務 (每週/每日)。

2. 磁碟調度器

• SSD/NVMe： none (no-op) 或 mq-deadline —— 繞過調度器直接下發，降低延遲

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

• HDD/RAID： deadline 或 bfq (桌面/混合負載)

3. RAID 策略與配置

建議： 資料目錄 RAID 10、Binlog/Redo Log 獨立 RAID 1 (或同 RAID 10 不同邏輯卷)、tmpdir 獨立 RAID 0 或 tmpfs (記憶體盤)。

4. 符號連結分離熱點檔案 (舊技巧，現建議用邏輯卷/獨立掛載點)

將 ibdata1、ib_logfile*、*.ibd (大表) 透過符號連結指向不同物理裝置，現代建議直接用 LVM 邏輯卷分別掛載，管理更清晰。

二、硬體資源配置

• 記憶體： 越大越好。InnoDB Buffer Pool 設為 **實體記憶體 60-80%**，避免 Swap。生產機嚴禁 Swap (或設 vm.swappiness=1、vm.zone_reclaim_mode=0)，必要時關閉 Swap 分區。
• CPU： 高主頻 > 多核心 (MySQL 單查詢單執行緒)，但高並發受益於多核。開啟 innodb_thread_concurrency=0 (自動)。
• 網路： 萬兆網卡 + 啟用 RSS/RPS/RFS、關閉巨型幀 (除非全鏈路支援)。
• NUMA 架構： 綁定 MySQL 進程至單一 NUMA 節點 (numactl --interleave=all 或 taskset)，避免跨節點記憶體存取延遲。

三、MySQL 核心參數調校 (my.cnf 重點)

[mysqld]
# === 基礎身分 ===
server-id = 1
port = 3306
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid
user = mysql
default-storage-engine = InnoDB
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# === 連線與安全 ===
max_connections = 2000
max_connect_errors = 100000
skip-name-resolve = 1           # 禁用 DNS 反解，加速連線建立
sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

# === InnoDB 核心 (效能心臟) ===
innodb_buffer_pool_size = 12G   # 實體記憶體 70-80%，專用機可到 80%
innodb_buffer_pool_instances = 8 # 1GB 一個實例，減少鎖競爭
innodb_log_file_size = 1G       # 大 Redo Log 減少 checkpoint 衝刷，重啟恢復稍慢可接受
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1  # ACID 嚴格模式，極致效能可設 2 (秒級丟失風險)
innodb_flush_method = O_DIRECT    # 繞過 OS Cache，避免雙重緩衝
innodb_file_per_table = 1         # 獨立表空間，利於空間回收與遷移
innodb_io_capacity = 4000         # SSD/NVMe IOPS 能力，HDD 設 200-500
innodb_io_capacity_max = 8000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_purge_threads = 4
innodb_thread_concurrency = 0     # 0 = 自動
innodb_lock_wait_timeout = 50
innodb_deadlock_detect = ON

# === 暫存表與排序 ===
tmp_table_size = 256M
max_heap_table_size = 256M
sort_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
join_buffer_size = 4M
# 注意：每連線記憶體 = sort+read+read_rnd+join，max_connections * 此和 < 總記憶體

# === Binlog (主從/備份必備) ===
log_bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 500M
sync_binlog = 1                  # 1 = 最安全，效能敏感可設 100-1000
binlog_cache_size = 4M

# === 慢查詢日誌 (調優利器) ===
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1              # 超過 1 秒記錄
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1

# === 其他 ===
open_files_limit = 65535
table_open_cache = 4000
table_definition_cache = 2000
max_allowed_packet = 64M
thread_cache_size = 100
query_cache_type = 0             # 5.7+ 建議關閉，8.0 已移除
query_cache_size = 0

四、系統層面核心參數 (/etc/sysctl.conf)

# 記憶體管理
vm.swappiness = 1
vm.dirty_ratio = 10
vm.dirty_background_ratio = 5
vm.zone_reclaim_mode = 0
vm.overcommit_memory = 1

# 檔案系統
fs.file-max = 1000000
fs.aio-max-nr = 1048576

# 網路
net.core.somaxconn = 65535
net.core.netdev_max_backlog = 5000
net.ipv4.tcp_max_syn_backlog = 65535
net.ipv4.tcp_tw_reuse = 1
net.ipv4.tcp_fin_timeout = 15

# 套用
sysctl -p

五、驗證與持續優化流程

1. 基準測試： sysbench / tpcc-mysql 建立基準線。
2. 關鍵指標監控： Innodb_buffer_pool_reads (磁碟讀取)、Innodb_log_waits (Redo Log 等待)、Innodb_row_lock_waits、Threads_connected、Slow_queries。
3. 定期分析 Slow Log： pt-query-digest 找出 Top N 慢查詢 → 索引優化 / SQL 改寫 / 分表分庫。
4. 容量規劃： 監控 data_free、table_rows、磁碟增長趨勢，提前擴容。

MySQL 系統級優化沒有銀彈，只有**「測量 → 找瓶頸 → 調整 → 驗證 → 循環」**。磁碟 I/O 子系統通常是第一瓶頸，其次是記憶體配置與 Buffer Pool 參數。建立標準化 my.cnf 模板、sysctl 基線、監控儀表板與 Slow Log 分析流程，才能讓優化從「玄學」變成「工程」。