MySQL UPDATE中子查询SELECT COUNT(*)过慢 求等价高效替代方案

原查询的性能瓶颈主要来自两个方面：

1. 子查询为相关子查询，每处理一行table2数据都需要单独执行一次子查询，整体查询次数和table2的行数完全一致
2. 子查询的WHERE条件使用OR关联两个字段，无法直接命中索引，每次子查询都需要全表扫描table1，数据量越大性能衰减越明显

第一步：创建必要索引 #

提前为table1的两个关联字段创建单列索引，是所有优化的前提：

CREATE INDEX idx_t1_col1 ON table1(column1_table2index_id);
CREATE INDEX idx_t1_col2 ON table1(column2_table2index_id);

第二步：改写查询逻辑 #

通用高性能方案（所有场景适用，性能最高） #

先预计算所有index_id的匹配次数，再批量更新table2，全程命中索引，仅需扫描table1两次，不存在逐行查询的开销，逻辑与原查询完全等价：

UPDATE table2 t2
LEFT JOIN (
    -- 拆分两个关联字段，去重后分组计数，避免同一条table1记录匹配多次被重复计数
    SELECT match_id, COUNT(*) AS total
    FROM (
        SELECT column1_table2index_id AS match_id, id AS t1_row_id FROM table1 WHERE column1_table2index_id IS NOT NULL
        UNION
        SELECT column2_table2index_id AS match_id, id AS t1_row_id FROM table1 WHERE column2_table2index_id IS NOT NULL
    ) AS temp
    GROUP BY match_id
) AS stat ON stat.match_id = t2.index_id
SET t2.count_of_matches = IFNULL(stat.total, 0);

注：如果你的table1没有主键/唯一行标识，可以用所有字段组合替代t1_row_id，只要能唯一标识一行table1记录即可

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