MySQL中SQL性能分享-explain详解

简介：MySQL explain详解

explain 命令获取 select 语句的执行计划，通过 explain 我们可以知道以下信息：表的读取顺序，数据读取操作的类型，哪些索引可以使用，哪些索引实际使用了，表之间的引用，每张表有多少行被优化器查询等信息。

使用方法也很简单只需要在select之前加explain即可,如下图所示：

接下来我们来解释一下分析结果中各个字段的含义。

1.id：

id列的编号是 select 的序列号，sql语句中查询了几次数据库就会有几个id。当我们使用union查询时就会出现多个id

此外涉及两个表的子查询也会出现多个id，例如：通过管理员信息查询角色相关信息造成的子查询

2.select_type

select表示对应的查询类型，区分简单查询，子查询，复杂查询

• SIMPLE：简单查询。查询不包含子查询和union

• PRIMARY：复杂查询中最外层的 select
• SUBQUERY：包含在 select 中的子查询（不在 from 子句中）

• UNION：在 union 中的第二个和随后的 select

3.table

当前访问哪个表

4.partitions

如果查询是基于分区表的话，会显示当前查询将访问的分区

5.type

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行。

• NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表
• CONST, SYSTEM：mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快
• EQ_REF：primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type
• REF：相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行
• REF_OR_NULL：类似ref，但是可以搜索值为NULL的行
• index_merge：表示使用了索引合并的优化方法。 例如下表：id是主键，tenant_id是普通索引。or 的时候没有用 primary key，而是使用了 primary key(id) 和 tenant_id 索引
• RANGE：范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行
• INDEX：和ALL一样，不同就是mysql只需扫描索引树，这通常比ALL快一些
• ALL：即全表扫描，意味着mysql需要从头到尾去查找所需要的行。通常情况下这需要增加索引来进行优化了

依次从最优到最差分别为：

system > const > eq_ref > ref > ref_or_null > index_merge > range > index > ALL

6.possible_keys

这一列显示查询可能使用哪些索引来查找。如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能。另外如果这个列出现大量可能被使用的索引(例如多于3 个), 那么这 意味着备选索引数量太多了，同时也可能提示存在无效的索引。

7.key

该列指出mysql优化器决定选择使用哪个索引来优化对该表的访问。一般来说SQL查询中的每个表都只会使用一个索引。但是也存在索引合并的少数情况，如给定表上用到了两个或者更多索引。查询过程中由优化器来决定实际使用的索引。如果possible_keys索引列表中没有适合查找行的索引，那么这个key可能会命名一个不存在于该possible_keys值中的索引

如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index

8.key_len

这一列显示了mysql在索引里使用的字节数。

9.ref

ref 列显示使用哪个列或常数与key一起从表中选择数据行。指出对 key 列所选择的索引的查找方式，常见的值有 const, func, NULL, 具体字段名。当 key 列为 NULL ，即不使用索引时 。如果值是func，则使用的值是某个函数的结果

10.rows

这一列是mysql评估 为了找到所需的行而要读取的行数。这个数字是内嵌循环关联计划里的循环数目，也就是说它不是mysql认为它最终要从表里读取出来的行数，而是mysql为了找到符合查询的每一点上标准的那些行而必须读取的行的平均数。

rows 列提供了试图分析所有存在于累计结果集中的行数目的MySQL 优化器估计值。执行计划很容易描述这个很困难的统计量。 查询中总的读操作数量是基于合并之前行的每一行的rows 值的连续积累而得出的。这是一种嵌套行算法。数值越大越不好，说明没有用好索引

11.filtered

通过查询条件获取的最终记录行数占通过type字段指明的搜索方式搜索出来的记录行数的百分比。

12.extra

• NULL: 不存在信息
• range checked for each record :没有找到合适的索引
• Using index：这发生在对表的请求列都是同一索引的部分的时候，返回的列数据只使用了索引中的信息，而没有再去访问表中的行记录。是性能高的表现。
• Using index condition：出现这个说明mysql使用了覆盖索引，避免访问了表的数据行，效率不错！
• using temporary：对查询结果进行排序的时候使用了一张临时表。出现这种情况一般是要进行优化的
• using filesort：对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。
• using where：表示 MySQL 服务器从存储引擎收到查询数据,再进行“后过滤”（Post-filter）。所谓“后过滤”，就是先读取整行数据，再检查此行是否符合 where 句的条件，符合就留下，不符合便丢弃。因为检查是在读取行后才进行的，所以称为“后过滤”
• distinct：一旦mysql找到了与行相联合匹配的行，就不再搜索了

有遗漏或者不对的可以在我的公众号留言哦