MySQL规范化电商offer表多属性匹配查询优化及方案咨询

问题场景 #

数据库中存在offer_properties表（EAV实体-属性-值结构），数据如下：

+----------+-------------+---------+
| offer_id | pkey        | pvalue  |
+----------+-------------+---------+
|       63 | shoesize    | shoe_47 |
|       63 | sport       | walking |
|       63 | color       | multi   |
|       12 | color       | multi   |
|       12 | shoesize    | size_48 |
|       12 | shoesize    | size_47 |
|       12 | shoesize    | size_46 |
|       12 | sneakertype | comfort |
|       12 | sport       | running |
+----------+-------------+---------+

需求是筛选出满足shoesize = size_48 AND sport = running的offer_id，当前使用嵌套IN查询实现：

select offer_id from offer_properties 
where (pkey = "sport" and pvalue = "running") 
and offer_id IN (select offer_id from offer_properties where (pkey = "shoesize" and pvalue = "size_48"));

但该写法在多属性匹配、关联价格/描述/标签等其他表时，复杂度会快速上升，需要更高效的优化方案，同时纠结是否应该通过应用层逻辑逐步过滤。

优化方案建议 #

1. 优化SQL写法（替代嵌套IN查询） #

方案A：同表多JOIN匹配

通过多次关联offer_properties表，每次匹配一个属性条件，逻辑清晰且便于扩展关联其他表：

SELECT op1.offer_id
FROM offer_properties op1
JOIN offer_properties op2 ON op1.offer_id = op2.offer_id
WHERE op1.pkey = 'sport' AND op1.pvalue = 'running'
  AND op2.pkey = 'shoesize' AND op2.pvalue = 'size_48';

若需增加属性条件，只需继续添加对应的JOIN语句即可。

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

适合多属性条件场景，通过分组后统计满足条件的属性数量筛选结果：

SELECT offer_id
FROM offer_properties
WHERE (pkey = 'sport' AND pvalue = 'running') 
   OR (pkey = 'shoesize' AND pvalue = 'size_48')
GROUP BY offer_id
HAVING COUNT(DISTINCT pkey) = 2; -- 条件数量为2，需确保两个属性都匹配

如果存在多值属性匹配需求（如某个offer有多个shoesize，只需匹配其中一个），可调整HAVING逻辑，例如：

HAVING SUM(CASE WHEN pkey='sport' AND pvalue='running' THEN 1 ELSE 0 END) >=1
   AND SUM(CASE WHEN pkey='shoesize' AND pvalue='size_48' THEN 1 ELSE 0 END) >=1;

2. 应用层过滤的取舍 #

若业务中属性组合极度灵活，且数据量不大，可以考虑先通过简单SQL批量获取相关offer_id及属性，再在应用层（如用Promise异步处理）完成过滤：

• 优势：SQL逻辑极简，应对多变的属性需求更灵活；关联其他表时可先批量拉取数据再过滤，降低SQL复杂度。
• 劣势：数据量较大时，会将大量无关数据加载到应用层，占用内存且拖慢性能；无法利用数据库索引优化查询效率。

3. 长期优化：调整表结构 #

如果多属性查询是核心业务场景，EAV结构的局限性会逐渐凸显，可考虑以下调整：

• 转宽表：将常用属性（如shoesize、sport、color）设为表的固定字段，不常用属性可存入单独的EAV表或JSON字段，适合属性相对固定的场景。
• 使用JSON类型存储属性：将单个offer的所有属性存入JSON字段（如{"shoesize": ["size_48", "size_47"], "sport": "running"}），利用MySQL JSON函数查询：

  SELECT offer_id
  FROM offer_properties_new
  WHERE JSON_CONTAINS(properties, '"running"', '$.sport')
    AND JSON_CONTAINS(properties, '"size_48"', '$.shoesize');
  

  注意需为JSON字段创建函数索引，提升查询效率。

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