无需UNION：将两个查询合并为单个高性能子查询

我需要一个能整合进主查询的子查询，要求它的输出等价于以下两个参考查询的UNION结果，且books表有数百万行数据，必须保证性能最优。当前我自己写的子查询只输出1、2、3，预期应该是1、2、3、5。

补充字段说明：prid是指向person表主键的外键，engages表的id字段同理。

参考查询与输出 #

Query I #

SELECT DISTINCT bk1.id FROM books bk1
INNER JOIN engages eg1 ON eg1.prid = bk1.prid
WHERE eg1.id = 2 AND eg1.typ = 1;

输出：

Query II #

SELECT DISTINCT bk1.id FROM books bk1
INNER JOIN promotes pm1 ON pm1.bkid = bk1.id
INNER JOIN engages eg1 ON eg1.prid = pm1.prid
WHERE eg1.id = 2 AND eg1.typ = 1
AND pm1.point = 5 AND bk1.prid <> 2;

输出：

最优实现方案 #

方案1：UNION ALL + 去重（简洁易读） #

因为两个查询的结果无重叠（Query II明确限制bk1.prid <> 2，而Query I的关联条件对应bk1.prid=2），所以用UNION ALL替代UNION可避免重复的去重操作，再整体做一次去重（极端场景防重叠）：

SELECT DISTINCT id FROM (
    SELECT bk1.id FROM books bk1
    INNER JOIN engages eg1 ON eg1.prid = bk1.prid
    WHERE eg1.id = 2 AND eg1.typ = 1
    UNION ALL
    SELECT bk1.id FROM books bk1
    INNER JOIN promotes pm1 ON pm1.bkid = bk1.id
    INNER JOIN engages eg1 ON eg1.prid = pm1.prid
    WHERE eg1.id = 2 AND eg1.typ = 1
      AND pm1.point = 5 AND bk1.prid <> 2
) AS combined;

若确认两个子查询结果完全不重叠，可直接去掉外层DISTINCT，进一步提升性能。

方案2：合并逻辑为单查询（减少表扫描） #

通过EXISTS合并条件，减少对books表的扫描次数，更适配大数据量场景：

SELECT DISTINCT bk1.id
FROM books bk1
WHERE 
    -- 匹配Query I的条件
    EXISTS (
        SELECT 1 FROM engages eg1 
        WHERE eg1.prid = bk1.prid AND eg1.id = 2 AND eg1.typ = 1
    )
    -- 匹配Query II的条件
    OR (
        bk1.prid <> 2
        AND EXISTS (
            SELECT 1 FROM promotes pm1 
            JOIN engages eg1 ON eg1.prid = pm1.prid
            WHERE pm1.bkid = bk1.id 
              AND pm1.point = 5 
              AND eg1.id = 2 AND eg1.typ = 1
        )
    );

性能优化建议 #

• 给engages(prid, id, typ)创建复合索引，加速关联与过滤
• 给promotes(bkid, prid, point)创建复合索引，提升Query II的关联效率
• 给books(prid, id)创建复合索引，缩小表扫描范围

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