MySQL关联分类、帖子、属性三表实现高级筛选的查询方案咨询

你当前使用的是EAV（实体-属性-值）模型存储不同品类的自定义属性，针对高级筛选需求有两种常用的可落地实现方案：

方案1：多INNER JOIN关联（适合固定筛选条件场景） #

如果筛选维度是固定的（比如汽车品类固定按品牌、年份、里程三个维度筛选），可以给每个筛选属性单独关联一次属性表：

SELECT p.* 
FROM posts p
-- 关联品牌属性
INNER JOIN posts_attributes pa_brand 
    ON p.ID = pa_brand.ad_id 
    AND pa_brand.att_cat = 3 -- 3对应cars分类的品牌属性分类，可按实际业务调整
-- 关联年份属性
INNER JOIN posts_attributes pa_year 
    ON p.ID = pa_year.ad_id 
    AND pa_year.att_cat = 3 -- 调整为对应年份的属性分类值
-- 关联里程属性
INNER JOIN posts_attributes pa_km 
    ON p.ID = pa_km.ad_id 
    AND pa_km.att_cat = 3 -- 调整为对应里程的属性分类值
WHERE 
    p.ad_title LIKE CONCAT('%',?,'%') 
    AND p.ad_sub_cat = ? 
    AND p.ad_price >= ? 
    AND p.ad_price <= ?
    -- 品牌筛选条件
    AND pa_brand.ad_att LIKE CONCAT('%',?,'%')
    -- 年份筛选条件
    AND CAST(pa_year.ad_att AS UNSIGNED) >= ?
    -- 里程筛选条件
    AND CAST(REPLACE(pa_km.ad_att, 'km', '') AS UNSIGNED) <= ?

该方案逻辑清晰，筛选条件可直接写在WHERE子句中，也支持按属性排序，缺点是每新增一个筛选维度就要多关联一次属性表，不适合筛选维度动态变化的场景。

方案2：GROUP BY + HAVING聚合筛选（适合动态筛选场景） #

如果不同品类的筛选维度完全不同、需要动态生成筛选条件，用聚合筛选更灵活，不需要动态拼接JOIN语句：

SELECT p.*
FROM posts p
INNER JOIN posts_attributes pa 
    ON p.ID = pa.ad_id
    AND pa.att_cat = ? -- 动态传入当前筛选的分类ID
WHERE 
    p.ad_title LIKE CONCAT('%',?,'%') 
    AND p.ad_sub_cat = ? 
    AND p.ad_price >= ? 
    AND p.ad_price <= ?
GROUP BY p.ID
-- 所有属性筛选条件统一放在HAVING子句中
HAVING
    -- 品牌筛选：匹配到指定品牌
    MAX(CASE WHEN pa.ad_att LIKE CONCAT('%',?,'%') THEN 1 ELSE 0 END) = 1
    -- 年份筛选：匹配到大于等于指定年份
    AND MAX(CASE WHEN CAST(pa.ad_att AS UNSIGNED) >= ? THEN 1 ELSE 0 END) = 1
    -- 里程筛选：匹配到小于等于指定里程
    AND MAX(CASE WHEN CAST(REPLACE(pa.ad_att, 'km', '') AS UNSIGNED) <= ? THEN 1 ELSE 0 END) = 1

该方案不管有多少个筛选条件都只需要关联一次属性表，非常适配多分类动态筛选的业务场景，缺点是大数据量下性能比第一种方案略低，需要配合索引优化。

优化建议 #

• 给posts_attributes表加联合索引(ad_id, att_cat, ad_att)，可以大幅提升关联查询和属性筛选的速度
• 数值类型的属性建议单独存储数值字段，不要带单位（比如里程单独存数值类型的ad_att_value字段，单位单独存储到其他字段），避免查询时用函数处理字段导致索引失效

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