mysql 分析执行计划的效率_MySQL执行计划分析

大家好,我是anyux。本文介绍MySQL执行计划分析。

8a18de9478073000dae4b04541b4a7d9.png

作用

通过explain或desc命令将优化器选择后的执行计划截取出来,便于管理和判断语句的执行效率

获取执行计划

desc SQL语句

explain SQL语句

获取执行计划后选择的方法

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

使用explain获取执行计划

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

获取执行计划时,SQL语句并没有真正运行,对性能没有影响。desc 和 explain对于获取执行计划的结果相同的

获取内容如下所示:

d8e2ade00cb8f5e9e28d8c1e604b0471.png

分析执行计划

首先要查看table对应的表,在真实环境中,可能存在多个联合查询,分析时需要明确是哪张表查询出现性能瓶颈

然后是查看type对应的值,第三个是possible-keys,第四个key,第五个key_len,第六个Extra

5bb37de7b1dd0947c41e9a8a9c99807c.png

type指的是查询的类型,分为全表扫描和索引扫描。全表扫描是低效的。索引扫描又分为几个级别,包含辅助索引扫描和聚集索引扫描,各个级别不一样,性能也不一样

c4c7298cb3c81eb932a281ed6c6e2f8b.png

全表扫描对应的执行计划是:ALL。全表扫描只有一种

索引扫描对应的执行计划分别为: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; 这条语句执行计划是index索引扫描

6ac1cb76065eb452ebb456daa848f08a.png

range:索引范围扫描

range索引范围扫描包含的符号有:>(大于),=(大于等于),<=(小于等于),between(关键字),and(关键字),or(关键字),in(关键字),like(关键字)

只要在SQL语句中出现以上关键字或符号的,就代表着会使用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; 这条语句执行计划是range:索引范围扫描

ac4e86d36c2af7b9765ad675e3035878.png

演示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'; 这条语句执行计划是range:索引范围扫描

e7ca4c3a66d222911fa7f65167918a9a.png

注意:在同一数量级下,例如在千万条记录中获取10条数据,演示1的SQL语句性能优于演示2的SQL语句,原因是MySQL5.7默认使用B*Tree,在枝结点上存在双向指针,不需要再向下一结点查询,可以做到很快的响应处理,对于演示1中大于2000,能够快速响应,而像like 'CH%',字符存储也是连续的,也能够快速响应。而对于演示2的SQL语句只能使用普通BTree的查找算法,对于每个值都需要重新遍历叶子结点,所以性能不是特别好。

像演示2这种情况一般需要改写,改写为 union all语句

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

改写后,type值改为了ref,明显ref比较range范围索引性能更高

eebfe11f79a54dc1185ad18737cf7778.png

ref:辅助索引等值查询

ref代表着索引变化为等值的,相比range而言,范围缩小了,查询效率也更高了

演示ref索引扫描

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

下面图中type值为ref,意味着select * from city where countrycode='CHN';这条语句执行计划是ref索引扫描

35330c24409b8fdab9645d8916a20044.png

eq_ref:子表使用主键列或唯一列作为连接条件

在多表连接时,子表使用主键列或唯一列作为连接条件。在使用join连接多表时,说过驱动表和子表。驱动表就是from后面紧跟着的表,一般使用记录行少的表作为驱动表。右边的表都属于子表。

A join B on A.id=B.aid

A是驱动表,B是子表

当B.aid是主键或唯一列的时候,使用的是eq_ref方式查询。原因是驱动表是不使用索引的,而是使用全表扫描的方式,从第二张表是可以使用索引的。一般地开发人员会在设计数据库时,会有预想到数据量增加及多表联查的情况

da10a20f042f95d880cc336a2917cded.png

演示: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;这条语句执行计划是eq_ref索引扫描

34b5f422a8c017e83434be928a26ae98.png

const(system):主键或唯一键的等值查询

使用此种索引返回的记录均为1,查询效果相同。但是性能存在一定差距,使用主键等值查询会好一些。唯一索引是辅助索引,还是要回到原表查询id,效果依然比辅助索引查询来得要好

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

desc select * from city where id=100;

下面图中type值为const,意味着select * from city where id=100;这条语句执行计划是const索引扫描

cdffd3fdfcf69f635dad918d1ac30c30.png

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

为构造tmp_db,需要修改tmp_student表结构

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';这条语句执行计划是const索引扫描

a111075e4d1e2931ce9d86f647c60be9.png

NULL:不工作时,时间最短

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

下面图中type值为NULL,意味着select * from tmp_student where tel='1';这条语句执行计划是NULL,即不工作

61602cf033f1cafb8a99fe6d30f421c1.png

type值为NULL代表的是不需要回数据行查询记录,原因是表中不存在要查询的记录

提示:

对于辅助索引来说!=(不等于),<>(不等于),not in(),使用的是全表扫描,不走索引

对于主键索引来说!=(不等于),<>(不等于),not in(),使用的是range索引范围扫描

对于like ,如果%(百分号)在前面,使用的是全表扫描,不走索引

如果like给定的条件太少导致可选范围过大,使用全表扫描,不走索引

  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值