如何优化近乎相同表的UPDATE JOIN查询？

针对你提到的customersPrimary与customersSecondary的更新场景，以下是几个高效的优化方向：

1. 仅更新有数据变更的行 #

当前查询会匹配所有groupID+IDInGroup一致的行，哪怕name和address完全相同也会执行无意义的更新。添加字段对比条件，能直接过滤掉无需更新的行，大幅减少扫描和更新的行数：

UPDATE customersPrimary 
INNER JOIN customersSecondary 
  ON customersPrimary.groupID = customersSecondary.groupID
  AND customersPrimary.IDInGroup = customersSecondary.IDInGroup
SET
  customersPrimary.name = customersSecondary.name, 
  customersPrimary.address = customersSecondary.address
WHERE 
  -- 过滤字段值不同的情况
  customersPrimary.name <> customersSecondary.name
  OR customersPrimary.address <> customersSecondary.address
  -- 单独处理NULL值（NULL与任何值比较结果为UNKNOWN，需显式判断）
  OR (customersPrimary.name IS NULL XOR customersSecondary.name IS NULL)
  OR (customersPrimary.address IS NULL XOR customersSecondary.address IS NULL);

2. 用覆盖索引减少IO开销 #

虽然两张表已具备groupID+IDInGroup的索引，但可以为customersSecondary添加覆盖索引，让数据库无需回表就能获取更新所需的所有字段，降低磁盘IO：

-- 覆盖索引包含关联条件和更新字段，直接在索引中完成数据读取
CREATE INDEX idx_groupID_IDInGroup_name_address ON customersSecondary(groupID, IDInGroup, name, address);

注：customersSecondary的主键本身就是groupID,IDInGroup，该索引属于主键的扩展覆盖索引，InnoDB会直接利用主键组织的索引结构返回数据，完全避免主表访问。

3. 分批次更新（大表场景必备） #

如果单批次更新行数过多，会导致锁表时间过长、事务日志膨胀。可以按groupID分批次处理，控制单次更新的数据量：

-- 示例：按groupID逐个批次更新
SET @current_group = (SELECT MIN(groupID) FROM customersSecondary);
WHILE @current_group IS NOT NULL DO
  UPDATE customersPrimary 
  INNER JOIN customersSecondary 
    ON customersPrimary.groupID = customersSecondary.groupID
    AND customersPrimary.IDInGroup = customersSecondary.IDInGroup
  SET
    customersPrimary.name = customersSecondary.name, 
    customersPrimary.address = customersSecondary.address
  WHERE 
    customersSecondary.groupID = @current_group
    AND (
      customersPrimary.name <> customersSecondary.name
      OR customersPrimary.address <> customersSecondary.address
      OR (customersPrimary.name IS NULL XOR customersSecondary.name IS NULL)
      OR (customersPrimary.address IS NULL XOR customersSecondary.address IS NULL)
    );
  
  SET @current_group = (SELECT MIN(groupID) FROM customersSecondary WHERE groupID > @current_group);
END WHILE;

可根据数据分布调整批次粒度，比如每次处理100个groupID，避免单批次操作过多数据。

4. 先筛选再更新，缩小关联范围 #

通过EXISTS子查询先筛选出确实需要更新的customersPrimary行，再执行字段更新，减少不必要的关联操作：

UPDATE customersPrimary
SET
  name = (SELECT name FROM customersSecondary WHERE groupID = customersPrimary.groupID AND IDInGroup = customersPrimary.IDInGroup),
  address = (SELECT address FROM customersSecondary WHERE groupID = customersPrimary.groupID AND IDInGroup = customersPrimary.IDInGroup)
WHERE EXISTS (
  SELECT 1 FROM customersSecondary 
  WHERE groupID = customersPrimary.groupID 
    AND IDInGroup = customersPrimary.IDInGroup
    AND (
      customersPrimary.name <> customersSecondary.name
      OR customersPrimary.address <> customersSecondary.address
      OR (customersPrimary.name IS NULL XOR customersSecondary.name IS NULL)
      OR (customersPrimary.address IS NULL XOR customersSecondary.address IS NULL)
    )
);

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