使用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中插入3M行，在MySQL中插入480s）时，我的性能得到了更好的提高。不幸的是，MySQL是必要的，因为这个项目将来会扩大规模。在

-

编辑

使用加载数据填充就像一个魅力。感谢所有帮忙的人！插入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...");

另外，您可以尝试在文件中写入数据，并使用^{} from Mysql，该文件旨在以“非常高的速度”插入（dixit Mysql）。在

我不知道“file writing”+“MySQL Load Data”是否比在一个查询中插入多个值（如果MySQL有限制的话，可以多个查询）快

这取决于你的硬件（用SSD写文件很快）、你的文件系统配置、MySQL配置等等。因此，你必须在你的prod环境上测试，看看哪个解决方案对你来说最快。在

Answer 2

Insert的直接插入，生成一个sql文件（使用extended inserts等）然后将其取到MySQL中，这样可以节省大量的开销。在

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

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

=>

^{pr2}$