仓酷云

标题: 绝无经由的MySQL中Join算法完成道理剖析 [打印本页]

作者: 山那边是海 时间: 2015-1-16 20:12
标题: 绝无经由的MySQL中Join算法完成道理剖析
在我们工作的过程中，经常能听到很多不使用MySQL的理由，虽然其中有一些是对MySQL的误解，不过同样也有一些是合情合理的。在MySQL中，只要一种Join算法，就是赫赫有名的NestedLoopJoin，他没有其他良多数据库所供应的HashJoin，也没有SortMergeJoin。望文生义，NestedLoopJoin实践上就是经由过程驱动表的了局集作为轮回基本数据，然后一条一条的经由过程该了局会合的数据作为过滤前提到下一个表中查询数据，然后兼并了局。假如另有第三个介入Join，则再经由过程前两个表的Join了局集作为轮回基本数据，再一次经由过程轮回查询前提到第三个表中查询数据，云云来去。
仍是经由过程示例和图解来讲明吧，前面将经由过程我团体数据库测试情况中的一个example(自行计划，非MySQL本人供应)数据库中的三个表的Join查询来举行示例。
注重：因为这里有些内容必要在MySQL5.1.18以后的版本中才会表现出来，以是本测试的MySQL版本为5.1.26
表布局：

