MySQL?EXPLAIN用法實(shí)例深度詳解
一、什么是 EXPLAIN
EXPLAIN 是 MySQL 提供的一個(gè)用于分析 SQL 語(yǔ)句執(zhí)行計(jì)劃的強(qiáng)大工具。通過(guò)它，我們可以了解 MySQL 查詢優(yōu)化器是如何執(zhí)行 SQL 語(yǔ)句的，包括表的讀取順序、索引使用情況、掃描行數(shù)等關(guān)鍵信息，從而幫助我們定位和優(yōu)化性能瓶頸。
版本說(shuō)明：本文基于 MySQL 5.7+ 和 8.0+ 版本。EXPLAIN ANALYZE 和 Hash Join 特性需要 MySQL 8.0.18+ 和 8.0.20+。
二、基本語(yǔ)法
2.1 標(biāo)準(zhǔn)用法
EXPLAIN SELECT * FROM table_name WHERE condition;
2.2 支持的語(yǔ)句類型
EXPLAIN 支持以下語(yǔ)句：
SELECTDELETEINSERTREPLACEUPDATE
2.3 MySQL 8.0+ 新增用法
-- 實(shí)際執(zhí)行并分析耗時(shí)（MySQL 8.0.18+） EXPLAIN ANALYZE SELECT * FROM users WHERE age = 25; -- JSON 格式輸出（包含成本模型數(shù)據(jù)） EXPLAIN FORMAT=JSON SELECT * FROM users WHERE age = 25;
三、EXPLAIN 輸出字段詳解
執(zhí)行 EXPLAIN 后，MySQL 會(huì)返回一個(gè)結(jié)果集，包含以下列：
字段 | 含義 |
id | 查詢標(biāo)識(shí)符，表示執(zhí)行順序 |
select_type | 查詢類型（SIMPLE、PRIMARY、SUBQUERY 等） |
table | 訪問(wèn)的表名 |
partitions | 匹配的分區(qū)（MySQL 5.7+） |
type | 訪問(wèn)類型（ALL、index、range、ref、eq_ref 等） |
possible_keys | 可能使用的索引 |
key | 實(shí)際使用的索引 |
key_len | 使用的索引長(zhǎng)度 |
ref | 與索引比較的列或常量 |
rows | 估算需要掃描的行數(shù) |
filtered | 按條件過(guò)濾后剩余行的百分比（MySQL 5.7+） |
Extra | 額外信息（非常重要） |
四、核心字段深度解析
4.1 id 列 - 執(zhí)行順序標(biāo)識(shí)
規(guī)則：
- id 相同：從上往下順序執(zhí)行
- id 不同：id 值越大，優(yōu)先級(jí)越高，越先執(zhí)行
- id 為 NULL：最后執(zhí)行（通常是 UNION 結(jié)果合并）
-- 示例：子查詢 EXPLAIN SELECT * FROM test1 WHERE id IN (SELECT id FROM test2); -- 結(jié)果中 id=2 的子查詢會(huì)先執(zhí)行，id=1 的主查詢后執(zhí)行
4.2 select_type 列 - 查詢類型
類型 | 說(shuō)明 |
SIMPLE | 簡(jiǎn)單查詢，不包含子查詢或 UNION |
PRIMARY | 最外層查詢 |
SUBQUERY | SELECT 或 WHERE 中的子查詢 |
DERIVED | FROM 中的子查詢（派生表） |
UNION | UNION 中的第二個(gè)及后續(xù)查詢 |
UNION RESULT | UNION 結(jié)果合并 |
4.3 type 列 - 訪問(wèn)類型（性能關(guān)鍵）
性能從優(yōu)到劣排序：
類型 | 說(shuō)明 |
system | 不進(jìn)行磁盤(pán)IO,查詢系統(tǒng)表,僅僅返回一條數(shù)據(jù) |
const | 通過(guò)主鍵或唯一索引一次就找到 |
eq_ref | 連接查詢中，被驅(qū)動(dòng)表使用主鍵/唯一索引等值匹配 |
ref | 使用普通索引等值匹配 |
range | 索引范圍掃描（BETWEEN、IN、>、< 等） |
index | 遍歷整顆索引樹(shù),比ALL快一些,因?yàn)樗饕募葦?shù)據(jù)文件小 |
ALL | 全表掃描 |
優(yōu)化建議：至少達(dá)到 range 級(jí)別，最好達(dá)到 ref 或 eq_ref。
4.4 Extra 列 - 額外信息
這是最重要的優(yōu)化線索列：
值 | 含義 | 優(yōu)化建議 |
Using index | 使用覆蓋索引 | 理想狀態(tài)，無(wú)需回表 |
Using where | 使用 WHERE 過(guò)濾（全表掃描或者在查找使用索引的情況下，但是還有查詢條件不在索引字段當(dāng)中） | 正常情況 |
Using filesort | 使用外部排序（無(wú)法利用索引排序） | 需要優(yōu)化，考慮添加索引 |
Using temporary | 使用臨時(shí)表來(lái)存儲(chǔ)結(jié)果集，常見(jiàn)于排序和分組查詢 | 常見(jiàn)于 GROUP BY / ORDER BY，需優(yōu)化 |
Using join buffer | 使用連接緩存 | 連接條件未使用索引 |
Impossible WHERE | WHERE 條件永遠(yuǎn)為 false | 檢查邏輯 |
Select tables optimized away | 優(yōu)化器確定最多返回一行 | 無(wú)需優(yōu)化 |
五、實(shí)戰(zhàn)案例
5.1 單表查詢分析
-- 表結(jié)構(gòu)：users(id, age, score, name, address) -- 索引：idx_age_score_name(age, score, name) EXPLAIN SELECT * FROM users WHERE age = 25;
結(jié)果分析：
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | users | NULL | ref | idx_age_score_name | idx_age_score_name | 5 | const | 12 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+------+----------+-------------+
解讀：
type=ref：使用普通索引等值匹配key=idx_age_score_name：實(shí)際使用了聯(lián)合索引Extra=Using index：覆蓋索引，無(wú)需回表查詢rows=12：只需掃描 12 行
5.2 連接查詢分析
EXPLAIN SELECT * FROM test1 t1 INNER JOIN test2 t2 ON t1.id = t2.id;
關(guān)鍵觀察點(diǎn)：
- 查看哪個(gè)表是驅(qū)動(dòng)表（通常 rows 小的作為驅(qū)動(dòng)表更優(yōu)）
- 被驅(qū)動(dòng)表的 type 應(yīng)該為
eq_ref（使用主鍵/唯一索引）
5.3 使用 EXPLAIN ANALYZE（MySQL 8.0.18+）
EXPLAIN ANALYZE SELECT * FROM users WHERE age = 25\G
輸出示例：
*************************** 1. row *************************** EXPLAIN: -> Covering index lookup on users using idx_age_score_name (age=25) (cost=1.52 rows=12) (actual time=0.0272..0.0344 rows=12 loops=1)
優(yōu)勢(shì)：
- 顯示實(shí)際執(zhí)行時(shí)間（actual time）
- 顯示實(shí)際返回行數(shù)（rows）
- 比標(biāo)準(zhǔn) EXPLAIN 的估算數(shù)據(jù)更可靠
六、常見(jiàn)優(yōu)化場(chǎng)景
6.1 避免全表掃描（type = ALL）
問(wèn)題診斷：
- 查詢條件列沒(méi)有索引
- 查詢使用函數(shù)導(dǎo)致索引失效
- 多表 JOIN 驅(qū)動(dòng)表選擇不合理
優(yōu)化方法：
-- 錯(cuò)誤：函數(shù)包裝導(dǎo)致索引失效 SELECT * FROM orders WHERE YEAR(order_date) = 2023; -- 正確：改寫(xiě)為范圍查詢 SELECT * FROM orders WHERE order_date >= '2023-01-01' AND order_date < '2024-01-01';
6.2 消除文件排序（Using filesort）
-- 添加合適的索引避免 filesort CREATE INDEX idx_age_name ON users(age, name); -- 查詢同時(shí)滿足 WHERE 和 ORDER BY SELECT * FROM users WHERE age > 20 ORDER BY age, name;
6.3 利用覆蓋索引
-- 索引：idx_age_name(age, name) -- ? 覆蓋索引查詢（Extra = Using index） EXPLAIN SELECT age, name FROM users WHERE age = 25; -- ? 非覆蓋索引（需要回表查詢） EXPLAIN SELECT * FROM users WHERE age = 25;
七、EXPLAIN 的局限性
需要注意 EXPLAIN 的以下限制：
- 不會(huì)告訴你關(guān)于觸發(fā)器、存儲(chǔ)過(guò)程的信息
- 不考慮各種 Cache（查詢緩存等）
- 不能顯示 MySQL 在執(zhí)行查詢時(shí)所作的優(yōu)化工作
- 部分統(tǒng)計(jì)信息是估算的，并非精確值
- 標(biāo)準(zhǔn) EXPLAIN 不會(huì)真正執(zhí)行 SQL（除 EXPLAIN ANALYZE 外）
八、總結(jié)
檢查項(xiàng) | 優(yōu)化目標(biāo) |
type | 至少達(dá)到 range，最好 ref 或 eq_ref |
key | 確保實(shí)際使用了索引 |
rows | 越小越好 |
Extra | 避免出現(xiàn) Using filesort、Using temporary |
覆蓋索引 | 盡量讓 Extra 顯示 Using index |
掌握 EXPLAIN 的使用是 SQL 性能優(yōu)化的基礎(chǔ)技能。通過(guò)分析執(zhí)行計(jì)劃，我們可以快速定位性能瓶頸，有針對(duì)性地進(jìn)行索引優(yōu)化和 SQL 改寫(xiě)
到此這篇關(guān)于MySQL EXPLAIN用法的文章就介紹到這了,更多相關(guān)MySQL EXPLAIN用法內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
這篇文章主要介紹了MySQL 8.0.13 下載安裝教程,本文圖文并茂給大家介紹的非常詳細(xì)，具有一定的參考借鑒價(jià)值，需要的朋友可以參考下2018-11-11
MySQL是一個(gè)關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，由瑞典MySQL AB公司開(kāi)發(fā)，目前屬于Oracle旗下產(chǎn)品。這篇文章主要介紹了MySQL 5.7.20綠色版安裝詳細(xì)圖文教程,需要的朋友可以參考下2017-11-11
MySQL深分頁(yè),limit 100000,10優(yōu)化方式
MySQL中深分頁(yè)查詢因需掃描大量數(shù)據(jù)行導(dǎo)致效率低下,優(yōu)化方法包括子查詢優(yōu)化、延遲關(guān)聯(lián)、標(biāo)簽記錄法和使用between...and...等,通過(guò)減少回表次數(shù)和范圍掃描提升查詢性能,覆蓋索引幫助減少搜索次數(shù),提升性能2024-10-10
這篇文章主要介紹了幾種在Linux中找到MySQL的安裝目錄方法,包括使用which命令、whereis命令、檢查服務(wù)狀態(tài)、直接查詢MySQL以及查閱配置文件,文中通過(guò)代碼介紹的非常詳細(xì),需要的朋友可以參考下2025-04-04
mysql給id設(shè)置默認(rèn)值為UUID的實(shí)現(xiàn)方法
由于mysql并不支持默認(rèn)值為函數(shù)類型,給id設(shè)值有兩種方式,本文主要介紹了mysql給id設(shè)置默認(rèn)值為UUID的實(shí)現(xiàn)方法,具有一定的參考價(jià)值,感興趣的可以了解一下2023-08-08
mysql啟動(dòng)提示mysql.host 不存在，啟動(dòng)失敗的解決方法
我將s9當(dāng)眾原來(lái)的mysql4.0刪除后，重新裝了個(gè)mysql5.0，啟動(dòng)過(guò)程中報(bào)一下錯(cuò)誤，啟動(dòng)失敗，查了一下群里面的老帖子也沒(méi)有個(gè)具體的明確說(shuō)明2011-10-10
在數(shù)據(jù)庫(kù)中我們一般都會(huì)對(duì)一些字段進(jìn)行索引操作,這樣可以提升數(shù)據(jù)的查詢速度，下面這篇文章主要給大家介紹了關(guān)于MySQL8新特性：降序索引的相關(guān)資料，需要的朋友可以參考借鑒，下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2018-07-07
MySQL RelayLog中繼日志是主從復(fù)制架構(gòu)中的核心組件,負(fù)責(zé)將從主庫(kù)獲取的Binlog事件暫存并應(yīng)用到從庫(kù),本文就來(lái)詳細(xì)的介紹一下RelayLog中繼日志的使用,感興趣的可以了解一下2025-12-12
MySQL連接被阻塞的問(wèn)題分析與解決方案(從錯(cuò)誤到修復(fù))
在Java應(yīng)用開(kāi)發(fā)中,數(shù)據(jù)庫(kù)連接是必不可少的一環(huán),然而,在使用MySQL時(shí),我們可能會(huì)遇到MySQL服務(wù)器由于檢測(cè)到過(guò)多的連接失敗,自動(dòng)阻止了來(lái)自該主機(jī)的連接請(qǐng)求,本文將深入分析該問(wèn)題的原因,并提供完整的解決方案,需要的朋友可以參考下2025-04-04
MySQL的雙寫(xiě)緩沖區(qū)Doublewrite Buffer詳解
這篇文章主要介紹了MySQL的雙寫(xiě)緩沖區(qū)Doublewrite Buffer詳解,InnoDB是MySQL中一種常用的事務(wù)性存儲(chǔ)引擎，它具有很多優(yōu)秀的特性,其中，Doublewrite Buffer是InnoDB的一個(gè)重要特性之一，本文將介紹Doublewrite Buffer的原理和應(yīng)用,需要的朋友可以參考下2023-07-07