Mysql优化工具explain

explain的使用很简单，只要在select语句的前面加上explain的关键字就好了，来看个例子

先创建两个表，注意两个表的索引

CREATE TABLE `user` (
  `id` varchar(32) NOT NULL,
  `name` varchar(32) DEFAULT NULL,
  `role` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `user` VALUES ('1', '1', '1');
INSERT INTO `user` VALUES ('10', '10', '5');
INSERT INTO `user` VALUES ('2', '2', '2');
INSERT INTO `user` VALUES ('3', '3', '3');
INSERT INTO `user` VALUES ('4', '4', '4');
INSERT INTO `user` VALUES ('5', '5', '5');
INSERT INTO `user` VALUES ('6', '6', '1');
INSERT INTO `user` VALUES ('7', '7', '2');
INSERT INTO `user` VALUES ('8', '8', '3');
INSERT INTO `user` VALUES ('9', '9', '4');

CREATE TABLE `role` (
  `id` varchar(32) NOT NULL,
  `name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `role` VALUES ('1', '1');
INSERT INTO `role` VALUES ('6', '1');
INSERT INTO `role` VALUES ('2', '2');
INSERT INTO `role` VALUES ('3', '3');
INSERT INTO `role` VALUES ('4', '4');
INSERT INTO `role` VALUES ('5', '5');

试一下explain

1	EXPLAIN SELECT * FROM `user` WHERE id = '1'

来看输出结果,表格太长了，就不以表格的形式显示结果了

           id: 1
 select_type : SIMPLE
        table: user
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      ken_len: 98
          ref: const
         rows: 1
        Extra: NULL

来看看各个字段的含义：

id: select查询的序号。
select_type: 查询的类型
table：查询涉及的表
partitions: 匹配的分区
type：表的连接类型
possible_keys：可能应用到的索引
key：实际被使用的索引列
ken_len：索引中使用的字节数
ref：关联的字段
rows: 此查询一共扫描了多少行
filtered: 表示此查询条件所过滤的数据的百分比
extra：执行情况的额外的说明

有两个字段（partitions，filtered）没有在查询的结果中出现，一般也不需要关注这两个字段，同时所有的字段并不是全部是重要的，这里来详细了解下各个字段。

id

id的值说明了sql执行的先后顺序，可能出现三种情况：

id相同

id相同时，执行的顺序是自上到下依次运行

id不同

比如使用子查询的情况下，会出现id的值递增的情况。id的值越大执行的优先级越高。

id不同和相同的同时存在

可以把相同的理解为一组，相同一组的执行顺序自上到下依次运行，不同组的id的值越大，执行的优先级越高。

select_type

select_type的值主要有以下几种：

SIMPLE

简单的select,不使用UNION或子查询

例子

1	EXPLAIN SELECT * FROM `user` WHERE id = '1'

PRIMARY

最外层查询的select查询

例子

EXPLAIN SELECT * FROM `user`
WHERE role = (
  SELECT id FROM role
  WHERE `name` = '1'
  )

其中user表的类型是PRIMARY

UNION

UNION查询中的第二个语句或后面的语句

例如

1
2
3

EXPLAIN SELECT * FROM `user` WHERE id = '1'
         UNION
         SELECT * FROM `user` WHERE id = '2'

DEPENDENT UNION

UNION查询中的第二个或后面的查询语句, 取决于外面的查询，即子查询依赖于外层查询的结果.

UNION RESULT

UNION查询的结果

例如

1
2
3

EXPLAIN SELECT * FROM `user` WHERE id = '1'
         UNION
         SELECT * FROM `user` WHERE id = '2'

SUBQUERY

子查询中的第一个select

例如

EXPLAIN SELECT * FROM `user`
WHERE role = (
  SELECT id FROM role
  WHERE `name` = '1'
  )

DEPENDENT SUBQUERY

子查询中的第一个select,取决于外面的查询。

table

table表示该次查询涉及到的表名或表的别名。

type

type是一个非常重要的一个字段。根据type字段可以判断查询是否性能高效。

常用的类型有：

system

表示结果集仅有一行，这是const类型的一个特例，一般是在myisam或memory存储引擎中，在innodb存储引擎中为const

const

表示通过主键或者唯一索引查找数据时只匹配了一行数据，const说明查询速度非常快。

例子

1	EXPLAIN SELECT * FROM `user` WHERE id = '1'

eq_ref

多出现在联接查询，表示索引是主键或唯一非 NULL 索引时，对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高,这是最好的联接类型。

例子

1 2	EXPLAIN SELECT * FROM `user`,role WHERE `user`.id = role.`id`

说明：
user.id是唯一索引, 每条user.id可以联接role中的一条数据。

ref

通常出现在多表的联接查询, 针对于非唯一或非主键索引, 或者是使用了最左前缀规则索引的查询.

例子：

1 2	EXPLAIN SELECT * FROM `user`,role WHERE `user`.`name` = role.name

说明：role.name是索引，但并不是唯一索引和主键索引

range

表示使用索引范围查询,这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中。

例子

1 2	EXPLAIN SELECT * FROM `user` WHERE id IN ('1','2','3')

index

表示全索引扫描，扫描所有的索引

例子

1	EXPLAIN SELECT id,`name` FROM `role`

ALL

表示全表扫描，这是最坏的结果。

最好的结果到最差的结果：system > const > eq_ref > ref > range > index > ALL，一般来说，得保证查询至少达到range级别，最好能达到ref。

possible_keys

可能用到的索引。注意：只是可能。即使有些索引在这个字段中出现。也不一定会被真正用到。

key

实际被用到的索引列。注意和possible_keys做区分

ken_len

表示索引中使用的字节数，key_len显示的值为索引字段的最大可能长度，并非实际使用长度。这个当然越短越好。

ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

rows

rows这个也是非常重要的一个字段,估算要扫描的行数，原则上越少越好。注意：这个值并不是完全准确的值，只是估算。

Extra

Extra提供了多个值，这只说几个比较常见的，有兴趣的少侠可以自行百度，谷歌。

Using temporary

查询时需要用额外的临时表来存储结果集，比较常见在group by,order by中。

Using filesort

当包含order by操作，而且无法利用索引完成的排序操作称为“文件排序”，建议优化。

Using join buffer

在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。

Using index

“覆盖索引”,表示查询的数据在索引中就可以找到，说明性能不错。

注意

explain只能解释select
explain不计算各种Cache
部分信息只是估算。