陳慶

摘要：對于大型的企業(yè)或部門來說，每天都需要處理大量的數(shù)據(jù)業(yè)務(wù)，數(shù)據(jù)庫系統(tǒng)的設(shè)計變的異常復雜，而數(shù)據(jù)庫的性能的好壞直接影響到各項業(yè)務(wù)能否順利進行。然而數(shù)據(jù)庫優(yōu)化涉及到許多方面，如：良好的系統(tǒng)和數(shù)據(jù)庫設(shè)計；優(yōu)質(zhì)的SQL語句編寫；合適的數(shù)據(jù)表索引設(shè)計等，甚至包括各種硬件因素，如：網(wǎng)絡(luò)性能；服務(wù)器的性能；操作系統(tǒng)的性能等。如果只想通過升級硬件系統(tǒng)來提升性能將會付出很大的經(jīng)濟代價，通過優(yōu)化SQL語句的編寫提升數(shù)據(jù)庫性能會是一個不錯的選擇。

關(guān)鍵詞：數(shù)據(jù)庫設(shè)計;數(shù)據(jù)庫系統(tǒng);數(shù)據(jù)庫性能;SQL語句;優(yōu)化

中圖分類號：TP311文獻標識碼：A文章編號：1009-3044(2012)05-0988-03

SQL（Structured Query Language）結(jié)構(gòu)化查詢語言，是一種高級的非過程化編程語言，是溝通數(shù)據(jù)庫服務(wù)器和客戶端的重要工具，它不要求用戶指定對數(shù)據(jù)的存放方法，也不需要用戶了解具體的數(shù)據(jù)存放方式，所以具有在不同數(shù)據(jù)庫系統(tǒng)上使用相同的SQL語言作為數(shù)據(jù)輸入與管理的接口。它以記錄集合作為操作對象，所有SQL語句接受集合作為輸入，返回集合作為輸出，這種集合特性允許一條SQL語句的輸出作為另一條SQL語句的輸入，所以SQL語句可以嵌套，這使它具有極大的靈活性和強大的功能，在多數(shù)情況下，在其他語言中需要一大段程序?qū)崿F(xiàn)的功能只需要一個SQL語句就可以達到目的，這也意味著用SQL語言可以寫出非常復雜的語句。然而SQL語句的不同寫法卻會對數(shù)據(jù)庫系統(tǒng)運行帶來完全不同的效果，本文就如何優(yōu)化SQL語句提升系統(tǒng)性能作一深入探討。

1優(yōu)化SQL語句的好處

對于大部分基于數(shù)據(jù)庫的應(yīng)用程序來說，多數(shù)都是C/S或B/S架構(gòu)，其與數(shù)據(jù)庫的聯(lián)系是通過客戶端嵌入的SQL語句或調(diào)用數(shù)據(jù)庫上的過程實現(xiàn)的。所以SQL語句質(zhì)量的好壞會影響整個系統(tǒng)，因此優(yōu)化SQL語句有以下幾個好處：一是提高系統(tǒng)的運行效率，減少數(shù)據(jù)庫死鎖的風險；二是降低系統(tǒng)對硬件資源的消耗，節(jié)約投資；三是強化系統(tǒng)源代碼的可讀性，降低程序員修改程序的勞動強度。

2合理使用數(shù)據(jù)庫索引

索引是對數(shù)據(jù)庫表中一列或多列的值進行排序的一種結(jié)構(gòu)，使用索引可快速訪問數(shù)據(jù)庫表中的特定信息。

創(chuàng)建索引可以大大提高系統(tǒng)的性能。第一，通過創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。第二，可以大大加快數(shù)據(jù)的檢索速度，這也是創(chuàng)建索引的最主要的原因。第三，可以加速表和表之間的連接，特別是在實現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義。第四，在使用分組和排序子句進行數(shù)據(jù)檢索時，同樣可以顯著減少查詢中分組和排序的時間。第五，通過使用索引，可以在查詢的過程中，使用優(yōu)化器，提高系統(tǒng)的性能。

