MySQL之EXPLAIN

红太狼 2022-11-21 11:50 153阅读 0赞

关键词：explain 索引

EXPLAIN提供了关于MySQL如何执行语句的信息，即执行计划。可以用在SELECT、UPDATE、INSERT、REPLACE等语句前，本文主要讨论的是SELECT。通常使用EXPLAIN SELECT，结合分析返回结果，对SQL语句进行优化。

<文中案例操作使用的MySQL版本为：5.7.32，版本不同会存在差异>

比如下文中的例子，同样的查询语句，通过EXPLAINE查看执行计划，得到的结果有所不同。

5.1.73版本：

2020110117450267.png

5.7.31版本：

2020110117452644.png

从上面的两个结果比较可以看出，版本的不同，不仅仅是字段中的内容不同，而且，5.7.31版本要比5.1.73版本中多了两列：partitions、filtered。

█ 使用

以下结果截图，如果没有指明具体的版本，都是在5.7.31版本中执行后的结果。

①创建表和索引，并插入数据

  1. drop table if exists STUDENT;
  2. create table STUDENT(
  3.    id int primary key auto_increment,
  4.    car_id varchar(5),
  5.    first_name varchar(10),
  6.    last_name varchar(10),
  7.    class_id int,
  8.    index index_name(first_name, last_name)
  9. );
  11. create index index_car_id on STUDENT(car_id);
  13. insert into STUDENT(car_id,first_name,last_name,class_id) values('111','Jim', 'Green',1);
  14. insert into STUDENT(car_id,first_name,last_name,class_id) values('111','Dave', 'Brice',1);
  15. insert into STUDENT(car_id,first_name,last_name,class_id) values('222','Ann', 'King',2);
  16. insert into STUDENT(car_id,first_name,last_name,class_id) values('222','Rose', 'Rice',3);
  17. insert into STUDENT(car_id,first_name,last_name,class_id) values('333','jack', 'ma',4);
  19. drop table if exists CLASS;
  20. create table CLASS(
  21.    id int primary key auto_increment,
  22.    name varchar(10)
  23. );
  25. create index index_name on CLASS(name);
  27. insert into CLASS(name) values('语文');
  28. insert into CLASS(name) values('数学');
  29. insert into CLASS(name) values('英语');

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl81MDUxODI3MQ_size_16_color_FFFFFF_t_70 student表数据

20201101174755949.png class表数据20201101174829803.png student表索引20201101174857202.png class表索引

②使用explain查看表结构

语法：explain 表名;

此用法和desc相同。

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl81MDUxODI3MQ_size_16_color_FFFFFF_t_70 1

③使用explain查看执行计划

语法：explain select语句;

20201101175009667.png

█ 执行计划

  • id：查询的标识

用来区分每组查询。值是个有序递增数字，从1开始。值相同时，表示是一组查询，值不同时，表示不同组的查询。值越大，优先级越高，越先被执行。值相同时，按照结果表格中从上到下依次执行。比如查看select s.first_name, (select c.name from CLASS c) from STUDENT s; 的执行计划：

20201101175138547.png

返回结果的id不同，数字越大越优先执行。select c.name from CLASS c语句的id为2，则会先被执行，然后再执行select s.first_name from STUDENT s;再比如查看select * from STUDENT where CLASS_ID in (select name from CLASS); 的执行计划：

2020110117521420.png

从返回结果的id相同，从上到下依次执行。select * from STUDENT语句在上，先被执行，接着再执行select name from CLASS。

  • select_type：查询的连接类型

有以下可选项，不同的值表示不同的类：

①SIMPLE

简单查询，没有使用UNION的查询或子查询。比如查看select * from STUDENT where id=1; 的执行计划：

20201101175303231.png

②PRIMARY

在UNION或者子查询中，最外层的查询会是此类型，即最后被执行的语句。比如查看子查询：select s.first_name, (select c.name from CLASS c) from STUDENT s;的执行计划：

20201101175329956.png

从id的值可以看出，id=1的查询语句是最后被执行的，其select_type就是PRIMARY类型的。再比如查看UNION查询：select s.first_name from STUDENT s union all select c.name from CLASS c;的执行计划：

20201101175355691.png

③UNION

对于UNION查询，不是最后一个执行的语句会是此类型，因为最后一个被执行的语句是PRIMARY类型。比如查看select s.first_name from STUDENT s union all select c.name from CLASS c union all select class.id from CLASS class;三张表使用UNION的执行计划：

20201101175423571.png

④DEPENDENT UNION

