如何优化基于关联子表元素数量删除外键主表数据的SQL语句

原查询性能差的原因 #

原SQL需要全量关联两张表后对所有轨迹做分组统计，相当于扫描全量track_points表的所有行计算每个轨迹的点位总数，数据量大时自然耗时久。同时嵌套了两层不必要的子查询，也会增加额外的执行开销。

优化方案 #

方案1：最高效的EXISTS快速判断写法（推荐） #

我们不需要统计每个轨迹的精确点位数量，只需要判断是否存在至少2个点位即可，子查询找到2个点位就会立即终止扫描，不需要遍历该轨迹下的所有点位，完全复用你已经创建的track_id_idx索引，性能提升非常明显：

DELETE FROM tracks
WHERE NOT EXISTS (
    SELECT 1 
    FROM track_points tp1
    INNER JOIN track_points tp2 
        ON tp1.track_id = tp2.track_id 
        AND tp1.id <> tp2.id
    WHERE tp1.track_id = tracks.id
    LIMIT 1
);

如果需要兼容更简单的写法，也可以用HAVING去掉一层嵌套，比原写法更简洁，不过性能略低于上面的EXISTS方案：

DELETE FROM tracks WHERE id IN (
    SELECT tracks.id 
    FROM tracks
    LEFT JOIN track_points ON tracks.id = track_points.track_id
    GROUP BY tracks.id
    HAVING COUNT(track_points.id) <= 1
)

方案2：大数量场景分批次删除 #

如果要删除的数据量特别大，为了避免长时间锁表，可以分批次执行删除：

-- 每次删除1000条，循环执行直到返回影响行数为0即可
DELETE FROM tracks
WHERE NOT EXISTS (
    SELECT 1 
    FROM track_points tp1
    INNER JOIN track_points tp2 
        ON tp1.track_id = tp2.track_id 
        AND tp1.id <> tp2.id
    WHERE tp1.track_id = tracks.id
    LIMIT 1
)
LIMIT 1000;

方案3：长期高频清理场景的终极优化 #

如果这类清理操作需要频繁执行，可以在tracks表新增冗余字段point_count INTEGER NOT NULL DEFAULT 0，新增/删除点位的时候同步更新该字段，再给point_count加索引，之后删除语句可以直接写成：

DELETE FROM tracks WHERE point_count <= 1;

执行速度可以达到毫秒级，仅需要额外维护冗余字段的一致性即可。

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