MySQL之explain的type列

页面导航:首页 > 数据库 > Mysql > MySQL之explain的type列

MySQL之explain的type列

来源: 作者: 时间:2016-01-30 09:02 【

explain 可以分析 select 语句的执行,即 MySQL 的执行计划。type 列MySQL 在表里找到所需行的方式。包括(由左至右,由最差到最好):| All | index | range | ref | eq_ref | const

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

type 列

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

ALL 全表扫描,MySQL 从头到尾扫描整张表查找行。 > explain select * from a\G ...
type: ALL

如果加上 limit 如 select * from a limit 100 MySQL 会扫描 100 行,但扫描方式不会变,还是从头到尾扫描。

index 按索引次序扫描表,就是先读索引,再读实际的行,其实还是全表扫描。主要优点是避免了排序,因为索引是排好序的。(按照索引的排序去读对应的数据行。) create table a(a_id int not null, key(a_id)); insert into a value(1),(2); mysql> explain select * 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 了。

Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<