一文帶你看懂MySQL執行計劃

2022-04-14 10:02:17

一、id ，select 查詢序列號
二、select_type，查詢語句型別
三、table，查詢涉及的表或衍生表
四、partitions查詢涉及到的分割區
五、type提供了判斷查詢是否高效的重要依據依據
六、possible_keys：指示MySQL可以從中選擇查詢此表中的行的索引。
七、key：MySQL查詢實際使用到的索引。
八、key_len：表示索引中使用的位元組數（只計算利用索引作為index key的索引長度，不包括用於group by/order by的索引長度）
九、ref：顯示該表的索引欄位關聯了哪張表的哪個欄位
十、rows：根據表統計資訊及選用情況，大致估算出找到所需的記錄或所需讀取的行數，數值越小越好
十一、filtered：返回結果的行數佔讀取行數的百分比，值越大越好
十二、extra：包含不適合在其他列中顯示但十分重要的額外資訊。常見的值如下

小結：

總結

前言

專案開發中，效能是我們比較關注的問題，特別是資料庫的效能；作為一個開發，經常和SQL語句打交道，想要寫出合格的SQL語句，我們需要了解SQL語句在資料庫中是如何掃描表、如何使用索引的；

MySQL提供explain/desc命令輸出執行計劃，我們通過執行計劃優化SQL語句。

下面我們以MySQL5.7為例瞭解一下執行計劃：

注：文中涉及到的表結構、sql語句只是為了理解explain/desc執行計劃，有不合理之處勿噴

explain/desc 用法

只需要在我們的查詢語句前加explain/desc即可

準備資料表

-- 建立user表
create table user(
    id int,
    name varchar(20),
    role_id int,
    primary key(id)
)engine=innodb default charset=utf8;
-- 建立role表
create table role(
    id int,
    name varchar(20),
    primary key(id)
)engine=innodb default charset=utf8;

查詢，執行計劃

 explain select * from user;

執行計劃輸出有id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、extra，這些內容有什麼意義，下面簡單介紹一下

explain/desc 輸出詳解

一、id ，select 查詢序列號

1 id相同，從上往下一次執行；

-- 左關聯
explain select * from user a left join user b on a.id=b.id;
-- 右關聯
explain select * from user a right join user b on a.id=b.id;

通過left join 和 right join 驗證；id一樣（注意執行計劃的table列），left join 先掃描a表，再掃描b表；rightjoin 先掃描b表，再掃描a表

2 id不同，id越大優先順序越高，越先被執行

desc select * from user where role_id=(select id from role where name='開發');

我們編寫查詢角色為開發的使用者；可以知道先查詢角色name為開發角色id，查詢序列號為2；再根據角色id查詢使用者，查詢序列號為1；

二、select_type，查詢語句型別

(1)SIMPLE(簡單SELECT,不使用UNION或子查詢等)

explain select * from user;

(2)PRIMARY(查詢中若包含任何複雜的子部分,最外層的select被標記為PRIMARY)

 desc select * from user where role_id=(select id from role where name='開發');

(3)UNION(UNION中的第二個或後面的SELECT語句)

desc select * from user where name='Java' union select * from user where role_id=1;

(4)DEPENDENT UNION(UNION中的第二個或後面的SELECT語句，取決於外面的查詢)

desc select * from user a 
     where id in ( 
         select b.id from user b where b.id=a.id union 
         select c.id from role c where c.id=a.role_id 
     );

(5)UNION RESULT(UNION的結果)

desc select * from user where name='Java' union select * from user where role_id=1;

(6)SUBQUERY(子查詢中的第一個SELECT)

 desc select * from user where role_id=(select id from role where name='開發');

(7)DEPENDENT SUBQUERY(子查詢中的第一個SELECT，取決於外面的查詢)

desc select * from user where role_id = ( select id from role where id=user.id );

(8)DERIVED(派生/衍生表的SELECT, FROM子句的子查詢)

desc select * from ( select * from user where name='Java' union select * from user where role_id=1 ) a;

