两则数据库优化的分析与解决

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(AustinDatabases)

原文发表时间:2019-04-30

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券