前言

項(xiàng)目開發(fā)中，性能是我們比較關(guān)注的問題，特別是數(shù)據(jù)庫(kù)的性能；作為一個(gè)開發(fā)，經(jīng)常和SQL語句打交道，想要寫出合格的SQL語句，我們需要了解SQL語句在數(shù)據(jù)庫(kù)中是如何掃描表、如何使用索引的；

MySQL提供explain/desc命令輸出執(zhí)行計(jì)劃，我們通過執(zhí)行計(jì)劃優(yōu)化SQL語句。

下面我們以MySQL5.7為例了解一下執(zhí)行計(jì)劃：

注：文中涉及到的表結(jié)構(gòu)、sql語句只是為了理解explain/desc執(zhí)行計(jì)劃，有不合理之處勿噴

explain/desc 用法

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

準(zhǔn)備數(shù)據(jù)表

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

查詢，執(zhí)行計(jì)劃

 explain select * from user;

執(zhí)行計(jì)劃輸出有id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、extra，這些內(nèi)容有什么意義，下面簡(jiǎn)單介紹一下

explain/desc 輸出詳解

一、id ，select 查詢序列號(hào)

1 id相同，從上往下一次執(zhí)行；

-- 左關(guān)聯(lián)
explain select * from user a left join user b on a.id=b.id;
-- 右關(guān)聯(lián)
explain select * from user a right join user b on a.id=b.id;

通過left join 和 right join 驗(yàn)證；id一樣（注意執(zhí)行計(jì)劃的table列），left join 先掃描a表，再掃描b表；rightjoin 先掃描b表，再掃描a表

2 id不同，id越大優(yōu)先級(jí)越高，越先被執(zhí)行

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

我們編寫查詢角色為開發(fā)的用戶；可以知道先查詢角色name為開發(fā)角色id，查詢序列號(hào)為2；再根據(jù)角色id查詢用戶，查詢序列號(hào)為1；

二、select_type，查詢語句類型

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

explain select * from user;

(2)PRIMARY(查詢中若包含任何復(fù)雜的子部分,最外層的select被標(biāo)記為PRIMARY)

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

(3)UNION(UNION中的第二個(gè)或后面的SELECT語句)

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

(4)DEPENDENT UNION(UNION中的第二個(gè)或后面的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的結(jié)果)

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

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

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

(7)DEPENDENT SUBQUERY(子查詢中的第一個(gè)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執(zhí)行過程中，第一次需要子查詢結(jié)果時(shí)執(zhí)行子查詢并將子查詢的結(jié)果保存為臨時(shí)表 ，后續(xù)對(duì)子查詢結(jié)果集的訪問將直接通過臨時(shí)表獲得。

(10)UNCACHEABLE SUBQUERY(一個(gè)子查詢的結(jié)果不能被緩存，必須重新評(píng)估外鏈接的第一行)

(11)UNCACHEABLE UNION(UNION查詢的結(jié)果不能被緩存)

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

table分別user、role表

四、partitions查詢涉及到的分區(qū)

創(chuàng)建分區(qū)表，

-- 創(chuàng)建分區(qū)表，
-- 按照id分區(qū)，id<100 p0分區(qū)，其他p1分區(qū)
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分區(qū)）的記錄，查看執(zhí)行計(jì)劃，partitions是p1，符合我們的分區(qū)規(guī)則

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

通過type字段, 我們判斷此次查詢是全表掃描還是索引掃描等，下面簡(jiǎn)單介紹一下常用的type；

（1）system: 表中只有一條數(shù)據(jù)，相當(dāng)于系統(tǒng)表； 這個(gè)類型是特殊的const類型；

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

主鍵

-- 創(chuàng)建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查詢的執(zhí)行計(jì)劃；發(fā)現(xiàn)按主鍵id查詢，執(zhí)行計(jì)劃type為const

將主鍵設(shè)置為id和role_id

-- 刪除主鍵
alter table user drop primary key;
-- 設(shè)置主鍵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;

發(fā)現(xiàn)只有按照全部主鍵查詢，執(zhí)行計(jì)劃type為const

唯一索引

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

發(fā)現(xiàn)按role_id唯一索引查詢；執(zhí)行計(jì)劃type為const

普通索引

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

發(fā)現(xiàn)按role_id普通索引查詢；執(zhí)行計(jì)劃type為ref

const用于主鍵或唯一索引查詢；將PRIMARY KEY或UNIQUE索引的所有部分與常量值進(jìn)行比較時(shí)使用；與索引類型有關(guān)。

（3）eq_ref: 除了system和const類型之外,效率最高的連接類型；唯一索引掃描，對(duì)于每個(gè)索引鍵，表中只有一條記錄與之對(duì)應(yīng)；常用于主鍵或唯一索引掃描

準(zhǔn)備數(shù)據(jù)

-- 創(chuàng)建teacher表
create table teacher( id int primary key, name varchar(20), tc_id int );
-- 插入3條數(shù)據(jù)
insert into teacher values (1,'a',1),(2,'b',2),(3,'c',3);
-- 創(chuàng)建teacher_card表
create table teacher_card( id int primary key, remark varchar(20) );
-- 插入2條數(shù)據(jù)
insert into teacher_card values (1,'aa'),(2,'bb');
-- 關(guān)聯(lián)查詢，執(zhí)行計(jì)劃
desc select * from teacher t  join  teacher_card tc on t.tc_id=tc.id  where t.name='a';

執(zhí)行計(jì)劃

根據(jù)上面的知識(shí)；可知id相同，由上至下依次執(zhí)行，分析結(jié)果可知：

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

再查詢tc即teacher_card表使用主鍵和之前的t.tc_id關(guān)聯(lián)；由于是關(guān)聯(lián)查詢，并且是通過唯一索引（主鍵）進(jìn)行查詢，僅能返回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);
 -- 關(guān)聯(lián)查詢，執(zhí)行計(jì)劃
 desc select * from teacher t  join  teacher_card tc on t.tc_id=tc.id  where t.name='a';

