MySQL 百万级/千万级表 全量更新

这篇具有很好参考价值的文章主要介绍了MySQL 百万级/千万级表 全量更新。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

业务需求:今天从生成测试环境迁移了一批百万级/千万级表的数据,领导要求将这批数据进行脱敏处理(将真实姓名 、电话、邮箱、身份证号等敏感信息进行替换)。迁移数据记录数如下(小于百万级的全量更新不是本文重点):

表名 表名含义 行记录数
base_house 房屋表 4201183
base_license 预售证表 17653209
base_contract 网签合同 1500579

解决办法:

第一种:使用Update 语句进行全表更新。

结论:update 更新时间慢长,单表百万级数据更新少说几小时起步。这种方案放弃

第二种:使用存储过程,进行批量更新。

实战:

create procedure batch_update_house()
begin
	-- 定义变量
	declare i int default 1;
    -- 批次更新大小 10000
	declare pageSize int default 10000;
	declare j int default 1;
	-- 421 为房屋总记录 数/10000 向上取整
	while i < 421 do
		if i = 1 then
            -- 温馨提示:update 语句不能直接使用limit,必须使用子查询
			update base_house set real_name ="******", real_phone="136****0511"  where id in(select id from (select id from base_house limit  i, pageSize ) as temp);
		else
			set j = i * pageSize + 1;
            -- 温馨提示:update 语句不能直接使用limit,必须使用子查询
			update base_house set real_name ="******", real_phone="136****0511"  where id in(select id from (select id from base_house limit  j, pageSize ) as temp);
		end if;
		set i = i + 1;
	end while;
end

功能说明: batch_update_house 房屋全量更新存储过程。

结论:伴随limit 偏移量量增大,每次花费寻找起始位置行的时间会延长,但能够避免超长时间执行。(入选清洗方案)   

第三种:使用中间表,通过大表关联中间表进行update,通过where 条件命中索引,可以提升批量更新寻找起始位置行的时间     

第一步:设计房屋清洗中间表:base_middle_house,设计两个字段(id 主键 自增长、house_id 房屋关联主键,唯一主键)

create table "base_middle_house" (
    `id` int(11) not null auto_increment comment '主键',
    `house_id` varchar(200) not null comment '房屋关联主键',
    primary key (`id`),
    unique key `unique_house_id` (`house_id`)
) engine=InnoDB auto_increment=1 default charset=utf8mb4

第二步:创建批量更新存储过程

create procedure batch_update_middle_house()
begin
	-- 定义变量
	declare i int default 1;
	declare pageSize int default 100000;
	declare j int default 1;
	
	while i < 43 do
		if i = 1 then
			update base_house h, base_middle_house t set  h.real_name ="******", h.real_phone="136****0511"  where t.house_id = h.id and t.id >=1 amd t.id <=100000;
		else
			set j = (i -1) * pageSize + 1;
			update base_house h, base_middle_house t set h.real_name ="******", h.real_phone="136****0511"  where t.house_id = h.id and t.id >=j amd t.id <= i*pageSize;
		end if;
		set i = i + 1;
	end while;
end

实战:执行batch_update_middle_house 存储过程,base_house 全表4201183 记录数在1小时10分钟内全部清洗完毕。

结论:第三种方案针对第二种方案对于寻找起始行位置通过索引进行了优化,批量更新的时间也有明细的提示,达到单台服务器每分钟处理5000条记录数据。初步达到领导的要求。(清洗方案s首推) 文章来源地址https://www.toymoban.com/news/detail-454553.html

