sql调优之：join的优化

一、join的原理

mysql都是使用（Nested Loop ）循环套嵌的方式实现join的，用小表做驱动表、大表作为匹配表，开销会小点。
Nested Loop 是有三种的：Simple Nested-Loop Join、Index Nested-Loop Join、Block Nested-Loop Join。这里介绍一下最简单，大概了解一下join的原理。
Simple Nested-Loop Join：
以左边的r表作为驱动表，每次从中取一条取匹配表中查，驱动表每次加载一次就要去匹配表中查，如果驱动是个大表的话，就要进行多次的表的连接、查询（这里多次连接可能优化了，多次的连接可能没有那么消耗资源了，但是数据库的开销还是有的），详见下面截图。

详细的介绍可见：https://www.jianshu.com/p/16ad9669d8a9

二、大小表优化

left join : 小表 left join 大表
right join : 大表 right join 小表

三. 子query代替join优化

网上看的一段sql优化代码还是能挺透彻的说明用子query代替join的优化。用子query能减少join作为驱动表的数据量，从而减少查询表的次数？（个人理解）

原始sql: 10s

select
  o.no,s_order.no,sum(s_item.count),sum(after_sale_item.count)
  from
  buyer_order o
  left join seller_order s_order on o.id = s_order.buyer_order_id
  left join seller_order_item s_item on s_order.id = s_item.seller_order_id
  left join seller_order_after_sale after_sale on s_order.id = after_sale.seller_order_id
  left join seller_order_after_sale_item after_sale_item on after_sale.id = after_sale_item.after_sale_id
where o.add_time >='2019-05-01'
group by
  o.id,s_order.id
order by
  o.id
limit 0,10

优化后：20ms

select
  o.id,o.no,s_order.no,
  (select sum(sot.count) from seller_order so
    left join seller_order_item sot on so.id = sot.seller_order_id
        where so.id =s_order.id ),
  (select sum(osat.count) from seller_order_after_sale osa
    left join seller_order_after_sale_item osat on osa.id = osat.after_sale_id
        where osa.seller_order_id = s_order.id )
  from
  buyer_order o
  left join seller_order s_order on o.id = s_order.buyer_order_id
where o.addTime >='2019-05-01'
order by
  o.id
limit 0,10

四、优化点分析：

1. 用子query代替left join，依据join的原理，减少了驱动表的数据量，从而减少访问匹配表的次数。
2. 不使用group by减少了cpu对分组数据的处理，分组的操作可以理解为数据库引擎查询数据出来再处理的过程，当然是越少越好。
3. 因为group by的优先级是在limit之前的，所以之前是对查询的所有数据先进行分组，然后取10条。优化后，先取10条，然后再进行上面的sum()操作。优化力度还是挺大的。
   综上所述：效率提高了很多，蹭蹭的。

五、 总结

关于join的优化，侧重于从两点着手：
1. 减少驱动表的数据量。
2. 多用子query，用子query代替一些left join的操作。（其实也是为了减少驱动表的数据量）