分析結(jié)果，將teacher_card的id設(shè)置為唯一索引，type為eq_ref；滿足僅能返回1或0條記錄。

 -- 刪除teacher_card唯一索引
 alter table teacher_card drop index ui_id;
 -- 設(shè)置teacher_card.id為普通索引
 alter table teacher_card add index index_id(id);
 -- 關(guān)聯(lián)查詢，執(zhí)行計(jì)劃
 desc select * from teacher t  join  teacher_card tc on t.tc_id=tc.id  where t.name='a';

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

equ_ref用于唯一索引查詢，對(duì)每個(gè)索引鍵，表中只有一條或零條記錄與之匹配。

（4）ref：此類型通常出現(xiàn)在多表的 join 查詢, 針對(duì)于非唯一或非主鍵索引, 或者是使用了最左前綴規(guī)則索引的查詢（換句話說，連接不能基于鍵值選擇單行,可能是多行）。

 -- teacher.tc_id無索引，執(zhí)行計(jì)劃
 desc select * from teacher t  join  teacher_card tc on t.tc_id=tc.id  where tc.remark='aa';
 -- 設(shè)置teacher.tc_id為普通索引
 alter table teacher add index index_tcid(tc_id);
 -- teacher.tc_id有索引，執(zhí)行計(jì)劃
 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關(guān)聯(lián)；由于是關(guān)聯(lián)查詢，不是索引,全表掃描，所以type為ALL。

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

（5）range: 表示使用索引范圍查詢, 通過索引字段范圍獲取表中部分?jǐn)?shù)據(jù)記錄. 這個(gè)類型通常出現(xiàn)在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。

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

（6）index: 掃描索引樹

如果索引是復(fù)合索引，并且復(fù)合索引列滿足select所需的所有數(shù)據(jù)，則僅掃描索引樹。在這種情況下，Extra為Using index。僅索引掃描通常比ALL索引的大小通常小于表數(shù)據(jù)更快。

索引列不滿足select所需的所有數(shù)據(jù)，此時(shí)需要回表掃描；按索引順序查找數(shù)據(jù)行。Uses index沒有出現(xiàn)在Extra列中。

 -- 查看teacher表索引
 show index from teacher;
 -- 查詢tc_id，執(zhí)行計(jì)劃
 desc select tc_id from teacher;
 -- 按tc_id索引分組，執(zhí)行計(jì)劃
 desc select name from teacher group by tc_id;

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

按tc_id分組，全表掃描，以按索引順序查找數(shù)據(jù)行。

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

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

