mysql range ref,MySQL EXPLAIN的输出信息

MySQL EXPLAIN的输出信息

表结构

drop table article;

drop table user;

-- 最终的两个表的定义，authorId上没有任何索引(不是外键，也没有索引)

create table article(

id int auto_increment primary key,

title varchar(255) not null,

shortName varchar(255) not null,

authorId int not null,

createTime datetime not null,

state int  not null,

totalView int default null,

unique index idx_short_name_title (title,shortName)

);

create table user (

id int auto_increment primary key,

name varchar(255) not null,

sex bit default 0,

email varchar(50) not null,

address varchar(255) default null,

unique index idx_email (email),

index idx_name (name)

);

insert into article (title,shortName,authorId,createTime,state,totalView)

values ('hello world','hello-world-0',1,'2015-10-11 08:08:08',1,10),

('hello world','hello-world-1',1,'2015-10-11 08:08:08',2,10),

('hello world','hello-world-2',2,'2015-10-11 08:08:08',3,10),

('hello world','hello-world-3',3,'2015-10-11 08:08:08',4,10),

('hello world','hello-world-4',3,'2015-10-11 08:08:08',5,10);

insert into user (name,sex,email,address) values('lyx',0,'000@gmail.com','bj'),

('lyx',0,'111@gmail.com','bj'),

('lyx-0',0,'222@gmail.com','bj'),

('lyx-1',0,'333@gmail.com','bj');

====

alter table article add constraint fk_author_id foreign key (authorId) references user(id);

alter table article drop foreign key fk_author_id;

alter table article drop index fk_author_id;

alter table article add index idx_author_id (authorId);

alter table article drop index idx_author_id;

show create table article;

====

基本用法

explain article;

explain select * from article a where a.author_id in (select author_id from user);

第一个相当于desc表结构。

第二个表示select查询语句的查询计划

select_type 列

SUBQUERY

> explain select (select 1 from article limit 1) from user

******************** 1. row *********************

id: 1

select_type: PRIMARY

table: user

type: index

possible_keys:

key: idx_email

key_len: 52

ref:

rows: 4

Extra: Using index

******************** 2. row *********************

id: 2

select_type: SUBQUERY

table: article

type: index

possible_keys:

key: idx_short_name_title

key_len: 514

ref:

rows: 5

Extra: Using index

2 rows in set

包含在select列表中的子查询的select(换句话说，不在form子句中)标记为SUBQUERY。

DEPENDENT SUBQUERY

> explain

select * from article a where exists (select * from user u where a.authorId = u.id)

******************** 1. row *********************

id: 1

select_type: PRIMARY

table: a

type: ALL

possible_keys:

key:

key_len:

ref:

rows: 5

Extra: Using where

******************** 2. row *********************

id: 2

select_type: DEPENDENT SUBQUERY

table: u

type: eq_ref

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: test.a.authorId

rows: 1

Extra: Using index

2 rows in set

DERIVED

> explain select max(id) from (select * from article) a

******************** 1. row *********************

id: 1

select_type: PRIMARY

table: 

type: ALL

possible_keys:

key:

key_len:

ref:

rows: 5

Extra:

******************** 2. row *********************

id: 2

select_type: DERIVED

table: article

type: ALL

possible_keys:

key:

key_len:

ref:

rows: 5

Extra:

2 rows in set

DERIVED值用来表示包含在from子句的子查询中的select，mysql会递归并将结果防盗一个临时表中。服务器内部称其为“派生表”，因为该表是从子查询中派生出来的。

UNION && UNION RESULT

> explain select id , title from article a where a.id = 1 union select id ,title from article b where b.id = 2

******************** 1. row *********************

id: 1

select_type: PRIMARY

table: a

type: const

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: const

rows: 1

Extra:

******************** 2. row *********************

id: 2

select_type: UNION

table: b

type: const

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: const

rows: 1

Extra:

******************** 3. row *********************

id:

select_type: UNION RESULT

table: 

type: ALL

possible_keys:

key:

key_len:

ref:

rows:

Extra: Using temporary

3 rows in set

type 列

mysql用户手册上说这一列显示了“关联类型”，但我们认为更准确的说法是访问类型——换言之就是mysql决定如何查找表中的行。下面是最重的访问数据行的方法，依次从最差到最优。

ALL

这就是人们所称的全表扫描，通常意味着mysql必须扫描整张表，从头到尾，去找到需要的行。(这里也有个例外，例如在查询里使用了LIMIT，或者在Extra列中显示using distinct／not exists)。

比如，

> explain

select * from article

******************** 1. row *********************

id: 1

select_type: SIMPLE

table: article

type: ALL

possible_keys:

key:

key_len:

ref:

rows: 5

Extra:

1 rows in set

index

这个跟全表扫描一样，只是mysql扫描表的时候按照索引次序进行而不是行。它的主要优点是避免了排序；最大的缺点时要承担按索引次序读取整个表的开销。这通常意味着如是按照随机次序访问行，开销将会非常大。

如果在Extra列中看到了using index，说明mysql正在使用覆盖索引，他只是扫描索引的数据，而不是按索引次序的每一行。他比按照索引次序全表扫描的开销要少很多。

> explain

select id , title from article

******************** 1. row *********************

id: 1

select_type: SIMPLE

table: article

type: index

possible_keys:

key: idx_short_name_title

key_len: 514

ref:

rows: 5

Extra: Using index

1 rows in set

range

mysql5.6手册解释：

