MySQL EXPLAIN 执行计划详解：从入门到性能调优实战

EXPLAIN 是 MySQL 性能調優的「聽診器」。本文系統講解 EXPLAIN 輸出各欄位含義、type 存取類型優劣排序、Extra 關鍵提示解讀、組合索引最左前綴原則、慢查詢日誌配置與分析工具、MyISAM/InnoDB 鎖差異、事務提交策略，構建完整調優知識體系。

一、EXPLAIN 基本語法

EXPLAIN [EXTENDED] SELECT ... FROM ... WHERE ...;
-- EXTENDED 後可用 SHOW WARNINGS 查看優化後的重寫 SQL

二、輸出欄位深度解析

欄位	含義	關鍵判讀
id	SELECT 識別序號	相同 id 同組執行，越大越優先
select_type	查詢類型	SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION 等
table	引用表/衍生表	<derivedN> 表示衍生表
type	存取策略	system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
possible_keys	理論可用索引	NULL 表示無索引可用，需檢查 WHERE/ON 條件列
key	實際使用索引	NULL 為全表掃描，需優化
key_len	索引長度(字節)	越短越好；可推斷複合索引用了幾列
ref	與 key 配合的列/常數	const、func、NULL、列名
rows	預估掃描行數	InnoDB 為估算值；越小越好
Extra	額外資訊	見下表關鍵值解讀

三、Extra 關鍵值紅綠燈解讀

Extra 值	嚴重度	含義與對策
Using index	🟢 好	覆蓋索引，僅掃描索引樹，無需回表
Using where	🟡 一般	Server 層過濾；若 type=ALL/index 需加索引
Using index condition	🟢 好	ICP (Index Condition Pushdown)，存儲引擎層過濾
Using filesort	🔴 差	需額外排序；優化 ORDER BY 索引或增大 sort_buffer_size
Using temporary	🔴 差	建立臨時表；優化 GROUP BY/DISTINCT 或增大 tmp_table_size
Using join buffer	🟡 一般	Block Nested Loop Join；加索引轉為 ref/eq_ref
Impossible WHERE	🟢 好	WHERE 條件恆假，優化器直接返回空集
Select tables optimized away	🟢 好	聚合函數優化，無需掃描表

四、組合索引最左前綴原則實戰

建立 INDEX idx_a_b_c (a, b, c)：

WHERE 條件	是否用到索引	說明
a = 1	✅	最左列
a = 1 AND b = 2	✅	最左兩列
a = 1 AND c = 3	⚠️ 部分	僅用 a，c 斷層
b = 2	❌	非最左列，全表掃描
a = 1 ORDER BY b	✅	索引下推 + 排序免費
a = 1 ORDER BY c	⚠️ 部分	用 a 過濾，c 需 filesort

五、慢查詢日誌配置與分析工具鏈

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1          # 秒，建議 0.5~1
log_queries_not_using_indexes = 1
log_output = FILE

分析工具：

• mysqldumpslow：官方內建，聚合相似 SQL
• pt-query-digest (Percona Toolkit)：業界標準，多維度報表
• mysqlsla：老牌 Perl 工具，支援多種日誌格式
• mysqlreport：狀態計數器健康檢查

六、MyISAM vs InnoDB 鎖差異對調優的影響

• MyISAM：表鎖，UPDATE 期間 SELECT 全阻塞；適合唯讀/批量載入。
• InnoDB：行鎖，UPDATE 僅鎖涉及行，SELECT 不阻塞（MVCC）；高併發首選。

七、InnoDB 事務提交策略 innodb_flush_log_at_trx_commit

值	行為	效能	安全性	適用場景
1 (預設)	Commit 時 fsync 刷盤	最慢	全 ACID	核心交易、金融
2	Commit 寫 OS 緩存，每秒 fsync	快	掉電丟 1 秒	高併發、可容忍秒級丟失
0	每秒寫 OS 緩存並 fsync	最快	崩潰丟 1 秒	日誌、監控、非核心數據

八、調優實戰清單

1. EXPLAIN 確認 type ≥ ref，key 非 NULL，無 Using filesort/temporary。
2. 慢查詢日誌 + pt-query-digest 定位 Top 10 消耗 SQL。
3. 針對性加索引（覆蓋索引、最左前綴）、改寫 SQL（延遲關聯、子查詢轉 JOIN）。
4. 調整緩存參數（innodb_buffer_pool_size、sort_buffer_size、join_buffer_size）。
5. 監控 Innodb_buffer_pool_reads、Innodb_log_waits、Threads_created 趨勢。