MySQL执行计划全面解释

MySQL执行计划

项目开发中,性能往往都是是我们重点关注的问题,其实很多时候一个SQL往往是整个请求中瓶颈最大的地方,因此我们必须了解SQL语句的执行过程、数据库中是如何扫描表、如何使用索引的、是否命中索引等信息来帮助我们做SQL语句的优化。MySQL提供了explain/desc语句,来显示这条SQL语句的执行计划,执行计划可以帮助我们查看SQL语句的执行情况,我们可以根据反馈的结果来进行SQL的优化。

  • 准备测试数据
use test;
CREATE TABLE `test`.`role`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

CREATE TABLE `test`.`user`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `role_id` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

explaindesc效果一样,都是帮我们列出SQL语句的执行计划。

mysql> explain select * from user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select * from user;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql>

两个语句的效果是一模一样的,我们后面就统一使用explain来查看SQL语句的执行计划。

1.1 id字段

id字段存在三种情况:

1)id相同:id越靠前的表越先执行

explain select * from user u left join role r on u.role_id=r.id;

在这里插入图片描述
2)id不同:id越大的表越先执行

explain select * from user u where u.role_id=(select id from role r where r.id=1);

在这里插入图片描述
3)id有相同,也有不同:id相同越大的表越先执行,在id相同的表中,id越靠前的表越先执行

1.2 select_type 字段

  • SIMPLE:简单的 select 查询,查询中不包含子查询或者 union
explain select * from user;

在这里插入图片描述

  • PRIMARY:查询条件中包含有子查询时最外层的表(u1)
explain select * from user u1 where u1.id =(select id from user u2 where u2.id=1);

在这里插入图片描述

  • UNION(u2):使用到union关联时,union关联的表
  • UNION RESULT(<union1,2>):使用union时,最终的结果集表
explain select * from user u1 union select * from user u2;

在这里插入图片描述

  • DEPENDENT UNION(u3,u4):在子查询中使用到union的第二个以上的表
  • DEPENDENT SUBQUERY(u2):在子查询中,使用到union的第一表
EXPLAIN SELECT
	* 
FROM
	user u1 
WHERE
	u1.id IN ( 
		SELECT id FROM user u2 WHERE u2.id = 1 
		UNION 
		SELECT id FROM user u3 WHERE u3.id = 2 
		UNION 
		SELECT id FROM user u4 WHERE u4.id = 2  
	);

在这里插入图片描述

  • SUBQUERY(u2):条件子查询中的表
explain select * from user u1 where u1.id =(select id from user u2 where u2.id=1);

在这里插入图片描述

  • SUBQUERY(u2,u3):条件中的子查询中的表(包括多重层级)
explain select * from user u1 
where u1.name =(
	select name from user u2 where u2.name=(select name from user u3 where u3.name='zs')
);

在这里插入图片描述

  • DEPENDENT SUBQUERY(r1): 子查询中的条件依赖于外部的查询(r1的条件是u1表中的数据)
explain select * from user u1 where u1.role_id=(select id from role r1 where u1.id=1);

在这里插入图片描述

  • DERIVED(u1):衍生表的from子表(该子表必须使用union关联其他表)
explain select * from 
(select * from user u1 where u1.role_id=1 union select * from user u2 where u2.name='zs') temp;

在这里插入图片描述

1.3 table 字段

表示该SQL语句是作用于那张表的,取值为:表名、表别名、衍生表名等。

explain select * from user;

explain select * from user u1;

在这里插入图片描述

1.4 partitions 字段

涉及到分区的表

  • 准备数据
create table goods_partitions (
	id int auto_increment, 
	name varchar(12),
    primary key(id)
)
partition by range(id)(
		partition p0 values less than(10000),
		partition p1 values less than MAXVALUE
);
  • 查看MySQL的物理存储路径:
show variables like '%dir%';

