使用Laravel在MySQL中导入大型CSV文件

我有一个csv文件,范围从50k到超过100k行数据.

我目前正在使用Laravel w/Laravel Forge,MySQL和Maatwebsite Laravel Excel软件包.

这是由最终用户而不是我自己使用,所以我在我的刀片视图上创建了一个简单的表单:

{!! Form::open(
    array(
        'route' => 'import.store', 
        'class' => 'form',
        'id' => 'upload',
        'novalidate' => 'novalidate', 
        'files' => true)) !!}

    

        
CSV Product Import
        {!! Form::file('upload_file', null, array('class' => 'file')) !!}
    

    

        {!! Form::submit('Upload Products', array('class' => 'btn btn-success')) !!}
    
{!! Form::close() !!}

然后,这将成功地将文件存储在服务器上,现在我可以使用诸如foreach循环之类的东西来迭代结果.

现在这里是我按时间顺序和修复/尝试面临的问题:(10k行测试csv文件)

[问题] PHP超时.

[remedy]将其更改为通过作业命令异步运行.

[result]最多可输入1500行.

[问题]服务器内存不足.

[补救措施]添加了1GB的交换驱动器.

[结果]最多可输入3000行.

[问题]服务器内存不足.

[补救]打开每块250行的分块结果.

[result]最多可输入5000行.

[问题]服务器内存不足.

[补救]删除了一些转置/连接表逻辑.

[result]最多可输入7000行.

正如你所看到的结果是边缘的,远不及50k,我甚至几乎不能接近10k.

我已经阅读并研究了可能的建议,例如:

使用原始查询运行"加载数据本地Infile".

导入前拆分文件.

存储在服务器上然后将服务器拆分成文件并让cron处理它们.

作为最后的手段,将我的512mb DO液滴升级到1gb.

使用加载数据本地infile可能无法工作,因为我的标题列可能会更改每个文件,这就是为什么我有逻辑来处理/迭代它们.

在导入之前拆分文件在10k以下是好的但是对于50k或更多？那将是非常不切实际的.

存储在服务器上然后让服务器拆分并单独运行它们而不会给最终用户带来麻烦？可能但甚至不确定如何在PHP中实现这一点,但只是简单地了解一下.

还要注意,我的队列工作程序设置为10000秒超时,这也是非常不切实际和糟糕的做法,但似乎这是它在内存受到打击之前继续运行的唯一方式.

现在我可以放弃并将内存升级到1gb,但我觉得它最好可能会在重新失败之前将我跳到20k行.有些东西需要快速有效地处理所有这些行.

最后这里是我的表结构的一瞥:

Inventory
+----+------------+-------------+-------+---------+
| id | profile_id | category_id |  sku  |  title  |
+----+------------+-------------+-------+---------+
|  1 |         50 |       51234 | mysku | mytitle |
+----+------------+-------------+-------+---------+

Profile
+----+---------------+
| id |     name      |
+----+---------------+
| 50 | myprofilename |
+----+---------------+

Category
+----+------------+--------+
| id | categoryId |  name  |
+----+------------+--------+
|  1 |      51234 | brakes |
+----+------------+--------+

Specifics
+----+---------------------+------------+-------+
| id | specificsCategoryId | categoryId | name  |
+----+---------------------+------------+-------+
|  1 |                  20 |      57357 | make  |
|  2 |                  20 |      57357 | model |
|  3 |                  20 |      57357 | year  |
+----+---------------------+------------+-------+

SpecificsValues
+----+-------------+-------+--------+
| id | inventoryId | name  | value  |
+----+-------------+-------+--------+
|  1 |           1 | make  | honda  |
|  2 |           1 | model | accord |
|  3 |           1 | year  | 1998   |
+----+-------------+-------+--------+

Full CSV Sample
+----+------------+-------------+-------+---------+-------+--------+------+
| id | profile_id | category_id |  sku  |  title  | make  | model  | year |
+----+------------+-------------+-------+---------+-------+--------+------+
|  1 |         50 |       51234 | mysku | mytitle | honda | accord | 1998 |
+----+------------+-------------+-------+---------+-------+--------+------+

因此,尽可能简单地快速浏览我的逻辑工作流程:

将文件加载到Maatwebsite/Laravel-Excel中并遍历一个分块循环

检查category_id和sku是否为空,否则忽略并将错误记录到数组中.

查找category_id并从它使用的所有相关表中提取所有相关列字段,然后如果没有空插入数据库.

使用文件中可用的字段使用更多逻辑生成自定义标题.

冲洗并重复.

最后,将errors数组导出到一个文件中,并将其记录到数据库中进行下载,以便最后查看错误.

我希望有人可以与我分享一些关于我应该如何解决这个问题的一些可能的想法,同时记住使用Laravel,并且它不是一个简单的上传,我需要处理并放入不同的相关表格,否则我会加载数据一次性输入所有内容.

谢谢!

您似乎已经找到了解释CSV行并将其转换为在数据库上插入查询的逻辑,因此我将重点关注内存耗尽问题.

在PHP中处理大型文件时,任何将整个文件加载到内存的方法都会失败,变得难以忍受地慢或者需要比Droplet更多的RAM.

所以我的建议是:

使用逐行读取文件 fgetcsv

$handle = fopen('file.csv', 'r');
if ($handle) {
    while ($line = fgetcsv($handle)) {
        // Process this line and save to database
    }
}

这样一次只能将一行加载到内存中.然后,您可以处理它,保存到数据库,并用下一个覆盖它.

为记录保留单独的文件句柄

您的服务器内存不足,因此将错误记录到阵列可能不是一个好主意,因为所有错误都将保留在其中.如果您的csv有大量带有空skus和类别ID的条目,那么这可能会成为一个问题.

Laravel开箱即用Monolog,您可以尝试根据您的需求进行调整.但是,如果它最终也使用了太多资源或者不能满足您的需求,那么更简单的方法可能就是解决方案.

$log = fopen('log.txt', 'w');
if (some_condition) {
    fwrite($log, $text . PHP_EOL);
}

然后,在脚本的末尾,您可以将日志文件存储在任何位置.

禁用Laravel的查询日志

Laravel会将您的所有查询保存在内存中,这可能会对您的应用程序造成问题.幸运的是,您可以使用disableQueryLog方法释放一些宝贵的RAM.

DB::connection()->disableQueryLog();

如果需要,使用原始查询

如果您遵循这些提示,我认为您不太可能再次耗尽内存,但您总是可以牺牲Laravel的一些便利来提取最后一滴性能.

如果您了解SQL的方法,则可以对数据库执行原始查询.

编辑:

至于超时问题,您应该按照注释中的建议将此代码作为排队任务运行,无论如何.插入那么多行将花费一些时间(特别是如果你有很多索引)并且用户不应该长时间盯着无响应的页面.