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

120 篇文章 18 订阅
108 篇文章 4 订阅

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

表名表名含义行记录数
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首推) 

  • 2
    点赞
  • 27
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值