设为首页收藏本站
开启辅助访问 切换到窄版

仓酷云

 找回密码
 立即注册
搜索
热搜: 活动 交友 discuz
仓酷云»社区 › 程序开发 Program › MYSQL数据库开发 › 绝无经由的MySQL中Join算法完成道理剖析
返回列表 发新帖
查看: 838|回复: 8
打印 上一主题 下一主题

[学习教程] 绝无经由的MySQL中Join算法完成道理剖析

[复制链接]
山那边是海 该用户已被删除
跳转到指定楼层
楼主
发表于 2015-1-16 20:12:32 | 只看该作者 回帖奖励 |倒序浏览 |阅读模式

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

您需要 登录 才可以下载或查看，没有帐号？立即注册

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

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

利用Query以下：
  1. 1selectm.subjectmsg_subject,c.contentmsg_content
  2. 2
  3. 3fromuser_groupg,group_messagem,group_message_contentc
  4. 4
  5. 5whereg.user_id=1
  6. 6
  7. 7andm.group_id=g.group_id
  8. 8
  9. 9andc.group_msg_id=m.id
复制代码





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

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

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

相关帖子
回复

使用道具 举报

只想知道 该用户已被删除
沙发
发表于 2015-1-18 17:08:39 | 只看该作者
这是一个不错的新特性。虽然索引的附加字段没有索引键值效率高，但是相对映射到数据表中效率还是提高了很多。我做过试验，在我的实验环境中会比映射到表中提高30%左右的效率。
回复 支持 反对

使用道具 举报

莫相离 该用户已被删除
板凳
发表于 2015-1-22 16:43:11 | 只看该作者
外键的级联更能扩展可能大部分的同行在设计OLTP系统的时候都不愿意建立外键，都是通过程序来控制父子数据的完整性。
回复 支持 反对

使用道具 举报

因胸联盟 该用户已被删除
地板
发表于 2015-1-31 08:17:53 | 只看该作者
光写几个SQL实在叫无知。
回复 支持 反对

使用道具 举报

灵魂腐蚀 该用户已被删除
5#
发表于 2015-2-6 18:57:41 | 只看该作者
呵呵，这就是偶想说的
回复 支持 反对

使用道具 举报

活着的死人 该用户已被删除
6#
发表于 2015-2-18 08:26:57 | 只看该作者
SQL语言是学习所有数据库产品的基础，无论你是做数据库管理还是做数据库开发都是这样。不过具体学习的侧重点要看你将来做哪一块，如果是做数据库管理（DBA），侧重点应该放在SQLServer的系统管理上.
回复 支持 反对

使用道具 举报

老尸 该用户已被删除
7#
发表于 2015-3-6 02:40:20 | 只看该作者
以前的DTS轻盈简单。但是现在的SSIS虽然功能强大了很多，但是总是让人感觉太麻烦。看看论坛中询问SSIS的贴子就知道。做的功能太强大了，往往会有很多用户不会用了
回复 支持 反对

使用道具 举报

精灵巫婆 该用户已被删除
8#
发表于 2015-3-12 19:06:16 | 只看该作者
习惯敲命令行的朋友可能会爽一些。但是功能有限。适合机器跑不动SQLServerManagementStudio的朋友使用。
回复 支持 反对

使用道具 举报

蒙在股里 该用户已被删除
9#
发表于 2015-3-20 02:06:27 | 只看该作者
对于数据库来说，查询是数据库的灵魂，那么SQL查询效率究竟效率如何呢？下文将带对SQL查询的相关问题进行讨论，供您参考。
回复 支持 反对

使用道具 举报

返回列表 发新帖
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|Archiver|手机版|仓酷云 鄂ICP备14007578号-2

GMT+8, 2024-6-19 09:14

Powered by Discuz! X3.2

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表