Explain 命令中的 type 列，显示MySQL查问所应用的 关联类型（Join Types） 或者 拜访类型，它表明 MySQL决定如何查找表中符合条件的行。
常见拜访类型性能由最差到最优顺次为：ALL < index < range < index_subquery < unique_subquery < index_merge < ref_or_null < fulltext < ref < eq_ref < const < system。
0、测试环境简述
本文 MySQL 实例版本为 5.7，表存储引擎为 InnoDB
数据库 t 中有两张表 user、user_captcha，每张表中有2W+条数据，上面是两张表的建表语句（表构造只为满足试验要求，没有理论业务逻辑参考价值）：
user 表
- id 字段是主键
- email 字段建设了惟一索引
- phone 与 country_code 字段组成联结惟一索引
- birth_year 与 gender 字段组成联结一般索引
- nickname 字段前10个字符建设了一般索引
CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `nickname` varchar(255) DEFAULT NULL, `country_code` smallint(6) unsigned NOT NULL DEFAULT '0', `phone` varchar(12) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '', `email` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, `gender` tinyint(4) DEFAULT NULL, `birth_year` smallint(11) unsigned DEFAULT NULL, `created_at` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `unq_phone_country_code` (`phone`,`country_code`) USING BTREE, UNIQUE KEY `unq_email` (`email`), KEY `idx_birth_year_gender` (`birth_year`,`gender`) USING BTREE, KEY `idx_nickname` (`nickname`(10))) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4user_captcha 表
- id 字段是主键
- user_id 字段建设了惟一索引，能够为空
- receiver 字段建设了惟一索引
CREATE TABLE `user_captcha` ( `id` int(11) NOT NULL AUTO_INCREMENT, `user_id` int(11) unsigned DEFAULT NULL, `code` char(6) COLLATE utf8_unicode_ci NOT NULL COMMENT '验证码', `retry_times` int(11) NOT NULL COMMENT '重试次数', `last_request_at` int(11) unsigned DEFAULT NULL COMMENT '最初申请工夫', `receiver` varchar(255) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL COMMENT '接收者（手机号或邮箱）', `created_at` int(11) NOT NULL, `expired_at` int(11) NOT NULL COMMENT '过期工夫', PRIMARY KEY (`id`), UNIQUE KEY `unq_receiver` (`receiver`) USING BTREE, UNIQUE KEY `unique_user` (`user_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb41. ALL
全表扫描，通常意味着MySQL必须从头到尾扫描整张表，去查找匹配的行的行，性能极差。
然而，如果在查问里应用了 LIMIT n，尽管 type 仍然是 ALL，然而MySQL只须要扫描到符合条件的前 n 行数据，就会进行持续扫描。
- 查问昵称中带 雪 字的用户数据，因为应用了前缀含糊匹配，不能命中索引，会导致全表扫描
mysql> EXPLAIN SELECT * FROM `user` WHERE `nickname` LIKE '%雪%' LIMIT 1 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 22748 filtered: 11.11 Extra: Using where- 查问依据用户id能够被10整除的用户数据。因为在 = 前的索引列上进行了表达式运算，不能命中索引，会全表扫描。
mysql> EXPLAIN SELECT * FROM `user` WHERE id%10=0 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 22293 filtered: 100.00 Extra: Using where1 row in set, 1 warning (0.01 sec)- 查问手机号是 18888888888 的用户数据，因为数据表中 phone 字段是字符串类型，而查问时应用了数字类型，会触发隐式类型转换，不会命中索引，因而会全表扫描。
mysql> EXPLAIN SELECT * FROM `user` WHERE phone=18888888888 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: ALLpossible_keys: unq_phone_country_code key: NULL key_len: NULL ref: NULL rows: 22293 filtered: 10.00 Extra: Using where2. index
index 跟 ALL 一样，也会进行全表扫描，只是MySQL会按索引秩序进行全表扫描，而不是间接扫描行数据。它的次要长处是防止了排序；最大的毛病是要承当按索引秩序读取整个表的开销。若是按随机秩序拜访行，开销将会十分大。
- 依据出世年分组去重，查问用户数据。
mysql> EXPLAIN SELECT * FROM `user` GROUP BY `birth_year` \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: indexpossible_keys: idx_birth_year_gender key: idx_birth_year_gender key_len: 5 ref: NULL rows: 22748 filtered: 100.00 Extra: NULL如果在 Extra 列中看到 Using index，阐明MySQL正在应用笼罩索引，索引的数据中蕴含了查问所需的所有字段，因而只须要扫描索引树就可能实现查问工作。它比按索引秩序全表扫描的开销要少很多，因为索引树的大小通常要远小于全表数据。
- 依据出世年分组，查问不同年份出世的用户个数，这里用到了笼罩索引。
mysql> EXPLAIN SELECT `birth_year`,COUNT(*) FROM `user` GROUP BY `birth_year`\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: indexpossible_keys: idx_birth_year_gender key: idx_birth_year_gender key_len: 5 ref: NULL rows: 22748 filtered: 100.00 Extra: Using index- 查问用户的id、性别、出世年数据，因为
idx_birth_year_gender索引中蕴含birth_year和gender字段，而 InnoDB的所有索引都蕴含id字段，不须要回表查问其余数据，因而也能用到笼罩索引。
mysql> EXPLAIN SELECT `id`,`birth_year`,`gender` FROM `user` LIMIT 10 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: indexpossible_keys: NULL key: idx_birth_year_gender key_len: 5 ref: NULL rows: 22748 filtered: 100.00 Extra: Using index- 查问表数据总条数，查问数据条数时，InnoDB存储引擎会主动抉择最短的索引，通过遍历该索引，就能够计算出数据总条数，不须要回表查问其余数据，因而也能用到笼罩索引。
mysql> EXPLAIN SELECT COUNT(*) FROM user \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: indexpossible_keys: NULL key: idx_birth_year_gender key_len: 5 ref: NULL rows: 22748 filtered: 100.00 Extra: Using index3. range
范畴扫描，就是一个有范畴限度的索引扫描，它开始于索引里的某一点，返回匹配这个范畴值的行。range 比全索引扫描更高效，因为它用不着遍历全副索引。
范畴扫描分为以下两种状况：
- 范畴条件查问：在
WHERE子句里带有BETWEEN、>、<、>=、<=的查问。 - 多个等值条件查问：应用
IN()和OR，以及应用like进行前缀匹配含糊查问。
- 查问
id >= 1000且id < 2000的用户数据。
mysql> EXPLAIN SELECT * FROM `user` WHERE `id`>=1000 AND `id`<2000 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: rangepossible_keys: PRIMARY key: PRIMARY key_len: 4 ref: NULL rows: 8 filtered: 100.00 Extra: Using where- 查问 90后 的用户数据。
mysql> EXPLAIN SELECT * FROM `user` WHERE `birth_year` BETWEEN 1990 AND 1999 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: rangepossible_keys: idx_birth_year_gender key: idx_birth_year_gender key_len: 3 ref: NULL rows: 150 filtered: 100.00 Extra: Using index condition- 查问昵称以 雪 字结尾的用户数据。
mysql> EXPLAIN SELECT * FROM `user` WHERE `nickname` LIKE '雪%' \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: rangepossible_keys: idx_nickname key: idx_nickname key_len: 43 ref: NULL rows: 30 filtered: 100.00 Extra: Using where- 别离应用
IN()和OR两种形式查问出世年份在 1990,2000,2010 的用户数据。
mysql> EXPLAIN SELECT * FROM `user` WHERE `birth_year` IN (1990,2000,2010) \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: rangepossible_keys: idx_birth_year_gender key: idx_birth_year_gender key_len: 3 ref: NULL rows: 41 filtered: 100.00 Extra: Using index conditionmysql> EXPLAIN SELECT * FROM `user` WHERE `birth_year`=1990 OR `birth_year`=2000 OR `birth_year`=2010 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: rangepossible_keys: idx_birth_year_gender key: idx_birth_year_gender key_len: 3 ref: NULL rows: 41 filtered: 100.00 Extra: Using index condition4. index_subquery
index_subquery 替换了以下模式的子查问中的 eq_ref 拜访类型，其中 key_column 是非惟一索引。
value IN (SELECT key_column FROM single_table WHERE some_expr)index_subquery 只是一个索引查找函数，它能够齐全替换子查问，进步查问效率。
大多数状况下，应用SELECT子查问时，MySQL查问优化器会主动将子查问优化为联表查问，因而 type 不会显示为 index_subquery。
- 在MySQL查问优化器断定能够对 SELECT 子查问进行优化的状况下，应用子查问与联表查问的执行打算是雷同的。
mysql> EXPLAIN SELECT code FROM user_captcha LEFT JOIN user ON user.phone=user_captcha.receiver WHERE phone like '1888%' \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: rangepossible_keys: unq_phone_country_code key: unq_phone_country_code key_len: 14 ref: NULL rows: 44 filtered: 100.00 Extra: Using where; Using index*************************** 2. row *************************** id: 1 select_type: SIMPLE table: user_captcha partitions: NULL type: eq_refpossible_keys: unq_receiver key: unq_receiver key_len: 257 ref: t.user.phone rows: 1 filtered: 100.00 Extra: Using index conditionmysql> EXPLAIN SELECT code FROM user_captcha WHERE receiver IN (SELECT phone FROM `user` WHERE phone like '1888%') \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: rangepossible_keys: unq_phone_country_code key: unq_phone_country_code key_len: 14 ref: NULL rows: 44 filtered: 100.00 Extra: Using where; Using index; LooseScan*************************** 2. row *************************** id: 1 select_type: SIMPLE table: user_captcha partitions: NULL type: eq_refpossible_keys: unq_receiver key: unq_receiver key_len: 257 ref: t.user.phone rows: 1 filtered: 100.00 Extra: Using index condition- 咱们能够通过在 UPDATE 语句的执行打算中看到 index_subquery。
mysql> EXPLAIN UPDATE user_captcha SET retry_times=1 WHERE receiver IN (SELECT phone FROM `user` WHERE phone like '1888%') \G*************************** 1. row *************************** id: 1 select_type: UPDATE table: user_captcha partitions: NULL type: indexpossible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 22433 filtered: 100.00 Extra: Using where*************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: user partitions: NULL type: index_subquerypossible_keys: unq_phone_country_code key: unq_phone_country_code key_len: 14 ref: func rows: 1 filtered: 100.00 Extra: Using where; Using index5. unique_subquery
unique_subquery 跟 index_subquery 相似，它替换了以下模式的子查问中的 eq_ref 拜访类型，其中 primary_key 能够是主键索引或惟一索引。
value IN (SELECT primary_key FROM single_table WHERE some_expr)unique_subquery 只是一个索引查找函数，它能够齐全替换子查问，进步查问效率。
- 因为MySQL查问优化器会对 SELECT 子查问进行优化，咱们能够在 UPDATE 语句的执行打算中看到 unique_subquery。
mysql> EXPLAIN UPDATE user_captcha SET retry_times=1 WHERE user_id IN (SELECT id FROM `user` WHERE phone like '%1888%') \G*************************** 1. row *************************** id: 1 select_type: UPDATE table: user_captcha partitions: NULL type: indexpossible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 22433 filtered: 100.00 Extra: Using where*************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: user partitions: NULL type: unique_subquerypossible_keys: PRIMARY key: PRIMARY key_len: 4 ref: func rows: 1 filtered: 11.11 Extra: Using where6. index_merge
示意呈现了索引合并优化，通常是将多个索引字段的范畴扫描合并为一个。包含单表中多个索引的交加，并集以及交加之间的并集，但不包含跨多张表和全文索引。
这种优化并非必然产生的，当查问优化器判断优化后查问效率更优时才会进行优化。详情可查看官网文档
- 查问出世年在 1990,2000,2010 年，或 id<1000 的用户数据
mysql> EXPLAIN SELECT * FROM `user` WHERE `birth_year` IN (1990,2000,2010) OR `id`<1000 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: index_mergepossible_keys: PRIMARY,idx_birth_year_gender key: idx_birth_year_gender,PRIMARY key_len: 3,4 ref: NULL rows: 46 filtered: 100.00 Extra: Using sort_union(idx_birth_year_gender,PRIMARY); Using where- 查问手机号以 183 结尾或 出世年 大于1990 年的用户数据
mysql> EXPLAIN SELECT * FROM `user` WHERE `phone` like '183%' OR `birth_year`>1990 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: index_mergepossible_keys: unq_phone_country_code,idx_birth_year_gender key: unq_phone_country_code,idx_birth_year_gender key_len: 14,3 ref: NULL rows: 1105 filtered: 100.00 Extra: Using sort_union(unq_phone_country_code,idx_birth_year_gender); Using where- 查问出世年在 1990 年或 id=1000 的用户数据
mysql> EXPLAIN SELECT * FROM `user` WHERE `birth_year`=1990 OR `id`=1000 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: index_mergepossible_keys: PRIMARY,idx_birth_year_gender key: idx_birth_year_gender,PRIMARY key_len: 3,4 ref: NULL rows: 11 filtered: 100.00 Extra: Using sort_union(idx_birth_year_gender,PRIMARY); Using where1 row in set, 1 warning (0.01 sec)7. ref_or_null
ref_or_null 于 ref 相似，然而MySQL必须对蕴含 NULL 值的行就行额定搜寻。
- 查找昵称是 空字符串 '' 或 NULL 的用户数据
mysql> EXPLAIN SELECT * FROM `user` WHERE `nickname`='' OR `nickname` IS NULL \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: ref_or_nullpossible_keys: idx_nickname key: idx_nickname key_len: 43 ref: const rows: 2 filtered: 100.00 Extra: Using where8. fulltext
命中全文索引时 type 为 fulltext。
9. ref
索引拜访（有时也叫做索引查找），它返回所有匹配某个单个值的行。然而，它可能会找到多个符合条件的行，因而，它是查找和扫描的混合体。此类索引拜访只有当应用非唯一性索引或者唯一性索引的非唯一性前缀时才会产生。把它叫做 ref 是因为索引要跟某个参考值相比拟。这个参考值或者是一个常数，或者是来自多表查问前一个表里的后果值。
- 查找出世年在2000年的用户数据。
mysql> EXPLAIN SELECT * FROM `user` WHERE `birth_year`=2000 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: refpossible_keys: idx_birth_year_gender key: idx_birth_year_gender key_len: 3 ref: const rows: 30 filtered: 100.00 Extra: NULL- 查找电话号码是 18888888888 的用户数据，
phone与country_count联结组成惟一索引unq_phone_country_code，phone是惟一索引unq_phone_country_code的非唯一性前缀。
mysql> EXPLAIN SELECT * FROM `user` WHERE `phone`='18888888888'\G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: refpossible_keys: unq_phone_country_code key: unq_phone_country_code key_len: 14 ref: const rows: 1 filtered: 100.00 Extra: NULL10. eq_ref
当进行等值联表查问时，联结字段命中主键索引或惟一的非空索引时，将应用 eq_ref。
(《高性能MySQL（第3版）》一书中说"应用主键或惟一索引查问时会用 eq_ref"，通过重复测试，并查阅MySQL5.6、5.7版本的官网文档，实际上应用主键或惟一索引进行等值条件查问时 type 会显示 const，《高性能MySQL（第3版）》这里应该是只实用于5.5之前的版本。)
- 以
user_captcha表作为主表，LEFT JOINuser表查问用户数据，因为user表中id字段是主键，所以第二行的user表的 type 为 eq_ref
mysql> EXPLAIN SELECT * FROM `user_captcha` LEFT JOIN `user` ON `user`.`id`=`user_captcha`.`user_id` \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user_captcha partitions: NULL type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 22433 filtered: 100.00 Extra: NULL*************************** 2. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: eq_refpossible_keys: PRIMARY key: PRIMARY key_len: 4 ref: t.user_captcha.user_id rows: 1 filtered: 100.00 Extra: Using where2 rows in set, 1 warning (0.01 sec)- 当应用
user表作为主表，LEFT JOINuser_captcha表时，因为user_captcha表中user_id字段与device_id组成联结惟一索引，user_id并非独立的惟一索引，所以第二行的user_captcha表的 type 为 ref，而并非 eq_ref
mysql> EXPLAIN SELECT * FROM `user` LEFT JOIN `user_captcha` ON `user`.`id`=`user_captcha`.`user_id` \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 22999 filtered: 100.00 Extra: NULL*************************** 2. row *************************** id: 1 select_type: SIMPLE table: user_captcha partitions: NULL type: refpossible_keys: unique_user key: unique_user key_len: 5 ref: t.user.id rows: 1 filtered: 100.00 Extra: Using where11. const
MySQL 晓得查问最多只能匹配到一条符合条件的记录。因为只有一行，所以优化器能够将这一行中的列中的值视为常量。const 表查问十分快，因为它们只读取一次数据行。
通常应用主键或惟一索引进行等值条件查问时会用 const。
- 应用主键查问用户数据
mysql> EXPLAIN SELECT * FROM `user` WHERE `id`=120 \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: constpossible_keys: PRIMARY key: PRIMARY key_len: 4 ref: const rows: 1 filtered: 100.00 Extra: NULL- 应用惟一索引查问用户数据
mysql> EXPLAIN SELECT * FROM `user` WHERE `email`='54222806@qq.com' \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: constpossible_keys: unq_email key: unq_email key_len: 258 ref: const rows: 1 filtered: 100.00 Extra: NULL- 应用联结惟一索引查问用户数据
mysql> EXPLAIN SELECT * FROM `user` WHERE `country_code`=86 AND `phone`='18888888888' \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: user partitions: NULL type: constpossible_keys: unq_phone_country_code key: unq_phone_country_code key_len: 16 ref: const,const rows: 1 filtered: 100.00 Extra: NULL12. system
官网文档原文是：The table has only one row (= system table). This is a special case of the const join type.
该表只有一行（=零碎表）。这是 const 关联类型的特例。
- 从零碎库mysql的零碎表
proxies_priv里查问数据，这里的数据在Mysql服务启动时候曾经加载在内存中，不须要进行磁盘IO。
mysql> EXPLAIN SELECT * FROM `mysql`.`proxies_priv` \G*************************** 1. row *************************** id: 1 select_type: SIMPLE table: proxies_priv partitions: NULL type: systempossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 1 filtered: 100.00 Extra: NULL参考：
《高性能MySQL（第3版）》
MySQL 5.7官网文档：explain-join-types