MySQLjoin实现原理

　　join实现原理
　　MySQl在设计时，采用了这样的思路：针对主要应用场景选择一个或几个性能优异的核心算法作为引擎，然后努力将一些非主要应用场景作为该算法的特例或变种植入到引擎当中。（systemconstrefrefeqrangeindexall）join实现原理
　　Join是select的核心，Join是根。Joinbuffer：BlockNestedLoopJoin（BNL）joinbuffer只有当join查询中有表用到以下查询：All：全表扫描，Index：全索引扫描，Range：范围扫描，如in（），id18000，indexmerge：的时候，join查询才会用到joinbuffer。在MySQL中只有一种Join算法：NestedLoopJoin（没有其他很多数据库所提供的HashJoin，也没有SortMergeJoin）。
　　驱动表用这个表作为第一个查询的表，其他表在这个基础上递归。
　　NestedLoopJoinNestedLoopJoin就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。如果还有第三个参与Join，则再通过前两个表的Join结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此往复。接下来通过一个三表join查询来说明mysql的NestedLoopJoin的实现方式。selectm。subjectmsgsubject，c。contentmsgcontentfromusergroupg，groupmessagem，groupmessagecontentcwhereg。userid1andm。groupidg。groupidandc。groupmsgidm。id；使用explain看看执行计划：Explainselectm。subjectmsgsubject，c。contentmsgcontentfromusergroupg，groupmessagem，groupmessagecontentcwhereg。userid1andm。groupidg。groupidandc。groupmsgidm。id；结果如下：1。rowid：1selecttype：SIMPLEtable：gtype：refpossiblekeys：usergroupgidind，usergroupuidind，usergroupgiduidindkey：usergroupuidindkeylen：4ref：constrows：2Extra：2。rowid：1selecttype：SIMPLEtable：mtype：refpossiblekeys：PRIMARY，idxgroupmessagegiduidkey：idxgroupmessagegiduidkeylen：4ref：g。groupid（根据g表的groupid查，所以要先查出g表记录，每条g表符合记录对m表递归）rows：3Extra：3。rowid：1selecttype：SIMPLEtable：ctype：refpossiblekeys：idxgroupmessagecontentmsgidkey：idxgroupmessagecontentmsgidkeylen：4ref：m。id（根据m表的id查，所以要先查出m表记录，每条m表符合条件记录递归c表）rows：2Extra：从结果可以看出，explain选择usergroup作为驱动表，首先通过索引usergroupuidind来进行const条件的索引ref查找，然后用usergroup表中过滤出来的结果集groupid字段作为查询条件，对groupmessage循环查询，然后再用过滤出来的结果集中的groupmessage的id作为条件与groupmessagecontent的groupmsgid进行循环比较查询，获得最终的结果。这个过程可以通过如下伪代码来表示：foreachrecordgrecintableusergroupthatgrec。userid1｛foreachrecordmrecingroupmessagethatmrec。groupidgrec。groupid｛foreachrecordcrecingroupmessagecontentthatcrec。groupmsgidmrec。idpassthe（grec。userid，mrec。subject，crec。content）rowcombinationtooutput；｝｝如果去掉groupmessagecontent表上面的groupmsgid字段的索引，执行计划会有所不一样。dropindexidxgroupmessagecontentmsgidongroupmessagecontent；Explainselectm。subjectmsgsubject，c。contentmsgcontentfromusergroupg，groupmessagem，groupmessagecontentcwhereg。userid1andm。groupidg。groupidandc。groupmsgidm。id；得到的执行计划如下：1。rowid：1selecttype：SIMPLEtable：gtype：refpossiblekeys：usergroupuidindkey：usergroupuidindkeylen：4ref：constrows：2Extra：2。rowid：1selecttype：SIMPLEtable：mtype：refpossiblekeys：PRIMARY，idxgroupmessagegiduidkey：idxgroupmessagegiduidkeylen：4ref：g。groupidrows：3Extra：3。rowid：1selecttype：SIMPLEtable：ctype：ALLpossiblekeys：NULLkey：NULLkeylen：NULLref：NULLrows：96Extra：Usingwhere；Usingjoinbuffer因为删除了索引，所以groupmessagecontent的访问从ref变成了ALL，keys相关的信息也变成了NULL，Extra信息也变成了UsingWhere和Usingjoinbuffer，这时获取content内容只能通过对全表的数据进行where过滤才能获取。Usingjoinbuffer是指使用到了Cache，只有当join类型为ALL，index，range或者是indexmerge的时候才会使用joinbuffer，它的使用过程可以用下面代码来表示：foreachrecordgrecintableusergroup｛foreachrecordmrecingroupmessagethatmrec。groupidgrec。groupid｛put（grec，mrec）intothejoinbufferif（bufferisfull）flushbuffer（）；｝｝flushbuffer（）｛foreachrecordcrecingroupmessagecontentthatcrec。groupmsgidcrec。id｛foreachrecordinthejoinbufferpass（grec。userid，mrec。subject，crec。content）rowcombinationtooutput；｝emptythebuffer；｝在实现过程中可以看到把usergroup和groupmessage的结果集放到joinbuffer中，而不用每次usergroup和groupmessage关联后马上和groupmessagecontent关联，这也是没有必要的；需要注意的是joinbuffer中只保留查询结果中出现的列值，它的大小不依赖于表的大小，我们在伪代码中看到当joinbuffer被填满后，mysql将会flushbuffer，flushbuffer时将3者做关联并返回到输出output。
　　针对join实现原理想到的join语句的优化1、在query中用orderby时，尽可能利用已有的索引避免实际的排序计算；2、在有些query的优化过程中，为了避免实际的排序操作而调整索引字段的顺序，甚至是增加索引字段也是值得的。当然，在调整索引前，同时还需要评估调整该索引对其他query所带来的影响，平衡整体得失。3、用小结果集驱动大结果集，尽量减少join语句中的NestedLoop的循环总次数；4、优先优化NestedLoop的内层循环，因为内层循环是循环中执行次数最多的，每次循环提升很小的性能都能在整个循环中提升很大的性能；5、对被驱动表的join字段上建立索引（前面看到，没有索引的要做全表扫描）；6、当被驱动表的join字段上无法建立索引的时候，设置足够的JoinBufferSize，当mysql对条件字段进行排序时，如果需要排序字段的总长度大于该参数的值的时候，mysql就会对需要排序的字段使用临时表进行分段，这样也会有性能的消耗。