解决搜索操作性能的最佳策略 - SQL Server 2008

Question

我正在开发一个移动网站，该网站越来越受欢迎，这导致一些关键数据库表的增长 - 并且我们在访问这些表时开始看到一些性能问题。我们不是数据库专家（现阶段也没有钱聘请任何专家），我们正在努力了解导致性能问题的原因。我们的表并没有那么大，因此 SQL 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_id 字段上的内容表的联接）。我们尝试使用各种不同的方法修改 WHERE 和 AND 子句，我们认为我们已经尽了最大努力，但仍然太慢。

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

那么，我们有什么选择呢？我们可以想到一些，但都各有利弊：

表结构的非规范化

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

优点：

• 使用主键连接内容表将更加优化。

缺点：

• 我们现有的存储过程和网站代码将会有很多变化。
• 维护最多 8 个额外的外键（更实际的是，我们只使用其中的 2 个）将不会像当前的单键那么容易。

表结构的更多非规范化

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

优点：

• 不再需要连接内容表——这显着减少了 SQL 要做的工作。

缺点

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

创建中间层索引层

使用 Lucene.NET 之类的工具，我们可以在数据库之上放置一个索引层。理论上，这将提高所有搜索的性能，同时减少服务器上的负载。

优点：

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

缺点：

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

这些是我们提出的，现阶段我们认为第二个选择是最好的 - 我知道非规范化有它的问题，但有时最好牺牲架构纯度以获得性能增益，所以我们准备支付这笔费用。

还有其他可能对我们有用的方法吗？我上面概述的方法是否还有其他可能影响我们决策的优点和/或缺点？

Answer 1

使用非聚集索引从内容表中查找
内容_子_类型_id。接下来是哈希匹配
对照内容表的 content_group_id

此描述表明您的昂贵查询会根据 content_type 中的字段筛选 content 表：

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_type_id 作为主键组织聚集索引上的 content 表。这将允许连接进行范围扫描，并避免为投影完整性进行键/书签查找。当聚集索引发生变化时，它会对其他查询产生影响，因此必须仔细测试。 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。您提到了非规范化，但您将内容非规范化为用户的建议对我来说毫无意义。删除 content_type 表，将 content_type 字段拉入 content 表本身，并接受所有非规范化问题。

4) 在物化视图中预加入。你说你已经尝试过了，但我怀疑你是否尝试过正确的物化视图。您还需要了解，只有企业版自动使用物化视图索引，所有其他版本都需要 NOEXPAND 提示：

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 行，我愿意打赌它也有同样的问题，这需要将其也推入聚集索引（也许作为第二个）主键）。