sql - 复杂多阶段搜索的一般策略

Question

我有一个应用程序，允许根据几个不同的标准(总共有 20 种不同的方法)搜索某个实体。我希望能够组合多个搜索的结果以生成单个结果集。

例如:

results = (entities from search 1 AND entities from search 2) OR (entities from search 3)

让我们假设搜索本质上足够复杂，以至于不可能将它们组合成单个逻辑查询(由于需要查询的复杂关系等)。

我们还假设所涉及的实体数量(可能)使得任何类型的内存策略都不可行。

我最初的想法是这样的:

1) 单独执行搜索，从每个搜索中获取匹配的“实体ID”列表，然后基于这些执行“根级”搜索。

例如:

select * from entity e
where 
(e.Id in (search 1 id list) AND e.Id in(search 2 id list))
OR e.Id in (search 3 id list)

2) 执行外部查询，根据我的(复杂)子查询返回的结果选择实体。

例如:

select * from entity e
where (e.Id in (select e1.id from entity e1 where ...) AND e.Id in (select e2.id from entity e2 where...))
OR e.Id in (select e3.id from entity e3 where...)

显然，为了说明的目的，这些例子被大大简化了；单个查询将更加复杂，它们的组合将是任意的(我刚刚在这里展示了一个代表性示例)。

我很想听听其他人如何处理这种情况的建议。我当然愿意接受我上面没有探讨过的任何可能性。

作为引用，这是一个使用由 SQL Server 2008 R2 数据库支持的 NHibernate ORM 的 .NET 应用程序。

我已经决定为此使用 hql 或 native sql，因为 ICriteria 或 Linq 不提供执行单个查询所需的灵活性，也不提供所需的组合操作。

Answer 1

我通过将搜索性能计数器保存在一个表中来做到这一点。基本上监视搜索过滤的行的平均百分比和运行时间。

然后我创建了一个基于
TotalNumberOfRowsToSearch * Percent_Not_Matched/RunTimeInSeconds
这个数字是它可以过滤掉的每秒行数的直接相关性。对数千次运行进行平均，这是一个相当好的预测。

然后我按顺序运行每个查询，首先是性能最高的图 1。

如果您对总结果进行逻辑 AND 运算，请仅对前一个查询的结果运行每个后续查询。

如果您正在执行逻辑 OR，则仅对 NOT IN 组合的先前搜索结果中的结果运行每个后续查询。

通过这样做，您的查询将根据索引和数据类型进行更改。

如果您想要一个不太动态的解决方案，只需计算搜索的每个部分的性能数据，然后首先使用性能更好的数据。请记住，在 55 毫秒内运行但匹配 99% 结果的查询不如在 1 秒内运行并匹配 1% 结果的查询有用，因此请注意结果可能与您最初的想法背道而驰。

在计算性能数据时，只需注意除以 0 的误差。