在Laravel 5.7中插入/更新一百万行的最快方法 [英] Fastest way to insert/update a million rows in Laravel 5.7

问题描述

我正在使用Laravel 5.7从API服务器获取大量数据(大约50万行)，并将其插入表的频率很高(称为表A)(至少每6个小时一次，24/7)-但是，下次插入时仅插入更改就足够了(但是至少有60-70％的项目会更改).因此，该表将很快具有数千万行.

I'm using Laravel 5.7 to fetch large amounts of data (around 500k rows) from an API server and insert it into a table (call it Table A) quite frequently (at least every six hours, 24/7) - however, it's enough to insert only the changes the next time we insert (but at least 60-70% of the items will change). So this table will quickly have tens of millions of rows.

我想到了制作一个辅助表(称为表B)的想法，以将所有新数据存储到该表中.在将所有内容插入表A之前，我想将其与表B中的先前数据(使用Laravel，PHP)进行比较-因此，我将仅插入需要更新的记录.同样，它通常会占记录的60-70％.

I came up with the idea to make a helper table (call it Table B) to store all the new data into it. Before inserting everything into Table A, I want to compare it to the previous data (with Laravel, PHP) from Table B - so I will only insert the records that need to be updated. Again it will usually be around 60-70% of the records.

我的第一个问题是，在这种情况下(显然，我想使它尽快发生.)这种方法是否是上述首选方法?更新表中的记录将花费更多时间，并且会使表忙/锁定它.是否有更好的方法可以实现相同目的(意味着更新数据库中的记录).

My first question is if this above-mentioned way is the preferred way of doing it, in this situation (obviously I want to make it happen as fast as possible.) I assume that searching for an updating the records in the table would take a lot more time and it would keep the table busy / lock it. Is there a better way to achieve the same (meaning to update the records in the DB).

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

The second issue I'm facing is the slow insert times. Right now I'm using a local environment (16GB RAM, I7-6920HQ CPU) and MySQL is inserting the rows very slowly (about 30-40 records at a time). The size of one row is around 50 bytes.

我知道通过修改InnoDB的设置可以使它更快.但是，我也想以为我可以在Laravel方面做些事情来提高性能.

I know it can be made a lot faster by fiddling around with InnoDB's settings. However, I'd also like to think that I can do something on Laravel's side to improve performance.

现在，我的Laravel代码如下所示(一次只能插入1条记录):

Right now my Laravel code looks like this (only inserting 1 record at a time):

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级别(更改配置).

So my second question: is there any way to increase the inserting time of the records to something like 50k/second - both on the Laravel application layer (by doing batch inserts) and MySQL InnoDB level (changing the config).

当前的InnoDB设置:

Current InnoDB settings:

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.

MySQL version is 5.7.21.

如果我忘记告诉/添加任何内容，请在评论中让我知道，我会尽快进行.

If I forgot to tell/add anything, please let me know in a comment and I will do it quickly.

修改1: 我打算使用的服务器上将装有SSD-如果有任何不同的话.我认为MySQL插入仍将算作I/O.

Edit 1: The server that I'm planning to use will have SSD on it - if that makes any difference. I assume MySQL inserts will still count as I/O.

推荐答案

请勿在foreach()内部调用insert()，因为当您拥有n number of data时，它将在数据库中执行n number of queries.

Don't call insert() inside a foreach() because it will execute n number of queries to the database when you have n number of data.

首先创建一个与数据库列名匹配的数据对象数组.然后将创建的数组传递给insert()函数.

First create an array of data objects matching with the database column names. and then pass the created array to insert() function.

这将只对数据库执行one查询，而不管您有多少数据.

This will only execute one query to the database regardless of how many number of data you have.

这快得多，太快了.

$data_to_insert = [];

foreach ($response as $key => $value)
{
    array_push($data_to_insert, [
            'test1' => $value['test1'],
            'test2' => $value['test2'],
            'test3' => $value['test3'],
            'test4' => $value['test4'],
            'test5' => $value['test5'],
    ]);
}

DB::table('table_a')->insert($data_to_insert);

这篇关于在Laravel 5.7中插入/更新一百万行的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！