快速处理大数据经验总结

SSSibyl

已于 2022-08-25 10:07:36 修改

阅读量1.5k

点赞数

分类专栏：数据处理文章标签：数据库大数据数据处理

于 2022-07-14 17:35:16 首次发布

本文链接：https://blog.csdn.net/SSibyl/article/details/125788864

版权

数据处理专栏收录该内容

3 篇文章 0 订阅

订阅专栏

文章目录

加速处理大数据的思路

频繁调用数据库会耗费大量时间，所以需要减少调用数据库次数。

此外，可以借助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

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

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)