醫(yī)院數(shù)據(jù)庫系統(tǒng)SQL語句優(yōu)化分析

文就如何優(yōu)化大型數(shù)據(jù)庫的性能進(jìn)行了一些探索，提出了優(yōu)化數(shù)據(jù)庫訪問性能的若干策略，特別是對SQL語句進(jìn)行了有效的分析設(shè)計(jì)的問題，以使其加快執(zhí)行速度，減少網(wǎng)絡(luò)傳輸，能更高效地工作，充分發(fā)揮系統(tǒng)的效率。

隨著醫(yī)院信息系統(tǒng)模塊的不斷增加，特別是近兩年電子病歷的使用，臨床診療信息大量寫入數(shù)據(jù)庫，數(shù)據(jù)量急劇增加，造成業(yè)務(wù)數(shù)據(jù)庫非常龐大，業(yè)務(wù)處理的速度明顯下降?；谶@一問題，本文就如何優(yōu)化大型數(shù)據(jù)庫的性能進(jìn)行了一些探索，提出了優(yōu)化數(shù)據(jù)庫訪問性能的若干策略，特別是對SQL語句進(jìn)行了有效的分析設(shè)計(jì)的問題，以使其加快執(zhí)行速度，減少網(wǎng)絡(luò)傳輸，能更高效地工作，充分發(fā)揮系統(tǒng)的效率。

醫(yī)院經(jīng)過多年的信息化建設(shè)，取得了顯著成效，信息化由原來的以收費(fèi)、記帳為主，逐步向臨床醫(yī)療、服務(wù)病人過渡。隨著醫(yī)院信息系統(tǒng)模塊的不斷增加，特別是近兩年電子病歷的使用，臨床診療信息大量寫入數(shù)據(jù)庫，數(shù)據(jù)量急劇增加，造成業(yè)務(wù)數(shù)據(jù)庫非常龐大，業(yè)務(wù)處理的速度明顯下降。加之在頻繁的業(yè)務(wù)數(shù)據(jù)庫中還要進(jìn)行大數(shù)據(jù)量查詢或報(bào)表統(tǒng)計(jì)，導(dǎo)致在業(yè)務(wù)處理時(shí)經(jīng)常出現(xiàn)阻塞或死鎖現(xiàn)象，嚴(yán)重影響到日常的工作。故如何對數(shù)據(jù)庫性能在進(jìn)行優(yōu)化設(shè)計(jì)，即提高數(shù)據(jù)庫的吞吐量、減少用戶等待時(shí)間具有重大意義。

傳統(tǒng)的數(shù)據(jù)庫性能優(yōu)化主要從操作系統(tǒng)、客戶端應(yīng)用軟件程序設(shè)計(jì)、網(wǎng)絡(luò)及其它硬件設(shè)備等方面來考慮，這種方法只是調(diào)整數(shù)據(jù)庫的周邊環(huán)境，只能暫時(shí)緩解問題，而不能從根本上解決問題。實(shí)際應(yīng)用中，更多情況是醫(yī)院信息系統(tǒng)(包括數(shù)據(jù)庫系統(tǒng))都已設(shè)計(jì)好，只是在運(yùn)行的過程中隨著數(shù)據(jù)規(guī)模的增大，使得系統(tǒng)出現(xiàn)周期性性能問題。本文提出的醫(yī)院數(shù)據(jù)庫系統(tǒng)性能優(yōu)化是在己有的硬件設(shè)施升級、數(shù)據(jù)庫的物理設(shè)計(jì)、關(guān)系規(guī)范化等方面進(jìn)行改進(jìn)基礎(chǔ)之上，對SQL語句進(jìn)行了有效的分析設(shè)計(jì)的問題，以使其加快執(zhí)行速度，減少網(wǎng)絡(luò)傳輸，能更高效地工作，充分發(fā)揮系統(tǒng)的效率。

1 合理使用索引

