mysql explain type的区别和性能优化

mysql查看性能工具explain中type有很多种，主要的有：

链接类型	说明
system	表只有一行，MyISAM引擎。
const	常量连接，表最多只有一行匹配，通用用于主键或者唯一索引比较时
eq_ref	每次与之前的表合并行都只在该表读取一行，这是除了system，const之外最好的一种，特点是使用=，而且索引的所有部分都参与join且索引是主键或非空唯一键的索引
ref	如果每次只匹配少数行，那就是比较好的一种，使用=或<=>，可以是左覆盖索引或非主键或非唯一键
fulltext	全文搜索
ref_or_null	与ref类似，但包括NULL
index_merge	表示出现了索引合并优化(包括交集，并集以及交集之间的并集)，但不包括跨表和全文索引。这个比较复杂，目前的理解是合并单表的范围索引扫描（如果成本估算比普通的range要更优的话）
unique_subquery	在in子查询中，就是value in (select…)把形如select unique_key_column的子查询替换。PS：所以不一定in子句中使用子查询就是低效的！
index_subquery	同上，但把形如”select non_unique_key_column“的子查询替换
range	常数值的范围
index	索引树扫描。a.当查询是索引覆盖的，即所有数据均可从索引树获取的时候（Extra中有Using Index）；b.以索引顺序从索引中查找数据行的全表扫描（无 Using Index）；c.如果Extra中Using Index与Using Where同时出现的话，则是利用索引查找键值的意思；d.如单独出现，则是用读索引来代替读行，但不用于查找
all	全表扫描(full table scan)

现在把主要的实现一下，以下性能越来越慢。

system > const > eq_ref > ref > range > index > ALL

system

CREATE TABLE `news` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `news` (`id`, `title`) VALUES
(1, 'test');
explain select * from news where id=1;

主键或唯一索引查找常量值，只有一条记录，并且是MyISAM引擎。

const

INSERT INTO `news` (`id`, `title`) VALUES (NULL, 'test1');
EXPLAIN select * from news where id=1;

const扫描的条件为：
（1）命中主键(primary key)或者唯一(unique)索引；
（2）被连接的部分是一个常量(const)值；

eq_ref

CREATE TABLE `content` (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `news_id` int(10) UNSIGNED NOT NULL,
  `content` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO `content` (`id`, `news_id`, `content`) VALUES (NULL, '1', 'content');
INSERT INTO `content` (`id`, `news_id`, `content`) VALUES (NULL, '2', 'content2');
EXPLAIN SELECT * FROM `news` JOIN `content` ON news.id=content.news_id;

eq_ref扫描的条件为，对于前表的每一行(row)，后表只有一行被扫描。
（1）join查询；
（2）命中 主键(primary key) 或者 非空唯一(unique not null) 索引；
（3）等值连接；

ref

ALTER TABLE `news` ADD INDEX(`title`);
explain SELECT * FROM news where title="test";

当id改为普通非唯一索引后，常量的连接查询，也由const降级为了ref，因为也可能有多于一行的数据被扫描。
ref扫描，可能出现在join里，也可能出现在单表普通索引里，每一次匹配可能有多行数据返回，虽然它比eq_ref要慢，但它仍然是一个很快的join类型。

range

explain SELECT * FROM news where id>1;

between，in，>都是典型的范围(range)查询。

index

如果是MyISAM引擎，type是NULL。

ALTER TABLE `news` ENGINE = InnoDB;
explain SELECT count(*) FROM news;

index类型，需要扫描索引上的全部数据。

ALL

explain select * from content;

system最快：很少能出现。
const：PK或者unique上的等值查询
eq_ref：PK或者unique上的join查询，等值匹配，对于前表的每一行(row)，后表只有一行命中
ref：非唯一索引，等值匹配，可能有多行命中
range：索引上的范围扫描，例如：between/in/>
index：索引上的全集扫描，例如：InnoDB的count
ALL最慢：全表扫描(full table scan)