mysqljoin和leftjoin对于索引的问题
今天遇到⼀个left join优化的问题，搞了⼀下午，中间查了不少资料，对MySQL的查询计划还有查询优化有了更进⼀步的了解，做⼀个简单的记录：
select c.* from hotel_info_original c
left join hotel_info_collection h
on c.hotel_type=h.hotel_type and c.hotel_id =h.hotel_id
where h.hotel_id is null
这个sql是⽤来查询出c表中有h表中⽆的记录，所以想到了⽤left join的特性（返回左边全部记录，右表不满⾜匹配条件的记录对应⾏返回null）来满⾜需求，不料这个查询⾮常慢。先来看查询计划：
rows代表这个步骤相对上⼀步结果的每⼀⾏需要扫描的⾏数，可以看到这个sql需要扫描的⾏数为35773*8134，⾮常⼤的⼀个数字。本来c和h表的记录条数分别为40000+和10000+，这⼏乎是两个表做笛卡尔积的开销了（select * from c,h）。
多表left join于是我上⽹查了下MySQL实现join的原理，原来MySQL内部采⽤了⼀种叫做 nested loop join的算法。Nested Loop Join 实际上就是通过驱动表的结果集作为循环基础数据，然后⼀条⼀条的通过该结果集中的数据作为过滤条件到下⼀个表中查询数据，然后合并结果。如果还有第三个参与 Join，则再通过前两个表的 Join 结果集作为循环基础数据，再⼀次通过循环查询条件到第三个表中查询数据，如此往复，基本上MySQL采⽤的是最容易理解的算法来实现join。所以驱动表的选择⾮常重要，驱动表的数据⼩可以显著降低扫描的⾏数。
那么为什么⼀般情况下join的效率要⾼于left join很多？很多⼈说不明⽩原因，只⼈云亦云，我今天下午感悟出来了⼀点。⼀般情况下参与联合查询的两张表都会⼀⼤⼀⼩，如果是join，在没有其他过滤条件的情况下MySQL会选择⼩表作为驱动表，但是left join⼀般⽤作⼤表去join⼩表，⽽left join本⾝的特性决定了MySQL会⽤⼤表去做驱动表，这样下来效率就差了不少，如果我把上⾯那个sql改成
select c.* from hotel_info_original c
join hotel_info_collection h
on c.hotel_type=h.hotel_type and c.hotel_id =h.hotel_id
查询计划如下：
很明显，MySQL选择了⼩表作为驱动表，再配合(hotel_id,hotel_type)上的索引瞬间降低了好多个数量级。。。。。
另外，我今天还明⽩了⼀个关于left join 的通⽤法则，即：如果where条件中含有右表的⾮空条件（除开is null），则left join语句等同于join语句，可直接改写成join语句。
后记：
随着查看MySQL reference manual对这个问题进⾏了更进⼀步的了解。MySQL在执⾏join时会把join分为
system/const/eq_ref/ref/range/index/ALl等好⼏类，连接的效率从前往后
依次递减，对于我的第⼀个sql，连接类型是index，所以⼏乎是全表扫描的效果。但是我很奇怪我在(hotel_id,hotel_type)两列上声明了unique key，根据官⽅⽂档连接类型应该是eq_ref才对，
这个问题⼀直困扰了我两天，在google和stackoverflow上都没有到能够解释这个问题的⽂章，莫⾮我这个问题⽆解了？抱着解决这个问题的决⼼今天⼜翻看了⼀遍MySQL官⽅⽂档
关于优化查询的部分，看到了这样⼀句：这⾥的⼀个问题是MySQL能更⾼效地在声明具有相同类型和尺⼨的列上使⽤索引。我感觉我到了问题所在，于是我将original和 collection表的(hotel_type,hotel_id)的encoding和collation（决定字符⽐较的规则）全部改成统⼀的utf8_general_ci，然后再次运⾏第⼀条sql的查询计划，得到如下结果：
连接类型已经由index优化到了ref，如果将hotel_type申明为not null可以优化到eq_ref，不过这⾥影响不⼤了，优化后这条sql能在0.01ms内运⾏完。
那么如何优化left join：
1、条件中尽量能够过滤⼀些⾏将驱动表变得⼩⼀点，⽤⼩表去驱动⼤表
1、条件中尽量能够过滤⼀些⾏将驱动表变得⼩⼀点，⽤⼩表去驱动⼤表
2、右表的条件列⼀定要加上索引（主键、唯⼀索引、前缀索引等），最好能够使type达到range及以上（ref,eq_ref,const,system）
3、⽆视以上两点，⼀般不要⽤left join~~！

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。