到了这里,关于MySQL 百万级/千万级表 全量更新的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 【Spark数仓项目】需求八:MySQL的DataX全量导入和增量导入Hive

    【Spark数仓项目】需求八:MySQL的DataX全量导入和增量导入Hive 本需求将模拟从MySQL中向Hive数仓中导入数据,数据以时间分区。测试两种导入场景,一种是将数据全量导入,即包含所有时间分区;另一种是每天运行调度,仅导入当天时间分区中的用户数据。 mysql表建表语句:

    2024年02月03日
    浏览(37)
  • kafka千万级数据积压原因以及解决方案

    一、原因 kafka作为消息队列,其中数据积压也是经常遇到的问题之一。 我们都知道,数据积压的直接原因,一定是系统中的某个部分出现了性能问题,来不及处理上游发送的数据,才会导致数据积压。 那么我们就需要分析在使用kafka时,如何通过优化代码以及参数配置来最大

    2024年02月12日
    浏览(44)
  • 千万级数据深分页查询SQL性能优化实践

    如何在Mysql中实现上亿数据的遍历查询?先来介绍一下系统主角:关注系统,主要是维护京东用户和业务对象之前的关注关系;并对外提供各种关系查询,比如查询用户的关注商品或店铺列表,查询用户是否关注了某个商品或店铺等。但是最近接到了一个新需求,要求提供查

    2024年02月11日
    浏览(55)
  • ElasticSearch + Canal 开发千万级的实时搜索系统【转】

    我们总结一下新搜索系统需要解决的几个问题: 海量请求。几百万的请求毫无压力,上千万上亿也要可以扛得住。 实时搜索。指的是当一个用户修改了其数据之后,另一个用户能实时地搜索到改用户。 海量请求。要扛得起海量的搜索请求,可以使用ElasticSearch来实现,它是在

    2024年04月09日
    浏览(39)
  • 千万级数据并发解决方案(理论+实战) 高并发解决思路 方案

    课程地址 项目地址 秒杀 高并发 新闻系统 超大数据量 一般的网站 写入的少 读取的次数多 模糊查询 数据量少的时候可以用 like 数据量多的时候用 Elasticsearch搜索引擎 占用磁盘空间比较大 在laravel中 创建 要提前配置好数据库 在Tests文件中写入 运行命令 php artisan app:tests 生成

    2024年02月08日
    浏览(83)
  • Java中处理千万级数据的最佳实践:性能优化指南

    在今天的数字化时代,处理大规模数据已经成为许多Java应用程序的核心任务。无论您是构建数据分析工具、实现实时监控系统,还是处理大规模日志文件,性能优化都是确保应用程序能够高效运行的关键因素。本指南将介绍一系列最佳实践,帮助您在处理千万级数据时提高

    2024年02月03日
    浏览(50)
  • 百万数据慢慢读?Pandas性能优化法速读百万级数据无压力

    作为数据分析工作者,我们每天都要处理大量数据,这时Pandas等工具的读取性能也就备受关注。特别是当数据集达到百万行以上时,如何提高读取效率,让数据分析工作跑上“快车道”?本文将详细分析Pandas读取大数据的性能优化方法,以及一些建议和经验。 1. 使用SQL进行预处理 可

    2024年02月09日
    浏览(48)
  • 千万级数据的表,我把慢sql优化后性能提升30倍!

    背景:系统中有一个统计页面加载特别慢,前端设置的40s超时时间都加载不出来数据,因为是个统计页面,基本上一猜就知道是mysql的语句有问题,遗留了很久没有解决,正好趁不忙的时候,下定决心一定把它给搞定! (mysql5.7) 执行一下问题sql,可以看到单表查就需要61s 这

    2024年02月14日
    浏览(49)
  • 百万级sql server数据库优化案例分享

            在我们的IT职业生涯中,能有一次百万级的数据库的优化经历是很难得的,如果你遇到了恭喜你,你的职业生涯将会更加完美,如果你遇到并解决了,那么一定足够你炫耀很多年。         这里我将要分享一次完美的百万级数据库优化经历,希望能给在IT行业的小

    2024年02月17日
    浏览(68)
  • 千万级并发架构下,如何进行关系型数据库的分库分表

    最近项目上线后由于用户量的剧增,导致数据库的数据量剧增,随之而来的就是海量数据存储的问题,针对最近解决数据的优化过程,谈谈sql语句的优化以及数据库分库分表的方案。 建议大家先阅读一下数据库的优化方案 《数据库大数据量的优化方案》,里面从 1.优化现有数

    2024年02月16日
    浏览(51)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包