生产环境百万级MariaDB数据表批量更新方案可行性及最优解咨询

你的方案是否可行？ #

整体是可行的。分批次处理的思路能有效避免一次性全量更新对生产库造成的锁表、CPU/IO过载问题，临时表作为中间载体也能减少直接更新主表的压力。但这个方案存在几个可优化的细节：

• 临时表test_table1_2未给table1_id加索引，更新时关联test_table2会触发全表扫描，批次量大时性能会明显下降。
• 如果分批次查询用LIMIT offset, 1000的方式，当偏移量增大到数十万级时，数据库需要扫描大量无关数据才能定位目标记录，查询效率会越来越低。
• 脚本先拉取所有关联记录再做条件判断，会把不需要更新的记录也拉到程序中，浪费网络和内存资源。

最优解决方案 #

结合生产环境的性能要求，推荐以下优化后的实施方案：

1. 前置准备：给关联字段加索引 #

如果test_table2的table1_id没有索引，先在低峰期创建索引（避免影响业务）：

ALTER TABLE test_table2 ADD INDEX idx_table1_id (table1_id);

这能大幅提升两表关联查询和更新的效率。

2. 优化临时表结构 #

给临时表的关联字段加主键索引，避免更新时的全表扫描：

CREATE TEMPORARY TABLE test_table1_2 (
    `table1_id` int(10) unsigned NOT NULL,
    `full_name` text,
    `full_name_esp` text,
    PRIMARY KEY (table1_id)
);

3. 分批次查询：用主键范围替代偏移量分页 #

不要用LIMIT offset, size，改用主键范围分段的方式精准查询需要更新的记录，比如按table1_id的范围切分：

-- 示例：每次查询table1_id在[start_id, end_id)范围内的待更新记录
SELECT t2.table1_id, CONCAT(t1.name, ' ', t1.surname) AS target_full_name
FROM test_table2 t2
JOIN test_table1 t1 ON t2.table1_id = t1.id
WHERE t2.table1_id BETWEEN ? AND ?
  AND (t2.full_name = '' OR t2.full_name != CONCAT(t1.name, ' ', t1.surname));

这种方式每次查询都是精准定位，不会随着批次增加而变慢。

4. 批量处理翻译与更新 #

• 如果翻译支持批量接口，将一个批次内的target_full_name批量传给翻译服务，一次性获取所有西班牙语翻译，减少网络请求开销。
• 将待更新的table1_id、target_full_name、翻译后的full_name_esp批量插入临时表。
• 执行高效更新（临时表有主键索引，关联速度极快）：

UPDATE test_table2 t2
JOIN test_table1_2 tmp ON t2.table1_id = tmp.table1_id
SET t2.full_name = tmp.full_name, t2.full_name_esp = tmp.full_name_esp;

• 截断临时表，进入下一批次，直到所有符合条件的记录处理完成。

5. 额外性能保障措施 #

• 批次大小可根据生产库负载调整：从1000条开始，观察CPU、IO情况后，可调整至2000或5000条。
• 用事务包裹每个批次的插入和更新操作，确保批次内数据一致性，出错时可回滚重试。
• 选择业务低峰期执行整个流程，进一步降低对生产业务的影响。

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