如何高效利用另一张大数据MySQL表更新目标大数据表？

核心思路 #

先从表A提取唯一name-code映射（仅11000行），避免重复扫描200万行数据，再基于映射批量更新表B，同时优化索引和更新语句的执行方式。

步骤1：预处理表A，生成唯一name-code映射 #

创建临时表存储每个唯一名称对应的code（如果同一name对应多个code，用MAX/MIN取固定值，根据实际业务需求调整）：

CREATE TEMPORARY TABLE name_code_map AS
SELECT name, MAX(code) AS code
FROM my_records
GROUP BY name;

给临时表加索引，加速后续关联查询：

ALTER TABLE name_code_map ADD INDEX idx_name (name);

步骤2：批量更新表B #

方案A：一次性JOIN更新

直接用UPDATE JOIN语句批量更新，比单条循环效率高几个数量级：

UPDATE my_records_new t2
JOIN name_code_map t1 ON t2.name = t1.name
SET t2.code = t1.code
WHERE t2.code = '';

方案B：分批次更新（避免单次超时）

如果一次性更新仍超时，按限制行数分批次执行，循环直到影响行数为0：

UPDATE my_records_new t2
JOIN name_code_map t1 ON t2.name = t1.name
JOIN (
    SELECT ID FROM my_records_new WHERE code = '' LIMIT 10000
) t3 ON t2.ID = t3.ID
SET t2.code = t1.code;

步骤3：解决索引添加超时问题 #

给my_records_new的name字段加索引时，用在线DDL减少锁表和执行时间（MySQL 5.6+支持）：

ALTER TABLE my_records_new ADD INDEX idx_name (name) ALGORITHM=INPLACE, LOCK=NONE;

如果是老版本MySQL，选择业务低峰期执行索引添加操作。

优化你的PHP代码 #

将循环单条UPDATE改为批量更新，减少数据库交互开销：

global $wpdb;
$sql = "SELECT t2.ID, t1.code FROM my_records AS t1 INNER JOIN my_records_new AS t2 ON t1.name = t2.name WHERE t2.code = '' LIMIT 20000;";
$rows = $wpdb->get_results($sql);

if (!empty($rows)) {
    $values = [];
    foreach ($rows as $obj) {
        // 转义避免SQL注入
        $escaped_code = $wpdb->escape($obj->code);
        $values[] = "({$obj->ID}, '{$escaped_code}')";
    }
    // 用INSERT ON DUPLICATE KEY实现批量更新
    $batchSql = "INSERT INTO my_records_new (ID, code) VALUES " . implode(',', $values) . " ON DUPLICATE KEY UPDATE code = VALUES(code);";
    $wpdb->query($batchSql);
}

注意事项 #

• 操作前务必备份表，避免数据异常；
• 确保表A中每个name对应的code是唯一的，若有多个需先明确取值规则；
• 大表操作尽量选择业务低峰期执行。

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