mysql explain中的type类型

2017-04-30 15:52:15

mysql中常使用explain查看sql进行计划,而type类型是非常重要的。这里主要描述一下type的几种类型和意义。

先创建两个简单的表,用于测试。

CREATE TABLE `author` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `sort` int(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

CREATE TABLE `blog` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `author_id` int(10) unsigned DEFAULT NULL,
  `author_sort` int(20) DEFAULT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `author_id` (`author_id`),
  KEY `title` (`title`) USING BTREE,
  KEY `author_sort` (`author_sort`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

type列很重要,是用来说明表与表之间是如何进行关联操作的,有没有使用索引。MySQL中“关联”一词比一般意义上的要宽泛,MySQL认为任何一次查询都是一次“关联”,并不仅仅是一个查询需要两张表才叫关联,所以也可以理解MySQL是如何访问表的。主要有下面几种类别。

system
该表(系统表)只有一行,是const的一种特例

const
单表中最多只有一个匹配行(主键或者唯一索引),在优化阶段即可读取到数据。
const查询非常快,因为只读取一次

const 在使用主键或唯一索引查询时出现

eq_ref
使用 = 关联表,对于前一个表的每一记录,所关联的表都仅有一条数据匹配。
通常使用主键或唯一索引进行表关联时,会出现eq_ref。eq_ref对唯一性的索引B+树进行检索。
对于eq_ref,innobd和MyISAM表现不一致

Innodb在数组量小的表现如下

为了测试MyISAM的情况,先创建对于MyISAM的表

CREATE TABLE `blog2` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `author_id` int(10) unsigned DEFAULT NULL,
  `author_sort` int(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `author_id` (`author_id`),
  KEY `author_sort` (`author_sort`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;


CREATE TABLE `author2` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `birthday` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `sort` int(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

MyISAM表现如下

ref
使用普通索引(只使用了索引的最左前缀,或者索引不是UNIQUE和PRIMARY KEY)进行查询或关联操作只使用了普通索引
ref可以用于使用=或<=>操作符的带索引的列。eq_ref对普通的索引B+树进行检索。

fulltext
fulltext是使用全文索引进行的。一般我们用到的索引都是B树,这里就不举例说明了。

ref_or_null
该类型和ref类似。但是MySQL会做一个额外的搜索包含NULL列的操作。在解决子查询中经常使用该联接类型的优化
对于ref_or_null,innobd和MyISAM在小数据量的情况下(大数据量待测试)表现也不一致

innodb

MyISAM:

unique_subquery

该类型替换了下面形式的IN子查询的ref:

value IN (SELECT primary_key FROM single_table WHERE some_expr)

unique_subquery是一个索引查找函数,可以完全替换子查询,效率更高。

index_subquery

该联接类型类似于unique_subquery。可以替换IN子查询,但只适合下列形式的子查询中的非唯一索引:

value IN (SELECT key_column FROM single_table WHERE some_expr)

range
对索引进行给定范围内的检索,
当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符,用常量比较关键字列时,可以使用range:
我的测试中:只有查询字段是主键或唯一索引时type才会为range。

index
对索引树进行全扫描,但通常比ALL快,因为索引文件通常比数据文件小。这个类型通常的作用是告诉我们查询是否使用索引进行排序操作。
常见于用id排序,如

all
最慢的一种方式,即全表扫描。

对于exists和in效率,不能简单地判定哪一个效率更高,比较如下查询:

使用in查询,对author表进行了全表查询,如果author表数据量较小,则in查询更快。

使用exists查询,对blog表进行了全表查询,如果blog表数据量较小,则exists查询更快。

水平有限,如有错漏,还望指出。

参考:
MySQL优化—工欲善其事,必先利其器之EXPLAIN
MySQL 处理海量数据时的一些优化查询速度方法