MySQL连接查询的底层执行逻辑

       在连接查询中，是有驱动表和被驱动表的概念，MySQL会从驱动表依次取出数据去被驱动表中根据条件进行查询过滤，驱动表有多少数据，就对应着到被驱动表进行多少次查询，所以在连接查询的外链接中（左连接，右连接）尽量使驱动表中的数据越少越好，能减少去被驱动表中过滤的次数。内连接mysql则会自动选择数据量最小的表作为驱动表。总的来说就是将连接查询拆除多个单表查询依次执行。

---

以具体例子来看

T1

id	name	age
1	李华	15
2	谢强	8
3	张三	7
4	狗蛋	7

T2

id	course	grades
1	语文	20
2	语文	13
2	数学	15
3	语文	15

sql语句：select * from t1 left join t2 on t1.id=t2.id where t1.id>‘1’ and t2.grades<‘16’

这条sql语句的执行步骤如下

步骤1：选取T1为驱动表，进行只涉及驱动表检索条件的单表查询

（select * from T1 where id>'1'）

下表为驱动表过滤后的结果集：

id	name	age
2	谢强	8
3	张三	7
4	狗蛋	7

步骤2：将步骤1过滤后的结果集依次一条条的去被驱动表中进行匹配，并通过只涉及被驱动表的检索条件进行查询,（在每次查询都需要被驱动表全表扫描的情况下，也并不是一条条的去被驱动表中查询，mysql会使用join buffer来进行优化，这里文章最后会进行解释）步骤1过滤完的数据只剩下三条分别是id为2，id为3和id为4的数据，所以被驱动表会分别进行三次单表查询，根据连接条件T1.id=T2.id所以2，3，4就是被驱动表的查询条件会执行以下三条语句

（select * from T2 where T2.id='2' and T2.grades<16；

  select * from T2 where T2.id='3' and T2.grades<16；

select * from T2 where T2.id='4' and T2.grades<16；

）

步骤3：步骤1执行完的结果集和被驱动表过滤后剩下的结果集进行结合，这里被驱动表可能会出现多条满足的结果，都要分别与相应的驱动表结果集进行结合,如步骤2执行的第一条语句  ：

select * from T2 where T2.id='2' and T2.grades<16   它查询出的结果集有两条

id	course	grades
2	语文	13
2	数学	15

结合后如下：

T1.id	T1.name	T1.age	T2.id	T2.course	T2.grades
2	谢强	8	2	语文	13
2	谢强	8	2	数学	15

同理接着执行 select * from T2 where T2.id='3' and T2.grades<16 和

select * from T2 where T2.id='4' and T2.grades<16

最终全部返回的结果集为

T1.id	T1.name	T1.age	T2.id	T2.course	T2.grades
2	谢强	8	2	语文	13
2	谢强	8	2	数学	15
3	张三	7	3	语文	15
4	狗蛋	7	null	null	null

因为t2中没有检索到id为4的相关数据，所以t2的连接部分全为null，然后进行返回

小结：连接查询步骤其实就是多个单表查询结合来的，先在驱动表上查，然后在依次以驱动表过滤出的数据为条件去被驱动表中进行单表查询，有驱动表过滤出的数据有多少，就在被驱动表中进行多少次的单表查询（所以建议驱动表的数据量尽可能的要少），然后在将合适的数据进行连接返回。这样看来连接查询也只是多个单表查询组成的，所以优化思路其实就是将这几个单表查询进行优化，比如建建索引啥的巴拉巴拉

一些知识点：

1.上文提到的 join buffer ：join buffer叫做连接缓冲区，上文提到如果驱动表中每一条记录，都需要在被驱动表中进行全表扫描的时候，mysql会采用join buffer进行优化，这时就不是驱动表一条一条的去被驱动表中进行查询，而是mysql会一次性加载多条驱动表的数据同时去进行查询，原因是因为mysql读取数据的最小单位是页，mysql会一次性将一页的数据从磁盘读取到内存中，方便下次读数据的时候可以直接从内存中获取，减少磁盘io，但是内存是有限的，在一些大数据量的表中，它需要的数据页是非常多的，把所有页都读进内存是不太现实的，所以mysql在读数据页到内存到一定的阈值时它会喜新厌旧，将旧的数据页进行丢弃，当下次它需要这些旧的数据页时它又嘚重新去磁盘中读取到内存，因为这个特性，mysql在进行全表扫描的时候可能会出现一个情况就是表前面的数据和表较后面的数据两者同时只有一方在内存中，这就导致在被驱动表的每一次单表查询结束后内存中的数据都是表后半段的数据，紧接着下一轮的全表查询又开始了，结果发现内存中的数据全是表的后半段，而它需要的是表的前半段数据，这不又得重新去磁盘中获取相应数据页。针对这个痛点，mysql采用了joinbuffer来一次性将驱动表多条数据去与被驱动表中进行查询过滤，这就是趁单表查询的开始阶段内存中表的前半段数据还在的时候赶快一起去访问，这样就减少了磁盘io，但是在遇到这种需要大数据量被驱动表进行全表扫描的情况下，我们还是尽量的用索引进行优化。

2.在某些特殊情况下，当外连接查询中出现where子句中包含被驱动表的列不为null值的条件的时候，这时候的条件称为空值拒绝（reject-NULL）这时外连接就会变成内连接，比如

select * from t1 left join t2 on t1.id=t2.id where t2.grades is not null (当然这里t2的grades设置为不允许为null) 

相当于

select * from t1 join t2 on t1.id=t2.id