深入理解Mysql——性能分析explain

1.explain是什么

2.explain能做什么

3.explain具体使用

1.explain是什么mysql的explain（查看执行计划）可以模拟数据库执行引擎执行sql语句，从而知道mysql是如何处理你的sql语句的，进而分析这个查询语句的瓶颈。

此外，mysql有自己的专门负责优化select语句的优化器模块，它会分析你这个select语句，提供它认为最优的执行计划，比如去掉一些无用而显而易见的条件（1=1）,结构调整（根据索引的顺序调整where后面的顺序）。

2.explain能做什么explain可以列出一个查询的执行计划，显示出以下这些信息1)表的读取顺序2)数据读取的时候的操作类型3)哪些索引可能被使用4)**哪些索引实际被使用5)被使用的索引长度6)表之间的引用7)如果有分区，是哪个分区被使用7)每张表有多少行被优化器查询8)这些被查询的行，最终被查询出的有效数据命中率是多少9)一些额外的排序等信息

3.explain具体使用

3.1如何使用explain

explain 你的查询语句

比如 explain select * from t_customer

执行完毕后会查出很多信息：

我们来挨个解释一下

3.2 idid表示select查询的序号，包含一组数字，表示查询中执行的select语句中操作表的顺序

它会有两种情况：当id相同的时候，执行顺序由上到下：

我们从图中可以看出，这个查询是先执行了t1，再执行t2的查询。

当id不同的时候，id值越大的越先被执行：我们可以看出，这个查询先查询的是t2，然后再查询t1

3.3 select_typeselect_type会有以下的值：

SIMPLE:简单的select查询，查询中不包含子查询或者union

PRIMARY:若查询中包含任何复杂的子部分，最外层的查询则被标记为PRIMARY

SUBQUERY:在select或者where列表中包含了子查询

DEREVED:在from列表中包含的子查询被标记为DEREVED（衍生），mysql会递归执行这些子查询，把结果放到临时表里面。

UNION:若第二个select出现在union之后，则被标记为union，如果union包含在from子句的子查询中，外层select将标记为DEREVED。

UNION RESULT:从union中取结果的select

3.4 tabletable显示这一行的数据是关于哪张表的。

3.5 partitions如果查询时基于分区表的查询，会显示查询将访问的分区。

3.6 type

type极为重要，是较为重要的一个指标，结果值从最好到最坏依次为：system>const>eq_ref>ref>fulltext>ref_or_null>unique_subquery>index_subquery>range>index>All

这么写可能太多，我们可以列举几个常用的：system>const>eq_ref>ref_range>index>all

system:表只有一行记录，直接查询就查到了，这种情况非常稀少。

const:数据示通过索引依次就可以查到，常用于主键或者唯一索引，只用匹配一行数据，如果主键置于where列表中，mysql就能将该查询转换为一个const。

eq_ref:唯一索引扫描，对于每个索引的key，表中只有一行记录可以和一个key匹配，这是除了上面那两种类型以外最好的类型了。

ref:使用到了索引，但是索引不是唯一索引，这个索引key可能会和多条数据匹配，会找到多条符合数据的行。

range：范围扫描，通常出现在 <,>,between,in之类的语句中，用索引来检索一个给定的范围。

index:index类型为遍历所有的索引，找到匹配的数据。

ALL：扫描数据库所有的行，找到匹配的数据。

index和all相比，一个是扫描所有的索引，一个是原表扫描所有的数据，所以index会比all快很多

3.7 possible_key

可能会用到的索引，但是不一定在实际查询中被使用。

3.8 key

实际用到的索引，如果为null就是没用到索引。

3.9 key_len表示索引中使用的字节数，指要查询到这条数据使用的索引长度，长度越短越好。

3.10 ref显示索引哪一列被使用了。

3.11 rows要查出这条数据，大概需要扫描多少行，越少越好。

3.12 filtered表示通过rows筛选出的数据后，符合条件的比例，注意是百分比，不是记录总数。

如：扫描一行就可以得出数据，命中率是百分之一百

3.13 extra包含不适合在其它列中显示，但是却十分重要的信息：

Using filesort（坏）：说明mysql无法使用索引排序，会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。

Using temporary （坏）：使用了临时表的中间结果，mysql在对查询结果进行排序的时候使用了临时表，常见于order by 或者group by .

USING index （好）： 表示select操作中使用了覆盖索引，避免了原表访问。

Using where （好）：使用了where过滤

using join buffer （好） ：使用了链接缓存

impossible where（坏） ：where子句的总值是false，不能拿来使用

select tables optimized away （好）：在没有group by子句的情况下，基于索引优化max/min的操作，不必等到执行阶段再计算。

8.distinct（好）： 优化distinct，找到第一行匹配的值马上停止寻找同样的值。