由于name字段不存在索引，type:ALL全表掃描；可通過對(duì)name字段設(shè)置索引進(jìn)行優(yōu)化。

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

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

-- 創(chuàng)建course表
create table course(id int primary key,name varchar(20),t_id int,key index_name(name),key index_tid(t_id));
-- 插入數(shù)據(jù)
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';

查看執(zhí)行計(jì)劃

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

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

八、key_len：表示索引中使用的字節(jié)數(shù)（只計(jì)算利用索引作為index key的索引長(zhǎng)度，不包括用于group by/order by的索引長(zhǎng)度）

• 一般地，key_len 等于索引列類型字節(jié)長(zhǎng)度，例如int類型為4 bytes，bigint為8 bytes；
• 如果是字符串類型，還需要同時(shí)考慮字符集因素，例如utf8字符集1個(gè)字符占3個(gè)字節(jié)，gbk字符集1個(gè)字符占2個(gè)字節(jié)
• 若該列類型定義時(shí)允許NULL，其key_len還需要再加 1 bytes
• 若該列類型為變長(zhǎng)類型，例如 VARCHAR（TEXT\BLOB不允許整列創(chuàng)建索引，如果創(chuàng)建部分索引也被視為動(dòng)態(tài)列類型），其key_len還需要再加 2 bytes

字符集會(huì)影響索引長(zhǎng)度、數(shù)據(jù)的存儲(chǔ)空間，為列選擇合適的字符集；變長(zhǎng)字段需要額外的2個(gè)字節(jié)，固定長(zhǎng)度字段不需要額外的字節(jié)。而null都需要1個(gè)字節(jié)的額外空間，所以以前有個(gè)說法：索引字段最好不要為NULL，因?yàn)镹ULL讓統(tǒng)計(jì)更加復(fù)雜，并且需要額外一個(gè)字節(jié)的存儲(chǔ)空間。

-- key_len的長(zhǎng)度計(jì)算公式：

-- varchar(len)變長(zhǎng)字段且允許NULL      : len*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(變長(zhǎng)字段)
-- varchar(len)變長(zhǎng)字段且不允許NULL    : len*(Character Set：utf8=3,gbk=2,latin1=1)+2(變長(zhǎng)字段)

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

下面驗(yàn)證一下結(jié)論：

-- 創(chuàng)建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;
-- 插入數(shù)據(jù)
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，實(shí)際用到的索引key為primary，key_len為4；

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

分析結(jié)果：按照role_id比按照id（均為int類型）的key_len大5-4=1，因?yàn)閞ole_id可以為null，需要一個(gè)標(biāo)志位；

 -- 按照name查詢 varchar(10) not null utf8 一個(gè)字符占3個(gè)字節(jié) 10*3+2(變長(zhǎng))=32
 desc select * from user_info where name='a';
 -- 按照alias_name查詢 varchar(10) utf8 一個(gè)字符占3個(gè)字節(jié) 10*3+2(變長(zhǎng))+1(null標(biāo)志位)=33
 desc select * from user_info where alias_name='aa';

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

按照alias_name查詢possible_keys為index_alias，實(shí)際用到的索引key為index_alias，key_len為33=10*3+2(變長(zhǎng))+1(null標(biāo)志位)；

分析結(jié)果：name與remark均為變長(zhǎng)且字符集一致，remark可以為null，33-32=1多占一個(gè)標(biāo)志位；

 -- 按照name查詢 varchar(10) not null utf8 一個(gè)字符占3個(gè)字節(jié) 10*3+2(變長(zhǎng))=32
 desc select * from user_info where name='a';
 -- 按照remark查詢 varchar(10) not null gbk 一個(gè)字符占2個(gè)字節(jié) 10*2+2(變長(zhǎng))=22
 desc select * from user_info where remark='aaa';

按照name查詢possible_keys為index_name，實(shí)際用到的索引key為index_name，key_len為32=10*3(utf8一個(gè)字符3個(gè)字節(jié))+2(變長(zhǎng))；

按照remark查詢possible_keys為index_remark，實(shí)際用到的索引key為index_remark，key_len為22=10*2(gbk一個(gè)字符2個(gè)字節(jié))+2(變長(zhǎng))；

