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