您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 效能優化全攻略：從硬體瓶頸到 SQL 重寫、參數調優、引擎選型的系統性方法論

編輯：MySQL 資料庫相關知識

前言：優化是系統工程，非單點突擊

資料庫效能受限於四大硬體資源：磁碟 I/O（尋道/吞吐）、CPU 運算、記憶體容量、網路頻寬。優化目標是將熱資料留在記憶體、減少磁碟存取、降低 CPU 無效運算。本文建立從硬體 → 參數 → SQL → 架構的四層優化模型。

第一層：硬體與作業系統基石

資源	瓶頸特徵	優化手段
磁碟尋道	隨機 I/O 7200 RPM 理論 7200 IOPS	RAID 10、SSD/NVMe、多磁碟分離數據/日誌/備份
磁碟吞吐	循序讀寫快	並行讀寫、大塊 I/O、檔案系統調優 (deadline/noop 調度器)
CPU	小表熱點運算	多核並行、查詢並行化、避免複雜函數在 WHERE 中
記憶體	緩衝區不足導致磁碟交換	大內存、關閉 Swap 或用 tmpfs、NUMA 親和性綁定

第二層：MySQL 服務參數調優

核心原則：一次只改一個參數、漸進式增大、測試環境驗證、參考官方範本 (my-small/medium/large/huge.cnf)。

關鍵參數速查表

參數	建議起始值	作用	調優信號
innodb_buffer_pool_size	物理記憶體 60-80%	InnoDB 數據+索引緩衝	Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests > 1%
innodb_log_file_size	512M - 2G	Redo Log 大小	頻繁 checkpoint、log 切換等待
innodb_log_buffer_size	16M - 64M	事務日誌緩衝	大事務提交慢
innodb_flush_log_at_trx_commit	1 (安全) / 2 (效能)	提交時 fsync 策略	2 輕微風險換高寫入吞吐
max_connections	500-2000	最大連線數	Threads_connected 接近上限
table_open_cache	4000+	表緩存	Opened_tables 快速增長
query_cache_type/size	OFF / 0	查詢緩存	MySQL 5.7+ 建議關閉，改用 Redis
sort_buffer_size	2M-8M	排序緩衝	Sort_merge_passes 高
join_buffer_size	2M-8M	JOIN 緩衝	Select_full_join 高
tmp_table_size / max_heap_table_size	64M-256M	臨時表記憶體上限	Created_tmp_disk_tables 高
thread_cache_size	100+	線程緩存	Threads_created 快速增長

第三層：SQL 與索引優化（收益最大）

1. 索引設計鐵律

最左前綴原則： 複合索引 (a,b,c) 僅支援 a / a,b / a,b,c 查詢。
覆蓋索引： SELECT 欄位全在索引中，避免回表。
區分度優先： 高基數欄位放前（性別<郵編<身份證<手機號）。
前綴索引： 長字串索引前 10-20 字符 (name(10))。
避免冗餘索引： (a) + (a,b) → 保留 (a,b)。

2. WHERE 條件重寫規則

-- 消除冗餘括號、常數折疊、常數傳遞
(a AND b) AND c OR (a AND b) AND (a AND d)
→ (a AND b AND c) OR (a AND b AND d)

-- 常數條件下推
WHERE a > 5 AND b = 5 → WHERE b = 5 AND a > 5  (b=5 先過濾)

3. JOIN 優化

小表驅動大表（驅動表結果集小）。
被驅動表關聯欄位必有索引。
優先 INNER JOIN，LEFT JOIN 驅動表在左。
避免 SELECT *，只取所需欄位（覆蓋索引機會）。

4. LIMIT 優化

LIMIT 1 等價於索引定位，極快。
LIMIT m,n 深分頁用「延遲關聯」或「ID 緩存」（參考文章 90981）。
LIMIT + ORDER BY 若排序欄位有索引，可避免 filesort。

5. DML 批次優化

-- INSERT 批次化
INSERT INTO t VALUES (...), (...), ...;  -- 單條 SQL 多行

-- 大量載入
LOAD DATA INFILE 'file.csv' INTO TABLE t;  -- 比 INSERT 快 20 倍

-- UPDATE/DELETE 分批、加鎖優化
LOCK TABLES t WRITE;  -- 批次操作前鎖表，索引緩衝區只刷一次盤

第四層：儲存引擎選型

引擎	特點	適用場景
InnoDB	事務、行鎖、外鍵、崩潰恢複、MVCC	預設首選，OLTP 核心業務
MyISAM	表鎖、全文索引、壓縮表、讀多寫少	唯讀報表、日誌、靜態查詢表
MEMORY (HEAP)	純記憶體、HASH 索引、重啟丟失	臨時查詢緩存、查找表、Session 存儲
Archive	僅支援 INSERT/SELECT、高壓縮	審計日誌、歷史歸檔
NDB Cluster	分散式、記憶體、同步複寫、99.999%	電信級計費、即時競價