1sky@localhost:example11:09:32>showcreate[color=#336699]table[/color]user_groupG
2
3***************************1.row***************************
4
5[color=#336699]table[/color]:user_group
6
7Create[color=#336699]table[/color]:CREATE[color=#336699]table[/color]`user_group`(
8
9`user_id`int(11)NOTNULL,
10
11`group_id`int(11)NOTNULL,
12
13`user_type`int(11)NOTNULL,
14
15`gmt_create`datetimeNOTNULL,
16
17`gmt_modified`datetimeNOTNULL,
18
19`status`varchar(16)NOTNULL,
20
21KEY`idx_user_group_uid`(`user_id`)
22
23)ENGINE=MyISAMDEFAULTCHARSET=utf8
24
251rowinset(0.00sec)
26
27sky@localhost:example11:10:32>showcreate[color=#336699]table[/color]group_messageG
28
29***************************1.row***************************
30
31[color=#336699]table[/color]:group_message
32
33Create[color=#336699]table[/color]:CREATE[color=#336699]table[/color]`group_message`(
34
35`id`int(11)NOTNULLAUTO_INCREMENT,
36
37`gmt_create`datetimeNOTNULL,
38
39`gmt_modified`datetimeNOTNULL,
40
41`group_id`int(11)NOTNULL,
42
43`user_id`int(11)NOTNULL,
44
45`author`varchar(32)NOTNULL,
46
47`subject`varchar(128)NOTNULL,
48
49PRIMARYKEY(`id`),
50
51KEY`idx_group_message_author_subject`(`author`,`subject`(16)),
52
53KEY`idx_group_message_author`(`author`),
54
55KEY`idx_group_message_gid_uid`(`group_id`,`user_id`)
56
57)ENGINE=MyISAMAUTO_INCREMENT=97DEFAULTCHARSET=utf8
58
591rowinset(0.00sec)
60
61sky@localhost:example11:10:43>showcreate[color=#336699]table[/color]group_message_contentG
62
63***************************1.row***************************
64
65[color=#336699]table[/color]:group_message_content
66
67Create[color=#336699]table[/color]:CREATE[color=#336699]table[/color]`group_message_content`(
68
69`group_msg_id`int(11)NOTNULL,
70
71`content`textNOTNULL,
72
73KEY`group_message_content_msg_id`(`group_msg_id`)
74
75)ENGINE=MyISAMDEFAULTCHARSET=utf8
76
771rowinset(0.00sec)

复制代码

利用Query以下：

1selectm.subjectmsg_subject,c.contentmsg_content
2
3fromuser_groupg,group_messagem,group_message_contentc
4
5whereg.user_id=1
6
7andm.group_id=g.group_id
8
9andc.group_msg_id=m.id

复制代码

看看我们的Query的实行企图：

[align=left]1sky@localhost:example11:17:04>explainselectm.subjectmsg_subject,c.contentmsg_content
2
3->fromuser_groupg,group_messagem,group_message_contentc
4
5->whereg.user_id=1
6
7->andm.group_id=g.group_id
8
9->andc.group_msg_id=m.idG
10
11***************************1.row***************************
12
13id:1
14
15select_type:SIMPLE
16
17[color=#336699]table[/color]:g
18
19type:ref
20
21possible_keys:user_group_gid_ind,user_group_uid_ind,user_group_gid_uid_ind
22
23key:user_group_uid_ind
24
25key_len:4
26
27ref:const
28
29rows:2
30
31Extra:
32
33***************************2.row***************************
34
35id:1
36
37select_type:SIMPLE
38
39[color=#336699]table[/color]:m
40
41type:ref
42
43possible_keys:PRIMARY,idx_group_message_gid_uid
44
45key:idx_group_message_gid_uid
46
47key_len:4
48
49ref:example.g.group_id
50
51rows:3
52
53Extra:
54
55***************************3.row***************************
56
57id:1
58
59select_type:SIMPLE
60
61[color=#336699]table[/color]:c
62
63type:ref
64
65possible_keys:idx_group_message_content_msg_id
66
67key:idx_group_message_content_msg_id
68
69key_len:4
70
71ref:example.m.id
72
73rows:2
74
75Extra:[/align]

复制代码

我们能够看出，MySQLQueryOptimizer选择了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这个索引，然后再看看会是甚么效果：

1sky@localhost:example11:25:36>dropindexidx_group_message_content_msg_idongroup_message_content;
2
3QueryOK,96rowsaffected(0.11sec)
4
5sky@localhost:example10:21:06>explain
6
7->selectm.subjectmsg_subject,c.contentmsg_content
8
9->fromuser_groupg,group_messagem,group_message_contentc
10
11->whereg.user_id=1
12
13->andm.group_id=g.group_id
14
15->andc.group_msg_id=m.idG
16
17***************************1.row***************************
18
19id:1
20
21select_type:SIMPLE
22
23[color=#336699]table[/color]:g
24
25type:ref
26
27possible_keys:idx_user_group_uid
28
29key:idx_user_group_uid
30
31key_len:4
32
33ref:const
34
35rows:2
36
37Extra:
38
39***************************2.row***************************
40
41id:1
42
43select_type:SIMPLE
44
45[color=#336699]table[/color]:m
46
47type:ref
48
49possible_keys:PRIMARY,idx_group_message_gid_uid
50
51key:idx_group_message_gid_uid
52
53key_len:4
54
55ref:example.g.group_id
56
57rows:3
58
59Extra:
60
61***************************3.row***************************
62
63id:1
64
65select_type:SIMPLE
66
67[color=#336699]table[/color]:c
68
69type:ALL
70
71possible_keys:NULL
72
73key:NULL
74
75key_len:NULL
76
77ref:NULL
78
79rows:96
80
81Extra:Usingwhere;Usingjoinbuffer

复制代码

我们看到不单单group_message_content表的会见从ref酿成了ALL，别的，在最初一行的Extra信息从没有任何内容酿成为Usingwhere;Usingjoinbuffer，也就是说，关于从ref酿成ALL很简单了解，没有可使用的索引的索引了嘛，固然得举行全表扫描了，Usingwhere也是由于酿成全表扫描以后，我们必要获得的content字段只能经由过程对表中的数据举行where过滤才干获得，可是前面呈现的Usingjoinbuffer是一个啥呢?

我们晓得，MySQL中有一个供我们设置的参数join_buffer_size，这里实践上就是利用到了经由过程该参数所设置的Buffer地区。那为啥之前的实行企图中没有效到呢?
实践上，JoinBuffer只要当我们的Join范例为ALL(如示例中)，index，rang大概是index_merge的时分才干够利用，以是，在我们往失落group_message_content表的group_msg_id字段的索引之前，因为Join是ref范例的，以是我们的实行企图中并没有看到有利用JoinBuffer。
当我们利用了JoinBuffer以后，我们能够经由过程上面的这张图片来暗示Join完成历程：

表里面的记录数量越多，这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引，MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。

作者: 只想知道 时间: 2015-1-18 17:08
这是一个不错的新特性。虽然索引的附加字段没有索引键值效率高，但是相对映射到数据表中效率还是提高了很多。我做过试验，在我的实验环境中会比映射到表中提高30%左右的效率。

作者: 莫相离 时间: 2015-1-22 16:43
外键的级联更能扩展可能大部分的同行在设计OLTP系统的时候都不愿意建立外键，都是通过程序来控制父子数据的完整性。

作者: 因胸联盟 时间: 2015-1-31 08:17
光写几个SQL实在叫无知。

作者: 灵魂腐蚀 时间: 2015-2-6 18:57
呵呵，这就是偶想说的

作者: 活着的死人 时间: 2015-2-18 08:26
SQL语言是学习所有数据库产品的基础，无论你是做数据库管理还是做数据库开发都是这样。不过具体学习的侧重点要看你将来做哪一块，如果是做数据库管理（DBA），侧重点应该放在SQLServer的系统管理上.

作者: 老尸 时间: 2015-3-6 02:40
以前的DTS轻盈简单。但是现在的SSIS虽然功能强大了很多，但是总是让人感觉太麻烦。看看论坛中询问SSIS的贴子就知道。做的功能太强大了，往往会有很多用户不会用了

作者: 精灵巫婆 时间: 2015-3-12 19:06
习惯敲命令行的朋友可能会爽一些。但是功能有限。适合机器跑不动SQLServerManagementStudio的朋友使用。

作者: 蒙在股里 时间: 2015-3-20 02:06
对于数据库来说，查询是数据库的灵魂，那么SQL查询效率究竟效率如何呢？下文将带对SQL查询的相关问题进行讨论，供您参考。

欢迎光临仓酷云 (http://ckuyun.com/)