SQL結構化查詢字符串的改寫，是實現(xiàn)數(shù)據(jù)庫查詢性能提升的最現(xiàn)實、最有效的手段，有時甚至是唯一的手段，比如在不允許大幅度修改現(xiàn)有數(shù)據(jù)庫結構的情況下。
通過優(yōu)化SQL語句提高查詢性能的關鍵是：
? 根據(jù)實際需求情況，建立合適的索引；
? 使用一切可能的方式去利用好索引，避免全表掃描；
? 盡量減少內(nèi)存及數(shù)據(jù)I/O方面的開銷

一、 建立索引

（一）建立“適當”的索引，是快速查詢的基礎。

索引（index）是除表之外另一重要的、用戶定義的存儲在物理介質(zhì)上的數(shù)據(jù)結構。當根據(jù)索引碼的值搜索數(shù)據(jù)時，索引提供了對數(shù)據(jù)的快速訪問。事實上，沒有索引，數(shù)據(jù)庫也能根據(jù)SELECT語句成功地檢索到結果，但隨著表變得越來越大，使用“適當”的索引的效果就越來越明顯。注意，在這句話中，我們用了 “適當”這個詞，這是因為，如果使用索引時不認真考慮其實現(xiàn)過程，索引既可以提高也會破壞數(shù)據(jù)庫的工作性能。
索引實際上是一種特殊的目錄，SQL SERVER提供了兩種索引：
? 聚集索引（clustered index，也稱聚類索引、簇集索引）
我們把這種正文內(nèi)容本身就是一種按照一定規(guī)則排列的目錄稱為“聚集索引”。
例如：
漢語字典中按拼音查某一個字，就是使用“聚集索引”，實際上，你根本用不著查目錄，直接在字典正文里找，就能很快找到需要的漢字（假設你知道發(fā)音）。
? 非聚集索引（nonclustered index，也稱非聚類索引、非簇集索引）
我們把目錄純粹是目錄，正文純粹是正文的排序方式稱為“非聚集索引”。
例如：
漢語字典中按部首查某一個字，部首目錄和正文一定要刻意的通過頁碼才能聯(lián)系到一起，其順序不是天然一致的。

聚集索引與非聚集索引的最大區(qū)別就在于：聚集索引是對原數(shù)據(jù)表進行排序，因此只要符合索引條件，就能夠直接連續(xù)的讀取數(shù)據(jù)記錄，幾乎可以達到對數(shù)據(jù)表的零掃描；而非聚集索引僅僅只是另外建了一張索引表，取數(shù)據(jù)的時候，從索引表取得結果后，還需要到指針所指的數(shù)據(jù)行讀取相應數(shù)據(jù)，因此，在性能上，聚集索引會大大優(yōu)于非聚集索引。
但是在一張表中，聚集索引只允許一個，是比較寶貴的，因此要盡可能的用于那些使用頻率最高的索引上。 另外，查詢時必需要用到索引的起始列，否則索引無效。另外，起始列也必需是使用頻繁的列，那樣的索引性能才會達到最優(yōu)化。

（二）表：何時應使用聚集索引或非聚集索引

動作描述 使用聚集索引 使用非聚集索引
列經(jīng)常被分組排序 ○ ○
返回某范圍內(nèi)的數(shù)據(jù) ○
一個或極少不同值
小數(shù)目的不同值 ○
大數(shù)目的不同值 ○
頻繁更新的列 ○
外鍵列 ○ ○
主鍵列 ○ ○
頻繁修改索引列 ○

（三）索引建立的一些注意項

