一張圖徹底搞定 explain

explain的用途

1. 表的讀取順序如何
2. 資料讀取操作有哪些操作類型
3. 哪些索引可以使用
4. 哪些索引被實際使用
5. 表之間是如何引用
6. 每張表有多少行被優化器查詢
......           

explain的執行效果

mysql> explain select * from subject where id = 1 \G
******************************************************
           id: 1
  select_type: SIMPLE
        table: subject
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
******************************************************           

explain包含的字段

1. id //select查詢的序列号，包含一組數字，表示查詢中執行select子句或操作表的順序
2. select_type //查詢類型
3. table //正在通路哪個表
4. partitions //比對的分區
5. type //通路的類型
6. possible_keys //顯示可能應用在這張表中的索引，一個或多個，但不一定實際使用到
7. key //實際使用到的索引，如果為NULL，則沒有使用索引
8. key_len //表示索引中使用的位元組數，可通過該列計算查詢中使用的索引的長度
9. ref //顯示索引的哪一列被使用了，如果可能的話，是一個常數，哪些列或常量被用于查找索引列上的值
10. rows //根據表統計資訊及索引選用情況，大緻估算出找到所需的記錄所需讀取的行數
11. filtered //查詢的表行占表的百分比
12. Extra //包含不适合在其它列中顯示但十分重要的額外資訊           

圖檔版

文字版

id字段

1. id相同

執行順序從上至下
例子：
explain select subject.* from subject,student_score,teacher where subject.id = student_id and subject.teacher_id = teacher.id;
讀取順序：subject > teacher > student_score           

2. id不同

如果是子查詢，id的序号會遞增，id的值越大優先級越高，越先被執行
例子：
explain select score.* from student_score as score where subject_id = (select id from subject where teacher_id = (select id from teacher where id = 2));
讀取順序：teacher > subject > student_score           

3. id相同又不同

id如果相同，可以認為是一組，從上往下順序執行
在所有組中，id值越大，優先級越高，越先執行
例子：
explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id
 -> union 
 -> select subject.* from subject right join teacher on subject.teacher_id = teacher.id;
 讀取順序：2.teacher > 2.subject > 1.subject > 1.teacher           

select_type字段

1. SIMPLE

簡單查詢，不包含子查詢或Union查詢
例子：
explain select subject.* from subject,student_score,teacher where subject.id = student_id and subject.teacher_id = teacher.id;           

2. PRIMARY

查詢中若包含任何複雜的子部分，最外層查詢則被标記為主查詢
例子：
explain select score.* from student_score as score where subject_id = (select id from subject where teacher_id = (select id from teacher where id = 2));           

3. SUBQUERY

在select或where中包含子查詢
例子：
explain select score.* from student_score as score where subject_id = (select id from subject where teacher_id = (select id from teacher where id = 2));           

4. DERIVED

在FROM清單中包含的子查詢被标記為DERIVED（衍生），MySQL
會遞歸執行這些子查詢，把結果放在臨時表中
備注：
MySQL5.7+ 進行優化了，增加了derived_merge（派生合并），預設開啟，可加快查詢效率           

5. UNION

若第二個select出現在uion之後，則被标記為UNION
例子：
explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id
 -> union 
 -> select subject.* from subject right join teacher on subject.teacher_id = teacher.id;           

6. UNION RESULT

從UNION表擷取結果的select
例子：
explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id
 -> union 
 -> select subject.* from subject right join teacher on subject.teacher_id = teacher.id;           

type字段

NULL>system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL //最好到最差
備注：掌握以下10種常見的即可
NULL>system>const>eq_ref>ref>ref_or_null>index_merge>range>index>ALL           

1. NULL

MySQL能夠在優化階段分解查詢語句，在執行階段用不着再通路表或索引
例子：
explain select min(id) from subject;           

2. system

表隻有一行記錄（等于系統表），這是const類型的特列，平時不大會出現，可以忽略           

3. const

表示通過索引一次就找到了，const用于比較primary key或uique索引，因為隻比對一行資料，是以很快，如主鍵置于where清單中，MySQL就能将該查詢轉換為一個常量
例子：
explain select * from teacher where teacher_no = 'T2010001';           

4. eq_ref

唯一性索引掃描，對于每個索引鍵，表中隻有一條記錄與之比對，常見于主鍵或唯一索引掃描
例子：
explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id;           

5. ref

非唯一性索引掃描，傳回比對某個單獨值的所有行
本質上也是一種索引通路，傳回所有比對某個單獨值的行
然而可能會找到多個符合條件的行，應該屬于查找和掃描的混合體
例子：
explain select subject.* from subject,student_score,teacher where subject.id = student_id and subject.teacher_id = teacher.id;           

6. ref_or_null

類似ref，但是可以搜尋值為NULL的行
例子：
explain select * from teacher where name = 'wangsi' or name is null;           

7. index_merge

表示使用了索引合并的優化方法
例子：
explain select * from teacher where id = 1 or teacher_no = 'T2010001' .           

8. range

