使用.executemany()加速MySQL插入操作

Question

我怎么能在这里使用 executemany 来加快这个过程呢？

with dest_conn.cursor() as dcur:
    while True:
        rows = scur.fetchmany(size=25)
        if rows:
            place_holders = "(%s)" % ','.join("?"*len(rows[0]))
            place_holders_list = ', '.join([place_holders] * len(rows))
            insert_query = "INSERT IGNORE INTO `%s` VALUES %s" % (tname, place_holders_list)
            dcur.execute(insert_query, (val for row in rows for val in row))

        else:
            log("No more rows found to insert")
            break

这里的 dcur 是目标游标，用来复制数据，而 scur 是源游标，我是从这里获取数据的。

即使我一次插入25行数据（我发现这个数量对我的数据库来说是最优的），我还是在创建一个准备好的语句并执行它们。oursql 的手册上说 executemany 更快，因为它可以批量发送所有的值。那么我该怎么在这里使用它，而不是用 execute 呢？

Answer 1

你可以对你的代码做一些修改。首先，你应该只创建一次 insert_query 字符串，因为在循环中它是不会改变的。另外，你的代码里似乎有一些错误，比如 '?'*nr 这个写法并不会返回一个序列，所以我也把这些问题修正了。

使用 oursql

import oursql

# ...

place_holders = '(' + ','.join(['?'] * len(scur.description)) + ')'
insert_query = "INSERT IGNORE INTO `%s` VALUES %s" % (tname, place_holders)

with dest_conn.cursor() as dcur:
    while True:
        rows = scur.fetchmany(size=25)
        if not rows:
            log("No more rows found to insert")
            break

        dcur.executemany(insert_query, rows)

不过，我发现你在 executemany() 方法上没有做太多优化。这个方法总是会使用 MySQL 的预处理语句，并且一个一个地执行每个插入操作。

使用 oursql 执行的 MySQL 通用日志条目：

..
14 Prepare  SELECT * FROM t1
14 Execute  SELECT * FROM t1
15 Prepare  INSERT INTO `t1copy` VALUES (?)
15 Execute  INSERT INTO `t1copy` VALUES (1)
15 Execute  INSERT INTO `t1copy` VALUES (2)
15 Execute  INSERT INTO `t1copy` VALUES (3)
..

使用 MySQL Connector/Python

如果你使用 MySQL Connector/Python（顺便说一下，我是这个库的维护者），你会看到发送到 MySQL 服务器的查询会有所不同。这里有一段类似的代码，但经过调整，可以与 mysql.connector 一起运行：

import mysql.connector

# ...

place_holders = ','.join(['%s'] * len(scur.description))
place_holders_list = ', '.join([place_holders] * len(scur.description))
insert_query = "INSERT INTO `{0}` VALUES ({1})".format(tname, place_holders_list)

dcur = dest_conn.cursor()
while True:
    rows = scur.fetchmany(size=25)
    if not rows:
        log("No more rows found to insert")
        break

    dcur.executemany(insert_query, rows)
    dest_conn.commit()

使用 mysql.connector 执行的 MySQL 通用日志条目：

..
18 Query    SELECT * FROM t1
19 Query    INSERT INTO `t1copy` VALUES (1),(2),(3),(4),(5),(6),(1),(2),(3),(4),(5),(6)
19 Query    COMMIT

哪个更快需要进行基准测试。oursql 使用的是 MySQL 的 C 库，而 MySQL Connector/Python 是纯 Python 的。为了实现优化插入，所用的也是纯 Python 字符串解析，所以你需要自己去检查一下。

结论

oursql 并没有优化 INSERT 语句本身。相反，executemany() 只是创建了一次 MySQL 的预处理语句。所以这一点是好的。