时间戳

首页 > 解决方案 > 在 Laravel 5.7 中插入/更新一百万行的最快方法

php - 在 Laravel 5.7 中插入/更新一百万行的最快方法

问题描述

我正在使用 Laravel 5.7 从 API 服务器获取大量数据（大约 500k 行）并将其插入到表中（称为表 A）非常频繁（至少每六个小时，24/7） - 但是，它是足以在我们下次插入时仅插入更改（但至少 60-70% 的项目会更改）。所以这张表很快就会有几千万行。

我想出了制作一个辅助表（称为表 B）来将所有新数据存储到其中的想法。在将所有内容插入表 A 之前，我想将其与表 B 中的先前数据（使用 Laravel、PHP）进行比较 - 所以我只会插入需要更新的记录。同样，它通常会占记录的 60-70% 左右。

我的第一个问题是，如果上述方式是首选方式，在这种情况下（显然我想让它尽快发生。）我假设搜索更新表中的记录需要一个更多的时间，它会让桌子保持忙碌/锁定它。有没有更好的方法来实现相同的（意味着更新数据库中的记录）。

我面临的第二个问题是插入时间很慢。现在我正在使用本地环境（16GB RAM，I7-6920HQ CPU），MySQL 插入行非常缓慢（一次大约 30-40 条记录）。一行的大小约为 50 字节。

我知道通过摆弄 InnoDB 的设置可以让它变得更快。但是，我也想认为我可以在 Laravel 方面做一些事情来提高性能。

现在我的 Laravel 代码如下所示（一次只插入 1 条记录）：

foreach ($response as $key => $value)
{
    DB::table('table_a')
        ->insert(
        [
            'test1' => $value['test1'],
            'test2' => $value['test2'],
            'test3' => $value['test3'],
            'test4' => $value['test4'],
            'test5' => $value['test5'],
        ]);
}

$response是一种数组。

所以我的第二个问题：有什么方法可以将记录的插入时间增加到 50k/秒 - 在 Laravel 应用程序层（通过执行批量插入）和 MySQL InnoDB 级别（更改配置）。

当前 InnoDB 设置：

innodb_buffer_pool_size        = 256M
innodb_log_file_size           = 256M
innodb_thread_concurrency      = 16
innodb_flush_log_at_trx_commit = 2
innodb_flush_method            = normal
innodb_use_native_aio = true

MySQL 版本是 5.7.21。

如果我忘记告诉/添加任何内容，请在评论中告诉我，我会尽快完成。

编辑 1： 我计划使用的服务器上将有 SSD - 如果这有什么不同的话。我假设 MySQL 插入仍然算作 I/O。

标签： phpmysqllaravel

解决方案

autocommit在插入结束时禁用并手动提交

根据 MySQL 8.0 文档。（8.5.5 InnoDB 表的批量数据加载）

您可以通过关闭自动提交来提高 INSERT 速度：

  • 将数据导入 InnoDB 时，请关闭自动提交模式，因为它会为每次插入执行日志刷新到磁盘。要在导入操作期间禁用自动提交，请使用 SET autocommit 和 COMMIT 语句将其括起来：
    SET autocommit=0;
    ... SQL import statements ...
    COMMIT;

在 Laravel 中执行此操作的其他方法是使用数据库事务：

DB::beginTransaction()

// Your inserts here

DB::commit()

INSERT与多个一起使用VALUES

此外，根据 MySQL 8.0 文档（8.2.5.1 Optimizing INSERT StatementsVALUES ），您可以通过在单个插入语句上使用多个来优化 INSERT 速度。

要使用 Laravel 做到这一点，您只需将一组值传递给该insert()方法：

DB::table('your_table')->insert([
   [
       'column_a'=>'value',
       'column_b'=>'value',
   ],
   [
       'column_a'=>'value',
       'column_b'=>'value',
   ],
   [
       'column_a'=>'value',
       'column_b'=>'value',
   ],
]);

根据文档，它可以快很多倍。

阅读文档

我在这篇文章中的两个 MySQL 文档链接都有大量关于提高 INSERT 速度的技巧。

避免使用 Laravel/PHP 来插入它

如果您的数据源是（或可以是）CSV 文件，您可以使用它来更快地运行它mysqlimport来导入数据。

使用 PHP 和 Laravel 从 CSV 文件导入数据是一种开销，除非您需要在插入之前进行一些数据处理。

推荐阅读