MySQL explain详解和索引优化

作者:青山常在人不老   阅读 (779)  |  收藏 (0)  |  点赞 (0)

摘要

详细讲解了explain中的各列内容,以及如何进行优化


原文链接:MySQL explain详解和索引优化

Explain详解

在Mysql运用过程中,我们经常会发现自己写的SQL查询起来似乎很慢,几十万条数据就要响应很久,此时就需要对SQL进优化。而优化的查询SQL的一种重要的手段就是索引优化,通过explain执行计划可以对索引进行优化。

通过EXPLAIN,我们可以分析出以下结果

  1. 表的读取顺序

  2. 哪些索引被实际使用到

  3. 数据读取操作的操作类型

  4. 表有大约有多少行被优化器查询

  5. ...

执行Explain,可以得到如下的内容:

image.png

展示内容包含id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra。接下来我们从左往右依次说明它们的作用。

1、id

--id相同,执行顺序由上而下

--id不同,值越大越先被执行

2、seelct_type

select_type 总共有以下几种类型:

  • SIMPLE:表示此查询不包含 UNION 查询或子查询

  • PRIMARY:表示此查询是最外层的查询

  • SUBQUERY:子查询中的第一个 SELECT

  • UNION:表示此查询是 UNION 的第二或随后的查询

  • DEPENDENT UNION:UNION 中的第二个或后面的查询语句, 取决于外面的查询

  • UNION RESULT, UNION 的结果

  • DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果.

  • DERIVED:衍生,表示导出表的SELECT(FROM子句的子查询)

3、table

table表示查询涉及的表或者由于表查询衍生的虚拟表:

例如下面id为1的<derived2>的表示id为2的表衍生出来的。

640.png

4、partitions

版本5.7以前,该项是explain partitions显示的选项,5.7以后成为了默认选项。该列显示的为分区表命中的分区情况。非分区表该字段为空(null)

5、type

type字段的意义很重要,是直观的判断查询是否高效的最直接的重要依据。通过type字段的展示内容,我们可以知道当前SQL是如何查扫描查询的。

type有如下的取值:

system: 表中只有一条数据, 这个类型是特殊的 const 类型。

const: 针对主键或唯一索引的等值查询扫描,最多只返回一行数据。const 查询速度非常快, 因为它仅仅读取一次即可。

eq_ref: 此类型通常出现在多表的 join 查询,表示对于前表的每一个结果,都只能匹配到后表的一行结果。并且查询的比较操作通常是 =,查询效率较高。

ref: 此类型通常出现在多表的 join 查询,针对于非唯一或非主键索引,或是使用了 最左前缀 规则索引的查询。

range: 表示使用索引范围查询,通过索引字段范围获取表中部分数据记录。这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。

index: 表示全索引扫描(full index scan),和 ALL 类型相比,ALL 类型是全表扫描,而 index 类型则仅仅扫描所有的索引, 而不扫描数据。index 类型通常出现在:所要查询的数据直接在索引树中就可以获取到, 而不需要回表扫描其他数据。当为这种情况时,Extra 字段会显示 Using index。

ALL: 表示全表扫描,这个类型的查询是性能最差的查询之一。通常来说, 我们的查询不应该出现 ALL 类型的查询,因为这样的查询在数据量大的情况下,对数据库的性能是巨大的灾难。如一个查询是 ALL 类型查询, 那么一般来说可以对相应的字段添加索引来避免。

上面的集中类型,从最优到最差为如下的级别:

system > const > eq_ref > ref > range > index > ALL

通常我们要求保证查询达到range级别,最好达到ref级别。

6、possible_key

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

7、key

此字段是 mysql 在当前查询时真正用到的索引。比如组织一个活动的场景,possible_keys是应到多少人,key是实到多少人。

8、key_len

展示查询的时候优化器使用了索引的字节数,该字段可以判断组合索引是否完全被使用,

key_len计算规则如下:

字符串:

char(n)和varchar(n),5.0.3以后版本中,n均代表字符数,而不是字节数,如果是utf-8,一个数字

或字母占1个字节,一个汉字占3个字节

char(n):如果存汉字长度就是 3n 字节

varchar(n):如果存汉字则长度是 3n + 2 字节,加的2字节用来存储字符串长度,因为varchar是变长字符串

数值类型

  • tinyint:1字节

  • smallint:2字节

  • int:4字节

  • bigint:8字节

时间类型

  • date:3字节

  • timestamp:4字节

  • datetime:8字节

如果字段允许为 NULL,需要1字节记录是否为 NULL

索引最大长度是768字节,当字符串过长时,mysql会做一个类似左前缀索引的处理,将前半部分的字符提取出来做引。

9、ref

这一列显示了在key列记录的索引中,表查找值所用到的列或常量,常见的有:const(常量),func,NULL,字段名(例:film.id)。前文的type属性里也有ref,注意区别。

10、rows

rows 也是一个重要的字段,mysql 查询优化器根据统计信息,估算 sql 要查找到结果集需要扫描读取的数据行数,这个值非常直观的显示 sql 效率好坏, 原则上 rows 越少越好。可以对比key中的例子,一个没建立索引前,rows是9,建立索引后,rows是4。

11、Extra

explain 中的很多额外的信息会在 extra 字段显示, 常见的有以下几种内容:

using filesort :表示 mysql 需额外的排序操作,不能通过索引顺序达到排序效果。一般有 using filesort都建议优化去掉,因为这样的查询 cpu 资源消耗大。

using index:索引覆盖扫描,表示查询在索引树中就可查找所需数据,不用扫描表数据文件,往往说明性能不错。

using temporary:查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高,建议优化。using where :表名使用了where过滤。

优化建议

索引能让我们的查询性能和效率变高,但它本质上仍旧是以文件形式存在的,SQL在查询数据的时候需要读取文件来达到高性能的目的,因此所以并非越多越好,合适数量的索引能在增加查询性能的同时,不过多的增加系统的压力。

由于联合索引和主键索引的差异(联合索引叶子节点指向的是该条数据的磁盘位置,联合索引指向主键的值),因此如果通过联合索引查询列最好只是主键,尽量保证覆盖索引。

分类   数据库设计
字数   2920

博客标签    MySql Explain  

评论