mysql中如何设置过滤器_mysql – 使用计数器实现过滤器

我想要实现的目标：

我正在开发带有产品目录的网站.

这是与我的问题相关的实体的规范化模型(简化)：

因此存在一些产品特征(如本例中的大小和类型),它们都具有预定义的值集(例如,存在大小1,2和3,类型可以是1,2或3(这些集合不必相等) ,只是一个例子.)).

产品与每个功能之间的关系是“多对多” – 一个功能的不同值不会相互排斥.

我的任务是构建表单,允许用户根据产品的功能过滤搜索结果.截屏示例：

使用“AND”逻辑混合一个特征的多个检查值,因此如果我检查了尺寸一和三,我需要所有具有两种尺寸的产品(可能具有任何其他尺寸,无关紧要,但必须选择出席).

功能的每个值附近的数字表示产品的数量,如果用户现在检查此值,则返回该数量.因此,它实际上是一些满足过滤器“当前有源滤波器应用这一值”的产品.

当用户检查/取消选中任何值时,必须考虑新的“当前过滤器”更新计数器.

问题：

实际使用案例是：~200k产品,~6个特征,每个~5-15个值.

我的COUNT个查询(特别是选择的选项数量太多)太慢了,并且要渲染我需要的表单,因为所有过滤器的值都很多 – 总共会产生不可接受的响应时间.

我试过的：

>查询以检索结果：

select * from products p, product_size ps

where p.id = ps.product_id

and (ps.size_id IN (1, 2, 3, 5))

group by p.id

having count(p.id) = 4;

(这是为了同时选择尺寸为1,2,3和5的产品).

它在120k产品上完成约0.360秒,几乎相同的时间,COUNT缠绕在它周围.此查询不允许多个功能(但我可以将所有功能的值放在一个表中).

>检索同一组的另一个查询：

SELECT ps1.product_id

FROM product_size AS ps1, (SELECT id FROM size AS s1 WHERE id IN (1, 2, 3, 5)) AS t

WHERE ps1.size_id = t.id

GROUP BY ps1.product_id

HAVING COUNT(ps1.size_id) = (SELECT COUNT(id) FROM (SELECT id FROM size AS s2 WHERE id IN (1, 2, 3, 5)) AS t2);

>替代架构：

非规范化模型,其中每个要素的值是Products表中的布尔列.

这里的查询很明显：

select * from products

where `size_1` = 1 and `size_2` = 1

and `size_3` = 1 and `size_5` = 1;

在应用程序的代码中保持奇怪和难以维护,但在计数时在~0.056秒内完成.

这些方法本身都不是可接受的,因为它们会增加~30倍(以填充表格中的所有计数器),从而提供不充分的响应时间.

>缓存和预计算

数据库中的数据每天只会更新几次(例如,甚至可能是2),因此我可能会在数据更新时预先计算所有过滤器组合的计数(我没有测量必要的时间来说实话) ,但它无论如何也不会起作用 – 搜索表单包含具有任意值的字段(如最小/最大价格和按产品名称搜索文本),我无法预先计算.

>动态加载表格中的计数器

渲染表单,但通过AJAX获取数字,因此用户可以看到页面,然后,经过相当长的等待,数字.这是我的最后一个想法,但对我来说似乎服务质量差(可能比没有柜台更差).

我被卡住了.任何提示？可能是我没有看到更大的图片？任何建议我都会很高兴.

更新：如果我们忘记了计数器,那么只使用这样的过滤器(或者我做错了什么)检索结果的有效且常用的方法(查询)是什么？就像“查找所有请求标签的帖子”模型一样,这是等效的.我怀疑它可能比我的0.230秒(查询#2)更快,考虑到MySQL的小行数(？).