本教程中所使用的数据库的建表语句都在“MySQL高阶教程索引”这篇文章中,点击链接直达:索引&建表语句
摘要:本文主要介绍Explain的核心字段——type
Explain字段解释——type(重点)
1.system & const
System只出现于单表单行(一张表只有一行数据)~基本不可能存在的
2.eq_ref
唯一性索引扫描,对于每一个索引键,表中只有一行数据与之对应,常见于主键或者唯一索引扫描。
3.ref
非唯一的索引扫描,返回匹配单个值的所有行。
为了便于理解,
我们对比着看const、eq_ref、ref、all:
#type = ALL
explain select * from tbl_emp where name = 'z3';
#为name建立唯一索引
create unique index unq_name on tbl_emp(name);
#type = const
explain select * from tbl_emp where name = 'z3';
#type = const, 同上,要匹配的值都是只有一个
explain select * from tbl_emp where name in ('z3');
#type = range, 同上,要匹配的值不止一个
explain select * from tbl_emp where name in ('z3', 'z4');
#type = eq_ref sql92语法的内连接,where条件的两个列都是主键列,所以是eq_ref
explain select * from employees e, departments d where d.department_id = e.employee_id;
#type = ref where条件中有一列不是主键或唯一索引列(department_id是d的主键列,但只是e的普通索引列),所以就变成了ref
explain select * from employees e, departments d where d.department_id = e.department_id;
where后两个字段索引类型与最终type关系:
-
两个都不是索引列,ALL
-
一个是普通索引列,一个不是索引列,ref
-
两个都是普通索引列,ref
-
一个是主键或唯一索引列,一个不是索引列,eq_ref
-
一个是主键或唯一索引列,一个是普通索引列,eq_ref
-
两个都是主键或唯一索引列,eq_ref
由上述可知,当有两个或多个列连接时:
-
没有一个索引列,ALL。
-
有一个或多个普通索引列,但没有主键或唯一索引列,ref。
-
有一个或多个主键或唯一索引列,eq_ref。
但有一个例外:如果有出现的主键或唯一索引列,与普通索引是外键关系的话,比如d.department_id = e.department_id,虽然一个是主键列,一个是普通索引列,但type是ref,而不是eq_ref。
以下是上面每一条结论对应的测试代码,注:departments表中主键列是departments_id,普通索引列是location_id,无索引列是manager_id。 employees表中主键列是employees_id,普通索引列是department_id,无索引列是manager_id。
drop index unq_name on tbl_emp;
create indx idx_name on tbl_emp(name); #为name建立普通索引
explain select * from tbl_emp where name = 'z3'; #type = ref