mysql join原理和优化 | idouba

mysql join原理和优化

一、关于

在MySQL 中,只有一种Join 算法,就是大名鼎鼎的Nested Loop Join,他没有其他很多数据库所提供的Hash Join,也没有Sort Merge Join。顾名思义,Nested Loop Join 实际上就是通过驱动表的结果集作为循环基础数据,然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据,然后合并结果。如果还有第三个参与Join,则再通过前两个表的Join 结果集作为循环基础数据,再一次通过循环查询条件到第三个表中查询数据,如此往复。

二、原理

参考的query为:

在作者前面的例子中,其中在user_group的group_id、user_id列上,在 group_message的 id列上,在group_message的group_id列上,在group_message_content的group_msg_id列上都是已经有索引的。即上面的query的where字句涉及到的三个表的五个列上都是有索引的。

通过考察在group_message_content的group_msg_id列上有和没有索引时候的join的执行来分析join的执行。

?1. join的列上有索引。

?explain的结果为:

根据explain的结果可以看出其执行过程是:MySQL Query Optimizer 选择了user_group 作为驱动表,首先利用传入的条件user_id 通过该表上面的索引user_group_uid_ind 来进行const 条件的索引ref 查找,然后以user_group 表中过滤出来的结果集的group_id 字段作为查询条件,对group_message 循环查询,然后再通过user_group 和group_message 两个表的结果集中的group_message 的id 作为条件与group_message_content 的group_msg_id 比较进行循环查询,才得到最终的结果。

伪代码描述下,就是三层循环:

作者还生怕伪代码描述不清楚,一个图示:

join-with-index

2. join的列上无索引

group_message_content 表上面的group_msg_id 字段的索引drop掉:

同样的语句执行explain的结果:

观察到不仅仅group_message_content 表(作者原文此处写成了user_group,应该是笔误)表的访问从ref 变成了ALL,此外,在最后一行的Extra 信息从没有任何内容变成为Using where; Using join buffer,对于从ref 变成ALL 很容易理解,没有可以使用的索引的索引,当然得进行全表扫描了,Using where 也是因为变成全表扫描之后,我们需要取得的content 字段只能通过对表中的数据进行where 过滤才能取得。更重要的从explain的extra中引出了关于Using join buffer的解释。

实际上,Join Buffer (join_buffer_size 参数所设置的Join Buffer)只有当的Join 类型为ALL,index,rang 或者是index_merge 的时候才能够使用(非ref类型,即不能直接ref的),在去掉group_message_content 表的group_msg_id 字段的索引之前,由于Join 是ref 类型的,执行计划中并没有看到有使用Join Buffer。

在不能直接ref,必须要使用join buffer后,join执行的伪代码为:

图示:

join-without-index

我的注解:比较上面一个在join的列上有索引的情况,如果有索引,则直接join,因为没有索引只能做全表扫描,而对于链接来说,前面的执行结果要先缓存下。

?三、优化

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

如何减少Nested Loop 的循环总次数?最有效的办法只有一个,那就是让驱动表的结果集尽可能的小,即“永远用小结果集驱动大的结果集”。

因为驱动结果集越大,意味着需要循环的次数越多,也就是说在被驱动结果集上面所需要执行的查询检索次数会越多。比如,当两个表(表A 和表B) Join 的时候,如果表A 通过WHERE 条件过滤后有10 条记录,而表B 有20 条记录。如果我们选择表A 作为驱动表,也就是被驱动表的结果集为20,那么我们通过Join 条件对被驱动表(表B)的比较过滤就会有10 次。反之,如果我们选择表B 作为驱动表,则需要有20 次对表A 的比较过滤。

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

2. 优先优化Nested Loop 的内层循环

不仅仅是在数据库的Join 中应该做的,实际上在优化程序语言的时候也有类似的优化原则。内层循环是循环中执行次数最多的,每次循环节约很小的资源,在整个循环中就能节约很大的资源。

3. 保证Join 语句中被驱动表上Join 条件字段已经被索引

保证被驱动表上Join 条件字段已经被索引的目的,正是针对上面两点的考虑,只有让被驱动表的Join 条件字段被索引了,才能保证循环中每次查询都能够消耗较少的资源,这也正是优化内层循环的实际优化方法。

4. 当无法保证被驱动表的Join 条件字段被索引且内存资源充足的前提下,不要太吝惜JoinBuffer 的设置

当在某些特殊的环境中,我们的Join 必须是All,Index,range 或者是index_merge 类型的时候,Join Buffer 就会派上用场了。在这种情况下,Join Buffer 的大小将对整个Join 语句的消耗起到非常关键的作用。

四、参照

《MySQL性能调优与架构设计》

原创文章。为了维护文章的版本一致、最新、可追溯,转载请注明: 转载自idouba

本文链接地址: mysql join原理和优化


,

No comments yet.

发表评论