很长时间之前我写过一篇《使用 Python 将海量 MySQL 数据导入 Elastic Search/MongoDB》，主要就说了如何把大量 MySQL 数据写入键值对数据库。

现在回过头看来这篇文章还是很有参考价值的。

有的时候我们会遇到相反的需求，比如说把对应的键值对数据库（或者说是 json 文件）导入（列数据类型相符）的 MySQL 表中。通常来说这不是个啥大问题，根据 json 提取出列，生成 SQL 语句就可以了。也就这么几行，然后 execute 加参数就可以了：

placeholders = ', '.join(['%s'] * len(raw_dict))
columns = ', '.join(raw_dict.keys())
insert_sql = "INSERT INTO %s ( %s ) VALUES ( %s )" % (insert_table, columns, placeholders)

当然了你要是想直接生成能用的 sql 语句那我也没意见（并抛出一个? 的表情）

大量插入数据的时候咋整呢？

首先我们……

首先我们要知道，性能是由多方面因素决定的，比如说代码效率质量、Python 性能、服务器性能（包含 MySQL 版本，编译优化，宿主机 IO/CPU/RAM 等）、表结构的设计、网络速度等，咱要找到短板才可以。为了找到短板，需要使用控制变量法，但是有些变量不可控，那就很不好办了……

然后我需要送上 MySQL 官方文档两篇，说的非常详细了，我简单总结下

Optimizing INSERT Statements: https://dev.mysql.com/doc/refman/8.0/en/insert-optimization.html

Bulk Data Loading for InnoDB Tables: https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-bulk-data-loading.html

优化 INSERT 语句

插入时间由以下因素决定，数字代表权重

• Connecting: (3)
• Sending query to server: (2)
• Parsing query: (2)
• Inserting row: (1 × size of row)
• Inserting indexes: (1 × number of indexes)
• Closing: (1)

可以使用以下方式加速插入：

• 如果从同一个客户端同时插入，使用多值的 INSERT 语句一次性插入多行，这比使用单行的 INSERT 语句要快得多。如果要将数据添加到非空表中，则可以调整该 bulk_insert_buffer_size 变量以使数据插入速度更快。
• 从文本文件加载表格时，请使用 LOAD DATA INFILE。这通常比使用 INSERT 语句快 20 倍
• 利用列具有默认值的事实。只有当要插入的值不同于默认值时才显式插入值。这减少了 MySQL 必须执行的解析并提高了插入速度。

InnoDB 表的批量数据加载

关闭自动提交

关闭自动提交，因为它为每个操作执行日志刷新。

实际上默认情况下 pymysql 帮我们做了，set autocommit=0

关闭唯一性校验

set unique checks = 0

对于大表而言这会节约很多磁盘 IO，不过要确保数据没有重复记录。

关闭外键校验

SET foreign_key_checks=0;

多值 INSERT

使用多值 INSERT 来减少客户端与服务器之间的通信开销

INSERT INTO yourtable VALUES (1,2), (5,5), ...;

需要注意的是，MyISAM 用这招也管用。

这一点是我们本文讨论的重点内容。

自增列的处理

当对具有自动增量列的表进行批量插入时，请将其设置 innodb_autoinc_lock_mode为 2 而不是默认值 1

主键排序

InnoDB 表是按照主键顺序排列的，所以将数据顺序按照主键顺序排列好，可以提高导入效率

Python 性能

这可能是 PHP 被黑的最惨的一次。

如果单纯的比较执行性能的话，Python 是肯定比不过静态语言的。不过我早就过了单纯的年龄，我很邪恶的。由于这是 Python 自身的短板，咱也不太好做点什么有实质性的工作，也许仅限于把解释器换成 PyPy，编译 Python 时带上enable-optimizations，把性能关键的模块用 C/C++ 写一遍然后胶水糊上。

但可是啊可但是啊，代码执行时绝大部分的时间都消耗在了网络 IO、磁盘 IO，在语言本身上的劣势并不是那么明显。当然了非得说语言本身，那动态语言运行时要解释，要运行时检查……

代码效率质量

实际上老实说，这部分才是最关键的。一个足够 Pythonic 的代码看起来是很舒服的嗯……

查询

查询时 SQL 注入的预防

在有些情况下（比如说登录的时候）我们需要进行 where 限制条件查询，此时如果使用拼接 SQL 语句的方式，那么被注入几乎就是肯定的了。

比如某些新手在登录的查询语句可能会构造如下语句：

SELECT * FROM account WHERE username='benny' AND password='123456'

其中 benny 和 123456 是由用户输入的变量，通过判断结果集是否为空来判断是否登录成功。乍一看似乎没啥问题，但是…… 问题太多了。

