文档地址:http://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-join-types
EXPLAIN Join Types
EXPLAIN命令输出中的type字段返回了表是如何关联的。在JSON格式化输出中,该值可以查询access_type的键值。以下列表给出了关联方式,从最好到最差的方式排序:
l system
表只有一行记录(=系统表)。这是const关联方式的一种特殊情况。
l const
在开始查询前,表至多只有一行被读取的匹配记录。因为仅有一行记录的原因,来自
于该记录的字段值被后面的查询优化器视为常量。const表非常快因为它们只被读取
一次。
const被用于当你将各个PRIMARY KEY或者UNIQUE索引与常量值匹配的情况下。
在以下查询中,tbl_name被用作const表:
SELECT * FROM tbl_name WHERE primary_key=1;
SELECT * FROM tbl_name
WHERE primary_key_part1=1 AND primary_key_part2=2;
l eq_ref
对于与前表记录的每次关联,当前表一行被读取。除了system和const方式,这是
最好的关联方式。它被用于当一个索引的所有字段部分被关联使用以及索引是
PRIMARY KEY或者UNIQUE NOT NULL的情况下。
eq_ref能被用于与使用了“=”运算符进行匹配的索引字段。匹配值可以是一个常量,
或者是使用来自于先于当前表被读取的表字段的表达式。在以下示例中,MySQL使用
eq_ref关联来操作ref_table:
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
l ref
对于与前表记录的每次关联,当前表匹配索引值的所有记录行被读取。ref被用于关联
仅使用索引字段的最左前缀部分或者该索引字段不是PRIMARY KEY或者UNIQUE(换
句话说,基于索引键值,关联查询到的不是单单一条记录)。如果被使用的索引键仅仅
匹配少数记录,这是不错的关联方式。
ref能被用于与使用了“=”或者“<=>”运算符进行匹配的索引字段。在以下示例中,
MySQL使用ref关联来操作ref_table:
SELECT * FROM ref_table WHERE key_column=expr;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
l fulltext
关联被用在使用了FULLTEXT索引的情况下。
这种关联方式类似于ref,但是作为附加操作MySQL执行一次针对包含NULL值记录
的额外查询。该关联方式优化最常被用于解决子查询。在以下示例中,MySQL使用
ref_or_null关联来操作ref_table:
SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;
参见Section 9.2.1.8, “IS NULL Optimization”。
该关联方式说明索引合并优化被使用。在这种情况下,(EXPLAIN命令)返回记录里的
key字段包含了被使用索引一栏,另外key_len字段包含了被使用索引最长键部分
一栏。更多信息参见Section 9.2.1.4, “Index Merge Optimization”。
如下所示IN子查询,该方式替代eq_ref:
value IN (SELECT primary_key FROM single_table WHERE some_expr)
unique_subquery正是一种为了更高性能而代替完全子查询的索引查询方式。
该关联方式类似于unique_subquery。它也代替IN子查询,但是,如下所示IN子
查询,该操作是对非唯一索引:
value IN (SELECT key_column FROM single_table WHERE some_expr)
l range
使用索引查询记录,在被给定范围内的记录才会被检索。(EXPLAIN命令)返回记录里
的key字段说明哪个索引被使用。key_len字段包含了被使用最长索引键部分。该方
式的ref字段是NULL。
range能被用于当索引字段与使用了以下任一运算符常量进行匹配的情况:=,<>,>,
>=,<,<=,IS NULL,<=>,BETWEEN以及IN()。
SELECT * FROM tbl_name
WHERE key_column = 10;
SELECT * FROM tbl_name
WHERE key_column BETWEEN 10 and 20;
SELECT * FROM tbl_name
WHERE key_column IN (10,20,30);
SELECT * FROM tbl_name
WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
l index
除了索引树被扫描,index关联方式与ALL是一样的。它出现在以下两方面:
对于查询,当索引是覆盖索引并且可以用来满足需要从表中得到的所有数据时,只有索引树被扫描。在这种情况下,(EXPLAIN命令)返回记录里的Extra字段提示Using index。由于索引的大小通常比表数据小,所以仅索引扫描通常比ALL快。
一个全表扫描被执行,使用来自于索引的读入以索引的顺序去查找数据行。Uses index不会出现在(EXPLAIN命令)返回记录里的Extra字段。
当查询仅使用单个索引的部分字段时,MySQL使用该关联方式。
l ALL
对于与前表记录的每次关联,当前表进行全表扫描。如果表是第一张未标记const的
表,这通常不好,并且通常在所有其他情况下very差。基于来自于前表的常量值或字
段值,一般情况下你可以通过添加当前表记录检索的索引来避免ALL。