mysql select type_MySQL EXPLAIN type 类型说明

数据库版本(select version();): 5.7.32-log

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

ALL, index, range, ref, eq_ref, const, system, NULL (从左到右, 性能从差到好)

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 字段没有索引.

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是一个虚拟的表, 可以直接忽略.