1、不要把聚集索引浪費在主鍵上，除非你只按主鍵查詢
雖然SQL SERVER默認是在主鍵上建立聚集索引的，但實際應用中，這樣做比較浪費。通常，我們會在每個表中都建立一個ID列，以區(qū)分每條數(shù)據(jù)，并且這個ID列是自動增大的，步長一般為1。此時，如果我們將這個列設為主鍵，SQL SERVER會將此列默認為聚集索引。這樣做有好處，就是可以讓您的數(shù)據(jù)在數(shù)據(jù)庫中按照ID進行物理排序，但這樣做實用價值不大。
從我們前面談到的聚集索引的定義我們可以看出，使用聚集索引的最大好處就是能夠根據(jù)查詢要求，迅速縮小查詢范圍，避免全表掃描。在實際應用中，因為ID號是自動生成的，我們并不知道每條記錄的ID號，所以我們很難在實踐中用ID號來進行查詢。這就使讓ID號這個主鍵作為聚集索引成為一種資源浪費。聚集索引相對與非聚集索引的優(yōu)勢是很明顯的，而每個表中只能有一個聚集索引的規(guī)則，這使得聚集索引變得更加寶貴，應該用在其他查詢頻率高的字段上。其次，讓每個 ID號都不同的字段作為聚集索引也不符合“大數(shù)目的不同值情況下不應建立聚合索引”規(guī)則；當然，這種情況只是針對用戶經(jīng)常修改記錄內(nèi)容，特別是索引項的時候會負作用，但對于查詢速度并沒有影響。

2、索引的建立要根據(jù)實際應用需求來進行
并非是在任何字段上簡單地建立索引就能提高查詢速度。聚集索引建立的規(guī)則大致是“既不能絕大多數(shù)都相同，又不能只有極少數(shù)相同”。舉個例子，在公文表的收發(fā)日期字段上建立聚合索引是比較合適的。在政務系統(tǒng)中，我們每天都會收一些文件，這些文件的發(fā)文日期將會相同，在發(fā)文日期上建立聚合索引對性能的提升應該是相當大的。在群集索引下，數(shù)據(jù)物理上按順序存于數(shù)據(jù)頁上，重復值也排列在一起，因而在范圍查找時，可以先找到這個范圍的起末點，且只在這個范圍內(nèi)掃描數(shù)據(jù)頁，避免了大范圍掃描，提高了查詢速度。
另一個相反的例子：比如在雇員表的“性別”列上只有“男”與“女”兩個不同值，因此就完全沒必要建立索引。

3、在聚集索引中加入所有需要提高查詢速度的字段，形成復合索引
根據(jù)一些實驗的結果，我們可以得出一些可供參考的結論：
? 僅用復合聚集索引的起始列作為查詢條件和同時用到復合聚集索引的全部列的查詢，速度是幾乎一樣的，甚至比后者還要快（在查詢結果集數(shù)目一樣的情況下）；
? 僅用復合聚集索引的非起始列作為查詢條件的話，
這個索引是不起任何作用的。
? 復合聚集索引的所有列都用上，而且因為查詢條件嚴格，查詢結果少的話，會形成“索引覆蓋”，性能可以達到最優(yōu)。
? 最重要的一點：無論是否經(jīng)常使用復合聚合索引的其他列，其起始列一定要是使用最頻繁的列。

4.根據(jù)實踐得出的一些其他經(jīng)驗，特定情況下有效
? 用聚合索引比用不是聚合索引的主鍵速度快；
? 用聚合索引比用一般的主鍵作order by速度快，特別是在小數(shù)據(jù)量情況；
? 使用聚合索引內(nèi)的時間段，搜索時間會按數(shù)據(jù)占整個數(shù)據(jù)表的百分比成比例減少，而無論聚合索引使用了多少個；
? 日期列不會因為有分秒的輸入而減慢查詢速度；
? 由于改變一個表的內(nèi)容，將會引起索引的變化。頻繁的insert,update,delete語句將導致系統(tǒng)花費較大的代價進行索引更新，引起整體性能的下降。一般來講，在對查詢性能的要求高于對數(shù)據(jù)維護性能要求時，應該盡量使用索引，否則，就要慎重考慮一下付出的代價。在某些極端情況下，可先刪除索引，再對數(shù)據(jù)庫表更新大量數(shù)據(jù)，最后再重建索引，新建立的索引總是比較好用。

二、 編寫優(yōu)化的SQL語句，充分利用索引