提高數(shù)據(jù)庫查詢速度最有效的方法就是優(yōu)化索引。索引是建立在實(shí)體表上的一種數(shù)據(jù)組織，它可以提高訪問表中一條或多條記錄的查詢效率，使用索引的目的是為了避免全表掃描，減少磁盤I/O的次數(shù)，加快查詢速度，在大型的表中進(jìn)行索引的建立對加快表的查詢有著重要的意義。但是也并不對任何的數(shù)據(jù)表都要建立索引，索引通常能提高select、update以及delete語句的性能（當(dāng)訪問的行較少時(shí)），但會降低insert語句的性能（因?yàn)樾枰瑫r(shí)對表和索引進(jìn)行插入）。此外，過多的索引會產(chǎn)生維護(hù)上的開銷，只會降低而不是增加系統(tǒng)的性能，索引的使用要恰到好處。索引使用原則如下：

（1）在經(jīng)常進(jìn)行連接，但是沒有指定為外鍵的列上建立索引，而不經(jīng)常連接的字段則由優(yōu)化器自動生成索引。

（2）在頻繁進(jìn)行排序或分組（即進(jìn)行g(shù)roup by或order by操作）的列上建立索引，而頻繁進(jìn)行刪除、插入操作的表不要建立過多的索引。

（3）在條件表達(dá)式中經(jīng)常用到的不同值較多的列上建立檢索，在不同值少的列上不要建立索引。比如在雇員表的“性別”列上只有“男”與“女”兩個(gè)不同值，因此就沒有必要建立索引，如果在此建立索引不但不會提高查詢效率，反而會嚴(yán)重降低更新速度。

（4）如果待排序的列有多個(gè)，可以在這些列上建立復(fù)合索引（compound index）。盡量使用較窄的索引, 這樣數(shù)據(jù)頁每頁上能因存放較多的索引行而減少操作。

（5）在查詢中經(jīng)常作為條件表達(dá)式并且不同值較多的列上建立索引，而不同值較少的列上不要建立索引。

（6）當(dāng)數(shù)據(jù)庫表更新大數(shù)據(jù)后, 刪除并重新建立索引來提高查詢速度。

總之，建立索引一定要慎重，對每個(gè)索引建立的必要性都要仔細(xì)分析，一定要有建立的依據(jù)。過多的索引或不充分、不正確的索引對提升數(shù)據(jù)庫的性能毫無益處。

2 SQL語句優(yōu)化

SQL語言是一種非常靈活的語言，相同功能的實(shí)現(xiàn)?？梢杂脦追N不同的語句來表達(dá)，但語句的執(zhí)行效率可能存在很的差別。因此，任何一個(gè)數(shù)據(jù)庫應(yīng)用系統(tǒng)中，合理的對SQL語句進(jìn)行優(yōu)化將大大的提高整個(gè)數(shù)據(jù)庫系統(tǒng)的性能。所有的SQL語句執(zhí)行過程分三個(gè)階段，分別是進(jìn)行處理語法分析、執(zhí)行、讀取數(shù)據(jù)。

圖1 SQL語句執(zhí)行過程

在使用SQL時(shí)，性能差異在大型的或是復(fù)雜的數(shù)據(jù)庫環(huán)境中，如在HIS的一些大型表中表現(xiàn)尤為明顯。經(jīng)過一段時(shí)間的總結(jié)，發(fā)現(xiàn)SQL語句比較低下的原因主要來自于不恰當(dāng)?shù)乃饕O(shè)計(jì)、不充分的連接條件和不可優(yōu)化的WHERE子句及其它不恰當(dāng)?shù)恼Z句操作等，在對它們進(jìn)行適當(dāng)?shù)膬?yōu)化后，其運(yùn)行速度有了明顯提高。下面將從這幾個(gè)方面分別進(jìn)行說明：

2.1 LIKE操作符

LIKE操作符可以應(yīng)用通配符查詢，里面的通配符組合可能達(dá)到幾乎是任意的查詢，但是如果用得不好則會產(chǎn)生性能上的問題，如like 'a%' 使用索引，like ‘%a’ 不使用索引。用 like ‘%a%’ 查詢時(shí)，查詢耗時(shí)和字段值總長度成正比，所以不能用CHAR類型，而是VARCHAR。