个人认为，对于UNION查询，如果PRIMARY类型的查询语句需要依赖UNION的某个语句，则这个被依赖的语句会是DEPENDENT UNION类型。所谓依赖是指需要使用到语句的查询结果。比如查看select * from STUDENT stu where stu.first_name in (select s.first_name from STUDENT s union select c.name from CLASS c);的执行计划：

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl81MDUxODI3MQ_size_16_color_FFFFFF_t_70 2

⑤UNION RESULT

UNION查询的结果，根据（4）中图片最后一行可以看到此类型，并且当select_type是NUION RESULT时，id为NULL。

⑥SUBQUERY

子查询中的第一个查询语句，即子查询中最后一个被执行的语句。比如查看select FIRST_NAME, (select c.name from CLASS c) as className from STUDENT;的执行计划：

20201101175521329.png

图中的子查询select c.name from CLASS c，因为此语句的结果不会被select from student所依赖，所以是SUBQUERY，如果被依赖了，则是下面的DEPENDENT SUBQUERY类型

⑦DEPENDENT SUBQUERY

个人认为是在子查询中，结果被PRIMARY类型的语句所依赖的会是此类型。比如在5.1.7版本中查看select * from STUDENT where FIRST_NAME in (select id from CLASS);的执行计划（此语句在5.7.31中获得的结果不一样）：

20201101175550445.png 5.1.7版本执行结果

⑧DERIVED

派生表。比如查看select * from (select now() from dual) d; 的执行计划，其中(select now() from dual) d就是派生表。

20201101175626527.png

⑨MATERIALIZED

物化子查询。

⑩UNCACHEABLE SUBQUERY

子查询每次都不是从缓存中查询的。不能缓存其结果的子查询，并且必须针对类型为PRIMARY的每次查询的每一行重新求值。

⑪UNCACHEABLE UNION

和UNCACHEABLE SUBQUERY一样，只不过是针对UNION查询的。

  • table

查询的表名。当指定了别名，则显示别名。没有指定别名使用实际的表名。

未指定别名：

20201101175735176.png

指定了别名：

20201101175755139.png

并且区分大小写：

20201101175813711.png

  • partitions

查询的分区，当没有设置多个分区时，为NULL。

  • type

表连接的类型。有如下几种表连接类型，查询的效率依次从高到低。

①system

查询只有一条记录的系统表。是一种特殊的const类型。比如sys数据库中的version表：

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl81MDUxODI3MQ_size_16_color_FFFFFF_t_70 3

从执行计划结果看出，我猜测，执行select * from sys.version，感觉是执行了select version() from dual后再查询的。

②const

使用主键索引或唯一键索引等值查询表（使用=），即查询的索引列数据没有重复值，查询的结果最多有一条命中。比如：重新往STUDENT中插入多条数据，然后查询select * from STUDENT stu where stu.id = 3;的执行计划，其中id是主键索引。

20201101180010237.png

再比如查询select * from student where class_id = 3;的执行计划，其中class_id列虽然是索引，但是不是唯一值，是有重复值的：

20201101180030499.png

③eq_ref

连接查询时，表与表之间通过副表（左连接的右表或右连接的左表）的主键索引或者唯一键且不为NULL的索引作为连接条件的。比如查看select * from STUDENT s left join CLASS c on s.CAR_ID = c.id;连接查询的执行计划，查询条件使用了CLASS表的主键id索引，在左连接中，CLASS表是右表，是副表。

20201101180050348.png

比如查看select * from STUDENT s left join CLASS c on s.id = c.name;的执行计划，使用了左表（主表）的主键id索引，但是结果类型并不是eq_ref：

20201101180109717.png

④ref

ref反应的是查询条件用到的索引，不是主键索引和唯一索引，即索引列的值有重复值。比如查看select * from STUDENT where car_id = ‘111’;的执行计划，其中car_id列是索引，但是有重复值。

20201101180136716.png

⑤fulltext

全文索引的类型。

⑥ref_or_null

在ref类型的基础上，加上了查询NULL值的情况。比如在5.1.7版本中查看select * from STUDENT where CAR_ID = ‘111’ or CAR_ID is NULL的执行计划：

20201101180231203.png 5.1.7版本执行结果

⑦index_merge

索引合并。

⑧unique_subquery

唯一结果的子查询，即子查询返回的结果是唯一索引列（主键索引是特殊的唯一索引）。比如在5.1.7版本中查看select * from STUDENT where id in (select id from CLASS);的执行计划，其中子查询select id from CLASS返回的是CLASS主键列。

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl81MDUxODI3MQ_size_16_color_FFFFFF_t_70 4

⑨index_subquery

