多对多关系下如何实现商品多属性组合筛选查询

当前建模属于典型的EAV（实体-属性-值）模型，多属性同筛的核心是找到同时命中所有指定属性维度条件的商品，以下两种方案均支持任意数量的属性组合扩展：

方案1：分组聚合匹配（优先推荐，扩展成本最低） #

核心逻辑：先筛选出命中任意一个目标属性条件的商品关联记录，按商品维度分组后，统计该商品命中的不同属性维度的数量，数量等于设置的属性条件总个数，即说明该商品满足所有筛选要求。

需求1实现（branch=Acer 且 screen=13 Inch） #

SELECT p.id, p.name
FROM product p
INNER JOIN product_attribute pa 
  ON p.id = pa.product_id
INNER JOIN attribute a 
  ON pa.attribute_id = a.id
WHERE 
  -- 枚举所有属性条件，不同维度条件用OR连接
  (a.`name` = 'branch' AND a.`value` = 'Acer')
  OR (a.`name` = 'screen' AND a.`value` = '13 Inch')
GROUP BY p.id, p.name
-- 匹配到的属性维度数必须等于总条件数（当前共2个属性维度）
HAVING COUNT(DISTINCT a.`name`) = 2;

运行结果返回LAPTOP A，符合预期。

需求2实现（branch为Acer/Dell 且 screen为13/15.6 Inch） #

SELECT p.id, p.name
FROM product p
INNER JOIN product_attribute pa 
  ON p.id = pa.product_id
INNER JOIN attribute a 
  ON pa.attribute_id = a.id
WHERE 
  (a.`name` = 'branch' AND a.`value` IN ('Acer','Dell'))
  OR (a.`name` = 'screen' AND a.`value` IN ('13 Inch','15.6 Inch'))
GROUP BY p.id, p.name
HAVING COUNT(DISTINCT a.`name`) = 2;

运行结果同时返回LAPTOP A和LAPTOP B，符合预期。

扩展方式 #

如果需要支持3个及以上属性筛选，比如新增「内存=16G」的条件，只需要在WHERE块中新增一行OR条件OR (a.name = 'memory' AND a.value = '16G')，再把HAVING后的数字改成3即可，不需要改动其他查询结构。

方案2：多表关联匹配（适合属性维度固定、大数据量场景） #

核心逻辑：每一个属性维度单独关联一次中间表和属性表，关联时直接带上该维度的筛选条件，所有关联都能命中的商品即为符合要求的商品。

需求1实现 #

SELECT p.id, p.name
FROM product p
-- 关联第一个属性维度：branch=Acer
INNER JOIN product_attribute pa1 
  ON p.id = pa1.product_id
INNER JOIN attribute a1 
  ON pa1.attribute_id = a1.id 
  AND a1.`name` = 'branch' 
  AND a1.`value` = 'Acer'
-- 关联第二个属性维度：screen=13 Inch
INNER JOIN product_attribute pa2 
  ON p.id = pa2.product_id
INNER JOIN attribute a2 
  ON pa2.attribute_id = a2.id 
  AND a2.`name` = 'screen' 
  AND a2.`value` = '13 Inch';

需求2实现 #

SELECT p.id, p.name
FROM product p
INNER JOIN product_attribute pa1 
  ON p.id = pa1.product_id
INNER JOIN attribute a1 
  ON pa1.attribute_id = a1.id 
  AND a1.`name` = 'branch' 
  AND a1.`value` IN ('Acer','Dell')
INNER JOIN product_attribute pa2 
  ON p.id = pa2.product_id
INNER JOIN attribute a2 
  ON pa2.attribute_id = a2.id 
  AND a2.`name` = 'screen' 
  AND a2.`value` IN ('13 Inch','15.6 Inch');

扩展方式 #

每新增一个属性筛选维度，就新增一组product_attribute和attribute的JOIN块即可。该方案在数据量较大、索引配置合理的情况下查询性能优于聚合方案，但扩展时需要新增JOIN逻辑，灵活度稍差。

性能优化建议 #

• 给attribute表添加name、value的联合索引，大幅提升属性条件匹配速度：

ALTER TABLE attribute ADD INDEX idx_attr_name_value(`name`, `value`);

• 如果product_attribute表数据量超过百万级，可以把现有两个单独索引替换成(product_id, attribute_id)和(attribute_id, product_id)两个联合索引，进一步提升关联性能。

内容的提问来源于stack exchange，提问作者Boycpu