MySQL 高级：EXPLAIN 性能分析

目录

MySQL的常见瓶颈

EXPLIAN 概念

是什么（查看执行计划）

能干嘛

怎么用

EXPLAIN中各字段解释

id

select_type

table

type

possible_keys

key

key_len

ref

rows

Extra

---

MySQL的常见瓶颈

CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候。

IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候

服务器硬件的性能瓶颈：简而言之，机器性能本来就差那就没什么好说的了。

EXPLIAN 概念

是什么（查看执行计划）

使用EXPLIAN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句。分析你的查询语句是或是表结构的性能瓶颈。

能干嘛

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

怎么用

Explain + SQL 语句

EXPLAIN中各字段解释

利用本机的一个简单的员工表来展示执行计划的各个字段。

id

SELECT查询的序列号，表示查询中执行SELECT子句或操作表的顺序。

它的值有三种情况

• id相同，执行顺序由上至下。
• id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越被优先执行。
• id相同不同，同时存在。

id相同，执行顺序由上至下：

传统习惯会觉得id是主键，但实际上是查询中执行SELECT表的操作顺序。

当id相同的时候，表名的执行顺序是由上至下，下图的例子，在传统思维中，认为t1第一执行,t2第二执行，t3最后执行。

实际上的执行顺序却是t1，t2，t3。

id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越被优先执行。

上图的SQL例子有了子查询，从select_type中可以看到primary(主查询)，subquery（子查询），这条SQL的执行顺序由T3优先执行，T2第二执行，T1最后执行。

这是MYSQL内部程序自己的理解和执行顺序。

对这种执行顺序的理解，PRIMARY代表最外层查询，SUBQUERY代表内层查询，也就是说PRIMARY最后执行，就好比SUBQUERY像是鸡蛋芯，PRIMARY是鸡蛋白一样，由内至外。

id相同不同，同时存在：

上图的SQL中，括号内的优先执行，也就是t3先执行。对应explian执行顺序的分析，id值越大，优先执行，id2对应的select_type为DERIVED，意味着衍生，从t3查询的内容被包裹起来，成了一张为s1的虚表，是内部衍生出来的。所以为衍生虚表。id2的table对应的derived2对应的其实就是id2的dervied。

id如果相同，可以认为是一组，从上往下顺序执行，在所有组中，id值越大，优先级越高，越先被执行。

这就对应了EXPLAIN能干嘛中的第一条，从EXPLAIN中可以知道表的执行顺序。

select_type

查询的类型主要是用于区别普通查询、联合查询、子查询等的复杂查询。

select_type的类型：

• SIMPLE                        简单的select查询，查询中不包含子查询或者union
• PRIMARY                     查询中若包含任何复杂的子部分，最外层则标记为PRIMARY
• SUBQUERY                 在SELECT或WHERE列表中包含了子查询
• DERIUED                     在FROM列表中包含了只查询被标记为DERIVED(衍生)

                                             MySQL会递归执行这些子查询，把结果放在临时表里。

• UNION                          若第二个SELECT出现在UNION之后，则被标记为UNION

                                              若UNION包含在FROM字句的子查询中，外层的SELECT将被标记为DERIVED

• UNION RESULT           从UNION表获取结果的SELECT

这就对应了EXPLAIN能干嘛中的第二条，数据读取操作的操作类型

table

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

type

访问类型排列

类型如下图所示：

显示查询使用了何种类型，

常见的类型从最好到最差依次是：

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

为什么ALL是最差的？

例如select* type类型为ALL，是全表扫描。

 一般来说，得保证查询至少达到range级别，最好能达到ref。

type类型解释：

• system

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

• const

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

例如下图SQL，括号中优先执行，起了一个d1的别名，为一张衍生虚拟表，它的type类型为const，d1表内其实只有一行数据，因为where条件查询的是id=1,，因此查询出来只会有一条数据。

所以它的type类型为const

不过实际情况中，这种情况也很少。

•  eq_ref

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

这是个什么概念呢？

通过下图SQL来理解。

假设 t1和t2是员工表和部门表，员工表和部门表都有 department_id 这个字段，假设条件 t1.id = t2.id 就是t1.department_id = t2.department_id，这个department_id 作为连接条件，可以加一个索引，加快查询效率。

当有个部门用到了这条部门的查询索引，但只有一条记录，就是eq_ref类型。

就好比一个部门有多个员工，像研发部有多个程序员，但是有可能有个部门（总裁办）就只有一个员工，如CEO，他自然也是在员工表里的。

所以通过EXPLAIN分析这行SQL语句时，优先加载了t2，type类型是All，t1则是eq_ref，也就是通过department_id 作为连接条件，员工表里只查询到了CEO这一条记录，因此是eq_ref。

ref

非唯一性索引扫描，返回匹配某个单独值的所有行。

