MySQL 效能優化大全：硬體、作業系統、SQL、架構、維護五大面向

MySQL 優化是系統工程，涵蓋硬體選型、OS 調校、SQL 重寫、架構演進、日常維護五大層面。本文系統化彙整實戰精華。

一、硬體層優化

• CPU：大量運算/複雜查詢受益於高主頻、多核心；InnoDB 內部大量使用 64 位整數，**64 位 CPU 必選**。
• 記憶體：優先級最高。InnoDB Buffer Pool 設為物理記憶體 60-80%，足夠容納熱點索引與資料頁，減少磁碟 I/O。
• 磁碟：NVMe SSD 必選。隨機 I/O 能力決定交易吞吐。
  RAID 0 提升吞吐；RAID 1/10 兼顧安全；**避免 RAID 5/6**（寫入懲罰）。
  資料目錄、Redo Log、Binlog、暫存目錄**分盤掛載**並行 I/O。
• 網路：高併發場景建議 10GbE，啟用 TCP BBR 擁塞控制，調整 net.core.somaxconn、tcp_max_syn_backlog。

二、作業系統層

• 檔案系統：**XFS 或 ext4**（`noatime`、`nodiratime` 掛載選項減少元資料寫入）。
• 關閉 Swap 或設 vm.swappiness=1，避免 MySQL 記憶體被換出。
• 提高檔案描述符限制：ulimit -n 65535（寫入 /etc/security/limits.conf）。
• 啟用 Huge Pages（vm.nr_hugepages），減少 TLB Miss，InnoDB 大幅受益。
• I/O 排程器：**deadline 或 noop**（SSD），避免 cfq。
• Linux 開啟 hdparm -m16 -d1（DMA、多扇區傳輸）。

三、SQL 與結構層

四、架構層演進

• 讀寫分離：一主多從，主庫寫、從庫讀，中間件（ProxySQL、MyCat、ShardingSphere）路由。
• 分庫分表：水平拆分（Sharding）解決單表/單庫寫入瓶頸；垂直拆分隔離熱冷業務。
• 快取層：Redis/Memcached 吸收高頻讀請求，降低 MySQL 壓力。
• 異步處理：非核心寫入（日誌、通知）進 MQ（Kafka/RocketMQ）削峰填谷。

五、日常維護

• OPTIMIZE TABLE：MyISAM/InnoDB 碎片整理（InnoDB 相當於 ALTER TABLE ... ENGINE=InnoDB）。
• ANALYZE TABLE：更新索引統計資訊，優化器決策依據。
• CHECK TABLE / REPAIR TABLE：偵測/修復損壞。
• 監控指標：QPS、TPS、Threads_running、Innodb_row_lock_waits、Slow_queries、Buffer Pool Hit Rate。
• 備份策略：全備 + 增量 Binlog，定期演練恢復（RTO/RPO 驗證）。

避坑指南

• 不要在大表上直接 ALTER TABLE（鎖表久），用 pt-online-schema-change 或 gh-ost 線上變更。
• 不要用 OFFSET 1000000 LIMIT 10 深分頁。
• 不要在循環裡單條 INSERT，用批次 INSERT ... VALUES (),(),() 或 LOAD DATA。
• 不要忽略字元集不一致導致的隱性轉換（索引失效）。