兩則數(shù)據(jù)庫優(yōu)化的分析與解決是怎樣的

兩則數(shù)據(jù)庫優(yōu)化的分析與解決是怎樣的，很多新手對此不是很清楚，為了幫助大家解決這個難題，下面小編將為大家詳細講解，有這方面需求的人可以來學習下，希望你能有所收獲。

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.

本來昨天就答應顧問查看，財務軟件中的一個存在的問題，但一直在忙沒有時間來支持，今天一大早就找了顧問，問題出現(xiàn)在 ORACLE 數(shù)據(jù)庫，在執(zhí)行一個存儲過程時，第一次返回的速度很快，而第二次后續(xù)的就會越來越慢，最后可能都無法忍受了。

首先就的先看看到底是怎樣的一個存儲過程，經(jīng)過查看后，發(fā)現(xiàn)是兩個存儲過程，其中一個是一個游標，并且每次將獲取到的數(shù)值變量給另一個存儲過程，進行調用，并且另一個調用的存儲過程，另一個存儲過程存在兩個游標，屬于嵌套型的。

首先這里面最主要的一個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;

經(jīng)過存儲過程的運行，發(fā)現(xiàn)鎖存在于

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 語句的執(zhí)行時間，這個語句大約執(zhí)行的時間在 不到一分鐘，由于內(nèi)存小，數(shù)據(jù)量也比較大幾千萬的數(shù)據(jù)（其實還好）。

而其實我之前是有講過的，在數(shù)據(jù)的操作中，（SQL SERVER , MYSQL , PG， Oracle）,這幾類RDS 數(shù)據(jù)庫都最好都不要使用(尤其查詢很慢）的insert into  select 。

我們建議的方法是，查詢和插入要分開，并且ORACLE  SQL SERVER ，PG都有良好的臨時表機制，尤其是SESSION 基別的。 MYSQL 也是有臨時表的，但大概率是不使用的，這與他使用方式有關，當然要使用看具體情況。

而上面的出現(xiàn)問題的兩個原因

1  使用游標，的方式觸發(fā) insert into  select , 相當于高頻的觸發(fā)這個查詢較慢的SQL 語句，并且 INSERT INTO 和 SELECT 相當一個事務，則插入的表就會被鎖，所以造成經(jīng)常出現(xiàn)無法忍受的慢的問題，尤其是循環(huán)的次數(shù)很多的情況下。

數(shù)據(jù)庫的優(yōu)化中，是希望能批次一次性處理的，就不要分多次處理（例如游標方式），而在MYSQL 中的思想，短而小的事務，其實放到其他數(shù)據(jù)庫的使用中也是有益處的。終歸長期霸占表的 X鎖，這絕對是不美好的。

這里給出的解決方法

1 采用 ORACLE 的臨時表 SESSION級別的，那每次將數(shù)據(jù)先插入臨時表，然后在將臨時表的數(shù)據(jù) insert  into  到最終的表中，這樣降低insert into  select 的時間，對數(shù)據(jù)庫優(yōu)化是有幫助的。

2 理順邏輯，能將游標轉換為一次 select 能查詢的數(shù)據(jù)，就不要使用游標的方式。

當然還有其他的優(yōu)化方式，但目前的情況，以上兩種可以解決問題。

剛理清上面的問題，下午開發(fā)人員又過來

提出需求，是這樣的，批量要插入MYSQL 的數(shù)據(jù)，插入的表是有唯一索引的，而當插入的值與這個唯一索引有沖突的時候，則不能插入，這是當然的，是當初設計這個唯一索引的根本，就是不要他插入，防止扣款或放款重復，但問題是如果批量插入，一條插不進去，整體都ROLLBACK ，這可不是一件不美好的事情，而后期程序員改為一條條的數(shù)據(jù)插入，那其實是一件更不美好的事情，低效，對數(shù)據(jù)庫的壓力明顯增高。

最后的解決方案：

insert into  on duplicate key update  這樣的語句，既然批量的插入中發(fā)現(xiàn)有重復的，我們可以在原表增加一個字段，并且發(fā)現(xiàn)重復的值，我們就不在插入，并且更新后面的那個新添加的字段，去UPDATE 一個值。這樣既保證有重復插入不批量回滾，同時也能知道到底哪些行，曾經(jīng)有重復的值妄圖想插入。算是一個一舉兩得的idea。

看完上述內(nèi)容是否對您有幫助呢？如果還想對相關知識有進一步的了解或閱讀更多相關文章，請關注億速云行業(yè)資訊頻道，感謝您對億速云的支持。