MySQL更新千万级表数据


参考: https://blog.csdn.net/zhouzhiwengang/article/details/127794994?spm=1001.2014.3001.5506

业务需求

更新推送消息表的历史数据,测试环境30W+数据,没什么问题。
去线上执行SQL报错(***The total number of locks exceeds the lock table size***)
线上的表,数据2.4KW,虽然符合条件的数据才1W多条
在不改动数据库配置的情况下,去更新数据。
备注:索引、锁表、存储过程相关知识点自己百度

update语句

举例:

update user set name = '张三' where city = '山东' and name = '李四';
测试环境比较快,毕竟才30W数据
到线上环境,2.4KW数据,报错。

update语句升级

举例:

update user set name = '张三' where id in (select id from (select id from user where city = '山东' and name = '李四') as temp);
通过条件查询到主键id,再通过主键id去更新
SQL进行了升级,但还是不行,数据量太大

存储过程

参考博客:
需要查出表的最大id(根据最大id设计循环次数)

SELECT id FROM user ORDER BY id DESC LIMIT 1;
-- 创建存储过程
create procedure batch_update_user()
begin
	-- 定义变量
	declare i int default 1;
	declare pageSize int default 1000000; -- 这里一次100W
	declare j int default 1;
	
	while i < 26 do
		if i = 1 then
			update user set  name = '张三'  where id >=1 and id <=pageSize AND city = '山东' and name = '李四' ;
		else
			set j = (i -1) * pageSize + 1;
			update user set  name = '张三'   where id >=j and id <= i*pageSize AND city = '山东' and name = '李四' ;
		end if;
		set i = i + 1;
	end while;
end
;
-- 执行 存储过程
CALL batch_update_user();
-- 删除存储过程
DROP PROCEDURE  batch_update_user;
通过存储过程来循环执行,当然也可以写程序去for循环执行(直接执行存储过程省事)
结果:2.4kw数据,修改符合条件的数据1W+,大约耗时300S
  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

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

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

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值