mysql range ref_explain都不会用，你还好意思说精通MySQL查询优化？

Explain简介

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

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

// 用户表

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=InnoDBDEFAULTCHARSET=utf8;

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');

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=InnoDBDEFAULTCHARSET=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=InnoDBDEFAULTCHARSET=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;，执行后可以看到执行的结果如下：

可以看到这里有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);

+----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------------------------------------------------------------+

| id | select_type | table     | partitions | type  | possible_keys | key                | key_len | ref  | rows | filtered | Extra                                                                             |

+----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------------------------------------------------------------+

|  1 | SIMPLE      | user      | NULL       | ALL   | PRIMARY       | NULL               | NULL    | NULL |    3 |   100.00 | NULL                                                                              |

|  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) |

+----+-------------+-----------+------------+-------+---------------+--------------------+---------+------+------+----------+-----------------------------------------------------------------------------------+

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

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

+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra       |

+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+

|  1 | PRIMARY     | role  | NULL       | index | NULL          | index_name | 33      | NULL |    3 |   100.00 | Using index |

|  2 | SUBQUERY    | user  | NULL       | index | NULL          | PRIMARY    | 4       | NULL |    3 |   100.00 | Using index |

+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-------------+

就会看到有两条记录，并且两条记录的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字段中可以看出，第四行的记录来源于第二行和第三行，因此一个UNION查询语句的结果就会被标记为UNION RESULT

其它

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

DEPENDENT UNION：也表示UNION查询语句中第二个或者后面的语句，但是取决于外面的查询。

DEPENDENT SUBQUERY：子查询中的第一个select语句，也是依赖于外部的查询。

UNCACHEABLE SUBQUERY：子查询的结果不能被缓存，必须重新评估外连接的第一行。

table字段

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

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

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，来解决：

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