关系数据库中分面搜索的高效实现

Question

我正在尝试实现一个带有多标签过滤的Faceted search或标签。在分面导航中，仅显示非空类别，并且在括号中显示该类别中也与已应用的条件匹配的项目的数量。

I can get all items having assigned categories using INNER JOINs和get number of items in all category using COUNT and GROUP BY，但是我不确定它将如何扩展到数以百万计的对象和数千个标签。尤其是数数的时候。

我知道有一些非关系型解决方案，比如Lucene + SOLR，但我也发现了一些基于闭源关系型数据库的实现，它们据说是企业级的，比如FacetMap.com或Endeca软件，所以必须有一种在关系数据库中执行分面搜索的有效方法。

有没有人有切面搜索的经验，并能给出一些建议？

是否缓存每个类别集的计数？也许可以使用一些智能的增量技术来更新计数器？

编辑：

在这里可以找到一个分面导航的例子：Flamenco。

目前，我有一个标准的3表模式(项目、标签和这里描述的items_tags：http://www.pui.ch/phred/archives/2005/04/tags-database-schemas.html#toxi )，外加一个用于方面的表。每个标记都分配了一个方面。

Answer 1

我只能证实尼尔斯说的话。RDBMS不适合多维查询。我使用过一些智能的解决方案，比如缓存计数器、使用触发器等等。但最终，外部专用索引器总是胜出。

也许，如果您将您的数据转换为维度模型，并将其提供给某个OLAP，我是说MDX引擎-它将执行得很好。但它似乎有点太重的解决方案，它肯定不会是实时的。

相反，使用专用索引引擎(想想Lucene，想想Sphinx)的解决方案可以通过增量索引更新来接近实时。

Answer 2

在国际海事组织，关系数据库并不擅长搜索。您可以从专用搜索引擎(如Solr/Lucene)获得更好的性能。

Answer 3

分面搜索是一个分析问题，这意味着维度设计是一个很好的选择。也就是说，你搜索的东西必须是表格形式的。

在分析表中包括所有感兴趣的列。

将连续值放入存储桶中。

将布尔列用于“许多”项，如类别或标签，例如，如果有三个标签"foo“、"bar”和"baz"，则将有三个布尔列。

使用实体化视图创建分析表。

把这些垃圾编入索引。某些数据库支持这种类型的应用程序的索引。

只过滤一次。

合并您的结果。

为常见查询构建预聚合的实体化视图。

这篇文章也可能对你有所帮助：https://blog.jooq.org/2017/04/20/how-to-calculate-multiple-aggregate-functions-in-a-single-query/

with filtered as (
    select
    *
    from cars_analytic
    where
        [some search conditions]
)

--for each facet:

select
    'brand' as facet,
    brand as value,
    count(*) as count
from
    filtered
group by
    brand

union

select
    'cool-tag' as facet,
    'cool-tag'as value,
    count(*) as count
from
    filtered
where
    cool_tag

union

...


-- sort at the end
order by
    facet,
    count desc,
    value

100,000条记录，5个方面，大约150毫秒