针对数据库表使用多个可选参数实现搜索功能

Question

我想检查是否存在一种首选设计模式，用于针对数据库表实现具有多个可选参数的搜索功能，其中对数据库的访问只能通过存储过程进行。

目标平台是带有 SQL 2005、2008 后端的 .Net，但我认为这是一个非常普遍的问题。

例如，我们有客户表，我们希望为 UI 提供不同参数的搜索功能，例如客户类型、客户状态、客户邮政编码等，所有这些都是可选的，可以以任意组合进行选择。换句话说，用户可以仅通过customerType或者通过customerType、customerZIp或任何其他可能的组合来搜索。 有几种可用的设计方法，但它们都有一些缺点，我想问一下其中是否有首选设计或者是否有其他方法。

1. 根据UI的搜索请求，在业务层动态生成sql where子句sql语句，并将其作为参数传递给存储过程。类似于 @Where = 'where CustomerZip = 111111' 在存储过程内部生成动态sql语句并使用sp_executesql执行它。 缺点：动态sql、sql注入

2. 实现具有多个输入参数的存储过程，代表UI中的搜索字段，并使用以下构造仅选择where语句中请求字段的记录。

WHERE

        (CustomerType = @CustomerType OR @CustomerType is null )

AND      (CustomerZip = @CustomerZip OR @CustomerZip is null )

AND   …………………………………………

缺点：sql 可能存在性能问题。

3.为每个搜索参数组合实现单独的存储过程。 缺点：存储过程的数量会随着搜索参数的增加而迅速增加，重复代码较多。

Answer 1

查询对象模式。

Answer 2

方法1：动态SQL可以带参数，做起来非常简单，并且几乎消除了SQL注入的风险。反对动态 SQL 的最佳论据是，重要的语句可能需要一些复杂的逻辑来生成，尽管如果您使用像样的 ORM，这也不是问题。

NHiberante 和 LinqToSql 在幕后构建动态 SQL，并且它们不会充满安全漏洞。我认为，在推出自己的 DAL 之前，您最好考虑这两种技术中的一种。

方法2：我过去亲自使用过方法二，没有任何问题。您评论了“sql 可能存在的性能问题”，但是您是否进行过分析？比较执行计划？根据我自己的经验，使用 @param is null OR col = @param 方法对性能几乎没有影响。请记住，如果您花费 10 小时的开发时间来优化代码以每年节省 10 微秒的执行时间，那么您节省的净成本仍然接近 -10 小时。

方法3：组合爆炸。不惜一切代价避免。

Answer 3

我将此作为评论发布，但我意识到这可能应该是一个答案。

将谓词编写为 WHERE @Param IS NULL OR Column = @Param 并不好，因为优化器通常认为它是不可控制的。

尝试这个实验：使用填充最多的表，并尝试仅查询主键字段，该字段应该是您的聚集索引：

DECLARE @PrimaryKey int
SET @PrimaryKey = 1

SELECT CoveredColumn
FROM Table
WHERE @PrimaryKey IS NULL
OR PrimaryKeyColumn = @PrimaryKey

SELECT CoveredColumn
FROM Table
WHERE PrimaryKeyColumn >= ISNULL(@PrimaryKey, 0)
AND PrimaryKeyColumn <= ISNULL(@PrimaryKey, 2147483647)

这两个 SELECT 语句将生成相同的结果结果，假设 PK 列是非负 int。但调出执行计划后，您会发现成本存在巨大差异。第一个 SELECT 执行完整索引扫描，通常占用大约 90% 的查询成本。

当您希望在 SQL 中具有可选搜索条件，并且无法使用动态 SQL 时，如果可以将其转换为范围查询而不是使用 ISNULL，则性能最佳。即使范围很大（这里实际上是 int 范围的一半），当可选参数为时，优化器仍然会计算出它用过的。