Mysql更新百万历史数据

场景

目前在main库里有一张main_test表，数据大约1000万

CREATE TABLE
    main.main_test
    (
        id BIGINT NOT NULL COMMENT '主键ID',
        name VARCHAR(100) COMMENT '名称',
        PRIMARY KEY (id)
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='主表';

需求为给main_test表添加一code列

ALTER TABLE main_test ADD COLUMN code  VARCHAR(60) COMMENT '代码' AFTER id ;

然后从另一个数据库sla的表slave_test关联更新main_test表的code,具体关联关系为

UPDATE main.main_test mt INNER JOIN sla.slave_test st ON mt.name = st.name SET mt.code = st.code;

问题

1. main_test表与slave_test表不在同一个库，因此不能直接关联更新
2. 即使在同一个库，但是main_test表数据量大，并且name字段没有索引，也不能临时添加索引，强行在生产环境更新会因为锁表阻塞导致应用雪崩

解决方案

1、在main库创建临时表main_test_tmp

CREATE TABLE
    main.main_test_tmp
    (
        id INT AUTO_INCREMENT PRIMARY KEY NOT NULL COMMENT '主键ID',
        test_id BIGINT COMMENT 'main_test表主键', 
        name VARCHAR(100) COMMENT '名称',
        code VARCHAR(60) COMMENT '代码'
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='主表';

2、查询slave_test表并导出到文件
建议导出为insert sql，并检查 name, code 值是否包含单引号’，将一个单引号’替换成两个单引号’，如’替换成’’，防止sql注入攻击。目标表修改为main_test_tmp

SELECT name, code FROM sla.slave_test WHERE code IS NOT NULL AND name IS NOT NULL

3、向main_test_tmp表插入文件中的sql
4、给main_test_tmp表name字段创建前缀索引
因为name字段长度为100，一些数据库对索引长度有限制，并且name字段的前10个字符唯一度已经很高

ALTER TABLE main_test_tmp ADD INDEX (name(10));

5、更新main_test_tmp表的test_id字段

UPDATE main_test_tmp mtt INNER JOIN main_test mt ON mtt.name = mt.name SET mtt.test_id = mt.id
WHERE mt.code IS NULL;

6、给main_test_tmp表test_id字段创建索引
注意，第6步只执行一次，以后不再执行

ALTER TABLE main_test_tmp ADD INDEX  (test_id);

7、查询main_test_tmp表是否存在test_id字段不为null的数据

SELECT * FROM main_test_tmp WHERE test_id IS NOT NULL LIMIT 1;

如果存在，则继续下一步，否则执行第11步。

8、更新main_test表的code字段

UPDATE main_test mt INNER JOIN main_test_tmp mtt ON mt.id = mtt.test_id SET mt.code = mtt.code
WHERE mtt.test_id IS NOT NULL;

9、main_test_tmp表test_id字段都更新为null

UPDATE TABLE main_test_tmp SET test_id = null;

10、回到第5步，重复步骤（需要跳过第6步）
11、main_test表的code字段更新完毕，删除临时表main_test_tmp

DROP TABLE main_test_tmp;

说明

以上只考虑了main_test表name字段重复的情况。