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
>>> Hello World <<<
这篇内容是否帮助到你了呢?
如果你有任何疑问或有建议留给其他朋友,都可以给我留言。
目前有有一条留言:
批量修改的sql大数据时很慢 ,快速游标更新法不错,可以减少很多时间
ps,网站做得不错~