MySQL 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_group\G
2
3 　　*************************** 1. row ***************************
4
5 　　Table: user_group
6
7 　　Create Table: CREATE TABLE `user_group` (
8
9 　　`user_id` int(11) NOT NULL,
10
11 　　`group_id` int(11) NOT NULL,
12
13 　　`user_type` int(11) NOT NULL,
14
15 　　`gmt_create` datetime NOT NULL,
16
17 　　`gmt_modified` datetime NOT NULL,
18
19 　　`status` varchar(16) NOT NULL,
20
21 　　KEY `idx_user_group_uid` (`user_id`)
22
23 　　) ENGINE=MyISAM DEFAULT CHARSET=utf8
24
25 　　1 row in set (0.00 sec)
26
27 　　sky@localhost : example 11:10:32> show create table group_message\G
28
29 　　*************************** 1. row ***************************
30
31 　　Table: group_message
32
33 　　Create Table: CREATE TABLE `group_message` (
34
35 　　`id` int(11) NOT NULL AUTO_INCREMENT,
36
37 　　`gmt_create` datetime NOT NULL,
38
39 　　`gmt_modified` datetime NOT NULL,
40
41 　　`group_id` int(11) NOT NULL,
42
43 　　`user_id` int(11) NOT NULL,
44
45 　　`author` varchar(32) NOT NULL,
46
47 　　`subject` varchar(128) NOT NULL,
48
49 　　PRIMARY KEY (`id`),
50
51 　　KEY `idx_group_message_author_subject` (`author`,`subject`(16)),
52
53 　　KEY `idx_group_message_author` (`author`),
54
55 　　KEY `idx_group_message_gid_uid` (`group_id`,`user_id`)
56
57 　　) ENGINE=MyISAM AUTO_INCREMENT=97 DEFAULT CHARSET=utf8
58
59 　　1 row in set (0.00 sec)
60
61 　　sky@localhost : example 11:10:43> show create table group_message_content\G
62
63 　　*************************** 1. row ***************************
64
65 　　Table: group_message_content
66
67 　　Create Table: CREATE TABLE `group_message_content` (
68
69 　　`group_msg_id` int(11) NOT NULL,
70
71 　　`content` text NOT NULL,
72
73 　　KEY `group_message_content_msg_id` (`group_msg_id`)
74
75 　　) ENGINE=MyISAM DEFAULT CHARSET=utf8
76
77 　　1 row in set (0.00 sec)

　　使用Query如下：

1 　　select m.subject msg_subject, c.content msg_content
2
3 　　from user_group g,group_message m,group_message_content c
4
5 　　where g.user_id = 1
6
7 　　and m.group_id = g.group_id
8
9 　　and c.group_msg_id = m.id

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

1 　　sky@localhost : example 11:17:04> explain select m.subject msg_subject, c.content msg_content
2
3 　　-> from user_group g,group_message m,group_message_content c
4
5 　　-> where g.user_id = 1
6
7 　　-> and m.group_id = g.group_id
8
9 　　-> and c.group_msg_id = m.id\G
10
11 　　*************************** 1. row ***************************
12
13 　　id: 1
14
15 　　select_type: SIMPLE
16
17 　　table: g
18
19 　　type: ref
20
21 　　possible_keys: user_group_gid_ind,user_group_uid_ind,user_group_gid_uid_ind
22
23 　　key: user_group_uid_ind
24
25 　　key_len: 4
26
27 　　ref: const
28
29 　　rows: 2
30
31 　　Extra:
32
33 　　*************************** 2. row ***************************
34
35 　　id: 1
36
37 　　select_type: SIMPLE
38
39 　　table: m
40
41 　　type: ref
42
43 　　possible_keys: PRIMARY,idx_group_message_gid_uid
44
45 　　key: idx_group_message_gid_uid
46
47 　　key_len: 4
48
49 　　ref: example.g.group_id
50
51 　　rows: 3
52
53 　　Extra:
54
55 　　*************************** 3. row ***************************
56
57 　　id: 1
58
59 　　select_type: SIMPLE
60
61 　　table: c
62
63 　　type: ref
64
65 　　possible_keys: idx_group_message_content_msg_id
66
67 　　key: idx_group_message_content_msg_id
68
69 　　key_len: 4
70
71 　　ref: example.m.id
72
73 　　rows: 2
74
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 比较进行循环查询，才得到最终的结果。没啥特别的，后一个引用前一个的结果集作为条件，实现过程可以通过下图表示：

ChinaUnix-ITpub网站MySQL技术征文大赛参赛作品
作者：简朝阳