|
|
51CTO旗下网站
|
|
移动端

explain都不会用,你还好意思说精通MySQL查询优化?

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

作者:来源:数据库开发|2020-08-26 14:40

Explain简介

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

在讲解Explain之前首先创建需要的「用户表user、角色表role、以及用户角色关系表role_user」作为测试用的表:

  1. // 用户表  
  2. DROP TABLE IF EXISTS `user`;  
  3. CREATE TABLE `user` (  
  4.   `id` int(11) NOT NULL,  
  5.   `name` varchar(25) DEFAULT NULL,  
  6.   `age` int(11)  NOT NULL DEFAULT 0,  
  7.   `update_time` datetime DEFAULT NULL,  
  8.   PRIMARY KEY (`id`)  
  9. ENGINE=InnoDB DEFAULT CHARSET=utf8 
  10. INSERT INTO `user` (`id`, `name`, `age`,`update_time`) VALUES (1,'张三',23,'2020-12-22 15:27:18'), (2,'李四',24,'2020-06-21 15:27:18'), (3,'王五',25,'2020-07-20 15:27:18'); 
  11.  DROP TABLE IF EXISTS `role`;  
  12. CREATE TABLE `role` (  
  13.   `id` int(11) NOT NULL AUTO_INCREMENT,  
  14.   `name` varchar(10) DEFAULT NULL,  
  15.   PRIMARY KEY (`id`),  
  16.   KEY `index_name` (`name`)  
  17. ENGINE=InnoDB DEFAULT CHARSET=utf8 
  18. INSERT INTO `role` (`id`, `name`) VALUES (1,'产品经理'),(2,'技术经理'),(3,'项目总监');  
  19. DROP TABLE IF EXISTS `role_user`;  
  20. CREATE TABLE `role_user` (  
  21.   `id` int(11) NOT NULL,  
  22.   `role_id` int(11) NOT NULL,  
  23.   `user_id` int(11) NOT NULL,  
  24.   PRIMARY KEY (`id`),  
  25.   KEY `index_role_user_id` (`role_id`,`user_id`)  
  26. ENGINE=InnoDB DEFAULT CHARSET=utf8 
  27. 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;,执行后可以看到执行的结果如下:

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

id字段

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

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

  1. +----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------------------------------------------------------------+ 
  2. | id | select_type | table     | partitions | type  | possible_keys | key                | key_len | ref  | rows | filtered | Extra                                                                             | 
  3. +----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------------------------------------------------------------+ 
  4. |  1 | SIMPLE      | user      | NULL       | ALL   | PRIMARY       | NULL               | NULL    | NULL |    3 |   100.00 | NULL                                                                              | 
  5. |  1 | SIMPLE      | role_user | NULL       | index | NULL          | index_role_user_id | 8       | NULL |    3 |    33.33 | Using where; Using index; FirstMatch(user); Using join buffer (Block Nested Loop) | 
  6. +----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------------------------------------------------------------+ 

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

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

  1. +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+  
  2. | id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra       |  
  3. +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+  
  4. |  1 | PRIMARY     | role  | NULL       | index | NULL          | index_name | 33      | NULL |    3 |   100.00 | Using index |  
  5. |  2 | SUBQUERY    | user  | NULL       | index | NULL          | PRIMARY    | 4       | NULL |    3 |   100.00 | Using index |  
  6. +----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+ 

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

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

select_type字段

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

simple

simple表示简单查询,不含有任何的复杂查询。

PRIMARY

复杂查询中「最外层的select语句的查询类型就是PRIMARY」,例如执行下面的sql: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类型。

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 = '张三';

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

UNION

在出现「UNION查询语句中,第二个select的查询语句就会被表示为UNION」:

UNION RESULT

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

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

其它

上面的七个select_type都是比较常见的,还有一些不常见的,作为了解就好:

  1.  DEPENDENT UNION:也表示UNION查询语句中第二个或者后面的语句,但是取决于外面的查询。
  2.  DEPENDENT SUBQUERY:子查询中的第一个select语句,也是依赖于外部的查询。
  3.  UNCACHEABLE SUBQUERY:子查询的结果不能被缓存,必须重新评估外连接的第一行。

table字段

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

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

type字段

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

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

system

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

const

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

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;

ref

ref性能比eq_ref差,也表示表的链接匹配条件,也就是使用哪些表字段作为查询索引列上的值,ref与eq_ref的区别就是eq_ref使用的是唯一索引或者主键索引。

ref扫描后的结果可能会找到多条符合条件的行数据,本质上是一种索引访问,返回匹配的行。例如:explain select * from user where name = '张三';

range

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

index

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

ALL

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

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;

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会根据统计表信息和索引的选用情况,估算出查找记录所要扫描的行数,注意这个并不是实际结果集的行数。

partitions、filtered字段

partitions表示所匹配的分区;filtered表示的是查询表行所占表的百分比。

Extra字段

该字段显示的是sql查询的额外信息,主要有以下几种情况:

Using index

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

假如同时出现了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;

Using temporary

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

Using filesort

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

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;

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

Impossible where

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

Select tables optimized away

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

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

说了那么多理论总是要实践一下的,下面以user测试表为例进行测试实践。

实践

(1)通过查询 explain select * from user where name ='张三';name字段并没有创建索引。

我们可以通过创建一个联合索引index_name_age_time,来解决:

  1. alter table user add index index_name_age_time (name,age,update_time) ; 

当再次查询的时候,就会使用上了索引:

(2)使用联合索引要遵循「最左前缀法则」,关于最左前缀法则原则的使用,之前我写过一篇详细介绍的文章,可以参考[]。

(3)在使用索引进行查询的时候,不要做任何的函数操作,不然会导致索引失效:例子:EXPLAIN SELECT * FROM user WHERE name = '张三';

但是你在使用的时候,使用了left()函数,如:EXPLAIN SELECT * FROM employees WHERE left(name,2) = '张三';,会导致索引失效。

(4)在数据库的查询中不要使用(!=或者<>)等判条件和is null,is not null、like关键词中以%开头来判断,不然也会使索引失效:

【责任编辑:庞桂玉 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

数据中心和VPDN网络建设案例

数据中心和VPDN网络建设案例

漫画+案例
共20章 | 捷哥CCIE

136人订阅学习

搭建数据中心实验Lab

搭建数据中心实验Lab

实验平台Datacenter
共5章 | ITGO(老曾)

94人订阅学习

大数据安全运维实战

大数据安全运维实战

CDH+Ambari
共20章 | 大数据陈浩

91人订阅学习

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微