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

一般在GROUP BY 個(gè)HAVING字句之前就能剔除多余的行，所以盡量不要用它們來(lái)做剔除行的工作。他們的執(zhí)行順序應(yīng)該如下最優(yōu)：select 的Where字句選擇所有合適的行，Group By用來(lái)分組個(gè)統(tǒng)計(jì)行，Having字句用來(lái)剔除多余的分組。這樣Group By 個(gè)Having的開(kāi)銷(xiāo)小，查詢(xún)快。對(duì)于大的數(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會(huì)多次掃描表，使用EXISTS、NOT EXISTS ，IN , LEFT OUTER JOIN 來(lái)替代，特別是左連接,而Exists比IN更快，最慢的是NOT操作.如果列的值含有空，以前它的索引不起作用， “＜＞”, “!=”, “!＞”,等還是不能優(yōu)化，用不到索引。

不要在WHere字句中的列名加函數(shù)，如Convert，substring等,如果必須用函數(shù)的時(shí)候，創(chuàng)建計(jì)算列再創(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)化器對(duì)其進(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)子查詢(xún)

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

3 SQL案例分析

3.1案例分析一

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

原先統(tǒng)計(jì)的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

根據(jù)對(duì)系統(tǒng)的分析(僅限于MS SQL Server數(shù)據(jù)庫(kù))，給出相應(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

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

3.2案例分析二

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

經(jīng)過(guò)分析發(fā)現(xiàn),在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ǔ)句導(dǎo)致速度很慢,然后把該語(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 “

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

3.3案例分析三

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

顯然這個(gè)問(wèn)題是由于不合理的子查詢(xún)?cè)斐傻模?jīng)分析后我們把子查詢(xún)從where子句中去除，語(yǔ)句改成如下后，2秒內(nèi)數(shù)據(jù)庫(kù)出來(lái)了。

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

沒(méi)有建立正確的索引，SQL 語(yǔ)句本身存在的問(wèn)題，在醫(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))