大家好，我是anyux。本文介紹MySQL執(zhí)行計劃分析。

作用

通過explain或desc命令將優(yōu)化器選擇后的執(zhí)行計劃截取出來，便于管理和判斷語句的執(zhí)行效率

獲取執(zhí)行計劃

desc SQL語句

explain SQL語句

獲取執(zhí)行計劃后選擇的方法

desc select * from `test`.`t100w` where k2='rsEF';

使用explain獲取執(zhí)行計劃

explain select * from `test`.`t100w` where k2='rsEF';

獲取執(zhí)行計劃時，SQL語句并沒有真正運行，對性能沒有影響。desc 和 explain對于獲取執(zhí)行計劃的結(jié)果相同的

獲取內(nèi)容如下所示：

分析執(zhí)行計劃

首先要查看table對應(yīng)的表，在真實環(huán)境中，可能存在多個聯(lián)合查詢，分析時需要明確是哪張表查詢出現(xiàn)性能瓶頸

然后是查看type對應(yīng)的值，第三個是possible-keys,第四個key,第五個key_len,第六個Extra

type指的是查詢的類型，分為全表掃描和索引掃描。全表掃描是低效的。索引掃描又分為幾個級別，包含輔助索引掃描和聚集索引掃描，各個級別不一樣，性能也不一樣

全表掃描對應(yīng)的執(zhí)行計劃是：ALL。全表掃描只有一種

索引掃描對應(yīng)的執(zhí)行計劃分別為：index,range,ref,eq_ref,const(system),NULL

索引掃描按上面的排序，從左到右性能依次變好

index：全索引掃描

演示index索引掃描

use world;desc city;desc select id from city;

下面圖中type值為index，意味著select id from city; 這條語句執(zhí)行計劃是index索引掃描

range:索引范圍掃描

range索引范圍掃描包含的符號有：>(大于)，=(大于等于),<=(小于等于),between(關(guān)鍵字),and(關(guān)鍵字),or(關(guān)鍵字),in(關(guān)鍵字)，like(關(guān)鍵字)

只要在SQL語句中出現(xiàn)以上關(guān)鍵字或符號的，就代表著會使用range索引范圍掃描

演示1:range索引掃描

range表示范圍掃描

desc select * from city where id>2000;desc select * from city where countrycode like 'CH%';

下面圖中type值為range，意味著select * from city where id>2000; 這條語句執(zhí)行計劃是range:索引范圍掃描

演示2:range索引掃描

desc select * from city where countrycode='CHN' or countrycode='USA'\Gdesc select * from city where countrycode in ('CHN','USA')\G

下面圖中type值為range，意味著select * from city where countrycode='CHN' or countrycode='USA'; 這條語句執(zhí)行計劃是range:索引范圍掃描

注意：在同一數(shù)量級下，例如在千萬條記錄中獲取10條數(shù)據(jù)，演示1的SQL語句性能優(yōu)于演示2的SQL語句，原因是MySQL5.7默認(rèn)使用B*Tree，在枝結(jié)點上存在雙向指針，不需要再向下一結(jié)點查詢，可以做到很快的響應(yīng)處理，對于演示1中大于2000，能夠快速響應(yīng)，而像like 'CH%',字符存儲也是連續(xù)的，也能夠快速響應(yīng)。而對于演示2的SQL語句只能使用普通BTree的查找算法，對于每個值都需要重新遍歷葉子結(jié)點，所以性能不是特別好。

像演示2這種情況一般需要改寫，改寫為 union all語句

desc select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

改寫后，type值改為了ref,明顯ref比較range范圍索引性能更高

ref：輔助索引等值查詢

ref代表著索引變化為等值的，相比range而言，范圍縮小了，查詢效率也更高了

演示ref索引掃描

desc select * from city where countrycode='CHN';

下面圖中type值為ref，意味著select * from city where countrycode='CHN';這條語句執(zhí)行計劃是ref索引掃描

eq_ref：子表使用主鍵列或唯一列作為連接條件

在多表連接時，子表使用主鍵列或唯一列作為連接條件。在使用join連接多表時，說過驅(qū)動表和子表。驅(qū)動表就是from后面緊跟著的表，一般使用記錄行少的表作為驅(qū)動表。右邊的表都屬于子表。

A join B on A.id=B.aid

A是驅(qū)動表，B是子表

當(dāng)B.aid是主鍵或唯一列的時候，使用的是eq_ref方式查詢。原因是驅(qū)動表是不使用索引的，而是使用全表掃描的方式，從第二張表是可以使用索引的。一般地開發(fā)人員會在設(shè)計數(shù)據(jù)庫時，會有預(yù)想到數(shù)據(jù)量增加及多表聯(lián)查的情況

演示:eq_ref索引掃描

desc select CITY.name ,COU.name,CITY.population from city as CITY join country as COU on CITY.countrycode=COU.code where CITY.population<100;

下面圖中type值為eq_ref，意味著select CITY.name ,COU.name,CITY.population from city as CITY join country as COU on CITY.countrycode=COU.code where CITY.population<100;這條語句執(zhí)行計劃是eq_ref索引掃描

const(system):主鍵或唯一鍵的等值查詢

使用此種索引返回的記錄均為1，查詢效果相同。但是性能存在一定差距，使用主鍵等值查詢會好一些。唯一索引是輔助索引，還是要回到原表查詢id,效果依然比輔助索引查詢來得要好

演示1:const(system)索引掃描

desc select * from city where id=100;

下面圖中type值為const，意味著select * from city where id=100;這條語句執(zhí)行計劃是const索引掃描

演示2:const(system)索引掃描

為構(gòu)造tmp_db，需要修改tmp_student表結(jié)構(gòu)

create database tmp_db charset utf8mb4;

use tmp_db;

create table tmp_student(id int not null primary key auto_increment,name varchar(20) not null,intime datetime not null,tel varchar(20) not null unique);

insert into tmp_student(name,intime,tel) values('zs',now(),'110'),('ls',now(),'120'),('ww',now(),'130');

desc select * from tmp_student where tel='120';

下面圖中type值為const，意味著select * from tmp_student where tel='120';這條語句執(zhí)行計劃是const索引掃描

NULL:不工作時，時間最短

desc select * from tmp_student where tel='1';

下面圖中type值為NULL，意味著select * from tmp_student where tel='1';這條語句執(zhí)行計劃是NULL,即不工作

type值為NULL代表的是不需要回數(shù)據(jù)行查詢記錄，原因是表中不存在要查詢的記錄

提示：

對于輔助索引來說!=(不等于),<>(不等于)，not in(),使用的是全表掃描,不走索引

對于主鍵索引來說!=(不等于),<>(不等于)，not in(),使用的是range索引范圍掃描

對于like ，如果%(百分號)在前面，使用的是全表掃描，不走索引

如果like給定的條件太少導(dǎo)致可選范圍過大，使用全表掃描，不走索引

總結(jié)

以上是生活随笔為你收集整理的mysql 分析执行计划的效率_MySQL执行计划分析的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。