|
本文就如何優(yōu)化大型數據庫的性能進(jìn)行了一些探索，提出了優(yōu)化數據庫訪(fǎng)問(wèn)性能的若干策略，特別是對SQL語(yǔ)句進(jìn)行了有效的分析設計的問(wèn)題，以使其加快執行速度，減少網(wǎng)絡(luò )傳輸，能更高效地工作，充分發(fā)揮系統的效率。 隨著(zhù)醫院信息系統模塊的不斷增加，特別是近兩年電子病歷的使用，臨床診療信息大量寫(xiě)入數據庫，數據量急劇增加，造成業(yè)務(wù)數據庫非常龐大，業(yè)務(wù)處理的速度明顯下降�；谶@一問(wèn)題，本文就如何優(yōu)化大型數據庫的性能進(jìn)行了一些探索，提出了優(yōu)化數據庫訪(fǎng)問(wèn)性能的若干策略，特別是對SQL語(yǔ)句進(jìn)行了有效的分析設計的問(wèn)題，以使其加快執行速度，減少網(wǎng)絡(luò )傳輸，能更高效地工作，充分發(fā)揮系統的效率。 醫院經(jīng)過(guò)多年的信息化建設，取得了顯著(zhù)成效，信息化由原來(lái)的以收費、記帳為主，逐步向臨床醫療、服務(wù)病人過(guò)渡。隨著(zhù)醫院信息系統模塊的不斷增加，特別是近兩年電子病歷的使用，臨床診療信息大量寫(xiě)入數據庫，數據量急劇增加，造成業(yè)務(wù)數據庫非常龐大，業(yè)務(wù)處理的速度明顯下降。加之在頻繁的業(yè)務(wù)數據庫中還要進(jìn)行大數據量查詢(xún)或報表統計，導致在業(yè)務(wù)處理時(shí)經(jīng)常出現阻塞或死鎖現象，嚴重影響到日常的工作。故如何對數據庫性能在進(jìn)行優(yōu)化設計，即提高數據庫的吞吐量、減少用戶(hù)等待時(shí)間具有重大意義。 傳統的數據庫性能優(yōu)化主要從操作系統、客戶(hù)端應用軟件程序設計、網(wǎng)絡(luò )及其它硬件設備等方面來(lái)考慮，這種方法只是調整數據庫的周邊環(huán)境，只能暫時(shí)緩解問(wèn)題，而不能從根本上解決問(wèn)題。實(shí)際應用中，更多情況是醫院信息系統(包括數據庫系統)都已設計好，只是在運行的過(guò)程中隨著(zhù)數據規模的增大，使得系統出現周期性性能問(wèn)題。本文提出的醫院數據庫系統性能優(yōu)化是在己有的硬件設施升級、數據庫的物理設計、關(guān)系規范化等方面進(jìn)行改進(jìn)基礎之上，對SQL語(yǔ)句進(jìn)行了有效的分析設計的問(wèn)題，以使其加快執行速度，減少網(wǎng)絡(luò )傳輸，能更高效地工作，充分發(fā)揮系統的效率。 1 合理使用索引 提高數據庫查詢(xún)速度最有效的方法就是優(yōu)化索引。索引是建立在實(shí)體表上的一種數據組織，它可以提高訪(fǎng)問(wèn)表中一條或多條記錄的查詢(xún)效率，使用索引的目的是為了避免全表掃描，減少磁盤(pán)I/O的次數，加快查詢(xún)速度，在大型的表中進(jìn)行索引的建立對加快表的查詢(xún)有著(zhù)重要的意義。但是也并不對任何的數據表都要建立索引，索引通常能提高select、update以及delete語(yǔ)句的性能（當訪(fǎng)問(wèn)的行較少時(shí)），但會(huì )降低insert語(yǔ)句的性能（因為需要同時(shí)對表和索引進(jìn)行插入）。此外，過(guò)多的索引會(huì )產(chǎn)生維護上的開(kāi)銷(xiāo)，只會(huì )降低而不是增加系統的性能，索引的使用要恰到好處。索引使用原則如下： （1）在經(jīng)常進(jìn)行連接，但是沒(méi)有指定為外鍵的列上建立索引，而不經(jīng)常連接的字段則由優(yōu)化器自動(dòng)生成索引。 （2）在頻繁進(jìn)行排序或分組（即進(jìn)行g(shù)roup by或order by操作）的列上建立索引，而頻繁進(jìn)行刪除、插入操作的表不要建立過(guò)多的索引。 （3）在條件表達式中經(jīng)常用到的不同值較多的列上建立檢索，在不同值少的列上不要建立索引。比如在雇員表的“性別”列上只有“男”與“女”兩個(gè)不同值，因此就沒(méi)有必要建立索引，如果在此建立索引不但不會(huì )提高查詢(xún)效率，反而會(huì )嚴重降低更新速度。 （4）如果待排序的列有多個(gè)，可以在這些列上建立復合索引（compound index）。盡量使用較窄的索引, 這樣數據頁(yè)每頁(yè)上能因存放較多的索引行而減少操作。 （5）在查詢(xún)中經(jīng)常作為條件表達式并且不同值較多的列上建立索引，而不同值較少的列上不要建立索引。 （6）當數據庫表更新大數據后, 刪除并重新建立索引來(lái)提高查詢(xún)速度。 總之，建立索引一定要慎重，對每個(gè)索引建立的必要性都要仔細分析，一定要有建立的依據。過(guò)多的索引或不充分、不正確的索引對提升數據庫的性能毫無(wú)益處。 2 SQL語(yǔ)句優(yōu)化 SQL語(yǔ)言是一種非常靈活的語(yǔ)言，相同功能的實(shí)現�？梢杂脦追N不同的語(yǔ)句來(lái)表達，但語(yǔ)句的執行效率可能存在很的差別。因此，任何一個(gè)數據庫應用系統中，合理的對SQL語(yǔ)句進(jìn)行優(yōu)化將大大的提高整個(gè)數據庫系統的性能。所有的SQL語(yǔ)句執行過(guò)程分三個(gè)階段，分別是進(jìn)行處理語(yǔ)法分析、執行、讀取數據。 圖1 SQL語(yǔ)句執行過(guò)程 在使用SQL時(shí)，性能差異在大型的或是復雜的數據庫環(huán)境中，如在HIS的一些大型表中表現尤為明顯。經(jīng)過(guò)一段時(shí)間的總結，發(fā)現SQL語(yǔ)句比較低下的原因主要來(lái)自于不恰當的索引設計、不充分的連接條件和不可優(yōu)化的WHERE子句及其它不恰當的語(yǔ)句操作等，在對它們進(jìn)行適當的優(yōu)化后，其運行速度有了明顯提高。下面將從這幾個(gè)方面分別進(jìn)行說(shuō)明： 2.1 LIKE操作符 LIKE操作符可以應用通配符查詢(xún)，里面的通配符組合可能達到幾乎是任意的查詢(xún)，但是如果用得不好則會(huì )產(chǎn)生性能上的問(wèn)題，如like 'a%' 使用索引，like ‘%a’ 不使用索引。用 like ‘%a%’ 查詢(xún)時(shí)，查詢(xún)耗時(shí)和字段值總長(cháng)度成正比，所以不能用CHAR類(lèi)型，而是VARCHAR。 2.2 限制返回行 在查詢(xún)Select語(yǔ)句中用Where字句限制返回的行數，避免表掃描，如果返回不必要的數據，浪費了服務(wù)器的I/O資源，加重了網(wǎng)絡(luò )的負擔降低性能。如果表很大，在表掃描的期間將表鎖住，禁止其他的聯(lián)接訪(fǎng)問(wèn)表，后果嚴重�？梢允褂肨OP語(yǔ)句來(lái)限制返回結果。當返回多行數據時(shí)，盡可能不使用光標，因為它占用大量的資源，應該使用datastore。 2.3 UNION操作符 UNION在進(jìn)行表鏈接后會(huì )篩選掉重復的記錄，所以在表鏈接后會(huì )對所產(chǎn)生的結果集進(jìn)行排序運算，刪除重復的記錄再返回結果。實(shí)際大部分應用中是不會(huì )產(chǎn)生重復的記錄，最常見(jiàn)的是過(guò)程表與歷史表UNION。推薦采用UNION ALL操作符替代UNION，因為UNION ALL操作只是簡(jiǎn)單的將兩個(gè)結果合并后就返回。 2.4 Between與IN Between在某些時(shí)候比IN速度更快，Between能夠更快地根據索引找到范圍。如： select * from YF_KCMX where YPXH in (12,13) Select * from YF_KCMX where between 12 and 13 一般在GROUP BY 個(gè)HAVING字句之前就能剔除多余的行，所以盡量不要用它們來(lái)做剔除行的工作。他們的執行順序應該如下最優(yōu)：select 的Where字句選擇所有合適的行，Group By用來(lái)分組個(gè)統計行，Having字句用來(lái)剔除多余的分組。這樣Group By 個(gè)Having的開(kāi)銷(xiāo)小，查詢(xún)快。對于大的數據行進(jìn)行分組和Having十分消耗資源。如果Group BY的目的不包括計算，只是分組，那么用Distinct更快。 2.5 注意細節 一般不要用如下的字句: “＜＞”, “!=”, “!＞”, “!＜”, “NOT”, “NOT EXISTS”, “NOT IN”, “NOT LIKE”, and “LIKE ‘%500’”，因為他們不走索引全是表掃描。NOT IN會(huì )多次掃描表，使用EXISTS、NOT EXISTS ，IN , LEFT OUTER JOIN 來(lái)替代，特別是左連接,而Exists比IN更快，最慢的是NOT操作.如果列的值含有空，以前它的索引不起作用， “＜＞”, “!=”, “!＞”,等還是不能優(yōu)化，用不到索引。 不要在WHere字句中的列名加函數，如Convert，substring等,如果必須用函數的時(shí)候，創(chuàng )建計算列再創(chuàng )建索引來(lái)替代。還可以變通寫(xiě)法： WHERE SUBSTRING(firstname,1,1) = ‘m’ 改為：WHERE firstname like ‘m%’（索引掃描）,但MIN() 和 MAX()能使用到合適的索引。 select * form ZY_FYMX where FYDJ ＞ 3000 分析在此語(yǔ)句中若FYDJ是Float類(lèi)型的，則優(yōu)化器對其進(jìn)行優(yōu)化為Convert(float,3000)，因為3000是個(gè)整數，我們應在編程時(shí)使用3000.0而不要等運行時(shí)讓DBMS進(jìn)行轉化。同樣字符和整型數據的轉換。應改為： select * form ZY_FYMX where FYDJ ＞ 3000.00 2.6 避免相關(guān)子查詢(xún) 一個(gè)列的標簽同時(shí)在主查詢(xún)和where子句中的查詢(xún)中出現，那么很可能當主查詢(xún)中的列值改變之后，子查詢(xún)必須重新查詢(xún)一次。查詢(xún)嵌套層次越多，效率越低，因此應當盡量避免子查詢(xún)。如果子查詢(xún)不可避免，那么要在子查詢(xún)中過(guò)濾掉盡可能多的行。 3 SQL案例分析 3.1案例分析一 醫院數據庫容量為28GB，根據對MS_CF01和MS_CF02的大小統計，其中MS_CF02記錄條數為1000萬(wàn)條；發(fā)藥統計時(shí)間一個(gè)月，耗時(shí)30分鐘仍然無(wú)法得到結果，嚴重影響正常業(yè)務(wù)，遂中止。 原先統計的SQL語(yǔ)句如下： select sum(MS_CF02.YPSL*MS_CF02.YPDJ*MS_CF02.CFTS) as total from MS_CF01,MS_CF02 where MS_CF01.CFSB=MS_CF02.CFSB and MS_CF01.CFLX=1 and (MS_CF01.FYBZ=1 or MS_CF01.FYBZ=3) and MS_CF01.FYRQ＞=”2004.3.1 00:00:00” and MS_CF01.FYRQ＜=”2004.3.30 00:00:00” and MS_CF01.ZFPB=0 根據對系統的分析(僅限于MS SQL Server數據庫)，給出相應的優(yōu)化方案，可以在性能上大幅度提高： select top 1CFSB from MS_CF01 where FYRQ＞=”2004.3.1 00:00:00” //得到該時(shí)間段內最小的CFSB,例如3198724 select top 1CFSB from MS_CF01 where FYRQ＜=”2004.3.30 00:00:00” order by CFSB desc //得到該時(shí)間段內最大的CFSB，例為4178763 select sum(MS_CF02.YPSL*MS_CF02.YPDJ*MS_CF02.CFTS) as total from MS_CF01,MS_CF02 where MS_CF01.CFSB=MS_CF02.CFSB and MS_CF01.CFLX=1 and MS_CF02.CFSB＞=3198724 and MS_CF02.CFSB＜=4178763 and (MS_CF01.FYBZ=1 or MS_CF01.FYBZ=3) and MS_CF01.ZFPB=0 所有語(yǔ)句執行完畢后，結果不超過(guò)18秒。 3.2案例分析二 醫院工作人員反映在藥庫系統自動(dòng)計劃模塊執行速度很慢，有時(shí)大約需要半個(gè)小時(shí)才能跳出窗口。 經(jīng)過(guò)分析發(fā)現,在w_yk_plan.cb_auto.clicked() 18行開(kāi)始的代碼如下： ls_select+=" select YK_TYPK.YPXH,YPMC,YPGG,YPDW,GCSL,DCSL,0 " ls_select+=" from YK_TYPK,YK_KCMX " ls_select+=" where XTSB="+string(base_info.syscode)+" and DCSL＞0 and GCSL＞DCSL and YKZF=0 " ls_select+=" and YK_TYPK.YPXH not in (select YPXH from YK_KCMX) " ls_select+=" group by YK_TYPK.YPXH,YPMC,YPGG,YPDW,GCSL,DCSL " 顯然是NOT IN語(yǔ)句導致速度很慢,然后把該語(yǔ)句改成: ls_select+=" select YK_TYPK.YPXH,YPMC,YPGG,YPDW,GCSL,DCSL,0 " ls_select+=" from YK_TYPK " ls_select+=" where XTSB="+string(base_info.syscode)+" and DCSL＞0 and GCSL＞DCSL and YKZF=0 " ls_select+=" and not exists ( select YPXH FROM YK_KCMX WHERE YK_TYPK.YPXH = YK_KCMX.YPXH ) " ls_select+=" group by YK_TYPK.YPXH,YPMC,YPGG,YPDW,GCSL,DCSL “ 結果5秒內執行完成。 3.3案例分析三 以下SQL的功能是醫院用于作日報時(shí)顯示哪些收費員還沒(méi)有做過(guò)當天的個(gè)人日報，速度非常慢，每次都需要好幾分鐘才能出來(lái)數據。 顯然這個(gè)問(wèn)題是由于不合理的子查詢(xún)造成的，經(jīng)分析后我們把子查詢(xún)從where子句中去除，語(yǔ)句改成如下后，2秒內數據庫出來(lái)了。 某些HIS系統中病區系統中醫囑提交、醫囑執行、醫技提交速度慢問(wèn)題原因分析ZY_BQYZ 這個(gè)表中出院數據沒(méi)有轉出到ZY_BQYZ_CY 表中。 沒(méi)有建立正確的索引，SQL 語(yǔ)句本身存在的問(wèn)題，在醫囑提交中的WHERE子句： WHERE ( ZY_BQYZ.ZYH = ZY_BRRY.ZYH AND ZY_BRRY.CYPB = 0 ) AND ( ZY_BQYZ.SRKS = :al_hsql) AND ( ZY_BQYZ.LSBZ=0 OR ZY_BQYZ.LSBZ=2) AND ( ZY_BQYZ.SYBZ = 0) AND ( ZY_BQYZ.XMLX＜4 ) AND ( ZY_BQYZ.YZPB=0 ) AND ( ZY_BQYZ.FYSX＜2) AND ( ZY_BQYZ.JFBZ＜2) AND ( ZY_BQYZ.YSBZ = 0 OR (ZY_BQYZ.YSBZ = 1 AND ZY_BQYZ.YSTJ = 1)) 在醫院執行模塊的數據窗口SQL： SELECT DISTINCT ZY_BQYZ.ZYH,ZY_BRRY.BRCH,ZY_BRRY.ZYHM,ZY_BRRY.BRXM,ZY_BRRY.BRXZ FROM ZY_BQYZ, ZY_BRRY WHERE ( ZY_BQYZ.ZYH = ZY_BRRY.ZYH ) AND ( ZY_BRRY.CYPB = 0 ) AND ( ZY_BQYZ.SRKS = :al_hsql ) AND ( ZY_BQYZ.SYBZ = 0 ) AND ( ZY_BQYZ.LSBZ = 0 OR ZY_BQYZ.LSBZ = 2 ) AND ( ZY_BQYZ.QRSJ ＜ :ad_today OR ( ZY_BQYZ.QRSJ IS NULL ) ) AND ( ZY_BQYZ.JFBZ = 2 OR ZY_BQYZ.JFBZ = 9 ) AND ( ZY_BQYZ.XMLX ＞ 3 ) AND ( ZY_BQYZ.YZPB = 0 ) ORDER BY ZY_BRRY.BRCH 4小結 從以上這些例子可以看出，SQL優(yōu)化的實(shí)質(zhì)就是在結果正確的前提下，用優(yōu)化器可以識別的語(yǔ)句，充份利用索引，減少表掃描的1/O 次數，盡量避免表搜索的發(fā)生。其實(shí)SQL的性能優(yōu)化是一個(gè)復雜的過(guò)程，上述這些只是在SQL語(yǔ)句應用時(shí)的一種具體實(shí)例和平時(shí)應用中的總結�？傊�，數據庫的性能優(yōu)化是一個(gè)系統工程，涉及的方面很多。數據庫管理員需要綜合運用多種方法，認真分析數據庫運行過(guò)程中出現的各種問(wèn)題，才能保證數據庫高效地運行。 |