Only rows that are in a given range are retrieved, using an index to select the rows. The key column in the output row indicates which index is used. The key_len contains the longest key part that was used. The ref column isNULL for this type.

range can be used when a key column is compared to a constant using any of the =, <>, >, >=, , BETWEEN, or IN() operators.

范围扫描就是一个有限制的索引扫描，它开始于索引里的某一点，返回匹配这个值域的行。这比全索引扫描好一些，因为它用不着遍历全部索引。显而易见的范围扫描是带有between或在where子句里带有>的查询。

当mysql使用索引去查找一系列值时，例如 IN() 或 OR列表，也会显示为范围扫描。然而这两者其实是相当不同的访问类型，在性能上有重要的差异。

> explain

select * from article where id > 2

******************** 1. row *********************

id: 1

select_type: SIMPLE

table: article

type: range

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref:

rows: 3

Extra: Using where

1 rows in set

要注意并不是所有的范围查询当执行查询计划时出现type＝range，如下面的sql

> explain

select * from article a where a.totalView >2

******************** 1. row *********************

id: 1

select_type: SIMPLE

table: a

type: ALL

possible_keys:

key:

key_len:

ref:

rows: 5

Extra: Using where

1 rows in set

这是因为totalView列上没有建立索引。

ref 列

这是一种索引访问(有时也叫做索引查找)，它返回所有匹配某个单个值的行。然而，它可能会找到多个符合条件的行，因此，它是查找和扫描的混合体。此类索引访问只有当使用非唯一性索引或者唯一性索引的非唯一性前缀时才会发生。把它叫做ref是因为要跟某个参考值相比较。这个参考值或者是一个常数，或者是来自多表查询前一个表里的结果值。

ref_or_null是ref之上的一个遍体，它意味着mysql必须在初次查找的结果里进行第二次查找以找出NULL条目。

首先我们给authorId列建上索引，

alter table article add index idx_author_id (authorId);

> explain

select * from article where authorId = 1

******************** 1. row *********************

id: 1

select_type: SIMPLE

table: article

type: ref

possible_keys: idx_author_id

key: idx_author_id

key_len: 4

ref: const

rows: 2

Extra:

1 rows in set

再看一个示例，

> explain

select * from article a , user u where a.authorId = u.id and a.authorId = 1

******************** 1. row *********************

id: 1

select_type: SIMPLE

table: u

type: const

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: const

rows: 1

Extra:

******************** 2. row *********************

id: 1

select_type: SIMPLE

table: a

type: ref

possible_keys: idx_author_id

key: idx_author_id

key_len: 4

ref: const

rows: 2

Extra:

2 rows in set

下面着三个sql的访问数据的方式type都是ref，

explain

select * from article a , user u where a.authorId = u.id and a.authorId = 1;

explain

select * from article a inner join user u on a.authorId = u.id and a.authorId = 1;

explain

select * from article a inner join user u on a.authorId = u.id where a.authorId = 1;

eq_ref

使用这种索引查找，mysql知道最多只返回一条符合条件的记录。这种访问方式可以在mysql使用主键活着唯一索引查找时看到，它会将他们与某个参考值做比较。mysql对于这类访问类型的优化做的非常好，因为它知道无须估计匹配行的范围或在找到匹配行后再继续查找。

基于上面的表结构中，authorId列上没有索引，我们要给他建立一个唯一索引

alter table article add unique index idx_author_id (authorId);

> explain

select * from article a left join user u on a.authorId = u.id

******************** 1. row *********************

id: 1

select_type: SIMPLE

table: a

type: ALL

possible_keys:

key:

key_len:

ref:

rows: 1

Extra:

******************** 2. row *********************

id: 1

select_type: SIMPLE

table: u

type: eq_ref

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: test.a.authorId

rows: 1

Extra:

2 rows in set

再看一个使用exists子查询的示例，先把上面建立的索引删掉

alter table article drop index idx_author_id;

> explain

select * from article a where exists (select * from user u where a.authorId = u.id)

******************** 1. row *********************

id: 1

select_type: PRIMARY

table: a

type: ALL

possible_keys:

key:

key_len:

ref:

rows: 5

Extra: Using where

******************** 2. row *********************

id: 2

select_type: DEPENDENT SUBQUERY

table: u

type: eq_ref

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: test.a.authorId

rows: 1

Extra: Using index

2 rows in set

访问数据行的类型也是eq_ref。exists子查询的作用就是判断前面查询出来的数据行在子查询中存在不存在，如果存在就返回true。所以只要确定有一条记录存在就行。

type const && type system

当mysql能对查询的某部分进行优化并将其转换为一个常量时，他就会使用这些访问类型。举例来说，如果你通过将某一行的主键放入where子句中里的方式来选取此行的主键，mysql就能将这个查询转换为一个常量。

> explain

select * from (select * from article a where a.id=1) temp_a inner join user u on temp_a.authorId = u.id

******************** 1. row *********************

id: 1

select_type: PRIMARY

table: 

type: system

possible_keys:

key:

key_len:

ref:

rows: 1

Extra:

******************** 2. row *********************

id: 1

select_type: PRIMARY

table: u

type: const

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: const

rows: 1

Extra:

******************** 3. row *********************

id: 2

select_type: DERIVED

table: a

type: const

possible_keys: PRIMARY

key: PRIMARY

key_len: 4

ref: const

rows: 1

Extra:

3 rows in set

Extra 列

放到下一篇文章来说。

============END============