基于MySQL EAV模型的分面搜索（Faceted Search）：如何提升Facet计数查询速度？

阿华AIGC实验室

2026-4-29

看起来你在MySQL实现分面搜索时，遇到了数据量增长后Facet计数查询变慢的瓶颈——小分类（46个商品）查询快，但到651个商品的分类时，哪怕单筛选器都慢到3秒以上。我来给你几个针对性的优化思路，从查询逻辑、索引、预计算等多个层面解决问题：

1. 重构查询逻辑，扔掉低效的嵌套和HAVING写法

你当前的查询嵌套了多层子查询，而且HAVING里的MAX(fi.filter_v_id = 'XXX')=1这种写法，MySQL很难优化执行计划，尤其是数据量上去后，分组和条件判断的开销会指数级增长。

推荐用CTE（MySQL 8.0及以上支持）先筛选出符合条件的item_id集合，再基于这个集合做分面统计，逻辑更清晰，性能也会提升很多：

-- 先筛选出当前分类下符合筛选条件的商品ID
WITH filtered_items AS (
    SELECT ic.item_id
    FROM item_cat ic
    JOIN filter_item fi ON ic.item_id = fi.item_id
    WHERE ic.category_id = '52'
    GROUP BY ic.item_id
    HAVING (
        -- 用CASE表达式替代原来的OR+MAX，逻辑更明确，MySQL更容易利用索引
        MAX(CASE WHEN fi.filter_id = '47' AND fi.filter_v_id = '474' THEN 1 ELSE 0 END) = 1
        OR MAX(CASE WHEN fi.filter_id = '47' THEN 1 ELSE 0 END) = 1
    )
)
-- 基于筛选后的商品ID统计各分面的数量
SELECT fi.filter_id, fi.filter_v_id, COUNT(DISTINCT fi.item_id) AS cnt
FROM item_cat ic
JOIN filter_item fi ON ic.item_id = fi.item_id
JOIN filtered_items fi2 ON ic.item_id = fi2.item_id
WHERE ic.category_id = '52'
GROUP BY fi.filter_id, fi.filter_v_id;

如果你的MySQL版本低于8.0，可以把CTE换成临时表，效果类似。另外用COUNT(DISTINCT)能避免同一个商品被重复计数的问题。

2. 优化索引，砍掉冗余，新增适配分面查询的复合索引

你的表已经有不少索引，但有些是冗余的，而且缺少适配分面查询场景的核心索引：

item_cat表：现有主键(item_id, category_id)已经能高效按category筛选商品，但可以加一个覆盖索引(category_id, item_id, publicate)——这样查询分类下的商品时，不需要回表取数据，直接从索引就能拿到所有需要的字段。
filter_item表：
- 保留主键(item_id, filter_id, filter_v_id)：确保唯一性，同时支持按item_id快速找到对应的筛选器值。
- 新增复合索引(filter_id, filter_v_id, item_id)：这个索引能快速统计某个筛选值下的商品数量，而且是覆盖索引，不需要回表。
- 保留(item_id, filter_id, filter_v_id, filter_v)：当需要做filter_v的范围查询时（比如你例子里的价格区间），这个索引能覆盖所有查询字段，避免磁盘IO。
- 删除冗余索引：比如item_id、item_id_2这些单字段或部分复合索引，因为主键已经包含了item_id，这些索引不仅没用，还会增加写入时的开销。

3. 预计算分面统计（缓存/物化视图），用空间换时间

如果你的分面搜索实时性要求不是极高（比如允许10-30分钟的延迟），预计算绝对是提升速度最有效的方法：

创建一个统计表，存储每个分类下各筛选器的商品数量：

CREATE TABLE facet_stats (
    category_id INT(11) NOT NULL,
    filter_id INT(11) NOT NULL,
    filter_v_id INT(11) NOT NULL,
    item_count INT(11) NOT NULL,
    PRIMARY KEY (category_id, filter_id, filter_v_id),
    KEY idx_filter (filter_id, filter_v_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

定时用后台任务刷新这个表的数据（比如每小时跑一次）：

REPLACE INTO facet_stats (category_id, filter_id, filter_v_id, item_count)
SELECT ic.category_id, fi.filter_id, fi.filter_v_id, COUNT(DISTINCT ic.item_id)
FROM item_cat ic
JOIN filter_item fi ON ic.item_id = fi.item_id
WHERE ic.publicate = 1 -- 只统计已发布的商品
GROUP BY ic.category_id, fi.filter_id, fi.filter_v_id;

之后查询分面计数时，直接从这个统计表查，速度能快到毫秒级。如果需要支持实时的筛选组合计数，可以用Redis缓存筛选条件对应的结果，比如把category:52&filter:47=474作为key，计数结果作为value，缓存10分钟左右。