PostgreSQL查询未被使用的无效town_id并优化慢查询

针对你500万条数据的towns表清理需求，原查询因使用NOT IN导致性能极差甚至服务器崩溃，以下是几种优化方案：

1. 用LEFT JOIN + IS NULL替代NOT IN #

NOT IN在处理大子查询时容易触发全表扫描，且子查询字段含NULL时会导致逻辑异常，改用LEFT JOIN能更高效地过滤数据：

SELECT COUNT(*)
FROM towns t
LEFT JOIN banks b ON t.id = b.town_id
LEFT JOIN employees e ON t.id = e.town_id
WHERE t.vendor_id IS NULL
  AND b.town_id IS NULL
  AND e.town_id IS NULL;

数据库对LEFT JOIN的优化支持更好，能结合索引快速排除已关联的记录。

2. 为关联字段添加索引 #

索引是提升大表查询速度的核心，确保以下字段有索引：

• towns.id：主键默认带索引，无需额外创建；
• banks.town_id和employees.town_id：创建普通索引：

CREATE INDEX idx_banks_town_id ON banks(town_id);
CREATE INDEX idx_employees_town_id ON employees(town_id);

索引能让数据库快速定位关联记录，避免全表扫描带来的性能损耗。

3. 用NOT EXISTS替代NOT IN #

NOT EXISTS的执行逻辑是找到第一条匹配即停止比对，比NOT IN的集合遍历更高效：

SELECT COUNT(*)
FROM towns t
WHERE t.vendor_id IS NULL
  AND NOT EXISTS (SELECT 1 FROM banks b WHERE b.town_id = t.id)
  AND NOT EXISTS (SELECT 1 FROM employees e WHERE e.town_id = t.id);

这种写法在大表场景下，查询计划通常更优。

4. 分批删除（最终清理数据时用） #

如果要删除符合条件的记录，一次性操作大量数据会导致事务过大、锁表甚至服务器崩溃，建议分批处理：

-- 每次删除1000条，可根据服务器性能调整数量
WHILE EXISTS (
    SELECT 1 FROM towns t
    LEFT JOIN banks b ON t.id = b.town_id
    LEFT JOIN employees e ON t.id = e.town_id
    WHERE t.vendor_id IS NULL
      AND b.town_id IS NULL
      AND e.town_id IS NULL
) LOOP
    DELETE FROM towns t
    USING towns t1
    LEFT JOIN banks b ON t1.id = b.town_id
    LEFT JOIN employees e ON t1.id = e.town_id
    WHERE t.id = t1.id
      AND t1.vendor_id IS NULL
      AND b.town_id IS NULL
      AND e.town_id IS NULL
    LIMIT 1000;
    COMMIT; -- 每批提交，释放资源
END LOOP;

5. 临时表存储待删除ID后批量删除 #

先把符合条件的towns.id存入临时表，再关联删除，拆分复杂逻辑：

-- 创建临时表存储待删除ID
CREATE TEMP TABLE temp_towns_to_delete AS
SELECT t.id
FROM towns t
LEFT JOIN banks b ON t.id = b.town_id
LEFT JOIN employees e ON t.id = e.town_id
WHERE t.vendor_id IS NULL
  AND b.town_id IS NULL
  AND e.town_id IS NULL;

-- 给临时表加索引加速删除
CREATE INDEX idx_temp_town_id ON temp_towns_to_delete(id);

-- 执行批量删除
DELETE FROM towns t
USING temp_towns_to_delete td
WHERE t.id = td.id;

注意：执行删除操作前，务必先通过SELECT语句确认结果符合预期，且做好数据备份。

内容的提问来源于stack exchange，提问作者Roxy'Pro