首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >两则数据库优化的分析与解决

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

作者头像
AustinDatabases
发布2019-06-21 15:58:06
6980
发布2019-06-21 15:58:06
举报
文章被收录于专栏:AustinDatabasesAustinDatabases

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。

本文参与 腾讯云自媒体分享计划,分享自微信公众号。
原始发表:2019-04-30,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 AustinDatabases 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档