SQL优化：化解表关联的多对多join

关系数据库的基本概念就是关系，对应到数据库软件中就是join，具体有：（1对1，1对多，多对多）。

在写sql时，经常会进行join，但是如果在join时没注意，关联条件是多对多，那么数据量就会成几何级数的增长，接下来又进行了group by，去除重复，真是吃力不讨好，曾经写过一个sql，运行需要7分钟，在修改之后，只需要10秒。。。

昨天写了一个存储过程，写好后发现非常慢，但是当前系统里的数据量就几十万条，数据量是比较小的。

代码如下：

• declare @biz_date varchar(7)
  
  set @biz_date = '2016-10'
  
  select '达成率' kpi,
  4 as sort,
  v.emp_id,
  count(distinct v.store_id)*1.0/nullif(count(distinct m.store_id),0) v
  FROM TB_CALL_PLAN v with(nolock)
  inner  join TB_STORE m with(nolock)
       on m.org_id = v.org_id and m.state = '1'
  WHERE   v.business_date LIKE @BIZ_DATE+'%'
  group by V.EMP_ID
  

这段代码运行时间是1分17秒，要计算某个人员的 达成率，逻辑也是很简单，其中的2个表是多对多的关系。

仔细想想慢可能是由于这种多对多的关系，一下子把数据集放大了好多倍，最后又通过group by 来去重 count(distinct store_id)，所以就慢了。

于是修改了代码：

• declare @biz_date varchar(7)
  
  set @biz_date = '2016-10'
  
  select '达成率' kpi,
  4 as sort,
  v.emp_id,
  count(distinct v.store_id)*1.0/nullif(c,0) v
  FROM TB_CALL_PLAN v with(nolock)
  inner  join 
  (
  select m.org_id,
  count(*) as c
  from TB_STORE m with(nolock)
  where m.state = '1'
  group by m.org_id
  )m
  on m.org_id = v.org_id
  WHERE v.business_date LIKE @BIZ_DATE+'%'
  group by V.EMP_ID,c
  

这段代码从表面看，好像是比最初的代码要复杂了，现在内部聚合tb_store的数据，然后再和外面的v表关联，但一个好处是对应关系变成了多对一的关系，也就是m的一个org_id对应多个v中的org_id，先把一个表的数据量通过group by缩小，然后再关联，这样关联数据就不会太多。

这么修改代码，运行时间降为0秒，实则是化繁为简。

本文转自51GT51CTO博客，原文链接：http://blog.51cto.com/yataigp/1950682 ，如需转载请自行联系原作者