更多优质内容
请关注公众号
请关注公众号
explain是mysql的执行计划查看工具，用于分析一个查询语句的性能
其三种格式如下：
explain select ...
explain extended select ... # 该命令将执行计划反编译成select语句,运行 show warnings 可以得到被mysql优化后的查询语句
explain partitions select... # 该命令用于分区表的explain命令
explain 命令的输出有以下字段
id| select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
下面详细叙述explain输出的各项内容:
id
表示查询中执行select子句或操作表的顺序
id为几就代表是第几个select
如果id相同,则执行的循序由上到下,例如
mysql> explain select t.id,t.name,count(*) as arts_count from arts a join type t on a.tid=t.id where is_send=0 group by t.id\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t
partitions: NULL
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 9
filtered: 100.00
Extra: Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: ref
possible_keys: tid
key: tid
key_len: 5
ref: art.t.id
rows: 978
filtered: 10.00
Extra: Using where
2 rows in set, 1 warning (0.00 sec)
他们的id相同,所以执行顺序是第一行中的表再是第二行中的表
如果id不同,id大的优先级高,先被执行
select_type
表示每个查询的类型,有一下几种类型:
simple,primary,subquery,derived,union unoim result
simple : 查询中不包括子查询或union查询的类型,简单类型
primary: 查询中若包含任何复杂的子部分,则最外层查询被标记为primary
subquery:select或where后包含了子查询,则标记为subquery
derived: from后包含了子查询则标记为derived
从 union 表中获取结果的select标记为 union result
第二个select出现在union之后被标记为union ,也就是union之后的select是Union类型,但union之前的那个select不是union而是primary,整个union语句是一个union result
如 :
select * from a union select * from b #这也是最常规的 union 联合查询
若union包含在from子句的子查询中,则外层select被标记为derived
例如:
explain select id,title from arts where id<1500 union select id,title from arts where id>70000 \G;
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: arts
partitions: NULL
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 394
filtered: 100.00
Extra: Using index condition
*************************** 2. row ***************************
id: 2
select_type: UNION
table: arts
partitions: NULL
type: range
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 2612
filtered: 100.00
Extra: Using index condition
*************************** 3. row ***************************
id: NULL
select_type: UNION RESULT
table: <union1,2>
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: Using temporary
3 rows in set, 1 warning (0.00 sec)
又例如:
explain select d1.name,(select id from t3) d2 from (select id,name from t1 where other_column="") d1 union (select name,id from t2);
得到结果如下
id select_type table
1 primary <derived3>
3 derived t1
2 subquery t3
4 union t2
null union result <union1,4>
第一行:id为1,表示它是第一个select,select_type为primary表示该查询是一个外层查询,table列是<drived3>表示查询结果来自于一个衍生表,其中3代表这个该查询衍生来自第3个select,即id为3的select
第二行:id为3,是第三个select,因为查询语句是在from之中,所以是derived
第三行:id为2,第二个select是作为子查询
第四行:id为4,查看第四个select,在union关键字后面,它是union类型且它是最先执行的
第五行:id为null 表示它是一个临时表,没有select,它代表的是整个语句,table列的<union1,4>表示它是对第一个和第四个select的结果的union操作
type
在表中找到所需行的方式,又叫访问方式:
有如下类型:
all/index/range/ref/eq_ref/const,system/null
all 全表扫描,当不使用where条件进行查询或者使用where但是作为条件的字段不是索引字段则为全表扫描
index 查询的字段只有主键,而且是获取所有行的主键的情况,不能有where,如 select id from t1;
range 索引范围扫描,where条件是索引而且是范围搜索的情况,如 select * from t1 where id>10; 还有in,between and ;如果搜索条件不是索引为条件,就会变成全表扫描ref 非唯一性索引扫描 即使用普通索引或者唯一索引的非唯一前缀作为where条件搜索
例如:
# 我设置了一个文章表的tid字段(分类id)为普通索引
explain select * from arts where tid=6;
+----+-------------+-------+------------+------+---------------+------+---------
+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len
| ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------
+-------+------+----------+-------+
| 1 | SIMPLE | arts | NULL | ref | tid | tid | 5
| const | 2189 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------
+-------+------+----------+-------+
explain select * from arts where title="新闻标题"; #此时的 type 是 all 全表扫描
explain select t.name,arts.* from arts join type t on arts.tid=t.id; #对文章表和分类表进行多表联查
+----+-------------+-------+------------+------+---------------+------+---------
+----------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len
| ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------
+----------+-------+----------+-------+
| 1 | SIMPLE | t | NULL | ALL | PRIMARY | NULL | NULL
| NULL | 9 | 100.00 | NULL |
| 1 | SIMPLE | arts | NULL | ref | tid | tid | 5
| art.t.id | 24064 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------
+----------+-------+----------+-------+
# 结果对分类表是全表扫描,对文章表是索引扫描
explain select * from arts where tid>6; # 此时的type就变为range了
eq_ref 唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常用于主键和唯一索引扫描。
const、system 当mysql对查询进行优化并转化为一个常量的时候type就是const类型,例如使用主键或者唯一键作为where的条件查询,此时mysql会将这个查询结果转化为一个常量。system是const的一个特例，当查询的表只有一行数据的时候，type就是system类型
例如：
explain select * from arts where id=1300; #type 为const
null Mysql 在优化过程中分解语句,查询时甚至不用访问表或者索引
例如:
explain select max(id) from arts;
explain select count(*) from arts;
possible_keys
指出mysql可以使用哪个索引在表中找到行,涉及到若字段上存在索引则索引会被列出,但不一定会被查询用到
key
显示在查询中实际使用到的索引
如:
explain select title from arts where id=750000;
如果 要查询的字段就是这个索引则 key 为该索引字段名,但possible_keys为null
select id from arts
ref
多表联查时的连接匹配条件
rows
是估算执行这条语句需要扫描的记录数
例如 我的arts表有60多万条数据,我的tid字段有建立索引的
explain select * from arts where tid=6;
结果 rows为325186 ,要扫描三十多万条
当我删除tid索引的时候再执行:
explain select * from arts where tid=6;
rows: 614436 变成扫描60多万跳,变成了全表扫描
所以查看性能 rows是很重要的一个指标,而建立索引和使用where的范围查询可以减少扫描的行数
如果用主键来查,发现rows只有1而已
但其实,不一定扫描的行数越少就查的越快,例如
我这个表有60万+的数据
select title from arts where tid=6 and id>500000
select title from arts where id>500000 and tid=6;
select title from arts where id in (select id from arts where id>500000) and tid=6;
这前两条显示的rows都是311430,第三条显示的是203329和1,但是第一个用了40s,第二个用了16s,第三个只用了1.3秒,他们的结果是一样的
这是因为子查询查的字段是主键id,又是根据主键范围为条件查的,所以会非常快,只花了0.1秒,而外层查询又是根据主键id和索引tid查的,所以也是飞快。
这里说一下
select title from arts where id>500000
select id from arts where id>500000
这两句的条件都是用主键进行范围查询，扫描的条数也相同，但前者花了3.5秒，后者花了0.1秒而已，只因为查询的字段后者是主键。
同样说明不一定扫描行数不一定是越少就越快
Extra
包含不适合在其他列显示但非常重要的信息，有以下4中：
Using Index 表示相应的select查询使用了覆盖索引;覆盖索引就是包含满足查询需要的所有数据的索引
Using where 表示mysql在存储引擎收到记录后进行"后过滤",并不是说查询语句中使用了where就会有Using where
explain select id from arts where id>500000; # 显示了Using where
explain select * from arts where id>500000; # 没有显示Using where
Using temporary 表示使用了临时表来存储结果集,常用于排序和分组查询;如果同时出现了Using temporary和Using filesort 则性能是不佳的,这种情况出现在使用非索引字段分组的情况
explain select title from arts where id>600000 group by is_send desc;
+------+-------------+-------+-------+---------------+---------+---------+------+--------+--------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+--------+--------------------------------------------------------+
| 1 | SIMPLE | arts | range | PRIMARY | PRIMARY | 4 | NULL | 122818 | Using index condition; Using temporary; Using filesort |
+------+-------------+-------+-------+---------------+---------+---------+------+--------+--------------------------------------------------------+
explain select title from arts group by id desc;
+------+-------------+-------+------+---------------+------+---------+------+--------+----------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+--------+----------------+
| 1 | SIMPLE | arts | ALL | NULL | NULL | NULL | NULL | 614436 | Using filesort |
+------+-------------+-------+------+---------------+------+---------+------+--------+----------------+
Using filesort 文件排序,mysql将无法利用到索引的排序操作成为文件排序,所以出现Using filesort比不出现Using filesort的性能差
select sql_no_cache title from arts where id>500000 order by create_time desc; # Using index condition; Using filesort 用了3秒多
explain select sql_no_cache title from arts where id>700000 order by id desc; # Using where; 用了1秒多
所以尽可能使用索引排序
PS: Explain不会告诉你关于触发器,存储过程的信息或者用户自定义函数对查询的性能影响情况
Explain不会考虑缓存因素
Explain不会显示mysql在查询中所做的优化
部分统计信息是估算的,不准确
如果有子查询或者使用了临时表的视图,使用explain的开销会很大
