一条SQL语句的执行流程及索引优化

一、总览

1.1 MySQL架构

1.2 SQL语句的执行流程

二、各组件的功能

2.1 连接器

连接器负责与客户端建立连接、获取权限、维持和管理连接。

用户创建会话时，会检查用户的用户名和密码，验证通过后会查询权限表，得到该用户的所有权限。

2.2 分析器

当用户发送sql语句进行数据库操作时，分析器负责对这条sql进行词法分析、语法分析、语义分析。

• 词法分析：将关键字识别出来，将查询条件、字段等都提取出来。
• 语法分析：判断当前sql语句是否符合语法
• 语义分析：判断查找的表是否存在、要操作的字段是否存在等等

2.3 优化器

主要进行执行方案的选择，会选择用不用索引，用哪个索引等等。

2.4 执行器

调用数据库引擎的接口，执行sql。

• 首先判断用户是否有对当前表的操作权限
• 然后调用引擎接口去执行sql

三、语句分析(**)

select * from a left join b on a.id = b.id 
         where a.sex = "男" 
         group by a.age
         having a.age > 16
         order by a.age

//该sql的实际执行顺序是：所以这也就是为什么要小表驱动大表
//(Mysql采用了嵌套循环的方式),小表在前，可以减少两张表的连接次数
from a
on a.id = b.id
left join b
where a.sex = "男"
group by a.age
having a.age > 16
select
order by a.age

a表：

b表：

结果：

查询计划：

树状查询计划：

首先：(由于mysql采取了嵌套循环的形式)
一般sql语句的大致执行流程：

//伪代码形式(仅join部分)
遍历左表的每一行{
   Boolean b = false；
   遍历右表，寻找满足条件的行{
     如果满足where条件{
       合并结果并输出
     }
   }
   b = true;//说明有满足的行
   if(!b){
    说明没有和当前的左表行匹配的右表行
    使用NULL填充
   }
}

但是我们看查询计划，好像是先用where过滤掉了 a.sex = "女"的记录，然后再和b表进行的join。这是为什么呢？到底是先where还是先join呢？

先where还是先join？

我的理解是：这是因为当前a是驱动表，也就是左表，而where条件又是左表的过滤条件，此时先join后where得到的结果和先where后join得到的结果是一样的，此时优化器会先把左表的where条件对左表进行过滤，减少和右表join的次数。此时如果先join，那么得到所有记录，a.sex = "女"的依然还是要过滤掉，但这样会白白进行几次记录的合并(做了无用功)，所以当where条件是驱动表的过滤条件时，会先对驱动表进行where，然后再进行join (逻辑上还是可以认为是先join，后where)

但如果where条件是右表的，那么就需要先join，后where了，因为如果先where的话，会导致逻辑错误，因为只有满足a.id = b.id的记录才会进行合并，如果先用where把一部分记录给过滤掉，那么和a进行合并的时候就会导致很多原本a.id和b.id相等的记录没法合并(b.id对应的那条记录被where过滤掉了),所以要先join,再通过where条件对b进行过滤

EG：
我们改变a，b两张表：
a表：

b表：

sql语句：

SELECT * FROM a LEFT JOIN b ON a.id = b.id  AND a.sex = "男"
WHERE b.name IS NOT NULL

结果：

查询计划：

我们可以看到先扫描a表，然后通过join on 里的a.sex 过滤掉了性别为"女"的记录，然后通过id查找对应b记录，最后才通过where条件的 b.name is not null进行过滤。

如果先执行where 会怎么样呢？ 因为b表里面没有任何一条记录的name字段是NULL，这样在join的时候相当于where不起作用。

总结

对于任何一条sql，如果存在join，其执行逻辑都是先进行join，再用where过滤，只是当where条件为过滤驱动表(左表)记录的条件时，mysql会进行优化，使得where先于join执行以提升效率，如果where的条件是被驱动表(右表),那么此时where必须要在join后执行。此外，mysql使用的嵌套查询，还会优化使用index嵌套查询，即与被驱动表连接的时候，会先访问被驱动表对应的索引以提高效率。

所以最开始的那条SQL语句结合执行计划的流程为：
sql语句：

select * from a left join b on a.id = b.id 
         where a.sex = "男" 
         group by a.age
         having a.age > 16
         order by a.age

• 全表扫描a(因为sex字段没有索引)，得到所有性别为男的记录，然后根据这些记录的a.id去b表查找满足a.id = b.id 条件的列 (b是根据id找的，使用聚簇索引，所以为eq_ref，表示根据a的每一行数据，返回b的一条数据)，进行合并
• 然后进行group by
• 再通过having条件过滤
• 再进行sort排序(这里sort 排序是起作用的，下面的sql语句 sort排序不起作用)
• 如果有limit参数，则从排序好的结果中取limit限制地数目返回。