然而增加索引也有許多不利的方面。第一，創(chuàng)建索引和維護索引要耗費時間，這種時間隨著數(shù)據(jù)量的增加而增加。第二，索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會更大。第三，當對表中的數(shù)據(jù)進行增加、刪除和修改的時候，索引也要動態(tài)的維護，這樣就降低了數(shù)據(jù)的維護速度。

因此在寫SQL語句時要合理使用數(shù)據(jù)庫索引，這會讓你的數(shù)據(jù)庫運行效果事半功倍。

3優(yōu)化SQL語句的方法

3.1合理的使用各種操作符

首先要注意操作符的使用方法，雖然有時候不同的操作符實現(xiàn)的功能是一樣的，但執(zhí)行的效率卻相差很多，這對于數(shù)據(jù)庫的初學者是常常容易忽視的。

3.1.1 IN（NOT IN）與EXISTS（NOT EXISTS）操作符的區(qū)別

我們在調(diào)用數(shù)據(jù)的時候會將一列和一系列值相比較，最簡單的辦法就是在WHERE子句中使用子查詢，而在WHERE子句中有兩種方式的子查詢。下面以zy_dxsfls，zy_brzlls兩個表為例，如下所示：

第一種方式使用IN操作符，

Select a.blh,a.sfks,a.sfrq,a.yzm,a.zfje,a.xzys

From zy_dxsfls a

where a.blh in (select b.blh from zy_brzlls b where b.csrq>= 1970-1-1)；

第二種方式使用EXIST操作符，

Select a.blh,a.sfks,a.sfrq,a.yzm,a.zfje,a.xzys

From zy_dxsfls a

where exists (select 1 from zy_brzlls b where b.blh=a.blh and b.csrq>=1970-1-1)。

雖然兩種方法得出的結(jié)果是一樣的，但是使用的時候卻是有區(qū)別的。IN操作符適合于zy_brzlls表大而zy_dxsfls表小的情況，而EXISTS操作符適合于zy_brzlls表小而zy_dxsfls表大的情況。因此使用的時候應(yīng)視情況而定，不能盲目使用，NOT IN操作符和NOT EXISTS操作符也是一樣的。

2.1.2巧用比較條件（>、>=、<、<=）操作符

以大于操作符和大于等于操作符為例，它們能實現(xiàn)相同的功能，但是對它們優(yōu)化之后會有不同的效果。如一個表有100萬記錄，有一個數(shù)值型字段“A”，A=0的記錄有30萬行，A=1的記錄30萬行，A=2的記錄有39萬行，A=3的記錄有1萬行。那么執(zhí)行A>2與A>=3的效果就有很大的區(qū)別了，因為A>2時數(shù)據(jù)庫會先找出為2的記錄再進行比較，而A>=3時數(shù)據(jù)庫則直接找到等于3的記錄，所以選擇好的比較條件會提高數(shù)據(jù)庫的執(zhí)行效率。

3.1.3盡量少用UNION操作符

UNION操作符是用來合并查詢結(jié)果的，最常見的是過程表與歷史表的合并。在執(zhí)行該操作時數(shù)據(jù)庫先取出兩個表的結(jié)果，再用排序空間進行排序刪除重復的記錄，最后返回結(jié)果集，如果表數(shù)據(jù)量大的話可能會導致數(shù)據(jù)庫系統(tǒng)效率下降。不過可以采用UNION ALL操作符替代UNION，因為UNION ALL操作只是簡單的將兩個結(jié)果合并后就返回。

3.1.4變相使用IS NULL（IS NOT NULL）操作符

NULL值即空值的意思，根據(jù)數(shù)據(jù)庫的特性，是不能使用包含NULL值的字段作為索引的，即使對該字段建立了索引，只要某行上面有NULL值，該字段就不能使用索引。但是我們可以使用變相方法來表示NULL值，如將某行上的NULL值用0表示，這樣WHERE條件語句“a is null”就可以改成“a=0”來實現(xiàn)；另外還可以設(shè)置字段不允許為空，而用一個缺省值代替空值，如一個時間類型的字段，可以將默認時間設(shè)為“1900-01-01”來表示空值。

3.1.5合理使用LIKE操作符

LIKE操作符是可以使用通配符查詢的語句，里面的通配符組合可以達到任意多個，但是如果用得不好則會產(chǎn)生性能上的問題，如yzm like %阿莫西林%這種查詢不會使用數(shù)據(jù)庫索引，而yzm like 阿莫西林%則會使用數(shù)據(jù)庫索引。用一個實例測試，如表1所示。

表1

3.1.6避免在WHERE子句中做函數(shù)計算

這樣做會使數(shù)據(jù)庫在表的每行上進行運算，從而導致該列的索引不能被使用而觸發(fā)全表掃描。我們可以從下列對比中看出效果，如表2所示。

表2

還有很多操作符都不能利用數(shù)據(jù)庫索引，如：NOT、!=、<>、!>、!<、NOT EXISTS、NOT IN、NOT LIKE、OR等，這些都會導致數(shù)據(jù)庫引擎放棄使用索引而進行全表掃描。

3.2合理的書寫SQL語句

3.2.1注意SQL語句中空格和字母的大小寫

首先讓我們看一個示例，如表3所示。

以上四個SQL語句分析整理之后產(chǎn)生的結(jié)果及執(zhí)行的時間是一樣的，但是從數(shù)據(jù)庫共享內(nèi)存的原理，可以得出數(shù)據(jù)庫對每個SQL語句都會進行一次分析，并且占用共享內(nèi)存，如果將SQL的字符串及格式寫得完全相同，則數(shù)據(jù)庫只會分析一次，共享內(nèi)存也只會留下一次的分析結(jié)果，這不僅可以減少分析SQL語句的時間，而且可以減少共享內(nèi)存重復的信息。

表3

3.2.2注意WHERE子句后面的條件順序

WHERE子句后面的條件順序?qū)Υ髷?shù)據(jù)量表的查詢會產(chǎn)生直接的影響。以下面兩條語句為例：Select id From Emp Where emp? no<=2000 and sal>=1000；Select id From Emp Where sal>=1000 and empno<=2000；假設(shè)以上兩個SQL語句中“empno”及“sal”兩個字段都沒有索引，所以執(zhí)行的時候都是全表掃描，其中的“sal>=1000”條件在記錄集內(nèi)比率為99%，而“empno<=2000”的比率只為0.5%。在執(zhí)行第一條SQL語句的時候，查詢先得到符合條件sal>=1000的記錄M條，接著處理條件empno<=2000這一次只需要在第一次查詢的結(jié)果集中進行查詢即可，得出N條，所以總共查詢了M+N條記錄。而在進行第二條SQL語句的時候，查詢先得到符合條件emp? no<=2000的記錄為A條，接著處理條件sal>=1000，同樣只要在第一次查詢的結(jié)果集中進行查詢即可，得出B條，所以總共查詢了A+ B條記錄。可以看出數(shù)據(jù)庫掃描的數(shù)據(jù)量A+B

3.2.3按需提取字段，避免使用“SELECT *”

首先，讓我們看一個示例，有一張表zy_dxsfls擁有1500萬行數(shù)據(jù)，測試結(jié)果如表4所示。

表4

從上面的結(jié)果我們可以看出，數(shù)據(jù)執(zhí)行的效率相差很大，這是由以下兩個方面造成的：（1）“SELECT *”提取了所有的數(shù)據(jù)，數(shù)據(jù)容量大（字節(jié)數(shù)多），查詢效率自然會降低，所以字段的提取一定要按照“用多少提多少”的原則，避免使用“SELECT *”這樣的操作，但提升的速度還要看你舍棄的字段的大小來判斷。(2)“SELECT *”的最大問題是影響查詢的方式，數(shù)據(jù)庫主要使用索引去查詢你需要的數(shù)據(jù)，當索引包含你請求查詢的所有字段時，數(shù)據(jù)庫就不會再去表中查詢，這個概念稱作索引覆蓋。因為索引掃描比其他方式的掃描快很多。

