讓 PHP 網站飛起來：MySQL 效能優化全維度指南（硬件、系統、SQL、架構）

前言

MySQL 優化不是單一維度的參數調整，而是硬件 → 作業系統 → 文件系統 → MySQL 服務器 → SQL/索引 → 應用架構的系統工程。本文從五大維度給出可落地的優化清單。

一、硬件優化：投資回報率最高的層面

• CPU：大表、複雜計算場景選高主頻、多核心（如 AMD EPYC / Intel Xeon Gold）；MySQL 內部大量 64 位運算，**64 位 CPU 必選**。
• 內存 (RAM)：優先級最高。InnoDB Buffer Pool 越大，熱數據命中率越高，磁盤 I/O 指數級下降。建議：專用 DB 服務器內存佔總數據量 20%+，或至少 64GB 起步。
• 存儲：NVMe SSD > SATA SSD > SAS HDD > SATA HDD。關鍵指標：IOPS（隨機 4K 讀寫）與延遲。RAID 10 平衡性能與冗余；避免 RAID 5/6 寫入懲罰。
• 網絡：萬兆網卡、低延遲交換機、啟用 Jumbo Frames (MTU 9000)，減少協議開銷。

二、磁盤與文件系統優化

• 分離掛載：數據目錄、Redo Log、Undo Log、Binlog、tmpdir 各自獨立掛載點（不同物理裝置/邏輯卷）。
• 文件系統：XFS / EXT4 (在 Linux)；格式化時 mkfs.xfs -f -i size=512 /dev/nvme0n1 加大 inode。
• 掛載選項：noatime,nodiratime,discard（SSD TRIM），減少元數據寫入。
• I/O 調度器：NVMe 用 none (noop)；HDD 用 deadline 或 bfq。
• Linux 內核參數：

  vm.swappiness = 1
  vm.dirty_ratio = 10
  vm.dirty_background_ratio = 5
  net.core.somaxconn = 65535
  fs.file-max = 1000000

三、MySQL 服務器核心參數 (InnoDB 專用)

[mysqld]
# 內存核心
innodb_buffer_pool_size = 0.7 * 物理內存
innodb_buffer_pool_instances = 8
innodb_log_file_size = 2G
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 20000          # NVMe IOPS
innodb_io_capacity_max = 40000
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_purge_threads = 4
innodb_page_cleaners = 4

# 連接與緩存
max_connections = 5000
thread_cache_size = 100
table_open_cache = 10000
table_definition_cache = 5000
open_files_limit = 1000000

# 查詢優化
optimizer_switch = 'mrr=on,mrr_cost_based=off,batched_key_access=on'
join_buffer_size = 2M
sort_buffer_size = 4M
read_rnd_buffer_size = 4M

# 安全與復制
sync_binlog = 1
binlog_format = ROW
gtid_mode = ON
enforce_gtid_consistency = ON
expire_logs_days = 7

# 字符集
character_set_server = utf8mb4
collation_server = utf8mb4_0900_ai_ci

四、SQL 與索引優化（最高 ROI，零成本）

1. 慢查詢日志：long_query_time=0.5，配合 pt-query-digest 定期分析 Top 10。
2. EXPLAIN 關鍵列：type (避免 ALL/index)、key (用到哪個索引)、rows (預估掃描行)、Extra (Using index/where/temporary/filesort)。
3. 索引設計原則：
   ① 最左前綴；
   ② 覆蓋索引 (Using index)；
   ③ 區分度高的欄位靠前；
   ④ 避免在索引列上用函數/運算/類型轉換；
   ⑤ 單表索引數 ≤ 5，聯合索引欄位 ≤ 5。
4. 常見改寫模式：
   IN (subquery) → EXISTS / JOIN
OR → UNION ALL
分頁 LIMIT 100000,20 → 延遲關聯 / 游標式 (WHERE id > last_id)
COUNT(*) → 近似值 SHOW TABLE STATUS / 觸發器維護計數表

五、架構層面：讀寫分離、分庫分表、緩存、異步化

六、總結

MySQL 優化無終點，只有持續迭代。記住優先級金字塔：
SQL/索引 (零成本、立竿見影) > 參數調優 (需重啟、效果邊際) > 硬件/架構 (成本高、上限高)。建立「慢查詢治理周報 → 參數基線版本化 → 壓測驗證 → 灰度發布」閉環，才能讓 PHP+MySQL 系統長期穩定「飛」起來。