文章目录
加速处理大数据的思路
频繁调用数据库会耗费大量时间,所以需要减少调用数据库次数。
此外,可以借助DataX
工具 将数据存入数据库。
动机
我要用python标识几千万条存放在sql server数据库中的数据,并将结果作为新的列插入到原表中。工作可以分为三个部分:
① 从数据库中取数据
② 用python给数据打标签
③ 将标签更新到数据库
如图所示:
最开始我一条一条地处理数据,并且处理完就马上存入数据库。但是这样不仅程序运行特别慢,还经常因为频繁调用数据库导致报错。
所以我就一直摸索加速程序运行的方法,最终将程序运行速度提升了100万倍!
-
之前的程序:一天最多能处理80000多条数据。
-
现在的程序:一天能处理9000万条数据。
最开始的方法
1. 概述
从数据库中一条一条取数据(①),取一条数据就处理一条(②),处理完马上将结果存入数据库(③)。如下图:
2. 遇到的问题
-
处理数据速度特别慢,平均一秒只能处理1~2条数据。一天大概只能处理8万条数据。
-
对数据库进行查询操作或更新操作时经常会报错:
pymssql.OperationalError: (20009, b'DB-Lib error message 20009, severity 9:\nUnable to connect: Adaptive Server is unavailable or does not exist ...
我几乎试了全网所有的解决方法都没办法解决这个问题,后来问了公司的前辈,前辈说可能是因为太过频繁调用数据库,占用太多资源。然后他让我用union
语句拼接查询结果,这样就能做到批量查询(下章会详细介绍)。
3. 速度慢的根本原因
经过分块测试后,我发现程序运行速度慢的根本原因是频繁调用数据库,并且更新数据库比查询更耗时。
基于此,我下面采取了几个手段来加速程序运行。
优化后的方法
1. 概述
首先我一次性将所有的数据都从数据库中取出来(①),然后多进程运行python程序处理数据(②),再将所有的结果存入csv文件中(③),然后用DataX
工具 将csv导入数据库(④),最后合并原数据Data Table和标签数据Tag Table(⑤),如图:
相较于之前的方法,现在的方法优化了以下几点:
- 优化查询和更新数据方法;
- 多进程运行python程序。
2. 具体方法(具体代码看下一章)
方法一:批量查询数据,减少调用数据库的次数
- 按批次拼接查询语句,并进行查询;
- 将每次的查询结果都存在列表里。
方法二:建立数据库索引并定时重建索引
这个方法图片太多了,所以我另外写了篇文章总结:《建立数据库索引并定时重建索引》
方法三:查询数据时指定列,不要全部查询所有列
如果我只需要每个数据的id,我只要查询id列:
select id from test_table
而不要查询所有列:
select * from test_table
方法四:多进程运行python程序
利用python的multiprocessing
模块,设置4个进程并行运行程序(一般电脑有几个cpu内核就设置几个进程,当然也可以多设置几个,但是电脑会很卡)。
方法五:用DataX工具 将结果存入数据库
-
将生成的tag存入csv文件
-
然后用
DataX
工具将csv文件存入数据库 -
最后再合并原表和tag表
推荐方法/工具
一、multiprocessing:多进程运行python程序
-
python代码:
from multiprocessing import Pool def fun1(n): for i in range(10): print(i+n) if __name__ == '__main__': process_number = 4 # 设置进程数 p = Pool(process_number) # 下面分了process_number个进程 p.apply_async(fun1(1)) # 将进程加入进程池 p.apply_async(fun1(10)) p.apply_async(fun1(100)) p.apply_async(fun1(1000)) p.close() p.join()
注意:
- 要将多进程代码写在
if __name__ == '__main__':
函数里; - 进程数可以超过cpu核数,但最好将进程数设置小于或等于cpu核数,不然电脑会很卡。
- 要将多进程代码写在
-
推荐教程:Python开启多个进程
二、批量查询、批量更新:减少调用数据库的次数
-
批量查询
-
假设test_table内容为:
id data 1111 data1 2222 data2 3333 data3 4444 data4 -
批量查询语句(t sql):
select * from test_table where id in (1111, 2222, 3333, 4444) -- 查询id为1111, 2222, 3333, 4444的所有数据 order by CHARINDEX(ltrim(id), '1111, 2222, 3333, 4444') -- 并将结果按照1111, 2222, 3333, 4444进行排序
-
自动拼接批量查询语句(python):最大查询数量不要超过20000,因为超过20000,会因为sql语句太长而报错
import math import tqdm from utils.util_function import execute_query # 执行查询语句 def get_all_datas(process_name, DBname, data_ids): """ :param process_name:进程数 :param DBname: 数据库名字 :param data_ids: 数据id:[[data_id, index], ...] :return: 以字典形式返回 """ data_length = len(data_ids) max_length = 20000 result_dict = {} m = math.ceil(data_length / max_length) # int()是向上取整 pre_index = next_index = 0 for i in range(m): """ 拼接查询语句 """ sql_string = "select * from {} with(nolock) where id in (".format(DBname) pre_index = next_index # 更新index if i < m-1: next_index = pre_index + max_length else: next_index = data_length # 获取这一批的数据 smaller_list = data_ids[pre_index:next_index] length = next_index - pre_index for j in tqdm(range(length), desc=process_name + ': Splicing ' + str(i+1) + 'st ' + 'string for datas'): if j != 0: sql_string += ", " data_id = smaller_list[j][0] sql_string += str(data_id) sql_string += ") order by CHARINDEX(ltrim(id), '" for j in tqdm(range(length), desc=process_name + ': Splicing ' + str(i+1) + 'st ' + 'string for datas'): if j != 0: sql_string += ", " data_id = smaller_list[j][0] sql_string += str(data_id) sql_string += "')" """ 从数据库读取数据 """ datas = execute_query(sql_string) # 执行查询语句 """ 将数据按{data_id : data}分组存入字典 """ for j in tqdm(range(length), desc=process_name + ': Storing in list to ' + str(i+1) + 'st list for datas.'): data_id = smaller_list[j][0] value = datas[j] result_dict[data_id] = value return result_dict
-
-
批量更新
如果数据量较少可以用批量更新的方法,否则就用上述方法四,不然还是会报错
-
假设更新前的test_table内容为:
id data tag 1111 data1 0 2222 data2 0 3333 data3 0 4444 data4 0 -
批量更新语句(t sql):
UPDATE test_table SET tag = CASE id when 1111 then -1, when 2222 then 1, when 3333 then -1, when 4444 then 1 END WHERE id in (1111, 2222, 3333, 4444)
-
自动拼接批量更新语句(python):最大查询数量不要超过500,因为超过500,会因为sql语句太长而报错
import math import tqdm from utils.util_function import execute_non_query # 执行非查询语句 def batch_tag_datas(tag_list, DBname, column_name): """ 批量更新tag列 :param tag_list: 标签列表:[[data_id, tag], ...] :param DBname: 数据库名称 :param column_name: 列名 :return: """ max_length = 500 data_num = len(tag_list) pre_index = next_index = 0 m = math.ceil(data_num / max_length) # int()是向上取整 print("--------- Start update tag ---------") for i in tqdm(range(m), desc='Update tag in batch: '): # 分批处理 """ 拼接更新语句 """ pre_index = next_index if i < m - 1: next_index = pre_index + max_length else: next_index = data_num small_list = tag_list[pre_index:next_index] length = next_index - pre_index sql_string = "UPDATE {} SET {} = CASE id ".format(DBname, column_name) updated_ids = "(" for j in range(length): data_id = small_list[j][0] tag = small_list[j][1] temp_string = "when {} THEN {} ".format(data_id, tag) if j < length - 1: sql_string += temp_string updated_ids += (str(data_id) + ", ") else: temp_string += "END WHERE id in " sql_string += temp_string updated_ids += (str(data_id) + ")") sql_string += updated_ids """ 更新数据 """ execute_non_query(sql_string)
-
更新后结果:
id data tag 1111 data1 -1 2222 data2 1 3333 data3 -1 4444 data4 1
-
三、DataX:传输大量数据
- Github:
- 推荐教程:
个人总结
之前我嫌麻烦,想只用python就解决所有问题,并且很抵触去改代码,导致项目进度停滞很久。但其实很多东西用sql
或其他工具更快。
后来我沉下心大改了代码,并且放弃用python处理一些东西,而是去学了别的工具怎么用,发现用别的工具比用python快多了。
还有,沟通真的很重要。之前疫情被封在家里3个月,我几乎没怎么和别人交流,就只是自己写代码,导致进度很慢。
后来解封后,我积极地向上司和技术部的同事寻求帮助,然后我的项目进展突飞猛进,并且我也学到很多新东西。
比如说之前我想将有1000万行数据的csv文件导入数据库,我之前使用python一条一条写入数据库,大概要花半年才能写完,但是技术部的老师10分钟就帮我导入了。