1. 无验证登录

恶意用户只需要把用户名输入为' or 1=1; -- ，SQL 语句就会变成这样：

SELECT * FROM account WHERE username='' or 1=1; -- ' AND password='123456'

这是一条带不带 WHERE 都一样的语句，只要表中有数据那么结果集就一定为真，自然也就绕过登录了。

1. 各种注入

通过构造各种各样的奇葩的 SQL 语句，可以摸索出来表的结构，甚至是某些关键库（比如 mysql 这个库）的数据，比如说这篇。

1. 密码存储问题

即使注入不是问题，从系统设计的角度来讲这种设计也是错误的。

明文密码，无论从传输角度还是从数据库角度来讲都是不合理的。即使是使用安全的散列函数、HMAC 也是错误的。

正确的姿势是用参数化查询查询出密码，使用 PBKDF 的方法进行等时间比较。

（如何存储用户密码是一门艺术，以后也许单独开一篇完整介绍吧）

dictCursor

有些时候我们可能需要查询出来的结果是一定类型的，默认情况下 pymysq 使用的 cursor 会查询出来[(),()]这种格式的数据，如果我们需要字典样式的数据，那么就要在构造 con 连接对象的时候指定游标，语法大致如下：

con = pymysql.connect(host='localhost', user='user', password='passwd',
db='db', charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor)

这样就不用再从 information_schema 里乱查一通了。

fetch 方式

cur 在获取数据时，有fetchall()、fetchone()、fetchmany()三种方法。细节已经在这里说了，所以就暂且不提。

插入

插入时 SQL 注入的预防

插入数据时也同样需要防范 SQL 注入，所以应该使用参数化查询。下面会详细说明

cur.execute() 与参数

首先看一眼原型：def execute(self, query, args=None)

第一个参数 query 代表要执行的 SQL 语句，第二个 args 代表可选的查询参数，类型可为 tuple, list or dict

也就是说，我们应该这样执行 execute：

1. cur.execute('select version()') 不需要任何参数，直接一整条 SQL 语句。
2. cur.execute('INSERT INTO sometable VALUES (%s,%s)', (1, 4)) 参数化查询，%s 是占位符，不需要考虑字段类型。无脑 %s 没事的，这不是 Python 的字符串 % 格式化风格语法。

根据 PEP 249，clientlibrary.paramstyle 可以看到占位符风格，比如说自带的 sqlite3 的占位符就是?，pymysql 是 pyformat，更多详细信息可以看看这里

比较不推荐的执行查询的方式

sql = 'insert into sometable values('
sql = sql + "'" + var1 + "','" + var2 + "','" + var3 + "'," + str(1984) + ")"

以及任何花样循环拼接的方式，看着那些单引号双引号我都头疼，就算 USE somedb 这种没办法搞参数化查询，那咱用 %s、format、f-string 拼字符串可读性也比这种 + 的好。况且 + 要开辟新的内存空间，会更慢了。

提示：mysql connector 的 cursor 支持 multiLine 模式，也就是说可以通过分号执行多条 SQL 语句。不过，pymysql 不支持哟。

cur.executemany() 参数

以为会 execute 参数化查询就够了吗？才不是呢，远远还不够。有些时候我们需要大量插入（或是 replace）。按照一般的脑回路：

sql = "INSERT INTO sometable VALUES (%s,%s,%s)"
for item in data:
    cur.execute(sql, item)

还是先来看下 executemany 的原型：executemany(self, query, args)

第一个参数 query 还是带有占位符的 SQL 语句，第二个参数 args 一般来说是双层嵌套的参数。例子如下：

sql = 'INSERT INTO SOMETABLE VALUES (%s,%s,%s)'
data = [[1, 2, 3], [4, 5, 6], [7, 8, 9]]
cur.executemany(sql, data)

核心关键就在这里，executemany 会通过多值的 INSERT 提升性能，其他情况下就和循环调用 execute 一样了

有些小盆友可能想问，为啥 executemany 能够提高性能呢？因为 executemany 生成的是类似INSERT INTO sometable VALUES(1,2,3),(4,5,6)这种语句啊，肯定要比一条一条 INSERT 要快很多了。不过为什么说 “肯定” 呢？想当然是很正常的，有理有据的想当然才是可以的（...( ＿ ＿)ノ｜继续看就知道为什么了）。

注意，由于单条 SQL 语句的长度有限制，这里可能需要看情况分割一下：

for i in range(0, len(data), SIZE):
    part = data[i:i + SIZE]
    # do something here, such as executemany

