MySQLjoin实现原理

　　join实现原理
　　MySQl在设计时，采用了这样的思路：针对主要应用场景选择一个或几个性能优异的核心算法作为引擎，然后努力将一些非主要应用场景作为该算法的特例或变种植入到引擎当中。（system>const>ref>ref_eq>range>index>all） join实现原理
　　Join是select的核心，Join是根。 Join buffer：Block Nested-Loop Join (BNL)-----------join buffer 只有当join查询中有表用到以下查询： All：全表扫描， Index：全索引扫描， Range：范围扫描，如 in(),id>18000, index_merge: 的时候，join查询才会用到join buffer。 在MySQL 中只有一种 Join 算法： Nested Loop Join（没有其他很多数据库所提供的 Hash Join，也没有 Sort Merge Join）。
　　驱动表用这个表作为第一个查询的表，其他表在这个基础上递归。
　　Nested Loop Join				Nested Loop Join 就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的 数据作为过滤条件到下一个表中查询数据，然后合并结果。如果还有第三个参与 Join，则再通过前两个表 的 Join 结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此往复。接下来通 过一个三表join查询来说明mysql的Nested Loop Join的实现方式。 select  	m.subject msg_subject, c.content msg_content   from  	user_group g,group_message m,group_message_content c   where  	g.user_id = 1   and  	m.group_id = g.group_id   and  	c.group_msg_id = m.id  ; 使用explain看看执行计划: Explain select  	m.subject msg_subject, c.content msg_content   from  	user_group g,group_message m,group_message_content c   where  	g.user_id = 1   and  	m.group_id = g.group_id   and  	c.group_msg_id = m.id  ; 结果如下： *************************** 1. row ***************************   id: 1   select_type: SIMPLE   table: g   type: ref   possible_keys: user_group_gid_ind,user_group_uid_ind,user_group_gid_uid_ind   key: user_group_uid_ind   key_len: 4   ref: const   rows: 2   Extra:  *************************** 2. row ***************************   id: 1   select_type: SIMPLE   table: m   type: ref   possible_keys: PRIMARY,idx_group_message_gid_uid    key: idx_group_message_gid_uid   key_len: 4   ref: g.group_id  （根据g表的group_id查，所以要先查出g表记录，每条g表符合记录对m表递归） rows: 3   Extra:   *************************** 3. row ***************************   id: 1   select_type: SIMPLE   table: c   type: ref   possible_keys: idx_group_message_content_msg_id   key: idx_group_message_content_msg_id   key_len: 4   ref: m.id  （根据m表的id查，所以要先查出m表记录，每条m表符合条件记录递归c表） rows: 2   Extra:   ******************************************************   从结果可以看出，explain选择user_group作为驱动表，首先通过索引user_group_uid_ind来进行const 条件的索引ref查找，然后用user_group表中过滤出来的结果集group_id字段作为查询条件，对group_message 循环查询，然后再用过滤出来的结果集中的group_message的id作为条件与group_message_content的 group_msg_id进行循环比较查询，获得最终的结果。 这个过程可以通过如下伪代码来表示： for each record g_rec in table user_group that g_rec.user_id=1{      for each record m_rec in group_message that m_rec.group_id=g_rec.group_id{           for each record c_rec in group_message_content that c_rec.group_msg_id=m_rec.id                 pass the (g_rec.user_id, m_rec.subject, c_rec.content) row           combination to output;       } } 如果去掉group_message_content表上面的group_msg_id字段的索引，执行计划会有所不一样。 drop index idx_group_message_content_msg_id on group_message_content; Explain select  	m.subject msg_subject, c.content msg_content   from  	user_group g,group_message m,group_message_content c   where  	g.user_id = 1   and  	m.group_id = g.group_id   and  	c.group_msg_id = m.id  ; 得到的执行计划如下： *************************** 1. row ***************************   id: 1   select_type: SIMPLE   table: g   type: ref   possible_keys: user_group_uid_ind   key: user_group_uid_ind   key_len: 4   ref: const   rows: 2   Extra:   *************************** 2. row ***************************   id: 1   select_type: SIMPLE   table: m   type: ref   possible_keys: PRIMARY,idx_group_message_gid_uid   key: idx_group_message_gid_uid   key_len: 4   ref: g.group_id   rows: 3   Extra:   *************************** 3. row ***************************   id: 1   select_type: SIMPLE   table: c   type: ALL   possible_keys: NULL   key: NULL   key_len: NULL   ref: NULL   rows: 96   Extra:Using where;Using join buffer   ****************************************************** 因为删除了索引，所以group_message_content的访问从ref变成了ALL，keys相关的信息也变成了NULL，Extra信息也变成了Using Where和Using join buffer， 这时获取content内容只能通过对全表的数据进行where过滤才能获取。Using join buffer是指使用到了Cache，只有当join类型为ALL，index，range或者是index_merge 的时候才会使用join buffer，它的使用过程可以用下面代码来表示： for each record g_rec in table user_group{       for each record m_rec in group_message that m_rec.group_id=g_rec.group_id{            put (g_rec, m_rec) into the join buffer            if (buffer is full)                  flush_buffer();       } } flush_buffer(){       for each record c_rec in group_message_content that c_rec.group_msg_id = c_rec.id{             for each record in the join buffer                  pass (g_rec.user_id, m_rec.subject, c_rec.content) row combination to output;       }       empty the buffer; } 在实现过程中可以看到把user_group和group_message的结果集放到join buffer中，而不用每次user_group 和group_message关联后马上和group_message_content关联，这也是没有必要的；需要注意的是 join buffer中只保留查询结果中出现的列值，它的大小不依赖于表的大小，我们在伪代码中看到当join buffer 被填满后，mysql将会flush buffer，flush buffer时将3者做关联并返回到输出output。
　　针对join实现原理想到的->join语句的优化 1、在query中用order by时，尽可能利用已有的索引避免实际的排序计算； 2、在有些 query 的优化过程中，为了避免实际的排序操作而调整索引字段的顺序，甚至是增加索引字段 也是值得的。当然，在调整索引前，同时还需要评估调整该索引对其他 query 所带来的影响，平衡整体得失。 3、用小结果集驱动大结果集，尽量减少join语句中的Nested Loop的循环总次数； 4、优先优化Nested Loop的内层循环，因为内层循环是循环中执行次数最多的，每次循环提升很小的性能 都能在整个循环中提升很大的性能； 5、对被驱动表的join字段上建立索引（前面看到，没有索引的要做全表扫描）； 6、当被驱动表的join字段上无法建立索引的时候，设置足够的Join Buffer Size，当mysql对条件字段进行 排序时，如果需要排序字段的总长度大于该参数的值的时候，mysql就会对需要排序的字段使用临时表进行 分段，这样也会有性能的消耗。