前言
MySQL 調優是系統工程，涵蓋硬體、作業系統、配置、模式設計、查詢寫法、備份策略、監控體系。本文整理 101 條實戰鐵律，分類索引，供架構師、DBA、開發參考。注：具體數值需依實際負載基準測試校準。
一、硬體與作業系統層（1~24）
- 記憶體夠大：InnoDB 熱數據全入 `buffer_pool`，避免磁碟 I/O。
- 拒絕 Swap：`vm.swappiness=0`，Swap 發生即效能崩塌。
- 電池備援 RAID 卡：Write-back cache 安全落盤。
- RAID 10 首選：讀寫平衡、重建快、無校驗開銷。
- 禁用 RAID 5/6：寫入懲罰大，重建風險高。
- OS 與資料分盤：物理隔離，避免系統 I/O 干擾。
- Binlog/Relaylog/臨時目錄獨立盤：順序寫不干擾隨機讀。
- 磁碟容量預留 30%+：滿盤效能指數級下降。
- NVMe/SAS 優於 SATA：IOPS/延遲數量級差距。
- 小容量高轉速/企業級 SSD：單盤效能更穩定。
- RAID 卡 BBWC/FBWC 必裝：斷電不丟寫入緩存。
- 拒絕軟 RAID：CPU 佔用高、可靠性低。
- 考慮 PCIe SSD / Optane 存放 Binlog/Redo Log：微秒級延遲。
- Linux `vm.swappiness=0`：告訴核心「別換頁，寧可 OOM」。
- 掛載 `noatime,nodiratime`：減少存取時間寫入。
- XFS 檔案系統：大檔案/高並發優於 ext4，無雙緩衝問題。
- XFS 日誌/緩衝調優：`logbsize=256k` `inode64` `allocsize=16m`。
- I/O 排程器 `noop` 或 `deadline`：SSD/NVMe 下 `cfq`/`bfq` 反而慢。
- 64 位元 OS 強制：突破 4GB 記憶體限制。
- 精簡套件/服務：減少記憶體佔用、攻擊面。
- /etc/hosts 直解主機名：免 DNS 查詢延遲。
- 嚴禁 `kill -9 mysqld`：必損壞資料、破壞備份一致性。
- 專機專用：無其他服務搶 CPU/記憶體/磁碟。
- NUMA 親和性綁定：`numactl --interleave=all` 或 BIOS 關閉 NUMA。
- 透明大頁 (THP) 關閉：`echo never > /sys/kernel/mm/transparent_hugepage/enabled`，避免延遲抖動。
二、MySQL 配置層（25~39）
- `innodb_flush_method=O_DIRECT`：繞過 OS cache，避免雙緩衝（EXT4/XFS）。
- 勿混用 `O_DIRECT` + `ext3`：會序列化寫入。
- `innodb_buffer_pool_size` ≥ 熱數據集大小，獨立主機 60~80% 實體記憶體。
- `innodb_log_file_size` 適中（1~4GB）：過大延長恢復時間，過小頻繁 checkpoint。
- 勿同時設 `innodb_thread_concurrency` 與 `thread_concurrency`：互斥。
- `max_conservative`：依連線池大小設定，防 OOM（如 500~2000）。
- `thread_cache_size` ≥ 16（1G→8, 4G→32, >8G→64）：減少執行緒建立銷毀。
- `skip-name-resolve`：免 DNS 反解，授權表只用 IP。
- 查詢緩存：讀多寫少、重複高才開啟；寫入頻繁**必關**（`query_cache_size=0`）。
- `tmp_table_size` / `max_heap_table_size` 同步調大（如 256M~1G）：防臨時表落盤。
- `sort_buffer_size` 勿過大（1~4MB）：每連線獨佔，高併發爆記憶體。
- `key_buffer_size`（MyISAM 專用）：依 `Key_reads/Key_read_requests < 1/1000` 調整。
- `innodb_flush_log_at_trx_commit=2`：折中效能/安全，主庫建議 1，從庫 2。
- 擁有測試環境：配置變更必先壓測，再上線。
三、模式設計層（40~62）
- 保持資料庫整潔：歸檔冷數據、刪除無用索引/表。
- 冷熱分離：歷史數據移至歸檔表/分區/外部存儲。
- 該加索引就加：`WHERE`/`JOIN`/`ORDER BY`/`GROUP BY` 欄位。
- 勿過度索引：單表 ≤5 個，寫入開銷、優化器困惑。
- 壓縮 TEXT/BLOB：`COMPRESS()` 存、`UNCOMPRESS()` 取，省空間減 I/O。
- `utf8mb4` 效能略低於 `latin1`，但國際化必選，接受代價。
- 觸發器慎用：隱藏邏輯、難除錯、鎖擴大，應用層處理優先。
- 冗餘欄位最小化：以空間換時間需量化收益。
- 關聯表優於寬表：正規化減少重複、鎖衝突。
- 選最小充足型別：`TINYINT` < `SMALLINT` < `INT` < `BIGINT`。
- 大字段垂直拆分：主表瘦身，Buffer Pool 命中率雙贏。
- 定期 `OPTIMIZE TABLE` / `ANALYZE TABLE`：整理碎片、更新統計。
- InnoDB 定期 `ALTER TABLE ... ENGINE=InnoDB` 重建（Online DDL 5.6+）。
- 加欄位前先刪索引，再加回：避免拷貝全表索引。
- 按業務選引擎：InnoDB 通用、MyISAM 唯讀/全文、Archive 歸檔日志、Memory 熱點緩存。
- 歸檔表用 Archive 引擎：極高壓縮、僅支援 INSERT/SELECT。
- Session 存 Redis/Memcached，勿存 MySQL：自動過期、避免膨脹。
- `VARCHAR` 取代 `CHAR`（非定長）：節省空間，`utf8mb4` 下無差異。
- 模式變更漸進式：小步快跑，每步驗證。
- 開發環境鏡像生產數據：真實分佈才能暴露問題。
- 配置檔「少即是多」：只改有把握的參數，保留預設值。
- 懷疑時用官方預設配置：比亂調強。
四、查詢優化層（63~86）
- 開啟慢查詢日誌：`long_query_time=0.1` `log_queries_not_using_indexes=1`。
- 用 `EXPLAIN` 讀懂執行計畫：`type`、`key`、`rows`、`Extra`。
- 持續基準測試：性能會隨數據增長而退化。
- 避免全表 `COUNT(*)`：鎖表、掃描聚簇索引，用計數表/估算值。
- 查詢保持一致：同參數化、同大小寫，命中 Query Cache（若開啟）。
- `GROUP BY` 替代 `DISTINCT`：可利用索引排序，避免臨時表。
- `WHERE`/`GROUP BY`/`ORDER BY` 欄位加索引（複合索引最左前綴）。
- 索引保持簡單：不在多個索引重複包含同一欄位。
- 優化器誤判用 `USE INDEX`/`FORCE INDEX` 強制指定。
- `SQL_MODE=STRICT` 開啟：捕獲資料截斷/非法值。
- 小表 `UNION` 用 `LIMIT` 替代 `OR`：各分支可用不同索引。
- `INSERT ... ON DUPLICATE KEY UPDATE` 替代 `SELECT` 再 `UPDATE`：原子、少一次往返。
- 避免 `MAX()` 全掃描：索引欄位 + `ORDER BY DESC LIMIT 1`。
- 嚴禁 `ORDER BY RAND()`：臨時表+排序，災難級。
- `LIMIT M,N` 大偏移量慢：改用主鍵範圍 `WHERE id > M LIMIT N`。
- 子查詢改 `JOIN`/`UNION`：MySQL 子查詢優化弱（5.6+ 改善但仍弱）。
- 更新用 `LOCK IN SHARE MODE`/`FOR UPDATE` 控制鎖粒度。
- 重啟後預熱：`SELECT * FROM hot_table` 讓熱數據入 Buffer Pool。
- `TRUNCATE` 替代 `DELETE` 清空表：秒級、不產生 Undo、重置自增。
- 最小化投影：`SELECT` 只取需要欄位，`*` 消耗網路/CPU/緩存。
- 持久連線 + 連線池：減少握手/認證開銷。
- 基準測試含負載：單查詢快不代表高併發快。
- 高負載時 `SHOW PROCESSLIST` 找阻塞/慢查詢。
- 可疑查詢在鏡像環境驗證：不影響線上。
五、備份與恢復層（87~101）
- 從從庫備份：不影響主庫效能、避免鎖衝突。
- 備份期間停止從庫同步：`STOP SLAVE`，保證時間點一致性。
- 物理備份（關機/快照）：`xtrabackup`/`mariabackup` 熱備、不鎖表。
- `mysqldump` 備份必帶 Binlog：`--master-data=2` `--flush-logs`，增量恢復依賴。
- 不信任 LVM 快照：資料庫層面不一致，恢復風險大。
- 按表導出：單表恢復快、並行導入快、隔離故障。
- `mysqldump --opt`：預設開啟 `quick`、`lock-tables` 等優化組合。
- 備份前 `CHECK TABLE` + `OPTIMIZE TABLE`：確保來源健康。
- 導入時暫關 `FOREIGN_KEY_CHECKS=0` `UNIQUE_CHECKS=0`：提速 30%+。
- 每次備份後記錄表/索引大小：監控增長趨勢、預警容量。
- 自動化監控同步延遲/錯誤：`Seconds_Behind_Master`、`Last_SQL_Error`。
- 定期備份：全量每日、增量每小時（Binlog）、邏輯每周。
- 定期演練恢復：未測試的備份 = 沒備份。
- 執行 MySQL 監控：Prometheus + `mysqld_exporter` + Grafana 儀表板，全鏈路可觀測。
總結
101 條鐵律看似繁多，實則圍繞**「減少磁碟 I/O、減少鎖衝突、減少網路往返、減少 CPU 計算、增加可觀測性」**五大核心。調優無銀彈，唯有**建立基線 → 持續監控 → 針對性優化 → 驗證固化**的工程化流程，才能讓 MySQL 在業務增長中持續穩定高效運行。
