MySQL 資料庫效能調優全攻略：從硬體選型到架構擴展

MySQL 資料庫效能調優全攻略：從硬體選型到架構擴展

資料庫效能調優是系統工程，非單一參數調整即可解決。本文依據六年一線城市核心業務實戰經驗，從硬體、安裝、配置、運行期監控、架構擴展五個維度，構建完整調優知識體系。

一、伺服器硬體選型：投資回報率最高的三大項

1. 磁碟 I/O（核心瓶頸）
   • 推薦：SAS 15K RPM × 6 做 RAID 10（RAID 5 寫入懲罰嚴重，不建議）。
   • 預算允許：NVMe SSD 做 RAID 1/10，隨機 IOPS 提升 10-50 倍。
   • 關鍵指標：iostat -x 1 觀察 %util、await、svctm。
2. CPU 運算能力
   • 推薦：雙路 8-16 核心（如 Intel Xeon Scalable / AMD EPYC），高主頻優於多核心。
   • MySQL 單查詢單執行緒，高主頻直接降低延遲。
3. 記憶體容量
   • 最低 32GB，生產建議 128GB~256GB。
   • InnoDB Buffer Pool 佔物理記憶體 60%~75%，剩餘留給 OS Cache、連線緩衝、排序區。

機型參考：Dell R740/R750、HP DL380 Gen10、自組 Supermicro 雙路機。

二、編譯安裝：榨乾效能的關鍵步驟

cmake . \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_DATADIR=/data/mysql \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_PARTITION_STORAGE_ENGINE=1 \
-DWITH_SSL=system \
-DWITH_ZLIB=system \
-DDEFAULT_CHARSET=utf8mb4 \
-DDEFAULT_COLLATION=utf8mb4_general_ci \
-DWITH_DEBUG=0 \                 # 關閉 Debug，效能提升 10%+
-DCMAKE_C_FLAGS="-O3 -march=native" \
-DCMAKE_CXX_FLAGS="-O3 -march=native"

• 靜態編譯 -DWITH_MYSQLD_LDFLAGS=-all-static 可再獲 5%~10% 提升，但升級維護較麻煩。
• OS 建議：CentOS 7/8、Ubuntu 20.04/22.04 LTS，Kernel 4.18+ 支援更好的 NUMA 平衡。

三、my.cnf 核心參數精解（以 128GB 記憶體、SSD RAID10 為例）

[mysqld]
# 基礎
user = mysql
port = 3306
socket = /data/mysql/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql/data
character-set-server = utf8mb4
skip-name-resolve
skip-host-cache
explicit_defaults_for_timestamp = 1

# 連線
max_connections = 3000
thread_cache_size = 256
back_log = 512
max_connect_errors = 100000
wait_timeout = 600
interactive_timeout = 600

# 記憶體 - 全域
key_buffer_size = 64M              # MyISAM 索引，極小即可
query_cache_type = 0               # 8.0 已移除，5.7 關閉避免鎖爭用
query_cache_size = 0

# 記憶體 - InnoDB 核心
innodb_buffer_pool_size = 90G      # 物理記憶體 70%
innodb_buffer_pool_instances = 16  # 1GB/instance，減少鎖競爭
innodb_log_file_size = 4G          # 大紅日誌減少 checkpoint 壓力
innodb_log_files_in_group = 4
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1 # ACID 嚴格模式，金融業務必須
innodb_flush_method = O_DIRECT     # 繞過 OS Cache，SSD 必開
innodb_file_per_table = 1
innodb_page_size = 16384           # 預設 16K，壓縮表可改 8K/4K
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 20000         # SSD IOPS 上限
innodb_io_capacity_max = 40000

# 並行控制
innodb_thread_concurrency = 0      # 0 = 無限制，現代版本自適應更好
innodb_purge_threads = 4
innodb_adaptive_hash_index = 1

# 臨時表與排序
tmp_table_size = 256M
max_heap_table_size = 256M
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 8M

# Binlog 與複寫
log_bin = /data/mysql/binlog/mysql-bin
binlog_format = ROW
binlog_row_image = MINIMAL
expire_logs_days = 7
sync_binlog = 1
gtid_mode = ON
enforce_gtid_consistency = ON
log_slave_updates = 1

# 慢查詢
slow_query_log = 1
slow_query_log_file = /data/mysql/log/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 1
log_throttle_queries_not_using_indexes = 60

# 其他
open_files_limit = 65535
table_open_cache = 4000
table_definition_cache = 2000
max_allowed_packet = 64M

四、上線後依據 STATUS 微調：十大黃金指標

工具推薦：mysqldumpslow -s c -t 20 slow.log、pt-query-digest、Prometheus + Grafana + mysqld_exporter。

五、架構擴展：單機極限後的出路

1. 讀寫分離（一主多從）
   • 中間件：ProxySQL、MaxScale、ShardingSphere-Proxy。
   • 從庫啟用 log_slave_updates、read_only=1。
   • 半同步複寫 rpl_semi_sync_master=1 保證核心資料不丟失。
2. 分庫分表
   • 垂直拆分：業務模組解耦，不同資料庫實例。
   • 水平分表：Sharding Key 選型（user_id、tenant_id、date），中間件路由。
   • 工具：Vitess、ShardingSphere、自研 Proxy。
3. 高可用架構
   • MHA / Orchestrator / GTID-based Failover（主從自動切換）。
   • PXC (Percona XtraDB Cluster) / Group Replication：多主同步，零資料損失，但寫入擴展受限。
   • DRBD + Heartbeat / Keepalived：塊級同步，強一致，效能較差。

六、調優心法：治本在於架構與代碼

「參數調優帶來的提升通常是線性的，架構優化與 SQL 改寫才能帶來數量級飛躍。」

• 開發階段：EXPLAIN 必須成為 Code Review 標準動作。
• 索引設計：遵循最左前綴、覆蓋索引、避免冗餘索引。
• 大事務拆分、避免長鎖定、善用 INSERT ... ON DUPLICATE KEY UPDATE。
• 監控先行：沒有監控的調優都是盲目嘗試。