MySQL Join 实现原理分析

时间：2009-05-31 来源：mysql2009

　　还是通过示例和图解来说明吧，后面将通过我个人数据库测试环境中的一个 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技术征文大赛参赛作品
作者：简朝阳