2.2 限制返回行

在查詢Select語句中用Where字句限制返回的行數(shù)，避免表掃描，如果返回不必要的數(shù)據(jù)，浪費(fèi)了服務(wù)器的I/O資源，加重了網(wǎng)絡(luò)的負(fù)擔(dān)降低性能。如果表很大，在表掃描的期間將表鎖住，禁止其他的聯(lián)接訪問表，后果嚴(yán)重。可以使用TOP語句來限制返回結(jié)果。當(dāng)返回多行數(shù)據(jù)時(shí)，盡可能不使用光標(biāo)，因?yàn)樗加么罅康馁Y源，應(yīng)該使用datastore。

2.3 UNION操作符

UNION在進(jìn)行表鏈接后會篩選掉重復(fù)的記錄，所以在表鏈接后會對所產(chǎn)生的結(jié)果集進(jìn)行排序運(yùn)算，刪除重復(fù)的記錄再返回結(jié)果。實(shí)際大部分應(yīng)用中是不會產(chǎn)生重復(fù)的記錄，最常見的是過程表與歷史表UNION。推薦采用UNION ALL操作符替代UNION，因?yàn)閁NION ALL操作只是簡單的將兩個(gè)結(jié)果合并后就返回。

2.4 Between與IN

Between在某些時(shí)候比IN速度更快，Between能夠更快地根據(jù)索引找到范圍。如：

select * from YF_KCMX where YPXH in (12,13)

Select * from YF_KCMX where between 12 and 13

一般在GROUP BY 個(gè)HAVING字句之前就能剔除多余的行，所以盡量不要用它們來做剔除行的工作。他們的執(zhí)行順序應(yīng)該如下最優(yōu)：select 的Where字句選擇所有合適的行，Group By用來分組個(gè)統(tǒng)計(jì)行，Having字句用來剔除多余的分組。這樣Group By 個(gè)Having的開銷小，查詢快。對于大的數(shù)據(jù)行進(jìn)行分組和Having十分消耗資源。如果Group BY的目的不包括計(jì)算，只是分組，那么用Distinct更快。

2.5 注意細(xì)節(jié)

一般不要用如下的字句: “＜＞”, “!=”, “!＞”, “!＜”, “NOT”, “NOT EXISTS”, “NOT IN”, “NOT LIKE”, and “LIKE ‘%500’”，因?yàn)樗麄儾蛔咚饕潜頀呙?。NOT IN會多次掃描表，使用EXISTS、NOT EXISTS ，IN , LEFT OUTER JOIN 來替代，特別是左連接,而Exists比IN更快，最慢的是NOT操作.如果列的值含有空，以前它的索引不起作用， “＜＞”, “!=”, “!＞”,等還是不能優(yōu)化，用不到索引。

不要在WHere字句中的列名加函數(shù)，如Convert，substring等,如果必須用函數(shù)的時(shí)候，創(chuàng)建計(jì)算列再創(chuàng)建索引來替代。還可以變通寫法：

WHERE SUBSTRING(firstname,1,1) = ‘m’

改為：WHERE firstname like ‘m%’（索引掃描）,但MIN() 和 MAX()能使用到合適的索引。

select * form ZY_FYMX where FYDJ ＞ 3000

分析在此語句中若FYDJ是Float類型的，則優(yōu)化器對其進(jìn)行優(yōu)化為Convert(float,3000)，因?yàn)?000是個(gè)整數(shù)，我們應(yīng)在編程時(shí)使用3000.0而不要等運(yùn)行時(shí)讓DBMS進(jìn)行轉(zhuǎn)化。同樣字符和整型數(shù)據(jù)的轉(zhuǎn)換。應(yīng)改為：

select * form ZY_FYMX where FYDJ ＞ 3000.00

2.6 避免相關(guān)子查詢

