使用Python向MySQL数据库插入数百万条记录

Question

我有一个包含约1亿条记录（数字）的txt文件。我正在使用Python读取此文件并使用简单的插入语句将其插入到MySQL数据库中。但是这个过程非常耗时，而且看起来脚本永远都无法完成。如何才能以最优方式执行此过程？脚本仅使用了不到1%的内存和10至15%的CPU。

如果您有任何处理大数据并高效地将其插入到数据库的建议，我们将不胜感激。

谢谢。

- jcoder12

---

你的记录是怎样的？ - Karpov

如果必要的话，您最好将文件重新格式化为CSV格式，然后从MySQL命令行批量加载它，而不是编写Python脚本。请参见使用LOAD DATA INFILE从CSV导入数据的MYSQL方式。 - Warren Dew

Answer 1

将行插入表中最快的方式是使用LOAD DATA INFILE语句。逐个执行INSERT语句插入一行，即RBAR（难以忍受的逐行插入），非常缓慢，因为数据库必须进行所有工作来执行语句...解析语法和语义，准备执行计划，获取和释放锁，写入二进制日志等等。如果您必须执行INSERT语句，则可以利用MySQL多行插入，这将更快速。参考： https://dev.mysql.com/doc/refman/5.6/en/load-data.html。

  INSERT INTO mytable (fee, fi, fo, fum) VALUES 
   (1,2,3,'shoe')
  ,(4,5,6,'sock')
  ,(7,8,9,'boot') 

如果您一次插入四行，那么需要执行的语句数量就会减少75%。

- spencer7593

---

感谢大家的建议。LOAD DATA INFILE 对我很有帮助。 - jcoder12

Answer 2

坚持使用 Python，你可以尝试从输入创建一个元组列表，并使用 Python MySQL 连接器中的“执行多个”语句。

如果文件太大，你可以使用生成器将其分成更易处理的块。

http://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-executemany.html

- emican

---

executemany 带有 INSERT 语句会调用多行语法... 单个插入语句插入多行。这比为每行执行单独的插入语句要快。由于 SQL 语句的大小有限制... 如果要插入 1 亿行，如果每次插入 100 行，那么仍然需要执行一百万个语句。 - spencer7593

Answer 3

最近我尝试了这个，发现了一种快速的方法，但这可能是因为我使用的是AWS Windows服务器来运行Python，并且该服务器与数据库连接速度很快。然而，它不是一个包含100万行的文件，而是多个文件，加起来有100万行。无论如何，这比我测试过的其他直接DB方法都要快。

采用这种方法，我能够顺序读取文件，然后运行MySQL Infile命令。我还在这个过程中使用了线程。经过计时，将1百万行导入MySQL只需要20秒。

免责声明：我对Python还很陌生，所以我试图看看我能推动这个过程到什么程度，但这会导致我的DEV AWS-RDS数据库变得无响应（我不得不重新启动它），所以采取不超负荷的方法可能是最好的！

- Coffee and Code