成佩涛编程之路——Mysql explain—type列详细说明

通过explain可以知道mysql是如何处理语句，分析出查询或是表结构的性能瓶颈。通过expalin可以得到：
1. 表的读取顺序
2.表的读取操作的操作类型
3.哪些索引可以使用
4. 哪些索引被实际使用
5.表之间的引用
6.每张表有多少行被优化器查询

explain显示字段

mysql> EXPLAIN SELECT * FROM jw_jobinfo where title='成佩涛';
+----+-------------+------------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | jw_jobinfo | ALL  | NULL          | NULL | NULL    | NULL | 19261 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+-------+-------------+
1 row in set

性能比较

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

   1、type: ALL

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

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

2、type: index

index 按索引次序扫描表，就是先读索引，再读实际的行，其实还是全表扫描。主要优点是避免了排序，因为索引是排好序的。（按照索引的排序去读对应的数据行。） create table a(a_id int not null, key(a_id)); insert into a value(1),(2); mysql> explain select * from a\G
...
    3、type: range

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
...

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

` 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
    5、type: eq_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 更快。

6、type: const

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

关键词：成佩涛编程之路 mysql优化