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的情况,查询效率不高,建议优化。

MySQL 字符串字段转换 crc32 建索引提高查询效率

给字符串类型的字段建立索引效率不高,但是必须要经常查这个字段怎么建索引?比如这个字段名称是 sys_trans_id 字符串类型,那么可以建一个字段 sys_trans_id_src32 来存储 crc32 的值,并给这个字段建立索引。

crc32 是整形,在MySQL中,给整形字段建立索引效率比较高,crc32虽然不能确保唯一性,但是无碍,相同的机率也是极小,关键是可以大大减少查询的范围,给sys_trans_id_src32 这个字段建立索引,查询的时候带上 crc32 字段就可以利用到索引。

继续阅读“MySQL 字符串字段转换 crc32 建索引提高查询效率”

mysql数据库建立索引的好处和代价

说起提高数据库性能,索引是最物美价廉的东西了。不用加内存,不用改程序,不用调sql,只要执行个正确的’create index’,查询速度就可能提高百倍千倍,这可真有诱惑力。可是天下没有免费的午餐,查询速度的提高是以插入、更新、删除的速度为代价的,这些写操作,增加了大量的I/O。由于索引的存储结构不同于表的存储,一个表的索引所占空间比数据所占空间还大的情况经常发生。

这意味着我们在写数据库的时候做了很多额外的工作,而这个工作只是为了提高读的效率。因此,我们建立一个索引,必须保证这个索引不会“亏本”。

继续阅读“mysql数据库建立索引的好处和代价”

MYSQL中myisam和innodb引擎的区别

MyISAM 是MySQL中默认的存储引擎,一般来说不是有太多人关心这个东西。决定使用什么样的存储引擎是一个很tricky的事情,但是还是值我们去研究一下,这里的文章只考虑 MyISAM 和InnoDB这两个,因为这两个是最常见的。

讲讲你对mysql myisam和innodb的认识。然后你认为他们的区别在那里?为什么?

继续阅读“MYSQL中myisam和innodb引擎的区别”