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

最新推荐文章于 2021-03-24 03:33:53 发布

niclascage

最新推荐文章于 2021-03-24 03:33:53 发布

阅读量474

点赞数

分类专栏： mysql 文章标签： mysql 优化 Join

本文链接：https://blog.csdn.net/niclascage/article/details/48009161

版权

mysql 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

Join 的实现原理

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

在 MySQL 中，只有一种 Join 算法，就是大名鼎鼎的 Nested Loop Join，Nested Loop Join 实际上就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。如果还有第三个参与 Join，则再通过前两个表的 Join 结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此往复。

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

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

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

join_buffer

join_buffer_size在mysql官方文档只说明了用于连接的buffer大小，
1,join buffer究竟是存放的什么数据？
2,为什么不使用标准的cache,而对于join操作要单独设置一个buffer?
再来看nest loop的处理过程，由于第二个表可能需要访问多次，可能造成的一个后果就是第二个表对的cache会非常热，从而形成热块，在多个线程执行连接同时去访问相应的的cache时，带来的影响越大. 使用了一个专用的join buffer来存放第二个表以后，至少可以解决以下问题:
1,由于join buffer是存放的基于每thread的连接表信息，这样在连接的时候，只需要访问join buffer就可以了，不需要再去有并发机制保护的cache.
2,join buffer的代码路径更短，执行访问速度更快.