系統性能的優劣取決於我們sql的查詢速度,MySQL Explain命令是分析SQL性能及優化不可缺少的一部分。
Explain被我們稱為解釋器,通過 explain 我們可以知道以下信息:表的讀取順序,數據讀取操作的類型,可能會使用哪些索引,實際真正使用了哪些索引,表之間的引用,每張表有多少行被優化器查詢等信息。
Explain基本語法
explain [extended|partition]select
在select前加explain關鍵字,MySQL會返回該查詢的執行計劃而不是執行這條SQL
根據語法我們知道explain還有兩種其他的用法:
1. explain extended :能夠在原本explain的基礎上額外的提供一些查詢優化的信息,這些信息可以通過mysql的show warnings命令得到,從而看出優化器優化了什麼。
2. explain partitions:相比 explain 多了個 partitions 字段,如果查詢是基於分區表的話,會顯示查詢將訪問的分區。
Explain列的含義
id:id列的編號是 select 的序列號,有幾個 select 就有幾個id,id不同,id值越大,優先級越高,越先 執行
select_type :顯示本行是簡單或複雜select。如果查詢有任何複雜的子查詢,則最外層標記為 PRIMARY(DERIVED、UNION、UNION RESUlT)
table :訪問查詢的表名或表別名|
type :表的訪問類型( MySQL 如何查詢表中的行記錄)效率高低:const eq_ref/ref/range/index/all
possible_keys:指出MySQL能使用哪個索引在該表中找到行
key :MySQL實際決定使用的鍵(索引)。如果沒有選擇索引,鍵是NULL
key_len :實際使用的索引長度(單位:字節)該字段顯示為索引字段的最大可能長度,並非實際使 用長度。
ref : 用於索引查找的值的來源,如果值未常量,則 ref 為 const
rows :預計查詢需要掃描的行數(在表或索引樹中)
filtered :查詢條件所過濾的行記錄數佔比
Extra:額外的信息:
- Using filesort:說明mysql會對數據適用一個外部的索引排序。而不是按照表內的索引順序進行讀取。MySQL中無法利用索引完成排序操作稱為“文件排序”
- Using temporary:使用了臨時表保存中間結果,mysql在查詢結果排序時使用臨時表。常見於排序order by和分組查詢group by。
- Using index:表示相應的select操作用使用覆蓋索引,避免訪問了表的數據行。如果同時出現using where,表名索引被用來執行索引鍵值的查找;如果沒有同時出現using where,表名索引用來讀取數據而非執行查詢動作。
- Using where :表明使用where過濾
- using join buffer:使用了連接緩存
- impossible where:where子句的值總是false,不能用來獲取任何元組
- select tables optimized away:在沒有group by子句的情況下,基於索引優化Min、max操作或者對於MyISAM存儲引擎優化count(*),不必等到執行階段再進行計算,查詢執行計劃生成的階段即完成優化。
- distinct:優化distinct操作,在找到第一匹配的元組後即停止找同樣值的動作
詳細介紹列的含義
1. id列
id 的值為數值,其表示的是SQL執行中的執行順序,規則如下:
- 如果 id 值相同,則執行順序為:從上到下
- 如果 id 值不同,則執行順序為: id 值越大的越先執行
- 如果 id 值相同,則可以認為他們是同一分組,同一分組中執行順序為:從上到下
- 在所有組中, id 值越大的越先執行
- 如果 id 值為 null ,則表示這是一個臨時表,臨時表不在SQL總出現,因此它的id是NULL
MySQL將 select 查詢分為簡單查詢和複雜查詢。複雜查詢分為三類:簡單子查詢、派生表(from語句中的子查詢)、union 查詢。
2. select_type列
這一列表示的是對應行對應的查詢類型,到底是簡單查詢還是複雜查詢,如果是複雜的查詢,又是簡單子查詢、from語句中的子查詢、union 查詢複雜查詢中的哪一種。
1. simple :簡單的select查詢,查詢中不包含子查詢或者 union
2. primary:複雜查詢最外層的查詢類型
3. subquery :在 select 或 where 列表中包含了子查詢
4. derived:包含在 from 子句中的子查詢。MySQL會將結果存放在一個臨時表中,也稱為派生表
5. union:union查詢語句出現在union之後的第二個和以後的查詢會被標為union類型
6. union result:從 union 構建的臨時表檢索結果的查詢類型
3. table列
這一列看名稱就知道是指的具體查詢的table名稱。
當 from 子句中有子查詢時,table列是 格式,表示當前查詢依賴 id=N 的查詢,於是先執行 id=N 的查詢。當有 union 時,UNION RESULT 的 table 列的值為 ,1和2表示參與 union 的 select 行id。
4. type列
這一列表示關聯類型或訪問類型,即MySQL決定如何查找表中的行
最優到最差分別為:system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
1. const, system:Mysql查詢對其進行優化並轉化為一個常量,只查詢一次就搜索出結果,用於 primary key 或 unique key 的所有列與常數比較時,所以表最多有一個匹配行,讀取1次,速度比較快。
2. eq_ref:唯一性索引掃描,對於每個索引鍵,表中只有一條記錄與之匹配。常見於主鍵或唯一索引掃描
3. ref:相比 eq_ref,不使用唯一索引,而是使用普通索引或者聯合索引的部分前綴,索引要和某個值相比較,可能會找到多個符合條件的行.
4. ref_or_null:與ref類型差不多,但是這種類型可以搜索為Null的行
5. index_merge:使用了索引合併的優化方法
6.range: 範圍掃描通常出現在 in(), between ,> ,= 等操作中。使用一個索引來檢索給定範圍的行
7 index:Full Index Scan,Index與All區別為index類型只遍歷索引樹。這通常比ALL快,因為索引文件通常比數據文件小
8. All:掃描全表,與index其實都是掃描全表進行檢索數據,區別在於,index類型是掃描索引樹進行數據掃描,而All類型則是直接掃磁盤,所以相對index類型比較慢
5. possible_keys列
顯示此次查詢可能會用到的索引,一個或者是多個,查詢涉及到的字段上若存在索引,則該索引將被列出,但不一定會應用。
explain 時可能出現 possible_keys 有列,而 key 顯示 NULL 的情況,這種情況是因為表中數據不多,mysql認為索引對此查詢幫助不大,選擇了全表查詢。
如果該列是NULL,則沒有相關的索引。在這種情況下,可以通過檢查 where 子句看是否可以創造一個適當的索引來提高查詢性能,然後用 explain 查看效果。但是有一種情況也可能會走索引,如果出現 覆蓋索引 的情況即使該列為null 依然有可能會走索引查詢
6. key列
mysql實際採用哪個索引來優化對該表的訪問,如果想強制指定索引或者忽視索引,可在查詢中使用 ***force index、ignore index***。
7. key_len列
表示索引中使用的字節數,可通過該列計算查詢中使用的索引的長度,在不損失精確性的情況下,長度越短越好,key_len表示的是索引的最大長度,而不是實際使用長度。
key_len計算規則如下:
+ 字符型
- char(n):n字節長度
- varchar(n):2字節存儲字符串長度,如果是utf-8,則長度 3n + 2
+ 數值型
- tinyint:1字節
- smallint:2字節
- int:4字節
- bigint:8字節
+ 時間類型
- date:3字節
- timestamp:4字節
- datetime:8字節
+ 如果字段允許為 NULL,需要額外增加1字節記錄是否為 NULL
8. ref列
這一列顯示了在key列記錄的索引中,表查找值所用到的列或常量,常見的有:const(常量),func,NULL,字段名
9. rows列
這一列表示這條SQL可能要檢索的數據行數,並不是返回結果集的行數。
10.Extra列
這一列展示的是一些額外的信息,但是也是十分重要的,對於我們提升SQL 的檢索性能是很有幫助的。
常見的類型如下:
- distinct: 一旦mysql找到了與行相聯合匹配的行,就不再搜索了
- Using index:表示相應的select操作中使用了覆蓋索引(Covering Index),避免訪問了表的數據行,效率還是非常可觀的。
覆蓋索引:簡單的理解就是這次select的字段只從索引中就可以獲取的到,沒有必要再去浪費一次IO讀取行數據,換句話說就是查詢的列被索引列所覆蓋
- Using where:就是使用了where條件查詢。mysql服務器將在存儲引擎檢索行後再進行過濾。就是先讀取整行數據,再按 where 條件進行檢查,符合就留下,不符合就丟棄。
- Using temporary:mysql需要創建一張臨時表來處理查詢結果。出現這種情況一般是要進行優化的,首先是想到用索引來優化。這種類型常見於order by 和group by的查詢中
- Using filesort:對查詢結果進行外部索引排序而不是按索引次序從表裡讀取行,這種情況可以考慮建立索引來進行優化。
參考文檔
[Mysql官方文檔-explain](https://dev.mysql.com/doc/refman/8.0/en/explain-output.html)
由於無法使用MarkDown 代碼部分已刪除,如需查看詳細代碼示例可瀏覽:[https://blog.csdn.net/liruichuan/article/details/103575911]