下面就某些SQL語句的where子句編寫中需要注意的問題作詳細介紹。在這些where子句中，即使某些列存在索引，但是由于編寫了劣質(zhì)的SQL，系統(tǒng)在運行該SQL語句時也不能使用該索引，而同樣使用全表掃描，這就造成了響應速度的極大降低。
SQL語句在提交給數(shù)據(jù)庫進行操作前，都會經(jīng)過查詢分析階段，SQLSERVER內(nèi)置的查詢優(yōu)化器會分析查詢條件的的每個部分，并判斷這些條件是否符合掃描參數(shù)（SARG）的標準。只有當一個查詢條件符合SARG的標準，才可以通過預先設置的索引，提升查詢性能。
SARG的定義：用于限制搜索操作的一種規(guī)范，通常是指一個特定的匹配，一個確定范圍內(nèi)的匹配或者兩個以上條件的AND連接。一般形式如下：
列名 操作符 <常數(shù) 或 變量>
或
<常數(shù) 或 變量> 操作符 列名
列名可以出現(xiàn)在操作符的一邊，而常數(shù)或變量出現(xiàn)在操作符的另一邊。如：
Name=’張三’
價格>5000
5000<價格
Name=’張三’ and 價格>5000
如果一個表達式不能滿足SARG的形式，那它就無法限制搜索的范圍了，也就是說SQL SERVER必須對每一行都判斷它是否滿足WHERE子句中的所有條件，既進行全表掃描。所以，一個索引對于不滿足SARG形式的表達式來說是無用的, 如：當查詢條件為“價格*2 >5000”時，就無法利用建立在價格字段上的索引 。
SQLSERVER內(nèi)置了查詢優(yōu)化器，能將一些條件自動轉(zhuǎn)換為符合SARG標準，如：將“價格*2 >5000” 轉(zhuǎn)換為“價格 >2500/2 ”，以達到可以使用索引的目的，但這種轉(zhuǎn)化不是100%可靠的，有時會有語義上的損失，有時轉(zhuǎn)化不了。如果對“查詢優(yōu)化器”的工作原理不是特別了解，寫出的SQL語句可能不會按照您的本意進行查詢。所以不能完全依賴查詢優(yōu)化器的優(yōu)化，建議大家還是利用自己的優(yōu)化知識，盡可能顯式的書寫出符合SARG標準的 SQL語句，自行確定查詢條件的構建方式，這樣一方面有利于查詢分析器分析最佳索引匹配順序，另一方面也有利于今后重讀代碼。
介紹完SARG后，我們再結合一些實際運用中的例子來做進一步的講解：

1、 Like語句是否屬于SARG取決于使用%通配符的樣式

如：name like ‘張%’ ，這就屬于SARG
而：name like ‘%張’ ,就不屬于SARG
通配符%在字符串首字符的使用會導致索引無法使用，雖然實際應用中很難避免這樣用，但還是應該對這種現(xiàn)象有所了解，至少知道此種用法性能是很低下的。

2、 “非”操作符不滿足SARG形式，使得索引無法使用

不滿足SARG形式的語句最典型的情況就是包括非操作符的語句，如：NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE等。
下面是一個NOT子句的例子：
... where not (status ='valid')
not運算符也隱式的包含在另外一些邏輯運算符中，比如<>運算符。見下例：
... where status <>'invalid';

再看下面這個例子：
select * from employee where salary<>3000;
對這個查詢，可以改寫為不使用not：
select * from employee where salary<3000 or salary>3000;

雖然這兩種查詢的結果一樣，但是第二種查詢方案會比第一種查詢方案更快些。第二種查詢允許對salary列使用索引，而第一種查詢則不能使用索引。

3、 函數(shù)運算不滿足SARG形式，使得索引無法使用

例：下列SQL條件語句中的列都建有恰當?shù)乃饕?#xff0c;但執(zhí)行速度卻非常慢：
select * from record where substring(card_no,1,4)=′5378′(13秒)
select * from record where amount/30< 1000（11秒）
select * from record where convert(char(10),date,112)=′19991201′（10秒）
分析：
where子句中對列的任何操作結果都是在SQL運行時逐列計算得到的，因此它不得不進行全表掃描，而沒有使用該列上面的索引；如果這些結果在查詢編譯時就能得到，那么就可以被SQL優(yōu)化器優(yōu)化，使用索引，避免表搜索，因此將SQL重寫成下面這樣：
select * from record where card_no like ′5378%′（< 1秒）
select * from record where amount < 1000*30（< 1秒）
select * from record where date= ′1999/12/01′ （< 1秒）

