MySQL执行计划分析

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

作用

通过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对于获取执行计划的结果相同的

获取内容如下所示:

分析执行计划

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

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

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

全表扫描对应的执行计划是: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索引扫描

range:索引范围扫描

range索引范围扫描包含的符号有:>(大于),=(大于等于),

只要在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:索引范围扫描

演示2:range索引扫描

range表示范围扫描

desc select * from city where countrycode='CHN' or countrycode='USA'\G

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

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

注意:在同一数量级下,例如在千万条记录中获取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范围索引性能更高

ref:辅助索引等值查询

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

演示ref索引扫描

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

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

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

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

A join B on A.id=B.aid

A是驱动表,B是子表

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

演示: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

下面图中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

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

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

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

desc select * from city where id=100;

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

演示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索引扫描

NULL:不工作时,时间最短

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

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

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

提示

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

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

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

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

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20200303A02XLS00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

扫码关注云+社区

领取腾讯云代金券