统计另一张表匹配数据更新目标列的SQL优化方案求助

原SQL属于相关子查询，执行时会对tableA的每一行都发起一次tableB的计数查询，重复扫描tableB的次数和tableA的行数一致，数据量较大时耗时会非常高，可改用预聚合后关联更新的方案优化：

方案1：预聚合关联更新（优先使用） #

提前统计好tableB每个column2值对应的匹配条数，再一次性关联更新tableA，仅需扫描两张表各一次，性能提升明显，不同数据库语法略有差异：

MySQL 语法 #

UPDATE tableA a
LEFT JOIN (
    SELECT column2, COUNT(*) AS match_cnt
    FROM tableB
    GROUP BY column2
) b ON a.column2 = b.column2
SET a.column1 = COALESCE(b.match_cnt, 0);

PostgreSQL 语法 #

UPDATE tableA a
SET column1 = COALESCE(b.match_cnt, 0)
FROM (
    SELECT column2, COUNT(*) AS match_cnt
    FROM tableB
    GROUP BY column2
) b
WHERE a.column2 = b.column2;

SQL Server 语法 #

UPDATE a
SET a.column1 = COALESCE(b.match_cnt, 0)
FROM tableA a
LEFT JOIN (
    SELECT column2, COUNT(*) AS match_cnt
    FROM tableB
    GROUP BY column2
) b ON a.column2 = b.column2;

方案2：新增索引进一步提速 #

配合方案1使用，可进一步降低执行耗时：

• 为tableB的column2字段创建索引，大幅提升分组统计的效率

CREATE INDEX idx_tableb_column2 ON tableB(column2);

• 若tableA数据量极大，也可同步为tableA的column2字段创建索引，提升关联匹配效率

CREATE INDEX idx_tablea_column2 ON tableA(column2);

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