SQL优化案例-规避拉表，减少多节点数据交换

select 

   x.rwkey,

   x.gjnum,

      (select count(1) from tb_ryhc_basic zj where zj.rw_id=w.id) yhcnum

  from (

   select 

   if(ifnull(f.hbid,'')='',x.gjid,f.hbid) rwkey,

   max(x.hdsj) hdsj,max(x.jbxxid) jbxxid,max(x.zjh) zjh,max(x.xm) xm,count(1) gjnum

   from fk_rysc_gjhz_xkyry x 

   left join fk_rysc_gjhz_rwxf f on  x.jbxxid=f.jbxxid and  x.gjid=f.gjid 

   where 1=1

         and x.hdsj >= concat('2017-03-01',' 00:00:00')

         and x.hdsj <= concat('2018-03-03',' 23:59:59')

   group by rwkey 

  ) x

  left join fk_rysc_ryjbxx t on x.jbxxid = t.id

  left join tb_ryhc_rw w on x.rwkey = w.hbid and w.hclx='HC12'

  left join tb_ryhc_confirm_record qs on w.id=qs.rw_id

  where 1=1 

  and t.LSXKYRY='1' 

      order by x.gjnum desc,x.hdsj desc

    limit 0,10

select 

   x.rwkey,

   x.gjnum,

      (select count(1) from tb_ryhc_basic zj where zj.rw_id=w.id) yhcnum

  from (

   select 

   if(ifnull(f.hbid,'')='',x.gjid,f.hbid) rwkey,

   max(x.hdsj) hdsj,x.jbxxid,max(x.zjh) zjh,max(x.xm) xm,count(1) gjnum

   from fk_rysc_gjhz_xkyry x 

   left join fk_rysc_gjhz_rwxf f on  x.jbxxid=f.jbxxid and  x.gjid=f.gjid 

   where 1=1

         and x.hdsj >= concat('2017-03-01',' 00:00:00')

         and x.hdsj <= concat('2018-03-03',' 23:59:59')

   group by x.jbxxid,rwkey  --加入hash分布列

  ) x

  left join fk_rysc_ryjbxx t on x.jbxxid = t.id

  left join tb_ryhc_rw w on x.rwkey = w.hbid and w.hclx='HC12'

  left join tb_ryhc_confirm_record qs on w.id=qs.rw_id

  where 1=1 

  and t.LSXKYRY='1' 

      order by x.gjnum desc,x.hdsj desc

    limit 0,10

优化前

优化后

3.58秒

1.5秒