MySQL explain的使用笔记

最新推荐文章于 2024-01-16 09:23:17 发布

易昂昜

最新推荐文章于 2024-01-16 09:23:17 发布

阅读量135

点赞数 1

分类专栏：数据库笔记文章标签： mysql 索引 sql

本文链接：https://blog.csdn.net/guyyyy/article/details/111245111

版权

数据库笔记专栏收录该内容

4 篇文章 0 订阅

订阅专栏

表：员工表emp ，部门表dep。

解析如下sql：

explain select e.ename,d.dname from emp e,dept d where e.dept_id=d.id

解析如下sql：

explain select * from (select * from dept LIMIT 2) d

在这里插入图片描述

解析如下sql：

explain select emp.ename,(select dept.dname from dept) from emp

在这里插入图片描述

id表示sql语句执行顺序，id越大，优先级越高。

select_type表示查询类型:

simple 表示没有子查询，只是简单的查询。
primary 表示是子查询中最外层的查询。
subquery 在select或where中包含的子查询。
derived 在from中包含的子查询被标记为dervied。
union 若第二条select出现在union后，则标记为union。
union result union的结果。

table表示输出的行所引用的表。

type表示连接类型。

null： MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
system： 表仅有一行（=系统表）这是const联结类型的一个特例，平时很少见。
const： 表示通过索引一次就找到，const用于比较primary key或者unique索引。因为只匹配一行数据。

explain select * from dept where id=20 #id为主键

eq_ref： 唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。它用在一个索引的所有部分被联接使用并且索引是UNIQUE或PRIMARY KEY。
ref： 非唯一性索引扫描，如果连接只使用键的最左前缀，或如果键不是unique或primary key，则使用ref。本质上也是一种索引访问。它返回所有匹配某个单独值的行，可能会找多个符合条件的行。如下sql：有多项匹配值。dept_id为外键。如果条件换为普通字段，则type为all。

explain select * from emp where emp.dept_id=20

ref_or_null： 这种连接类型类似ref，不同的是mysql会在检索的时候额外的搜索包含null 值的记录。

explain select * from emp where emp.dept_id=20 or emp.dept_id is null

range： 给定范围查询，且使用索引进行查询。
index index 与all区别为index类型只遍历索引树。通常比all快，因为索引文件比数据文件小很多。虽都为读全表，但index读索引，all读磁盘。
all： 遍历全表以找到匹配的行。

以上连接类型从上到下性能越来越差。

possible_keys 指出MySQL能使用哪个索引在该表中找到行。

注意，这个字段完全独立于explain 显示的表顺序。这就意味着 possible_keys里面所包含的索引可能在实际的使用中没用到。如果这个字段的值是null，就表示没有索引被用到。这种情况下，就可以检查 where子句中哪些字段那些字段适合增加索引以提高查询的性能。
可以使用 show index from tbl_name查看表有哪些索引。

key显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。查询中如果使用覆盖索引，则该索引和查询的select字段重叠。

想要让mysql强行使用或者忽略在 possible_keys字段中的索引列表，可以在查询语句中使用关键字force index, use index,或 ignore index。如果是 myisam 和 bdb 类型表，可以使用 analyzetable 来帮助分析使用使用哪个索引更好。如果是 myisam类型表，运行命令 myisamchk --analyze也是一样的效果。

key_len 显示了mysql使用索引的长度。当 key 字段的值为 null时，索引的长度就是 null。注意，key_len的值可以告诉你在联合索引中mysql会真正使用了哪些索引。

ref 显示索引的哪一列被使用了，显示使用哪个列或常数与key一起从表中选择行。

rows 显示了mysql认为在查询中所需要的记录要检索的行数。

Extra 该列包含MySQL解决查询的详细信息。

distinct mysql当找到当前记录的匹配联合结果的第一条记录之后，就不再搜索其他记录了。
not exists mysql在查询时做一个 left join优化时，当它在当前表中找到了和前一条记录符合 left join条件后，就不再搜索更多的记录了。下面是一个这种类型的查询例子：

select * from t1 left join t2 on t1.id=t2.id where t2.id isnull;

假使 t2.id 定义为 not null。这种情况下，mysql将会扫描表 t1并且用 t1.id 的值在 t2 中查找记录。当在t2中找到一条匹配的记录时，这就意味着 t2.id 肯定不会都是null，就不会再在 t2 中查找相同id值的其他记录了。也可以这么说，对于 t1 中的每个记录，mysql只需要在t2 中做一次查找，而不管在 t2 中实际有多少匹配的记录。

range checked for each record mysql没找到合适的可用的索引。取代的办法是，对于前一个表的每一个行连接，它会做一个检验以决定该使用哪个索引（如果有的话），并且使用这个索引来从表里取得记录。这个过程不会很快，但总比没有任何索引时做表连接来得快。
using filesort mysql需要额外的做一遍从而以排好的顺序取得记录。排序程序根据连接的类型遍历所有的记录，并且将所有符合 where条件的记录的要排序的键和指向记录的指针存储起来。这些键已经排完序了，对应的记录也会按照排好的顺序取出来。
当Query中包含 order by 操作，MySQL中无法利用索引完成排序操作称为“文件排序”。

explain select * from emp order by ename

using index： 表示字段的信息直接从索引树中的信息取得，而不再去扫描实际的记录。这种策略用于查询时的字段是一个独立索引的一部分。
如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查询动作。
using temporary： mysql需要创建临时表存储结果以完成查询。这种情况通常发生在查询时包含了groupby 和 order by 子句，它以不同的方式列出了各个字段。

出现using temporary就说明语句需要优化了

using where： 表示mysql服务器将在存储引擎检索行后再进行过滤，即使用where过滤。
using join buffer： 改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
impossible where: where子句的值总是false，不能用来获取任何元组。
select tables optimized away： 在没有group by子句的情况下，基于索引优化Min、max操作或者对于MyISAM存储引擎优化count（*），不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
即MySQL优化器不用查询就可以从一些统计信息中知道查询的结果。
No tables used： 查询语句中使用from dual或不含任何from子句。

关于出现using temporary：
执行如下sql：

explain select e.ename from emp e ,dept d where d.dname=‘1教研部’ and e.bonus=3000 and d.id=e.dept_id order by e.dept_id asc

在这里插入图片描述
可以看到出现了using temporary，且type是all全表查。
我在网上找到了一句话：

MySQL 表关联的算法是 Nest Loop Join，是通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。
EXPLAIN 结果中，第一行出现的表就是驱动表（Important!）以上两个查询语句，驱动表是 d，如上面的执行计划所示！
对驱动表可以直接排序，对非驱动表（的字段排序）需要对循环查询的合并结果（临时表）进行排序（Important!）
因此，order by e.dept_id asc时，就要先 using temporary 了！

驱动表的定义 当进行多表连接查询时， [驱动表] 的定义为：

指定了联接条件时，满足查询条件的记录行数少的表为[驱动表]；
未指定联接条件时，行数少的表为[驱动表]（Important!）。

所以以上sql应该使用驱动表来排序，explain得到的第一行出现的表为驱动表，则d为驱动表，修改为: