醫(yī)院數(shù)據(jù)庫系統(tǒng)SQL語句優(yōu)化分析
文就如何優(yōu)化大型數(shù)據(jù)庫的性能進行了一些探索,提出了優(yōu)化數(shù)據(jù)庫訪問性能的若干策略,特別是對SQL語句進行了有效的分析設(shè)計的問題,以使其加快執(zhí)行速度,減少網(wǎng)絡(luò)傳輸,能更高效地工作,充分發(fā)揮系統(tǒng)的效率。
本文引用地址:http://www.ex-cimer.com/article/199295.htm隨著醫(yī)院信息系統(tǒng)模塊的不斷增加,特別是近兩年電子病歷的使用,臨床診療信息大量寫入數(shù)據(jù)庫,數(shù)據(jù)量急劇增加,造成業(yè)務(wù)數(shù)據(jù)庫非常龐大,業(yè)務(wù)處理的速度明顯下降?;谶@一問題,本文就如何優(yōu)化大型數(shù)據(jù)庫的性能進行了一些探索,提出了優(yōu)化數(shù)據(jù)庫訪問性能的若干策略,特別是對SQL語句進行了有效的分析設(shè)計的問題,以使其加快執(zhí)行速度,減少網(wǎng)絡(luò)傳輸,能更高效地工作,充分發(fā)揮系統(tǒng)的效率。
醫(yī)院經(jīng)過多年的信息化建設(shè),取得了顯著成效,信息化由原來的以收費、記帳為主,逐步向臨床醫(yī)療、服務(wù)病人過渡。隨著醫(yī)院信息系統(tǒng)模塊的不斷增加,特別是近兩年電子病歷的使用,臨床診療信息大量寫入數(shù)據(jù)庫,數(shù)據(jù)量急劇增加,造成業(yè)務(wù)數(shù)據(jù)庫非常龐大,業(yè)務(wù)處理的速度明顯下降。加之在頻繁的業(yè)務(wù)數(shù)據(jù)庫中還要進行大數(shù)據(jù)量查詢或報表統(tǒng)計,導(dǎo)致在業(yè)務(wù)處理時經(jīng)常出現(xiàn)阻塞或死鎖現(xiàn)象,嚴(yán)重影響到日常的工作。故如何對數(shù)據(jù)庫性能在進行優(yōu)化設(shè)計,即提高數(shù)據(jù)庫的吞吐量、減少用戶等待時間具有重大意義。
傳統(tǒng)的數(shù)據(jù)庫性能優(yōu)化主要從操作系統(tǒng)、客戶端應(yīng)用軟件程序設(shè)計、網(wǎng)絡(luò)及其它硬件設(shè)備等方面來考慮,這種方法只是調(diào)整數(shù)據(jù)庫的周邊環(huán)境,只能暫時緩解問題,而不能從根本上解決問題。實際應(yīng)用中,更多情況是醫(yī)院信息系統(tǒng)(包括數(shù)據(jù)庫系統(tǒng))都已設(shè)計好,只是在運行的過程中隨著數(shù)據(jù)規(guī)模的增大,使得系統(tǒng)出現(xiàn)周期性性能問題。本文提出的醫(yī)院數(shù)據(jù)庫系統(tǒng)性能優(yōu)化是在己有的硬件設(shè)施升級、數(shù)據(jù)庫的物理設(shè)計、關(guān)系規(guī)范化等方面進行改進基礎(chǔ)之上,對SQL語句進行了有效的分析設(shè)計的問題,以使其加快執(zhí)行速度,減少網(wǎng)絡(luò)傳輸,能更高效地工作,充分發(fā)揮系統(tǒng)的效率。
1 合理使用索引
提高數(shù)據(jù)庫查詢速度最有效的方法就是優(yōu)化索引。索引是建立在實體表上的一種數(shù)據(jù)組織,它可以提高訪問表中一條或多條記錄的查詢效率,使用索引的目的是為了避免全表掃描,減少磁盤I/O的次數(shù),加快查詢速度,在大型的表中進行索引的建立對加快表的查詢有著重要的意義。但是也并不對任何的數(shù)據(jù)表都要建立索引,索引通常能提高select、update以及delete語句的性能(當(dāng)訪問的行較少時),但會降低insert語句的性能(因為需要同時對表和索引進行插入)。此外,過多的索引會產(chǎn)生維護上的開銷,只會降低而不是增加系統(tǒng)的性能,索引的使用要恰到好處。索引使用原則如下:
(1)在經(jīng)常進行連接,但是沒有指定為外鍵的列上建立索引,而不經(jīng)常連接的字段則由優(yōu)化器自動生成索引。
(2)在頻繁進行排序或分組(即進行g(shù)roup by或order by操作)的列上建立索引,而頻繁進行刪除、插入操作的表不要建立過多的索引。
(3)在條件表達式中經(jīng)常用到的不同值較多的列上建立檢索,在不同值少的列上不要建立索引。比如在雇員表的“性別”列上只有“男”與“女”兩個不同值,因此就沒有必要建立索引,如果在此建立索引不但不會提高查詢效率,反而會嚴(yán)重降低更新速度。
(4)如果待排序的列有多個,可以在這些列上建立復(fù)合索引(compound index)。盡量使用較窄的索引, 這樣數(shù)據(jù)頁每頁上能因存放較多的索引行而減少操作。
(5)在查詢中經(jīng)常作為條件表達式并且不同值較多的列上建立索引,而不同值較少的列上不要建立索引。
(6)當(dāng)數(shù)據(jù)庫表更新大數(shù)據(jù)后, 刪除并重新建立索引來提高查詢速度。
總之,建立索引一定要慎重,對每個索引建立的必要性都要仔細分析,一定要有建立的依據(jù)。過多的索引或不充分、不正確的索引對提升數(shù)據(jù)庫的性能毫無益處。
2 SQL語句優(yōu)化
SQL語言是一種非常靈活的語言,相同功能的實現(xiàn)??梢杂脦追N不同的語句來表達,但語句的執(zhí)行效率可能存在很的差別。因此,任何一個數(shù)據(jù)庫應(yīng)用系統(tǒng)中,合理的對SQL語句進行優(yōu)化將大大的提高整個數(shù)據(jù)庫系統(tǒng)的性能。所有的SQL語句執(zhí)行過程分三個階段,分別是進行處理語法分析、執(zhí)行、讀取數(shù)據(jù)。
圖1 SQL語句執(zhí)行過程
在使用SQL時,性能差異在大型的或是復(fù)雜的數(shù)據(jù)庫環(huán)境中,如在HIS的一些大型表中表現(xiàn)尤為明顯。經(jīng)過一段時間的總結(jié),發(fā)現(xiàn)SQL語句比較低下的原因主要來自于不恰當(dāng)?shù)乃饕O(shè)計、不充分的連接條件和不可優(yōu)化的WHERE子句及其它不恰當(dāng)?shù)恼Z句操作等,在對它們進行適當(dāng)?shù)膬?yōu)化后,其運行速度有了明顯提高。下面將從這幾個方面分別進行說明:
2.1 LIKE操作符
LIKE操作符可以應(yīng)用通配符查詢,里面的通配符組合可能達到幾乎是任意的查詢,但是如果用得不好則會產(chǎn)生性能上的問題,如like 'a%' 使用索引,like ‘%a’ 不使用索引。用 like ‘%a%’ 查詢時,查詢耗時和字段值總長度成正比,所以不能用CHAR類型,而是VARCHAR。
2.2 限制返回行
在查詢Select語句中用Where字句限制返回的行數(shù),避免表掃描,如果返回不必要的數(shù)據(jù),浪費了服務(wù)器的I/O資源,加重了網(wǎng)絡(luò)的負擔(dān)降低性能。如果表很大,在表掃描的期間將表鎖住,禁止其他的聯(lián)接訪問表,后果嚴(yán)重??梢允褂肨OP語句來限制返回結(jié)果。當(dāng)返回多行數(shù)據(jù)時,盡可能不使用光標(biāo),因為它占用大量的資源,應(yīng)該使用datastore。
2.3 UNION操作符
UNION在進行表鏈接后會篩選掉重復(fù)的記錄,所以在表鏈接后會對所產(chǎn)生的結(jié)果集進行排序運算,刪除重復(fù)的記錄再返回結(jié)果。實際大部分應(yīng)用中是不會產(chǎn)生重復(fù)的記錄,最常見的是過程表與歷史表UNION。推薦采用UNION ALL操作符替代UNION,因為UNION ALL操作只是簡單的將兩個結(jié)果合并后就返回。
2.4 Between與IN
Between在某些時候比IN速度更快,Between能夠更快地根據(jù)索引找到范圍。如:
select * from YF_KCMX where YPXH in (12,13)
Select * from YF_KCMX where between 12 and 13
評論