(9) MATERIALIZED(物化子查詢) 在SQL執行過程中，第一次需要子查詢結果時執行子查詢並將子查詢的結果儲存為臨時表，後續對子查詢結果集的存取將直接通過臨時表獲得。

(10)UNCACHEABLE SUBQUERY(一個子查詢的結果不能被快取，必須重新評估外連線的第一行)

(11)UNCACHEABLE UNION(UNION查詢的結果不能被快取)

三、table，查詢涉及的表或衍生表

table分別user、role表

四、partitions查詢涉及到的分割區

建立分割區表，

-- 建立分割區表，
-- 按照id分割區，id<100 p0分割區，其他p1分割區
create table user_partitions (id int auto_increment,
    name varchar(12),primary key(id))
    partition by range(id)(
        partition p0 values less than(100),
        partition p1 values less than maxvalue
    );

desc select * from user_partitions where id>200;

查詢id大於200（200>100，p1分割區）的記錄，檢視執行計劃，partitions是p1，符合我們的分割區規則

五、type提供了判斷查詢是否高效的重要依據依據

通過type欄位, 我們判斷此次查詢是全表掃描還是索引掃描等，下面簡單介紹一下常用的type；

（1）system: 表中只有一條資料，相當於系統表；這個型別是特殊的const型別；

（2）const:主鍵或者唯一索引的常數查詢，表格最多隻有1行記錄符合查詢，通常const使用到主鍵或者唯一索引進行定值查詢。

主鍵

-- 建立user表
create table user(id int primary key, name varchar(20), role_id int );
-- 插入一條記錄
insert into user values (1, 'a', 1 );
-- 按id查詢
desc select * from user where id=1;
-- 按role_id查詢
desc select * from user where role_id=1;

分別檢視按id和按role_id查詢的執行計劃；發現按主鍵id查詢，執行計劃type為const

將主鍵設定為id和role_id

-- 刪除主鍵
alter table user drop primary key;
-- 設定主鍵id,role_id
alter table user add primary key(id,role_id);
-- 按照部分主鍵查詢
desc select * from user where id=1;
-- 按照部分主鍵查詢
desc select * from user where role_id=1;
-- 按照全部主鍵查詢
desc select * from user where id=1 and role_id=1;

發現只有按照全部主鍵查詢，執行計劃type為const

唯一索引

-- 刪除主鍵
alter table user drop primary key;
-- 設定主鍵
alter table user add primary key(id);
-- 設定role_id為唯一索引
alter table user add unique key uk_role(role_id);
-- 按照唯一索引查詢
desc select * from user where role_id=1;

發現按role_id唯一索引查詢；執行計劃type為const

普通索引

-- 將role_id設定成普通索引
-- 刪除唯一索引
alter table user drop index uk_role;
-- 設定普通索引
alter table user add index index_role(role_id);
-- 按照普通索引查詢
desc select * from user where role_id=1;

發現按role_id普通索引查詢；執行計劃type為ref

const用於主鍵或唯一索引查詢；將PRIMARY KEY或UNIQUE索引的所有部分與常數值進行比較時使用；與索引型別有關。

（3）eq_ref: 除了system和const型別之外,效率最高的連線型別；唯一索引掃描，對於每個索引鍵，表中只有一條記錄與之對應；常用於主鍵或唯一索引掃描

準備資料

-- 建立teacher表
create table teacher( id int primary key, name varchar(20), tc_id int );
-- 插入3條資料
insert into teacher values (1,'a',1),(2,'b',2),(3,'c',3);
-- 建立teacher_card表
create table teacher_card( id int primary key, remark varchar(20) );
-- 插入2條資料
insert into teacher_card values (1,'aa'),(2,'bb');
-- 關聯查詢，執行計劃
desc select * from teacher t  join  teacher_card tc on t.tc_id=tc.id  where t.name='a';

執行計劃

根據上面的知識；可知id相同，由上至下依次執行，分析結果可知：

先查詢t表就是teacher表中name欄位為a的記錄，由於name欄位沒有索引，所以全表掃描（type:ALL），一共有3條記錄，掃描了3行（rows:3）,1條符合條件（filtered:33.33 1/3）；

