MySQL高效实现按匹配属性数量排序筛选关联商品

场景与需求 #

现有两张MySQL表：

• Products表存储商品基础信息：

id  |  value
================
1  |   foo
2  |   bar   
3  |   foobar
4  |   barbar
5  |   five
6  |   six

• properties表存储商品与属性的关联关系：

product_id  |  property_id
=============================
1      |       10
1      |       11
2      |       15     
2      |       16
3      |       10    
3      |       11
3      |       12
4      |       10
4      |       16
5      |       16
6      |       11

需要指定一组属性（示例为10、11、12），按以下规则筛选排序商品：

• 优先级1：展示匹配全部指定属性的商品
• 优先级2：展示仅匹配2个指定属性的商品（未被优先级1选中）
• 优先级3：展示仅匹配1个指定属性的商品（未被前两个优先级选中）
  同一优先级内的商品按关联的property_id升序排列。

实现SQL语句 #

SELECT 
    p.id AS product_id,
    p.value,
    COUNT(DISTINCT pr.property_id) AS matched_count
FROM 
    Products p
INNER JOIN 
    properties pr ON p.id = pr.product_id
WHERE 
    pr.property_id IN (10, 11, 12)
GROUP BY 
    p.id, p.value
HAVING 
    matched_count >= 1
ORDER BY 
    matched_count DESC,
    MIN(pr.property_id) ASC;

逻辑说明 #

1. 筛选关联记录：通过INNER JOIN关联商品表和属性表，用IN条件过滤出包含目标属性的关联数据。
2. 统计匹配数量：按商品ID分组，用COUNT(DISTINCT pr.property_id)统计每个商品匹配的目标属性数量（避免同一属性被重复统计）。
3. 过滤有效商品：HAVING matched_count >= 1确保只保留至少匹配1个目标属性的商品。
4. 按规则排序：
   • 第一排序项matched_count DESC：匹配数越多排序越靠前，直接满足优先级顺序；
   • 第二排序项MIN(pr.property_id) ASC：同一匹配数的商品，按其关联的最小property_id升序排列，符合需求中的组内排序要求。

性能优化建议 #

如果数据量较大，为properties表建立联合索引可以显著提升关联和筛选效率：

CREATE INDEX idx_product_property ON properties(product_id, property_id);

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