性能优化总结（二）：聚合SQL

2022-04-27 03:34:58

本篇主要讲如何使用一句较复杂的SQL来加载整个聚合对象，以达到最小化数据库连接次数。主要是解释其中的原理。

LazyLoad及其缺点

相信越来越多的人已经开始使用富领域对象进行领域/业务层的实现了。而目前主流的数据库依然还是关系型的。这中间的转换，我们叫它ORM。ORM的设计中，有一个常用的模式叫作“延迟加载（LazyLoad）”。基设计思想大致上是说，不要把所有的数据都加载进内存，而是等到真正要使用数据的时候，再把它加载进内存。

例如以下这个聚合对象：

（为了和后面的代码保持一致，这里面使用的是GIX4项目中真实的类，可能会带有一些领域特性，望读者见谅。后面可能会继续使用此例，现大致对其进行解释：其中，PBSType表示一套PBS模板/类型，一套模板由许多PBS组成。PBS是Project Breakdown Structure的简称，用于对某一个项目进行分解，这里面一个PBS对象的实例其实只是结构中的一项，应该在后面加上Item，不过公司的人都习惯了，所以就延用这个命名。每个PBS有许多属性（PBSProperty），每个属性又有许多可选值（PBSPropertyOptionalValue）。）

这个对象，在使用了LazyLoad对PBSType进行设计之后，客户程序使用代码如下：

var type = PBSType.Get(id);
//do something
//...

//lazily load a pbs list. data access occurs.
PBSList pbsList = type.PBSs;

//read from memory
var pbsListCount = type.PBSs.Count;

这里一共产生了两次数据访问：获取PBSType对象、获取所有在该PBS模板下的PBS对象列表。此例说明了对集合对象使用LazyLoad，还有一种比较常用的LazyLoad：对引用对象的LazyLoad。如下例：

文章对象引用一个用户对象来表示其作者。这个外键引用的关系，常常也被设计为LazyLoad。

这一模式已经被广泛地应用在各种ORM框架中，Linq to sql、EF等。这些ORM框架极大的方便了开发者，不需要再写烦人的SQL，加快了开发效率。但是如果不谨慎使用这一模式，很可能会造成过多的数据库连接次数，导致性能低下。如果是分布式程序，则会是更耗时的远程连接。如：

IList<Article> articles = ArticleRepository.Get(new PagerInfo()
{
    PageIndex = 1,
    PageSize = 10
});
foreach (var article in articles)
{
    //LazyLoad
    User owner = article.Owner;
}

这段代码中一共产生了 11 次数据访问/远程连接，相当的恐怖吧！

如何能保证又能降低连接次数，又不使用传统的Table方案呢？这就是今天要说的，一个用于重构的模式：聚合对象SQL。

什么是“聚合SQL”

要支持OO的领域对象，同时保证性能，我们的ORM就需要做到：获取对象时，一次性获取它指定的关系对象（集合/引用）；同时，仍然保留LazyLoad。

例如，当我们加载上述的Article及User时，可以调用类似ArticleRepository.Get_With_User的方法，使得一次性加载Article及其对应的User。那么，数据层访问数据库时，对应的SQL应该是把所有的数据都查询出来，大致是：

select a.*, u.*
from Articles a inner join Users u on a.UserId = u.Id

然后在把整个Table映射为Article对象列表的过程中，在每一行中读取并映射出User对象，然后对该行的Article对象的Owner属性赋值。

对应的，集合对象的一次性加载，要完成对数据的一次性加载，生成类似以下的SQL：

select * from PBSType t
left outer join PBS on t.Id = PBS.PBSTypeId

在应用中，当然不会那么简单，不过都是由以上两种方式组合而成。如，在GIX4的项目PBS模块中使用到这样的一个SQL，其中关于SQL的生成及格式定义，接下来我将会做更详细的解释：

        private static readonly string SQL_GET_BY_PROJECT_WITH_PROPERTY_VALUES = string.Format(@"
select 
{0},
{1},
{2},
{3}
from ProjectPBS pp
    left outer join ProjectPBSPropertyValue v on pp.Id = v.ProjectPBSId
    left outer join PBSProperty p on v.PBSPropertyID = p.Id
    left outer join PBSPropertyOptionalValue ov on p.Id = ov.PBSPropertyId
where pp.ProjectId = '{{0}}'
order by pp.Id, v.Id, p.Id
", ProjectPBS.GetReadableColumnsSql("pp"),
 ProjectPBSPropertyValue.GetReadableColumnsSql("v"),
 PBSProperty.GetReadableColumnsSql("p"),
 PBSPropertyOptionalValue.GetReadableColumnsSql("ov"));

今天先把理论写一下。下一节主要讲在目前的GIX4系统中，我们是如何引入聚合SQL来改善性能的。

码农公寓

相关文章