再查詢tc即teacher_card表使用主鍵和之前的t.tc_id關聯；由於是關聯查詢，並且是通過唯一索引（主鍵）進行查詢，僅能返回1或0條記錄，所以type為eq_ref。

 -- 刪除teacher_card主鍵
 alter table teacher_card drop primary key;
 -- 這是teacher_card.id為唯一索引
 alter table teacher_card add unique key ui_id(id);
 -- 關聯查詢，執行計劃
 desc select * from teacher t  join  teacher_card tc on t.tc_id=tc.id  where t.name='a';

分析結果，將teacher_card的id設定為唯一索引，type為eq_ref；滿足僅能返回1或0條記錄。

 -- 刪除teacher_card唯一索引
 alter table teacher_card drop index ui_id;
 -- 設定teacher_card.id為普通索引
 alter table teacher_card add index index_id(id);
 -- 關聯查詢，執行計劃
 desc select * from teacher t  join  teacher_card tc on t.tc_id=tc.id  where t.name='a';

分析結果，將teacher_card的id設定為普通索引，type為ref；不滿足僅能返回1或0條記錄。

equ_ref用於唯一索引查詢，對每個索引鍵，表中只有一條或零條記錄與之匹配。

（4）ref：此型別通常出現在多表的 join 查詢, 針對於非唯一或非主鍵索引, 或者是使用了最左字首規則索引的查詢（換句話說，連線不能基於鍵值選擇單行,可能是多行）。

 -- teacher.tc_id無索引，執行計劃
 desc select * from teacher t  join  teacher_card tc on t.tc_id=tc.id  where tc.remark='aa';
 -- 設定teacher.tc_id為普通索引
 alter table teacher add index index_tcid(tc_id);
 -- teacher.tc_id有索引，執行計劃
 desc select * from teacher t  join  teacher_card tc on t.tc_id=tc.id  where tc.remark='aa';

先查詢tc表就是teacher_card表中remark欄位為aa的記錄，由於remark欄位沒有索引，所以全表掃描（type:ALL），一共有2條記錄，掃描了2行（rows:2）,1條符合條件（filtered:50,1/2）；

tc_id無索引再查詢t即teacher表使用tc_id和之前的tc.id關聯；由於是關聯查詢，不是索引,全表掃描，所以type為ALL。

tc_id有索引再查詢t即teacher表使用tc_id和之前的tc.id關聯；由於是關聯查詢，索引掃描，能返回0或1或多條記錄，所以type為ref。

（5）range: 表示使用索引範圍查詢, 通過索引欄位範圍獲取表中部分資料記錄. 這個型別通常出現在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。

 desc select * from teacher where id>2;
 desc select * from teacher where id in (1,2,3);

（6）index: 掃描索引樹

如果索引是複合索引，並且複合索引列滿足select所需的所有資料，則僅掃描索引樹。在這種情況下，Extra為Using index。僅索引掃描通常比ALL索引的大小通常小於表資料更快。

索引列不滿足select所需的所有資料，此時需要回表掃描；按索引順序查詢資料行。Uses index沒有出現在Extra列中。

 -- 檢視teacher表索引
 show index from teacher;
 -- 查詢tc_id，執行計劃
 desc select tc_id from teacher;
 -- 按tc_id索引分組，執行計劃
 desc select name from teacher group by tc_id;

查詢tc_id，掃描索引樹，type為index，Extra為Using index；

按tc_id分組，全表掃描，以按索引順序查詢資料行。

（7）ALL: 全表掃描,沒有任何索引可以使用時。這是最差的情況，應該避免。

 -- 檢視teacher表索引
 show index from teacher;
 desc select * from teacher where name='a';

由於name欄位不存在索引，type:ALL全表掃描；可通過對name欄位設定索引進行優化。

六、possible_keys：指示MySQL可以從中選擇查詢此表中的行的索引。

七、key：MySQL查詢實際使用到的索引。