3.3適當?shù)氖褂眠^渡表

把表的一個子集進行排序并創(chuàng)建臨時表，有時能加速查詢。它有助于避免多重排序操作，而且在其他方面還能簡化優(yōu)化器的工作。以查詢在院病人費用超過1萬元的醫(yī)囑明細為例，如下所示：

Select a.BLH,b.SFKS,b.SFRQ,b.YZM,b.ZFJE,……From zy_brzl a,zy_dxsf b

where a.blh=b.blh and a.zt=在院 and a.zfje>=10000

Order by a.blh；

如果這個查詢要被執(zhí)行多次而不止一次，可以把所有未付款的客戶找出來放在一個臨時文件中，并按客戶的名字進行排序。SQL語句可以改為：

Select a.BLH,b.SFKS,b.SFRQ,b.YZM,b.ZFJE,……Into zy_brzl_zy

From zy_brzl a,zy_dxsf b

Where a.blh=b.blh and a.zt=在院

Order by a.blh；

然后以下面的方式在臨時表中查詢:Select * From zy_brzl_zy Where zfje >=10000；

由于臨時表中的行要比主表中的行少，而且物理順序就是所要求的順序，減少了磁盤的I/O操作，所以查詢工作量可以得到大幅減少。但是需要注意臨時表創(chuàng)建后不會反映主表的修改，在主表中數(shù)據(jù)頻繁修改的情況下，不要丟失數(shù)據(jù)。

4應(yīng)用實例

以作者所在單位為例，原本使用的是IBM3650服務(wù)器，100多個客戶端，應(yīng)用程序比較單一，整個業(yè)務(wù)系統(tǒng)運行還算平穩(wěn)?？墒莾H僅過了2年多的時間，單位業(yè)務(wù)量便出現(xiàn)急劇增長，應(yīng)用程序不斷擴充，現(xiàn)在已經(jīng)擁有了300個客戶端，而系統(tǒng)的運行也出現(xiàn)了問題，客戶端的應(yīng)用程序會不時的出現(xiàn)“卡機”現(xiàn)象，數(shù)據(jù)庫還會出現(xiàn)“死鎖”的情況。經(jīng)過深入研究發(fā)現(xiàn)，每次出現(xiàn)問題都是一些特定的SQL語句造成的，后來經(jīng)過大規(guī)模的優(yōu)化SQL語句和數(shù)據(jù)庫索引使問題得到了圓滿的解決，避免了硬件資源在未到使用壽命之前就被更換掉的命運，從而為單位節(jié)約了大量人力和物力，提高了經(jīng)濟效益。

5總結(jié)

對于擁有幾百甚至上千臺客戶端的企業(yè)或部門來說，每天都有大量的并發(fā)業(yè)務(wù)數(shù)據(jù)，如果應(yīng)用程序中的SQL語句寫的不好，就需要消耗更多的硬件資源來滿足系統(tǒng)的運行，從而導致企業(yè)或部門花費更多的人力和財力去提升系統(tǒng)硬件。因此優(yōu)秀的SQL語句將為整個業(yè)務(wù)系統(tǒng)的執(zhí)行效率帶來很大的提高，減少數(shù)據(jù)庫死鎖的風險，降低數(shù)據(jù)庫維護成本，為大型的企業(yè)或部門帶來經(jīng)濟效益的提升。

參考文獻：

[1]范秀平,尚武.SQL語法范例手冊[M].北京:科學出版社,2007.

[2]蒙祖強.SQL Server 2005應(yīng)用開發(fā)大全[M].北京:清華大學出版社,2007.

[3]黃開枝,康會光,于軍.SQL Server 2005中文版基礎(chǔ)教程[M].北京:清華大學出版社,2007.

[4]韓朝軍,梁冰,劉瑩.SQL Server管理與開發(fā)技術(shù)大全[M].北京:人民郵電出版社,2007.