python - 使用 Python/MySQL 插入约 2000 万行的最佳方法

Question

我需要将包含 ~20M 对象的 defaultdict 对象存储到数据库中。字典将一个字符串映射到一个字符串，所以表有两列，没有主键，因为是后面构造的。

我尝试过的事情:

• executemany，传入字典中的一组键和值。当值的数量 < ~1M 时效果很好。
• 执行单个语句。有效，但速度较慢。

• 使用交易

  con = sqlutils.getconnection()
  cur = con.cursor()
  print len(self.table)
  
  cur.execute("SET FOREIGN_KEY_CHECKS = 0;")
  cur.execute("SET UNIQUE_CHECKS = 0;")
  cur.execute("SET AUTOCOMMIT = 0;")
  i = 0
  for k in self.table:
      cur.execute("INSERT INTO " + sqlutils.gettablename(self.sequence) + " (key, matches) values (%s, %s);", (k, str(self.hashtable[k])))
      i += 1
      if i % 10000 == 0:
          print i
  #cur.executemany("INSERT INTO " + sqlutils.gettablename(self.sequence) + " (key, matches) values (%s, %s)", [(k, str(self.table[k])) for k in self.table])
  
  cur.execute("SET UNIQUE_CHECKS = 1;")
  cur.execute("SET FOREIGN_KEY_CHECKS = 1;")
  cur.execute("COMMIT")
  con.commit()
  cur.close()
  con.close()
  
  print "Finished", self.sequence, "in %.3f sec" % (time.time() - t)
  

这是最近从 SQLite 到 MySQL 的转换。奇怪的是，当我使用 SQLite 时，我获得了更好的性能(在 SQLite 中插入 300 万行需要 30 秒，在 MySQL 中需要 480 秒)。不幸的是，MySQL 是必需的，因为该项目将在未来扩展。

-

编辑

使用 LOAD DATA INFILE 就像一个魅力。感谢所有帮助过的人!插入 320 万行需要大约 25 秒。

Answer 1

MySQL 可以通过一个查询插入多个值:INSERT INTO table (key1, key2) VALUES ("value_key1", "value_key2"), ("another_value_key1", "another_value_key2"), ("and_again", "and_again...");

此外，您可以尝试将数据写入文件并使用 LOAD DATA from Mysql旨在以“非常高的速度”插入(dixit Mysql)。

我不知道“文件写入”+“MySQL 加载数据”是否会比在一个查询中插入多个值(或多个查询，如果 MySQL 有限制的话)更快

这取决于您的硬件(使用 SSD 写入文件“快速”)、文件系统配置、MySQL 配置等。因此，您必须在“prod”环境上进行测试以查看解决方案是什么对你来说最快。