MySQL 效能優化大全：從硬體、作業系統、SQL 到架構的 35 條實戰技巧

優化維度總覽

資料庫效能優化是系統工程，涵蓋五大層面：

1. 硬體（CPU、記憶體、磁碟、網路）
2. 作業系統/軟體庫
3. MySQL 服務器配置與 SQL 語句
4. 應用程式介面 (API) 選擇
5. 應用程式架構設計

一、硬體選型與配置

1.1 CPU：大表首選 64 位

表 > 2GB 時，MySQL 大量使用 64 位整數運算，Alpha/Sparc/IA-64/x86-64 顯著優於 32 位。

1.2 記憶體：越大越好

優化順序：RAM → 快速磁碟 → CPU。更多記憶體 = 更多熱點索引頁緩存 = 更少磁碟 I/O。

1.3 磁碟 I/O 策略

• 系統盤、程式盤、臨時檔、Binary Log、Redo Log 分盤掛載。
• RAID 0 提升吞吐；RAID 1/10 兼顧安全與讀效能；臨時檔/可重建資料勿用 RAID 1/5。
• Linux 掛載參數 async,noatime；hdparm -m16 -d1 開啟多扇區/DMA，效能提升 5~50%。
• 極大表可用符號連結將資料庫/表分散至不同物理磁碟。

1.4 網路

專用資料庫服務器建議 1 Gbps 乙太網，低延遲同等重要。

二、作業系統調校

• 避免 Swap：記憶體不足優先加記憶體，或降低 innodb_buffer_pool_size 等緩存參數。
• 禁用 NFS 掛載資料目錄（鎖定問題）。
• 提高 ulimit -n（開啟檔案數）、max user processes（執行緒數）。
• 檔案系統選型：Linux 推薦 XFS/ext4（大檔案、快速 fsck）；Solaris 用 UFS+logging 或 ZFS。

三、API / 連接器選擇

建議：核心高頻路徑用 C/PHP 原生驅動；異構報表用 ODBC/JDBC。

四、應用層優化原則

1. 使用持久連線（連線池），減少握手開銷。
2. 應用層緩存熱點資料（Redis/Memcached），降低 DB QPS。
3. 嚴禁 SELECT *，僅查所需欄位。
4. 批次寫入用 LOCK TABLES + 多行 INSERT，減少交互往返。
5. 壓測鎖定瓶頸，模組化替換熱點邏輯。

五、可移植應用設計

• 僅使用 ANSI SQL 共同子集或易模擬構造。
• 參考 mysql.com 的 crash-me 測試相容性。
• 為缺失功能編寫 OS/DB 包裝層。

六、MySQL 服務器核心參數調優

七、SQL 與 Schema 設計優化

7.1 索引策略

• 「索引加速讀、拖慢寫」——只建業務真正用到的索引。
• 複合索引遵循「最左前綴」，高選擇性列靠前。
• 長字串列用前綴索引：INDEX(name(10))。
• 覆蓋索引：查詢僅需索引列時避免回表。

7.2 表設計

• 定長行（無 VARCHAR/BLOB/TEXT）優於動態行：速度快、不易碎片、安全。
• 用 PROCEDURE ANALYSE() 建議最優類型。
• NOT NULL + DEFAULT 取代 NULL，利於索引與統計。
• 大表分區/分表；摘要表預聚合替代即時 GROUP BY。
• InnoDB 取代 MyISAM（事務、行鎖、崩潰恢復）。

7.3 查詢改寫技巧

• LIMIT m,n 分頁；DELETE ... LIMIT 批次刪除。
• INSERT ... ON DUPLICATE KEY UPDATE / REPLACE 合併寫入邏輯。
• LOAD DATA INFILE 批量導入比 INSERT 快 20 倍。
• 子查詢改寫為 JOIN（MySQL 優化器對 JOIN 更成熟）。
• 用 EXPLAIN 分析 type=ALL/index、Extra=Using temporary/filesort 等危險信號。

八、維護與監控

• 定期 OPTIMIZE TABLE（動態行表）、ANALYZE TABLE 更新統計。
• myisamchk -a 重建索引統計（先停庫）。
• 碎片嚴重時離線拷貝重建。
• mysqladmin -i10 processlist extended-status 監控；或用 MySQL Workbench/GUI。

九、進階功能與新特性（MySQL 5.0+）

• Partitioning：大表水平切分。
• Replication：讀寫分離、備份、高可用。
• Event Scheduler：替代 cron 定時任務。
• Query Cache（5.7 移除）：重複唯讀查詢加速。
• ICC 編譯：Linux/Intel 平台效能 +10~20%。
• --with-mysqld-ldflags=-all-static 靜態編譯 + strip 精簡二進制。

十、避坑清單

1. 大量刪除 + 長查詢並發 → 鎖爭用。
2. HAVING 用於非聚合欄位（應移至 WHERE）。
3. JOIN 條件欄位類型不一致（隱式轉換導致索引失效）。
4. HEAP 表非「=」條件查詢、或對 HEAP 表 ORDER BY。
5. 忘記 WHERE 執行 UPDATE/DELETE（用 --i-am-a-dummy 防呆）。

結語

優化無終點，唯有「找瓶頸 → 針對性調優 → 驗證 → 迭代」。以 EXPLAIN、SHOW STATUS、慢查詢日誌為三大儀器，配合業務增長節奏，持續演進架構與參數，方能長治久安。