MySQL explain type 连接类型

查看使用的数据库版本

select version();
5.7.30

官方提供的示例数据sakila 下载地址: https://dev.mysql.com/doc/index-other.html

所谓的 type 表示 MySQL 在表中找到所需行的方式， 又称"访问类型"。

type 是一个较为重要的指标，性能从优到劣分别为: NULL > system > const > eq_ref > ref > range > index > all。

1. ALL

全表扫描(Full Table Scan)， MySQL将遍历全表以找到匹配的行。

mysql> explain select * from film where rating ='G';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | film  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |    20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

film 表中 rating 字段没有索引.

ALL 类型查询的性能非常差，我们应该尽可能地避免全表扫描。

2. index

全索引扫描(Full Index Scan)， index 与 ALL 区别为 index 类型只遍历索引树。MYSQL 遍历整个索引来查找匹配的行。

mysql> explain select title from film;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | film  | NULL       | index | NULL          | idx_title | 514     | NULL | 1000 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-------------+

虽然 where 条件中没有用到索引， 但是要取出的列 title 是索引包含的列， 所以只要全扫描 title 索引即可， 直接使用索引树查找数据。

因此如果我们访问的列都包含在索引中，我们应该指定目标列，从而避免回表。

3. range

索引范围扫描， 常见于 '<'， '<='， '>'， '>='， 'between' 等操作符。

mysql> explain select * from film where film_id > 100;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | film  | NULL       | range | PRIMARY       | PRIMARY | 2       | NULL |  900 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

因为 film_id 是索引(此处为主键索引)， 所以只要查找索引的某个范围即可， 通过索引找到具体的数据.

4. ref

使用非唯一性索引或者唯一索引的前缀扫描， 返回匹配某个单独值的记录行。

mysql> explain select * from payment where customer_id = 10;
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys      | key                | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | payment | NULL       | ref  | idx_fk_customer_id | idx_fk_customer_id | 2       | const |   25 |   100.00 | NULL  |
+----+-------------+---------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+

customer_id 在 payment 表中是非唯一性索引

5. eq_ref

类似ref， 区别就在使用的索引是唯一索引。在联表查询中使用 primary key 或者 unique key 作为关联条件。

mysql> explain select * from film a left join film_text b on a.film_id = b.film_id;
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref              | rows | filtered | Extra       |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE      | a     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL             | 1000 |   100.00 | NULL        |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 2       | sakila.a.film_id |    1 |   100.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+------------------+------+----------+-------------+

6. const/system

当 MySQL 对查询某部分进行优化， 并转换为一个常量时， 使用这些类型访问。如将主键置于 where 列表中， MySQL 就能将该查询转换为一个常量， system 是 const 类型的特例， 当查询的表只有一行的情况下使用 system。

mysql> explain select * from film where film_id = 1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | film  | NULL       | const | PRIMARY       | PRIMARY | 2       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+

7. NULL

MySQL 不用访问表或者索引就直接能到结果。

mysql> explain select 1 from dual where 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------+

dual是一个虚拟的表， 可以直接忽略。

小结

我列了一张表，小结如下：

Type(连接类型)	说明
system	表只有一行
const	表最多只有一行匹配，通常用于主键或者唯一索引比较时
eq_ref	每次与之前的表合并行都只在该表读取一行（除system，const之外最好的特点是使用=， 而且索引的所有部分都参与join且索引是主键或非空唯一键的索引 ）
ref	每次只匹配少数行，使用=或<=>，可以是左覆盖索引或非唯一键
range	常数值的范围
index	a.当查询是索引覆盖的，即所有数据均可从索引树获取（Extra中有Using Index）； b.以索引顺序从索引中查找数据行的全表扫描（无 Using Index）； c.如果Extra中Using Index与Using Where同时出现的话，则是利用索引查找键值的意思； d.如单独出现，则是用读索引来代替读行，但不用于查找
all	全表扫描
index_merge	表示出现了索引合并优化(包括交集，并集以及交集之间的并集)，但不包括跨表和全文索引。 这个比较复杂，目前的理解是合并单表的范围索引扫描（如果成本估算比普通的range要更优的话）
unique_subquery	在in子查询中，就是value in (select...)把形如“select unique_key_column”的子查询替换 PS：所以不一定in子句中使用子查询就是低效的！
index_subquery	同上，但把形如”select non_unique_key_column“的子查询替换