分析結(jié)果：name與remark均為變長(zhǎng)但字符集不一致，分別為utf8與gbk；符合公式；

 -- 將name修改為char(10) 定長(zhǎng) character set utf8 not null
 alter table user_info modify name char(10)  character set utf8 not null;
 -- 按照name查詢 varchar(10) not null utf8 一個(gè)字符占3個(gè)字節(jié) 10*3=30
 desc select * from user_info where name='a';

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

因?yàn)閷ame修改為char(10) 定長(zhǎng) character set utf8 not null，10*3=30；符合公式

九、ref：顯示該表的索引字段關(guān)聯(lián)了哪張表的哪個(gè)字段

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

通過執(zhí)行計(jì)劃可知，role表執(zhí)行計(jì)劃ref為study.user.role_id；說明role.id關(guān)聯(lián)user.role_id；

十、rows：根據(jù)表統(tǒng)計(jì)信息及選用情況，大致估算出找到所需的記錄或所需讀取的行數(shù)，數(shù)值越小越好

十一、filtered：返回結(jié)果的行數(shù)占讀取行數(shù)的百分比，值越大越好

-- 查看teacher數(shù)據(jù)
select * from teacher;
-- 查看teacher_card數(shù)據(jù)
select * from teacher_card;
-- 查詢語句
select * from teacher t  join  teacher_card tc on t.tc_id=tc.id  where t.name='a';
-- 執(zhí)行計(jì)劃
desc select * from teacher t  join  teacher_card tc on t.tc_id=tc.id  where t.name='a';

根據(jù)上面的知識(shí)；可知id相同，由上至下依次執(zhí)行，分析結(jié)果可知：

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

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

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

use filesort：MySQL會(huì)對(duì)數(shù)據(jù)使用非索引列進(jìn)行排序，而不是按照索引順序進(jìn)行讀?。蝗舫霈F(xiàn)改值，應(yīng)優(yōu)化索引

-- 查看user索引
show index from user;
-- 查詢name并排序
desc select name from user order by name;
-- 為name列設(shè)置索引，優(yōu)化
alter table user add index index_name(name);
-- 查詢name并排序
desc select name from user order by name;

use temporary：使用臨時(shí)表保存中間結(jié)果，比如，MySQL在對(duì)查詢結(jié)果排序時(shí)使用臨時(shí)表，常見于order by和group by；若出現(xiàn)改值，應(yīng)優(yōu)化索引

use index：表示select操作使用了索引覆蓋，避免回表訪問數(shù)據(jù)行，效率不錯(cuò)

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

-- 創(chuàng)建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;
-- 插入數(shù)據(jù)
insert into student values (1,'a','b');
-- 按照first_name查找
desc select first_name,last_name from student where first_name='a';
-- 設(shè)置first_name,last_name復(fù)合索引
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';

分析結(jié)果：

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

當(dāng)設(shè)置first_name，last_name為復(fù)合索引(聯(lián)合索引)，按照first_name查詢；type:ref、possible_keys:indes_name、key:indes_name、extra:Using index；type:ref用到索引，因?yàn)槭菑?fù)合索引不需要回表掃描，extra:Using index索引覆蓋；注意此時(shí)key_len為33=10*3(utf8)+2(變長(zhǎng))+1(null標(biāo)志位)，用到了復(fù)合索引的一部分即first_name

當(dāng)設(shè)置first_name，last_name為復(fù)合索引(聯(lián)合索引)，按照last_name查詢；type:index、possible_keys:null、key:indes_name、extra:Using where，Using index；type:index而不是ref，掃描索引樹，復(fù)合索引的最左原則；此時(shí)key_len為66=10*3(utf8)+2(變長(zhǎng))+1(null)+10*3(utf8)+2(變長(zhǎng))+1(null標(biāo)志位)；Using where應(yīng)where子句進(jìn)行限制

小結(jié)：

根據(jù)MySQL執(zhí)行計(jì)劃的輸出，分析索引使用情況、掃描的行數(shù)可以預(yù)估查詢效率；進(jìn)而可以重構(gòu)SQL語句、調(diào)整索引，提升查詢效率。

本文只是簡(jiǎn)單介紹一下MySQL執(zhí)行計(jì)劃，想全面深入了解MySQL,可優(yōu)先閱讀MySQL官方手冊(cè)。

總結(jié)

到此這篇關(guān)于MySQL執(zhí)行計(jì)劃的文章就介紹到這了,更多相關(guān)MySQL執(zhí)行計(jì)劃內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論