MySQL和分面导航（按属性过滤）-Java 学习之路

我觉得这个问题可能已经被问过一千次了，所以如果有人回答，我会道歉 . 如果是这样，有人能指出我正确的帖子/链接？

我要做的是为我的网站构建一个分面导航 . 它使用MySQL，这里是我正在使用的表的草图：

products:
- id
- title
- description

attributes:
- product_id
- name
- value

categories:
- id
- name

products_to_categories:
- product_id
- category_id

我想要做的是当你在一个类别时显示可用属性列表，允许你为每个属性选择一个或多个值 . 举个例子，从Office Depot查看此页面：http://www.officedepot.com/a/browse/binders/N=5+2177/

到目前为止，我已经使用了很多连接来过滤多个属性：

SELECT products.*, a_options.*
FROM products_to_categories AS pc, products,
attributes AS a_options,    /* list of attribute/value pairs I can continue to refine on */
attributes AS a_select1     /* first selected attribute */
attributes AS a_select2     /* second selected attribute */
...
WHERE pc.category_id = 1
AND products.id = pc.product_id
AND a_options.product_id = products.id
AND a_options.name != 'Color' AND a_options.name != 'Size'
AND a_select1.product_id = products.id
AND a_select1.name = 'Color' AND (a_select1.value = 'Blue' OR a_select1.value = 'Black')
AND a_select2.product_id = products.id
AND a_select2.name = 'Size' AND a_select2.value = '8.5 x 11'

基本上 a_options 将返回那些产品的所有属性，这些产品是我使用 a_select1 和 a_select2 应用的过滤器的子集 . 因此，如果我使用Office Depot中的Binders示例，我想在选择Blue或Black for Color和"8.5 x 11"作为Size之后显示所有可用属性 .

然后我使用PHP代码删除重复项并将结果属性排列到这样的数组中：

attributes[name1] = (val1, val2, val3, ...)
attributes[name2] = (val1, val2, val3, ...)

有没有办法可以加快查询速度或更有效地编写查询？我在属性表中的名称和值（以及所有ID号）上设置了索引 . 但如果有人选择了几个属性，那么查询运行速度很慢 .

在此先感谢您的帮助，
斯里达尔

2 回答

4
“然后我使用PHP代码删除重复项”

它不会扩展 .

在我读完http://www.amazon.com/Data-Warehouse-Toolkit-Techniques-Dimensional/dp/0471153370之后，我不停地推出了切面和过滤机制 .

基本的想法是你使用星型模式..

您创建一个存储事实的事实表
```
customerid | dateregisteredid | datelastloginid
1 | 1 | 1
2 | 1 | 2
```
您可以将外键用于存储属性的维度表
```
date_registered
Id | weekday | weeknumber | year | month | month_year | daymonth | daymonthyear
1 | Wed      | 2            | 2009 | 2   |2-2009      | 4        | 4-2-2009
```
然后你使用哪个日期“范例”，从该维度表中获取所有ID
```
select * from the fact table where the fact.dateregisteredid is IN( ... the ids from the date dimension table that represent your time period)
```
这些数据的“索引视图”应位于单独的数据库中，对生产环境中对象的更改应将该记录排入队列，以便在分析系统中重新编制索引 . 大型网站可能会在非高峰时段将其记录批量处理，统计报告应用程序始终会滞后几小时或几天 . 如果架构支持它，我总是尽量保持第二 .

如果要显示rowcount预览，则可能还需要进行一些优化或缓存 .

基本上总结一下，你复制数据和反规范化 . 该技术的名称为“数据仓库”或OLAP（在线分析处理） .

有更好的方法，使用像Oracle这样的商业数据库，但是星型模式使任何拥有开源关系数据库的人都可以使用它 .

你一定要阅读工具包，但他讨论了许多可以节省大量时间的事情 . 与处理更新数据的策略一样，并在报告应用程序中保留审核历史记录 . 对于每个问题，他都概述了多种解决方案，每种解决方案都适用于不同的环境 .

如果你不采取简单的方法并使用大量不必要的连接，它可以扩展到数百万行 .
回复于 2024-04-28T20:07:40+08:00

您可以根据规范化的数据库表生成构面表 .
例如：

> SELECT * FROM product_facet
product_id | facet_type | facet_value
1          | color      | blue
2          | color      | blue
3          | color      | green
4          | color      | yellow
1          | speed      | slow
2          | speed      | slow

然后只需执行此查询即可获得每个属性的总数：

SELECT facet_type, facet_value, COUNT(facet_value) as total
FROM product_facet
GROUP BY facet_type, facet_value;

结果：

facet_type | facet_value | total
color      | blue        | 2
color      | green       | 1
color      | yellow      | 1
speed      | slow        | 2

使用条件搜索时，您可以通过匹配产品ID选择构面表：

SELECT facet_type, facet_value, COUNT(facet_value) as total
FROM product_facet
WHERE product_id in (SELECT product_id FROM products WHERE ... )
GROUP BY facet_type, facet_value;

回复于 2024-04-28T20:07:40+08:00

MySQL和分面导航（按属性过滤）

2 回答

相关问题