关于mysql:MySQL-Explain命令详解type列详解及案例分析

26次阅读

共计 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 比全索引扫描更高效，因为它用不着遍历全副索引。

范畴扫描 分为以下两种状况：

  1. 范畴条件查问：在 WHERE 子句里带有 BETWEEN、>、<、>=、<= 的查问。
  2. 多个等值条件查问：应用 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 JOIN user 表查问用户数据，因为 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 JOIN user_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

正文完
mysql
发表至： mysql
2020-11-04
 0
关于后端:大白话mysql之深入浅出索引原理-下
关于mysql:MySQL并发控制下的｜事务｜MVCC｜锁机制｜解读
关于mysql:mysql中binlog的使用场景
关于java:如何判断自己适不适合做程序员这几个特点了解一下