优化MySQL地址表去重及外键关联调整的查询性能

原SQL性能极差的核心根因 #

当前写法耗时73小时并非数据量过大导致，核心是三个设计缺陷：

• 逐行触发重复计算：标量子查询的执行逻辑是每更新1行customer_address，就重新执行一次「分组算最小id+关联地址表匹配」的全量计算，本来只需要算1次的39万组映射，被重复触发了百万次，计算开销被放大了几百倍
• 索引不匹配：之前单独给city/zip_code/street/house_number建的单列索引完全没法支撑多列等值匹配的分组场景，优化器每次触发子查询都要全表扫90万条address记录算聚合
• 逐行触发外键校验：每更新1条customer_address的address_id字段，就会触发一次外键关联合法性校验，几十万次校验的累积开销占了总耗时的近3成

可落地的极速优化步骤 #

整个流程把「重复计算」改成「一次性计算映射+批量集合更新」，90万条数据量级下全流程耗时一般不会超过10分钟：

第一步：建联合覆盖索引，消除重复映射计算的全表扫开销 #

给address表建和匹配维度完全对齐的联合覆盖索引，让分组算最小id的操作直接走索引，完全不用回表：

ALTER TABLE address ADD INDEX idx_addr_match (zip_code, city, street, house_number, id);

这个索引覆盖了所有匹配、分组、取min(id)需要的字段，原本全表扫90万行算聚合的操作，直接通过索引顺序扫描完成，速度提升10倍以上。注意匹配逻辑要使用null安全判断，避免字段为NULL时的匹配遗漏

第二步：一次性固化新旧id的映射关系，不要在更新时临时算 #

建临时映射表，一次性把所有地址的对应保留id算完，只跑1次聚合计算，没有任何重复开销：

-- 建临时映射表
CREATE TABLE tmp_address_id_map (
  old_address_id varchar(255) NOT NULL COMMENT '原address表的id',
  new_address_id varchar(255) NOT NULL COMMENT '同组保留的最小id',
  PRIMARY KEY (old_address_id),
  KEY (new_address_id)
) ENGINE=InnoDB;

-- 一次性灌入所有映射关系，走刚才建的覆盖索引计算，几秒钟就能完成
INSERT INTO tmp_address_id_map (old_address_id, new_address_id)
SELECT 
  a.id AS old_address_id,
  MIN(a2.id) AS new_address_id
FROM address a
INNER JOIN address a2 
  ON a2.zip_code <=> a.zip_code
  AND a2.city <=> a.city
  AND a2.street <=> a.street
  AND a2.house_number <=> a.house_number
GROUP BY a.id;

这里用<=>运算符是因为常规等值判断对NULL值会返回unknown，原本的SQL写法会漏掉所有匹配字段含NULL的重复地址，属于潜在逻辑bug

第三步：批量关联更新customer_address，跳过不必要的外键校验 #

把逐行触发的更新改成集合关联的批量更新，同时临时关闭外键检查减少校验开销：

-- 临时关闭外键检查，避免逐行校验的开销
SET FOREIGN_KEY_CHECKS = 0;

-- 批量关联映射表更新，只更新实际需要调整id的行
UPDATE customer_address ca
INNER JOIN tmp_address_id_map m 
  ON ca.address_id = m.old_address_id
SET ca.address_id = m.new_address_id
WHERE ca.address_id != m.new_address_id;

-- 更新完成后恢复外键检查
SET FOREIGN_KEY_CHECKS = 1;

如果customer_address表本身数据量超过10万行，建议按主键分批更新（每次更新1000-2000行），避免长事务占满undo log导致锁等待：

-- 按自增主键id分批更新，循环跑直到影响行数为0
UPDATE customer_address ca
INNER JOIN tmp_address_id_map m ON ca.address_id = m.old_address_id
SET ca.address_id = m.new_address_id
WHERE ca.id BETWEEN :start_id AND :start_id + 1999
  AND ca.address_id != m.new_address_id;

第四步：清理重复的address记录 #

等所有关联关系都调整完成后，删除没有被引用的重复地址数据：

-- 删除所有重复的、已经被替换掉的旧address记录
DELETE a 
FROM address a
INNER JOIN tmp_address_id_map m 
  ON a.id = m.old_address_id
WHERE a.id != m.new_address_id;

-- 清理临时表
DROP TABLE tmp_address_id_map;

优化效果说明 #

原来的写法因为重复计算、全表扫、逐行触发校验，耗时是小时级；优化后所有聚合计算只跑1次，更新是集合操作带主键匹配，全流程是分钟级，整体性能提升百倍以上。

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