如何高效更新MySQL中10000行以上的数据？

我需要查询一张数据表并更新其中的数据，但数据量超过10000行，目前的代码执行时服务器会崩溃，想请教高效的处理方法。

我的数据表结构大概是这样：

当前使用的代码（DB::query等价于mysqli_query）：

$ss = DB::fetch_all("SELECT *FROM t1 ORDER BY rank ASC");
$x = 1;
foreach($ss as $sr){ 
    DB::query("UPDATE t1 SET newrank = '" . $x . "' WHERE id = '" . $sr['id'] . "'"); 
    $x++; 
}

你的问题核心是循环执行单条UPDATE语句导致数据库连接/服务器资源耗尽——1万次单独的UPDATE请求会产生大量的网络IO和数据库事务开销，服务器扛不住太正常了。下面给你几个从简单到进阶的优化方案：

1. 用SQL原生语句直接完成更新（最优解） #

既然你的newrank是按照rank排序后的自增序号，完全不需要用PHP循环，直接让数据库自己计算更新：

SET @x := 0;
UPDATE t1 
SET newrank = (@x := @x + 1)
ORDER BY rank ASC;

如果你的数据库是MySQL 8.0+，还可以用窗口函数更优雅地实现：

UPDATE t1
JOIN (
    SELECT id, ROW_NUMBER() OVER(ORDER BY rank ASC) AS rn
    FROM t1
) AS temp ON t1.id = temp.id
SET t1.newrank = temp.rn;

这种方法只需要1次数据库请求，直接在数据库层面完成计算和更新，性能提升几个数量级，完全不会有服务器崩溃的问题。

2. 批量执行UPDATE（如果必须用PHP处理） #

如果因为业务逻辑限制必须用PHP处理，那就把多个UPDATE合并成批量操作，减少请求次数：

方法A：用CASE语句批量更新 #

一次性生成包含所有更新逻辑的SQL：

$ss = DB::fetch_all("SELECT id FROM t1 ORDER BY rank ASC");
$updateCases = [];
$x = 1;
foreach($ss as $sr){
    $updateCases[] = "WHEN id = " . (int)$sr['id'] . " THEN " . $x;
    $x++;
}
$updateSql = "UPDATE t1 SET newrank = CASE " . implode(' ', $updateCases) . " END WHERE id IN (" . implode(',', array_column($ss, 'id')) . ")";
DB::query($updateSql);

这种方式把1万次请求变成1次，极大降低开销。注意：如果数据量特别大（比如10万+），可以拆分多个批次（比如每2000条执行一次），避免单条SQL过长。

方法B：开启事务+分批执行 #

开启数据库事务，每处理N条提交一次，避免长时间占用锁和连接：

try {
    DB::query("START TRANSACTION");
    $ss = DB::fetch_all("SELECT id FROM t1 ORDER BY rank ASC");
    $batchSize = 2000; // 每批次处理2000条
    $x = 1;
    foreach(array_chunk($ss, $batchSize) as $batch){
        $updateCases = [];
        foreach($batch as $sr){
            $updateCases[] = "WHEN id = " . (int)$sr['id'] . " THEN " . $x;
            $x++;
        }
        $updateSql = "UPDATE t1 SET newrank = CASE " . implode(' ', $updateCases) . " END WHERE id IN (" . implode(',', array_column($batch, 'id')) . ")";
        DB::query($updateSql);
        DB::query("COMMIT");
        DB::query("START TRANSACTION");
    }
    DB::query("COMMIT");
} catch(Exception $e){
    DB::query("ROLLBACK");
    throw $e;
}

3. 额外优化建议 #

• 确保id和rank字段有索引，这样查询和排序的速度会快很多；
• 避免用SELECT *，只查询需要的字段（比如这里只需要id），减少内存占用；
• 如果服务器配置较低，可以临时调整PHP的max_execution_time和memory_limit，但这只是临时方案，核心还是优化SQL逻辑。

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