explain的使用
性能分析
Mysql query optimizer
mysql中有专门复制优化select语句的优化模块。主要功能是同各国计算分析系统中收集到的统计信息,为客户端请求的query提供它认为最优的执行计划。
Mysql常见瓶颈
- Cpu:cpu在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
- IO:磁盘IO瓶颈发生在装入数据远大于内存容量的时候
- 服务器硬件的性能瓶颈:top,free,iostat,vmstat来查看系统的性能状态
查看执行计划Explain
介绍
可以模拟优化器执行SQL查询语句,从而指导MYSQL是如何处理你的sql语句的。分析查询语句或是表结构的性能瓶颈。
语法
Explain SQL语句; #执行计划包含的信息
各字段解释
Id
Select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序
三种情况
-
Id相同,执行顺序由上至下
-
id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
先执行子查询,所以t3的id最大
-
Id相同/不同,同时存在
先子查询,因此t3的id为2;
之后同级的id都为1,顺序执行,先是子查询构造的虚表s1,然后是t2
Select_type
查询的类型,主要用于区别不同的查询。
分类
- Simple:简单的select查询,查询中不包含子查询或者union
- Primary:查询中若包含任何复杂的子部分,则最外层查询被标记为primary
- Subquery:在select或where列表中包含了子查询
- Derived:在from列表中包含的子查询被标记为derived,mysql会递归执行这些子查询,把结果放在临时表里
- Union:若第二个select出现在union之后,则被标记为union,如果union包含在from子句的子查询中,外层select将被标记为derived
- Union result:从union表获取结果的select
Table
显示这一行的数据是关于哪张表的
Type
显示查询使用了哪种类型
- System:表中只有一行记录(等于系统表),这是const类型的特例
- Const:通过索引一次就找到了,const用于比较主键或者唯一索引。因为只匹配一行数据,所以很快,如果将主键置于where列表中,mysql就能将该查询转换为一个常量
- Eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描
- Ref:非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独数值的行,然而,它可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体。
- Range:只检索给定范围的行,使用一个索引来选择行,key列显示使用了哪个索引。一般就是在where语句中出现了between/>/</in等的查询,这个范围扫描索引扫描比全表扫描好,属于查找和扫描的混合体。
- Index:full index scan,index和all的区别为index只遍历索引树,通常比all,因为索引文件通常比数据文件小。
- All:遍历全表以找到匹配的行。
从最好到最差:
System>const>eq_ref>ref>range>index>all
一般来说,要保证查询至少达到range级别,最好能达到ref
Possible_keys
显示可能应用在这张表中的索引,一个或多个
查询涉及到的字段,若存在索引,则该索引将被列出,但不一定被查询实际使用。
Key
实际使用的索引,如果为null,则没有使用索引
查询中如果使用了覆盖索引,则该索引仅出现在key列表中。
覆盖索引
Covering index
Select的数据列只用从索引中就能取得,不必读取数据行,mysql可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件。即查询列被索引覆盖
如果要使用覆盖索引,一定要注意select只取需要的列,不能select * 。 如果将所有字段一起做索引会导致索引文件过大导致查询性能下降。
(自己的理解:查询要啥这个索引有啥,不用问别人要,不求人)
Key_len
表示索引中使用的字节数,可以通过该列计算查询中使用的索引长度。在不损失准确性的情况下,长度越短越好.
Key_len显示的值是索引字段的最大可能长度,并非实际使用长度。是根据表定义计算得出,不是通过表内索引得出的。
Ref
显示索引的哪一列被使用了,如果可能的话,是一个常数const,哪些列或常量被用于查找索引列上的值。
可知,t1匹配了一个常量’ ’,t2匹配了ti.id,t3匹配了t1.id
Rows
根据表统计信息和索引选用情况,大概估算出找到所需的记录需要读取的行数。
没有建立索引时是640+1,建立索引后是142+1。
Extra
包含不适合在其他列中显示但十分重要的额外信息
-
Using filesort× 尽快优化!
说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,mysql中无法利用索引完成的顺序成为“文件排序”
前者排序时没有用到索引,只能自己排序,所以using filesort
后者排序时充分使用了索引,所以没有using filesort -
Using temporary ×
使用了临时表保存中间结果,MYSQL在对查询结果排序时使用了临时表,常见于排序和分组查询
对于col1_col2的索引,
前者分组排序group by col2,没有用上索引,只能自己再新建一个表进行分组
后者充分用上了索引。 -
Using index √
说明查询语句用上了索引。
-
Using where
表名使用了where过滤
-
其他
-
Using join buffer
使用了连接缓存
-
Impossible where
Where 子句的值总是false,不能用来获取任何元组。
-
Select tables optimized away
在没有groupby子句的情况下,基于索引优化min、max操作
或对于myisam存储优化count(*)操作。 -
Distinct
优化distinct 操作,在找到第一匹配的元组后即停止找相同值的动作。
-