SQL优化之外连接（一）

         前两天接触了一个项目，里面的一个报表查询功能，访问数据库很慢，严重时请求直接超时，写的sql语句得有三四百行，真是头疼，没办法，硬着头皮，只能一段一段的分析sql，查询sql慢的问题。解决思路就是使用EXPLAIN 命令来查看哪些表没有用到索引。通过查找终于找到问题所在，现记录如下：

1、问题所在：

join on 条件后面不能使用or连接，造成索引失效，会全表扫描。

sql语句实例：

SELECT
		sp.id,
		gcm.id AS gcmId,
		gcm.material_id
		FROM
		gt_supply_order sp
		LEFT JOIN gt_supply_order_material gm ON sp.id = gm.supply_order_id
		LEFT JOIN gt_contract gc ON sp.contract_id = gc.id
		left join gt_orderform_goods nm on nm.order_id=gc.id or nm.id=gm.order_form_goods_id
		LEFT JOIN gt_orderform_goods gcm ON gcm.id = nm.id
		OR gcm.main_contract_goods_id = nm.id
		AND gcm.deleted = 0
		WHERE
		sp.deleted = 0
		AND gm.deleted = 0
		AND sp.contract_id = #{jgMaterialSupplyVO.contractId} and sp.goods_class_name = #{jgMaterialSupplyVO.goodsName} and sp.supply_order_status_enum=4
		GROUP BY
		sp.id,gcm.id

2、解决方案：

上面查询（join on or）可以用 union 替换（索引有效），缺点sql语句长（相比性能问题还是可取的）

中间有个小插曲：

union 操作会对结果去重且排序，union all 不会去重

优化后的SQL如下：

select  a.id,gcm.id AS gcmId,gcm.material_id from(
 SELECT
  sp.id,
    nm.id as nmid
  FROM
  gt_supply_order sp
  LEFT JOIN gt_supply_order_material gm ON sp.id = gm.supply_order_id
  LEFT JOIN gt_contract gc ON sp.contract_id = gc.id
  left join gt_orderform_goods nm on nm.order_id=gc.id  
 WHERE
  sp.deleted = 0
  AND gm.deleted = 0 AND nm.deleted=0
   AND sp.contract_id = 137957291194000 and sp.goods_class_name = '桥梁支座' and sp.supply_order_status_enum=4
 union
  SELECT
  sp.id,
  nm.id as nmid
  FROM
  gt_supply_order sp
  LEFT JOIN gt_supply_order_material gm ON sp.id = gm.supply_order_id
  LEFT JOIN gt_contract gc ON sp.contract_id = gc.id
  left join gt_orderform_goods nm on  nm.id=gm.order_form_goods_id
 WHERE
  sp.deleted = 0
  AND gm.deleted = 0 AND nm.deleted=0
   AND sp.contract_id = 137957291194000 and sp.goods_class_name = '桥梁支座' and sp.supply_order_status_enum=4
)a
 LEFT JOIN gt_orderform_goods gcm ON gcm.id = a.nmid 
where gcm.id IS NOT NULL
GROUP BY a.id,gcm.id
union
select  b.id,gcm.id AS gcmId,gcm.material_id from 
(
 SELECT
  sp.id,
    nm.id as nmid
  FROM
  gt_supply_order sp
  LEFT JOIN gt_supply_order_material gm ON sp.id = gm.supply_order_id
  LEFT JOIN gt_contract gc ON sp.contract_id = gc.id
  left join gt_orderform_goods nm on nm.order_id=gc.id  

 WHERE
  sp.deleted = 0
  AND gm.deleted = 0 AND nm.deleted=0
   AND sp.contract_id = 137957291194000 and sp.goods_class_name = '桥梁支座' and sp.supply_order_status_enum=4
 union
  SELECT
 sp.id,
  nm.id as nmid
  FROM
  gt_supply_order sp
  LEFT JOIN gt_supply_order_material gm ON sp.id = gm.supply_order_id
  LEFT JOIN gt_contract gc ON sp.contract_id = gc.id
  left join gt_orderform_goods nm on nm.id=gm.order_form_goods_id
 WHERE
  sp.deleted = 0
  AND gm.deleted = 0 AND nm.deleted=0
   AND sp.contract_id = 137957291194000 and sp.goods_class_name = '桥梁支座' and sp.supply_order_status_enum=4
)b
 LEFT JOIN gt_orderform_goods gcm ON  gcm.main_contract_goods_id = b.nmid 
where gcm.id IS NOT NULL
 GROUP BY
  b.id,gcm.id

 

3、总结：

（1）通过上面的优化，查询速度由原来的77秒，提速到了0.107秒，提高700多倍，可见，SQL优化是多么的重要呀。

（2）这种优化方式可能不是最优的（sql语句太长），如有高人还有其他优化方式，希望可以交流，谢谢。

（3）有兴趣的小伙伴可以测试下，欢迎交流。