当写脚本循环更新几百万数据发现很慢怎么办

1. 核心逻辑：CASE WHEN条件更新

UPDATE table 
SET order_items_id = 
  CASE 
    WHEN stock_out_item_id = 1 THEN 100
    WHEN stock_out_item_id = 2 THEN 200
    ELSE order_items_id 
  END
WHERE stock_out_item_id IN (1,2)

• 原理：通过单条SQL实现多条记录的条件更新，每个WHEN对应一个stock_out_item_id到order_item_id的映射
• ELSE保留原值：确保不在更新列表中的记录保持不变
• WHERE IN过滤：仅更新指定ID的记录，避免全表扫描

以php代码为例

public function handle()
    {
        $this->syncStockoutItemsSaleOrderItemsIdV2();
    }


    public function syncStockoutItemsSaleOrderItemsIdV2(){
        // 关闭查询日志以减少内存使用
        DB::connection('mysql')->disableQueryLog();
        
        // 记录开始时间
        $startTime = microtime(true);
        $updatedCount = 0;
        DB::connection("mysql")->table('lie_stock_out_items')
            ->orderBy("stock_out_item_id","desc")
            ->where("order_items_id",0)
            ->where("ext_info","like","%order_item_id%")
            ->chunk(5000, function ($stockOutItemsList) use (&$updatedCount) {
                // 准备批量更新的数据
                $itemsUpdateData = []; // 用于lie_stock_out_items表的更新数据
                $reportUpdateData = []; // 用于lie_stock_out_report表的更新数据
                
                foreach($stockOutItemsList as $stockOutItems){
                    $extinfo = json_decode($stockOutItems->ext_info, true);
                    
                    if(!empty($extinfo) && !empty($extinfo["order_item_id"])){
                        $stockOutItemId = $stockOutItems->stock_out_item_id;
                        $orderItemId = $extinfo["order_item_id"];
                        
                        // 收集需要更新的数据，使用关联数组存储
                        $itemsUpdateData[$stockOutItemId] = $orderItemId;
                        $reportUpdateData[$stockOutItemId] = $orderItemId;
                        $updatedCount++;
                    }
                }
                // dump($itemsUpdateData);
                // dump($reportUpdateData);
                // 执行批量更新
                $this->batchUpdate('lie_stock_out_items', $itemsUpdateData);
                $this->batchUpdate('lie_stock_out_report', $reportUpdateData);
            });
        
        // 输出执行统计
        $endTime = microtime(true);
        $executionTime = round($endTime - $startTime, 2);
        echo "批量更新完成！共处理 {$updatedCount} 条记录，耗时 {$executionTime} 秒\n";
    }
    
    // 批量更新方法
    private function batchUpdate($table, $updateData){
        if(empty($updateData)) return;
        
        // 分批处理大数组，每批最多处理2000条，避免SQL语句过长
        $chunks = array_chunk($updateData, 2000, true);
        
        foreach($chunks as $chunk){
            $cases = [];
            $params = [];
            $ids = [];
            
            // 构建CASE语句
            foreach ($chunk as $stockOutItemId => $orderItemId) {
                $ids[] = $stockOutItemId;
                $cases[] = "WHEN stock_out_item_id = ? THEN ?";
                $params[] = $stockOutItemId;
                $params[] = $orderItemId;
            }
            
            // 执行批量更新
            $idsStr = implode(',', $ids);
            $casesStr = implode(' ', $cases);
            
            $query = "UPDATE {$table} SET order_items_id = CASE {$casesStr} ELSE order_items_id END WHERE stock_out_item_id IN ({$idsStr})";
            // dump($query);
            // dump($params);
            DB::connection('mysql')->statement($query, $params);
        }
    }

分块处理机制（关键优化）

 

$chunks = array_chunk($updateData, 2000, true); // 每批2000条

 

• 必要性：MySQL对单条SQL语句长度有限制（max_allowed_packet默认4MB），超大数据量会导致：
  • 语法解析失败
  • 内存溢出
  • 网络传输超时
• 分块策略：保持原始键值对关系（true参数），确保更新时ID与新值正确对应

3. 参数绑定防注入

 

$params = [1,100,2,200]; // 实际绑定值
DB::statement($query, $params); // 使用PDO预处理

 

• 安全机制：所有动态值通过?占位符绑定，避免SQL注入
• 参数顺序：绑定顺序需与CASE中的?出现顺序严格对应

4. 性能对比优势

更新方式	SQL次数	数据库交互	事务开销	适用场景
传统循环更新	N次	N次	N次	小数据量
本方案	1次	1次	1次	大数据量批量更新
Eloquent批量更新	1次	1次	1次	简单字段更新

执行流程示例

假设输入：

$updateData = [
    101 => 2001,
    102 => 2002,
    // ... 共3000条
];

处理步骤：

1. 分成2个块（每块2000条）
2. 生成SQL片段：

-- 块1
UPDATE table 
SET order_items_id = CASE 
  WHEN stock_out_item_id=101 THEN 2001
  ... # 共2000个WHEN
END 
WHERE stock_out_item_id IN (101,102,...,2000)

-- 块2
UPDATE table 
SET order_items_id = CASE 
  WHEN stock_out_item_id=2001 THEN 2003
  ... # 剩余1000个WHEN
END 
WHERE stock_out_item_id IN (2001,2002,...,3000)

这种方案在百万级数据更新场景下，比传统循环更新快10倍以上，同时避免内存溢出风险，是Laravel中处理大数据量更新的标准实践。