你會發(fā)現(xiàn)SQL明顯快很多

4、 盡量不要對建立了索引的字段，作任何的直接處理

select * from employs where first_name + last_name ='beill cliton';
無法使用索引

改為：
select * from employee where
first_name = substr('beill cliton',1,instr('beill cliton',' ')-1)
and
last_name = substr('beill cliton',instr('beill cliton',' ')+1)
則可以使用索引

5、 不同類型的索引效能是不一樣的，應盡可能先使用效能高的

比如：數(shù)字類型的索引查找效率高于字符串類型，定長字符串char，nchar的索引效率高于變長字符串varchar,nvarchar的索引。
應該將
where username='張三' and age>20
改進為
where age>20 and username='張三'
注意：

此處，SQL的查詢分析優(yōu)化功能可以做到自動重排條件順序，但還是建議預先手工排列好。

6、 盡量不要使用 is null 與 is not null作為查詢條件

　　任何包含null值的列都將不會被包含在索引中，如果某列數(shù)據(jù)中存在空值，那么對該列建立索引的性能提升是值得懷疑的，尤其是將null作為查詢條件的一部分時。建議一方面避免使用is null和is not null, 另一方面不要讓數(shù)據(jù)庫字段中存在null, 即使沒有內(nèi)容，也應利用缺省值，或者手動的填入一個值，如:’’ 空字符串。

7、 某些情況下IN 的作用與OR 相當 ，且都不能充分利用索引

例：表stuff有200000行，id_no上有非群集索引，請看下面這個SQL：
select count(*) from stuff where id_no in(′0′,′1′) （23秒）

where條件中的′in′在邏輯上相當于′or′，所以語法分析器會將in (′0′,′1′)轉(zhuǎn)化為id_no =′0′ or id_no=′1′來執(zhí)行。我們期望它會根據(jù)每個or子句分別查找，再將結果相加，這樣可以利用id_no上的索引；但實際上,它卻采用了"OR策略"，即先取出滿足每個or子句的行，存入臨時數(shù)據(jù)庫的工作表中，再建立唯一索引以去掉重復行，最后從這個臨時表中計算結果。因此，實際過程沒有利用id_no 上索引，并且完成時間還要受tempdb數(shù)據(jù)庫性能的影響。
實踐證明，表的行數(shù)越多，工作表的性能就越差，當stuff有620000行時，執(zhí)行時間會非常長！如果確定不同的條件不會產(chǎn)生大量重復值，還不如將or子句分開：
select count(*) from stuff where id_no=′0′
select count(*) from stuff where id_no=′1′
得到兩個結果，再用union作一次加法合算。因為每句都使用了索引，執(zhí)行時間會比較短，
select count(*) from stuff where id_no=′0′
union
select count(*) from stuff where id_no=′1′
從實踐效果來看，使用union在通常情況下比用or的效率要高的多，而exist關鍵字和in關鍵字在用法上類似，性能上也類似，都會產(chǎn)生全表掃描，效率比較低下，根據(jù)未經(jīng)驗證的說法，exist可能比in要快些。

8、 使用變通的方法提高查詢效率

　　like關鍵字支持通配符匹配，但這種匹配特別耗時。例如：select * from customer where zipcode like “21_ _ _”，即使在zipcode字段上已建立了索引，在這種情況下也可能還是采用全表掃描方式。如果把語句改為：select * from customer where zipcode >“21000”，在執(zhí)行查詢時就會利用索引，大大提高速度。但這種變通是有限制的，不應引起業(yè)務意義上的損失，對于郵政編碼而言，zipcode like “21_ _ _” 和 zipcode >“21000” 意義是完全一致的。

9、 組合索引的高效使用

假設已在date，place，amount三個字段上建立了組合索引
select count(*) from record
where date > ′19991201′ and date < ′19991214′ and amount > 2000
(< 1秒)

select date,sum(amount) from record group by date
（11秒）

