MySQL 效能調優全景圖：從硬體、架構、OS、參數到 SQL 的系統化優化體系

調優金字塔：自下而上、分層推進

L7  SQL 與索引優化          ← 最高 ROI，最常見
L6  my.cnf 參數調優
L5  資料庫服務配置（引擎、編譯、大表拆分）
L4  OS 層面（檔案系統、限制、服務精簡）
L3  架構層（讀寫分離、分庫分表、快取層）
L2  單機優化（服務配置、實例隔離）
L1  硬體選型（CPU、記憶體、磁碟、網路）  ← 基石

L1 硬體選型鐵律

L2-L3 架構演進路徑

1. 縱向拆分：Web / Middleware / DB 分離 → DB 專用機。
2. 引入快取：Memcached/Redis 前置，吸收 80%+ 讀請求。
3. 橫向拆分：Master-Slave 讀寫分離 → 多 Slave 負載均衡 → 分庫分表 → 高可用叢集（MHA/Orchestrator/Group Replication）。

L4 OS 級優化清單

• 64 位 OS：突破 32 位 2-3.5GB 進程記憶體限制。
• 關閉無用服務：systemctl disable postfix chronyd firewalld ...。
• 提高檔案描述符：/etc/security/limits.conf mysql soft nofile 65535; mysql hard nofile 65535。
• 檔案系統選 XFS/ext4：XFS 大檔案、高併發表現更佳；掛載加 noatime,nodiratime 減少元資料寫入。
• 關閉 Transparent Huge Pages：echo never > /sys/kernel/mm/transparent_hugepage/enabled，避免記憶體分配延遲抖動。
• I/O 調度器：SSD 用 noop/none，HDD 用 deadline。

L5 資料庫服務配置

• 源碼編譯：-DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 ... 精簡模組。
• 引擎選型：
  • MyISAM：表鎖、低開銷、適合唯讀/讀多寫少、不支援事務。
  • InnoDB：行鎖、MVCC、支援事務、崩潰恢復、適合寫入頻繁、業務核心表。
• 大表拆分：
  • 垂直拆分：將大欄位（TEXT/BLOB/長 VARCHAR）移至副表，主表只存高頻小欄位。
  • 水平拆分：按時間/Hash/Range 分表或分庫，單表控制在 2000-5000 萬行。

L6 my.cnf 核心參數分類速查

L7 SQL 與索引優化核心清單

1. 建表即設計索引：EXPLAIN 驗證 type=ref/range/eq_ref、Extra=Using index。
2. 最左前綴原則：複合索引 (a,b,c) 支援 a/a,b/a,b,c 查詢。
3. 覆蓋索引：SELECT a,b FROM t WHERE c=1 → INDEX (c,a,b) 避免回表。
4. 避免函數/運算在索引列：WHERE DATE(created_at)='2024-01-01' → WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'。
5. 分頁優化：LIMIT 100000,10 → 子查詢 WHERE id > (SELECT id FROM t ORDER BY id LIMIT 100000,1) 或游標式 WHERE id > last_id LIMIT 10。
6. 定期 ANALYZE TABLE 更新統計資訊，防止優化器誤判。

L8 診斷工具箱

調優無終點，唯有持續度量、假設、驗證、固化。建立基準線，設定 SLA，自動化巡檢，讓資料庫永遠在最佳狀態服務業務。