天天看點

SQL中explaind的使用介紹

一、explain 能幹嘛?

1、表的讀取順序

2、資料讀取操作的操作類型

3、哪些索引可以使用

4、哪些索引被實際使用

5、表之間的使用

6、每張表有多少行被優化器查詢

二、怎麼使用?

1、explain + sql 

2、執行計劃包含的資訊

SQL中explaind的使用介紹

id:select 查詢到序列号,包含一組數字,表示查詢中select子句或操作表的順序。

id有三種值:決定表的讀取順序

(1)id相同,表執行順序由上到下,與sql中順序無關

SQL中explaind的使用介紹

(2)id不同,

SQL中explaind的使用介紹

如以上子查詢,id的序号會遞增,id值越大優先級越高,越先被執行

(3)id相同有不同

SQL中explaind的使用介紹

id 如果相同,可以認為是一組,從上往下順序執行;(衍生 = derived)

在所有組中,id值越大,優先級越高,越先執行

select_type  一般有六種類别

(1)simple

SQL中explaind的使用介紹

簡單查詢,查詢中不包含子查詢或者union等任何複雜查詢

(2)primary

SQL中explaind的使用介紹

查詢中若包含任何複雜的子查詢,則最外層被标記為paimary,俗稱是雞蛋殼

(3)subquery

在select或where清單包含了子查詢

SQL中explaind的使用介紹

(4)derived

在from清單中包含的子查詢被标記為derived(衍生),mysql會遞歸執行這些子查詢,把結果放在臨時表裡(臨時表會增加系統負擔,但有時不得不用)

(5)union

若第二個select出現在union之後,則被标記為union;若union包含在from子句的子查詢中,外層select将被标記為:derived

SQL中explaind的使用介紹

(6)union result

兩種union結果的合并

SQL中explaind的使用介紹

table  (就是顯示這一行的資料是哪一張表的,不多介紹)

type   通路類型判斷(跟語句性能息息相關),顯示查詢使用了何種類型  不要為了優化而優化

從最好到最差依次是:

system > const  > eq_ref > ref > range > index > all(全表掃描)

一般來說,得保證查詢級别至少達到range級别,最好能達到ref

(1)system 

表隻要一行記錄(等于系統表),這是const類型的特例,平時不會出現,這個也可以忽略不計

(2)const

SQL中explaind的使用介紹

表示通過索引一次就找到了,const用于比較primary key 或者 unique索引,因為隻比對一行資料,是以很快,如将主鍵置于where清單中,mysql就能将該查詢轉換為一個常量。

(3)eq_ref   

唯一性索引掃描。對于每個索引建,表中隻有一條記錄與之比對。常見于主鍵或唯一索引掃描

SQL中explaind的使用介紹

(4)ref

非唯一性索引掃描,傳回比對某個單獨值得所有行,本質上也是一種索引通路,它傳回所有比對某個單獨值得行,然而它可能會找到多個符合條件的行,是以他應該屬于查找和掃描的混合體

(5)range

隻檢索給定範圍的行,使用一個索引來選擇行,key 列顯示使用了哪個索引,一般就是你的where語句中出現了between、<、>、in等的查詢(mysql5.7支援in走索引),這種範圍掃描索引掃描比全表掃描好,因為它至于要開始索引的某一點,而結束語另一點,不用掃描全部索引

(6)index

full index scan(全索引掃描),index與all差別為index類型隻周遊索引樹,這通常比all塊,因為索引檔案通常比資料檔案小。(也就是說雖然all和index都是讀全表,單index是從索引中讀取的,而all是從硬碟中讀的)

(7)all

全表掃描

possible_keys  顯示可能應用在這張表中的索引,一個或多個。查詢涉及到的字段上若存在索引,則該索引将被列出,但不一定被查詢實際使用

key   實際上使用到的索引,如果為null,則沒有使用索引

查詢中若使用了(覆寫索引),則該索引僅出現在key清單中,

覆寫索引:

就是說我select 字段1,字段2,與我建立的符合索引個數和順序一緻。例如:建的索引 字段一   字段二,查的也是字段一、字段二。了解方式一:就是select 的資料列隻用從索引中就能夠取得,不必讀取資料行,mysql可以利用索引傳回select清單中的字段,而不必根據索引再次讀取資料檔案,換句話說查詢列要被所建的索引覆寫,一個索引包含了(或覆寫了)滿足查詢結果的資料就叫做覆寫索引。

注意:如果要使用覆寫索引,一定要注意select清單中隻取出需要的列,不可select * ,因為如果将所有字段一起做索引會導緻索引檔案過大,查詢性能下降。索引的字段不隻包含查詢列,還需要包含查詢條件、排序等。

key_len :表示索引中使用的位元組數,可通過該列計算查詢中使用的索引的長度,在不損失精确性的情況下,長度越短越好。

key_len顯示的值為索引字段的最大可能長度,并非實際使用長度,即key_len是根據表定義計算而得,不是通過表内檢索出的。

ref :顯示索引的哪一列被使用了,如果可能的話,是一個常數。哪些列或常量被用于查找索引列上的值

rows:根據表統計資訊及索引選用情況,大緻估算出找到所需的記錄所需要讀取的行數(越少越好),每張表有多少行被優化器查詢

extra:包含不适合在其他列顯示但是很重要的額外資訊

(1)using filrsort(九死一生)

說明mysql會對資料使用一個外部的索引排序,而不是按照表内的索引順序進行讀取。mysql中無法利用索引完成的排序操作稱為"檔案排序",一旦出現這種情況很危險。

(2)using index

使用到了索引、這種情況是好事。表示響應的select操作中使用了覆寫索引(cocering index),避免通路了表的資料行,效率不錯!如果同時出現了using where,表名索引被用來執行索引鍵值的查找;如果沒有同時出現using where,表名索引用來讀取資料而非執行查找動作。

(3)using where

使用了where條件

(4)using temporary(十死無生)

使用了臨時表儲存中間結果。mysql在對查詢結果排序時使用臨時表。常見于排序order by和分組查詢 group by。(group by 最好與索引的字段、順序一緻)

範圍(> < in)可能導緻索引失效

左連接配接索引加在右表

右連接配接索引加在左表