select count(*) from record
where date > ′19990901′ and place in (′BJ′,′SH′)
（< 1秒）
這是一個設置較合理的組合索引。它將date作為前導列，使每個SQL都可以利用索引，并且在第一和第三個SQL中形成了索引覆蓋，因而性能達到了最優(yōu)。如果索引不便于更改，修正SQL中的條件順序以配合索引順序也是可行的。

10、 order by按聚集索引列排序效率最高

排序是較耗時的操作，應盡量簡化或避免對大型表進行排序，如縮小排序的列的范圍，只在有索引的列上排序等等。
我們來看：（gid是主鍵，fariqi是聚合索引列）
select top 10000 gid,fariqi,reader,title from tgongwen
用時：196 毫秒。 掃描計數(shù) 1，邏輯讀 289 次，物理讀 1 次，預讀 1527 次。
select top 10000 gid,fariqi,reader,title from tgongwen order by gid asc
用時：4720毫秒。 掃描計數(shù) 1，邏輯讀 41956 次，物理讀 0 次，預讀 1287 次。
select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc
用時：4736毫秒。 掃描計數(shù) 1，邏輯讀 55350 次，物理讀 10 次，預讀 775 次。
select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi asc
用時：173毫秒。 掃描計數(shù) 1，邏輯讀 290 次，物理讀 0 次，預讀 0 次。
select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi desc
用時：156毫秒。 掃描計數(shù) 1，邏輯讀 289 次，物理讀 0 次，預讀 0 次。
從以上我們可以看出，不排序的速度以及邏輯讀次數(shù)都是和“order by 聚集索引列” 的速度是相當?shù)?#xff0c;但這些都比“order by 非聚集索引列”的查詢速度是快得多的。
同時，按照某個字段進行排序的時候，無論是正序還是倒序，速度是基本相當?shù)摹?/p>

三、 關于節(jié)省數(shù)據(jù)查詢系統(tǒng)開銷方面的措施

1、 使用TOP盡量減少取出的數(shù)據(jù)量

TOP是SQL SERVER中用來提取前幾條或前某個百分比數(shù)據(jù)的關鍵詞。
select top 20 gid,fariqi,reader,title from tgongwen order by gid desc
select top 60 percent gid,fariqi,reader,title from tgongwen order by gid desc
在實際的應用中，應該經(jīng)常利用top 剔除掉不必要的數(shù)據(jù)，只保留必須的數(shù)據(jù)集合。這樣不僅可以減少數(shù)據(jù)庫邏輯讀的次數(shù)，還能避免不必要的內(nèi)存浪費，對系統(tǒng)性能的提升都是有好處的。

2、 字段提取要按照“需多少、提多少”的原則，避免“select *”

這個舉個例子：
select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc
用時：4673毫秒
select top 10000 gid,fariqi,title from tgongwen order by gid desc
用時：1376毫秒
select top 10000 gid,fariqi from tgongwen order by gid desc
用時：80毫秒
由此看來，字段大小越大，數(shù)目越多，select所耗費的資源就越多，比如取int類型的字段就會比取char的快很多。我們每少提取一個字段，數(shù)據(jù)的提取速度就會有相應的提升。提升的幅度根據(jù)舍棄的字段的大小來判斷。

3、 count(*) 與 count(字段) 方法比較

我們來看一些實驗例子（gid為Tgongwen的主鍵）：
select count(*) from Tgongwen
用時：1500毫秒
select count(gid) from Tgongwen
用時：1483毫秒
select count(fariqi) from Tgongwen
用時：3140毫秒
select count(title) from Tgongwen
用時：52050毫秒
從以上可以看出，用count(*)和用count(主鍵)的速度是相當?shù)?#xff0c;而count(*)卻比其他任何除主鍵以外的字段匯總速度要快，而且字段越長，匯總速度就越慢。如果用count(*)， SQL SERVER會自動查找最小字段來匯總。當然，如果您直接寫count(主鍵)將會來的更直接些。

4、 有嵌套查詢時，盡可能在內(nèi)層過濾掉數(shù)據(jù)

如果一個列同時在主查詢和where子句中出現(xiàn)，很可能當主查詢中的列值改變之后，子查詢必須重新查詢一次。而且查詢嵌套層次越多，效率越低，因此應當盡量避免子查詢。如果子查詢不可避免，那么要在子查詢中過濾掉盡可能多的行。

