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

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

UPDATE table 
SET order_items_id = CASE WHEN stock_out_item_id = 1 THEN 100WHEN stock_out_item_id = 2 THEN 200ELSE 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中处理大数据量更新的标准实践。