乐趣区

关于mysql:带你看懂MySQL执行计划

jiezi

前言：

后面文章，咱们学习了 MySQL 慢日志相干内容，当咱们筛选失去具体的慢 SQL 后，就要想方法去优化啦。优化 SQL 的第一步应该是读懂 SQL 的执行打算。本篇文章，咱们一起来学习下 MySQL explain 执行打算相干常识。

1. 执行打算简介

执行打算是指一条 SQL 语句在通过 MySQL 查问优化器的优化会后，具体的执行形式。MySQL 为咱们提供了 EXPLAIN 语句，来获取执行打算的相干信息。须要留神的是，EXPLAIN 语句并不会真的去执行相干的语句，而是通过查问优化器对语句进行剖析，找出最优的查问计划，并显示对应的信息。

执行打算通常用于 SQL 性能剖析、优化等场景。通过 explain 的后果，能够理解到如数据表的查问程序、数据查问操作的操作类型、哪些索引能够被命中、哪些索引理论会命中、每个数据表有多少行记录被查问等信息。

explain 执行打算反对 SELECT、DELETE、INSERT、REPLACE 以及 UPDATE 语句。咱们个别多用于剖析 select 查问语句。

2. 执行打算实战

咱们简略来看下一条查问语句的执行打算：

mysql> explain SELECT * FROM dept_emp WHERE emp_no IN (SELECT emp_no FROM dept_emp GROUP BY emp_no HAVING COUNT(emp_no)>1);
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys   | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+
|  1 | PRIMARY     | dept_emp | NULL       | ALL   | NULL            | NULL    | NULL    | NULL | 331143 |   100.00 | Using where |
|  2 | SUBQUERY    | dept_emp | NULL       | index | PRIMARY,dept_no | PRIMARY | 16      | NULL | 331143 |   100.00 | Using index |
+----+-------------+----------+------------+-------+-----------------+---------+---------+------+--------+----------+-------------+

能够看到，执行打算后果中共有 12 列，各列代表的含意总结如下表：

列名 含意
id SELECT 查问的序列标识符
select_type SELECT 关键字对应的查问类型
table 用到的表名
partitions 匹配的分区，对于未分区的表，值为 NULL
type 表的拜访办法
possible_keys 可能用到的索引
key 理论用到的索引
key_len 所选索引的长度
ref 当应用索引等值查问时，与索引作比拟的列或常量
rows 预计要读取的行数
filtered 按表条件过滤后，留存的记录数的百分比
Extra 附加信息

上面咱们来看下执行打算中局部重要列详解：

id：

SELECT 标识符。这是查问中 SELECT 的序号。如果该行援用其余行的并集后果，则值能够为 NULL。当 id 雷同时，执行程序 由上向下；当 id 不同时，id 值越大，优先级越高，越先执行。

select_type：

查问的类型，常见的值有：

  • SIMPLE：简略查问，不蕴含 UNION 或者子查问。
  • PRIMARY：查问中如果蕴含子查问或其余局部，外层的 SELECT 将被标记为 PRIMARY。
  • SUBQUERY：子查问中的第一个 SELECT。
  • UNION：在 UNION 语句中，UNION 之后呈现的 SELECT。
  • DERIVED：在 FROM 中呈现的子查问将被标记为 DERIVED。
  • UNION RESULT：UNION 查问的后果。

table：

示意查问用到的表名，每行都有对应的表名，表名除了失常的表之外，也可能是以下列出的值：

  • <unionM,N>: 本行援用了 id 为 M 和 N 的行的 UNION 后果；
  • <derivedN>: 本行援用了 id 为 N 的表所产生的的派生表后果。派生表有可能产生自 FROM 语句中的子查问。
  • <subqueryN>: 本行援用了 id 为 N 的表所产生的的物化子查问后果。

type：

查问执行的类型，形容了查问是如何执行的。所有值的程序从最优到最差排序为：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

常见的几种类型具体含意如下：

  • system：如果表应用的引擎对于表行数统计是准确的（如：MyISAM），且表中只有一行记录的状况下，拜访办法是 system，是 const 的一种特例。
  • const：表中最多只有一行匹配的记录，一次查问就能够找到，罕用于应用主键或惟一索引的所有字段作为查问条件。
  • eq_ref：当连表查问时，前一张表的行在以后这张表中只有一行与之对应。是除了 system 与 const 之外最好的 join 形式，罕用于应用主键或惟一索引的所有字段作为连表条件。
  • ref：应用一般索引作为查问条件，查问后果可能找到多个符合条件的行。
  • index_merge：当查问条件应用了多个索引时，示意开启了 Index Merge 优化，此时执行打算中的 key 列列出了应用到的索引。
  • range：对索引列进行范畴查问，执行打算中的 key 列示意哪个索引被应用了。
  • index：查问遍历了整棵索引树，与 ALL 相似，只不过扫描的是索引，而索引个别在内存中，速度更快。
  • ALL：全表扫描。

possible_keys：

possible_keys 列示意 MySQL 执行查问时可能用到的索引。如果这一列为 NULL，则示意没有可能用到的索引；这种状况下，须要查看 WHERE 语句中所应用的的列，看是否能够通过给这些列中某个或多个增加索引的办法来进步查问性能。

key：

key 列示意 MySQL 理论应用到的索引。如果为 NULL，则示意未用到索引。

key_len：

key_len 列示意 MySQL 理论应用的索引的最大长度；当应用到联结索引时，有可能是多个列的长度和。在满足需要的前提下越短越好。如果 key 列显示 NULL，则 key_len 列也显示 NULL。

rows：

rows 列示意依据表统计信息及选用状况，大抵估算出找到所需的记录或所需读取的行数，数值越小越好。

Extra：

这列蕴含了 MySQL 解析查问的额定信息，通过这些信息，能够更精确的了解 MySQL 到底是如何执行查问的。常见的值如下：

  • Using filesort：在排序时应用了内部的索引排序，没有用到表内索引进行排序。
  • Using temporary：MySQL 须要创立长期表来存储查问的后果，常见于 ORDER BY 和 GROUP BY。
  • Using index：表明查问应用了笼罩索引，不必回表，查问效率十分高。
  • Using index condition：示意查问优化器抉择应用了索引条件下推这个个性。
  • Using where：表明查问应用了 WHERE 子句进行条件过滤。个别在没有应用到索引的时候会呈现。
  • Using join buffer (Block Nested Loop)：连表查问的形式，示意当被驱动表的没有应用索引的时候，MySQL 会先将驱动表读出来放到 join buffer 中，再遍历被驱动表与驱动表进行查问。

这里揭示下，当 Extra 列蕴含 Using filesort 或 Using temporary 时，MySQL 的性能可能会存在问题，须要尽可能防止。

参考：

  • https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
  • https://juejin.cn/post/6953444668973514789
退出移动版