MySQL 2000万条数据按同user_id同批次规则分批更新的提速方案咨询

数据老陈

2026-9-27

现有方案性能瓶颈分析

你的方案慢的核心原因是以下3点：

子查询用LIMIT :offset, :limit分页，当offset达到几十万甚至上百万时，MySQL需要扫描跳过前面所有offset条记录才能拿到目标数据，越靠后的批次查询速度越慢
缺少针对过滤条件的覆盖索引，子查询取distinct user_id时需要回表，关联更新时匹配效率低
单次更新的数据量过大，InnoDB长事务持有行锁时间久，事务日志刷盘频繁，还容易出现锁等待

具体优化方案

1. 索引优化（成本最低，优先做）

新增覆盖联合索引：ALTER TABLE user_data ADD INDEX idx_set_user(set_no, user_id)

你所有的查询和更新都需要先过滤set_no=1的条件，这个索引可以让子查询取distinct user_id时直接走索引不需要回表，查询效率至少提升3倍
关联更新时的匹配逻辑也可以直接走该索引，不需要扫全表
注意：你原有代码里写的set=1是笔误，实际表结构字段是set_no，修正这个条件避免全表扫描

2. 替换offset分页为游标分页（核心优化）

彻底丢弃大offset分页逻辑，改为记录上一批次的最大user_id作为游标，下一次查询直接从游标位置往后取，不需要扫描前面的所有数据，性能比原分页逻辑高10倍以上：

// 原有统计总用户数逻辑可以保留
$query = "SELECT COUNT(DISTINCT user_id) from user_data WHERE set_no=1";
$stmt = $this->db->prepare($query);
$stmt->execute([':set_no'=> $this->set]);
$totalUserCount = $stmt->fetchColumn();

$limit = intval($totalUserCount/10);
$lastRecords = $totalUserCount%10;
$lastUserId = ''; // 游标，记录上一批次最大的user_id

for($i = 0 ; $i < 10 ; $i++)
{
    $currentLimit = $limit;
    // 前$lastRecords个批次多取1条，处理余数
    if($i < $lastRecords) $currentLimit +=1;
    
    $query = "UPDATE user_data t1 INNER JOIN (
                SELECT distinct user_id FROM user_data 
                WHERE set_no=1 AND user_id > :lastUserId 
                ORDER BY user_id ASC LIMIT :limit
              ) AS t2 
              ON t1.user_id = t2.user_id AND t1.set_no =1 
              SET batch_no=:batch_no";

    $stmt = $this->db->prepare($query);
    $batchNo = ($i+1);
    $stmt->bindParam(':batch_no',$batchNo,PDO::PARAM_INT);
    $stmt->bindParam(':lastUserId',$lastUserId,PDO::PARAM_STR);
    $stmt->bindParam(':limit',$currentLimit,PDO::PARAM_INT);
    $stmt->execute();

    // 更新游标为当前批次最大的user_id
    $maxStmt = $this->db->prepare("SELECT MAX(user_id) FROM user_data WHERE set_no=1 AND batch_no=:batch_no");
    $maxStmt->bindParam(':batch_no',$batchNo,PDO::PARAM_INT);
    $maxStmt->execute();
    $lastUserId = $maxStmt->fetchColumn();
}

3. 拆分大批次为小事务提交

如果单次更新的行数超过10万，可以把10个大批次再拆成每个1~2万行的小批次提交，避免长事务持有锁太久，也避免InnoDB undo日志膨胀。

4. 临时调整数据库参数（有权限的情况下操作，更新完改回原配置）

临时关闭自动提交：SET autocommit = 0，批量更新完再统一提交
临时调大事务日志：innodb_log_file_size调整为1G/2G，避免频繁刷日志
不需要同步从库的话临时关闭binlog，减少IO开销
临时调整刷盘策略：innodb_flush_log_at_trx_commit = 2，更新完改回1

5. 极速替代方案（如果user_id有固定规则）

如果你的user_id末尾几位是固定数字，可以直接用取模运算计算batch_no，不需要循环多次更新，一次性就能完成所有数据写入：

UPDATE user_data 
SET batch_no = MOD(CAST(RIGHT(user_id, 3) AS UNSIGNED), 10) + 1 
WHERE set_no =1;

该方案可以保证同一个user_id落在同一个批次，执行速度比循环更新快几十倍。

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