醫院數據庫系統SQL語(yǔ)句優(yōu)化分析

一般在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))