mysql - 插入多行时 commit() 的最佳实践

Question

[情境瞄准]
我们有一个 100 万行的 CSV 数据集。这些需要导入到 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 连接器中禁用。