如何优化SQL查询：筛选符合特定子关联条件的article记录

我有三个数据表：article（约4万行）、calendar（约45万行）和calendar_cost（约50万行），需要筛选article表中满足以下任一条件的记录：

• 在calendar表中没有对应关联条目；
• 若在calendar表中有对应关联条目，则所有这些calendar条目在calendar_cost表中都没有对应关联记录。

目前我使用的带UNION ALL运算符的SQL查询执行速度很慢，尤其是第二个条件的查询部分，现寻求最优实现方案，同时询问是否可以无需使用UNION ALL运算符。

表结构与测试数据 #

CREATE TABLE article (
    id INT PRIMARY KEY,
    name VARCHAR
);

CREATE TABLE calendar (
    id INT PRIMARY KEY,
    article_id INT REFERENCES article (id) ON DELETE CASCADE,
    number VARCHAR
);

CREATE TABLE calendar_cost (
    id INT PRIMARY KEY,
    calendar_id INT REFERENCES calendar (id) ON DELETE CASCADE,
    cost_value NUMERIC
);

INSERT INTO article (id, name) VALUES
(1, 'Article 1'),
(2, 'Article 2'),
(3, 'Article 3');

INSERT INTO calendar (id, article_id, number) VALUES
(101, 1, 'Point 1-1'),
(102, 1, 'Point 1-2'),
(103, 2, 'Point 2');

INSERT INTO calendar_cost (id, calendar_id, cost_value) VALUES
(400, 101, 100.123),
(401, 101, 400.567);

符合条件的结果为Article 2（满足条件2）和Article 3（满足条件1）。

当前查询语句 #

-- 第一个条件：无关联日历记录
SELECT a.id FROM article a 
LEFT JOIN calendar c ON a.id = c.article_id
WHERE c.id IS NULL

UNION ALL 

-- 第二个条件：所有关联日历都无成本记录
SELECT a.id FROM article a
WHERE id NOT IN(
    SELECT aa.id FROM article aa
    JOIN calendar c ON aa.id = c.article_id
    JOIN calendar_cost cost ON c.id = cost.calendar_id
    WHERE aa.id = a.id LIMIT 1
)

优化方案与性能对比 #

数据生成脚本（模拟真实数据量） #

先创建带索引的表并生成随机数据，确保测试贴近真实场景：

DO $$
DECLARE
    article_id INT;     
    calendar_id BIGINT;
    i INT; j INT;
BEGIN
    CREATE TABLE article (
        id INT PRIMARY KEY,
        name VARCHAR
    );
    
    CREATE TABLE calendar (
        id SERIAL PRIMARY KEY,
        article_id INT REFERENCES article (id) ON DELETE CASCADE,
        number VARCHAR      
    );
    CREATE INDEX ON calendar(article_id);

    CREATE TABLE calendar_cost (
        id SERIAL PRIMARY KEY,
        calendar_id BIGINT REFERENCES calendar (id) ON DELETE CASCADE,
        cost_value NUMERIC      
    );
    CREATE INDEX ON calendar_cost(calendar_id);

    FOR article_id IN 1..45000 LOOP
        INSERT INTO article (id, name) VALUES (article_id, 'Article ' || article_id);
        FOR i IN 0..FLOOR(RANDOM() * 25) LOOP
            INSERT INTO calendar (article_id, number) VALUES (article_id, 'Number ' || article_id || '-' || i) RETURNING id INTO calendar_id;
            FOR j IN 0..FLOOR(RANDOM() * 2) LOOP
                INSERT INTO calendar_cost (calendar_id, cost_value) VALUES (calendar_id, ROUND((RANDOM() * 100)::NUMERIC, 3));  
            END LOOP;
        END LOOP;
    END LOOP;
END $$;

各优化方案性能测试结果 #

添加索引后，不同优化查询的执行速度大幅提升，以下是各方案的性能对比：

• @nbk 方案：规划时间 0.702 ms，执行时间 165.129 ms（性能最优）
• @Stu 方案：规划时间 0.446 ms，执行时间 280.842 ms
• @Chris Maurer 方案：规划时间 0.803 ms，执行时间 800.000 ms
• @Bohemian 方案：规划时间 0.405 ms，执行时间 1196.082 ms

推荐的无UNION ALL优化查询 #

方案1：聚合判断（性能最优）

用LEFT JOIN结合聚合函数实现，逻辑简洁且效率高：

SELECT a.id, a.name
FROM article a
LEFT JOIN calendar c ON a.id = c.article_id
LEFT JOIN calendar_cost cc ON c.id = cc.calendar_id
GROUP BY a.id, a.name
HAVING COUNT(c.id) = 0 OR COUNT(cc.id) = 0;

逻辑说明：

• COUNT(c.id) = 0 对应条件1：该文章无任何关联日历记录
• COUNT(cc.id) = 0 对应条件2：所有关联日历都无对应的成本记录（只要有一条日历存在成本，COUNT(cc.id)就会大于0）

方案2：NOT EXISTS实现（逻辑直观）

通过双重NOT EXISTS分别匹配两个条件，可读性强：

SELECT a.id, a.name
FROM article a
WHERE NOT EXISTS (
    SELECT 1 FROM calendar c 
    WHERE c.article_id = a.id
)
OR NOT EXISTS (
    SELECT 1 FROM calendar c
    JOIN calendar_cost cc ON c.id = cc.calendar_id
    WHERE c.article_id = a.id
);

逻辑说明：

• 第一个NOT EXISTS对应条件1：无关联日历记录
• 第二个NOT EXISTS对应条件2：不存在任何关联日历有成本记录（即所有日历都无成本）

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