如何优化含DISTINCT与FIRST_VALUE的SQL查询以提升执行速度？

原查询语句 #

SELECT DISTINCT FIRST_VALUE(business_id)
       OVER (PARTITION BY b.sub_category_id
             ORDER BY AVG(stars) desc, COUNT(*) DESC) business_id,
       sub_category_id
FROM purchase_experience pe
JOIN businesses b ON b.id = pe.business_id
 AND b.status = 'active'
 AND b.sub_category_id IN (1010 ,1007 ,1034 ,1036)
WHERE pe.stars <> 0
GROUP BY business_id
LIMIT 4

查询结果 #

business_id | sub_category_id
1744        | 1007
13215       | 1010
9231        | 1034
9103        | 1036

可行优化方案 #

1. 针对性创建复合索引

• 给businesses表创建复合索引：(status, sub_category_id, id)，快速过滤active状态且目标分类的商家，同时直接获取关联所需的id字段，避免回表查询。
• 给purchase_experience表创建复合索引：(business_id, stars)，既支持快速关联商家表，又能过滤掉stars=0的无效数据，还能为后续聚合计算的AVG(stars)和COUNT(*)提供数据支撑。

2. 改写SQL逻辑，简化计算流程

原查询先聚合再用窗口函数+去重的逻辑冗余，可调整为先按分类和商家聚合，再在每个分类内取排名第一的商家，减少不必要的计算步骤：

WITH ranked_businesses AS (
    SELECT 
        b.sub_category_id,
        pe.business_id,
        AVG(pe.stars) avg_stars,
        COUNT(*) total_reviews,
        ROW_NUMBER() OVER (PARTITION BY b.sub_category_id ORDER BY AVG(pe.stars) DESC, COUNT(*) DESC) rn
    FROM purchase_experience pe
    JOIN businesses b ON b.id = pe.business_id
        AND b.status = 'active'
        AND b.sub_category_id IN (1010, 1007, 1034, 1036)
    WHERE pe.stars <> 0
    GROUP BY b.sub_category_id, pe.business_id
)
SELECT business_id, sub_category_id
FROM ranked_businesses
WHERE rn = 1
LIMIT 4;

该写法用ROW_NUMBER()直接标记每个分类的Top1商家，避免了FIRST_VALUE()加DISTINCT的多余去重操作，数据处理量更小。

3. 提前过滤数据，缩小关联范围

先从businesses表筛选出符合条件的记录，再和purchase_experience关联，减少大表关联的行数：

WITH filtered_businesses AS (
    SELECT id, sub_category_id
    FROM businesses
    WHERE status = 'active'
        AND sub_category_id IN (1010, 1007, 1034, 1036)
)
SELECT FIRST_VALUE(pe.business_id)
       OVER (PARTITION BY fb.sub_category_id
             ORDER BY AVG(pe.stars) desc, COUNT(*) DESC) business_id,
       fb.sub_category_id
FROM purchase_experience pe
JOIN filtered_businesses fb ON fb.id = pe.business_id
WHERE pe.stars <> 0
GROUP BY pe.business_id, fb.sub_category_id
LIMIT 4;

4. 移除不必要的DISTINCT

原查询中每个sub_category_id分区的FIRST_VALUE()仅返回一个值，结合LIMIT 4正好对应4个目标分类，DISTINCT属于多余操作，直接移除即可：

SELECT FIRST_VALUE(business_id)
       OVER (PARTITION BY b.sub_category_id
             ORDER BY AVG(stars) desc, COUNT(*) DESC) business_id,
       sub_category_id
FROM purchase_experience pe
JOIN businesses b ON b.id = pe.business_id
 AND b.status = 'active'
 AND b.sub_category_id IN (1010 ,1007 ,1034 ,1036)
WHERE pe.stars <> 0
GROUP BY business_id, b.sub_category_id
LIMIT 4;

注意需将b.sub_category_id加入GROUP BY（符合SQL标准对非聚合字段的要求）。

5. 根据执行计划调整关联策略

查看EXPLAIN结果，若存在低效的嵌套循环关联，可临时调整关联算法（如PostgreSQL中执行SET enable_nestloop = off;强制使用哈希关联）；若存在全表扫描，优先通过索引优化解决。

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