优化数据库更新

Question

我必须更新大量的数据，到目前为止，我有以下内容：

protected function updateWidgetsDb($APIWidgets, $date)
{
    echo "Eager loading...";
    $widgets = Widget::where('date', $date)->get();
    echo "Done\n";
    echo "Updating...";
    foreach ($APIWidgets as $APIWidget) {
        $widget = $widgets->where('widget_id', $APIWidget->dimensions[0])->first();

        if ($widget == null)
            continue;

        $widget->update(['revenue' => $APIWidget->metrics[0]->values[0]]);
    }
    echo "Done\n";
}

$APIWidgets是通过外部API获取的数组。我只获取特定日期的数据，所以我只加载数据库中已经存在的该日期的数据。

widget_id字段在DB中被索引

编辑：

我有大约60000的数据和大约2k的数据，我正在收到更新。这2k的每个记录都有一个ID，该ID已经可以在现有的60k数据中找到。因此，在更新后，数据的总和应该仍然是60k，而不是62k。

目前，更新过程需要10分钟。

Answer 1

我可以提出两种方法来加快大规模更新任务。我试图重现您的问题，因此在我的widgets表中创建了一组60k*7项，其中包含了widget_id,date和widget_id索引。

1. 首先，让我们加快获取$widget的速度。当我把$widgets->where('widget_id', $APIWidget->id)替换成 小部件：：where(‘date’，$date) ->where('widget_id'，$APIWidget->id) 剧本变得快了400倍。看起来，使用索引widget_id, date获取2000年mysql比通过60000大小的集合搜索Laravel集合更快。查询的结果是16秒，集合的结果是6400秒。
2. 那么让我们更快地更新。有一种很好的方法来更新表，只有一个查询使用临时表。因此，我创建了一个新的实体WidgetUpdate，其中包含表widget_updates和字段id, widget_id, revenue_new。 我修改了您的方法:首先收集一个更新数组，然后对widget_updates表进行批量插入，最后执行一个更新查询。我的机器运行时间为2.2秒，速度是我的5倍。对于我的情况，最后的加速比是2000倍。 受保护的函数updateWidgetsDb ($APIWidgets，$date) { echo“更新.”；$updates = []；foreach ($APIWidgets as $APIWidget) { $widget = Widget::where('date'，$date) ->where('widget_id'，$APIWidget->维度) ->first()；if ($widget == null)；$updates[] =[revenue_new‘=> $APIWidget->度量->值]；}#插入和更新WidgetUpdate::insert($updates)；DB：：语句(‘UPDATE，widget_updates’)。“设置widgets.revenue=widget_updates.revenue_new”。‘'WHERE widgets.id = widget_updates.widget_id')；回波“完成\n”；}

别忘了清理完成后的临时表。