线上一个SQL,使用了join去查询,A表数据量比较小(100以下),B表数据量比较大(1000W以上),大致的SQL内容如下:
select A.a1, B.b1 from A join B on A.a2 = B.b2 where B.b3 in (b31, b32, b33) and a = 1;
explain之后发现,由于join的时候优先选择了A表作为驱动表,而A表中的字段在B表中存在这大量的重复,在B表虽然设置索引的情况下,但是因为重复过多导致索引失效,导致慢查询的发生。
优化:join的时候直接指定,有索引的,量少的表作为驱动表,在这里b3建立了索引,所以sql可以优化为
select A.a1, B.b1 from A right join B on A.a2 = B.b2 where B.b3 in (b31, b32, b33) and a = 1;
再次执行explain的时候可以发现B表执行计划在A表的执行计划之前,也正常使用了索引,虽然使用了in但是可以看到也是使用了索引,也就是优先选择B表作为驱动表。
另外一个知识点:
1、on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。
2、where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了,条件不为真的就全部过滤掉。
参考文章:
mysql驱动表与被驱动表及join优化
https://blog.csdn.net/lovelessdream/article/details/103203310
Mysql多表连接查询的执行细节(一)
https://blog.csdn.net/qq_27529917/article/details/87904179
Mysql多表连接查询的执行细节(二)
https://blog.csdn.net/qq_27529917/article/details/87954427
sql(join on 和where的执行顺序)
https://www.cnblogs.com/jessy/p/3525419.html
Mysql join 算法原理
https://zhuanlan.zhihu.com/p/54275505
MySQL JOIN原理
https://www.cnblogs.com/shengdimaya/p/7123069.html