MySQL的explain之type

一、准备

1.创建表

创建clazz和student表

create table clazz (
    cid int,
    cname varchar(20)
);

create table student(
    sid int,
    sname varchar(20),
    cid int
);

2.创建索引

student表为sid创建唯一索引，为sname和cid创建普通索引

clazz表为cid创建唯一索引

create unique index idx_stu_sid on student(sid);
create index idx_stu_sname on student(sname);
create index idx_stu_cid on student(cid);
create unique index idx_cla_sid on clazz(cid);

3.插入数据

insert into clazz(cid, cname) values (1, 'clazz1'),(2, 'clazz2'),(3, 'clazz3');
insert into student(sid, sname, cid) values(1,'s1',1),(2,'s2',1),(3,'s3',1);

二、type类型

mysql> explain select * from student;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：

system>const>eq_ref>ref>fulltext>ref_or_null>index_merge>unique_subquery>index_subquery>range>index>ALL

三、常见类型

常见的有：system>const>eq_ref>ref>range>index>ALL

一般来说，得保证查询至少达到range级别，最好能达到ref

1.system

system：表只有一行记录(等于系统表)，这是const类型的特列，平时不会出现，这个也可以忽略不计

2.const

const：查找条件是主键或者唯一，并且只匹配一行数据,意味着不用往下扫描了

mysql> explain select * from student where sid=1;
+----+-------------+---------+------------+-------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type  | possible_keys | key         | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+-------+---------------+-------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | const | idx_stu_sid   | idx_stu_sid | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+-------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

3.eq_ref

eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描

4.ref

ref：查找条件使用了索引，并且不是主键或唯一

mysql> explain select * from student where sname='s1';
+----+-------------+---------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_stu_sname | idx_stu_sname | 83      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+---------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

5.range

range：查找条件使用了索引，值是个范围（beween、>等）

mysql> explain select * from student where cid > 2;
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | range | idx_stu_cid   | idx_stu_cid | 5       | NULL |    1 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

6.index

index：这种连接类型只是另外一种形式的全表扫描，只不过它的扫描是按照索引的顺序,all是沿着磁盘扫描，index是沿着索引扫描

索引覆盖：查询的值就是索引的值，不用扫描表

7.ALL

ALL：Full Table Scan，将遍历全表以找到匹配的行