（这个有点没搞清楚）和unique_subquery的区别是，子查询返回的结果是有重复值的索引列。比如在5.1.7版本中查看select * from STUDENT s1 where s1.FIRST_NAME in (select NAME from CLASS);的执行计划：

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl81MDUxODI3MQ_size_16_color_FFFFFF_t_70 5 5.1.7版本执行结果

⑩range

范围查询，使用=, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE 或 IN() 等操作符。比如查看select * from student where id in (1,2,3);和select * from student where id < 5;的执行计划：

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl81MDUxODI3MQ_size_16_color_FFFFFF_t_70 6

⑪index

全表扫描索引表，不会去查询数据。查询的结果通过索引就能获取，即查询索引列的内容。比如查询select id from student where id <> 0;的执行计划，返回结果id通过索引数据就能获取到：

20201101180507326.png

⑫ALL

全表扫描数据表，有两种情况，一种是查询没有使用索引，查找的时候需要遍历整张数据表去匹配数据，比如：select * from student;还有一种情况是使用了索引，但是还需要遍历整张数据表才能获取数据，比如：select * from student where id is not null;虽然id列是索引列，但是因为是select *，结果是需要查询所有表才能得到的。

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl81MDUxODI3MQ_size_16_color_FFFFFF_t_70 7

  • possible_keys

查询可能用到的索引，显示的是索引名。此内容只是可能用到的索引，实际未必会用到。

  • key

查询实际用到的索引。

  • key_len

查询实际用到的索引的长度。

  • ref

在进行查询时，将什么类型的数据与索引列进行比较的。这里就是显示什么类型的数据。

  • rows

查询过程查询了多少行数据才找到目标值，此值是个估计值。

  • filtered

表示根据查询条件会过滤的行数的一个估计的百分比。最大值是100，表示没有过滤任何行。值从100减少表示过滤的次数增加。可能是查询最终的结果个数 / rows显示的条数 = filtered的百分比值。

  • Extra

额外的信息。举例说明：

①Impossible WHERE

字面意思是不可能的where条件，当where条件永远是false的时候，会显示此信息。比如：select id from STUDENT where 1>2;

20201101180640793.png

②Using index

此处有个功能叫索引下推，当使用辅助索引的时候，通过辅助索引就能得到要查询的结果，不需要再去查询主键索引去获取行数据了。比如：select id from student where car_id = ‘111’;其中car_id是辅助索引，辅助索引的叶子节点会记录其对应的主键id值的：

20201101180709246.png

③no matching row in const table

使用唯一索引查询不到记录。比如：select * from student where id = 9;STUDENT表中总共有5条数据，id最大是5，根据id=9是查不到记录的：

20201101180731859.png

④Using where

根据索引过滤不出数据，必须通过where条件进行过滤数据。

20201101180755284.png

⑤Using filesort

在排序语句中，需要先根据where条件排序，再根据排序的内容去查询获得数据。一般在不适用索引的时候排序，或者使用辅助索引排序的时候出现。比如：select * from student order by class_id;其中class_id不是索引列。select * from student order by car_id;其中car_id虽然是索引列，但因为是select *，单单通过辅助索引是获取不到所有的字段信息的。

watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl81MDUxODI3MQ_size_16_color_FFFFFF_t_70 8

对于查询效率慢的sql，可以通过EXPLAIN查看执行计划，一般根据key来判断是否使用了索引，根据type、Extra信息来调整语句。

关于EXPLIANE的使用就到这里了，文章写的很浅显。关于EXPLAINE的使用和利用其优化SQL，需要在实际开发中经常使用，以此来体会。

发表评论

表情：
评论列表 （有 0 条评论，153人围观）

还没有评论，来说两句吧...

相关阅读

    相关 MySQL之-Explain

    一、Explain介绍： 定义：模拟优化器执行SQL语句，得出MySQL执行计划。分析SQL语句。 作用： 表的读取顺序；

    小咪咪小咪咪/ 0 赞/ 12 阅读

    相关 MySQL之EXPLAIN

    > 关键词：explain    索引   -------------------- EXPLAIN提供了关于MySQL如何执行语句的信息，即执行计划。可以用在SELEC

    红太狼红太狼/ 0 赞/ 154 阅读

    相关 mysql之explain详解

    在 explain的帮助下，您就知道什么时候该给表添加索引，以使用索引来查找记录从而让select 运行更快。 如果由于不恰当使用索引而引起一些问题的话，可以运行 anal

    ゝ一纸荒年。ゝ一纸荒年。/ 0 赞/ 252 阅读

    相关 mysql之explain

    1.定义：explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句。 ![271101207779336.pn

    冷不防冷不防/ 0 赞/ 173 阅读