隻檢索給定範圍的行，使用一個索引來選擇行，key列顯示使用了哪個索引
一般就是在你的where語句中出現between、<>、in等的查詢。
例子：
explain select * from subject where id between 1 and 3;           

9. index

Full index Scan，Index與All差別：index隻周遊索引樹，通常比All快
因為索引檔案通常比資料檔案小，也就是雖然all和index都是讀全表，但index是從索引中讀取的，而all是從硬碟讀的。
例子：
explain select id from subject;           

10. ALL

Full Table Scan，将周遊全表以找到比對行
例子：
explain select * from subject;           

table字段

資料來自哪張表           

possible_keys字段

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

key字段

實際使用到的索引，如果為NULL，則沒有使用索引
查詢中若使用了覆寫索引（查詢的列剛好是索引），則該索引僅出現在key清單           

key_len字段

表示索引中使用的位元組數，可通過該列計算查詢中使用的索引的長度
在不損失精确度的情況下，長度越短越好
key_len顯示的值為索引字段最大的可能長度，并非實際使用長度
即key_len是根據定義計算而得，不是通過表内檢索出的           

ref字段

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

rows字段

根據表統計資訊及索引選用情況，大緻估算出找到所需的記錄所需讀取的行數           

partitions字段

比對的分區           

filtered字段

查詢的表行占表的百分比           

Extra字段

包含不适合在其它列中顯示但十分重要的額外資訊           

1. Using filesort

說明MySQL會對資料使用一個外部的索引排序，而不是按照表内的索引順序進行讀取
MySQL中無法利用索引完成的排序操作稱為“檔案排序”
例子：
explain select * from subject order by name;           

2. Using temporary

使用了臨時表儲存中間結果，MySQL在對結果排序時使用臨時表，常見于排序order by 和分組查詢group by
例子：
explain select subject.* from subject left join teacher on subject.teacher_id = teacher.id
 -> union 
 -> select subject.* from subject right join teacher on subject.teacher_id = teacher.id;           

3. Using index

表示相應的select操作中使用了覆寫索引（Covering Index）,避免通路了表的資料行，效率不錯！
如果同時出現using where，表明索引被用來執行索引鍵值的查找
如果沒有同時出現using where，表明索引用來讀取資料而非執行查找動作
例子：
explain select subject.* from subject,student_score,teacher where subject.id = student_id and subject.teacher_id = teacher.id;
備注：
覆寫索引：select的資料列隻用從索引中就能夠取得，不必讀取資料行，MySQL可以利用索引傳回select清單中的字段，而不必根據索引再次讀取資料檔案，即查詢列要被所建的索引覆寫           

4. Using where

使用了where條件
例子：
explain select subject.* from subject,student_score,teacher where subject.id = student_id and subject.teacher_id = teacher.id;           

5. Using join buffer

使用了連接配接緩存
例子：
explain select student.*,teacher.*,subject.* from student,teacher,subject;           

6. impossible where

where子句的值總是false，不能用來擷取任何元組
例子：
explain select * from teacher where name = 'wangsi' and name = 'lisi';           

7. distinct

一旦mysql找到了與行相聯合比對的行，就不再搜尋了
例子：
explain select distinct teacher.name from teacher left join subject on teacher.id = subject.teacher_id;           

8. Select tables optimized away

SELECT操作已經優化到不能再優化了（MySQL根本沒有周遊表或索引就傳回資料了）
例子：
explain select min(id) from subject;           

使用的資料表

create table subject(
 -> id int(10) auto_increment,
 -> name varchar(20),
 -> teacher_id int(10),
 -> primary key (id),
 -> index idx_teacher_id (teacher_id));//學科表
 
create table teacher(
 -> id int(10) auto_increment,
 -> name varchar(20),
 -> teacher_no varchar(20),
 -> primary key (id),
 -> unique index unx_teacher_no (teacher_no(20)));//教師表
 
 create table student(
 -> id int(10) auto_increment,
 -> name varchar(20),
 -> student_no varchar(20),
 -> primary key (id),
 -> unique index unx_student_no (student_no(20)));//學生表
 
 create table student_score(
 -> id int(10) auto_increment,
 -> student_id int(10),
 -> subject_id int(10),
 -> score int(10),
 -> primary key (id),
 -> index idx_student_id (student_id),
 -> index idx_subject_id (subject_id));//學生成績表
 
 alter table teacher add index idx_name(name(20));//教師表增加名字普通索引
 
 資料填充：
 insert into student(name,student_no) values ('zhangsan','20200001'),('lisi','20200002'),('yan','20200003'),('dede','20200004');
 
 insert into teacher(name,teacher_no) values('wangsi','T2010001'),('sunsi','T2010002'),('jiangsi','T2010003'),('zhousi','T2010004');
 
 insert into subject(name,teacher_id) values('math',1),('Chinese',2),('English',3),('history',4);
 
insert into student_score(student_id,subject_id,score) values(1,1,90),(1,2,60),(1,3,80),(1,4,100),(2,4,60),(2,3,50),(2,2,80),(2,1,90),(3,1,90),(3,4,100),(4,1,40),(4,2,80),(4,3,80),(4,5,100);