很长时间之前我写过一篇《使用 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. 这可能是一个火苗