插入多行时提交()的最佳实践

Question

形势-Aim

我们在CSV中有100万行的数据集。需要将这些数据导入mysql 5.6数据库，该数据库的表中有innodb引擎，列如下(匹配csv文件)：

Column1: bigint(15)

Column2:日期时间

要导入这些数据，我们可以使用以下python脚本：

#python version: 3.4.3
#mysql connector version: 2.0.4
#Note: this is just a simple example script!

import mysql.connector
import csv

#Set-up db connection
cnx = mysql.connector.connect(user='user', password='secret', database='mydatabase')
cursor = cnx.cursor()

#read/ open csv
reader = csv.reader(open("C:/test.csv", "rt"))

#ignore header
next(reader)

#read CSV and send to mysql
for row in reader:
    id=row[0]
    adate = row[1]
    cursor.execute("""INSERT INTO mytable VALUES ('%s','%s')""" % (id,adate))

#commit the query
cnx.commit()

问题

当我将cnx.commit()放入for循环中时，在概念上发生了什么，如下所示：

for row in reader:
    id=row[0]
    adate = row[1]
    cursor.execute("""INSERT INTO mytable VALUES ('%s','%s')""" % (id,adate))    
    cnx.commit()

• 如果我正确理解了它，那么在每次迭代之后强制mysql编写与mysql在缓存中存储所有内容，然后发送commit()写命令是不同的。
• 这样做的好处/缺点是什么？
• 您是否遵守任何约定/最佳实践或实践?为什么？

谢谢!

Answer 1

你已经表明了两个极端的立场。

一种是只在插入之后才提交。另一种是在每一行之后提交。

这两种方法的性能都很差。首先是不好的，因为MySQL创建了一个大事务，最后必须提交整个事务。这需要大量的RAM或临时空间。第二个问题是坏的，因为它提交了许多小的小事务。

执行这种大容量插入的最佳方法是每一千行左右提交一次。但这比其他方案更难编程。在我的经验中，每一千行之后提交的速度是其他方法的几倍，因此值得增加少量的复杂性。

另一种快速(但易碎)的方法是使用LOAD DATA INFILE，您可以在这里读到：https://dev.mysql.com/doc/refman/5.6/en/load-data.html。

您的megarow提交有一个小问题: MySQL连接可以有一个自动提交模式集。在这种情况下，您的第一个示例相当于第二个示例。默认情况下，在python连接器中禁用该模式。