mysql Query 的优化—Join的实现原理及优化思路

Join 的实现原理

在寻找 Join 语句的优化思路之前，我们首先要理解 MySQL 中是如何来实现 Join 的，只要理解了实现原理之后，优化就比较简单了。

在 MySQL 中，只有一种 Join 算法，减少大名鼎鼎的 Nested Loop Join , Nested Loop Join 实际上就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。如果还有第三个参与 Join ，则通过前两个表的 Join 结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，第四个 Join 、第五个 Join 都是按照前面的结果集作为循环的基础数据，再去通过循环查询得到最终的数据，以此类推。

join 语句的优化

尽可能减少 Join 语句中的 Nested Loop 的循环总次数；

如何减少 Nested Loop 的循环次数？最有效的办法只有一个，那就是让驱动表的结果集尽可能的小，这也正是在本章第二节中的优化基本原则之一 “永远用小结果集驱动大的结果集”。

当然，此优化的前提条件是通过 Join 条件对各表的每次访问的资源消耗差别不是太大。如果访问存在较大的差别的时候（一般都是因为索引的区别），我们就不能简单的通过结果集的大小来判断需要 Join 语句的驱动顺序，而是要通过比较循环次数和每次循环所需的消耗的乘积的大小来得到如何驱动更优化。

1. 优先优化 Nested Loop 的内层循环；
2. 保证 Join 语句中被驱动表上 Join 条件字段已经被索引；
3. 当无法保证被驱动表的 Join 条件字段被索引且内存资源充足的前提下，不要太吝啬 JoinBuffer 的设置；

join_buffer

join_buffer_size 在MySQL官方文档只说明了用于连接的buffer大小；

1. Join Buffer 究竟是存放的什么数据？
2. 为什么不使用标准的 cache ，而对 Join 操作要单独设置一个 Buffer ?

再来看 Nested Loop 的处理过程，由于第二个表可能需要访问多次，可能造成的一个后果就是第二个表对的 cache 会非常热，从而形成热块，在多个线程执行连接同时去访问相应的 cache 时，带来的影响越大。使用了一个专用的 Join Buffer 来存放第二个表以后，至少可以解决以下问题：

1. 由于 Join Buffer 是存放的基于每 thread 的连接表信息，这样在连接的时候，只需要访问 Join Buffer 就可以了，不需要再去有并发机制保护的 cache 。
2. Join Buffer 的代码路径更短，执行访问速度更快。