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操作,在找到第一匹配的元组后即停止找同样值的动作 |