解决搜索操作中性能问题的优化策略- Server 2008

Question

我正在开发一个移动网站，这个网站越来越受欢迎，这导致了一些关键数据库表的增长--当我们访问这些表时，我们开始看到一些性能问题。我们不是数据库专家(在这个阶段也没有钱雇任何人)，我们很难理解是什么导致了性能问题。我们的表没有那么大，所以Server应该能够很好地处理它们，并且我们已经完成了我们所知道的优化查询的所有工作。下面是(伪)表结构：

[user] (approx. 40,000 rows, 37 cols):

id               INT (pk)
content_group_id INT (fk)
[username]       VARCHAR(20)
...

[content_group] (approx. 200,000 rows, 5 cols):

id    INT (pk)
title VARCHAR(20)
...

[content] (approx. 1,000,000 rows, 12 cols):

id                  INT (pk)
content_group_id    INT (fk)
content_type_id     INT (fk)
content_sub_type_id INT (fk)
...

[content_type] (2 rows, 3 cols)

id INT (pk)
...

[content_sub_type] (8 rows, 3 cols)
id              INT (pk)
content_type_id INT (fk)
...

我们预计这些行数会显著增长(特别是用户、content_group和内容表)。是的，用户表有相当多的列-我们已经确定了一些可以移动到其他表中的列。我们还为受影响的表应用了一堆索引，这些索引都有帮助。

最大的性能问题是我们用来搜索用户的存储过程(包括连接到content_group_id字段的内容表)。我们尝试使用各种不同的方法来修改WHERE和AND子句，我们认为我们已经尽可能好地获得了它们，但是仍然太慢了。

我们尝试过的另一件没有帮助的事情是在用户和内容表上设置一个索引视图。当我们这样做时，没有明显的性能提升，所以我们放弃了这个想法，因为拥有一个视图层所固有的额外复杂程度。

那么，我们有什么选择？我们可以想到一些，但都有正反两方面：

表结构的去噪

在用户和内容表之间添加多个直接外键约束--因此，对于每个内容子类型，内容表将有一个不同的外键。

优点：

• 通过使用主键，加入内容表将更加优化。

缺点：

• 我们现有的存储过程和网站代码将会有很多变化。
• 维护多达8个额外的外键(更实际地说，我们只使用其中的2个)不会像当前的单键那么简单。

--表结构的更多去畸形

只需将我们需要的字段从内容表直接复制到用户表中即可。

优点：

• 不再需要加入内容表-这大大减少了SQL必须做的工作。

缺点

• 与上面相同:用户表中需要维护的额外字段、对SQL和网站代码的更改。

创建中间层索引层

使用类似于Lucene.NET的方法，我们会在数据库的上方添加一个索引层。理论上，这将提高所有搜索的性能，同时减少服务器上的负载。

优点：

• 这是一个很好的长期解决方案。Lucene的存在是为了提高搜索引擎的性能。

缺点：

• 短期内会有更大的开发成本--我们需要尽快解决这个问题。

这些都是我们想出来的，在这个阶段，我们认为第二种选择是最好的--我知道去极化是有问题的，但是有时候最好牺牲建筑的纯洁性来获得性能的提升，所以我们准备支付这个成本。

还有其他可能对我们有用的方法吗？对于我上面概述的可能影响我们的决策的方法，还有什么其他的利弊吗？

Answer 1

使用content_sub_type_id从内容表中查找非聚集索引。然后是content_group_id上与content表的哈希匹配。

此描述表示您昂贵的查询将根据来自content的字段筛选content_type表。

select ...
from content c
join content_type ct on c.content_type_id = ct.id
where ct.<field> = <value>;

这个表的设计，以及你刚才看到的问题，实际上是相当常见的。出现这些问题的主要原因是查找表的选择性很低(content_type有2行，因此content_type_id在内容上的选择性可能是50%，巨大)。您可以尝试以下几种解决方案：

1)组织聚集索引上的content表，以content_type_id作为主导键。这将允许join进行范围扫描，并避免对投影完整性进行键/书签查找。随着聚集索引的变化，它将对其他查询产生影响，因此必须仔细测试。显然，content上的主键必须使用非集群约束来强制执行。

2)预先读取content_type_id值，然后在没有content和content_type之间连接的情况下进行查询。

select ...
from content c
where c.content_type_id = @contentTypeId;

只有当content_type_id的选择性很高时才能起作用(许多不同的值，每一行都有几行)，我怀疑这就是您的情况(您可能有很少的内容类型，每个条目都有很多条目)。

3)将content_Type去甲基化为内容。你提到了非正规化，但你关于将内容非正规化为用户的建议对我来说没有什么意义。删除content_type表，将content_type字段拉到content表本身，并处理所有的非正态化问题。

4)物化视图中的预连接。你说你已经试过了，但我怀疑你尝试了正确的物化观点。您还需要了解，只有Enterprise自动使用物化视图索引，所有其他版本都需要诺克潘德提示：

create view vwContentType 
with schemabinding
as 
select content_type_id, content_id
from dbo.content c
join dbo.content_type_id ct on c.content_type_id = ct.content_type_id;

create unique clustered index cdxContentType on vwContentType (content_type_id, content_id);

select ...
from content c
join vwContentType ct with (noexpand)
on ct.content_id = c.content_id
where ct.content_type_id = @contentTypeId;

解决方案2)、3)和4)大多是学术性的。考虑到content_type_id的选择性很低，您唯一有机会的解决方案是使它成为content聚集索引中的领先键。我没有将分析扩展到content_Sub_type，但是只有8行，我敢打赌它也有同样的问题，这需要将它也推到聚集索引中(可能作为第二个领先键)。