type显示访问类型,较为重要。通过它可以了解我们的SQL语句是否优化过、是否达到最佳的效果。
它的值有很多,从最好到最差比较如下:
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
一般而言,我们要保证查询至少达到ranag级别,最好能到达到ref。
分别了解一下几个常见的值的涵义。
system
表只有一行记录,这种情况平时一般不会出现,可以忽略。
const
表示通过索引一次就找到了,用于比较主键或唯一键索引。因为只匹配一行数据,所以很快。比如将主键置于where列表中,MySQL就能将该查询转换为一个常量。
如:explain select * from (select * from emp where id = 1)x;
FROM后的子查询,所以select_type为DERIVED,第2行结果的id值较大,所以优先执行。emp表中id是主键,id=1即只匹配一行数据,所以type的值为const。同时,外层查询从临时表x中查询全部,由于x中只有一行数据,所以type值是system。
eq_ref
唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键或唯一键索引扫描。type的值是eq_ref的查询中用到了索引,但查询只有一条记录匹配。
用两个表简单测试
test1表:
test2表
有以下SQL:
explain select * from test1 t1 ,test2 t2 where t1.id = t2.id;
看其执行计划
t2的type值是ALL,即全表搜索,t1为eq_ref。两表id字段为主键,t1.id=t2.id条件下只一行数据是符合的。
ref
非唯一性索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而,它可能会得到多个符合条件的行,所以它应该属于查找和扫描的混合体。
实例
emp表的数据
在这个表上用job和deptNo字段创建一个复合索引
mysql> create index index1 on emp(job,deptNo);Query OK, 10 rows affected (0.59 sec)
现在要查找deptNo=1的数据(deptNo和job是复合索引,但只使用了一半)
返回了两行,再看一下执行计划:
匹配deptNo=1的值的结果有两行。
现在把索引删除
drop index index1 on emp;
假设emp表的deptNo是test2的id。有以下sql
select * from emp e , test2 t2 where e.deptNo = t2.id;
我们知道结果有两行,其执行计划如下:
emp表的查询type是ref,因为test2表只有一行数据,所有就类似于匹配detpNo的单独的值行。
rang
只搜索给定范围的行。一般就是你在where语句中出现between、、in等查询这种范围扫描,比全表扫描要好,因为它只需要开始于索引的某一个点,而结束另一点,不用扫描全部索引。如以下SQL
select * from emp where id in (1,2);
能够达到rang已经可以了。
index和ALL
index和ALL都是读全表,两者区别在于index类型只遍历索引树,通常比ALL快。
如以下两条SQL:
select id from emp;
select id ,ename from emp;
explain查看如下:
若SQL能达不到rang或以上的话,能达到index也可以。
关于type介绍到此,主要是要了解它的值所代表的涵义,好坏的排列,那我们就可以知道SQL的优劣,以及优化是否有效果。