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

MySQL 效能優化基礎篇（一）：架構認知、儲存引擎、鎖機制、硬體規劃、Query 優化核心原則

編輯：MySQL 資料庫相關知識

一、MySQL 核心適用場景

Web 應用系統（LAMP/LNMP 標準組件）
日誌記錄系統（歸檔、查詢）
數據倉庫/OLAP（配合列式引擎/分區）
嵌入式系統（輕量、零配置）

二、MySQL 架構分層

MySQL 架構圖

連接層： 連接池、認證、權限檢查、線程管理
服務層： 查詢快取、解析器、優化器、執行器、內建函數
引擎層： 儲存引擎介面（プラグイン架構）
儲存層： 檔案系統、原始裝置、網路儲存

三、主流儲存引擎對比

引擎	特點	鎖粒度	事務	適用場景
MyISAM	.frm/.MYD/.MYI 三檔，B-Tree/R-Tree/Fulltext 索引	表鎖	❌	唯讀/讀多寫少、全文檢索、COUNT(*) 極快
InnoDB	表空間、MVCC、行鎖、外鍵、自適應哈希索引	行鎖	✅ ACID	高併發 OLTP、需事務、混合讀寫
Memory	純記憶體、Hash/B-Tree 索引、不支援 BLOB/TEXT	表鎖	❌	臨時表、查找表、極速讀取、重啟資料消失
NDB Cluster	分散式、共享無、自動分片、同步複寫	行鎖	✅	高可用叢集、電信級應用
Archive	壓縮存儲、僅支援 INSERT/SELECT、行鎖	行鎖	❌	歷史日誌、審計軌跡、極少讀取
CSV	標準 CSV 檔案、無索引	表鎖	❌	報表匯出、異構交換
Blackhole	「黑洞」，寫入不存儲、主從同步中繼	無	❌	Binlog 過濾、多源同步轉發
Federated	遠程表映射、類 Oracle DBLink	遠程	❌	跨實例查詢、遷移過渡期
Merge	多 MyISAM 表聯合視圖、結構必須完全一致	表鎖	❌	分區表前身、日誌分表查詢
BDB	BerkeleyDB、頁鎖、支援事務	頁鎖	✅	歷史產品，已少見

四、影響效能的五大維度

1. 業務需求

例：論壇貼文計數「即時更新」vs「定時批次」——前者需高併發寫入優化，後者可接受延遲。

2. 系統架構

不適合存入 DB： 二進位多媒體、流水隊列、超大文本。
適合 Cache： 系統配置、活躍用戶資料、個性化設定、準實時統計。

3. Query 語句

同樣需求，不同寫法效能相差百倍。核心案例：

-- 方案一：大表先 JOIN 再 LIMIT（掃描 3 萬行）
SELECT id, nick_name FROM user, user_group 
WHERE user_group.group_id=1 AND user_group.user_id=user.id 
ORDER BY user_group.gmt_create DESC LIMIT 100,20;

-- 方案二：先在小表取 ID 再 JOIN（僅 20 行參與 JOIN）
SELECT user.id, user.nick_name 
FROM (SELECT user_id FROM user_group WHERE group_id=1 ORDER BY gmt_create DESC LIMIT 100,20) t
JOIN user ON t.user_id = user.id;

方案二經優化器驗證，rows 從 31156 降至 20，效能提升數量級。

4. Schema 設計

減少 DB 訪問請求（批次、快取）
減少無效資料掃描（精準索引、分區）

5. 硬體環境

應用類型	CPU	記憶體	磁碟 IOPS	磁碟吞吐	網路
OLTP（高併發、小隨機 IO）	強多核	大（熱數據全記憶體）	極高（SSD/NVMe）	次要	千兆以上
OLAP（大順序掃描、低併發）	中等	中等	中等	極高（大頻寬 RAID/分散式）	集群內萬兆
高頻小查詢（DNS/查找服務）	強單核	極大（全量記憶體）	低	低	千兆

五、鎖機制速查

鎖類型	引擎代表	特點
表鎖	MyISAM, Memory, CSV	開銷最小、併發最差、死鎖概率零
行鎖	InnoDB, NDB	併發最高、開銷大、可能死鎖
頁鎖	BDB	折中、易產生碎片

六、Query 優化十大核心原則

優化最需要優化的 Query（Top SQL）
定位性能瓶頸（CPU/IO/鎖/網路）
設定明確優化目標（RT/QPS/吞吐）
從 EXPLAIN 入手分析執行計畫
多用 PROFILE / PERFORMANCE_SCHEMA 精準計時
永遠用小結果集驅動大結果集
盡可能在索引中完成排序（ORDER BY 用索引序）
只取需要的 Columns（拒絕 SELECT *）
僅用最高效過濾條件（避免冗餘 JOIN/子查詢）
合理設計並利用索引（B-Tree/Hash/Fulltext）

索引類型深度解析

B-Tree： 所有引擎通用，範圍/排序/分組/前綴匹配皆可用。InnoDB 實為 B+Tree（葉節點雙向鏈表，利於範圍掃描）。
Hash： Memory 預設，僅支援 =/IN/<=>，無序、不支援範圍、不支援部分鍵、遇大量碰撞效能劣化。
Fulltext： MyISAM 專屬（5.6+ InnoDB 也支援），解決 LIKE '%kw%' 低效，僅 CHAR/VARCHAR/TEXT 可建。