登录
  • 人们都希望被别人需要 却往往事与愿违
  • 软件设计就像做爱, 一次犯错, 你要用余下的一生来维护@Michael Sinz

假如给我一百万(的数据)

编程 Benny小土豆 5642次浏览 6342字 7个评论
文章目录[显示]
这篇文章在 2019年03月01日16:39:35 更新了哦~

很长时间之前我写过一篇《使用 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胜出。

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

简单表的性能benchmark
插入条数 execute executemany
1000 0.35 0.02
5000 0.84 0.06
10000 4.16 0.34
20000 4.87 0.39
50000 19.43 0.93
100000 40.84 3.18
500000 165.72 9.76
1000000 369.46 29.43

假如给我一百万(的数据)

看一下这个折线图更直观的感受下吧,平均来说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. 这可能是一个火苗

文章版权归原作者所有丨本站默认采用CC-BY-NC-SA 4.0协议进行授权|
转载必须包含本声明,并以超链接形式注明原作者和本文原始地址:
https://dmesg.app/one-million-data.html
喜欢 (12)
分享:-)
关于作者:
If you have any further questions, feel free to contact me in English or Chinese.
发表我的评论(代码和日志请使用Pastebin或Gist)
取消评论

                     

去你妹的实名制!

  • 昵称 (必填)
  • 邮箱 (必填,不要邮件提醒可以随便写)
  • 网址 (选填)
(7)个小伙伴在吐槽
  1. https://img.hacpai.com/file/2018/11/image-3e05720d.png 刚才抽风了
    _admin2018-11-30 10:38 回复
  2. 不对,还是出了墙才能访问,是不是你日本机器不行?
    _admin2018-11-28 22:49 回复
  3. 哦哦哦,可能是百度原因没跳过来吧。
    _admin2018-11-28 22:28 回复
    • 国内某良心云、中国移动、中国电信都表示没有问题啊
      Benny小土豆2018-11-29 08:41 回复
      • 昨天我分享到群里,有人反映打不开,出墙表示可以打开,今天在公司可以打开,公司电信,住的地方是鹏博士(间歇性的打不开),上海地铁上可以打开。
        _admin2018-11-30 10:32 回复
        • 鹏博士打不开貌似的确定的了?因为我以前也是……,拿不到tls握手的响应,但是却能ping通。
          Benny小土豆2018-11-30 10:35
  4. 这个站是不是被墙了,我是新人
    _admin2018-11-28 22:23 回复