Hits

Mysql性能优化--Explain详解

EXPLAIN

mysql explain命令是查询性能优化不可缺少的一部分。

explain output columns

列名 说明
id 执行编号,标识select所属的行。如果在语句中没有子查询或者关联查询,只有唯一的select,每行都将显示1。否则,内层的select语句一般会顺序编号,对应于其在原始语句中的位置
select_type 显示本行是简单或是复杂select。如果查询有任何复杂的子查询,则最外层标记为primary(derived、Union、Union resuit)
table 访问引用哪个表(引用某个查询,如“derived3”)
partitions 匹配的分区
type 数据访问/读取操作类型(all、index、range、ref、eq_ref、const/system、NULL)。join类型
possible_keys 揭示哪一些索引可能有利于高效的查找
key 此次查询中确切使用到的索引.
key_len 显示mysql在索引里使用的字节数
ref 哪个字段或常数与key一起被使用
rows 为了找到所需的列而需要读取的行数,估算值,不精确。通过把所有rows列值相乘,可粗略估算整个查询会检查的行数
filtered 表示此查询条件所过滤的数据的百分比
Extra 额外信息,如using index、filesort等

id

id是用来顺序标识整个查询中select语句的,在嵌套查询中id越大的语句越先执行。该值可能为null,说明这一行用来表示其他行的联合查询结果。

select_type

select_type 表示了查询的类型, 它的常用取值有:

  • SIMPLE,表示此查询不包含UNION查询或者子查询
  • PRIMARY,表示此查询是最外层的查询
  • UNION,表示此查询是UNION的第二或随后的查询
  • DEPENDENT UNION, UNION中的第二或后面的查询语句,取决于外面的查询
  • UNION RESULT,UNION的结果
  • SUBQUERY, 子查询中的第一个SELECT
  • DEPENDENT SUBQUERY:子查询中第一个SELECT,取决于外面的查询,即子查询依赖于外层查询的结果。

table

表示查询设计的表或者衍生表(表别名)

  • 关联优化器会为查询选择关联顺序,左侧深度优先
  • 当from中有子查询的时候,表名是derivedN的形式,N指向子查询,也就是explain结果中的下一列
  • 当有Union result的时候,表名是Union 1,2等形式,1,2表示参与Union的query id
  • 注意:mysql对待这些表和普通表一样,但是临时表是没有任何索引的。

type

type字段比较重要,它提供了判断查询是否高效的重要依据,通过type字段我们判断此查询是全表扫描还是索引扫描等。

  • system:表中只有一条数据,这个类型是特殊的const类型
  • const:针对主键或唯一索引的等值查询扫描,最多只返回一行数据,const查询速度非常快,因为它仅仅读取一次即可。
  • eq_ref:此类型通常出现在多表的join查询,表示对于前表的每个结果,都只匹配到后表的一行结果,并且查询的比较操作通常是=,查询效率高。
  • ref:此类型通常出现在多表的join查询,针对于非唯一或非主键索引,或者使用了最左前缀规则索引的查询。
  • range:表示使用索引范围查询,通过索引字段范围获取表中部分数据记录,这个通常出现在: =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。当type=range时,ref字段为null,并且key_len字段是此次查询中使用到的索引的最长的那个。
  • index:表示全索引扫描(full index scan), 和ALL类型类似,只不过ALL类型是全表扫描,而index类型则仅仅扫描所有的索引,而不扫描数据。index通常出现在:所要查询的数据直接在索引树种就可以获取到,而不需要扫描数据,当是这种情况时,extra字段会显示Using index
  • ALL:表示全表扫描,这个类型的查询是性能最差的查询之一,通常来说,我们的查询不应该出现ALL类型的查询,因为这样的查询在数据量大的情况下,对数据库的性能是巨大的灾难,一般可以用索引来避免

type类型的性能比较

ALL < index < range ≈ index_merge < ref < eq_ref < const < system

possible_keys

possible_keys表示mysql在查询时,能够使用到的索引,注意,即使有些索引在possible_keys中出现,但是不表示此索引会真正被Mysql使用到,Mysql在查询时具体使用了哪些索引,由key字段决定。

key

此字段是mysql在当前查询时所真正使用到的索引

key_len

表示查询优化器使用了索引的字节数,这个字段可以评估组合索引是否完全被使用,或只有最左部分字段被使用到。根据数据类型所占字节数计算出来。

  • 字符串
    • char(n): n 字节长度
    • varchar(n): 如果是 utf8 编码, 则是 3 n + 2字节; 如果是 utf8mb4 编码, 则是 4 n + 2 字节.
  • 数值类型:
    • TINYINT: 1字节
    • SMALLINT: 2字节
    • MEDIUMINT: 3字节
    • INT: 4字节
    • BIGINT: 8字节
  • 时间类型
    • DATE: 3字节
    • TIMESTAMP: 4字节
    • DATETIME: 8字节
  • 字段属性: NULL 属性 占用一个字节. 如果一个字段是 NOT NULL 的, 则没有此属性.

rows

rows也是一个重要的字段,mysql查询优化器根据统计信息,估算SQL要查找到结果集需要扫描读取的数据行数。这个值非常直观显示SQL的效率好坏,原则上rows越少越好。

Extra

explain中很多额外的信息都会在extra中显示,常见以下内容:

  • Using filesort,表示mysql需额外的排序操作,不能通过索引顺序达到排序效果,一般有Using filesort,都建议优化去掉,因为这样的查询CPU资源消耗大。
  • Using index,“覆盖索引扫描”,表示查询在索引树中就可以查询到所需数据,不用扫描表数据问题,往往说明性能挺好。
  • Using temporary,查询有使用临时表,一般出现于排序,分组和多表join的情况,查询效率不高,建议优化。

本文链接:参与评论 »

--EOF--

提醒:本文最后更新于 336 天前,文中所描述的信息可能已发生改变,请谨慎使用。

专题「数据库相关知识」的其它文章 »

Comments