2017年08月28日   码农之路   12,180 次浏览

近日在操作中需要将某个表的数据通过另一个表的数据来批量更新,因为数据量大,试了N多种方法(修改子查询、增加索引、删除索引、禁用触发器等等)都不理想,先看看最先的SQL如下:

update TF_USERS tt1
set (DGSID, VDATE) = (
	select t4.dgsid, t4.vdate from(
		select max(t2.dgsid) dgsid, t2.pid, t2.vdate from 
		(select t1.pid,  max(t1.VDATE) VDATE from HC_DIAGNOSIS t1
		where t1.pid in(
			SELECT a1.pid FROM TF_USERS a1
			left JOIN HC_DIAGNOSIS a2 ON a1.DGSID = a2.DGSID
			 where a1.dgsid is not null and a2.DGSID is null and a1.pid < 903425000
		)
		group by t1.PID) t3
		left join HC_DIAGNOSIS t2 on T2.pid = t3.pid and T2.VDATE = T3.VDATE
		group by t2.pid, t2.vdate
	) t4 where tt1.pid = t4.pid
)
where tt1.pid in(
	SELECT a1.pid FROM TF_USERS a1
	left JOIN HC_DIAGNOSIS a2 ON a1.DGSID = a2.DGSID
	 where a1.dgsid is not null and a2.DGSID is null and a1.pid < 903425000
)
受影响的行: 624
时间: 124.632s

以上SQL平均每条持行时间需要0.2秒,然而我的数据库中有30多万数据需要更新,粗略计算下需要十几个小时来计算了,天啦!!!这怎么能行呢?通过简单的测试分析了下原因,主要是数据量太大、查询太复杂、消耗内存(这个只是我的猜测),最后还是决定用存储过程来实现。

然后上网找有没有简单的方法,突然眼前一亮,发现了“快速游标更新法”——它可以支持复杂逻辑的查询语句,更新准确,无论数据多大更新效率依然很高,但执行后不返回影响行数。这不就是我需要的方法么。具体格式如下:

begin
  for cr in (查询语句) loop  --循环
    update table_name set ...   --更新语句
  end loop;  --结束循环
end;

原理就是先查询出来需要更新的数据,然后通过循环去更新数据,这样每次只需更新一条记录,速度自然快,以下是修改后的SQL,速度杠杠滴。

begin
for cr in (
	select t4.pid, t4.dgsid, t4.vdate from (
		select max(t2.dgsid) dgsid, t2.pid, t2.vdate from 
		(select t1.pid,  max(t1.VDATE) VDATE from HC_DIAGNOSIS t1
		where t1.pid in(
			SELECT a1.pid FROM TF_USERS a1
			left JOIN HC_DIAGNOSIS a2 ON a1.DGSID = a2.DGSID
			 where a1.dgsid is not null and a2.DGSID is null and a1.pid < 903425000
		)
		group by t1.PID) t3
		left join HC_DIAGNOSIS t2 on T2.pid = t3.pid and T2.VDATE = T3.VDATE
		group by t2.pid, t2.vdate
	) t4
) loop  --循环
update TF_USERS set DGSID = cr.dgsid, VDATE = cr.vdate where pid = cr.pid;
end loop;  --结束循环
end;
受影响的行: 1
时间: 4.151s

 

赞 赏
申明:除非注明,本站文章均为原创,转载请以链接形式标明本文地址。 如有问题,请于一周内与本站联系,本站将在第一时间对相关内容进行处理。
本文地址: http://www.yyjjssnn.cn/articles/765.html

>>> Hello World <<<

这篇内容是否帮助到你了呢?

如果你有任何疑问或有建议留给其他朋友,都可以给我留言。

目前有有一条留言:

  • 1# 白完古月:
    2018-06-28 14:58

    批量修改的sql大数据时很慢 ,快速游标更新法不错,可以减少很多时间
    ps,网站做得不错~

:wink: :twisted: :surprised: :smile: :smile9: :smile8: :smile7: :smile6: :smile5: :smile56: :smile55: :smile54: :smile53: :smile52: :smile51: :smile50: :smile4: :smile49: :smile48: :smile47: :smile46: :smile45: :smile44: :smile43: :smile42: :smile41: :smile40: :smile3: :smile39: :smile38: :smile37: :smile36: :smile35: :smile34: :smile33: :smile32: :smile31: :smile30: :smile2: :smile29: :smile28: :smile27: :smile26: :smile25: :smile24: :smile23: :smile22: :smile21: :smile20: :smile1: :smile19: :smile18: :smile17: :smile16: :smile15: :smile14: :smile13: :smile12: :smile11: :smile10: :smile0: :sad: :rolleyes1: :redface: :razz: :question: :neutral: :mrgreen: :mad: :lol: :idea: :exclaim: :evil: :eek: :cry: :cool: :confused: :biggrin: :arrow:

友情链接: 程序员刘杨 刘杨
Copyright 2003~2018 保留所有权利 | 网站地图
备案号:湘ICP备14001005号-2

湘公网安备 43011102001322号