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

两则数据库优化的分析与解决是怎样的，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。

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。

看完上述内容是否对您有帮助呢？如果还想对相关知识有进一步的了解或阅读更多相关文章，请关注亿速云行业资讯频道，感谢您对亿速云的支持。