相信我，只要插入数据量足够大，executemany 就能够成为性能提升的最关键的一点。

哦对了，executemany 配合参数化查询已经很好用了，还是别多值 INSERT，虽说也能提高效率……

以下测试有一点需要注意，不同的表结构、字段类型所得到的二者比值未必相同，但总体情况毫无疑问是 executemany 胜出。

下面是我的测试，测试脚本可以从如下地址获取：
开源地址

看一下这个折线图更直观的感受下吧，平均来说 executemany 要比 execute 快上至少 10 倍。

其他不太好玩方式

关于事务与 commit

不要尝试花样 commit，比如说一条一条插，每插入 1000 条 commit 一次；插一条 commit 一次。这比画蛇添足还过分，这就是自讨苦吃啊。一起 commit 就得了。（如果数据可能会插入失败，那么一定要用 try...except...finally 控制好异常确保无误）

并发 INSERT

在使用 InnoDB 情况下，由于 InnoDB 有事务，插入时有排他锁，表的结构可能不同，可能有主键外键唯一约束，可能有自增非空限制，数据库版本 / 配置可能不同，一些库的限制，GIL 锁等等，这个变量就有点太大了有点难控制…… 这个我真的没办法拿数据说话。

其他小的代码因素

除了 execute/executemany 这个最大的性能因素之外，还有一些在某些情况下 Python 层面的对性能提升未必很明显的处理方式:

1. 拼接字符串：尽量选择 f-string、%、format 或 join，+ 是最慢的，f-string 是最快的。
2. Python 2 需要特别注意一些方面，比如 range 和 xrange，True 和 1 等

其他因素

服务器性能

包含 MySQL 版本，编译优化，宿主机 IO/CPU/RAM 等。这一点就很难展开说了，说不定某个版本的 MySQL 在某个平台上通过某个编译优化参数性能就提升了 20% 呢。哦对了，Python 开启--enable-optimizations时据说性能会提升 10%-20% 呢。

网络速度

如果是远程连接到数据库的话，那么减少连接次数、减少数据量是更加的选择，此时就更应该选择批量的 executemany 而不是 execute 了。因为多次 execute 会嗷嗷的增加上传和下载的流量，而 executemany 不会增加下载的流量，就连上传流量的增加也不那么可怕哟。

还好都是一个 TCP 连接里走的，要不就更惨不忍睹了。

不信我抓包给你看~

单个 execute

一共这么几个请求，一个 autocommit=0，一个 commit，三个查询。

协议统计告诉我们，我发送了 682 字节，接受了 628 字节。

下面我们来看下 executemany

executemany

从图中我们可以看到，一个 autocommit=0，一个 commit，外加一次查询。

协议统计告诉我们，发送了 514 字节，接收了 537 字节。

假如我把数据量从 3 改成 1000，那么……

单次 execute 发送 94K 接受 65K

Executemany 发送 10K 接受 542 字节

网速影响总结

综上，在数量级为 1000 的情况下，execute 总流量是 159K，executemany 是 10.53K，差了接近 15 倍。那么在低网速、大数据的情况下谁的优势更明显就不言而喻了。

那么问题来了，假如单独 executemany 的时候差了十倍，流量又差了十倍，那是不是一共就差了 100 倍了？

(o=^• ェ •)o　┏━┓（你看桌子都翻了，所以呢）不可以这么计算的，大部分情况下网速对性能的影响并不是那么明显，当网速足够慢、数据足够大时，这个影响就会被放大；反之这个影响可以忽略不计。所以正确计算是要加权重的，然而这个权重是几乎无法得到的ε=ε=ε=┏(゜ロ゜;)┛

所以，如果在远程连接数据库的情况下执行查询，那么数据量大的时候，因为单次 execute 导致了网络传输量的急剧上升，execute 的负面效应会被一定程度的放大。

最终的总结

1. 官方文档是第二好的参考来源，那么第一好的参考来源是什么？源代码啦；
2. 当遇到问题的时候，想当然是很正常的，但是更重要的是通过分析实验与数据了解并证实猜想；
3. 某些因素可能会互相影响。像本例 execute 的副作用之一就是会增加网络流量，所以 execute 才是根源，网速只是伴随的副作用而已，不可因果倒置；
4. 某些性能因素是很难控制的，比如服务器 CPU/RAM/IO，Python 解释器性能，MySQL 版本 / 编译选项 / 配置 / 集群，甚至是操作系统、内核的差距。这种因素的变量几乎很难控制，那么就不要考虑了，等某因素成为关键点的时候再去分析；
5. 我有代码洁癖；
6. 这可能是一个火苗