本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。

在eq_ref中，我们举例了使用department_id为索引的查询情况，总裁办只有总裁一个人，但研发部却有很多个人。此时这些研发部门的员工，查询出来就是ref类型。

上图SQL中，为列1列2创建了复合索引，然后查询了列1等于某个值的情况，这个时候查询出的结果的type类型就是ref。

ref级别的结果是实际上我们写SQL语句最常用到的，它不像上面那几种类型，实际系统中一行记录是很少的。一般我们优化SQL能优化到ref级别已经算很好。

range

只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。一般就是在你的where语句中出现了between，<，>，in等查询

这种范围扫描索引比全表扫描索引要好，因为它只需要开始索引的某一点，而结束于另一点，不用扫描全部索引。

 上图SQL展示了range级别的搜索。

index

full index scan，index与ALL的区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读取的。）

 

 上图SQL查询了id，id是表主键，也是索引，因此type级别为index。

ALL

Full Table Scan ，将遍历全表以找到匹配的行。

备注：

一般来说，得保证查询至少达到range级别，最后能达到ref。

possible_keys

显示可能应用在这张表中的索引，一个或者多个。

查询设计到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用。

key

实际使用的索引，如果为NULL，则没有使用索引。

若查询中使用了覆盖索引，则该索引仅出现在key列表中。

覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。 也可以称之为实现了索引覆盖。 如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。

 以上的例子，为t2创建了col1,col2的复合索引。

通过 explain select col1,col2  from t1，执行计划的type类型为index，说明查询类型是索引扫描，实际上key列上用到了索引inx_col1_col2。

简而言之，prossible_key是MySQL自己的推测，而key是实际上真正落地实现用到的索引。

key_len

表示索引中使用字节数，可通过该列计算查询中使用索引的长度。在不损失精确性的情况下，长度越短越好。

key_len显示的值为索引字段最大可能长度，并非实际使用长度。即key_len是根据定义计算而得，不是通过表内检查出的。

 

上图SQL中，查询col1='ab'，使用到了索引idx_col1_col2，key_len的长度是13。

但加了一个查询条件 col2=‘ac’后，key_len的长度变成了26，因为条件多了一个，付出的代价当然也多了。

也就是说同样的查询结果前提下，精度越小越好。

ref

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

 由key_len可知t1表的idx_co1_col2被充分使用，col1匹配t2表的col1，col2匹配了一个常量，即‘ac’

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。

Extra

包含不适合在其他列中显示但十分重要的额外信息

• Using Filesort（常见，重要） 说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为‘‘文件排序’

看上图例子，从t1查询col1，给予了条件 col1=‘ac’的情况，并根据col3进行了排序。

从key的情况看到，使用了索引idx_col1_col2_col3，也就是说，t1表有个关于col1，col2，col3列的复合索引。

ref指出了依靠了 col1=‘ac’的常量

Extra 指出了 Using where ： 使用了where 查询，Using index 使用了索引查询，Using filesoft，使用了内部文件排序。

也就是说，创建了索引idx_col1_col2_col3，查询时的的确确用到了。

但排序的时候没有用到，因为order by 的条件是col3，而索引是col1_col2_col3。

回顾之前所讲：查询中排序的字段，排序字段若通过索引去访问，将大大提高排序速度。

排序的时候如果按照建立索引的顺序，个数去查，将大大提高效率。

所以在下图SQL中，where条件用了col1，查询条件用到了col2，col3，因此排序用到了复合索引idx_col1_col2_col3。

（根据最左前缀匹配原则：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。）

上图的例子也是违反了最左前缀匹配原则，组合索引不能中断，col3是没有索引的。

 extra出现using filesoft，最好尽快优化。

• Using temporay （常见，重要） 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询group by

 上图的表中复合索引为idx_col1_col2，虽然查询使用到了索引，但是group by col2，因此产生了 using temporary和using filesoft

上图group by col1，和col2 ，和索引顺序，个数吻合，因此extra正常。

这一块老师的举例不明朗，应该跟Btree复合索引查询原理有关系，需要补充完善。

• Using index （常见，重要）表示相应的select操作中使用了覆盖索引，避免了访问表的数据行，效率不错。如果同时出现using where，表名索引被用来执行索引键值的查找。如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。

覆盖索引，一说为索引覆盖。

理解方式：就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，不必根据索引再次读取数据文件，也就是查询列被索引所覆盖。

注意，如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可以select*

因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

•  Using where

表明使用了where过滤

• Using join buffer

使用了连接缓存

• impossible where

where 字句的值总是false，不能用来获取任何元组

• select tables optimized away

（略）

• distinct

通过不同的type类型解释，我们可以理解了EXPLAIN能干嘛中的那几条：哪些索引可以使用，哪些索引被实际使用，表之间的引用。