一個(gè)列的標(biāo)簽同時(shí)在主查詢和where子句中的查詢中出現(xiàn)，那么很可能當(dāng)主查詢中的列值改變之后，子查詢必須重新查詢一次。查詢嵌套層次越多，效率越低，因此應(yīng)當(dāng)盡量避免子查詢。如果子查詢不可避免，那么要在子查詢中過濾掉盡可能多的行。

3 SQL案例分析

3.1案例分析一

醫(yī)院數(shù)據(jù)庫容量為28GB，根據(jù)對MS_CF01和MS_CF02的大小統(tǒng)計(jì)，其中MS_CF02記錄條數(shù)為1000萬條；發(fā)藥統(tǒng)計(jì)時(shí)間一個(gè)月，耗時(shí)30分鐘仍然無法得到結(jié)果，嚴(yán)重影響正常業(yè)務(wù)，遂中止。

原先統(tǒng)計(jì)的SQL語句如下：

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

根據(jù)對系統(tǒng)的分析(僅限于MS SQL Server數(shù)據(jù)庫)，給出相應(yīng)的優(yōu)化方案，可以在性能上大幅度提高：

select top 1CFSB from MS_CF01 where FYRQ＞=”2004.3.1 00:00:00”

//得到該時(shí)間段內(nèi)最小的CFSB,例如3198724

select top 1CFSB from MS_CF01 where FYRQ＜=”2004.3.30 00:00:00”

order by CFSB desc //得到該時(shí)間段內(nèi)最大的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

所有語句執(zhí)行完畢后，結(jié)果不超過18秒。

3.2案例分析二

醫(yī)院工作人員反映在藥庫系統(tǒng)自動計(jì)劃模塊執(zhí)行速度很慢，有時(shí)大約需要半個(gè)小時(shí)才能跳出窗口。

經(jīng)過分析發(fā)現(xiàn),在w_yk_plan.cb_auto.clicked() 18行開始的代碼如下：

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語句導(dǎo)致速度很慢,然后把該語句改成:

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 “

結(jié)果5秒內(nèi)執(zhí)行完成。

3.3案例分析三

以下SQL的功能是醫(yī)院用于作日報(bào)時(shí)顯示哪些收費(fèi)員還沒有做過當(dāng)天的個(gè)人日報(bào)，速度非常慢，每次都需要好幾分鐘才能出來數(shù)據(jù)。

顯然這個(gè)問題是由于不合理的子查詢造成的，經(jīng)分析后我們把子查詢從where子句中去除，語句改成如下后，2秒內(nèi)數(shù)據(jù)庫出來了。

某些HIS系統(tǒng)中病區(qū)系統(tǒng)中醫(yī)囑提交、醫(yī)囑執(zhí)行、醫(yī)技提交速度慢問題原因分析ZY_BQYZ 這個(gè)表中出院數(shù)據(jù)沒有轉(zhuǎn)出到ZY_BQYZ_CY 表中。

沒有建立正確的索引，SQL 語句本身存在的問題，在醫(yī)囑提交中的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))

在醫(yī)院執(zhí)行模塊的數(shù)據(jù)窗口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小結(jié)

從以上這些例子可以看出，SQL優(yōu)化的實(shí)質(zhì)就是在結(jié)果正確的前提下，用優(yōu)化器可以識別的語句，充份利用索引，減少表掃描的1/O 次數(shù)，盡量避免表搜索的發(fā)生。其實(shí)SQL的性能優(yōu)化是一個(gè)復(fù)雜的過程，上述這些只是在SQL語句應(yīng)用時(shí)的一種具體實(shí)例和平時(shí)應(yīng)用中的總結(jié)?？傊?，數(shù)據(jù)庫的性能優(yōu)化是一個(gè)系統(tǒng)工程，涉及的方面很多。數(shù)據(jù)庫管理員需要綜合運(yùn)用多種方法，認(rèn)真分析數(shù)據(jù)庫運(yùn)行過程中出現(xiàn)的各種問題，才能保證數(shù)據(jù)庫高效地運(yùn)行。