醫院數據庫系統SQL語句優化分析
加入技術交流群
一般在GROUP BY 個HAVING字句之前就能剔除多余的行，所以盡量不要用它們來做剔除行的工作。他們的執行順序應該如下最優：select 的Where字句選擇所有合適的行，Group By用來分組個統計行，Having字句用來剔除多余的分組。這樣Group By 個Having的開銷小，查詢快。對于大的數據行進行分組和Having十分消耗資源。如果Group BY的目的不包括計算，只是分組，那么用Distinct更快。
2.5 注意細節
一般不要用如下的字句: “＜＞”, “!=”, “!＞”, “!＜”, “NOT”, “NOT EXISTS”, “NOT IN”, “NOT LIKE”, and “LIKE ‘%500’”，因為他們不走索引全是表掃描。NOT IN會多次掃描表，使用EXISTS、NOT EXISTS ，IN , LEFT OUTER JOIN 來替代，特別是左連接,而Exists比IN更快，最慢的是NOT操作.如果列的值含有空，以前它的索引不起作用， “＜＞”, “!=”, “!＞”,等還是不能優化，用不到索引。
不要在WHere字句中的列名加函數，如Convert，substring等,如果必須用函數的時候，創建計算列再創建索引來替代。還可以變通寫法：
WHERE SUBSTRING(firstname,1,1) = ‘m’
改為：WHERE firstname like ‘m%’（索引掃描）,但MIN() 和 MAX()能使用到合適的索引。
select * form ZY_FYMX where FYDJ ＞ 3000
分析在此語句中若FYDJ是Float類型的，則優化器對其進行優化為Convert(float,3000)，因為3000是個整數，我們應在編程時使用3000.0而不要等運行時讓DBMS進行轉化。同樣字符和整型數據的轉換。應改為：
select * form ZY_FYMX where FYDJ ＞ 3000.00
2.6 避免相關子查詢
一個列的標簽同時在主查詢和where子句中的查詢中出現，那么很可能當主查詢中的列值改變之后，子查詢必須重新查詢一次。查詢嵌套層次越多，效率越低，因此應當盡量避免子查詢。如果子查詢不可避免，那么要在子查詢中過濾掉盡可能多的行。
3 SQL案例分析
3.1案例分析一
醫院數據庫容量為28GB，根據對MS_CF01和MS_CF02的大小統計，其中MS_CF02記錄條數為1000萬條；發藥統計時間一個月，耗時30分鐘仍然無法得到結果，嚴重影響正常業務，遂中止。
原先統計的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
根據對系統的分析(僅限于MS SQL Server數據庫)，給出相應的優化方案，可以在性能上大幅度提高：
select top 1CFSB from MS_CF01 where FYRQ＞=”2004.3.1 00:00:00”
//得到該時間段內最小的CFSB,例如3198724
select top 1CFSB from MS_CF01 where FYRQ＜=”2004.3.30 00:00:00”
order by CFSB desc //得到該時間段內最大的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
所有語句執行完畢后，結果不超過18秒。
3.2案例分析二
醫院工作人員反映在藥庫系統自動計劃模塊執行速度很慢，有時大約需要半個小時才能跳出窗口。
經過分析發現,在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秒內執行完成。
3.3案例分析三
以下SQL的功能是醫院用于作日報時顯示哪些收費員還沒有做過當天的個人日報，速度非常慢，每次都需要好幾分鐘才能出來數據。
顯然這個問題是由于不合理的子查詢造成的，經分析后我們把子查詢從where子句中去除，語句改成如下后，2秒內數據庫出來了。
某些HIS系統中病區系統中醫囑提交、醫囑執行、醫技提交速度慢問題原因分析ZY_BQYZ 這個表中出院數據沒有轉出到ZY_BQYZ_CY 表中。
沒有建立正確的索引，SQL 語句本身存在的問題，在醫囑提交中的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))
評論