首页 > mysql > mysql中Join的实现原理及优化思路
2017
04-20

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

实现原理


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


示例


下面我们将通过一个三表 Join 语句示例来说明 MySQL 的 Nested Loop Join 实现方式。

#添加索引
create index idx_group_message_gid_uid on group_message(group_id);
#分析语句
explain select m.subject msg_subject, c.content msg_content 
from user_group g,group_message m,group_message_content c 
where g.user_id = 1 and m.group_id = g.group_id and c.group_msg_id = m.id \G


*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: g

type: ref

possible_keys: user_group_gid_ind,user_group_uid_ind,user_group_gid_uid_ind

key: user_group_uid_ind

key_len: 4

ref: const

rows: 2

Extra:

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: m

type: ref

possible_keys: PRIMARY,idx_group_message_gid_uid

key: idx_group_message_gid_uid

key_len: 4

ref: example.g.group_id

rows: 3

Extra:

*************************** 3. row ***************************

id: 1

select_type: SIMPLE

table: c

type: ref

possible_keys: idx_group_message_content_msg_id

key: idx_group_message_content_msg_id

key_len: 4

ref: example.m.id

rows: 2

Extra:


我们可以看出,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 比较进行循环查询,才得到最终的结果。



这个过程可以通过如下表达式来表示:

for each record g_rec in table user_group that g_rec.user_id=1{ 
    for each record m_rec in group_message that m_rec.group_id=g_rec.group_id{ 
        for each record c_rec in group_message_content that c_rec.group_msg_id=m_rec.id 
        pass the (g_rec.user_id, m_rec.subject, c_rec.content) row 
        combination to output; 
    }
}


下图可以更清晰的标识出实际的执行情况

图片.png

假设我们去掉 group_message_content 表上面的 group_msg_id 字段的索引,然后再看看执行计划会变成怎样

drop index idx_group_message_content_msg_id on group_message_content;

explain select m.subject msg_subject, c.content msg_content 
from user_group g,group_message m,group_message_content c 
where g.user_id = 1
and m.group_id = g.group_id 
and c.group_msg_id = m.id\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: g

type: ref

possible_keys: idx_user_group_uid

key: idx_user_group_uid

key_len: 4

ref: const

rows: 2

Extra:

*************************** 2. row ***************************

id: 1

select_type: SIMPLE

table: m

type: ref

possible_keys: PRIMARY,idx_group_message_gid_uid

key: idx_group_message_gid_uid

key_len: 4

ref: example.g.group_id

rows: 3

Extra:

*************************** 3. row ***************************

id: 1

select_type: SIMPLE

table: c

type: ALL

possible_keys: NULL

key: NULL

key_len: NULL

ref: NULL

rows: 96

Extra: Using where; Using join buffer


我们看到不仅仅 user_group 表的访问从 ref 变成了 ALL,此外,在最后一行的 Extra 信息从没有 任何内容变成为 Using where; Using join buffer,也就是说,对于从 ref 变成 ALL 很容易理解, 没有可以使用的索引的索引了嘛,当然得进行全表扫描了,Using where 也是因为变成全表扫描之后, 我们需要取得的 content 字段只能通过对表中的数据进行 where 过滤才能取得,但是后面出现的 Using join buffer 是一个啥呢?


实际上,这里的 Join 正是利用到了我们通过 join_buffer_size 参数所设置的 Join Buffer。 实际上,Join Buffer 只有当我们的 Join 类型为 ALL(如示例中),index,rang 或者是 index_merge 的时候才能够使用,所以,在我们去掉 group_message_content 表的 group_msg_id 字 段的索引之前,由于 Join 是 ref 类型的,所以我们的执行计划中并没有看到有使用 Join Buffer。

当我们使用了 Join Buffer 之后,我们可以通过下面的这个表达式描述出示例中我们的 Join 完成过程:

for each record g_rec in table user_group{ 
    for each record m_rec in group_message that m_rec.group_id=g_rec.group_id{ 
        put (g_rec, m_rec) into the buffer 
        if (buffer is full) 
        flush_buffer();
    }
}

flush_buffer(){ 
    for each record c_rec in group_message_content that c_rec.group_msg_id = c_rec.id{ 
        for each record in the buffer 
        pass (g_rec.user_id, m_rec.subject, c_rec.content) row combination to output; 
    } 
    empty the buffer; 
}

当然,如果通过类似于上面的图片来展现或许大家会觉得更容易理解一些,如下:

图片.png


通过上面的示例,我想大家应该对 MySQL 中 Nested Join 的实现原理有了一个了解了,也应该清 楚 MySQL 使用 Join Buffer 的方法了。当然,这里并没有涉及到 外连接的内容,实际对于外连接来 说,可能存在的区别主要是连接顺序以及组合空值记录方面。


优化总结

在明白了 MySQL 中 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条件字段已经被索引


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

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

本文》有 0 条评论

留下一个回复