-- 建立course表
create table course(id int primary key,name varchar(20),t_id int,key index_name(name),key index_tid(t_id));
-- 插入資料
insert into course values (1,'Java',1), (2,'Python',2);
-- 查詢1
desc select * from course where name='Java' or t_id=1;
-- 查詢2
desc select * from course where name='Java';

檢視執行計劃

查詢1，查詢name為Java或t_id為1的記錄；可能用到的索引possible_keys為index_name，index_tid；實際用到的索引key為NULL

查詢2，查詢name為Java；可能用到的索引possible_keys為index_name；實際用到的索引key為index_name

八、key_len：表示索引中使用的位元組數（只計算利用索引作為index key的索引長度，不包括用於group by/order by的索引長度）

一般地，key_len 等於索引列型別位元組長度，例如int型別為4 bytes，bigint為8 bytes；
如果是字串型別，還需要同時考慮字元集因素，例如utf8字元集1個字元佔3個位元組，gbk字元集1個字元佔2個位元組
若該列型別定義時允許NULL，其key_len還需要再加 1 bytes
若該列型別為變長型別，例如 VARCHAR（TEXTBLOB不允許整列建立索引，如果建立部分索引也被視為動態列型別），其key_len還需要再加 2 bytes

字元集會影響索引長度、資料的儲存空間，為列選擇合適的字元集；變長欄位需要額外的2個位元組，固定長度欄位不需要額外的位元組。而null都需要1個位元組的額外空間，所以以前有個說法：索引欄位最好不要為NULL，因為NULL讓統計更加複雜，並且需要額外一個位元組的儲存空間。

-- key_len的長度計算公式：

-- varchar(len)變長欄位且允許NULL      : len*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(變長欄位)
-- varchar(len)變長欄位且不允許NULL    : len*(Character Set：utf8=3,gbk=2,latin1=1)+2(變長欄位)

-- char(len)固定欄位且允許NULL         : len*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)
-- char(len)固定欄位且不允許NULL       : len*(Character Set：utf8=3,gbk=2,latin1=1)

下面驗證一下結論：

-- 建立user_info表
create table user_info( id int comment '主鍵',
    name varchar(10) character set utf8 not null comment '姓名',
    alias_name varchar(10) character set utf8 comment '姓名',
    role_id int comment '角色id',
    remark varchar(10) character set gbk not null comment '備註',
    primary key(id),
    key index_name(name),
    key index_alias(alias_name),
    key index_role(role_id),
    key index_remark(remark)
)engine=innodb;
-- 插入資料
insert into user_info values (1,'a','aa',1,'aaa');
-- 按主鍵查詢
desc select * from user_info where id=1;
-- 按索引role_id查詢
desc select * from user_info where role_id=1;

按照主鍵id查詢possible_keys為primary，實際用到的索引key為primary，key_len為4；

按照索引role_id查詢possible_keys為index_role，實際用到的索引key為index_role，key_len為5；

分析結果：按照role_id比按照id（均為int型別）的key_len大5-4=1，因為role_id可以為null，需要一個標誌位；

 -- 按照name查詢 varchar(10) not null utf8 一個字元佔3個位元組 10*3+2(變長)=32
 desc select * from user_info where name='a';
 -- 按照alias_name查詢 varchar(10) utf8 一個字元佔3個位元組 10*3+2(變長)+1(null標誌位)=33
 desc select * from user_info where alias_name='aa';

按照name查詢possible_keys為index_name，實際用到的索引key為index_name，key_len為32=10*3+2(變長)；

按照alias_name查詢possible_keys為index_alias，實際用到的索引key為index_alias，key_len為33=10*3+2(變長)+1(null標誌位)；

分析結果：name與remark均為變長且字元集一致，remark可以為null，33-32=1多佔一個標誌位；

 -- 按照name查詢 varchar(10) not null utf8 一個字元佔3個位元組 10*3+2(變長)=32
 desc select * from user_info where name='a';
 -- 按照remark查詢 varchar(10) not null gbk 一個字元佔2個位元組 10*2+2(變長)=22
 desc select * from user_info where remark='aaa';

