MySQL语句优化利器EXPLAIN-蒲公英云

MySQL语句优化利器EXPLAIN

EXPLAIN是什么

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈。EXPLAIN可以帮助选择更好的索引和写出更优化的查询语句。在MySQL5.6以及以后的版本中，除过select，其他比如insert，update和delete均可以使用EXPLAIN查看执行计划。

通过EXPLAIN，我们可以分析出以下结果：

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

EXPLAIN语法

EXPLAIN SELECT * FROM table

执行计划各字段含义

在这里插入图片描述

id
SELECT识别符。这是SELECT的查询序列号。id相同，执行顺序从上往下，
id不同，id值越大，优先级越高，越先执行。
select_type
查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询。
- simple ——简单的select查询，查询中不包含子查询或者UNION
- primary ——查询中若包含任何复杂的子部分，最外层查询被标记
- subquery——在select或where列表中包含了子查询
- derived——在from列表中包含的子查询被标记为derived（衍生），MySQL会递归执行这些子查询，把结果放到临时表中
- union——如果第二个select出现在UNION之后，则被标记为UNION，如果union包含在from子句的子查询中，外层select被标记为derived
- union result:UNION 的结果
table
查询所涉及的表。
partitions
版本5.7以前，该项是explain partitions显示的选项，5.7以后成为了默认选项。该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。
type
- system：表中仅有一行（=系统表）这是const联结类型的一个特例。
- const：表示通过索引一次就找到，const用于比较primary key或者unique索引。因为只匹配一行数据，所以如果将主键置于where列表中，mysql能将该查询转换为一个常量
- eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于唯一索引或者主键扫描
- ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行，可能会找多个符合条件的行，属于查找和扫描的混合体
- range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好，因为它开始于索引的某一个点，而结束另一个点，不用全表扫描
- index：index 与all区别为index类型只遍历索引树。通常比all快，因为索引文件比数据文件小很多。
- all：遍历全表以找到匹配的行
- 从好到坏的顺序一次是：system > const > eq_ref > ref > range > index > all
possible_keys
查询可能使用到的索引都会在这里列出来
key
查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。
key_len
表示索引中使用的字节数，该列计算查询中使用的索引的长度在不损失精度的情况下，长度越短越好。如果键是NULL,则长度为NULL。该字段显示为索引字段的最大可能长度，并非实际使用长度。
ref
显示索引的哪一列被使用了，如果有可能是一个常数，哪些列或常量被用于查询索引列上的值
rows
根据表统计信息以及索引选用情况，大致估算出找到所需的记录所需要读取的行数
filtered
查询条件过滤的行数的百分比
Extra
EXplain 中的很多额外的信息会在 Extra 字段显示, 常见的有以下几种内容
- distinct：在select部分使用了distinct关键字
- Using filesort：当 Extra 中有 Using filesort 时, 表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.
- Using index：“覆盖索引扫描”, 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错
- Using temporary：查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.