mysql之join left join踩坑记

背景：最近优化个线上导出的功能，本来测试环境没啥问题；一上线不行了，下载超时；然后一顿改，把代码实现的逻辑全扔sql里了，发现反而更快，啥查询字段里不要加函数，分批查询啥的；没有，就是简单粗暴能查出来就行；别的事又催的紧，那就先上去看看再说吧；这不一发上去发现，有些条件下的数据还能导出，有些条件下的数据还是不能导出还是超时；优化了个寂寞，哎。。。怀着不甘的心我本地造了几万的数据量，模拟下特定条件下的数据量，想着看看是不是数据量对查询计划有影响(实际大表数据量有50w+)，所以有了这篇文章；

初始化工作建表

建表mother_t(id,name,batch_date)
建表child_t(id,name,mother_id,batch_date)
mother表和child根据mother_id相关联，其中child_t中mother_id是mother_t 表的主键

初始化数据

6.19日期下初始化5万条数据，6.20日期下初始化500条数据，6.21日期下初始化10条数据

select ct.id from
child_t ct join  mother_t mt on ct.mother_id = mt.id
where batch_date = ''
-- 查看三个日期下的执行计划，发现一样；排除数据量的影响

思考

其中mother_t 表和child\_t表是1对1关系；这里有个小插曲，之前child表关联mother表是left join关联，同事说一对一的改为join关联就好了，测试环境测了下发现left join 和join 查询出来的数据量不同，但是业务肯定是要业务主表有数据的，子表没数据也不能影响主表看数；因为这边数据质量一直都不算好，所以又改回了left join;并且查看了这两张表join 和left join 查询的执行计划发现一样，所以就没放在心上；*

哎，终究是出问题；那就看看问题实际出在哪里吧；完成上述基础事情，我迫不及待的打开数据；看了下执行计划，发现确实是一样的啊，没毛病啊…等等，不对，这里实际业务场景还有一张brother表(大概就是那么个意思，理解就行，这里不描述具体业务)；其中mother作为主表，又存了brother表的主键；

所以改之前sql

select m.id from mother_t m
join brother_t bro on m.bro_id = bro.id
join child_t ch on ch.mother_id = m.id
where batch_date = ''

改之后sql

select m.id from mother_t m
left join brother_t bro on m.bro_id = bro.id
left join child_t ch on ch.mother_id = m.id
where batch_date = ''

难道这里，主表不同；join 和left join执行计划会变；我迫不及待的 调换了下主次表的顺序，再次比对其执行计划；发现果真有坑啊！！！

执行计划如下图

啊这，这和我想的不一样啊；一对1的关系，join 和left join应该没啥区别，性能没差才对啊；

然后，我问了波AI

me:你是个java开发工程师，mysql在联表查询时遇到了个问题，现在有两张表 表A 表B
其中表A中存在 表B的主键，两张表根据表B的主键有一对一的关系，查询的时候你发现表A做主表时，
join 和left join的执行计划相同；但是当你交换表A 表B的顺序也就是表B 做主表后，
你会发现join 和left join的执行计划不同了，其中left join 两张表都走的全表扫描；这是什么原因？

ai:这个问题可能是由于MySQL的查询优化器在处理查询时，根据表的大小、索引和其他因素来选择最佳的执行计划。
当你将表A作为主表时，查询优化器可能认为使用全表扫描是最优的选择，因为它可以更快地找到匹配的行。
然而，当你将表B作为主表时，查询优化器可能认为使用索引扫描是更好的选择，因为它可以避免全表扫描带来的性能损失

#感觉回答了个寂寞，索引优化器是个黑洞；只能根据执行计划来看了，基于上面的回答和上面执行计划的截图我们只需要记住，
建了“外键”的表做主表，那么join 和left join的查询效率相当；反之则left join都会走全表；

加索引会怎么样？

加了索引，发现不管哪张表做主表，只有join的关联条件才会走索引，left join 依然走全表扫描；好吧，我再次问了下ai

me:添加了索引后，发现；A做主表是  join  B_id 走了索引，left join B_id没有走索引，这是为何？

ai:当使用JOIN时，MySQL查询优化器通常会选择使用索引来提高查询性能。这是因为索引可以大大减少需要扫描的数据量，
从而提高查询速度。
然而，当你使用LEFT JOIN时，情况就不同了。在执行LEFT JOIN时，MySQL会先扫描左表（即主表），然后根据连接条件查找右表中的匹配行。
由于左表是主表，所以MySQL无法确定右表中是否存在匹配行，因此它必须扫描整个右表以找到所有可能的匹配行。
这就是为什么在使用LEFT JOIN时，即使已经添加了索引，MySQL仍然会进行全表扫描的原因。
如果你希望在使用LEFT JOIN时也走索引，可以考虑将右表作为主表，或者使用子查询来实现相同的效果;

#好吧，又学到了；总结就是，left join 时由于左边的是一定要的结果，所以会全表扫描；
如果左边表里存在“外键” 会走索引，否则需要交换表位置；
另外join 是根据两边匹配来取结果集，所以能走索引就走索引了

总结

至此，我们总算明白了join 和left join带来的性能差异点在哪里，在实际的应用中如果能用join 就用join 必须用left join时最好保证，含有关联关系的表作为主表，多张表关联无法兼顾时可采用子查询的方式；实践出真知，奥利给！