MySQL 效能調優全攻略：從硬體、作業系統到 SQL 與架構設計

核心觀念：調優是系統工程

資料庫效能取決於硬體、作業系統、MySQL 設定、SQL 寫法、索引設計、應用架構多層面的協同。單一層面的優化效果有限，需建立整體觀。

一、常見瓶頸分類

1. 磁碟 I/O（尋道與傳輸）：傳統 HDD 平均尋道 10ms，每秒約 100 次隨機 I/O。SSD/NVMe 大幅改善。
2. CPU 運算：複雜查詢、排序、哈希連接、壓縮/加密運算消耗 CPU。
3. 記憶體頻寬：大型資料集超出 CPU Cache 時，記憶體頻寬成為瓶頸。
4. 鎖競爭：行鎖、表鎖、元資料鎖導致併發度下降。
5. 網路延遲/頻寬：跨機房、大結果集傳輸。

二、硬體選型建議

• CPU：高主頻、多核心（InnoDB 多執行緒受益於多核）。推薦 Intel Xeon / AMD EPYC 新世代。
• 記憶體：越大越好。InnoDB Buffer Pool 建議設為實體記憶體 60%-80%。避免 Swap。
• 儲存：務必使用 NVMe SSD。RAID 0 提升吞吐，RAID 1/10 提供冗餘。noatime 掛載選項減少寫入。
• 網路：10GbE 以上，單一資料庫叢集內低延遲交換器。

三、作業系統調優

# /etc/sysctl.conf
vm.swappiness = 1                    # 盡量不使用 swap
vm.dirty_ratio = 10
vm.dirty_background_ratio = 5
fs.file-max = 1000000
net.core.somaxconn = 65535
net.ipv4.tcp_max_syn_backlog = 65535

# /etc/security/limits.conf
mysql soft nofile 65535
mysql hard nofile 65535
mysql soft nproc 65535
mysql hard nproc 65535

• 檔案系統：XFS 或 ext4（掛載 noatime,nodiratime）。
• I/O 排程器：NVMe 建議 none 或 mq-deadline；HDD/SSD 用 deadline。
• 關閉 Transparent Huge Pages（THP）：echo never > /sys/kernel/mm/transparent_hugepage/enabled，避免記憶體延遲抖動。

四、MySQL 核心參數調優

InnoDB 關鍵設定

[mysqld]
# 記憶體
innodb_buffer_pool_size = 64G        # 實體記憶體 70% 左右
innodb_buffer_pool_instances = 16    # 1GB 一個 instance，減少鎖競爭
innodb_log_file_size = 4G            # 大 Redo Log 減少 checkpoint 壓力
innodb_log_files_in_group = 2
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1   # ACID 嚴格模式；可放寬為 2 提升寫入
innodb_flush_method = O_DIRECT       # 繞過 OS cache，避免雙重緩衝
innodb_file_per_table = ON
innodb_io_capacity = 20000           # NVMe 可設高；HDD 約 200
innodb_io_capacity_max = 40000
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_purge_threads = 4

# 連線與執行緒
max_connections = 2000
thread_cache_size = 100
table_open_cache = 4000
table_definition_cache = 2000

# 查詢快取（MySQL 8.0 已移除，舊版建議關閉）
query_cache_type = 0
query_cache_size = 0

# 二進位日誌
binlog_format = ROW
sync_binlog = 1
expire_logs_days = 7
max_binlog_size = 1G

# 字符集
character_set_server = utf8mb4
collation_server = utf8mb4_general_ci

五、SQL 與索引優化

1. EXPLAIN 分析執行計畫：關注 type（避免 ALL/index）、key、rows、Extra（Using where/Using temporary/Using filesort）。
2. 覆蓋索引：查詢欄位全在索引中，避免回表。
3. 最左前綴原則：複合索引 (a,b,c) 支援 a、a,b、a,b,c 查詢。
4. 避免函數運算在索引欄位：WHERE DATE(created_at) = '2024-01-01' → WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'。
5. 分頁優化：LIMIT 100000, 20 效能差，改用 WHERE id > last_id ORDER BY id LIMIT 20（游標式分頁）。
6. 只取需要的欄位：禁止 SELECT *。

六、架構層面擴展

• 讀寫分離：Master 寫、Slave 讀，中間層路由（ProxySQL、MyCat、ShardingSphere）。
• 分庫分表：水平切分大表，解決單表過大問題。
• 快取層：Redis 緩存熱點資料，降低 DB 壓力。
• 異步處理：寫入訊息佇列（Kafka/RocketMQ），消費端批次寫入 DB。
• 唯讀實例/從庫延遲監控：關鍵查詢強制走 Master，避免讀到舊資料。

七、監控與持續優化

• 指標：QPS、TPS、慢查詢數、連線數、Buffer Pool 命中率、InnoDB 行鎖等待、磁碟 I/O 使用率、Replication Lag。
• 工具：Prometheus + Grafana、Percona Monitoring and Management (PMM)、pt-query-digest 分析慢查詢日誌。
• 定期 ANALYZE TABLE 更新統計資訊，OPTIMIZE TABLE 整理碎片（InnoDB 重建表）。

結語

MySQL 調優沒有銀彈，只有持續觀測、找出瓶頸、針對性優化、驗證效果的工程循環。從硬體與 OS 基礎打好地基，再配合合理的參數、索引、SQL 與架構設計，才能榨乾 MySQL 的最大效能。