为什么Python MySQLdb中的executemany很慢？

Question

我正在用Python开发一个程序，这个程序需要通过MySQLdb访问一个MySQL数据库。在某些情况下，我需要对很多行数据执行INSERT或REPLACE命令。现在我这样做：

db.execute("REPLACE INTO " + table + " (" + ",".join(cols) + ") VALUES" +
    ",".join(["(" + ",".join(["%s"] * len(cols)) + ")"] * len(data)),
    [row[col] for row in data for col in cols])

这样做是可以的，但感觉有点麻烦。我在想有没有办法让代码更容易阅读，于是我发现了executemany这个命令。我把代码改成了这样：

db.executemany("REPLACE INTO " + table + " (" + ",".join(cols) + ") " + 
    "VALUES(" + ",".join(["%s"] * len(cols)) + ")",
    [tuple(row[col] for col in cols) for row in data])

虽然这样也能运行，但速度慢了很多。在我的测试中，对于相对较小的数据集（大约100-200行），速度大约慢了6倍。而对于更大的数据集（大约13,000行，这是我预计要处理的最大数据量），速度慢了大约50倍。为什么会这样呢？

我真的想简化我的代码，但又不想牺牲性能。有没有人知道有什么办法可以让它更快？

我使用的是Python 2.7和MySQLdb 1.2.3。我尝试调整setinputsizes函数，但似乎没有任何效果。我查看了MySQLdb的源代码，感觉这个函数应该不会有影响。

Answer 1

强烈不推荐在 pyodbc 和 ceodbc 中使用 executeMany，因为它们速度慢，而且有很多bug。

建议使用 execute，并手动构建 SQL 查询，使用简单的字符串格式。

transaction = "TRANSACTION BEGIN {0} COMMIT TRANSACTION

bulkRequest = ""
for i in range(0, 100)
    bulkRequest = bulkRequest + "INSERT INTO ...... {0} {1} {2}"

ceodbc.execute(transaction.format(bulkRequest))

目前的实现方式非常简单、快速且可靠。

Answer 2

你第一个例子是一个比较复杂的语句，这个语句会被生成后发送到数据库。

而第二个例子则是一个简单得多的语句，它用来插入或替换一行数据，但这个操作会执行很多次。每个命令都是单独发送到数据库的，所以每插入一行数据，就要花时间从客户端到服务器再返回。这种在命令之间增加的等待时间，可能就是第二个例子性能下降的主要原因。

Answer 3

试着把你查询中的“values”这个词改成小写字母——这似乎是MySQL-python 1.2.3中的一个bug。

MySQL-python在执行批量插入时（也就是executemany()这个功能）是通过正则表达式来匹配VALUES这个部分的，然后它会把每一行的数据都复制一遍，所以你最终执行的查询和你最开始的方法是完全一样的。

不幸的是，在这个版本中，正则表达式的大小写不敏感的标志丢失了（后来在主干版本中修复了，具体可以查看r622，但这个修复没有被移植到1.2这个版本）所以它就退化成了对数据逐行处理，每一行都发一次查询。