Mysql explain知识点总结

背景

索引是一门学问，更是实际经验的体现。

最近项目中对索引进行了一些优化，有了些新的认识。对索引的使用不仅仅是where条件中符合了最左匹配原则这么简单，首先需要看懂explain的执行计划，由于explain涉及的信息量非常多，可组合的场景也非常多，本文主要通过一个多表关联的示例对explain的结果进行简要分析。

示例数据库使用mysql官方提供的employees库，获取地址https://github.com/datacharmer/test_db

employees的表结构很简单，包括部门、员工、员工所属部门、部门负责人、员工职位以及员工薪水

示例1：查询d001部门的所有员工

explain 
select b.dept_name,a.emp_no,a.first_name,a.last_name
  from employees a, departments b ,dept_emp c
 where a.emp_no = c.emp_no
   and b.dept_no = c.dept_no
   and b.dept_no = 'd001'

1. 3条记录
   因为关联了3张表
2. id
   explain如果有多条结果，但id都为1，表示只有一个查询但关联了多张表
3. table
   查询中涉及的表，如果有别名就显示别名，例如这里显示的a、b、c
   计划的顺序是b>c>a,与我们sql中的a>b>c不一致
   是由这3个表的关联关系决定的，因为我们要查询d001部门的员工，所以b在第一步，
   部门对应的员工在c中，所以c在第二步
   在c中拿到了emp_no,就要从a中取出员工信息，所以a在第三步
4. type
   对索引的使用的类型
   b的type为const
   const表示通过索引字段最多只匹配到一条数据，符合这个要求的就是主键或者唯一索引
   因为where条件dept_no是b的主键
   
   c的type为ref
   ref表示通过索引能检索少数数据，匹配度比较高
   c表使用的字段dept_no是普通索引，且过滤性比较高
   
   a的type为eq_ref
   b和a都是使用的PRIMARY主键字段查询，但b的type是const、a的type是eq_ref
   eq_ref也是作用于主键或者唯一索引的关联类型，看上去与const一样
   区别在于const所对应的索引字段是明确赋值的常量，而eq_ref是表关联的
   使用的另一个表的字段赋值。在上面的示例中b.dept_no=‘d001’，a.emp_no=c.emp_no印证了这一点
5. possible_keys
   表示可能会用到的索引
   b、a都是走主键
   c出现了主键和dept_no两个索引可用
   因为where中对c使用了emp_no和dept_no两个字段，这两个字段恰好是c的主键
6. key
   表示实际使用的索引
   b、a直接使用主键索引很好理解，而c用dept_no普通索引
   从sql的第5行可以看到b.dept_no = c.dept_no、而第6行b.dept_no = ‘d001’，所以mysql会优化为c.dept_no=‘d001’
7. key_len
   表示所有索引的长度
   b、c都是使用的dept_no char(4)，1个字符等于3字节，所以4*3=12
   a使用的是emp_no int，int类型是4字节
8. ref
   表示与key中的索引字段进行比较的列或者常量
   同b、c索引字段比较的都是固定的d001，即常量，索引ref为const
   同a索引比较的是c.emp_no
9. rows
   表示对该表可能需要检查的行数
   b、a都是为1，是因为b的条件dept_no = 'd001’只能匹配一行数据
   a的条件是a.emp_no = c.emp_no,emp_no是a的主键，所以也只能匹配一行数据
   c的检查行数是38276，因为一个部门关联多个用户，所以数据量比较多
   但实际执行select count(1) from dept_emp where dept_no='d001’;
   查看只有20211行，所以rows只是一个估算行数，否则sql查询的表越大，explain的结果输出就越慢了
10. filtered
    百分比，以rows为基数估算有多少需要过滤的行，100表示不会对行进行过滤
    3个表都是100，因为条件都走的索引，没有其他过滤条件，所以不会进一步过滤
11. Extra
    包含执行计划的其他说明
    b、a为null，说明没有什么其他的补充信息了
    c包含Using where; Using index
    Using where表示限制行与其他表的关联，在此例中可以直接确定c.dept_no=‘d001’，这样就缩小了对c的取数
    Using index表示通过索引获取数据不用回表查询，因为要获取c.emp_no给a关联使用，所以c.emp_no的获取是直接从索引中获取的

示例2：查询d001部门员工编号为10108的员工

explain 
select b.dept_name,a.emp_no,a.first_name,a.last_name
  from employees a, departments b ,dept_emp c
 where a.emp_no = c.emp_no
   and b.dept_no = c.dept_no
   and b.dept_no = 'd001'
   and a.emp_no = '10108'

此sql与示例1仅多了一个条件a.emp_no = ‘10108’，explain结果与示例1有很大变化

1. table
   表的顺序发了变化，是顺着来的a>b>c
   因为这次条件增加了emp_no直接可以确定一个员工，所以a放在第一个
2. type
   a的type有eq_ref变成了const
   因为emp_no是直接赋值的常量条件，不再是从其他表字段的动态条件
3. key
   c的key由dept_no变成了PRIMARY,c的主键是emp_no+dept_no
   因为刚好条件中这两个字段都有明确值，所以顺理成章的使用主键索引直接确定一行
4. ref
   a的ref从employees.c.emp_no变成了const
   因为a的关联字段emp_no是常量条件
   
   c的ref有一个const变成了两个const
   因为这次匹配是主键索引，而c的主键是两个字段组成的
5. rows
   c的rows由3w多行变成了1行
   因为c匹配到了emp_no+dept_no主键索引，当然只能匹配1行了
6. Extra
   c的extra少了Using where
   因为rows只有1行，不用过滤了

示例3：查询d001部门员工编号大于10108的员工

explain 
select b.dept_name,a.emp_no,a.first_name,a.last_name
  from employees a, departments b ,dept_emp c
 where a.emp_no = c.emp_no
   and b.dept_no = c.dept_no
   and b.dept_no = 'd001'
   and a.emp_no > '10108'

此sql同示例2相比，仅将emp_no的等于改成了大于号

1. type
   c的type为range
   直接看sql有range查询的是a.emp_no > ‘10108’，但emp_no是指定的a表，为什么c的type会是range呢
   因为mysql将a.emp_no > ‘10108’优化为了c.emp_no > ‘10108’,所以对c来说是range查询了
2. key
   c为什么选择使用dept_no索引，而不是主键索引
   因为dept_no的区分度更高，能过滤更多数据