No matter who or what, you will not destroy me. If you knock me down, I'll get back up. If you beat me, I will rise and try again.
本来昨天就答应顾问查看,财务软件中的一个存在的问题,但一直在忙没有时间来支持,今天一大早就找了顾问,问题出现在 ORACLE 数据库,在执行一个存储过程时,第一次返回的速度很快,而第二次后续的就会越来越慢,最后可能都无法忍受了。
首先就的先看看到底是怎样的一个存储过程,经过查看后,发现是两个存储过程,其中一个是一个游标,并且每次将获取到的数值变量给另一个存储过程,进行调用,并且另一个调用的存储过程,另一个存储过程存在两个游标,属于嵌套型的。
首先这里面最主要的一个SQL 是这样的
insert into cntvoucher_wqt
(vchdate, kmh, opkmh, dir, vchmemo, mny, mccode, vtid)
select cnt.vchdate,
cnt.kmh,
cnt.opkmh,
cnt.dir,
cnt.vchmemo,
sum(cnt.mny) mny,
cpid,
vtid_id
from cntvoucher cnt
left join cntbusssheet sheet
on cnt.transid = sheet.sheetid
where cnt.vchdate = f_actdate
--and sheet.extaddr2 in
and exists (select distinct b.extaddr2
from cntvoucher a
left join cntbusssheet b on a.transid = b.sheetid
where sheet.extaddr2 = b.extaddr2
and a.cpid = f_eventcode
and a.kmh = f_km
and a.dir = f_dir
and a.vchdate = f_actdate
-- and a.vchdate>=date'2018-01-01'
)
group by cnt.vchdate, cnt.kmh, cnt.opkmh, cnt.dir, cnt.vchmemo, cpid;
经过存储过程的运行,发现锁存在于
INSERT INTO CNTVOUCHER_WQT (VCHDATE, KMH, OPKMH, DIR, VCHMEMO, MNY, MCCODE, VTID)
SELECT CNT.VCHDATE, CNT.KMH, CNT.OPKMH, CNT.DIR, CNT.VCHMEMO, SUM(CNT.MNY) MNY, CPID, :B2
FROM CNTVOUCHER CNT
LEFT JOIN CNTBUSSSHEET SHEET ON CNT.TRANSID = SHEET.SHEETID WHERE CNT.VCHDATE = :B1 AND
EXISTS (SELECT DISTINCT B.EXTADDR2
FROM CNTVOUCHER A
LEFT JOIN CNTBUSSSHEET B ON A.TRANSID = B.SHEETID
WHERE SHEET.EXTADDR2 = B.EXTADDR2 AND A.CPID = :B5 AND A.KMH = :B4
AND A.DIR = :B3 AND A.VCHDATE = :B1 )
GROUP BY CNT.VCHDATE, CNT.KMH, CNT.OPKMH, CNT.DIR, CNT.VCHMEMO, CPID
以上的语句。
通过查看EXPLAIN 并验证这个 select 语句的执行时间,这个语句大约执行的时间在 不到一分钟,由于内存小,数据量也比较大几千万的数据(其实还好)。
而其实我之前是有讲过的,在数据的操作中,(SQL SERVER , MYSQL , PG, Oracle),这几类RDS 数据库都最好都不要使用(尤其查询很慢)的insert into select 。
我们建议的方法是,查询和插入要分开,并且ORACLE SQL SERVER ,PG都有良好的临时表机制,尤其是SESSION 基别的。 MYSQL 也是有临时表的,但大概率是不使用的,这与他使用方式有关,当然要使用看具体情况。
而上面的出现问题的两个原因
1 使用游标,的方式触发 insert into select , 相当于高频的触发这个查询较慢的SQL 语句,并且 INSERT INTO 和 SELECT 相当一个事务,则插入的表就会被锁,所以造成经常出现无法忍受的慢的问题,尤其是循环的次数很多的情况下。
数据库的优化中,是希望能批次一次性处理的,就不要分多次处理(例如游标方式),而在MYSQL 中的思想,短而小的事务,其实放到其他数据库的使用中也是有益处的。终归长期霸占表的 X锁,这绝对是不美好的。
这里给出的解决方法
1 采用 ORACLE 的临时表 SESSION级别的,那每次将数据先插入临时表,然后在将临时表的数据 insert into 到最终的表中,这样降低insert into select 的时间,对数据库优化是有帮助的。
2 理顺逻辑,能将游标转换为一次 select 能查询的数据,就不要使用游标的方式。
当然还有其他的优化方式,但目前的情况,以上两种可以解决问题。
刚理清上面的问题,下午开发人员又过来
提出需求,是这样的,批量要插入MYSQL 的数据,插入的表是有唯一索引的,而当插入的值与这个唯一索引有冲突的时候,则不能插入,这是当然的,是当初设计这个唯一索引的根本,就是不要他插入,防止扣款或放款重复,但问题是如果批量插入,一条插不进去,整体都ROLLBACK ,这可不是一件不美好的事情,而后期程序员改为一条条的数据插入,那其实是一件更不美好的事情,低效,对数据库的压力明显增高。
最后的解决方案是
insert into on duplicate key update 这样的语句,既然批量的插入中发现有重复的,我们可以在原表增加一个字段,并且发现重复的值,我们就不在插入,并且更新后面的那个新添加的字段,去UPDATE 一个值。这样既保证有重复插入不批量回滚,同时也能知道到底哪些行,曾经有重复的值妄图想插入。算是一个一举两得的idea。
本文分享自 AustinDatabases 微信公众号,前往查看
如有侵权,请联系 cloudcommunity@tencent.com 删除。
本文参与 腾讯云自媒体同步曝光计划 ,欢迎热爱写作的你一起参与!