如何阅读 Explain 执行计划

美猴王六小龄童
498 阅读6分钟

一：执行计划是什么

什么是执行计划？简而言之，就是 SQL 在数据库中执行时的表现情况，通常用于 SQL 性能分析、优化和加锁分析等场景。SQL语句会经过解析器、优化器、执行器等系列阶段。我们如何知道 SQL 语句在 MySQL 中是如何被看待的，它是如何来执行的。通过使用 explain 关键字，我们可以管中窥豹。

二：MySQL 执行过程

image.png

由此可见，一条 SQL 的计算成本由两部分组成：

Cost = Server Cost + Engine Cost = CPU Cost + IO Cost

其中，CPU Cost 表示计算的开销，比如索引键值的比较、记录值的比较、结果集的排序等这些操作都在 Server 层完成；

IO Cost 表示引擎层 IO 的开销。

数据库 mysql 下的表 server_cost、engine_cost 则记录了对于各种成本的计算。

Server层的开销

select * from mysql.server_cost;

image.png

  • disk_temptable_create_cost：创建磁盘临时表的成本，默认为 20。

  • disk_temptable_row_cost：磁盘临时表中每条记录的成本，默认为 0.5。

  • key_compare_cost：索引键值比较的成本，默认为 0.05。

  • memory_temptable_create_cost：创建内存临时表的成本：默认为 1。

  • memory_temptable_row_cost：内存临时表中每条记录的成本，默认为 0.1。

  • row_evaluate_cost：记录间的比较成本，默认为 0.1。

可以看到创建磁盘临时表的成本最大，创建索引键值比较的成本最小。

Engine层的开销

select * from mysql.engine_cost;

image.png

  • io_block_read_cost：从磁盘读取一个页的成本，默认值为 1。
  • memory_block_read_cost：从内存读取一个页的成本，默认值为 0.25。

可以看到io_block_read_cost 与 memory_block_read_cost 两者的成本相差4倍。

三：Explain 执行计划详解

3.1、总览

image.png

  • id：Query Optimizer 所选定的执行计划中查询的序列号

  • select_type：显示本行是简单或复杂 select。如果查询有任何复杂的子查询，则最外层标记为PRIMARY、DERIVED.、UNION、UNION RESUIT 等。

  • table：显示这一步所访问的数据库中的表的名称

  • partitions：查询时匹配到的分区信息，对于非分区表值为 NULL，当查询的是分区表时，partitions 显示分区表命中的分区情况。

  • type：数据访问、读取操作类型（ALL、index、range、ref、eq_ref、const、system）等

  • possible_keys：该查询可以利用的索引，如果没有任何索引可以使用，就会显示成 null，这一 项内容对于优化时候索引的调整非常重要。

  • key：MySQL Query Optimizer 从 possible_keys 中所选择使用的索引

  • key_len：被选中使用索引的索引键长度

  • ref：列出是通过常量（const），还是某个表的某个字段（如果是 join）来过滤（通过 key） 的。

  • rows：MySQL Query Optimizer 通过系统收集到的统计信息估算出来的结果集记录条数

  • filtered：表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例。

  • Extra：查询中每一步实现的额外细节信息，如 Using filesort、index 等。

3.2、具体说明

下面逐一来说明下各个列所对应的含义。

1、id

explain select * from emp where emp.empno in (select emp_role.user_id from emp_role where emp_role.role_id = (select role.role_id from role where role.role_name='普通用户'))

image.png

通过这个例子可以发现，id可以相同，也可以不同。相同的归为一组，两者的优先级一样可以视为同时执行；不同的组，id较大的，优先级较高先执行。

以上面的语句为例，先查询出普通用户的角色id，然后在基于角色id去查询员工信息。

等同于下面的SQL：

explain
select * from emp t1 inner join (select emp_role.user_id from emp_role where emp_role.role_id = (select role.role_id from role where role.role_name='普通用户')) t2 on t1.empno = t2.user_id

image.png

2、select_type

explain 
select * from emp;

image.png 单表查询，不关联其他表进行查询，基本上都是SIMPLE类型。

explain 
select emp_role.user_id from emp_role where emp_role.role_id = (select role.role_id from role where role.role_name='普通用户')

image.png 当查询语句中包含任何复杂的子部分，最外层查询则被标记为PRIMARY类型。

explain
select * from emp t1 inner join (select emp_role.user_id from emp_role where emp_role.role_id = (select role.role_id from role where role.role_name='普通用户')) t2 on t1.empno = t2.user_id

image.png 当 select 或 where 列表中包含了子查询，该子查询被标记为SUBQUERY类型。

EXPLAIN 
SELECT * FROM (SELECT deptno, count(*) as t FROM emp GROUP BY deptno) AS derived_t1 where t > 100;

image.png 表示包含在 from 子句中的子查询的 select，可以抽象的理解为派生表，该子查询会被标记为DERIVED类型。

EXPLAIN
select * from dept t1 union all select * from dept t2

image.png 如果 union 后边又出现的 select 语句，则会被标记为UNION类型。

EXPLAIN
select * from dept t1 union  select * from dept t2

image.png 代表从 union 的临时表中读取数据，而 table 列的 <union1,2> 表示用id为1和为4 select 的结果进行 union 操作，会被标记为UNION RESULT类型。

3、table

表名，可以是具体的表，也可以是临时表

4、partitions

如果有用到分区，会显示具体的分区信息

5、type

type：查询使用了哪种类型，它在 SQL优化中是一个非常重要的指标，以下性能从好到坏依次是：system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。

system：当表仅有一行记录时（系统表）

const：表示查询时命中 primary key 主键或者 unique 唯一索引

eq_ref：查询时命中主键 primary key 或者 unique key 索引

ref：表示使用非唯一性索引

ref_or_null：表示使用非唯一性索引，会扫描有null的数据

range：使用了范围查询，包括 bettween...and、<、>、<=、in 等条件查询

index：Index 与 ALL 其实都是读全表，区别在于 index 是遍历索引树读取，而 ALL 是从硬盘中读取。

ALL：将遍历全表以找到匹配的行

6、possible_keys

可能用到的索引

7、key

实际生效的索引

8、key_len

索引key的长度，因为索引也是用树结构进行存储的，所以长度越短越节省空间。

9、ref

显示了之前的表在key列记录的索引中查找值所用的列或常量。

image.png

10、rows

以表的统计信息和索引使用情况，估算要找到我们所需的记录，需要读取的行数。

11、filtered

filtered是一个百分比的值，表示表里符合条件的记录数的百分比。

12、Extra

Using index

使用了覆盖索引，避免回表，因为想要查询的数据在索引列中存在。

explain
select * from emp where ename = 'ksgDzK' and sal = 400

Using index condition

using condition index代表使用二级索引不够还要回表，但回表之前会过滤此二级索引能过滤的where条件。

explain
select * from emp where ename = 'ksgDzK' and sal = 400

image.png

Using where

未找到可用的索引，使用where条件进行过滤数据

Using temporary

有用到临时表来进行辅助分组或者排序

Using filesort

表示没有在引擎层完成数据排序，而是将数据在Server层进行了排序

Using join buffer

在联表查询的时候，如果表的连接条件没有用到索引，需要有一个连接缓冲区来存储中间结果

avatar
文章
阅读
粉丝