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秒，实则是化繁为简。