jQuery火箭图标返回顶部代码

mysql explain详解

开场白

面试官：你做过sql优化嘛？
小张：做过，我精通Mysql查询优化
面试官：哦？可否具体点？
小张：我会用explain
面试官当场拍桌，好！你被录取了。

哈哈，当然上面是开玩笑的，但是掌握explain，真的是掌握了sql 优化的半壁江山。进入正题。

Explain简介

Explain关键字是Mysql中sql优化的常用「关键字」，通常都会使用Explain来「查看sql的执行计划，而不用执行sql」，从而快速的找出sql的问题所在。

image

下面我们正式结束Explain的使用，在介绍之前，我们先创建三个数据表「用户表user、角色表role、以及用户角色关系表role_user」，边实战边讲解

image

// 用户表
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `age` int(11)  NOT NULL DEFAULT 0,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `user` (`id`, `name`, `age`,`update_time`) VALUES (1,'张三',23,'2021-08-24 15:27:18'), (2,'李四',24,'2021-06-21 15:27:18'), (3,'王五',25,'2021-07-20 15:27:18');

DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `role` (`id`, `name`) VALUES (1,'产品经理'),(2,'技术经理'),(3,'项目总监');

DROP TABLE IF EXISTS `role_user`;
CREATE TABLE `role_user` (
  `id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_role_user_id` (`role_id`,`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `role_user` (`id`, `role_id`, `user_id`) VALUES (1,2,1),(2,1,2),(3,3,3);

我们首先执行一条sql：explain select * from user where id =2;，执行后可以看到执行的结果如下：

image

可以看到这里有12个字段那个且都有对应的值，这就是explain的执行计划，能看懂这个执行计划，你离精通sql优化就不远了，下面就来详细的介绍这12个字段分别表示什么意思。

image

id字段

id表示执行select查询语句的序号，它是sql执行的顺序的标识，sql按照id从大到小执行，id相同的为一组，从上到下执行。

什么意思呢？例如执行这条sql：explain select * from user where id in (select user_id from role_user);

image

显示出的两者的id都相同，便表示sql的执行从上往下执行，第一条记录对应的是user表，然后第二条记录对应的是role_user表，这种是id相同的情况。

若是id不同，例如执行下面的sql：explain select (select 1 from user limit 1) from role;

image

就会看到有两条记录，并且两条记录的id会不一样，id越大的就越先执行，可以看到id=2的执行的是user表，也就是子查询部分，最后执行最外层的部分。

「结论：」 这个就是id标识sql的执行顺序，一般在复杂查询中会有多条记录，简单查询只有一条记录，复杂查询中id相同的为一组，执行的顺序是从上往下，而id越大的越先执行；Mysql 8中会存在对子查询进行优化，所以有时候即使是复杂查询，也只有一条记录。

select_type字段

select_type表示查询的类型，也就是对应的是简单查询还是复杂查询，若是复杂查询又包含：「简单的子查询、from子句的子查询、union查询」。下面就分别来看看select_type中的所有查询类型。

image

simple

simple表示简单查询，不含有任何的复杂查询。
如：explain select * from user ;

image

PRIMARY

复杂查询中「最外层的select语句的查询类型就是PRIMARY」。
例如：explain select * from role where id = (select id from role_user where role_id = (select id from user where id = 2));
最外层的select，也就是 select * from role where id =？会被标记为PRIMARY类型。

image

SUBQUERY

在「select或者where中包含的子查询」会被表示为SUBQUERY类型，例如上一句执行的sql中就有两次的子查询为SUBQUERY。

DERIVED

「DERIVED表示的是派生表或者衍生表的意思，在from包含的子查询中会被表示为DERIVED类型」，Mysql会递归执行这些子查询，并且把结果放在临时表中。
执行sql：explain select * from (select name from user union select name from role) a where a.name = '张三';

image

在Mysql 5.7以上的版本中对其做了优化，新增了derived_merge(派生合并)，可以加快查询效率。

UNION

在出现「UNION查询语句中，第二个select的查询语句就会被表示为UNION」。
执行sql: explain select * from (select name from user union select name from role) a where a.name = '张三';

image

UNION RESULT

「UNION查询语句的结果被标记为UNION RESULT」。
如上面执行的sql：explain select * from (select name from user union select name from role) a where a.name = '张三';

image

第四行记录中从table字段中可以看出，第四行的记录来源于第二行和第三行<union2,3>，因此一个UNION查询语句的结果就会被标记为UNION RESULT。

其它

上面的七个select_type都是比较常见的，还有一些不常见的，作为了解就好：
DEPENDENT UNION：也表示UNION查询语句中第二个或者后面的语句，但是取决于外面的查询。
DEPENDENT SUBQUERY：子查询中的第一个select语句，也是依赖于外部的查询。
UNCACHEABLE SUBQUERY：子查询的结果不能被缓存，必须重新评估外连接的第一行。

table字段

这个很容易看出「table字段表示的是查询的是哪个表」，一个是已经存在的表，比如上面的user、role都是我们自己创建的表，也可以表示衍生表。

比如：UNION RESULT的table字段表示为<union2,3>，也就是查询的是第二行和第三行的结果记录。

partitions

partitions表示所匹配的分区。

type字段

「type字段表示的sql关联的类型或者说是访问的类型」。从这个字段中我们可以确定这条sql查找数据库表的时候，查找记录的大概范围是怎么样的，直接就能体现sql的效率问题。

type字段的类型也是有比较多，主要常见掌握的有以下几个：system、const 、eq_ref 、ref 、range 、index 、ALL。它的性能体现是从高到低。
system > const > eq_ref > ref > range > index > ALL，下面就来详细的说一说这属性。

image

system

system是const的特例，「表示表中只有一行记录」，这个几乎不会出现，也作为了解。

const

const表示通过索引一次就查找到了数据，一般const出现在「唯一索引或者主键索引中使用等值查询」，因为表中只有一条数据匹配，所以查找的速度很快。
如：explain select * from user where id =2;

image

eq_ref

「eq_ref表示使用唯一索引或者主键索引扫描作为表链接匹配条件，对于每一个索引键，表中只有一条记录与之匹配」。
例如：explain select * from user left join role_user on user.id = role_user.user_id left join role on role_user.role_id=role.id;

image

ref

ref性能比eq_ref差，「也表示表的链接匹配条件，也就是使用哪些表字段作为查询索引列上的值」。
ref与eq_ref的区别就是：
eq_ref使用的是唯一索引或者主键索引。
ref扫描后的结果可能会找到多条符合条件的行数据，本质上是一种索引访问，返回匹配的行。
例如：explain select * from user where name = '张三';

image

这里type为ALL，说明name这个字段没有走索引，所以我们需要给name字段添加普通索引

image

在次执行，可以发现name走了普通索引。

image

range

「range使用索引来检索给定范围的行数据，一般是在where后面使用between、<>、in等查询语句就会出现range」
例如：explain select * from user where id > 2;

image

index

「index表示会遍历索引树」，index回避ALL速度快一些，但是出现index说明需要检查自己的索引是否使用正确。
例如：explain select id from user;

image

ALL

「ALL与index的区别就是ALL是从硬盘中读取，而index是从索引文件中读取」，ALL全表扫描意味着Mysql会从表的头到尾进行扫描，这时候表示通常需要增加索引来进行优化了，或者说是查询中并没有使用索引作为条件进行查询
例如：explain select * from user ;

image

possible_keys字段

possible_keys表示这一列查询语句可能使用到的索引，仅仅只是可能，列出来的索引并不一定真正的使用到。

当没有使用索引为NULL时，说明需要增加索引来优化查询了，若是表的数据比较少的话，数据库觉得全表扫描更快，也可能为NULL。

key字段

key字段与possible_keys的区别就是，表示的真正使用到的索引，即possible_keys中包含key的值。

若是想Mysql使用或者忽视possible_keys中的索引，可以使用FORCE INDEX、USE INDEX或者IGNORE INDEX。

key_len字段

表示sql查询语句中索引使用到的字节数，这个字节数并不是实际的长度，而是通过计算查询中使用到的索引中的长度得出来的，显示的是索引字段最大的可能长度。

一般来说在不损失精度的前提下，key_len是越小越好，比如上面的测试表的id为int类型，int类型由4个字节组成
例如：explain select * from user where id =2 ;

image

key_len对于不同的类型有自己的计算规则，具体的计算规则如下所示：

数据类型 所占字节数
字符串 char(n)：n字节长度
varchar(n)：2字节存储字符串长度，如果是utf-8，则长度 3n + 2
数值类型 tinyint：1字节
smallint：2字节
int：4字节
bigint：8字节
时间类型 date：3字节
timestamp：4字节
datetime：8字节

若是索引为字符串类型的时候，实际存储的字符串非常长，已经超出了字符串类型的存储最大长度（768字节），mysql，就会使用类似左前缀索引来处理。

ref字段

ref表示列与索引的比较，表连接的匹配条件，表示哪些列或者常量被用于查询索引列上的值。

rows字段

rows表示估算的要扫描的行数，一般Mysql会根据统计表信息和索引的选用情况，估算出 查找记录所要扫描的行数，注意这个并不是实际结果集的行数。

filtered字段

filtered表示的是查询表行所占表的百分比。

Extra字段

该字段显示的是sql查询的额外信息，主要有以下几种情况：
Using index、Using where、Using temporary、Using temporary、Using join buffer、Impossible where、Select tables optimized away

image

Using index

表示查询的列被索引覆盖，这个是查询性能比较高的体现，即所要查询的信息搜在索引里面可以得到，不用回表，索引被正确的使用
例如：explain select id from user where id =2;

image

假如同时出现了using where，表示索引用于执行索引键值的查找；若是没有出现using where，则表示索引用于读取数据，而非执行查询的动作。

Using where

该属性与Using index相反，查询的列并没有被索引覆盖，where条件后面使用的是非索引的前导列，它仅仅是使用了where条件而已。
例如：explain select user.* from user,role,role_user where user.id = role_user.user_id and role.id=role_user.role_id;

image

Using temporary

「Using temporary表示使用了临时表存储中间的结果，一般在对结果排序的时候会使用临时表」。
例如：排序order by 和分组查询group by。
例：explain select * from (select name from user union select name from role) a where a.name = '张三' ;

image

Using filesort

Using filesort表示文件排序，说明Mysql对数据使用了外部的索引进行排序，并没有使用表中的索引进行排序。
例如：explain select * from user order by name;

image

Using join buffer

Using join buffer表示使用连接缓存。
例如：explain select user.* from user,role,role_user where user.id = role_user.user_id and role.id=role_user.role_id ;

image

它强调在获取连接条件时，并没有使用索引，而是使用连接缓冲区来存储中间结果，若是出现该值，一般说明需要添加索引来进行优化了。

Impossible where

Impossible where会出现在where后的条件一直为false的情况下，这种可以忽视，比较少出现
例如：explain select * from user where name = 'hah' and name = 'sfsd' ;

image

Select tables optimized away

表示select语句没有遍历表或者索引就返回数据了。
例如：explain select min(id) from user ;

image

在Extra字段中还有其它的属性，但是几乎都没见过的，不出现，所以哪些就讲解，有兴趣的可以自己去了解，这里只列出这些常见的。

注意

  1. 使用联合索引要遵循「最左前缀法则」
  2. 在使用索引进行查询的时候，不要做任何的函数操作，不然会导致索引失效
  3. 在数据库的查询中不要使用（！=或者<>）等判条件和is null,is not null、like关键词中以%开头来判断，不然也会使索引失效
posted @ 2021-08-24 16:46  天下没有收费的bug  阅读(5528)  评论(0)  编辑  收藏  举报