摘要

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

Explain详解

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

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

表的读取顺序
哪些索引被实际使用到
数据读取操作的操作类型
表有大约有多少行被优化器查询
...

执行Explain，可以得到如下的内容：

展示内容包含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的表衍生出来的。

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

MySQL explain详解和索引优化

摘要