526互联

10-explain 调取语句的执行计划

发布时间 2023-12-27 14:45:59作者: EJW

explain（执行计划），使用explain关键字可以模拟优化器执行sql查询语句，从而知道MySQL是如何处理sql语句。explain主要用于分析查询语句或表结构的性能瓶颈

mysql> explain select name,gender,age from test where gender='F' and age <20;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test  | range | inx_test      | inx_test | 7       | NULL |    1 | Using index condition |

一、explain 个列的解释

  • table：显示这一行的数据是关于哪张表的。
  • type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为 const、eq_reg、ref、range、index 和 ALL。
  • possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从 WHERE 语句中选择一个合适的语句。
  • key： 实际使用的索引。如果为 NULL，则没有使用索引。很少的情况下，MYSQL 会选择优化不足的索引。这种情况下，可以在 SELECT 语句中使用 USE INDEX（indexname）来强制使用一个索引或者用 IGNORE INDEX（indexname）来强制 MYSQL 忽略索引。
  • key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好。如果太长建议把普通的索引替换成前缀索引
  • ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。
  • rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好
  • Extra：关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢

二、extra 列返回的描述的意义

1、Distinct : 一旦 MySQL 找到了与行相联合匹配的行，就不再搜索了。

2、Range checked for each Record（index map:#）: 没有找到理想的索引，因此对于从前面表中来的每一个行组合，MySQL 检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一。

3、Using filesort : 看到这个的时候，查询就需要优化了。MySQL 需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。

4、Using index : 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候。

5、Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL 需要创建一个临时表来存储结果，这通常发生在对不同的列集进行 ORDER BY 上，而不是 GROUP BY 上 Where used 使用了 WHERE 从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型 ALL或 index，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）。

6、system 表只有一行：system表。这是const连接类型的特殊情况

7、const : 表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为 MySQL 先读这个值然后把它当做常数来对待。

8、eq_ref : 在连接中，MySQL 在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用。

9、ref : 这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好。

10、range:这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况

11、index: 这个连接类型对前面的表中的每一个记录联合进行完全扫描（比 ALL 更好，因为索引一般小于表数据）。

12、ALL : 这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免

13、Not exists : MySQL 优化了LEFT JOIN，一旦它找到了匹配 LEFT JOIN 标准的行，就不再搜索了。

出现Using temporary
,Using filesort
,Using join buffer
一般是排序 order by ,group by ,distinct,条件上没有索引例如：
explain select * from city where countrycode='CHN' order by population;
或者在join 的条件列上没有建立索引

type : 表示MySQL在表中找到所需行的方式，又称“访问类型”，

  • 常见类型如下:ALL,index, range, ref, eq_ref, const, system, NULL从左到右，性能从最差到最好
  1. ALL：

Full Table Scan， MySQL将遍历全表以找到匹配的行

如果显示ALL，说明：
查询没有走索引：说明一语句本身的问题、二、索引的问题，没建立索引

例子

explain select count(*) from stu ;
  1. index

index： Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）
例子：

  1. range

range:索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。
显 而易见的索引范围扫描是带有between或者where子句里带有<,>查询。
where 条件中有范围查询或模糊查询时如：

>  < >= <=   between  and   in ()   or
like 'xx%'

当mysql使用索引去查找一系列值时，例如IN()和OR列表，也会显示range（范围扫描）,当然性能上面是有差异的。

  1. ref

对于普通索引这就是最高的级别了
ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行比如：

where stu_name='xiaoming'

explain select *  from stu  where stu_name='aa';

5、 eq_ref

eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，
就是多表连接中使用primary key或者 unique key作为关联条件

join条件使用的是primary key或者 unique key

6、 const,system

const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。
如将主键置于where条件的条件，MySQL就能将该查询转换为一个常量

explain select * from city where id=1;

  1. null

NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，
例如走子查询的数据在父查询中有，又或者数据在内存中直接有