mysql性能优化学习笔记-(4)性能分析

转载请备注来源： 《MySQL性能优化学习笔记-(4)性能分析》 | shuwoom.com

一、EXPLAIN命令分析

MySQL提供了一个EXPLAIN命令，它可以对select语句进行性能分析，并输出select执行的详细信息，以供开发人员进行分析优化。

如下，只需在SQL语句前面加上Explain命令即可。

下面，我们来详细介绍Explain命令返回的各个字段的含义。

• id：SELECT查询的标识符，每个SELECT都会自动分配一个唯一的标识符
• select_type：SELECT查询的类型
• table：查询哪个表
• type：这个字段很重要，显示了查询使用了那种类型。
• possible_keys：此次查询种可能选用的索引
• key：此次查询使用到的索引
• key_len：索引长度
• ref：哪个字段或常数与key一起被使用
• rows：显示此查询一共扫描了多少行，这个是一个估计值
• extra：额外的信息

下面，我们来看看比较重要的几个字段含义：

1.select_type

查询的类型，常用取值如下：

• simple：简单子查询，不包含子查询和union
• primary：包含union或者子查询，最外层的部分标记为primary
• subquery：一般子查询中的子查询被标记为subquery，也就是位于select列表中的查询
• derived：派生表——该临时表是从子查询派生出来的，位于form中的子查询
• union：位于union中第二个及其以后的子查询被标记为union，第一个就被标记为primary如果是union位于from中则标记为derived
• union：result：用来从匿名临时表里检索结果的select被标记为union：result
• dependent：union：顾名思义，首先需要满足UNION的条件，及UNION中第二个以及后面的SELECT语句，同时该语句依赖外部的查询
• subquery：子查询中第一个SELECT语句
• dependent subquery：和DEPENDENT：UNION相对UNION一样

2. type

查询类型从最好到最差依次为：cosnt、eq_reg、ref、range、index和ALL。

ALL 类型因为是全表扫描, 因此在相同的查询条件下, 它是速度最慢的.
而 index 类型的查询虽然不是全表扫描, 但是它扫描了所有的索引, 因此比 ALL 类型的稍快.
后面的几种类型都是利用了索引来查询数据, 因此可以过滤部分或大部分数据, 因此查询效率就比较高了

3. key_len

key_len表示mysql使用的建长度。在不损失精确性的情况下，长度越短越好。

4. rows

rows也是一个重要的字段。MySQL查询优化器根据统计信息，估算SQL要查找到结果需要扫描读取的数据行数。这个这段很直观显示了SQL的性能好坏，原则上rows越少越好。

5. extra

extra是EXPLAIN输出中一个很重要的字段，该列显示了MySQL在查询过程中的一些详细信息，MySQL查询器执行查询的过程中对查询计划的重要补充信息。

注意：看到Using filesort和Using temporary就要考虑对查询语句进行优化。

Using filesort	当有Using filesort时，表示MySQL需要额外的排序操作，不能通过索引顺序达到排序效果。一般有using filesort都建议优化去掉，因为这样的查询CPU资源消耗很大。
Using temporary	用临时表保存中间结果，常用于GROUP BY 和 ORDER BY操作中，一般看到它说明查询需要优化了，就算避免不了临时表的使用也要尽量避免硬盘临时表的使用。
Not exists	MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行， 就不再搜索了。
Using index	说明使用了覆盖索引，不需要回表读取数据行。
Using index condition	这是MySQL 5.6出来的新特性，叫做“索引条件推送”。简单说一点就是MySQL原来在索引上是不能执行如like这样的操作的，但是现在可以了，这样减少了不必要的IO操作，但是只能用在二级索引上。
Using where	使用了WHERE从句来限制哪些行将与下一张表匹配或者是返回给用户。
Using join buffer	使用了连接缓存
impossible where	where子句的值总是false，不能用来获取任何元组
select tables optimized away	在没有GROUP BY子句的情况下，基于索引优化MIN/MAX操作，或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
distinct	优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作