一張圖徹底搞定 explain

zhangdeTalk發表於2019-12-26
AI

explain關鍵字可以模擬MySQL優化器執行SQL語句，可以很好的分析SQL語句或表結構的效能瓶頸。

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 //包含不適合在其它列中顯示但十分重要的額外資訊

圖片版

一張圖搞定explain

文字版

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

一張圖搞定 explain

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

一張圖搞定 explain

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

一張圖搞定 explain

select_type欄位

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

一張圖搞定 explain

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));

一張圖搞定 explain

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));

一張圖搞定 explain

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;

一張圖搞定 explain

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;

一張圖搞定 explain

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;

一張圖搞定 explain

2. system
表只有一行記錄（等於系統表），這是const型別的特列，平時不大會出現，可以忽略
3. const
表示通過索引一次就找到了，const用於比較primary key或uique索引，因為只匹配一行資料，所以很快，如主鍵置於where列表中，MySQL就能將該查詢轉換為一個常量
例子：
explain select * from teacher where teacher_no = 'T2010001';

一張圖搞定 explain

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

一張圖搞定 explain

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

一張圖搞定 explain

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

一張圖搞定 explain

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

一張圖搞定 explain

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

一張圖搞定 explain

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

一張圖搞定 explain

10. ALL
Full Table Scan，將遍歷全表以找到匹配行
例子：
explain select * from subject;

一張圖搞定 explain

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;

一張圖搞定 explain

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;

一張圖搞定 explain

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列表中的欄位，而不必根據索引再次讀取資料檔案，即查詢列要被所建的索引覆蓋

一張圖搞定 explain

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

一張圖搞定 explain

5. Using join buffer
使用了連線快取
例子：
explain select student.*,teacher.*,subject.* from student,teacher,subject;

一張圖搞定 explain

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

一張圖搞定 explain

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

一張圖搞定 explain

8. Select tables optimized away
SELECT操作已經優化到不能再優化了（MySQL根本沒有遍歷表或索引就返回資料了）
例子：
explain select min(id) from subject;

一張圖搞定 explain

使用的資料表

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);

阿德

相關文章