按照name查詢possible_keys為index_name，實際用到的索引key為index_name，key_len為32=10*3(utf8一個字元3個位元組)+2(變長)；

按照remark查詢possible_keys為index_remark，實際用到的索引key為index_remark，key_len為22=10*2(gbk一個字元2個位元組)+2(變長)；

分析結果：name與remark均為變長但字元集不一致，分別為utf8與gbk；符合公式；

 -- 將name修改為char(10) 定長 character set utf8 not null
 alter table user_info modify name char(10)  character set utf8 not null;
 -- 按照name查詢 varchar(10) not null utf8 一個字元佔3個位元組 10*3=30
 desc select * from user_info where name='a';

按照name查詢possible_keys為index_name，實際用到的索引key為index_name，key_len為30；

因為將name修改為char(10) 定長 character set utf8 not null，10*3=30；符合公式

九、ref：顯示該表的索引欄位關聯了哪張表的哪個欄位

desc select * from user,role where user.role_id=role.id;

通過執行計劃可知，role表執行計劃ref為study.user.role_id；說明role.id關聯user.role_id；

十、rows：根據表統計資訊及選用情況，大致估算出找到所需的記錄或所需讀取的行數，數值越小越好

十一、filtered：返回結果的行數佔讀取行數的百分比，值越大越好

-- 檢視teacher資料
select * from teacher;
-- 檢視teacher_card資料
select * from teacher_card;
-- 查詢語句
select * from teacher t  join  teacher_card tc on t.tc_id=tc.id  where t.name='a';
-- 執行計劃
desc select * from teacher t  join  teacher_card tc on t.tc_id=tc.id  where t.name='a';

根據上面的知識；可知id相同，由上至下依次執行，分析結果可知：

再查詢tc即teacher_card表使用主鍵和之前的t.tc_id關聯；掃描索引（type:ref），返回1條記錄，最終返回1條記錄，（filtered:100 1/1）。

十二、extra：包含不適合在其他列中顯示但十分重要的額外資訊。常見的值如下

use filesort：MySQL會對資料使用非索引列進行排序，而不是按照索引順序進行讀取；若出現改值，應優化索引

-- 檢視user索引
show index from user;
-- 查詢name並排序
desc select name from user order by name;
-- 為name列設定索引，優化
alter table user add index index_name(name);
-- 查詢name並排序
desc select name from user order by name;

use temporary：使用臨時表儲存中間結果，比如，MySQL在對查詢結果排序時使用臨時表，常見於order by和group by；若出現改值，應優化索引

use index：表示select操作使用了索引覆蓋，避免回表存取資料行，效率不錯

use where：where子句用於限制哪一行

-- 建立student表
create  table student(
    id int,
    first_name varchar(10),
    last_name varchar(10),
    primary key(id),
    key index_first(first_name)
)engine=innodb default charset=utf8;
-- 插入資料
insert into student values (1,'a','b');
-- 按照first_name查詢
desc select first_name,last_name from student where first_name='a';
-- 設定first_name,last_name複合索引
alter table student drop index index_first;
alter table student add index index_name(first_name,last_name);
-- 按照first_name查詢
desc select first_name,last_name from student where first_name='a';

分析結果：

當設定first_name為普通索引(單列索引)，按照first_name查詢；type:ref、possible_keys:indes_first、key:indes_first、extra:null，用到索引；

當設定first_name，last_name為複合索引(聯合索引)，按照first_name查詢；type:ref、possible_keys:indes_name、key:indes_name、extra:Using index；type:ref用到索引，因為是複合索引不需要回表掃描，extra:Using index索引覆蓋；注意此時key_len為33=10*3(utf8)+2(變長)+1(null標誌位)，用到了複合索引的一部分即first_name

當設定first_name，last_name為複合索引(聯合索引)，按照last_name查詢；type:index、possible_keys:null、key:indes_name、extra:Using where，Using index；type:index而不是ref，掃描索引樹，複合索引的最左原則；此時key_len為66=10*3(utf8)+2(變長)+1(null)+10*3(utf8)+2(變長)+1(null標誌位)；Using where應where子句進行限制