MySQL之explain 的type列 & Extra列

柔光的暖阳◎ 2022-08-25 10:56 174阅读 0赞

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

**一、type 列**

MySQL 在表里找到所需行的方式。包括（由左至右，由最差到最好）：

**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');

此时 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又比较大，通常意味着你需要调整查询语句，或者需要添加索引，总之需要尽量消除这两个信息。