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

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

2.5 注意細節(jié)

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

不要在WHere字句中的列名加函數(shù)，如Convert，substring等,如果必須用函數(shù)的時候，創(chuàng)建計算列再創(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)化器對其進行優(yōu)化為Convert(float,3000)，因為3000是個整數(shù)，我們應在編程時使用3000.0而不要等運行時讓DBMS進行轉(zhuǎn)化。同樣字符和整型數(shù)據(jù)的轉(zhuǎn)換。應改為：

select * form ZY_FYMX where FYDJ ＞ 3000.00

2.6 避免相關子查詢

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

3 SQL案例分析

3.1案例分析一

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

原先統(tǒng)計的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ōu)化方案，可以在性能上大幅度提高：

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

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

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

order by CFSB desc //得到該時間段內(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í)行完畢后，結果不超過18秒。

3.2案例分析二

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

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

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秒內(nèi)執(zhí)行完成。

3.3案例分析三

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

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

某些HIS系統(tǒng)中病區(qū)系統(tǒng)中醫(yī)囑提交、醫(yī)囑執(zhí)行、醫(yī)技提交速度慢問題原因分析ZY_BQYZ 這個表中出院數(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))