mysql中join源码分析,MySQL中Join算法实现原理分析[图]

最新推荐文章于 2026-02-19 05:33:52 发布

转载最新推荐文章于 2026-02-19 05:33:52 发布 · 456 阅读

本文深入解析MySQL中的Join算法——Nested Loop Join，并通过实例演示不同索引情况下的执行计划及优化策略。

在MySQL 中，只有一种 Join 算法，就是大名鼎鼎的 Nested Loop Join，他没有其他很多数据库所提供的 Hash Join，也没有 Sort Merge Join。顾名思义，Nested Loop Join 实际上就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作

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

还是通过示例和图解来说明吧，后面将通过我个人数据库测试环境中的一个 example(自行设计，非MySQL 自己提供) 数据库中的三个表的 Join 查询来执行示例。

留心：由于这里有些内容须要在MySQL 5.1.18之后的版本中才会体现出来，所以本测试的MySQL 版本为5.1.26

表结构：

1　sky@localhost　:　example　11:09:32>　show　create　table　user_groupG

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

5　Table:　user_group

7　Create　Table:　CREATE　TABLE　`user_group`　(

9　`user_id`　int(11)　NOT　NULL,

11　`group_id`　int(11)　NOT　NULL,

13　`user_type`　int(11)　NOT　NULL,

15　`gmt_create`　datetime　NOT　NULL,

17　`gmt_modified`　datetime　NOT　NULL,

19　`status`　varchar(16)　NOT　NULL,

21　KEY　`idx_user_group_uid`　(`user_id`)

23　)　ENGINE=MyISAM　DEFAULT　CHARSET=utf8

25　1　row　in　set　(0.00　sec)

27　sky@localhost　:　example　11:10:32>　show　create　table　group_messageG

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

31　Table:　group_message

33　Create　Table:　CREATE　TABLE　`group_message`　(

35　`id`　int(11)　NOT　NULL　AUTO_INCREMENT,

37　`gmt_create`　datetime　NOT　NULL,

39　`gmt_modified`　datetime　NOT　NULL,

41　`group_id`　int(11)　NOT　NULL,

43　`user_id`　int(11)　NOT　NULL,

45　`author`　varchar(32)　NOT　NULL,

47　`subject`　varchar(128)　NOT　NULL,

49　PRIMARY　KEY　(`id`),

51　KEY　`idx_group_message_author_subject`　(`author`,`subject`(16)),

53　KEY　`idx_group_message_author`　(`author`),

55　KEY　`idx_group_message_gid_uid`　(`group_id`,`user_id`)

57　)　ENGINE=MyISAM　AUTO_INCREMENT=97　DEFAULT　CHARSET=utf8

59　1　row　in　set　(0.00　sec)

61　sky@localhost　:　example　11:10:43>　show　create　table　group_message_contentG

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

65　Table:　group_message_content

67　Create　Table:　CREATE　TABLE　`group_message_content`　(

69　`group_msg_id`　int(11)　NOT　NULL,

71　`content`　text　NOT　NULL,

73　KEY　`group_message_content_msg_id`　(`group_msg_id`)

75　)　ENGINE=MyISAM　DEFAULT　CHARSET=utf8

77　1　row　in　set　(0.00　sec)

运用 Query如下：

1　select　m.subject　msg_subject,　ntent　msg_content

3　from　user_group　g,group_message　m,group_message_content　c

5　where　g.user_id　=　1

7　and　m.group_id　=　g.group_id

9　and　c.group_msg_id　=　m.id

看看我们的 Query 的执行计划：

1　sky@localhost　:　example　11:17:04>　exp lain　select　m.subject　msg_subject,　ntent　msg_content

3　->　from　user_group　g,group_message　m,group_message_content　c

5　->　where　g.user_id　=　1

7　->　and　m.group_id　=　g.group_id

9　->　and　c.group_msg_id　=　m.idG

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

13　id:　1

15　select_type:　SIMPLE

17　table:　g

19　type:　ref

21　possible_keys:　user_group_gid_ind,user_group_uid_ind,user_group_gid_uid_ind

23　key:　user_group_uid_ind

25　key_len:　4

27　ref:　const

29　rows:　2

31　Extra:

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

35　id:　1

37　select_type:　SIMPLE

39　table:　m

41　type:　ref

43　possible_keys:　PRIMARY,idx_group_message_gid_uid

45　key:　idx_group_message_gid_uid

47　key_len:　4

49　ref:　example.g.group_id

51　rows:　3

53　Extra:

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

57　id:　1

59　select_type:　SIMPLE

61　table:　c

63　type:　ref

65　possible_keys:　idx_group_message_content_msg_id

67　key:　idx_group_message_content_msg_id

69　key_len:　4

71　ref:　example.m.id

73　rows:　2

75　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 比较执行循环查询，才得到最终的结果。没啥特别的，后一个引用前一个的结果集作为条件，实现流程可以通过下图表示：

下面的我们调整一下 group_message_content 去掉上面的 idx_group_message_content_msg_id 这个索引，然后再看看会是什么效果：

1　sky@localhost　:　example　11:25:36>　drop　index　idx_group_message_content_msg_id　on　group_message_content;

3　Query　OK,　96　rows　affected　(0.11　sec)

5　sky@localhost　:　example　10:21:06>　exp lain

7　->　select　m.subject　msg_subject,　ntent　msg_content

9　->　from　user_group　g,group_message　m,group_message_content　c

11　->　where　g.user_id　=　1

13　->　and　m.group_id　=　g.group_id

15　->　and　c.group_msg_id　=　m.idG

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

19　id:　1

21　select_type:　SIMPLE

23　table:　g

25　type:　ref

27　possible_keys:　idx_user_group_uid

29　key:　idx_user_group_uid

31　key_len:　4

33　ref:　const

35　rows:　2

37　Extra:

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

41　id:　1

43　select_type:　SIMPLE

45　table:　m

47　type:　ref

49　possible_keys:　PRIMARY,idx_group_message_gid_uid

51　key:　idx_group_message_gid_uid

53　key_len:　4

55　ref:　example.g.group_id

57　rows:　3

59　Extra:

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

63　id:　1

65　select_type:　SIMPLE

67　table:　c

69　type:　ALL

71　possible_keys:　NULL

73　key:　NULL

75　key_len:　NULL

77　ref:　NULL

79　rows:　96

81　Extra:　Using　where;　Using　join　buffer

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

我们知道，MySQL 中有一个供我们配置的参数 join_buffer_size ，这里实际上就是运用到了通过该参数所配置的 Buffer 区域。那为啥之前的执行计划中没有用到呢?

实际上，Join Buffer 只有当我们的 Join 类型为 ALL(如示例中)，index，rang 或者是 index_merge 的时候才能够运用，所以，在我们去掉 group_message_content 表的 group_msg_id 字段的索引之前，由于 Join 是 ref 类型的，所以我们的执行计划中并没有看到有运用 Join Buffer。

当我们运用了 Join Buffer 之后，我们可以通过下面的这张图片来表示 Join 完成流程：

标签

#mysql中join源码分析