MySQL高级——性能分析之EXPLAIN

1、MySQL常见瓶颈

CPU和磁盘通常是数据库中的瓶颈。CPU饱和一般发生在数据装入内存或从磁盘上读取数据时。磁盘I/O瓶颈发生在装入数据远大于内存容量的时候。服务器硬件瓶颈可以通过top,free,iostat和vmstat来查看系统的性能状态，如果是硬件原因让老板加钱买新设备；

除了硬件瓶颈，表结构或者SQL优化不到位也可能照成数据库效率低，直接的表现就算明明很简单的SQL语句却耗费了数据库大量性能。

2、EXPLAIN简介

官网介绍：使用EXPLAIN可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。

简单来说Explain就是查看SQL语句执行效果的，以便程序员找出SQL语句或者表设计的不足之处，从而对数据库性能调优。注意：EXPALIN 只能解释 SELECT 操作，其他操作要重写为 SELECT 后查看执行计划。

Explain的两个变种：

explain extended：会在explain 的基础上额外提供一些查询优化的信息。紧随其后通过showwarnings命令可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有filtered列，是一个半分比的值，rows * filtered/100 可以估算出将要和explain中前一个表进行连接的行数（前一个表指explain中的id值比当前表id值小的表）。

explain partitions：相比explain多了个partitions字段，如果查询是基于分区表的话，会显示查询将访问的分区。

使用方法：

在需要调优的SQL语句前加上EXPLAIN。如下图：

可以从图中看出使用效果是展示一张有很多个字段的表，这些个字段是什么意思呢？

结果分析：

使用Explain查询出的结果是SQL语句执行计划信息，下面将挨个介绍这些字段的含义；

（1）id

id列是查询的编号，有几个id就有几次查询。相同的id表示是同一个大查询，此时执行顺序由上往下。不同的id表示不同的大查询，如果是子查询，查询顺序是id从大到小。

在一次查询中可能包含多次大查询，例如子查询、UNION也是一次大查询，大查询次数越少越好。同样id的代表同一个大查询里的小查询，例如关联查询、连接查询。

（2）select_type

此列表示查询的类型，有以下几种：

• SIMPLE：简单的select查询，查询中不包含子查询或UNION

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

• SUBQUERY：在SELECT或WHERE列表中包含了子查询

• DERIVED：在FROM列表中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，把结果放在临时表里。

• UNION：若第二个SELECT出现在UNION之后，则被标记为UNION。若UNION包含在FROM子句的子查询中外层SELECT将被标记为DERIVED。

• UNION RESULT：从UNION表获取结果。有UNION语句时通常最后一步是将UNION左右两边合并，合并后的临时表就是UNION表。

（3）table

此列表示本条查询读取的是哪张表，根据id顺序就可以分析出表的读取顺序。

（4）type

此列表示访问类型，常见有以下几种类型：

从最好到最差分别是：system > const > eq_ref > ref > range > index > ALL

不是所有情况都需要优化访问类型，比如数据量极小情况下访问类型无所谓。一般最少达到range最好达到ref。具体怎么优化看了下面的每个类型意思就懂了：

• system：表只有一行记录(等于系统表)，这是const类型的特列，平时不会出现；

• const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为一个常量；

• eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描；

• ref：非唯一性索引扫描，返回所有匹配的行。它可能会找到多个符合条件的行，所以他属于查找和扫描的混合体；

• range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。一般是使用范围搜索，比全表扫描要好，因为它只需要扫描索引中某个范围，不用扫描全部索引；

• index：全表扫描，但是因为是扫描索引树所以比ALL要快；

• ALL：遍历全表以找到匹配的行；

（5）possible_keys和key

这两个字段表示是否用到索引和用到哪个索引

possible_keys：显示这次查询可能用到的索引，一个或多个。也就是查询中涉及到的字段，如果有索引就会在这里列出。但不一定会被使用。

key：此次查询实际使用到的索引。

（6）key_len

表示索引使用的字节数。该列显示的是索引字段可能的最大长度，并非实际长度。可通过该列计算查询使用的索引长度，在不损失精度情况下索引长度越短越好。

例如：索引中有两个字段，第一个最大长度2第二个最大长度5。这次查询使用了这两个字段，此列显示的就是7。如果只使用了第一个字段，那么此列显示的是2。

另外需要注意，虽然索引主要功能是查找和排序，但key_len中只会显示用于搜索的索引长度，不会显示用于排序的索引长度。

（7）ref

在索引被使用时，此列显示索引匹配的是哪些列或者常量。

t2表全表扫描没有使用索引，所以ref为null。
t1表使用了联合索引(idx_col1_col2)，分别匹配t2.col1和'ac'，所以ref为t2.col1，const(常量)。

（8）rows

大致估算出找到所需数据需要读取的行数，根据表统计信息和索引选用情况。不影响查询的情况下此字段越少越好。

（9）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：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

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

拓展：覆盖索引

复合索引里包括的字段刚好是select查找时需要找的字段，此时直接从索引中就可以获取数据，不必读取数据行，大大的提高了性能。

案例分析

根据以下结果进行分析

分析结果如下

3、总结

EXPLAIN是一个很好用的工具，它可以帮助程序员分析SQL运行的细节，如：表的读取顺序、数据读取操作的操作类型、哪些索引可以使用、哪些索引被实际使用、表之间的引用、每张表有多少行被优化器查询。程序员根据自己的经验再对SQL或者表结构进行优化，从而让数据库达到最优性能。