如何让子查询仅用主查询筛选的link_id优化SQL查询性能？

这问题我太熟了！你现在的核心痛点就是那个大UNION ALL子查询先扫描了所有符合时间范围的sales/clicks/impressions记录，哪怕这些记录对应的link根本不在你最终要筛选的范围内，完全是做无用功。要让子查询只处理主查询筛选出的link_id，咱们可以先提前获取符合条件的link集合，然后在每个统计子查询中加入过滤条件，而且完全可以在单条查询里实现，不用拆分成两次查询。

具体优化思路 #

1. 先通过一个CTE（公共表表达式）把你需要的所有有效link_id筛选出来——把原查询中links和products的筛选逻辑放进去，只保留link.id；
2. 在UNION ALL的每个统计分支里，都加上link_id IN (SELECT id FROM 有效link集合)的条件，这样每个子查询只会处理和目标link相关的数据，大幅减少扫描量。

优化后的查询语句 #

WITH valid_links AS (
    SELECT l.id
    FROM links l
    INNER JOIN products p ON p.id = l.product_id
    WHERE l.agent_id = 300 
      AND p.id = 3454 
      AND l.banner_size = 48 
      AND p.company NOT IN (13592, 28189)
)
SELECT 
    p.id, 
    p.name, 
    l.id, 
    l.banner_size, 
    COALESCE(SUM(CASE WHEN t1.col = 'sales' THEN ct ELSE 0 END), 0) AS total_sales, 
    COALESCE(SUM(CASE WHEN t1.col = 'clicks' THEN ct ELSE 0 END), 0) AS total_clicks, 
    COALESCE(SUM(CASE WHEN t1.col = 'impressions' THEN ct ELSE 0 END), 0) AS total_impressions, 
    t1.dt 
FROM links l 
INNER JOIN products p ON p.id = l.product_id
INNER JOIN valid_links vl ON vl.id = l.id -- 确保只取有效link
LEFT JOIN ( 
    SELECT COUNT(1) AS ct, link_id, DATE(clicked) dt, 'sales' AS col 
    FROM sales 
    WHERE clicked >= '2020-01-01 00:00:00' 
      AND clicked <= '2020-01-31 00:00:00'
      AND link_id IN (SELECT id FROM valid_links) -- 过滤有效link
    GROUP BY DATE(clicked), link_id 
    UNION ALL 
    SELECT COUNT(1) AS ct, link_id, DATE(created) dt, 'clicks' 
    FROM clicks_source1 
    WHERE created >= '2020-01-01 00:00:00' 
      AND created <= '2020-01-31 00:00:00'
      AND link_id IN (SELECT id FROM valid_links)
    GROUP BY DATE(created), link_id 
    UNION ALL 
    SELECT COUNT(1) AS ct, link_id, DATE(time) dt, 'clicks' 
    FROM clicks_source2 
    WHERE time >= '2020-01-01 00:00:00' 
      AND time <= '2020-01-31 00:00:00'
      AND link_id IN (SELECT id FROM valid_links)
    GROUP BY DATE(time), link_id 
    UNION ALL 
    SELECT COUNT(1) AS ct, link_id, DATE(created) dt, 'impressions' 
    FROM impression_source1 
    WHERE created > '2020-01-01 00:00:00' 
      AND created <= '2020-01-31 00:00:00'
      AND link_id IN (SELECT id FROM valid_links)
    GROUP BY DATE(created), link_id 
    UNION ALL 
    SELECT COUNT(1) AS ct, link_id, DATE(time) dt, 'impressions' 
    FROM impression_source2 
    WHERE time > '2020-01-01 00:00:00' 
      AND time <= '2020-01-31 00:00:00'
      AND link_id IN (SELECT id FROM valid_links)
    GROUP BY DATE(time), link_id 
) t1 ON t1.link_id = l.id 
GROUP BY p.id, p.name, l.id, l.banner_size, t1.dt 
HAVING (total_sales + total_clicks + total_impressions) > 0 
ORDER BY dt DESC, p.id ASC, l.banner_size ASC;

额外优化建议 #

• 给valid_links用到的字段加联合索引：比如links(agent_id, product_id, banner_size, id)，products(id, company)，这样CTE的查询会瞬间完成；
• 给各个统计表（sales、clicks_source1等）的link_id和时间字段加联合索引，比如sales(link_id, clicked)，这样每个子查询的过滤和分组会更快；
• 原查询里的GROUP BY c.id应该是笔误，我改成了p.id并补充了p.name（因为SELECT里有p.name，GROUP BY需要包含它，否则会有语法问题）。

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