如何用SQL基于EAV模型实现多属性筛选并优化查询？

你当前基于EAV模式搭建在线目录，需要实现多属性组合筛选（比如同时搭载intel i5处理器和rtx3060显卡的笔记本），现有多层IN子查询的方式虽然可行，但性能可以优化，下面提供两种更高效的实现方案：

方案一：GROUP BY + HAVING 统计匹配数 #

这是EAV多条件筛选的经典高效方案，核心思路是先筛选出所有符合任一条件的记录，再通过分组统计确保item匹配所有筛选条件：

SELECT iv.item_id, i.name
FROM item_value iv
JOIN `values` v ON iv.value_id = v.id
JOIN property p ON v.property_id = p.id
JOIN item i ON iv.item_id = i.id
-- 列出所有筛选条件，用OR连接
WHERE (p.name = 'cpu' AND v.value = 'intel i5') 
   OR (p.name = 'gpu' AND v.value = 'rtx3060')
GROUP BY iv.item_id, i.name
-- 统计匹配的不同属性数量，等于筛选条件的总数（这里是2个条件）
HAVING COUNT(DISTINCT p.id) = 2;

优势： #

• 扩展性强：新增筛选条件时，只需在WHERE里加对应OR分支，同时把HAVING的数字加1即可
• 性能更优：可以利用索引减少数据扫描，适合中大规模数据

方案二：多表JOIN取交集 #

如果筛选条件数量不多，也可以通过多次JOINitem_value表的方式，直接取同时满足所有条件的item：

SELECT DISTINCT iv1.item_id, i.name
FROM item_value iv1
JOIN `values` v1 ON iv1.value_id = v1.id
JOIN property p1 ON v1.property_id = p1.id
-- 关联第二个条件的记录
JOIN item_value iv2 ON iv1.item_id = iv2.item_id
JOIN `values` v2 ON iv2.value_id = v2.id
JOIN property p2 ON v2.property_id = p2.id
JOIN item i ON iv1.item_id = i.id
-- 分别指定每个条件
WHERE p1.name = 'cpu' AND v1.value = 'intel i5'
  AND p2.name = 'gpu' AND v2.value = 'rtx3060';

优势： #

• 逻辑直观：每个JOIN对应一个筛选条件，相当于直接取多个条件的交集
• 适合条件较少的场景，索引优化后性能稳定

关键优化建议 #

不管用哪种方案，都建议给以下字段添加联合索引，大幅提升查询效率：

• values表：(property_id, value)，快速定位特定属性的目标值
• item_value表：(item_id, value_id)和(value_id, item_id)，加速关联查询

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