MySql explain 详解

发表于 阅读次数： 本文字数： 3k 阅读时长 ≈ 3 分钟

什么是explain

使用优化器可以模拟优化器执行SQL查询语句，从而知道MySQL怎么处理你的SQL语句的，分析你的查询语句和表结构的性能瓶颈。

explain能够干什么

  • 读取表的顺序

  • 哪些索引能够被使用

  • 数据读取操作的操作类型

  • 哪些索引能够被实际使用

  • 表之间的引用

  • 每张表有多少行被物理查询

如何使用explain

使用而explain很简单就是，在你书写的SQL语句加一个单词 - explain，然后将 explain + SQL执行后会出现一个表，这个表会告诉你MySQL优化器是怎样执行你的SQL的。

就比如执行下面一句语句：

1
EXPLAIN SELECT * FROM student

explain各个字段代表的意思

  • id ：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
  • select_type ：查询类型 或者是 其他操作类型
  • table ：正在访问哪个表
  • partitions ：匹配的分区
  • type ：访问的类型
  • possible_keys ：显示可能应用在这张表中的索引，一个或多个，但不一定实际使用到
  • key ：实际使用到的索引，如果为NULL，则没有使用索引
  • key_len ：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
  • ref ：显示索引的哪一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值
  • rows ：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需读取的行数
  • filtered ：查询的表行占表的百分比
  • Extra ：包含不适合在其它列中显示但十分重要的额外信息

上面介绍了每个字段的意思，可以大体看一下，下面会逐一介绍每个字段表示的啥？该关注什么？

id与table字段

为什么要将id和table放在一起讲呢？因为通过这两个字段可以完全判断出你的每一条SQL语句的执行顺序和表的查询顺序。

先看id后看table，id和table在SQL执行判断过程中的关系就像是足球联赛的积分榜，首先一个联赛的球队排名应该先看积分，积分越高的球队排名越靠前，当两支或多只球队的积分一样高怎么办呢？那我们就看净胜球，净胜球越多的球队，排在前面。而在explain中你可以把id看作是球队积分，table当作是净胜球。

select_type字段

  • SIMPLE 简单查询，不包括子查询和union查询

  • PRIMARY 当存在子查询时，最外面的查询被标记为主查询

  • SUBQUERY 子查询

  • UNION 当一个查询在UNION关键字之后就会出现UNION

  • UNION RESULT 连接几个表查询后的结果

  • DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL
    会递归执行这些子查询，把结果放在临时表中
    MySQL5.7+ 进行优化了，增加了derived_merge（派生合并），默认开启，可加快查询效率

partitions字段

该列显示的为分区表命中的分区情况。非分区表该字段为空（null）。

type字段

首先说一下这个字段，要记住以下10个状态，（从左往右，越靠左边的越优秀）

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

NULL MySQL能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引

有没有这样一种疑惑，不查询索引也不查询表那你的数据是从哪里来的啊？谁说SELECT语句必须查询某样东西了？

  • SYSTEM 表只有一行记录（等于系统表），这是const类型的特列，平时不大会出现，可以忽略。

  • const 表示通过索引一次就找到了，const用于比较primary key或uique索引，因为只匹配一行数据，所以很快，如主键置于where列表中，MySQL就能将该查询转换为一个常量。简单来说，const是直接按主键或唯一键读取。

  • eq_ref 用于联表查询的情况，按联表的主键或唯一键联合查询。

    多表join时，对于来自前面表的每一行，在当前表中只能找到一行。这可能是除了system和const之外最好的类型。当主键或唯一非NULL索引的所有字段都被用作join联接时会使用此类型。

  • ref 可以用于单表扫描或者连接。如果是连接的话，驱动表的一条记录能够在被驱动表中通过非唯一（主键）属性所在索引中匹配多行数据，或者是在单表查询的时候通过非唯一（主键）属性所在索引中查到一行数据

  • ref_or_null 类似ref，但是可以搜索值为NULL的行

  • index_merge 表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取多个索引，性能可能大部分时间都不如range。

  • range 索引范围查询，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。

  • index index只遍历索引树，通常比All快。因为，索引文件通常比数据文件小，也就是虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘读的。

  • ALL 如果一个查询的type是All,并且表的数据量很大，那么请解决它！！！

possible_keys字段

这个表里面存在且可能会被使用的索引，可能会在这个字段下面出现，但是一般都以key为准。

key字段

实际使用的索引，如果为null,则没有使用索引，否则会显示你使用了哪些索引，查询中若使用了覆盖索引（查询的列刚好是索引），则该索引仅出现在key列表。

ref字段

显示哪些列被使用了，如果可能的话，最好是一个常数。哪些列或常量被用于查找索引列上的值。

rows字段和Filter字段

rows是根据表的统计信息和索引的选用情况，优化器大概帮你估算出你执行这行函数所需要查询的行数。

Filter是查询的行数与总行数的比值。其实作用与rows差不多，都是数值越小，效率越高。

Extra字段

这一字段包含不适合在其他列显示，但是也非常重要的额外信息。

  • Using filesort 表示当SQL中有一个地方需要对一些数据进行排序的时候，优化器找不到能够使用的索引，所以只能使用外部的索引排序，外部排序就不断的在磁盘和内存中交换数据，这样就摆脱不了很多次磁盘IO，以至于SQL执行的效率很低。反之呢？由于索引的底层是B+Tree实现的，他的叶子节点本来就是有序的，这样的查询能不爽吗？

  • Using tempporary 表示在对MySQL查询结果进行排序时，使用了临时表,,这样的查询效率是比外部排序更低的，常见于order by和group by。

  • Using index 表示使用了索引，很优秀👍。

  • Using where 使用了where但是好像没啥用。

  • Using join buffer 表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join buffer调大一些。

  • impossible where 筛选条件没能筛选出任何东西

  • distinct 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作