MySQL使用explain命令查看與分析索引的使用情況
在查詢語句中使用 explain 關(guān)鍵字,可以查看索引是否正在被使用,有沒有做全表掃描,并且輸出使用的索引信息。
語法格式如下:
explain select 語句;
一、數(shù)據(jù)準(zhǔn)備
有一個 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)
查詢結(jié)果中的各個選項(xiàng)的含義如下:
(1) id:SELECT 識別符 (2) select_type:SELECT 查詢的類型 (3) table:數(shù)據(jù)表的名字 (4) partitions:匹配的分區(qū) (5) type:訪問表的方式 (6) possible_keys:查詢時可能使用的索引 (7) key:實(shí)際使用的索引 (8) key_len:索引字段的長度 (9) ref:連接查詢時,用于顯示關(guān)聯(lián)的字段 (10) rows:需要掃描的行數(shù)(估算的行數(shù)) (11) filtered:按條件過濾后查詢到的記錄的百分比 (12) Extra:執(zhí)行情況的描述和說明
三、explain 各個選項(xiàng)的詳細(xì)說明及舉例
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='財(cái)務(wù)部')\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(子查詢中最外層查詢,查詢中若包含任何復(fù)雜的子部分,最外層的 select 被標(biāo)記為 PRIMARY) 例如: mysql> explain select * from emp where dept_id=(select dept_id from dept where dept_name='財(cái)務(wù)部')\G *************************** 1. row *************************** id: 1 ---外部查詢 select_type: PRIMARY table: emp *************************** 2. row *************************** id: 2 ---子查詢 select_type: SUBQUERY (3) SUBQUERY(子查詢中的第一個SELECT,結(jié)果不依賴于外部查詢) 例如:見上一個例子。 (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的結(jié)果,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 快,因?yàn)樗饕募ǔ1葦?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: 非唯一性索引掃描,返回匹配某個單獨(dú)值的所有行。本質(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 字段進(jìn)行查詢,查詢到多條記錄,并且為 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: 表示通過一次索引就找到了結(jié)果,常出現(xiàn)于 primary key 或 unique 索引。因?yàn)橹黄ヅ湟恍袛?shù)據(jù),所以查詢非??臁H鐚⒅麈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
顯示可能應(yīng)用在表中的索引,可能一個或多個。
查詢涉及到的字段若存在索引,則該索引將被列出,但不一定被查詢實(shí)際使用。
例如:
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
實(shí)際使用的索引,如為NULL,則表示未使用索引。
若查詢中使用了覆蓋索引,則該索引和查詢的 select 字段重疊。
見上一個例子。
8、key_len
表示索引所使用的字節(jié)數(shù),可通過該列計(jì)算查詢中使用的索引長度。
在不損失精確性的情況下,長度越短越好。
例如:
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,因?yàn)橹麈I時整型,長度為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
顯示關(guān)聯(lián)的字段,如果是非連接查詢,則顯示 const,如果是連接查詢,則會顯示關(guān)聯(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)計(jì)信息及索引選用情況大致估算出找到所需記錄所要讀取的行數(shù)。
當(dāng)然該值越小越好。
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) --沒有使用索引,需要進(jìn)行全表掃描,一共讀取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
顯示一些重要的額外信息。一般有以下幾項(xiàng):
(1)Using filesort:對數(shù)據(jù)使用一個外部的索引排序,而不是按照表內(nèi)的索引順序進(jìn)行讀取。當(dāng)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需要使用臨時表來存儲結(jié)果集,常見于排序和分組查詢,常見 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:表明相應(yīng)的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ū)來存儲中間結(jié)果。如果出現(xiàn)了這個值,應(yīng)注意根據(jù)查詢的具體情況可能需要添加索引來改進(jìn)能。 例如: 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)
總結(jié)
以上為個人經(jīng)驗(yàn),希望能給大家一個參考,也希望大家多多支持腳本之家。
相關(guān)文章
淺談mysql雙層not exists查詢執(zhí)行流程
本文主要介紹了淺談mysql雙層not?exists查詢執(zhí)行流程,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2023-06-06mysql通過INSERT IGNORE INTO插入拼音字符無效問題及解決
這篇文章主要介紹了mysql通過INSERT IGNORE INTO插入拼音字符無效問題及解決方案,具有很好的參考價(jià)值,希望對大家有所幫助。如有錯誤或未考慮完全的地方,望不吝賜教2022-08-08MySQL分表和分區(qū)的具體實(shí)現(xiàn)方法
這篇文章主要介紹了MySQL分表和分區(qū)的具體實(shí)現(xiàn)方法,文中通過示例代碼介紹的非常詳細(xì),對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2019-06-06mysql 5.7 docker 主從復(fù)制架構(gòu)搭建教程
這篇文章主要為大家詳細(xì)介紹了mysql 5.7 docker 主從復(fù)制架構(gòu)搭建教程,感興趣的小伙伴們可以參考一下2016-07-07