explain 让你的 sql 写的更踏实

小雷  |  2019. 06. 23   |  阅读 579 次
MySQL Node.js 服务端 数据

本文主要通过一些实例介绍如何使用 mysql 中的 explain 关键字分析查询语句,好让我们的查询语句写的更踏实,也让我们养成用 explain 分析的习惯,养成查询语句的写法习惯。

概念介绍

在 mysql 里,explain 是执行计划的意思,即可以通过该命令看出 mysql 是如何执行该条 sql 的,可以通过分析索引,表结构等方面来优化你的慢查询语句。

mysql 使用 explain + sql 语句 来查看执行计划,执行结果有十个字段,具体描述如下:

字段 描述

id

id相同,执行顺序由上至下;id不同,id的序号会递增,id值越大优先级越高,越先被执行

select_type

主要是用于区别普通查询、联合查询、子查询等的复杂查询

table

当前执行的表

type

访问类型

possible_keys

可能使用的索引

key

实际使用的索引

key_len

使用的索引的长度

ref

显示索引的哪一列被使用了

rows

查询过程中可能扫描的行数

Extra

解析查询的额外信息,通常会显示是否使用了索引,是否需要排序,是否会用到临时表等

其中 type 字段可选值如下,列表从上到下性能从低到高排列:

type 说明

ALL

全数据表扫描

index

全索引表扫描

RANGE

对索引列进行范围查找

INDEX_MERGE

合并索引,使用多个单列索引搜索

REF

根据索引查找一个或多个值

EQ_REF

搜索时使用primary key 或 unique类型

CONST

常量,表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次

SYSTEM

系统,表仅有一行(=系统表)。这是const联接类型的一个特例

OK,概念匆匆介绍之后,结合自己的分析习惯,下面会通过实例聚焦 typekeyrowsExtra 这几个字段,来介绍如何分析我们的查询语句。

实例分析

数据初始化

新建测试表,插入 10w 数据:

CREATE TABLE `test` (  
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

-- 批量插入 10w 数据
-- DROP PROCEDURE IF EXISTS batchInsert
DELIMITER $  
CREATE PROCEDURE batchInsert () BEGIN DECLARE i INT DEFAULT 1;  
START TRANSACTION; WHILE i<=100000  
DO  
INSERT INTO test (a,b) VALUES (i,i);  
SET i=i+1; END WHILE;  
COMMIT; END $  
CALL batchInsert ();  

全表查询

目前默认只有一个主键索引,我们分析下全表查询:

mysql> explain select * from test;  
id select_type table type possible_keys key key_len ref rows Extra

1

SIMPLE

test

ALL

NULL

NULL

NULL

NULL

100333

NULL

其中 type 值为 ALL,表示全表扫描了,我们看到 rows 这个字段显示有 100332 条,实际上我们一共才 10w 条数据,说明这个字段只是 mysql 的一个预估,不总是准确的。这个 test 表一次真实的查询时间为:2.708000s,可见这种全表扫描的效率非常低,是需要被优化的。

索引查询

接下来我们分别给字段 a 和 b 添加普通索引。

mysql> alter table test add index idx_a(a);  
mysql> alter table test add index idx_b(b);  

看下下面这条 sql:

mysql> explain select * from test where a > 10000;  
id select_type table type possible_keys key key_len ref rows Extra

1

SIMPLE

test

ALL

idx_a

NULL

NULL

NULL

100333

Using where

我们发现 type 竟然不是 index, 刚刚不是给字段 a 添加索引了么?还有 possible_keys 也显示了有 a_index,但是 key 显示 null,表示实际上不会使用任何索引,这是为什么呢?

这是因为 select * 的话还需要回到主键索引上查找 b 字段,这个过程叫回表

这条语句会从索引中查出 9w 条数据,也就是说这 9w 条数据都需要回表操作,全表扫描都才 10w 条数据,所以在 mysql 最后的决策是还不如直接全表扫描得了,至少还免去了回表过程了。

当然,最后决策是否用索引不是固定的,mysql 会比较各种查询的代价,我们把上面的 sql 中 where 条件再稍微改造一下。

mysql> explain select * from test where a > 90000;  
id select_type table type possible_keys key key_len ref rows Extra

1

SIMPLE

test

range

idx_a

idx_a

4

NULL

9999

Using index condition

再看这次 type 为 range 了,key 为 a_index,表示使用了 a 索引,如我们所愿了。这是因为满足这次索引中查出只有 10000 条数据,mysql 认为 10000 条数据就算回表也要比全表扫描的代价低,因而决定查索引。

上面两条查询说明 mysql 会比较 索引 + 回表直接全表扫描的查询性能,选择其中更好的作为最后的查询方式,这就是 mysql 优化器的作用了。

还有一点就是这次 Extra 字段中值为 Using index condition,这是指条件过滤的时候用到了索引,但因为是 select * ,所以还是需要回表,再看下面这个语句。

mysql> explain select a from test where a > 90000;  
id select_type table type possible_keys key key_len ref rows Extra

1

SIMPLE

test

range

idx_a

idx_a

4

NULL

9999

Using where; Using index

注意这次 Extra 的值为 Using where; Using index,表示查询用到了索引,且要查询的字段在索引中就能拿到,所以不需要回表,显然这种效率比上面的要高,这也是日常开发中不建议写 select * 的原因,尽量只查询业务所需的字段。

排序查询

再来看一个带排序的查询。

mysql> explain select a from test where a > 90000 order by b;  
id select_type table type possible_keys key key_len ref rows Extra

1

SIMPLE

test

range

idx_a

idx_a

4

NULL

9999

Using index condition; Using filesort

我们知道索引本来就是有序带,但这个 Extra 中返回了一个 Using filesort,说明无法利用索引完成排序,需要从内存或磁盘进行排序,具体哪种排序 explain 是没有体现的。 总之,这种情况也是需要优化的,尽量能利用索引的有序性,比如下面:

mysql> explain select a from test where a > 90000 order by a;

id select_type table type possible_keys key key_len ref rows Extra

1

SIMPLE

test

range

idx_a

idx_a

4

NULL

9999

Using where; Using index

这次 Extra 值有 Using index 了,表示使用上了索引。

复合索引

我们再创建一个复合索引看看。

mysql> alter table test add index idx_a_b(a,b);  

看下之前的查询 mysql> explain select * from test where a > 10000;

id select_type table type possible_keys key key_len ref rows Extra

1

SIMPLE

t

range

idxa,idxa_b

idxab

4

NULL

50166

Using where; Using index

这条 sql 刚刚在没有创建复合索引的时候,是走的全表扫描,现在看 Extra 有 Using index,说明利用了覆盖索引,同样也免去了回表过程,即在 idxab 索引上就能找出要查询的字段。

总结

本文通过几个实例介绍了如何使用 explain 来分析一条 sql 的查询计划,例子都很简单,旨在能通俗易懂的说明白一些常见的查询问题,也让我们能养成良好的查询习惯。

参考

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain_rows
https://blog.csdn.net/poxiaonie/article/details/77757471
https://www.cnblogs.com/tufujie/p/9413852.html
https://www.xttblog.com/?p=4225

分享到

   
Node 集群源码初探
加入我们