使用 Python/MySQL 插入 ~2000 万行的最佳方法

Question

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

我尝试过的事情：

• 执行许多，在字典中传入键和值集。当值数<~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 中插入 3M 行为 30 秒，在 MySQL 中插入 480 行）。不幸的是，MySQL是必要的，因为该项目将在未来扩大规模。

-

编辑

使用 LOAD DATA INFILE 就像一个魅力。感谢所有帮助过的人！插入 3.2M 行需要我 ~25 秒。

Answer 1

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

此外，您可以尝试将数据写入文件中，并使用Mysql中旨在以"非常高的速度"（dixit Mysql）插入的LOAD DATA。

我不知道"文件写入"+"MySQL加载数据"是否会比在一个查询中插入多个值（如果MySQL有限制，则插入多个查询）更快

这取决于您的硬件（使用 SSD 写入文件"快速"）、文件系统配置、MySQL 配置等。因此，您必须在"产品"环境中进行测试，以查看哪种解决方案对您来说是最快的。

Answer 2

直接

插入，生成一个sql文件（使用扩展插入等），然后将其提取到MySQL，这将为您节省相当多的开销。

注意：如果您避免在循环中重新计算常量值，您仍然可以节省一些执行时间，即：

for k in self.table:
    xxx = sqlutils.gettablename(self.sequence)
    do_something_with(xxx, k)

=>

xxx = sqlutils.gettablename(self.sequence)
for k in self.table:
    do_something_with(xxx, k)