共计 14524 个字符,预计需要花费 37 分钟才能阅读完成。
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=utf8mb4
user_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=utf8mb4
1. 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: ALL
possible_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: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 22293
filtered: 100.00
Extra: Using where
1 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: ALL
possible_keys: unq_phone_country_code
key: NULL
key_len: NULL
ref: NULL
rows: 22293
filtered: 10.00
Extra: Using where
2. 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: index
possible_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: index
possible_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: index
possible_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: index
possible_keys: NULL
key: idx_birth_year_gender
key_len: 5
ref: NULL
rows: 22748
filtered: 100.00
Extra: Using index
3. 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: range
possible_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: range
possible_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: range
possible_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: range
possible_keys: idx_birth_year_gender
key: idx_birth_year_gender
key_len: 3
ref: NULL
rows: 41
filtered: 100.00
Extra: Using index condition
mysql> 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: range
possible_keys: idx_birth_year_gender
key: idx_birth_year_gender
key_len: 3
ref: NULL
rows: 41
filtered: 100.00
Extra: Using index condition
4. 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: range
possible_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_ref
possible_keys: unq_receiver
key: unq_receiver
key_len: 257
ref: t.user.phone
rows: 1
filtered: 100.00
Extra: Using index condition
mysql> 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: range
possible_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_ref
possible_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: index
possible_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_subquery
possible_keys: unq_phone_country_code
key: unq_phone_country_code
key_len: 14
ref: func
rows: 1
filtered: 100.00
Extra: Using where; Using index
5. 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: index
possible_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_subquery
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: func
rows: 1
filtered: 11.11
Extra: Using where
6. 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_merge
possible_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_merge
possible_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_merge
possible_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 where
1 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_null
possible_keys: idx_nickname
key: idx_nickname
key_len: 43
ref: const
rows: 2
filtered: 100.00
Extra: Using where
8. 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: ref
possible_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: ref
possible_keys: unq_phone_country_code
key: unq_phone_country_code
key_len: 14
ref: const
rows: 1
filtered: 100.00
Extra: NULL
10. 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: ALL
possible_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_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: t.user_captcha.user_id
rows: 1
filtered: 100.00
Extra: Using where
2 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: ALL
possible_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: ref
possible_keys: unique_user
key: unique_user
key_len: 5
ref: t.user.id
rows: 1
filtered: 100.00
Extra: Using where
11. 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: const
possible_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: const
possible_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: const
possible_keys: unq_phone_country_code
key: unq_phone_country_code
key_len: 16
ref: const,const
rows: 1
filtered: 100.00
Extra: NULL
12. 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: system
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: NULL
参考:
《高性能 MySQL(第 3 版)》
MySQL 5.7 官网文档:explain-join-types