深入淺析MySQL?Explain
在日常工作中,我們會有時會開慢查詢?nèi)ビ涗浺恍﹫?zhí)行時間比較久的SQL語句,找出這些SQL語句并不意味著完事了,些時我們常常用到explain這個命令來查看一個這些SQL語句的執(zhí)行計劃,查看該SQL語句有沒有使用上了索引,有沒有做全表掃描,這都可以通過explain命令來查看。所以我們深入了解MySQL的基于開銷的優(yōu)化器,還可以獲得很多可能被優(yōu)化器考慮到的訪問策略的細節(jié),以及當運行SQL語句時哪種策略預計會被優(yōu)化器采用。
-- 實際SQL,查找用戶名為Jefabc的員工 select * from emp where name = 'Jefabc'; -- 查看SQL是否使用索引,前面加上explain即可 explain select * from emp where name = 'Jefabc';
expain出來的信息有10列,分別是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra
概要描述:
id:選擇標識符
select_type:表示查詢的類型。
table:輸出結果集的表
partitions:匹配的分區(qū)
type:表示表的連接類型
possible_keys:表示查詢時,可能使用的索引
key:表示實際使用的索引
key_len:索引字段的長度
ref:列與索引的比較
rows:掃描出的行數(shù)(估算的行數(shù))
filtered:按表條件過濾的行百分比
Extra:執(zhí)行情況的描述和說明
下面對這些字段出現(xiàn)的可能進行解釋:
一、id
SELECT識別符。這是SELECT的查詢序列號
我的理解是SQL執(zhí)行的順序的標識,SQL從大到小的執(zhí)行
1. id相同時,執(zhí)行順序由上至下
2. 如果是子查詢,id的序號會遞增,id值越大優(yōu)先級越高,越先被執(zhí)行
3. id如果相同,可以認為是一組,從上往下順序執(zhí)行;在所有組中,id值越大,優(yōu)先級越高,越先執(zhí)行
-- 查看在研發(fā)部并且名字以Jef開頭的員工,經(jīng)典查詢 explain select e.no, e.name from emp e left join dept d on e.dept_no = d.no where e.name like 'Jef%' and d.name = '研發(fā)部';
二、select_type
示查詢中每個select子句的類型
(1)SIMPLE(簡單SELECT,不使用UNION或子查詢等)
(2)PRIMARY(子查詢中最外層查詢,查詢中若包含任何復雜的子部分,最外層的select被標記為PRIMARY)
(3)UNION(UNION中的第二個或后面的SELECT語句)
(4)DEPENDENT UNION(UNION中的第二個或后面的SELECT語句,取決于外面的查詢)
(5)UNION RESULT(UNION的結果,union語句中第二個select開始后面所有select)
(6)SUBQUERY(子查詢中的第一個SELECT,結果不依賴于外部查詢)
(7)DEPENDENT SUBQUERY(子查詢中的第一個SELECT,依賴于外部查詢)
(8)DERIVED(派生表的SELECT, FROM子句的子查詢)
(9)UNCACHEABLE SUBQUERY(一個子查詢的結果不能被緩存,必須重新評估外鏈接的第一行)
三、table
顯示這一步所訪問數(shù)據(jù)庫中表名稱(顯示這一行的數(shù)據(jù)是關于哪張表的),有時不是真實的表名字,可能是簡稱,例如上面的e,d,也可能是第幾步執(zhí)行的結果的簡稱
四、type
對表訪問方式,表示MySQL在表中找到所需行的方式,又稱“訪問類型”。
常用的類型有:ALL、index、range、 ref、eq_ref、const、system、NULL(從左到右,性能從差到好)
ALL:Full Table Scan, MySQL將遍歷全表以找到匹配的行
index: Full Index Scan,index與ALL區(qū)別為index類型只遍歷索引樹
range:只檢索給定范圍的行,使用一個索引來選擇行
ref: 表示上述表的連接匹配條件,即哪些列或常量被用于查找索引列上的值
eq_ref: 類似ref,區(qū)別就在使用的索引是唯一索引,對于每個索引鍵值,表中只有一條記錄匹配,簡單來說,就是多表連接中使用primary key或者 unique key作為關聯(lián)條件
const、system: 當MySQL對查詢某部分進行優(yōu)化,并轉換為一個常量時,使用這些類型訪問。如將主鍵置于where列表中,MySQL就能將該查詢轉換為一個常量,system是const類型的特例,當查詢的表只有一行的情況下,使用system
NULL: MySQL在優(yōu)化過程中分解語句,執(zhí)行時甚至不用訪問表或索引,例如從一個索引列里選取最小值可以通過單獨索引查找完成。
五、possible_keys
指出MySQL能使用哪個索引在表中找到記錄,查詢涉及到的字段上若存在索引,則該索引將被列出,但不一定被查詢使用(該查詢可以利用的索引,如果沒有任何索引顯示 null)
該列完全獨立于EXPLAIN輸出所示的表的次序。這意味著在possible_keys中的某些鍵實際上不能按生成的表次序使用。
如果該列是NULL,則沒有相關的索引。在這種情況下,可以通過檢查WHERE子句看是否它引用某些列或適合索引的列來提高你的查詢性能。如果是這樣,創(chuàng)造一個適當?shù)乃饕⑶以俅斡肊XPLAIN檢查查詢
六、Key
key列顯示MySQL實際決定使用的鍵(索引),必然包含在possible_keys中
如果沒有選擇索引,鍵是NULL。要想強制MySQL使用或忽視possible_keys列中的索引,在查詢中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
七、key_len
表示索引中使用的字節(jié)數(shù),可通過該列計算查詢中使用的索引的長度(key_len顯示的值為索引字段的最大可能長度,并非實際使用長度,即key_len是根據(jù)表定義計算而得,不是通過表內(nèi)檢索出的)
不損失精確性的情況下,長度越短越好
八、ref
列與索引的比較,表示上述表的連接匹配條件,即哪些列或常量被用于查找索引列上的值
九、rows
估算出結果集行數(shù),表示MySQL根據(jù)表統(tǒng)計信息及索引選用情況,估算的找到所需的記錄所需要讀取的行數(shù)
十、Extra
該列包含MySQL解決查詢的詳細信息,有以下幾種情況:
Using where:不用讀取表中所有信息,僅通過索引就可以獲取所需數(shù)據(jù),這發(fā)生在對表的全部的請求列都是同一個索引的部分的時候,表示mysql服務器將在存儲引擎檢索行后再進行過濾
Using temporary:表示MySQL需要使用臨時表來存儲結果集,常見于排序和分組查詢,常見 group by ; order by
Using filesort:當Query中包含 order by 操作,而且無法利用索引完成的排序操作稱為“文件排序”
-- 測試Extra的filesort explain select * from emp order by name;
Using join buffer:改值強調了在獲取連接條件時沒有使用索引,并且需要連接緩沖區(qū)來存儲中間結果。如果出現(xiàn)了這個值,那應該注意,根據(jù)查詢的具體情況可能需要添加索引來改進能。
Impossible where:這個值強調了where語句會導致沒有符合條件的行(通過收集統(tǒng)計信息不可能存在結果)。
Select tables optimized away:這個值意味著僅通過使用索引,優(yōu)化器可能僅從聚合函數(shù)結果中返回一行
No tables used:Query語句中使用from dual 或不含任何from子句
-- explain select now() from dual;
總結:
• EXPLAIN不會告訴你關于觸發(fā)器、存儲過程的信息或用戶自定義函數(shù)對查詢的影響情況
• EXPLAIN不考慮各種Cache
• EXPLAIN不能顯示MySQL在執(zhí)行查詢時所作的優(yōu)化工作
• 部分統(tǒng)計信息是估算的,并非精確值
• EXPALIN只能解釋SELECT操作,其他操作要重寫為SELECT后查看執(zhí)行計劃。
通過收集統(tǒng)計信息不可能存在結果
到此這篇關于MySQLExplain詳解的文章就介紹到這了,更多相關MySQLExplain內(nèi)容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持本站!
版權聲明:本站文章來源標注為YINGSOO的內(nèi)容版權均為本站所有,歡迎引用、轉載,請保持原文完整并注明來源及原文鏈接。禁止復制或仿造本網(wǎng)站,禁止在非www.sddonglingsh.com所屬的服務器上建立鏡像,否則將依法追究法律責任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來,僅供學習參考,不代表本站立場,如有內(nèi)容涉嫌侵權,請聯(lián)系alex-e#qq.com處理。