两个sql设计方案的比较

浏览数：24 / 时间：2015年06月12日

我有一个买方表Buyer，大概1万条记录；一个卖方表Sale，大概5万条记录。有一些买方和卖方之间是有某种关联的，这种关联关系被记录在Partner表里，Partner表中的关键字段包括BuyerID,SaleID和LinkManID，其中LinkManID是卖方的业务员之一。系统里还有一个联系人的概念，对于每一个买方，如果它和卖方在Partner表里有记录，就取Partner表中的LinkManID作为联系人ID；如果它和卖方在Partner表中无记录，那么需要去查找这个卖方的主业务员ID（每个卖方有1至n个业务员，其中有且仅有一个主业务员）来作为联系人ID。
在程序中，经常涉及到查询和某买方有业务关系的卖方列表，里面都需要显示联系人信息。现在有两个方案：
（1）生成一个视图，视图里就三列：BuyerID,SaleID和LinkManID。视图很容易写，形成的视图在程序里用起来也很方便。但如果不加上查询条件，直接查询这个视图，会查出5五*1万=5亿条记录（卖方和买方表作笛卡尔积）。虽然我们实际用的时候，肯定会加上查询条件（不加查询条件是没有意义的），但似乎也存在忘加查询条件把服务器搞死的可能；另外，一个视图，只有给它加上查询条件才能使用，是不是也有点怪怪的？毕竟sqlserver不支持所谓带参视图。
（2）写一个函数，传入BuyerID和SaleID，以LinkManID为返回值。函数虽然好写，但放在查询语句里很别扭，用起来非常不舒服。
下面给出两个方案的伪代码：

--方案1 使用视图
2

SELECT pa.BuyerID, pa.SaleID, us.UserName as LinkMan, us.Mobile, us.Phone
3

FROM Buyer
4

inner join Partner as pa on Buyer.UserID = pa.BuyerID and pa.IsBuyerFavorite=1
5

inner join Sale on pa.SaleID = Sale.UserID
6

inner join vLinkMan as lm on Buyer.UserID = lm.BuyerID and Sale.UserID = lm.SaleID
7

inner join [user] as us on lm.LinkManID = us.UserID
8

--方案2 使用函数
10

SELECT a.*, us.UserName as LinkMan, us.Mobile, us.Phone FROM
11

(
12

SELECT pa.BuyerID, pa.SaleID, GetLinkManID(Buyer.UserID, Sale.UserID) as LinkManID
13

FROM Buyer
14

inner join Partner as pa on Buyer.UserID = pa.BuyerID and pa.IsBuyerFavorite=1
15

inner join Sale on pa.SaleID = Sale.UserID
16

) a
17

inner join [user] as us on a.LinkManID = us.UserID

在一开始，我个人比较偏向于方案1，因为正如伪代码中所显示的那样，使用视图更容易写查询语句，也更容易和其他的表进行关联；另外，从运行效率上，我对函数的运行效率深表怀疑，认为用视图更快。结果，当我试着在项目里把两种方案都试验了之后，吃了一惊，函数法的速度远远高于视图法。这时候我还不服气，忙着去给视图加索引，这时候才惊觉，由于我这个视图是使用外连接得到的（买方表和卖方表笛卡尔积），所以根本无法建立索引（如果这一点说错了请大家指正，在我建立索引的时候报错“无法在视图上创建索引，因为该视图未绑定到架构”，百度了一下，发现视图中有外连接的话是不能建立索引的）。由于对视图的查询很慢，在sql server的查询计划里我们甚至可以看到它对要和视图进行连接的[user]表进行了lasy spool，而这个步骤又占去了查询的绝大多数时间：

而另一个让我以前没有想到的是，使用函数的效率竟然很高，下面是方案2的执行计划中标量计算所占的cpu百分比：

所以，最后自己还是采用了方案2，虽然用起来多了一层嵌套，但无论是安全性还是效率，都好于1。