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

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

成都創(chuàng)新互聯(lián)公司專注于旌陽網(wǎng)站建設(shè)服務(wù)及定制，我們擁有豐富的企業(yè)做網(wǎng)站經(jīng)驗。 熱誠為您提供旌陽營銷型網(wǎng)站建設(shè)，旌陽網(wǎng)站制作、旌陽網(wǎng)頁設(shè)計、旌陽網(wǎng)站官網(wǎng)定制、重慶小程序開發(fā)服務(wù)，打造旌陽網(wǎng)絡(luò)公司原創(chuàng)品牌,更為您提供旌陽網(wǎng)站排名全網(wǎng)營銷落地服務(wù)。

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.

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

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

首先這里面最主要的一個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 也是有臨時表的，但大概率是不使用的，這與他使用方式有關(guān)，當(dāng)然要使用看具體情況。

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

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

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

這里給出的解決方法

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

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

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

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

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

最后的解決方案：

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

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

分享名稱：兩則數(shù)據(jù)庫優(yōu)化的分析與解決是怎樣的
URL網(wǎng)址：http://muchs.cn/article30/piccpo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供自適應(yīng)網(wǎng)站、品牌網(wǎng)站設(shè)計、云服務(wù)器、企業(yè)網(wǎng)站制作、全網(wǎng)營銷推廣、品牌網(wǎng)站建設(shè)

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)