MySQL多表连接的原理

一、前置概念：驱动表与被驱动表

1.1、什么是驱动表？

字面上理解，如A驱动B，显然A就是驱动者。

在连接查询中，A就称为驱动表，是它“主动”与B表建立连接。特点上，驱动表只会被访问一次。（注意是只访问一次，不是这样多次访问：每次访问记录下访问位置，后面访问又从着个位置继续访问（x）)

1.2、什么是被驱动表？

上面的B显然就是被驱动表。

我们来看一个查询过程，体会一下驱动表与被驱动表的差别。

select * from main_table m inner join vice_table v where m.id = v.id and m.id > 10 and n.id <35;

假设m表为驱动表，那么就会先去查询m表。在根据条件m.id>10获取到满足条件的第一条记录后(假设是一条id为11的记录)，马上去和被驱动表v做匹配，此时v表的查询条件就是：v.id<35 and v.id=11;

不难发现，v.id=11 这个条件，是在获取到驱动表记录后才产生的，因此可以说是一个“被驱动”产生的条件。从中也可以看出，由于这个“被驱动”产生的条件一直在随着驱动表搜索出来的内容在发生变化，因此被驱动表必然需要被多次访问。

 

 

 

1.3、谁是驱动表？

基于驱动表与被驱动表的区别，选择谁作为驱动表就很重要了。一般来讲：小表驱动大表，这是为什么呢？

如果直接从连接次数来看，假设m表作为驱动表记录数为A,n作为被驱动表记录数为B，假设A<B,那么：

从访问表的次数来看：m表访问1次，n表访问A次

从访问记录数来看：m表访问记录A条，n表访问记录A*B次

如果m表和n表的位置反过来，显然：

表的访问次数：n表访问1次，m表访问B次

记录数的访问：n表访问记录B条，m表访问记录B*A次

如果仅仅从两个次数上来看，不难看到，当记录数少的m表作为驱动表时，两个“次数”均小一些。

此外，如果驱动表记录数更大，意味着该表要更长期的占用内存，对内存空间的挑战显然不小。而如果是被驱动表记录数大，我们还可以选择分批载入内存，多次磁盘IO虽然说损耗性能，但至少内存大小不成问题。

 

二、三种连接方式

2.1、简单嵌套循环连接

这个不用我说，前面也讲了，每获取一条驱动表中的记录就立马到被驱动表中做连接。假设只有两张表，那么在连接完成后，就会将这个连接结果缓存起来。

此处的“简单”特指被驱动表没有为搜索字段建立索引。这意味着什么？这意味着要对被驱动表进行多次的全表扫描，性能之差，可想而知。

2.2、索引嵌套循环连接

很容易想到的解决方案是为被驱动表的搜索字段建立索引。你说说，被驱动表都要被访问那么多次了，怎么能连索引都不建呢！

2.3、块嵌套循环连接

不知道小伙伴们有没有一个疑惑，就是为什么不将驱动表中的记录先一次性查询出来，形成一个结果集，再配合连接条件组装多个只与被驱动表有关的条件，最后再去与被驱动表做连接。

其实，这种想法是有一定意义的。但是，有个弊端：如果驱动表的记录也很大，那怎么办，岂不是又要花费很大的一块内存空间来专门存放这个驱动表的查询结果集？

事实上，我们可以退一步来看问题。既然一次性存放全部驱动表的记录太占内存空间，那分几批总行吧！

这就是：join buffer -- 连接缓冲区

专门用来临时存储驱动表的查询结果集。

 

我们会发现，当我们这样做了，如果被驱动表是全表扫描的话，每扫描一条被驱动表的记录，就与join buffer中的结果集进行比对。此时，对被驱动表访问次数就取决于你将驱动表的结果集分为几批。当然，如果被驱动表建立了索引，这个措施就没有意义了。因为此时你是从join buffer的结果集出发，根据索引到被驱动表找记录。显然没有join buffer也是这样干。

上述讲述的块嵌套循环连接是有其重要运用背景的。当被驱动表记录很大很大时，此时为每一个搜索字段都建立上相应的二级索引，未免空间代价太大。所以此时，部分搜索列干脆就不建索引了。但是，如果碰上搜索时真用上了这个没建索引都字段，就要进行多次的全表扫描，岂不是慢死？

此外，当表数量很大时，一次加载全部数据到内存中是做不到的，必须分批加载。这意味着，在每次全表扫描被驱动表时，前面扫完的记录需要从内存空间中释放，以此来为后面还没扫描的内容加载腾出空间。而对被驱动表的访问是多次的，后面又要重复此过程，先前释放掉的页面又要再次加载上来......不敢想这会有多慢！因此，才有了前面缓存部分驱动表结果集的思想。这样做最大的好处就是：

减少了被驱动表的访问次数

进而减少了磁盘IO次数！

 

最后提一嘴：

一般来说，连接查询的效率：

简单<块<索引

所以说，索引yyds。尽量避免用没建索引的列进行搜索 虽说有块嵌套循环连接的优化！