使用SQLAlchemy加快记录插入到表中的速度

Question

我正在解析一个日志文件，并使用SQLAlchemy和Python将数据插入到MySQL或SQLite数据库中。现在，我打开数据库连接，然后在遍历每一行时，将解析后的数据插入（目前只是一个大表，对SQL不太熟悉）。当循环结束后，我再关闭连接。总结一下代码就是这样：

log_table = schema.Table('log_table', metadata,
                         schema.Column('id', types.Integer, primary_key=True),
                         schema.Column('time', types.DateTime),
                         schema.Column('ip', types.String(length=15))
....
engine = create_engine(...)
metadata.bind = engine
connection = engine.connect()
....
for line in file_to_parse:
    m = line_regex.match(line)
    if m:
        fields = m.groupdict()
        pythonified = pythoninfy_log(fields) #Turn them into ints, datatimes, etc
        if use_sql:
            ins = log_table.insert(values=pythonified)
            connection.execute(ins)
            parsed += 1

我有两个问题：

• 有没有办法在这个基本框架下加快插入的速度？比如说，使用一个插入队列和一些插入线程，或者进行批量插入之类的？
• 我在使用MySQL时，大约插入120万条记录花了15分钟。而使用SQLite时，插入时间超过了一个小时。这个数据库引擎之间的时间差是否正常，还是说我做错了什么？

Answer 1

我做了以下操作来实现一些批处理：

inserts = []
insert_every = 1000
for line in file_to_parse:
    m = line_regex.match(line)
    if m:
        fields = m.groupdict()
        if use_sql: #This uses Globals, Ick :-/
            inserts.append(pythonified)
            if (parsed % insert_every) == 0:
                connection.execute(log_table.insert(), inserts)
                inserts = []
            parsed += 1
if use_sql:
    if len(inserts) > 0:
        connection.execute(log_table.insert(), inserts)

这个方法没有使用事务，但以一种非常懒惰的方式让我把插入和解析的时间从大约13秒缩短到大约2秒，使用的是mysql数据库，样本也比较小。我现在会看看在这个改动下，使用完整样本时mysql和sqlite之间的差别。

我在这里找到了这个的基本信息：这里。

结果：
引擎：非分组插入时间（分钟）：分组插入时间（分钟）
Sqlite: 61: 8
MySql: 15: 2.5

在mysql和sqlite之间我没有清空缓存，这样可能会影响源文本文件，但我认为这不会造成比较大的差别。

Answer 2

在不知道你用的数据库表引擎（是MyISAM还是InnoDB？）、表结构和索引的情况下，很难具体评论你使用的两个数据库之间的差异。

不过，当你这样使用MySQL时，你会发现把数据先写到一个临时的文本文件里，然后再用LOAD DATA INFILE语法把这些数据加载到数据库里，通常会快得多。看起来你可以在你的连接对象上调用执行方法来运行必要的SQL语句来完成这个操作。

另外，如果你坚持要一行一行地添加数据，并且每次都在重新创建表的话，你可以在程序中验证关键约束条件，并在所有行都插入后再添加这些约束，这样可以节省数据库在每次插入时检查约束的时间。

Answer 3

你可以尝试把多个插入操作放在一个事务里，因为把数据保存到硬盘上是最耗时的部分。你需要决定一下批处理的级别，但一个简单的初步尝试就是把所有操作都放在一个事务里。