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。
