再谈MySQL(三):该不该使用join?如何优化join?

最近想起去年面试的时候被问到的一个数据库相关的问题：

该不该使用join语句？如何优化join语句？

当时仅仅知道join的用法，啥底层啥优化自然是一头雾水，当时也被问懵逼了，现在写个博客来记录下这个问题。

join语句是多表连接查询，连接的两个表分为驱动表和被驱动表。

如

SELECT t1.name,t2.age FROM t1 JOIN t2 WHERE t1.id=t2.id

（其中两个表的id字段都设有索引）

一般来说，数据库会先对表t1进行一次全表扫描，将查得的所有数据到表t2中进行一一比对，最后得到结果集。其中t1表叫做驱动表，t2表叫做被驱动表。于是join语句的执行也分两种情况：

• 被驱动表上的索引可以被使用Index Nested-Loop

这种情况下，数据库首先会到驱动表进行一次全表扫描，这是不可避免的。而到被驱动表中进行比对的时候，因为被驱动表的索引被使用到，所以进行的是树扫描而不是全表扫描。设驱动表的数据量为N，被驱动表的数据量是M，那么此次扫描的时间复杂度就是N+logM。

所以很明显，在这种情形下，尽量使得小表作为驱动表，大表作为被驱动表，这样树搜索带来的收益会更高。

这种能使用索引的情况，使用join是没啥问题的

• 被驱动表上的索引不能用Block Nested-Loop

这种情况下，就不可避免的要进行两次全表扫描了。再加上比对数据获取结果集，这个过程的事件复杂度是N*M。这个就叫作Block Nested-Loop。如果是数据量非常大的两个表进行这样的连表查询，那么速度就慢如蜗牛甚至直接把连接给干爆了。所以说这种用不了索引的情况，还是别使用join，在实际开发中最好不要出现。所以在执行join的时候最好explain一下，看看有没有出现Block Nested-Loop现象，如果有的话，一般是不要直接执行的。

如果非要这样执行，数据库也有一定的优化手段。数据库在面临Block Nested-Loop的时候会维护一个叫join_buffer的缓存区。在进行驱动表扫描的时候，将扫描结果一一放在join_buffer之中，然后一起拿去和被驱动表进行比对。那么这样就避免了每次在驱动表中查出一行，就要拿去和被驱动表进行比对的操作。减少了磁盘操作，换为了内存操作，对性能进行优化。

还有一个疑问，如果join_buffer的空间不足以存下驱动表的所有数据怎么办？其实也很简单，只要分次来读取就可以了。所以说join_buffer设置得越小，和驱动表与被驱动表的交互次数也越多，性能优化的效果也越低。所以正确设置join_buffer的大小也是对Block Nested-Loop进行调优的关键。

再多说两点。

一

其实在Index Nested-Loop下也是有join_buffer这个概念的。它的作用主要是对驱动表结果集进行一次排序。

试想一下，对驱动表进行一次全表扫描，结果再一一拿去在被驱动表上进行树搜索，那么每次搜索都是近乎一次随机搜索，这样的话效率不够高。

而此时join_buffer将在驱动表查询到的结果存放起来，并经过一次排序之后再去访问被驱动表来进行树搜索，那么这样查找的数据在B+树上是近乎连续的(因为索引本来就是组织有序的)，这样Index Nested-Loop经过join_buffer储存并排序之后再进行树搜索，也能对性能进行优化

二

要尽量避免Block Nested-Loop的出现，方法一般来说可以在被驱动表上加上索引，然后转化为Index Nested-Loop，优化性能。

但是不一定所有场景都适用。如果这是条数据量巨大，但执行机会很少的语句，也要专门因为该语句维护一个索引吗？很明显是不划算的。

这样的场景可以用临时表来解决，建立一个临时表，将被驱动表中符合条件的数据读进来(此时也可能会扫描全表，但是总不至于再乘上驱动表的行数)，然后在临时表中建立相关索引，再把临时表当作新的驱动表来执行相关语句就可以啦~