MySQL之explain 的type列 & Extra列

x33g5p2x  于2021-03-14 发布在 Mysql  
字(4.0k)|赞(0)|评价(0)|浏览(138)

explain 可以分析 select 语句的执行,即 MySQL 的“执行计划。

一、type 列

MySQL 在表里找到所需行的方式。包括(由左至右,由最差到最好):
| All | index | range | ref | eq_ref | const,system | null |

ALL(所有)
全表扫描,MySQL 从头到尾扫描整张表查找行。
mysql> explain select * from a\G
...
type: ALL
如果加上 limit 如 select * from a limit 100 MySQL 会扫描 100 行,但扫描方式不会变,还是从头到尾扫描。

index(索引)
根据索引来读取数据,如果索引已包含了查询数据,只需扫描索引树,否则执行全表扫描和All类似;
create table a(a_id int not null, key(a_id));
insert into a value(1),(2);
mysql> explain select a_id from a\G
...
type: index

range(范围)
以范围的形式扫描索引
建表:
create table a(a_id int not null, key(a_id));
insert into a values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
mysql> explain select * from a where a_id > 1\G
...
type: range
...

IN 比较符也会用 range 表示:
mysql> explain select * from a where a_id in (1,3,4)\G
...
type: range
...

ref(引用)
非唯一性索引访问
建表:

create table a(a_id int not null, key(a_id));
insert into a values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
mysql> explain select * from a where a_id=1\G
...
         type: ref
...

```~
eq_ref(等值引用)
使用有唯一性索引查找(主键或唯一性索引)
建表及插入数据:

```~
create table a(id int primary key);
create table a_info(id int primary key, title char(1));
insert into a value(1),(2);
insert into a_info value(1, 'a'),(2, 'b');
mysql> explain select * from a join a_info using(id);
...+--------+--------+...
...| table  | type   |...
...+--------+--------+...
...| a      | index  |...
...| a_info | eq_ref |...
...+--------+--------+...

```~
此时 a_info 每条记录与 a 一一对应,通过主键 id 关联起来,所以 a_info 的 type 为 eq_ref。
删除 a_info 的主键:ALTER TABLE  `a_info` DROP PRIMARY KEY;
现在 a_info 已经没有索引了:

```~
mysql> explain select * from a join a_info using(id);
+----+...+--------+--------+...
| id |...| table  | type   |...
+----+...+--------+--------+...
|  1 |...| a_info | ALL    |...
|  1 |...| a      | eq_ref |...
+----+...+--------+--------+...

```~
这次 MySQL 调整了执行顺序,先全表扫描 a_info 表,再对表 a 进行 eq_ref 查找,因为 a 表 id 还是主键。
删除 a 的主键:alter table a drop primary key;
现在 a 也没有索引了:

```~
mysql> explain select * from a join a_info using(id);
...+--------+------+...
...| table  | type |...
...+--------+------+...
...| a      | ALL  |...
...| a_info | ALL  |...
...+--------+------+...

```~
现在两个表都使用全表扫描了。


建表及插入数据:

```~
create table a(id int primary key);
create table a_info(id int, title char(1), key(id));
insert into a value(1),(2);
insert into a_info value(1, 'a'),(2, 'b');

```~
现在 a_info 表 id 列变为普通索引(非唯一性索引):

```~
mysql> explain select * from a join a_info using(id) where a.id=1;
...+--------+-------+...
...| table  | type  |...
...+--------+-------+...
...| a      | const |...
...| a_info | ref   |...
...+--------+-------+...

```~
a_info 表 type 变为 ref 类型了。
所以,唯一性索引才会出现 eq_ref (非唯一性索引会出现 ref ),因为唯一,所以最多只返回一条记录,找到后无需继续查找,因此比 ref 更快。

const(常量连接)
被称为“常量”,这个词不好理解,不过出现 const 的话就表示发生下面两种情况:
在整个查询过程中这个表最多只会有一条匹配的行,比如主键 id=1 就肯定只有一行,只需读取一次表数据便能取得所需的结果,且表数据在分解执行计划时读取。返回值直接放在 select 语句中,类似 select 1 AS f 。可以通过 extended 选择查看内部过程:

建表及插入数据:

```~
create table a(id int primary key, c1 char(20) not null, c2 text not null, c3 text not null);
insert into a values(1, 'asdfasdf', 'asdfasdf', 'asdfasdf'), (2, 'asdfasdf', 'asdfasdf', 'asdfasdf');
mysql> explain extended select * from a where id=1\G
...
         type: const
possible_keys: PRIMARY
          key: PRIMARY
...

```~
用 show warnings 查看 MySQL 是如何优化的:

```~
mysql> show warnings\G
...
Message: select '1' AS `id`,'asdfasdf' AS `c1`,'asdfasdf' AS `c2`,'asdfasdf' AS
`c3` from `test`.`a` where 1

```~
查询返回的结果为:

```~
mysql> select * from a where id=1;
+----+----------+----------+----------+
| id | c1       | c2       | c3       |
+----+----------+----------+----------+
|  1 | asdfasdf | asdfasdf | asdfasdf |
+----+----------+----------+----------+

```~
可以看出,返回结果中的字段值都以“值 AS 字段名”的形式直接出现在优化后的 select 语句中。
修改一下查询:

```~
mysql> explain select * from a where id in(1,2)\G
...
         type: range
...

```~
当返回结果超过 1 条时, type 便不再为 const 了。
重新建表及插入数据:

```~
create table a (id int not null);
insert into a value(1),(2),(3);
mysql> explain select * from a where id=1\G
...
         type: ALL

```~
目前表中只有一条 id=1 的记录,但 type 已为 ALL ,因为只有唯一性索引才能保证表中最多只有一条记录,只有这样 type 才有可能为 const 。
为 id 加普通索引后, type 变为 ref ,改为加唯一或主键索引后, type 便变为 const 了。



二、Extra 列
Extra表示附加信息,常见的有如下几种(也按查询效率从高到低排列):

**Using index:表示使用索引,如果只有 Using index,说明他没有查询到数据表,只用索引表就完成了这个查询,这个叫覆盖索引。如果同时出现Using where,代表使用索引来查找读取记录, 也是可以用到索引的,但是需要查询到数据表。
Using where:表示条件查询,如果不读取表的所有数据,或不是仅仅通过索引就可以获取所有需要的数据,则会出现 Using where。如果type列是ALL或index,而没有出现该信息,则你有可能在执行错误的查询:返回所有数据。  
Using filesort:不是“使用文件索引”的含义!filesort是MySQL所实现的一种排序策略,通常在使用到排序语句ORDER BY的时候,会出现该信息。  
Using temporary:表示为了得到结果,使用了临时表,这通常是出现在多表联合查询,结果排序的场合。**

如果EXPLAIN出现后面两个信息(Using filesort,Using temporary),而rows又比较大,通常意味着你需要调整查询语句,或者需要添加索引,总之需要尽量消除这两个信息。

相关文章

最新文章

更多