MySQL基于关联表更新字段过慢问题（已添加索引）

我来帮你捋捋这个问题——这种批量更新慢的情况，哪怕加了索引，也经常会碰到几个常见的坑，咱们一步步拆解优化：

一、先检查你的更新语句写法是否高效 #

很多人会用子查询来做同步，但子查询在处理大表时性能很差，推荐用JOIN关联的方式直接更新，这是提升速度的关键。

不同数据库的高效写法示例： #

MySQL/MariaDB：

UPDATE property p
INNER JOIN postcodelatlng pl 
  ON p.postcode = pl.postcode
SET p.latitude = pl.latitude, 
    p.longitude = pl.longitude
WHERE p.latitude IS NULL 
   OR p.longitude IS NULL;

PostgreSQL：

UPDATE property p
SET latitude = pl.latitude,
    longitude = pl.longitude
FROM postcodelatlng pl
WHERE p.postcode = pl.postcode
  AND (p.latitude IS NULL OR p.longitude IS NULL);

这种JOIN方式比子查询更高效，数据库能更好地利用索引做关联匹配。

二、确认索引真的在生效 #

你说已经加了索引，但得确保索引是正确的、被用到的：

• 对postcodelatlng表：给postcode加唯一索引（因为每个邮编对应唯一经纬度，唯一索引比普通索引性能更好）：

  CREATE UNIQUE INDEX idx_postcodelatlng_postcode ON postcodelatlng(postcode);
  

• 对property表：给postcode加普通索引，同时可以考虑联合索引（如果查询/更新经常用到postcode + latitude + longitude的组合）：

  CREATE INDEX idx_property_postcode ON property(postcode);
  -- 可选：如果WHERE条件经常判断lat/lng为空，可加联合索引
  CREATE INDEX idx_property_postcode_lat_lng ON property(postcode, latitude, longitude);
  

• 用EXPLAIN分析你的更新语句，看是否真的用到了索引：

  -- MySQL
  EXPLAIN UPDATE property p JOIN postcodelatlng pl ON p.postcode=pl.postcode SET ... WHERE ...;
  
  -- PostgreSQL
  EXPLAIN ANALYZE UPDATE property p FROM postcodelatlng pl WHERE ...;
  

  如果输出里出现ALL（全表扫描），那说明索引没生效，得检查索引是否正确，或者是否有隐式类型转换（比如postcode字段类型不一致，一个是字符串一个是数字）。

三、大表分批次更新，避免锁表/资源耗尽 #

如果property表数据量特别大（比如百万级以上），一次性更新所有符合条件的行，会占用大量数据库连接、锁表时间过长，导致速度变慢甚至影响其他业务。分批次更新是最优解：

示例（按主键分段，每次更新1000条）： #

-- MySQL/MariaDB，循环执行直到影响行数为0
UPDATE property p
INNER JOIN postcodelatlng pl 
  ON p.postcode = pl.postcode
SET p.latitude = pl.latitude, 
    p.longitude = pl.longitude
WHERE (p.latitude IS NULL OR p.longitude IS NULL)
  AND p.id BETWEEN 1 AND 1000;

-- 下一次执行：BETWEEN 1001 AND 2000，以此类推

如果不想手动改数值，也可以用存储过程自动循环，比如MySQL的存储过程：

DELIMITER //
CREATE PROCEDURE batch_update_lat_lng()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE start_id INT DEFAULT 1;
  DECLARE batch_size INT DEFAULT 1000;
  WHILE done = 0 DO
    UPDATE property p
    INNER JOIN postcodelatlng pl ON p.postcode = pl.postcode
    SET p.latitude = pl.latitude, p.longitude = pl.longitude
    WHERE (p.latitude IS NULL OR p.longitude IS NULL)
      AND p.id >= start_id 
      AND p.id < start_id + batch_size;
    IF ROW_COUNT() = 0 THEN
      SET done = 1;
    END IF;
    SET start_id = start_id + batch_size;
    -- 可选：每次更新后暂停1秒，避免过度占用资源
    SELECT SLEEP(1);
  END WHILE;
END //
DELIMITER ;

-- 调用存储过程
CALL batch_update_lat_lng();

四、其他优化小技巧 #

• 先过滤再更新：先用SELECT统计符合条件的行数，确认数据范围，避免更新不必要的行：

  SELECT COUNT(*)
  FROM property p
  JOIN postcodelatlng pl ON p.postcode = pl.postcode
  WHERE p.latitude IS NULL OR p.longitude IS NULL;
  

• 临时调整数据库参数：比如InnoDB的innodb_log_file_size，如果批量更新产生大量日志，临时调大日志文件大小可以减少刷盘次数（更新完记得改回原配置）。
• 避开业务高峰：在低峰期执行更新操作，避免影响正常业务。

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