MySQL单表千万级数据处理的思路分享

项目背景

在处理过程中，今天上午需要更新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

思路总结

  1. 避免深度翻页进而使用自增id进行查询数据和数据
  2. 使用多进程处理数据

数据处理技巧

记录处理成功与处理失败的数据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单表千万级数据处理的资料请关注我们其它相关文章！

赞 (0)

相关推荐

  • mysql千万级数据分页查询性能优化

    mysql数据量大时使用limit分页,随着页码的增大,查询效率越低下. 实验 1.直接使用用limit start, count分页语句: select * from order limit start, count 当起始页较小时,查询没有性能问题,我们分别看下从10, 100, 1000, 10000开始分页的执行时间(每页取20条), 如下: select * from order limit 10, 20 0.016秒 select * from order limit 100, 20

  • mysql千万级数据大表该如何优化?

    1.数据的容量:1-3年内会大概多少条数据,每条数据大概多少字节: 2.数据项:是否有大字段,那些字段的值是否经常被更新: 3.数据查询SQL条件:哪些数据项的列名称经常出现在WHERE.GROUP BY.ORDER BY子句中等: 4.数据更新类SQL条件:有多少列经常出现UPDATE或DELETE 的WHERE子句中: 5.SQL量的统计比,如:SELECT:UPDATE+DELETE:INSERT=多少? 6.预计大表及相关联的SQL,每天总的执行量在何数量级? 7.表中的数据:更新为主的

  • MySQL循环插入千万级数据

    1.创建测试表 CREATE TABLE `mysql_genarate` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uuid` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5999001 DEFAULT CHARSET=utf8; 2.创建一个循环插入的存储过程 CREATE DEFINER=`root`@`localhost` PROCEDURE

  • 详解MySQL数据库千万级数据查询和存储

    百万级数据处理方案 数据存储结构设计 表字段设计 表字段 not null,因为 null 值很难查询优化且占用额外的索引空间,推荐默认数字 0. 数据状态类型的字段,比如 status, type 等等,尽量不要定义负数,如 -1.因为这样可以加上 UNSIGNED,数值容量就会扩大一倍. 可以的话用 TINYINT.SMALLINT 等代替 INT,尽量不使用 BIGINT,因为占的空间更小. 字符串类型的字段会比数字类型占的空间更大,所以尽量用整型代替字符串,很多场景是可以通过编码逻辑来实

  • MySQL 千万级数据量如何快速分页

    前言 后端开发中为了防止一次性加载太多数据导致内存.磁盘IO都开销过大,经常需要分页展示,这个时候就需要用到MySQL的LIMIT关键字.但你以为LIMIT分页就万事大吉了么,Too young,too simple啊,LIMIT在数据量大的时候极可能造成的一个问题就是深度分页. 案例 这里我以显示电商订单详情为背景举个例子,新建表如下: CREATE TABLE `cps_user_order_detail` ( `id` bigint(20) unsigned NOT NULL AUTO_I

  • .Net Core导入千万级数据至Mysql数据库的实现方法

    .Net Core导入千万级数据至Mysql数据库的实现方法

    ​最近在工作中,涉及到一个数据迁移功能,从一个txt文本文件导入到MySQL功能. 数据迁移,在互联网企业可以说经常碰到,而且涉及到千万级.亿级的数据量是很常见的.大数据量迁移,这里面就涉及到一个问题:高性能的插入数据. 今天我们就来谈谈MySQL怎么高性能插入千万级的数据. 我们一起对比以下几种实现方法: 前期准备 订单测试表 CREATE TABLE `trade` ( `id` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',

  • mysql千万级数据量根据索引优化查询速度的实现

    (一)索引的作用 索引通俗来讲就相当于书的目录,当我们根据条件查询的时候,没有索引,便需要全表扫描,数据量少还可以,一旦数据量超过百万甚至千万,一条查询sql执行往往需要几十秒甚至更多,5秒以上就已经让人难以忍受了. 能在软件上解决的,就不在硬件上解决,毕竟硬件提升代码昂贵,性价比太低.代价小且行之有效的解决方法就是合理的加索引.索引使用得当,能使查询速度提升上千倍,效果惊人. (二)mysql的索引类型: mysql的索引有5种:主键索引.普通索引.唯一索引.全文索引.聚合索引(多列索引).

  • .Net Core导入千万级数据至Mysql的步骤

    .Net Core导入千万级数据至Mysql的步骤

    前期准备 订单测试表 CREATE TABLE `trade` (   `id` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',   `trade_no` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_unicode_ci',   UNIQUE INDEX `id` (`id`),   INDEX `trade_no` (`trade_no`) ) COMMENT='订单' COLLATE

  • MySQL单表千万级数据处理的思路分享

    项目背景 在处理过程中,今天上午需要更新A字段,下午爬虫组完成了规格书或图片的爬取又需要更新图片和规格书字段,由于单表千万级深度翻页会导致处理速度越来越慢. select a,b,c from db.tb limit 10000 offset 9000000 但是时间是有限的,是否有更好的方法去解决这种问题呢? 改进思路 是否有可以不需要深度翻页也可以进行数据更新的凭据? 是的,利用自增id列 观察数据特征 此单表有自增id列且为主键,根据索引列查询数据和更新数据是最理想的途径. select

  • MySQL单表多关键字模糊查询的实现方法

    在最近的一个项目需要实现在MySQL单表多关键字模糊查询,但这数个关键字并不一定都存在于某个字段.例如现有table表,其中有title,tag,description三个字段,分别记录一条资料的标题,标签和介绍.然后根据用户输入的查询请求,将输入的字串通过空格分割为多个关键字,再在这三个字段中查询包含这些关键字的记录. 可目前遇到的问题是,这些关键字是可能存在于三个字段中的任意一个或者多个,但又要求三个字段必须包含所有的关键词.如果分别对每个字段进行模糊匹配,是没法实现所需的要求,由此想到两种

  • 为什么说MySQL单表数据不要超过500万行

    今天,探讨一个有趣的话题:MySQL 单表数据达到多少时才需要考虑分库分表?有人说 2000 万行,也有人说 500 万行.那么,你觉得这个数值多少才合适呢? 曾经在中国互联网技术圈广为流传着这么一个说法:MySQL 单表数据量大于 2000 万行,性能会明显下降.事实上,这个传闻据说最早起源于百度.具体情况大概是这样的,当年的 DBA 测试 MySQL性能时发现,当单表的量在 2000 万行量级的时候,SQL 操作的性能急剧下降,因此,结论由此而来.然后又据说百度的工程师流动到业界的其它公司,

  • MySQL单表查询常见操作实例总结

    本文实例总结了MySQL单表查询常见操作.分享给大家供大家参考,具体如下: 创建fruits表: CREATE TABLE fruits ( f_id char(10) NOT NULL, s_id INT NOT NULL, f_name char(255) NOT NULL, f_price decimal(8,2) NOT NULL, PRIMARY KEY(f_id) ) ; INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES(

  • MySQL单表查询操作实例详解【语法、约束、分组、聚合、过滤、排序等】

    本文实例讲述了MySQL单表查询操作.分享给大家供大家参考,具体如下: 语法 一.单表查询的语法 SELECT 字段1,字段2... FROM 表名                   WHERE 条件                   GROUP BY field                   HAVING 筛选                   ORDER BY field                   LIMIT 限制条数 二.关键字的执行优先级(重点) 重点中的重点:关键

  • MySQL单表恢复的步骤

    正休息的时候一个电话将我的睡意完全打散,"开发童鞋写update SQL的时候忘了加where条件了",相信每一个DBA同学听到这个消息的时候都有骂街的冲动吧.万幸只是单表写花了,而不是哪位大神在DB里面drop table玩.虽然已经很久没进行单表恢复了,但是还好步骤都印在脑海中,没有出问题的就恢复完了. 言归正传,记录一下单表恢复的步骤和关键点,提醒自己也提醒大家. 第一步: 找一台性能比较高的服务器作为还原机,从备份池中将最近的一次备份恢复到这台还原机上.当然这个前提是你有备份,

  • MySQL单表查询实例详解

    1.准备数据 以下操作将在该表中进行 create table student ( id int unsigned primary key auto_increment, name char(12) not null, gender enum("male","famale") default "male", age tinyint unsigned not null, hoc_group char(12) not null, html tinyi

  • MySql 快速插入千万级大数据的方法示例

    在数据分析领域,数据库是我们的好帮手.不仅可以接受我们的查询时间,还可以在这基础上做进一步分析.所以,我们必然要在数据库插入数据.在实际应用中,我们经常遇到千万级,甚至更大的数据量.如果没有一个快速的插入方法,则会事倍功半,花费大量的时间. 在参加阿里的天池大数据算法竞赛中(流行音乐趋势预测),我遇到了这样的问题,在没有优化数据库查询及插入之前,我花了不少冤枉时间,没有优化之前,1500万条数据,光插入操作就花费了不可思议的12个小时以上(使用最基本的逐条插入).这也促使我思考怎样优化数据库插入

  • MySQL单表百万数据记录分页性能优化技巧

    MySQL单表百万数据记录分页性能优化技巧

    测试环境: 先让我们熟悉下基本的sql语句,来查看下我们将要测试表的基本信息 use infomation_schema SELECT * FROM TABLES WHERE TABLE_SCHEMA = 'dbname' AND TABLE_NAME = 'product' 查询结果: 从上图中我们可以看到表的基本信息: 表行数:866633 平均每行的数据长度:5133字节 单表大小:4448700632字节 关于行和表大小的单位都是字节,我们经过计算可以知道 平均行长度:大约5k 单表总大

随机推荐