5、 多表關聯(lián)查詢時，需注意表順序，并盡可能早的過濾掉數(shù)據(jù)

在使用Join進行多表關聯(lián)查詢時候，應該使用系統(tǒng)開銷最小的方案。連接條件要充份考慮帶有索引的表、行數(shù)多的表，并注意優(yōu)化表順序；說的簡單一點，就是盡可能早的將之后要做關聯(lián)的數(shù)據(jù)量降下來。
一般情況下，sqlserver 會對表的連接作出自動優(yōu)化。例如：
select name,no from A
join B on A. id=B.id
join C on C.id=A.id
where name='wang'
盡管A表在From中先列出，然后才是B,最后才是C。但sql server可能會首先使用c表。它的選擇原則是相對于該查詢限制為單行或少數(shù)幾行，就可以減少在其他表中查找的總數(shù)據(jù)量。絕大多數(shù)情況下，sql server 會作出最優(yōu)的選擇，但如果你發(fā)覺某個復雜的聯(lián)結查詢速度比預計的要慢，就可以使用SET FORCEPLAN語句強制sql server按照表出現(xiàn)順序使用表。如上例加上：SET FORCEPLAN ON…….SET FORCEPLAN OFF 表的執(zhí)行順序?qū)凑漳闼鶎懙捻樞驁?zhí)行。在查詢分析器中查看2種執(zhí)行效率，從而選擇表的連接順序。SET FORCEPLAN的缺點是只能在存儲過程中使用。

小結：

? 聚集索引比較寶貴，應該用在查詢頻率最高的地方；
? 在數(shù)據(jù)為“既不是絕大多數(shù)相同，也不是極少數(shù)相同”狀態(tài)時，
最能發(fā)揮聚集索引的潛力；
? 復合索引的設置和使用要注意保持順序一致；
? 條件子句的表達式最好符合SARG規(guī)范，是可利用索引的；
? 任何對列的操作都導致全表掃描，如數(shù)據(jù)庫函數(shù)、計算表達式等，
查詢時應盡可能將操作移至等號的某一邊；
? 要注意含有null值時，是不能充分利用索引的；
? exist, in、or等子句常會使索引失效；
如果不產(chǎn)生大量重復值，可以考慮把子句拆開，再用union拼合；
? 排序時應充分利用帶索引的字段；
? 盡可能早，快的過濾掉無用的數(shù)據(jù)，只將必須的數(shù)據(jù)帶到后續(xù)的操作中去
從前面講敘的內(nèi)容可以看出，SQL語句優(yōu)化的實質(zhì)就是在結果正確的前提下，用分析優(yōu)化器可以識別的SARG規(guī)范語句，充份利用索引，減少數(shù)據(jù)的I/O次數(shù)，盡量避免全表掃描的發(fā)生。
以上內(nèi)容有些是指導性的理論原則，有些是實際摸索的經(jīng)驗，大家在使用時應靈活處理，根據(jù)實際情況，選擇合適的方法。本文中列舉的實驗數(shù)據(jù)僅作比對用，不具備普遍意義。大家在實際項目中，應充分利用性能監(jiān)測和分析工具（如SQLSERVER帶的相關工具）來檢驗自己的優(yōu)化效果。
此外，還有很重要的一點要提醒大家，同樣復雜的數(shù)據(jù)操作，在SQLSERVER數(shù)據(jù)庫級別完成的代價要遠遠小于在應用端用程序代碼完成的代價，所以建議大家全面，深入的學習SQL語法中重要關鍵字的應用，如：Group By ，Having等，盡量把數(shù)據(jù)操作任務放在數(shù)據(jù)庫系統(tǒng)中完成。數(shù)據(jù)庫應用系統(tǒng)的性能優(yōu)化是一個復雜的過程，上述這些只是在SQL語句層次的一種體現(xiàn)，深入研究還會涉及數(shù)據(jù)庫層的資源配置、網(wǎng)絡層的流量控制以及操作系統(tǒng)層的總體設計等等，這些將在以后的文章中詳細論述。

總結

以上是生活随笔為你收集整理的常用SQL语句书写技巧-的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。