如何用SQL JOIN统计不同表列并优化COUNT(DISTINCT)性能

你的问题核心是多表JOIN产生了笛卡尔积：当sq表的一条记录对应多条student或books记录时，JOIN会生成大量重复行，导致不使用DISTINCT时统计结果被重复计算而偏大；而DISTINCT的去重操作需要遍历大量数据，直接拖慢查询速度。

以下是几种高效的优化思路：

1. 预聚合子查询（最优推荐） #

提前在子查询中按id统计各表的数量，再与主表关联。这种方式从根源上避免了笛卡尔积，因为子查询的结果是每个id对应的唯一统计值，关联时不会产生重复行，自然不需要DISTINCT。

SELECT 
    COALESCE(b_stats.book_count, 0) AS book_count, -- 处理无对应记录的情况，返回0
    sq.name,
    sq.id,
    COALESCE(s_stats.s_count, 0) AS s_count,
    sq.invoice_date,  
    sq.subscribed,
    sq.installed
FROM sq
LEFT JOIN (
    -- 预统计每个id对应的书籍数量
    SELECT id, COUNT(*) AS book_count
    FROM books
    GROUP BY id
) b_stats ON b_stats.id = sq.id
LEFT JOIN (
    -- 预统计每个id中已毕业学生的数量
    SELECT id, COUNT(*) FILTER(WHERE graduated=$1) AS s_count
    FROM student
    GROUP BY id
) s_stats ON s_stats.id = sq.id

使用LEFT JOIN和COALESCE是为了保留sq表中没有对应student或books的记录，与原查询逻辑一致。

2. EXISTS子查询直接统计 #

通过子查询直接针对每个sq.id统计关联表的数量，完全避免多表JOIN，适合sq表数据量不大、且关联表的id字段有索引的场景。

SELECT 
    (SELECT COUNT(*) FROM books b WHERE b.id = sq.id) AS book_count,
    sq.name,
    sq.id,
    (SELECT COUNT(*) FROM student s WHERE s.id = sq.id AND s.graduated=$1) AS s_count,
    sq.invoice_date,  
    sq.subscribed,
    sq.installed
FROM sq

这种方式相当于对每条sq记录执行两次索引查找（如果books.id和student.id有索引），查询效率非常高。

3. 添加针对性索引 #

无论采用哪种方案，合适的索引都能进一步提升速度：

• 给student表建复合索引：CREATE INDEX idx_student_id_graduated ON student(id, graduated);，让统计已毕业学生的子查询可以直接通过索引获取数据，无需全表扫描。
• 给books表的id字段建索引（如果未存在）：CREATE INDEX idx_books_id ON books(id);
• 确保sq表的id是主键（主键默认自带唯一索引）

4. 窗口函数替代（备选） #

如果业务场景需要保留JOIN逻辑，可以用窗口函数按sq.id分区统计，虽然仍需DISTINCT，但窗口函数的计算效率通常优于GROUP BY+DISTINCT：

SELECT DISTINCT
    COUNT(b.id) OVER(PARTITION BY sq.id) AS book_count,
    sq.name,
    sq.id,
    COUNT(s.id) FILTER(WHERE s.graduated=$1) OVER(PARTITION BY sq.id) AS s_count,
    sq.invoice_date,  
    sq.subscribed,
    sq.installed
FROM sq
LEFT JOIN student s ON s.id = sq.id
LEFT JOIN books b ON b.id = sq.id

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