MySQL中Join算法實(shí)現(xiàn)原理分析
在MySQL 中，只有一種 Join 算法，就是大名鼎鼎的 Nested Loop Join，他沒有其他很多數(shù)據(jù)庫(kù)所提供的 Hash Join，也沒有 Sort Merge Join。顧名思義，Nested Loop Join 實(shí)際上就是通過驅(qū)動(dòng)表的結(jié)果集作為循環(huán)基礎(chǔ)數(shù)據(jù)，然后一條一條的通過該結(jié)果集中的數(shù)據(jù)作為過濾條件到下一個(gè)表中查詢數(shù)據(jù)，然后合并結(jié)果。如果還有第三個(gè)參與 Join，則再通過前兩個(gè)表的 Join 結(jié)果集作為循環(huán)基礎(chǔ)數(shù)據(jù)，再一次通過循環(huán)查詢條件到第三個(gè)表中查詢數(shù)據(jù)，如此往復(fù)。
還是通過示例和圖解來說明吧，后面將通過我個(gè)人數(shù)據(jù)庫(kù)測(cè)試環(huán)境中的一個(gè) example(自行設(shè)計(jì)，非MySQL 自己提供) 數(shù)據(jù)庫(kù)中的三個(gè)表的 Join 查詢來進(jìn)行示例。
注意：由于這里有些內(nèi)容需要在MySQL 5.1.18之后的版本中才會(huì)體現(xiàn)出來，所以本測(cè)試的MySQL 版本為5.1.26
表結(jié)構(gòu)：
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=InnoDB 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=InnoDB 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=InnoDB DEFAULT CHARSET=utf8
76
77 1 row in set (0.00 sec)
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 的執(zhí)行計(jì)劃：
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 作為驅(qū)動(dòng)表，首先利用我們傳入的條件 user_id 通過 該表上面的索引 user_group_uid_ind 來進(jìn)行 const 條件的索引 ref 查找，然后以 user_group 表中過濾出來的結(jié)果集的 group_id 字段作為查詢條件，對(duì) group_message 循環(huán)查詢，然后再通過 user_group 和 group_message 兩個(gè)表的結(jié)果集中的 group_message 的 id 作為條件 與 group_message_content 的 group_msg_id 比較進(jìn)行循環(huán)查詢，才得到最終的結(jié)果。沒啥特別的，后一個(gè)引用前一個(gè)的結(jié)果集作為條件，實(shí)現(xiàn)過程可以通過下圖表示：
下面的我們調(diào)整一下 group_message_content 去掉上面的 idx_group_message_content_msg_id 這個(gè)索引，然后再看看會(huì)是什么效果：
1 sky@localhost : example 11:25:36> drop index idx_group_message_content_msg_id on group_message_content;
2
3 Query OK, 96 rows affected (0.11 sec)
4
5 sky@localhost : example 10:21:06> explain
6
7 -> select m.subject msg_subject, c.content msg_content
8
9 -> from user_group g,group_message m,group_message_content c
10
11 -> where g.user_id = 1
12
13 -> and m.group_id = g.group_id
14
15 -> and c.group_msg_id = m.id\G
16
17 *************************** 1. row ***************************
18
19 id: 1
20
21 select_type: SIMPLE
22
23 table: g
24
25 type: ref
26
27 possible_keys: idx_user_group_uid
28
29 key: idx_user_group_uid
30
31 key_len: 4
32
33 ref: const
34
35 rows: 2
36
37 Extra:
38
39 *************************** 2. row ***************************
40
41 id: 1
42
43 select_type: SIMPLE
44
45 table: m
46
47 type: ref
48
49 possible_keys: PRIMARY,idx_group_message_gid_uid
50
51 key: idx_group_message_gid_uid
52
53 key_len: 4
54
55 ref: example.g.group_id
56
57 rows: 3
58
59 Extra:
60
61 *************************** 3. row ***************************
62
63 id: 1
64
65 select_type: SIMPLE
66
67 table: c
68
69 type: ALL
70
71 possible_keys: NULL
72
73 key: NULL
74
75 key_len: NULL
76
77 ref: NULL
78
79 rows: 96
80
81 Extra: Using where; Using join buffer
我們看到不僅僅 group_message_content 表的訪問從 ref 變成了 ALL，此外，在最后一行的 Extra信息從沒有任何內(nèi)容變成為 Using where; Using join buffer，也就是說，對(duì)于從 ref 變成 ALL 很容易理解，沒有可以使用的索引的索引了嘛，當(dāng)然得進(jìn)行全表掃描了，Using where 也是因?yàn)樽兂扇頀呙柚螅覀冃枰〉玫?content 字段只能通過對(duì)表中的數(shù)據(jù)進(jìn)行 where 過濾才能取得，但是后面出現(xiàn)的 Using join buffer 是一個(gè)啥呢?
我們知道，MySQL 中有一個(gè)供我們?cè)O(shè)置的參數(shù) join_buffer_size ，這里實(shí)際上就是使用到了通過該參數(shù)所設(shè)置的 Buffer 區(qū)域。那為啥之前的執(zhí)行計(jì)劃中沒有用到呢?
實(shí)際上，Join Buffer 只有當(dāng)我們的 Join 類型為 ALL(如示例中)，index，rang 或者是 index_merge 的時(shí)候 才能夠使用，所以，在我們?nèi)サ?group_message_content 表的 group_msg_id 字段的索引之前，由于 Join 是 ref 類型的，所以我們的執(zhí)行計(jì)劃中并沒有看到有使用 Join Buffer。
當(dāng)我們使用了 Join Buffer 之后，我們可以通過下面的這張圖片來表示 Join 完成過程：
【編輯推薦】
