如何看懂MySQL的explain执行计划

素颜马尾好姑娘i 2022-11-19 01:08 193阅读 0赞

explain是个程序员应该都知道，但是要如何理解执行计划里面表达的信息呢？

里面包含了以下的一些信息：

*  id：复杂的查询语句可能包含多个执行计划，每个id都代表了一个执行计划
 *  select\_type：执行计划对应的查询类型
 *  table：查询的表名
 *  partitions：表分区
 *  type：使用索引的类型
 *  possible\_keys：可能使用的索引key
 *  key：实际使用的索引key
 *  key\_len：索引长度
 *  ref：使用某个字段的索引进行等值匹配时，等值匹配的那个目标值的一些信息（可能是const，也就是常量匹配，或者是别的表的某些字段都有可能，类似xxx\_db.table.id）
 *  rows：预计访问表的行数
 *  filtered：经过搜索条件过滤之后的剩余数据百分比，一般rows \* filtered%就能得到一个大概查出数据的数量
 *  Extra：额外的一些信息

里面的select\_type可能会有多种类型，常见的有SIMPLE（普通的简单查询）、PRIMARY（主查询）、SUBQUERY（子查询，一般都是在一个select语句中有另外一个select语句，类似select \* from table where id IN (select id from table2) ）、UNION（union查询语句）、DERIVED（针对子查询执行后的结果集会物化为一个内部临时表，然后外层查询是针对这个临时的物化表执行的）

接下来具体讲一下里面的type，这个很重要，一般也分为了几类

*  const：  
    类似这种直接使用聚簇索引查询的SQL语句：select \* from table where id = 1;  
    或者是通过二级索引 + 聚簇索引回表的形式查询的SQL语句：select \* from table where name = ‘xxx’;其中name必须是唯一索引  
    只有满足上面两种的查询才能叫做const，直接通过索引就能定位到数据，超高性能的常量级查询
 *  ref：  
    还是上面const类型的那个SQL：select \* from table where name = ‘xxx’;  
    如果这里的name并不是唯一索引，而是一个普通索引的话，那就是ref了，如果是多个列组成的一个联合索引的话，那么就需要保证从最左侧开始连续多个列都是等值匹配才行  
    这里还有一种特殊情况，那就是name即使是唯一索引，但是SQL中包含了 name IS NULL的话，那么查询方式也还是ref
 *  ref\_or\_null：  
    select \* from table where name = ‘xxx’ or name IS NULL;  
    这个SQL里面的name是普通索引的话，同时有索引等值比较和NULL值查询，就叫做ref\_or\_null
 *  range：  
    select \* from table where age >= 0 and age <= 18;  
    如果age是一个普通索引的话，这样使用索引进行范围查询的方式就是range
 *  index：  
    index是一种特殊的数据访问方式  
    假设有一个表里面的联合索引是KEY(x1,x2,x3)，SQL是select x1,x2,x3 from table where x2 = ‘xxx’，此时x2并不是联合索引最左的那个字段  
    所以SQL没办法从联合索引的索引树的根节点开始二分查找，但是查询的字段就是联合索引里面的几个字段

针对这种SQL，查询的时候，就会直接遍历联合索引的索引树的叶子节点  
聚簇索引的叶子结点里面存放的是完整的数据页，包含一行行的数据，而联合索引的叶子节点就包含了x1,x2,x3和主键的值  
所以针对这个SQL，直接遍历联合索引的叶子节点的那些页，一个个遍历，找到x2=xxx的数据，直接返回x1,x2,x3的值即可

这个遍历二级索引的过程，要比遍历聚簇索引快多了，因为字段的个数较少，相比聚簇索引的叶子结点就小多了，所以速度更快  
因此只要遍历一个KEY(x1,x2,x3)索引就可以了，不需要回源到聚簇索引上去

针对这种只要遍历二级索引就可以拿到想要的数据，而不需要回源到聚簇索引的访问方式，就叫做index

*  all：  
    全表查询，扫描聚簇索引里所有的叶子结点，表里面的数据一行行去扫描

Extra额外信息其实有的时候也能提供一些很重要的信息，这里举几种常见的例子

*  Using index：查询时仅用到了一个二级索引，不需要回表
 *  Using where：这个非常常见，一般是见于你直接针对一个表扫描，没用到索引，然后where里好几个条件，就会告诉你Using where，或者是你用了索引去查找，但是除了索引之外，还需要用其他的字段进行筛选，也会告诉你Using where
 *  Using filesort：排序时候使用的字段没有用到索引，此时就会把所有数据写入一个临时的磁盘文件，基于排序算法在磁盘文件里按照字段的值完成排序，这样往往效率都比较差
 *  Using temprory：如果使用group by、union、distinct之类的语法的时候，万一没法直接利用索引来进行分组聚合，那么他会直接基于临时表来完成，也会有大量的磁盘操作，性能其实也是极低的

**explain执行计划的核心就是分析哪些地方出现了全表扫描，或者扫描数据过大，尽可能通过合理优化索引保证执行计划每个步骤都可以基于索引执行，避免扫描过多的数据**