MySQL學習之explain

圣辉發表於2024-04-27

from之後的查詢得到的表叫做衍生表,是臨時表資料,生成臨時表之後的資料是無法使用索引的,如果資料量大查詢效率就會比較低,這就是查詢要儘量少使用子查詢這些臨時表。

explain詳解

id:

表示查詢序號,也可以表示優先順序;當值都不一樣的時候,值越大表示優先順序越高,越先執行;當值都一樣的時候,按照從上到下的順序去執行。

select_type:

表示查詢的型別,是簡單型別還是複雜型別。

  1. simple:簡單查詢。查詢不包含子查詢和union。
  2. primary:複雜查詢中最外層的 select。
  3. subquery:包含在 select 中的子查詢(不在 from 子句中)。
  4. derived:包含在 from 子句中的子查詢。MySQL會將結果存放在一個臨時表中,也稱為派生表(derived的英文含義)。

table:

表示查詢的表名,有些真實表有表名,臨時表也會起一個表名,複雜查詢可能沒有表名。表示本行訪問的表。當 from 子句中有子查詢時,table列是 <derivenN> 格式,表示當前查詢依賴 id=N 的查詢,於是先執行 id=N 的查詢。當有 union 時,UNION RESULT 的 table 列的值為<union1,2>,1和2表示參與 union 的 select 行id。

partitions:

表示分割槽資訊,很少使用到。

type:

索引使用型別,很重要。表示查詢範圍。最常使用的是有system,const,eq_ref,ref,range,index,ALL。這些查詢效率從左到右依次降低。一般來說我們要將查詢最佳化到range級別,最好是到ref級別。

  1. null表示查詢最佳化階段就可以確認要查詢的資料,不用再去查表了。類似掃描個別索引就能獲取到值。
  2. system,是查詢結果有且僅有一條資料,是const的特例。const是使用主鍵索引,或者唯一索引,查詢結果最多隻有一條資料,掃描次數很少,效率高。
  3. eq_ref,是equals,也就是說如果有連表查詢,被連的表的查詢條件是主鍵或者唯一鍵,能確認被連表只能查詢一條資料。簡單查詢不會出現這樣的結果。
  4. ref,表示沒有使用唯一索引,或者使用了唯一索引的部分,查詢到的資料不是唯一的,可能多個也可能一個。
  5. range,明確的是範圍查詢,常在int,between和and,>,<,>=等,是使用到了索引的查詢,查詢範圍資料,資料量較多。
  6. index,表示掃描索引就能獲取到全部資料,一般是查詢二級索引,並且要查詢的列都在二級索引上,可以避免回表操作。
  7. all,表示全表掃描,不會再使用索引了,效率最低。

注意:查詢語句優先查詢二級索引,因為二級索引更小,儲存的資料量更多,如果不滿足,才會去主鍵索引上去查詢。

possible_keys:

表示可能會用到的索引值,可能有多個,也可能為空。

key:

表示真正使用到的索引,該值有可能沒有在possible_keys中顯式,也可能在其中顯式;總的來說兩個沒有必然聯絡,這個是要看MySQL的查詢最佳化器的最佳化。

key_len:

表示使用到的索引的長度值。透過這個值可以算出具體使用了索引中的哪些列。

key_len計算規則如下:

字串,char(n)和varchar(n),5.0.3以後版本中,n均代表字元數,而不是位元組數,如果是utf-8,一個數字或字母佔1個位元組,一個漢字佔3個位元組
  char(n):如果存漢字長度就是 3n 位元組
  varchar(n):如果存漢字則長度是 3n + 2 位元組,加的2位元組用來儲存字串長度,因為varchar是變長字串
數值型別
  tinyint:1位元組
  smallint:2位元組
  int:4位元組
  bigint:8位元組  
時間型別 
  date:3位元組
  timestamp:4位元組
  datetime:8位元組
如果欄位允許為 NULL,需要1位元組記錄是否為 NULL。
索引最大長度是768位元組,當字串過長時,mysql會做一個類似左字首索引的處理,將前半部分的字元提取出來做索引。所以在建立索引的時候要注意索引個數。

ref:

這一列顯示了在key列記錄的索引中,表查詢值所用到的列或常量,常見的有:const(常量),欄位名(例:film.id)

rows:

表示本次查詢預估的條目數,越少越好。但是這個值並不準確。

filtered 列:

該列是一個百分比的值,rows * filtered/100 可以估算出將要和 explain 中前一個表進行連線的行數(前一個表指explain 中的id值比當前表id值小的表)

Extra列:

這一列展示的是額外資訊,並不是很準確的,只能是一個參考。常見的重要值如下
Using index:使用覆蓋索引。也就是說查詢的列在二級索引上即可獲取到,不用再回表操作。
Using where:使用 where 語句來處理結果,並且查詢的列未被索引覆蓋,一般來說要回表,效率降低了。
Using temporary:mysql需要建立一張臨時表來處理查詢。出現這種情況一般是要進行最佳化的,首先是想到用索引來最佳化。類似在查詢條件上加索引,或者令查詢條件使用到索引。例如針對一個沒有加索引的列去重,就要先查詢這一列的全部資料,然後放到臨時表中,再去重,所以要進行最佳化。
注意:能夠使用索引的前提條件是索引檔案中有這些資料,才能使用到索引,否則是無法使用索引的。

索引最佳最佳化實踐

使用等值查詢,可以使用到索引;
聯合索引,查詢條件符合最左字首原則,保證能命中索引;
不要對索引進行運算(型別轉換,函式,數學計算),因為這些查詢條件索引結構中沒有,會退化成全表掃描;
如果聯合索引中有範圍查詢,那麼後面的索引就失效了;
儘量使用覆蓋索引,儘量少使用select *,避免回表操作;
is null,is not null一般條件下無法使用索引;
使用不等於,not in,not exist會導致索引失效,大於小於不大於不小於這些會根據最佳化判斷是否走索引。有時候判斷查詢結果過多或者過少都會不走索引,而是去全表掃描;
like模糊查詢使用右側模糊,左側模糊索引無效;
避免查詢條件進行型別轉換,如果查詢列是字串,那麼查詢條件要加上引號;
少用in和or,當資料量大的情況下,可能不走索引,而去全表掃描;

相關文章