MySQL使用explain命令查看與分析索引的使用情況
更新時間：2023年12月21日 09:46:34 作者：睿思達DBA_WGX
這篇文章主要介紹了MySQL使用explain命令查看與分析索引的使用情況,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教
在查詢語句中使用 explain 關鍵字，可以查看索引是否正在被使用，有沒有做全表掃描，并且輸出使用的索引信息。
語法格式如下：
explain select 語句;
一、數(shù)據(jù)準備
有一個 emp 表，表中的索引信息如下：
mysql> show index from emp; +-------+------------+--------------+--------------+-------------+----------- | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation +-------+------------+--------------+--------------+-------------+----------- | emp | 0 | PRIMARY | 1 | e_id | A | emp | 0 | uq_idx_phone | 1 | phone | A | emp | 1 | dept_id | 1 | dept_id | A | emp | 1 | idx_ename | 1 | e_name | A | emp | 1 | idx_addr | 1 | addr | A +-------+------------+--------------+--------------+-------------+----------- 5 rows in set (0.00 sec)
二、使用 explain 分析查詢
執(zhí)行以下命令：
mysql> explain select * from emp where e_name='Mark'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp
partitions: NULL
type: ref
possible_keys: idx_ename
key: idx_ename
key_len: 81
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
查詢結果中的各個選項的含義如下：
（1） id：SELECT 識別符 （2） select_type：SELECT 查詢的類型 （3） table：數(shù)據(jù)表的名字 （4） partitions：匹配的分區(qū) （5） type：訪問表的方式 （6） possible_keys：查詢時可能使用的索引 （7） key：實際使用的索引 （8） key_len：索引字段的長度 （9） ref：連接查詢時，用于顯示關聯(lián)的字段 （10） rows：需要掃描的行數(shù)(估算的行數(shù)) （11） filtered：按條件過濾后查詢到的記錄的百分比 （12） Extra：執(zhí)行情況的描述和說明
三、explain 各個選項的詳細說明及舉例
1、id
SELECT 識別符，是SELECT 查詢的序列號。
表示查詢中執(zhí)行 select 子句或操作表的順序，id 相同，執(zhí)行順序從上到下，id 不同，id 值越大則執(zhí)行的優(yōu)先級越高。
例如：
mysql> explain select * from emp where dept_id=(select dept_id from dept where dept_name='財務部')\G
--如果包含子查詢，id 的序號會遞增，id 值越大執(zhí)行優(yōu)先級越高
*************************** 1. row ***************************
id: 1 ---外部查詢
select_type: PRIMARY
table: emp
*************************** 2. row ***************************
id: 2 ---子查詢
select_type: SUBQUERY
--id相同，執(zhí)行順序從上到下
mysql> explain select * from emp,dept where emp.dept_id=dept.dept_id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: dept
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: emp
2、select_type
表示查詢中每個 select 子句的類型。有以下幾種類型：
(1) SIMPLE（簡單的 select 查詢，查詢中不包含子查詢或 union 查詢）
例如：
mysql> explain select * from dept\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
(2) PRIMARY（子查詢中最外層查詢，查詢中若包含任何復雜的子部分，最外層的 select 被標記為 PRIMARY）
例如：
mysql> explain select * from emp where dept_id=(select dept_id from dept where dept_name='財務部')\G
*************************** 1. row ***************************
id: 1 ---外部查詢
select_type: PRIMARY
table: emp
*************************** 2. row ***************************
id: 2 ---子查詢
select_type: SUBQUERY
(3) SUBQUERY（子查詢中的第一個SELECT，結果不依賴于外部查詢）
例如：見上一個例子。
(4) DEPENDENT SUBQUERY(子查詢中的第一個SELECT，依賴于外部查詢)
例如：
mysql> explain select * from dept where exists (select * from emp where emp.dept_id=dept.dept_id)\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
(5) UNION（UNION中的第二個或后面的SELECT語句）
例如：
mysql> explain select * from emp where dept_id=11 union select * from emp where dept_id=22\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
*************************** 2. row ***************************
id: 2
select_type: UNION
*************************** 3. row ***************************
id: NULL
select_type: UNION RESULT
(6) UNION RESULT（UNION的結果，union語句中第二個select開始后面所有select）
例如：見上一個例子
3、table
查詢所用的表名稱，可能是別名。例如：
mysql> explain select * from emp e,dept d where e.dept_id=d.dept_id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: d
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: e
mysql> explain select * from emp\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp
4、partitions
查詢匹配的分區(qū)：例子略。
5、type
訪問表的方式，表示 MySQL 在表中找到所需行的方式。
常用的類型有： ALL、index、range、 ref、eq_ref、const、system、NULL（從左到右，性能從差到好）。
例如：
查詢用到的表及索引情況如下：
mysql> show index from dept; +-------+------------+----------+--------------+-------------+----------- | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation +-------+------------+----------+--------------+-------------+----------- | dept | 0 | PRIMARY | 1 | dept_id | A +-------+------------+----------+--------------+-------------+----------- 1 row in set (0.00 sec) mysql> show index from emp; +-------+------------+--------------+--------------+-------------+----------- | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation +-------+------------+--------------+--------------+-------------+----------- | emp | 0 | PRIMARY | 1 | e_id | A | emp | 0 | uq_idx_phone | 1 | phone | A | emp | 1 | dept_id | 1 | dept_id | A | emp | 1 | idx_ename | 1 | e_name | A | emp | 1 | idx_addr | 1 | addr | A +-------+------------+--------------+--------------+-------------+----------- 5 rows in set (0.00 sec)
常用的類型的說明及舉例：
（1）ALL：Full Table Scan，如果查詢沒有使用索引，MySQL將遍歷全表以找到匹配的行。
例如：
mysql> explain select * from emp where birth='1998-1-1'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp
partitions: NULL
type: ALL
--該查詢的條件沒有創(chuàng)建索引，因此是全表掃描。
（2）index: 全索引掃描，和 ALL 相比，index 只遍歷索引樹，通常比 ALL 快，因為索引文件通常比數(shù)據(jù)文件小。
例如：
mysql> explain select e_id from emp\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp
partitions: NULL
type: index
--該查詢是把 e_id 列中所有數(shù)據(jù)全部取出，并且對 e_id 列創(chuàng)建了索引，因此需要遍歷整個索引樹
（3）range：檢索給定范圍的行，可以在 key 列中查看使用的索引，一般出現(xiàn)在 where 語句的條件中，如使用between、>、<、in等查詢。
例如：
mysql> explain select * from emp where e_id>1000\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp
partitions: NULL
type: range
（4）ref: 非唯一性索引掃描，返回匹配某個單獨值的所有行。本質(zhì)上也是一種索引訪問，返回匹配某條件的多行值。
例如：
mysql> explain select * from emp where dept_id=11\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp
partitions: NULL
type: ref
--該查詢針對 dept_id 字段進行查詢，查詢到多條記錄，并且為 dept_id 字段創(chuàng)建了索引。
（5）eq_ref: 唯一索引掃描，對于每個索引鍵，表中只有一條記錄與之匹配。常見主鍵或唯一索引掃描。
mysql> explain select * from emp join dept on emp.dept_id=dept.dept_id where e_name='Jack'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp
partitions: NULL
type: ref
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: dept
partitions: NULL
type: eq_ref
（6）const: 表示通過一次索引就找到了結果，常出現(xiàn)于 primary key 或 unique 索引。因為只匹配一行數(shù)據(jù)，所以查詢非?？臁Ｈ鐚⒅麈I置于 where 條件中，MySQL 就能將查詢轉(zhuǎn)換為一個常量。
例如：
mysql> explain select * from emp where e_id=1002\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp
partitions: NULL
type: const
（7）NULL: 執(zhí)行時不用訪問表或索引。
例如：
mysql> explain select 1 from dual\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: NULL
partitions: NULL
type: NULL
6、possible_keys
顯示可能應用在表中的索引，可能一個或多個。
查詢涉及到的字段若存在索引，則該索引將被列出，但不一定被查詢實際使用。
例如：
mysql> explain select * from emp join dept on emp.dept_id=dept.dept_id where e_name='Jack'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp
partitions: NULL
type: ref
possible_keys: dept_id,idx_ename
key: idx_ename
key_len: 81
ref: const
rows: 1
filtered: 100.00
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: dept
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: wanggx.emp.dept_id
rows: 1
filtered: 100.00
Extra: NULL
2 rows in set, 1 warning (0.00 sec)
7、key
實際使用的索引，如為NULL，則表示未使用索引。
若查詢中使用了覆蓋索引，則該索引和查詢的 select 字段重疊。
見上一個例子。
8、key_len
表示索引所使用的字節(jié)數(shù)，可通過該列計算查詢中使用的索引長度。
在不損失精確性的情況下，長度越短越好。
例如：
mysql> explain select * from emp where e_id=1001\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
--索引長度為4，因為主鍵時整型，長度為4
mysql> explain select * from emp where phone='13037316644'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp
partitions: NULL
type: const
possible_keys: uq_idx_phone
key: uq_idx_phone
key_len: 81
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
9、ref
顯示關聯(lián)的字段，如果是非連接查詢，則顯示 const，如果是連接查詢，則會顯示關聯(lián)的字段。
例如：
mysql> explain select * from emp join dept on emp.dept_id=dept.dept_id where e_name='Jack'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp
partitions: NULL
type: ref
possible_keys: dept_id,idx_ename
key: idx_ename
key_len: 81
ref: const
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: dept
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: wanggx.emp.dept_id
10、rows
根據(jù)表統(tǒng)計信息及索引選用情況大致估算出找到所需記錄所要讀取的行數(shù)。
當然該值越小越好。
mysql> explain select * from emp where salary=5000\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 3
filtered: 33.33
Extra: Using where
1 row in set, 1 warning (0.00 sec)
--沒有使用索引，需要進行全表掃描，一共讀取3行
mysql> explain select * from emp where dept_id=11\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp
partitions: NULL
type: ref
possible_keys: dept_id
key: dept_id
key_len: 5
ref: const
rows: 2
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
--使用了索引，掃描2行
11、filtered
表示選取的行和讀取的行的百分比，100表示選取了100%，80表示讀取了80%。
例如：
mysql> explain select * from emp where phone = '13703735488'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp
partitions: NULL
type: const
possible_keys: uq_idx_phone
key: uq_idx_phone
key_len: 81
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.01 sec)
--根據(jù)phone創(chuàng)建了唯一索引，并且條件是等號（=），因此filtered為100%
mysql> explain select * from emp where salary = 5200\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
filtered: 50.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
--由于salary字段沒有創(chuàng)建索引，因此執(zhí)行全表掃描，filtered為50%
12、extra
顯示一些重要的額外信息。一般有以下幾項：
（1）Using filesort：對數(shù)據(jù)使用一個外部的索引排序，而不是按照表內(nèi)的索引順序進行讀取。當Query中包含 order by 操作，而且無法利用索引完成的排序操作稱為“文件排序”。
例如：
mysql> explain select * from emp order by salary\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
filtered: 100.00
Extra: Using filesort
1 row in set, 1 warning (0.01 sec)
（2）Using temporary：表示MySQL需要使用臨時表來存儲結果集，常見于排序和分組查詢，常見 group by 與 order by。
例如：
mysql> explain select count(*) from emp group by salary\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
filtered: 100.00
Extra: Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)
（3）Using index：表明相應的select操作中使用了覆蓋索引（select的數(shù)據(jù)列只從索引中就能取得數(shù)據(jù)，不必讀取數(shù)據(jù)行）。
mysql> explain select e_name,salary from emp order by e_name\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 2
filtered: 100.00
Extra: Using filesort
1 row in set, 1 warning (0.00 sec)
mysql> explain select e_name from emp order by e_name\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: emp
partitions: NULL
type: index
possible_keys: NULL
key: idx_ename
key_len: 81
ref: NULL
rows: 2
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
（4）Using join buffer：表明在獲取連接條件時沒有使用索引，并且需要連接緩沖區(qū)來存儲中間結果。如果出現(xiàn)了這個值，應注意根據(jù)查詢的具體情況可能需要添加索引來改進能。
例如：
mysql> explain select * from emp join dept on emp.dept_id=dept.dept_id\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: dept
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 2
filtered: 100.00
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: emp
partitions: NULL
type: ALL
possible_keys: dept_id
key: NULL
key_len: NULL
ref: NULL
rows: 2
filtered: 100.00
Extra: Using where; Using join buffer (Block Nested Loop)
2 rows in set, 1 warning (0.00 sec)
總結
以上為個人經(jīng)驗，希望能給大家一個參考，也希望大家多多支持腳本之家。
相關文章
該文章介紹了如何在MySQL中使用命令來查詢?nèi)罩緺顟B(tài)、獲取binlog文件列表、查看特定binlog文件內(nèi)容等,通過這些命令,用戶可以了解當前MySQL日志的狀態(tài),查看binlog文件的具體內(nèi)容等2026-05-05
最近別人問我一個問題:數(shù)據(jù)庫中的左連接和右連接有什么區(qū)別?所以這篇文章主要給大家介紹了關于Mysql內(nèi)連接和外連接區(qū)別的相關資料,需要的朋友可以參考下2023-01-01
JOIN是一種非常常見的操作，用于將兩個或多個表中的數(shù)據(jù)合并到一個結果集中。MySQL支持多種JOIN類型，本文通過代碼示例詳細介紹了Join的使用優(yōu)化，有需要的小伙伴可以參考閱讀2023-04-04
IDEA連接mysql又報錯！Server returns invalid timezone. Go to tab an
這篇文章主要介紹了IDEA連接mysql又報錯！Server returns invalid timezone. Go to 'Advanced' tab and set 'serverTimezone' prope問題，本文通過圖文并茂的形式給大家介紹的非常詳細,需要的朋友可以參考下2020-05-05
mysql innodb的監(jiān)控（系統(tǒng)層，數(shù)據(jù)庫層）
這篇文章主要介紹了mysql innodb的監(jiān)控（系統(tǒng)層，數(shù)據(jù)庫層）的相關資料,需要的朋友可以參考下2017-04-04
按照key進行分區(qū)非常類似于按照hash進行分區(qū),只不過hash分區(qū)允許使用用戶自定義的表達式,下面這篇文章主要給大家介紹了關于MySQL分區(qū)之KEY分區(qū)的相關資料,需要的朋友可以參考下2022-04-04
這篇文章主要介紹了Mysql Explain 詳細介紹的相關資料,這里對Mysql Explain 的語法，屬性，以及如何使用，做了全面介紹，需要的朋友可以參考下2016-11-11
如何解決mysql的count()函數(shù)條件表達式不生效問題
該文章總結了SQL查詢中`count`函數(shù)統(tǒng)計錯誤的原因,以及三種解決方法：使用`ornull`方法、`IF()`函數(shù)和`casewhen`表達式,當不滿足條件時,表達式的值為NULL2024-11-11
在CentOS上運行MySQL報錯Too many connections(連接數(shù)打滿)的解決方案
在CentOS服務器上運維MySQL時,經(jīng)常會遇到 Too many connections 報錯,尤其是通過 mysqld_safe 腳本啟動、而非系統(tǒng) systemctl 管理的MySQL實例,本文結合實際運維場景,詳細講解該報錯的原因、應急解決步驟、永久優(yōu)化方案,需要的朋友可以參考下2026-03-03
使用MySQL設置遠程數(shù)據(jù)庫以優(yōu)化網(wǎng)站性能的操作步驟
隨著您的應用程序或網(wǎng)站的增長,您可能會發(fā)現(xiàn)已經(jīng)超出了當前的設置,如果您目前將 Web 服務器和數(shù)據(jù)庫后端托管在同一臺 VPS 上,一個好主意是將這兩個功能分開,以便每個功能可以在自己的機器上運行和增長,在本指南中,我們將討論如何配置一個遠程數(shù)據(jù)庫服務器2024-11-11