将批量数据导入MySQL [英] import bulk data into MySQL

问题描述

因此，我试图将一些销售数据导入我的MySQL数据库.数据最初是原始CSV文件的形式，我的PHP应用程序需要先对其进行处理，然后将处理后的销售数据保存到数据库中.

So I'm trying to import some sales data into my MySQL database. The data is originally in the form of a raw CSV file, which my PHP application needs to first process, then save the processed sales data to the database.

最初，我是在进行单独的INSERT查询，但我意识到这效率非常低(约6000个查询耗时 2分钟).然后，我生成了一个大型查询，并一次INSERT全部处理了数据.这使我们的效率提高了3400％，并将查询时间缩短到 3秒.

Initially I was doing individual INSERT queries, which I realized was incredibly inefficient (~6000 queries taking almost 2 minutes). I then generated a single large query and INSERTed the data all at once. That gave us a 3400% increase in efficiency, and reduced the query time to just over 3 seconds.

但是据我了解，LOAD DATA INFILE应该比任何类型的INSERT查询都要快.因此，现在我正在考虑将处理后的数据写入文本文件，并使用LOAD DATA INFILE将其导入数据库.这是向数据库中插入大量数据的最佳方法吗?还是我完全以错误的方式来做这件事?

But as I understand it, LOAD DATA INFILE is supposed to be even quicker than any sort of INSERT query. So now I'm thinking about writing the processed data to a text file and using LOAD DATA INFILE to import it into the database. Is this the optimal way to insert large amounts of data to a database? Or am I going about this entirely the wrong way?

我知道几千行主要是数字数据在宏伟的设计方案中并不是很多，但是我试图使此Intranet应用程序尽可能地快速/响应.而且，我还想确保在我们决定将程序许可给其他公司的情况下扩大此过程.

I know a few thousand rows of mostly numeric data isn't a lot in the grand scheme of things, but I'm trying to make this intranet application as quick/responsive as possible. And I also want to make sure that this process scales up in case we decide to license the program to other companies.

更新:

因此，我确实按照建议进行了测试，并认为LOAD DATA INFILE可能只会给我边际速度的提高(因为我现在两次将相同的数据写入磁盘)，但是当它减少了查询时间时，我感到很惊讶从超过3300毫秒降低到约240毫秒.该页面仍然需要大约1500ms的时间来执行，但仍然明显比以前好.

So I did go ahead and test LOAD DATA INFILE out as suggested, thinking it might give me only marginal speed increases (since I was now writing the same data to disk twice), but I was surprised when it cut the query time from over 3300ms down to ~240ms. The page still takes about ~1500ms to execute total, but it's still noticeably better than before.

我想从这里开始检查数据库中是否有多余的索引，并且由于除了两个表外，所有其他表都是InnoDB，因此我将研究优化InnoDB缓冲池以优化整体性能.

From here I guess I'll check to see if I have any superfluous indexes in the database, and, since all but two of my tables are InnoDB, I will look into optimizing the InnoDB buffer pool to optimize the overall performance.

推荐答案

LOAD DATA INFILE非常快，并且是将文本文件导入MySQL的正确方法.根据以下方法，这是建议的一种加快数据插入速度的方法-快20倍:

LOAD DATA INFILE is very fast, and is the right way to import text files into MySQL. It is one of the recommended methods for speeding up the insertion of data -up to 20 times faster, according to this:

https://dev.mysql.com/doc /refman/8.0/en/insert-optimization.html

假设将处理后的数据写回到文本文件比将其插入数据库要快，那么这是个好方法.

Assuming that writing the processed data back to a text file is faster than inserting it into the database, then this is a good way to go.

这篇关于将批量数据导入MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！