【mysql】之Explain性能分析

SQL查询瓶颈

1、CPU（SQL中对大量数据进行比较、关联、排序、分组，最大的压力在于比较）

2、IO（服务器内存不能满足缓存数据，需要进行多次物理IO）

3、锁（不适宜锁的设置导致线程阻塞，尤其是线程交叉查询，导致死锁）

4、服务器自身性能（top,free, iostat和vmstat来查看系统的性能状态）

Explain

使用Explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析查询语句或是表结构的性能瓶颈。简单来说就是查看我们的SQL执行计划

主要作用有：

1、表的顺序读取

2、哪些索引可以使用

3、数据读取操作的操作类型

4、哪些索引被实际使用

5、表之间的引用

6、每张表有多少行被优化器查询

执行器主要包含的信息：

说明

id

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

1、id相同，从上往下执行
2、id不同，子查询的id值+1，id值越大优先级越高越先被执行

select_type

查询的类型，主要区别普通查询，联合查询，子查询等复杂查询

1、SIMPLE ,简单的 select 查询,查询中不包含子查询或者UNION

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

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

4、SUBQUERY,在SELECT或WHERE列表中包含了子查询

5、DEPENDENT SUBQUERY,在SELECT或WHERE列表中包含了子查询,子查询基于外层

6、UNCACHEABLE SUBQUREY,无法被缓存的子查询

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

8、UNION RESULT,从UNION表获取结果的SELECT

table

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

partitions

 

type

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：
 
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range(尽量保证) > index > ALL
 
system>const>eq_ref>ref>range>index>ALL
 
一般来说，得保证查询至少达到range级别，最好能达到ref。

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

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

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

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

5、range，只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引
一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

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

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

8、index_merge ，在查询过程中需要多个索引组合使用，通常出现在有 or 的关键字的sql中

9、ref_or_null，对于某个字段既需要关联条件，也需要null值得情况下。查询优化器会选择用ref_or_null连接查询。

10、index_subquery，利用索引来关联子查询，不再全表扫描。

11、unique_subquery，该联接类型类似于index_subquery。 子查询中的唯一索引

possible_keys

显示可能应用在这张表中的索引，一个或多个。
查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

key

实际使用的索引。如果为NULL，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠

key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 key_len字段能够帮你检查是否充分的利用上了索引（尤其在使用联合索引的时候）

列类型utf-8	KEY_LEN	备注
id(int)	key_len=4+1=5	允许为NULL，加1byte
id(int) not null	key_len=4
char(10)	key_len=10*3+1=31	允许为NULL +1
varchar(10)	key_len=10*3+2+1=33	动态列类型，加2byte，允许为NULL，加1
text(10)	key_len=10*3+2+1=33	TEXT列截取部分，视为动态列类型，加2byte，允许为NULL，加1

ref

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

rows

rows列显示MySQL认为它执行查询时必须检查的行数。（越少越好）

filtered

Extra

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

1、Using filesort，mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”

2、Using temporary，使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

3、USING index，表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！
如果同时出现using where，表明索引被用来执行索引键值的查找;
如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。
覆盖索引（例如组合索引id_name_age，select id,name from user where age=10）,覆盖索引适用于数据量大，且查询的是固定字段，可以使用这种方式建立索引，注意如果使用覆盖索引不可直接select * ,只查询出需要的索引列即可

4、Using where，表明使用了where过滤

5、Using join buffer，使用了连接缓存

6、impossible where，where子句的值总是false，不能用来获取任何值，例如
EXPLAIN  select * from t_a  where  (select (1<0))

7、select tables optimized away，在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。这个结果主要存在在myisam引擎（myisam 中会维护 总行数 (还有其他参数)这个参数，所以在执行查询时不会进行全表扫描。而是直接读取这个数。但会对增删产生一定的影响。