MySQL单表千万级数据处理的思路分享
更新时间:2021年6月6日 00:01 点击:1627
项目背景
在处理过程中,今天上午需要更新A字段,下午爬虫组完成了规格书或图片的爬取又需要更新图片和规格书字段,由于单表千万级深度翻页会导致处理速度越来越慢。
select a,b,c from db.tb limit 10000 offset 9000000
但是时间是有限的,是否有更好的方法去解决这种问题呢?
改进思路
是否有可以不需要深度翻页也可以进行数据更新的凭据?
是的,利用自增id列
观察数据特征
此单表有自增id列且为主键,根据索引列查询数据和更新数据是最理想的途径。
select a,b, c from db.tb where id=9999999; update db.tb set a=x where id=9999999;
多进程处理
每个进程处理一定id范围内的数据,这样既避免的深度翻页又可以同时多进程处理数据。
提高数据查询速度的同时也提高了数据处理速度。
下面是我编写的任务分配函数,供参考:
def mission_handler(all_missions, worker_mission_size): """ 根据总任务数和每个worker的任务数计算出任务列表, 任务列表元素为(任务开始id, 任务结束id)。 例: 总任务数100个,每个worker的任务数40, 那么任务列表为:[(1, 40), (41, 80), (81, 100)] :param all_missions: 总任务数 :param worker_mission_size: 每个worker的最大任务数 :return: [(start_id, end_id), (start_id, end_id), ...] """ worker_mission_ids = [] current_id = 0 while current_id <= all_missions: start_id = all_missions if current_id + 1 >= all_missions else current_id + 1 end_id = all_missions if current_id + worker_mission_size >= all_missions else current_id + worker_mission_size if start_id == end_id: if worker_mission_ids[-1][1] == start_id: break worker_mission_ids.append((start_id, end_id)) current_id += worker_mission_size return worker_mission_ids
假设单表id最大值为100, 然后我们希望每个进程处理20个id,那么任务列表将为:
>>> mission_handler(100, 40) [(1, 40), (41, 80), (81, 100)]
那么,
进程1将只需要处理id between 1 to 40的数据;
进程2将只需要处理id between 41 to 80的数据;
进程3将只需要处理id between 81 to 100的数据。
from concurrent.futures import ProcessPoolExecutor def main(): # 自增id最大值 max_id = 30000000 # 单worker处理数据量 worker_mission_size = 1000000 # 使用多进程进行处理 missions = mission_handler(max_id, worker_mission_size) workers = [] executor = ProcessPoolExecutor() for idx, mission in enumerate(missions): start_id, end_id = mission workers.append(executor.submit(data_handler, start_id, end_id, idx)) def data_handler(start_id, end_id, worker_id): pass
思路总结
- 避免深度翻页进而使用自增id进行查询数据和数据
- 使用多进程处理数据
数据处理技巧
记录处理成功与处理失败的数据id,以便后续跟进处理
# 用另外一张表记录处理状态 insert into db.tb_handle_status(row_id, success) values (999, 0);
循环体内进行异常捕获,避免程序异常退出
def data_handler(start_id, end_id, worker_id): # 数据连接 conn, cursor = mysql() current_id = start_id try: while current_id <= end_id: try: # TODO 数据处理代码 pass except Exception as e: # TODO 记录处理结果 # 数据移动到下一条 current_id += 1 continue else: # 无异常,继续处理下一条数据 current_id += 1 except Exception as e: return 'worker_id({}): result({})'.format(worker_id, False) finally: # 数据库资源释放 cursor.close() conn.close() return 'worker_id({}): result({})'.format(worker_id, True)
更新数据库数据尽量使用批量提交
sql = """update db.tb set a=%s, b=%s where id=%s""" values = [ ('a_value', 'b_value', 9999), ('a_value', 'b_value', 9998), ... ] # 批量提交,减少网络io以及锁获取频率 cursor.executemany(sql, values)
以上就是MySQL单表千万级数据处理的思路分享的详细内容,更多关于MySQL单表千万级数据处理的资料请关注猪先飞其它相关文章!
上一篇: MySQL索引失效的典型案例
下一篇: MySQL 时间类型的选择
相关文章
- 这篇文章主要介绍了MySQL性能监控软件Nagios的安装及配置教程,这里以CentOS操作系统为环境进行演示,需要的朋友可以参考下...2015-12-14
- 新版 Mysql 中加入了对 JSON Document 的支持,可以创建 JSON 类型的字段,并有一套函数支持对JSON的查询、修改等操作,下面就实际体验一下...2016-08-23
深入研究mysql中的varchar和limit(容易被忽略的知识)
为什么标题要起这个名字呢?commen sence指的是那些大家都应该知道的事情,但往往大家又会会略这些东西,或者对这些东西一知半解,今天我总结下自己在mysql中遇到的一些commen sense类型的问题。 ...2015-03-15- 这篇文章主要介绍了MySQL 字符串拆分操作(含分隔符的字符串截取),具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-22
- 一、先说一下为什么要分表:当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。根据个人经验,mysql执行一个sql的过程如下:1...2014-05-31
- 我们自己鼓捣mysql时,总免不了会遇到这个问题:插入中文字符出现乱码,虽然这是运维先给配好的环境,但是在自己机子上玩的时候咧,总得知道个一二吧,不然以后如何优雅的吹牛B。...2015-03-15
- 这几天在centos下装mysql,这里记录一下安装的过程,方便以后查阅Mysql5.5.37安装需要cmake,5.6版本开始都需要cmake来编译,5.5以后的版本应该也要装这个。安装cmake复制代码 代码如下: [root@local ~]# wget http://www.cm...2015-03-15
- 宿主机使用网线的时候,客户机在Bridged Adapter模式下,使用Atheros AR8131 PCI-E Gigabit Ethernet Controller上网没问题。 宿主机使用无线的时候,客户机在Bridged Adapter模式下,使用可选项里唯一一个WIFI选项,Microsoft Virtual Wifi Miniport Adapter也无法上网,故弃之。...2013-09-19
- 首先要声明一点,大部分情况下,修改MySQL密码是需要有mysql里的root权限的...2013-09-11
- MySQL命令行导出数据库: 1,进入MySQL目录下的bin文件夹:cd MySQL中到bin文件夹的目录 如我输入的命令行:cd C:/Program Files/MySQL/MySQL Server 4.1/bin (或者直接将windows的环境变量path中添加该目录) ...2013-09-26
- 一、连接Mysql格式: mysql -h主机地址 -u用户名 -p用户密码1、连接到本机上的MYSQL。首先打开DOS窗口,然后进入目录mysql/bin,再键入命令mysql -u root -p,回车后提示你输密码.注意用户名前可以有空格也可以没有空格,但是密...2015-11-08
- Navicat for MySQL注册码用来激活 Navicat for MySQL 软件,只要拥有 Navicat 注册码就能激活相应的 Navicat 产品。这篇文章主要介绍了Navicat for MySQL 11注册码\激活码汇总,需要的朋友可以参考下...2020-11-23
- 这篇文章主要介绍了mysql IS NULL使用索引案例讲解,本篇文章通过简要的案例,讲解了该项技术的了解与使用,以下就是详细内容,需要的朋友可以参考下...2021-08-14
- 这篇文章主要介绍了基于PostgreSQL和mysql数据类型对比兼容,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2020-12-25
- 一、准备编译环境,安装所需依赖包yum groupinstall 'Development' -y yum install openssl openssl-devel zlib zlib-devel -y yum install readline-devel pcre-devel ncurses-devel bison-devel cmake -y二、编译安...2015-10-21
Mysql中 show table status 获取表信息的方法
这篇文章主要介绍了Mysql中 show table status 获取表信息的方法的相关资料,需要的朋友可以参考下...2016-03-12- 这篇文章主要为大家分享了20分钟MySQL基础入门教程,快速掌握MySQL基础知识,真正了解MySQL,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2016-12-02
- 传统的关系数据库一般由数据库(database)、表(table)、记录(record)三个层次概念组成,MongoDB是由数据库(database)、集合(collection)、文档对象(document)三个层次组成。MongoDB对于关系型数据库里的表,但是集合中没有列、行和关...2013-09-11
- 这篇文章主要介绍了node.js如何操作MySQL数据库,帮助大家更好的进行web开发,感兴趣的朋友可以了解下...2020-10-29
- 这篇文章主要介绍了Delphi远程连接Mysql的实现方法,需要的朋友可以参考下...2020-06-30