在这里插入图片描述
查看物理存储文件,发现多了不同的文件来存储在这里插入图片描述

  • 查看查询语句所使用到的分区:
explain select * from goods_partitions;

在这里插入图片描述

整个goods_partitions使用到了两个分区。

  • 查询id<1000的记录(属于p0分区)
explain select * from goods_partitions where id<1000;

在这里插入图片描述

1.5 type字段

反应一段SQL语句性能指标的重要参数,可以通过此参数来判断是否使用到了索引、是否全表扫描、是否范围查询等。

插入测试数据:

insert into role values(1,'保洁');
insert into role values(2,'保安');
insert into role values(3,'厨师');
insert into user values(1,'zs',1);
  • null:代表不访问任何表
explain select 1;

在这里插入图片描述

  • system:表中只有一条记录,并且此表为系统表(一般很少出现)
use mysql;			-- 切换到mysql数据库
explain select * from db where host='localhost';

在这里插入图片描述

  • const:通过唯一索引查询到的数据,只查询一次就查询到了
explain select * from user where id=1;
explain select * from user where name='zs';

在这里插入图片描述

分别根据name和id查询,发现只有id的type为const。

给name字段加上唯一索引(必须要是唯一索引,普通索引不行):

create unique index user_name_unique on user(name);

在这里插入图片描述
测试完毕删除唯一索引:

drop index user_name_unique on user;
  • eq_ref:使用主键的关联查询
explain select * from user u left join role r on u.role_id=r.id;

在这里插入图片描述
代表有其他表引用了r表的主键。

这里需要提一下,eq_ref有时候会不准!

  • 首先查看两个表的数据:
mysql> select * from user;
+----+------+---------+
| id | name | role_id |
+----+------+---------+
|  1 | zs   |       1 |
+----+------+---------+
1 row in set (0.00 sec)

mysql> select * from role;
+----+--------+
| id | name   |
+----+--------+
|  1 | 保洁   |
|  2 | 保安   |
|  3 | 厨师   |
+----+--------+
3 rows in set (0.00 sec)

mysql> explain select * from user u left join role r on u.role_id=r.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref            | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+
|  1 | SIMPLE      | u     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL           |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | r     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.u.role_id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql>

user表示是1条记录,role是3条记录,得到的执行计划是正常的,没有问题。

但是如果user表中的记录不是一条,而是多条的时候就会变的不准确!

  • 在user表中新增一条记录,再次查看执行计划:
mysql> select * from user;
+----+------+---------+
| id | name | role_id |
+----+------+---------+
|  1 | zs   |       1 |
|  2 | ls   |       1 |
+----+------+---------+
2 rows in set (0.00 sec)

mysql> select * from role;
+----+--------+
| id | name   |
+----+--------+
|  1 | 保洁   |
|  2 | 保安   |
|  3 | 厨师   |
+----+--------+
3 rows in set (0.00 sec)

mysql> explain select * from user u left join role r on u.role_id=r.id;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | u     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL                                               |
|  1 | SIMPLE      | r     | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    3 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql>

发现type字段变为了ALL,而不是原来的eq_ref

同样道理,如果我们把role作为主表,一样是出现ALL

mysql> select * from user;
+----+------+---------+
| id | name | role_id |
+----+------+---------+
|  1 | zs   |       1 |
|  2 | ls   |       1 |
+----+------+---------+
2 rows in set (0.00 sec)

mysql> select * from role;
+----+--------+
| id | name   |
+----+--------+
|  1 | 保洁   |
|  2 | 保安   |
|  3 | 厨师   |
+----+--------+
3 rows in set (0.00 sec)

mysql> explain select * from user u right join role r on u.role_id=r.id;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | r     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL                                               |
|  1 | SIMPLE      | u     | NULL       | ALL  | role_id       | NULL | NULL    | NULL |    2 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

mysql>

除非删除role表的数据,只留一条数据:

mysql> select * from user;
+----+------+---------+
| id | name | role_id |
+----+------+---------+
|  1 | zs   |       1 |
|  2 | ls   |       1 |
+----+------+---------+
2 rows in set (0.00 sec)

mysql> select * from role;
+----+--------+
| id | name   |
+----+--------+
|  1 | 保洁   |
+----+--------+
1 row in set (0.00 sec)

mysql> explain select * from user u right join role r on u.role_id=r.id;
+----+-------------+-------+------------+------+---------------+---------+---------+-----------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref       | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-----------+------+----------+-------+
|  1 | SIMPLE      | r     | NULL       | ALL  | NULL          | NULL    | NULL    | NULL      |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | u     | NULL       | ref  | role_id       | role_id | 5       | test.r.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------+---------+-----------+------+----------+-------+
2 rows in set, 1 warning (0.01 sec)

mysql>

是不是有点奇怪?主表必须只有一条记录,执行计划才是正常的,否则都不正常,但是我反复测试后(过了一天再查看执行计划),发现有的时候又是正常的(驱动表多条记录时,执行计划也是正常),我感觉这个是执行计划的Bug。当然,这个等待求证

我查了很多资料,没有查到相关信息,问同事他们也是草草回答:“这是MySQL执行计划的Bug”。以后会再问问身边的技术大佬,如果有知道的小伙伴,也欢迎在评论区指出~~~

  • ref:通过非唯一索引查询到的数据

创建普通索引:

create index user_name_index on user(name);

查询执行计划:

explain select * from user where name='zs';

在这里插入图片描述
测试完毕删除索引:

drop index user_name_index on user;
  • range:使用索引的范围查询(普通列的范围查询不会是range)

我们执行如下两句sql查看执行计划:

explain select * from user u where u.id>20;				-- 使用索引列进行范围查询

explain select * from user u where u.role_id>20;		-- 使用普通列进行范围查询

在这里插入图片描述
给role_id列添加索引,再次执行sql,查看执行计划:

create index user_role_id_index on user(role_id);			

explain select * from user u where u.role_id>20;

在这里插入图片描述

  • index:查询的是索引列,遍历了索引树
explain select id from user;

在这里插入图片描述

  • ALL:效率最低,遍历全表
explain select * from user;

在这里插入图片描述
查询效率从高到底的取值为:

-- 所有的type字段取值:
NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

-- 一般情况下type字段取值:
system > const > eq_ref > ref > range > index > ALL

1.6 possible_keys 字段

查询语句中,可能应用到的索引,并非实际使用到的索引。实际使用到的索引根据key字段来反应。

例如:

-- 给name列加索引
create index idx_name on user(name); 

explain select * from user where user='1' or user='2';

在这里插入图片描述

可能用到了idx_name索引,但实际没有使用到。

测试完毕删除索引:

drop index idx_name on user;

1.7 key 字段

key字段反应sql语句实际使用的索引,为null代表没有使用索引

-- 根据id查询
explain select * from user where id=1;

-- 根据普通列查询
explain select * from user where name='zs';

-- 给name列加上索引
create index idx_name on user(name);

-- 根据索引查询
explain select * from user where name='zs';

在这里插入图片描述
测试完毕删除索引:

drop index idx_name on user;

1.8 key_len 字段

表示索引中使用的字节数

explain select * from user where id=1;

在这里插入图片描述

我的id类型为int类型,因此占用4个字节。

我们把id类型改为bigint(Long),再次查看索引使用字节数:

alter table user modify column id bigint;

explain select * from user where id=1;

在这里插入图片描述
测试完毕更改回来:

alter table user modify column id int;

1.9 ref 字段

表示某表的某个字段引用到了本表的索引字段

注意:ref这里也会出现type刚刚那里一样的情况,就是驱动表(u表)如果包含的数据大于1条,那么type字段会显示ALL,二ref字段将会显示NULL

驱动表只有一条记录时,没有问题:

mysql> select * from user;
+----+------+---------+
| id | name | role_id |
+----+------+---------+
|  1 | zs   |       1 |
+----+------+---------+
1 row in set (0.00 sec)

mysql> select * from role;
+----+--------+
| id | name   |
+----+--------+
|  1 | 保洁   |
|  2 | 保安   |
|  3 | 厨师   |
+----+--------+
3 rows in set (0.00 sec)

mysql> explain select * from user u left join role r on u.role_id=r.id;
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref            | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+
|  1 | SIMPLE      | u     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL           |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | r     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | test.u.role_id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql>

在这里插入图片描述

表示u表的role_id引用了本表(r表)的索引字段(PRIMARY),ref字段显示正常,type字段也显示eq_ref(正常).

使用其他索引列关联表:

create index role_name_index on role(name);		-- 给name列加索引。

explain select * from user u left join role r on u.name=r.name;   -- 使用name列来关联

在这里插入图片描述

表示u表的name字段引用了本表(r表)的索引字段(role_name_index),ref字段显示正常,type字段也显示eq_ref(正常).

为了测试那个问题,我们在user表中添加一条记录:

mysql> insert into user values(2,'ls',1);
Query OK, 1 row affected (0.00 sec)

mysql> explain select * from user u left join role r on u.role_id=r.id;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | u     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL                                               |
|  1 | SIMPLE      | r     | NULL       | ALL  | PRIMARY       | NULL | NULL    | NULL |    3 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.01 sec)

mysql>

发现ref字段变为了null,并且type字段又重新变为了ALL,而不是eq_ref。

这里再提一句:经过我反复测试(过了一天、几个小时、重启了几次虚拟机等等),再次执行SQL语句,发现ref和type字段都显示正常(数据还是驱动表是多条),我初步怀疑是MySQL执行计划的一个Bug(这个等待我去MySQL官网求证)。

测试完毕删除索引:

drop index role_name_index on role;

1.10 rows 字段

根据表统计信息及选用情况,大致估算出找到所需的记录或所需读取的行数。

explain select * from user;

explain select * from role;

在这里插入图片描述

user表中有1条记录,role表中有3条记录。

1.11 filtered 字段

返回结果与实际结果的差值占总记录数的百分比

insert into user values(2,'ls','4');

explain select * from user u inner join role r on u.role_id=r.id;

在这里插入图片描述

r表实际记录3条,上述sql语句关联查询出来的结果只能得出一条结果集,因此命中率为33.33%。

查询此SQL语句查询的记录数。

select * from user u inner join role r on u.role_id=r.id;

在这里插入图片描述

只有一条记录

1.12 extra 字段

显示其他扩展信息

  • Using filesort:排序时无法使用到索引时,就会出现这个。常见于order by和group by语句中。效率低
explain select name from user order by name;

在这里插入图片描述

  • Using temporary:表示SQL语句的操作使用到了临时表。
explain select name from user group by name;

在这里插入图片描述

  • Using index:代表使用到了索引,效率高
create index user_name_index on user(name);			-- 创建索引

explain select name from user order by name;

在这里插入图片描述
测试完毕删除索引:

drop index user_name_index on user;
  • Using where:扫描全表。通常是查询条件中不是索引字段。
explain select * from user where name='zs';

在这里插入图片描述

  • NULL:查询条件中的列,是索引字段,通过索引查询到记录,但查询列表不是索引字段,需要回表查询。即索引树上没有想要的数据,需要去查询表数据。但比Using where 要快的多,NULL最起码根据索引数找到了对应的行,然后去查询对应的行即可,Using where则根本没用使用到索引,全部扫描所有的数据查询。效率极低。
explain select * from user where id=1;

在这里插入图片描述
性能对比:

Using index > NULL > Using where >= Using temporary > Using filesort
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 创作都市 设计师: CSDN官方博客
应支付0元
点击重新获取
扫码支付

支付成功即可阅读