如果我们在age上添加一个索引，然后改变sql语句为

EXPLAIN
SELECT * FROM a LEFT JOIN b ON a.id = b.id 
         WHERE  a.age > 16
         GROUP BY a.age
         HAVING a.sex = "男"
         order by a.age

查询结果是一样的：

但是执行计划却不同，这里会使用到索引：

添加 format = tree查看树状的执行计划：

这里的执行过程为：

• 先扫描a表通过age这个索引查询出age>16的所有a表的记录
• 再根据这些记录的id去b表中查询id相等的记录进行合并
• 完成left join
• 再group by
• 再执行having过滤
• 这里order by不起作用，因为group by已经对age进行分组并排序了，所以优化器会将无用的order by语句给去掉。

四、索引是如何起作用的

4.1 MySQL执行计划解析

这部分的博客太多太多了，这篇我觉得很详细：

你确定你读懂了 MySQL 执行计划吗？

4.2 优化器是如何找到索引的？

InnoDB引擎会有一个xxx.ibd文件，数据和索引都在该文件中。所以查询某个表时，就可以根据这个文件得到与该表有关的所有索引。

4.3 优化器是如何选择索引的？

MySQL 优化器原来是这样工作的

五、索引的优化

索引创建的情形：

• 经常出现在where或者order by中的字段
• 多表连接查询的关联字段或者外键涉及的字段
• group by的字段

注意：有时候使用索引没有全表扫描高，这是因为当使用的是辅助索引时，需要回表，当表中数据很少时，辅助索引的回表操作导致IO次数变多。

索引的优化原则：

• 对区分度比较大的字段创建索引，即不怎么重复的，如果是重复性比较高的字段，例如性别，只有男和女，给这样的字段加索引，效果不大。

• 最左前缀：当我们创建复合索引时，要使用索引，必须从定义的第一个索引列开始才能匹配索引。即对表a创建复合索引(name,age)，那么要让索引生效，我们在查询时，要name在age前才可以应用到索引。这是因为B+树的节点存储的数据形式为(name,age)，即(name,age)是有序的，单独看name也是有序的，但是age不一定有序，只有当name相同时，age才有序，所以只有当name在age前面时索引才有效。如果想单独age也可以使用索引，那么需要单独为age创建一个索引，这样的话索引数目变多，当更新或者插入时，维护索引的成本就增加了。

  //在a表上创建复合索引 (name,age)
  //会用到索引
  select * from a where name = xxx and age = xxx
  select * from a where name = xxx
  //索引不生效
  select * from a where age = xxx
  
  
  

• 索引列上不要做任何操作(计算、函数、类型转换)，会导致索引失效

  //索引依然为name和age
  select * from a where lower(name) = "aaa"
  //因为对name使用了函数lower，而索引中没有lower(name)相关的数据
  //如果我们创建一个函数索引，那么该查询可以使用索引
  //eg：create unique index lower_name on a(lower(name))
  
  

• 范围查询尽量放到最右边，即最后。因为范围查询后面的字段，即便有索引也不会生效

  //假设在a表上创建复合索引(name,age,account)
  //name和age会应用到索引，account不会应用索引
  select * from a where name = xxx and age > 16 and account = xxx
  //应用到三个索引
  select * from a where name = xxx and account = xx and age >16
  
  

• 尽量使用覆盖索引：即要查询的列是索引列，这样可以不用回表，例如表a,我在name和age两列加了索引，当进行select name,age from a where age > 16这类语句，只查询name、age两个辅助索引就可以得到结果，不需要再回表，效率提升。

  //即要查询的信息在辅助索引中即可以找到
  //虽然用到了索引，但需要回表，因为还有一些别的信息，辅助索引找不到，
  //例如sex等
  select * from a where name = xxx and age > 16
  //不需要回表
  //因为辅助索引中含有name和age信息
  select name,age from a where name = xxx and age >16
  
  

• 可以使用索引来避免排序，可以将排序字段也加入到索引中，group by同理

• like以通配符开头也会使得索引失效，所以最好通配符放在右边。但如果查询字段刚好覆盖索引，那么会进行索引上的全表扫描，即类型为index。

• 尽量少使用or，or连接的字段，只要有一个没有索引，那么就不会走索引，只有当or连接的字段都单独建立了索引，才会走索引。