百万级表UPDATE含CONCAT操作过慢，求优化方案

针对全表UPDATE拼接字段导致的性能问题，以下是几个实用优化方向：

一、关闭自动提交，减少事务开销 #

默认数据库会为每一行更新单独提交事务，120万行将产生120万次事务操作，极大消耗资源。手动关闭自动提交，完成所有更新后再一次性提交：

SET autocommit = 0;

UPDATE [TABLE A]
SET `address 1` = CONCAT("Taiwan",`area`,`road`,`lane`,alley,number),
    `address 2` = CONCAT("Taiwan",`area`,`town`,`road`,lane,alley,number);

COMMIT;

二、分批更新，避免锁表与内存溢出 #

一次性更新全表会长时间占用表锁，影响其他业务，同时可能耗尽内存。按主键/唯一索引分批更新，每次处理1万-5万行：

SET autocommit = 0;
SET @last_id = 0;

REPEAT
  UPDATE [TABLE A]
  SET `address 1` = CONCAT("Taiwan",`area`,`road`,`lane`,alley,number),
      `address 2` = CONCAT("Taiwan",`area`,`town`,`road`,lane,alley,number)
  WHERE id > @last_id LIMIT 10000; -- 假设表有自增主键id，无主键可换唯一字段或范围条件
  
  SET @last_id = @last_id + 10000;
UNTIL ROW_COUNT() = 0 END REPEAT;

COMMIT;

三、用临时表预处理，再关联更新 #

先将拼接好的结果写入临时表（临时表操作更快），再通过主键关联更新原表，减少原表的直接写入压力：

-- 创建带主键的临时表
CREATE TEMPORARY TABLE temp_addr (
  id INT PRIMARY KEY,
  addr1 VARCHAR(255),
  addr2 VARCHAR(255)
);

-- 批量插入拼接后的地址数据
INSERT INTO temp_addr (id, addr1, addr2)
SELECT 
  id,
  CONCAT("Taiwan",`area`,`road`,`lane`,alley,number),
  CONCAT("Taiwan",`area`,`town`,`road`,lane,alley,number)
FROM [TABLE A];

-- 关联更新原表
UPDATE [TABLE A] a
JOIN temp_addr t ON a.id = t.id
SET a.`address 1` = t.addr1, a.`address 2` = t.addr2;

-- 清理临时表
DROP TEMPORARY TABLE temp_addr;

四、临时禁用触发器与外键约束 #

如果表上有UPDATE触发器或外键约束，这些会在更新时执行额外逻辑/检查，大幅拖慢速度。临时禁用它们，更新完成后恢复：

-- 关闭外键检查
SET FOREIGN_KEY_CHECKS = 0;
-- 禁用所有触发器（MySQL环境）
ALTER TABLE [TABLE A] DISABLE TRIGGER ALL;

-- 执行更新操作（可搭配上述分批或临时表方案）
UPDATE ...;

-- 恢复外键检查与触发器
SET FOREIGN_KEY_CHECKS = 1;
ALTER TABLE [TABLE A] ENABLE TRIGGER ALL;

五、调整数据库临时参数（按需操作） #

• 增大innodb_buffer_pool_size：让更多数据在内存中处理，减少磁盘IO（若内存充足）。
• 增大innodb_log_file_size：减少 redo log 切换次数，提升写入效率。
• 临时关闭binlog：若无需备份/主从同步，执行SET sql_log_bin = 0;关闭二进制日志，更新完成后再开启（SET sql_log_bin = 1;）。

六、优化字段类型与长度 #

确认address 1、address 2的字段长度刚好满足拼接后的需求（不要过大），避免不必要的存储空间占用；同时检查用于拼接的字段（如area、road等）是否为合适的字符类型，避免因类型转换额外消耗资源。

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