将数千条记录高效插入表中（MySQL, Python, Django）最好的方法是什么？

Question

我有一个数据库表，这个表里有一个唯一的字符串字段和几个整数字段。这个字符串字段的长度通常在10到100个字符之间。

大约每分钟，我会遇到这样的情况：我收到一个包含2000到10000个元组的列表，这些元组对应于表的记录结构，比如：

[("hello", 3, 4), ("cat", 5, 3), ...]

我需要把这些元组全部插入到表里（假设我已经确认这些字符串在数据库中不存在）。为了说明，我使用的是InnoDB，并且这个表有一个自增的主键，而这个字符串字段不是主键。

我现在的代码是遍历这个列表，为每个元组创建一个Python模块对象，并调用“.save()”方法，像这样：

@transaction.commit_on_success
def save_data_elements(input_list):
    for (s, i1, i2) in input_list:
        entry = DataElement(string=s, number1=i1, number2=i2)
        entry.save()

这段代码目前是我系统中的一个性能瓶颈，所以我在寻找优化的方法。

比如，我可以生成SQL代码，每条SQL包含100个元组的INSERT命令（直接写在SQL里），然后执行它，但我不知道这样做是否会有改善。

你有什么建议可以优化这个过程吗？

谢谢

Answer 1

如果你不使用 LOAD DATA INFILE，就像其他一些建议提到的那样，有两件事可以帮助你加快插入数据的速度：

1. 使用预处理语句 - 这样可以省去每次插入时解析SQL语句的时间。
2. 把所有的插入操作放在一个事务中进行 - 这需要使用支持事务的数据库引擎（比如InnoDB）。

Answer 2

对于MySQL来说，加载数据最快的方法是使用 LOAD DATA INFILE。所以如果你能把数据转换成它所需要的格式，这可能是把数据最快放进表里的方法。

Answer 3

你可以把每一行数据写入一个文件，格式是"字段1", "字段2", .. 然后使用LOAD DATA命令把这些数据加载进来。

data = '\n'.join(','.join('"%s"' % field for field in row) for row in data)
f= open('data.txt', 'w')
f.write(data)
f.close()

接着执行这个命令：

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

参考链接