用Python加快将大数据集从txt文件插入MySQL的速度

Question

背景：我有500个格式化的*.txt文件，需要把它们放进一个mysql数据库里。目前我有一个Python脚本，可以逐行读取这些文件，并把内容插入到mysql数据库中。

问题：这些文件比较大（每个大约100M），我测试了一下这个脚本，发现插入一个文件到数据库里花的时间太长了。

我该如何修改脚本来加快这个过程呢？

代码：

for file in os.listdir(INPUTFILEPATH):
    ## index += 1
    ## print "processing %s out of %s files " % (index, totalfiles)
    inputfilename = INPUTFILEPATH + "/" + file 
    open_file = open(inputfilename, 'r')
    contents = open_file.readlines()
    totalLines = len(contents)
    ## index2 = 0 
    for i in range(totalLines):
        ## index2 +=1
        ## print "processing %s out of %s lines " % (index2, totalLines)
        lineString = contents[i]
        lineString = lineString.rstrip('\n')
        values = lineString.split('\t')
        if ( len(re.findall(r'[0123456789_\'\.]',values[0])) > 0 ):  
            continue 
        message = """INSERT INTO %s(word,year,count,volume)VALUES('%s','%s','%s','%s')"""% ('1gram', values[0],values[1],values[2],values[3]) 
        cursor.execute(message)
        db.commit()

cursor.close()
db.close() 

Answer 1

试试这个：

处理一下文本文件，为每一行生成插入命令。等文件处理完了，所有的文本文件都变成了.sql文件后，就把它们导入到数据库里。

Answer 2

你在内存中存储了太多的文本。你应该使用像下面这样的缓冲区来处理：

with open(inputfilename, 'r') as f:
    for lineString in f:
        ... do your thing

Answer 3

这里有两个选项可以考虑：

1) 最简单的方法是一次插入多行数据。这样做比一次插入一行要快得多。

比如，不要这样写 INSERT INTO tbl ( cols ) VALUES ( vals )，而是可以这样写 INSERT INTO tbl ( cols ) VALUES ( vals ), ( vals ), ( vals )。

你一次可以插入多少行，取决于mysql服务器的最大数据包大小，但通常你可以安全地插入100、1000，甚至可能是10000行，这样做会大大提高性能。

想了解更多，可以查看这个链接：http://dev.mysql.com/doc/refman/5.5/en/insert-speed.html

2) LOAD DATA INFILE 这个方法稍微复杂一些，需要更多的准备工作，还有一些特定的要求，但它的速度非常快。