[轉載]讓SQL運行得更快

一、不合理的索引設計  
----例：表record有620000行，試看在不同的索引下，下面幾個 SQL的運行情況：  
---- 1.在date上建有一非個群集索引  
select count(*) from record where date >  
'19991201' and date < '19991214'and amount >  
2000 (25秒)  
select date,sum(amount) from record group by date  
(55秒)  
select count(*) from record where date >  
'19990901' and place in ('BJ','SH') (27秒)  
---- 分析：  
----date上有大量的重複值，在非群集索引下，資料在物理上隨機存放在資料頁上，在  
範圍尋找時，必須執行一次表掃描才能找到這一範圍內的全部行。  
---- 2.在date上的一個群集索引  
select count(*) from record where date >  
'19991201' and date < '19991214' and amount >  
2000 （14秒）  
select date,sum(amount) from record group by date  
（28秒）  
select count(*) from record where date >  
'19990901' and place in ('BJ','SH')（14秒）  
---- 分析：  
---- 在群集索引下，資料在物理上按順序在資料頁上，重複值也排列在一起，因而在範  
圍尋找時，可以先找到這個範圍的起末點，且只在這個範圍內掃描資料頁，避免了大範  
圍掃描，提高了查詢速度。  
---- 3.在place，date，amount上的複合式索引  
select count(*) from record where date >  
'19991201' and date < '19991214' and amount >  
2000 （26秒）  
select date,sum(amount) from record group by date  
（27秒）  
select count(*) from record where date >  
'19990901' and place in ('BJ', 'SH')（< 1秒）  
---- 分析：  
---- 這是一個不很合理的複合式索引，因為它的前置列是place，第一和第二條SQL沒有引  
用place，因此也沒有利用上索引；第三個SQL使用了place，且引用的所有列都包含在組  
合索引中，形成了索引覆蓋，所以它的速度是非常快的。  
---- 4.在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中形成了索引覆蓋，因而效能達到了最優。  
---- 5.總結：  
---- 預設情況下建立的索引是非群集索引，但有時它並不是最佳的；合理的索引設計要  
建立在對各種查詢的分析和預測上。一般來說：  
---- ①.有大量重複值、且經常有範圍查詢  
（between, >,< ，>=,< =）和order by  
、group by發生的列，可考慮建立群集索引；  
---- ②.經常同時存取多列，且每列都含有重複值可考慮建立複合式索引；  
---- ③.複合式索引要盡量使關鍵查詢形成索引覆蓋，其前置列一定是使用最頻繁的列。 二、不充份的串連條件：  
---- 例：表card有7896行，在card_no上有一個非叢集索引，表account有191122行，在  
account_no上有一個非叢集索引，試看在不同的表串連條件下，兩個SQL的執行情況： select sum(a.amount) from account a,  
card b where a.card_no = b.card_no（20秒）  
---- 將SQL改為：  
select sum(a.amount) from account a,  
card b where a.card_no = b.card_no and a.  
account_no=b.account_no（< 1秒）  
---- 分析：  
---- 在第一個串連條件下，最佳查詢方案是將account作外層表，card作內層表，利用  
card上的索引，其I/O次數可由以下公式估算為：  
---- 外層表account上的22541頁+（外層表account的191122行*內層表card上對應外層  
表第一行所要尋找的3頁）=595907次I/O  
---- 在第二個串連條件下，最佳查詢方案是將card作外層表，account作內層表，利用  
account上的索引，其I/O次數可由以下公式估算為：  
---- 外層表card上的1944頁+（外層表card的7896行*內層表account上對應外層表每一  
行所要尋找的4頁）= 33528次I/O  
---- 可見，只有充份的串連條件，真正的最佳方案才會被執行。  
---- 總結：  
---- 1.多表操作在被實際執行前，查詢最佳化工具會根據串連條件，列出幾組可能的串連方  
案並從中找出系統開銷最小的最佳方案。串連條件要充份考慮帶有索引的表、行數多的  
表；內外表的選擇可由公式：外層表中的匹配行數*內層表中每一次尋找的次數確定，乘  
積最小為最佳方案。  
---- 2.查看執行方案的方法-- 用set showplanon，開啟showplan選項，就可以看到連  
接順序、使用何種索引的資訊；想看更詳細的資訊，需用sa角色執行dbcc(3604,310,30  
2)。  
三、不可最佳化的where子句  
---- 1.例：下列SQL條件陳述式中的列都建有恰當的索引，但執行速度卻非常慢：  
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最佳化器最佳化，使用索引，避免表搜尋，因此將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秒）  
---- 你會發現SQL明顯快起來！  
---- 2.例：表stuff有200000行，id_no上有非群集索引，請看下面這個SQL：  
select count(*) from stuff where id_no in('0','1')  
（23秒）  
---- 分析：  
---- where條件中的'in'在邏輯上相當於'or'，所以文法分析器會將in ('0','1')轉化  
為id_no ='0' or id_no='1'來執行。我們期望它會根據每個or子句分別尋找，再將結果  
相加，這樣可以利用id_no上的索引；但實際上（根據showplan）,它卻採用了"OR策略"  
，即先取出滿足每個or子句的行，存入臨時資料庫的工作表中，再建立唯一索引以去掉  
重複行，最後從這個暫存資料表中計算結果。因此，實際過程沒有利用id_no上索引，並且完  
成時間還要受tempdb資料庫效能的影響。  
---- 實踐證明，表的行數越多，工作表的效能就越差，當stuff有620000行時，執行時  
間竟達到220秒！還不如將or子句分開：  
select count(*) from stuff where id_no='0'  
select count(*) from stuff where id_no='1'  
---- 得到兩個結果，再作一次加法合算。因為每句都使用了索引，執行時間只有3秒，  
在620000行下，時間也只有4秒。或者，用更好的方法，寫一個簡單的預存程序：  
create proc count_stuff as  
declare @a int  
declare @b int  
declare @c int  
declare @d char(10)  
begin  
select @a=count(*) from stuff where id_no='0'  
select @b=count(*) from stuff where id_no='1'  
end  
select @c=@a+@b  
select @d=convert(char(10),@c)  
print @d  
---- 直接算出結果，執行時間同上面一樣快！  
---- 總結：  
---- 可見，所謂最佳化即where子句利用了索引，不可最佳化即發生了表掃描或額外開銷。 ---- 1.任何對列的操作都將導致表掃描，它包括資料庫函數、計算運算式等等，查詢時  
要儘可能將操作移至等號右邊。  
---- 2.in、or子句常會使用工作表，使索引失效；如果不產生大量重複值，可以考慮把  
子句拆開；拆開的子句中應該包含索引。  
---- 3.要善於使用預存程序，它使SQL變得更加靈活和高效。  
---- 從以上這些例子可以看出，SQL最佳化的實質就是在結果正確的前提下，用最佳化器可  
以識別的語句，充份利用索引，減少表掃描的I/O次數，盡量避免表搜尋的發生。其實S  
QL的效能最佳化是一個複雜的過程，上述這些只是在應用程式層次的一種體現，深入研究還會  
涉及資料庫層的資源配置、網路層的流量控制以及作業系統層的總體設計。  
1．合理使用索引   
索引是資料庫中重要的資料結構，它的根本目的就是為了提高查詢效率。現在大多數的資料庫產品都採用IBM最先提出的ISAM索引結構。索引的使用要恰到好處，其使用原則如下：   
●在經常進行串連，但是沒有指定為外鍵的列上建立索引，而不經常串連的欄位則由最佳化器自動產生索引。   
●在頻繁進行排序或分組（即進行group by或order by操作）的列上建立索引。   
●在條件運算式中經常用到的不同值較多的列上建立檢索，在不同值少的列上不要建立索引。比如在僱員表的“性別”列上只有“男”與“女”兩個不同值，因此就無必要建立索引。如果建立索引不但不會提高查詢效率，反而會嚴重降低更新速度。   
●如果待排序的列有多個，可以在這些列上建立複合索引（compound index）。   
●使用系統工具。如Informix資料庫有一個tbcheck工具，可以在可疑的索引上進行檢查。在一些資料庫伺服器上，索引可能失效或者因為頻繁操作而使得讀取效率降低，如果一個使用索引的查詢不明不白地慢下來，可以試著用tbcheck工具檢查索引的完整性，必要時進行修複。另外，當資料庫表更新大量資料後，刪除並重建索引可以提高查詢速度。 2．避免或簡化排序   
應當簡化或避免對大型表進行重複的排序。當能夠利用索引自動以適當的次序產生輸出時，最佳化器就避免了排序的步驟。以下是一些影響因素：   
●索引中不包括一個或幾個待排序的列；   
●group by或order by子句中列的次序與索引的次序不一樣；   
●排序的列來自不同的表。   
為了避免不必要的排序，就要正確地增建索引，合理地合并資料庫表（儘管有時可能影響表的正常化，但相對於效率的提高是值得的）。如果排序不可避免，那麼應當試圖簡化它，如縮小排序的列的範圍等。 3．消除對大型表行資料的順序存取   
在巢狀查詢中，對錶的順序存取對查詢效率可能產生致命的影響。比如採用順序存取策略，一個嵌套3層的查詢，如果每層都查詢1000行，那麼這個查詢就要查詢10億行資料。避免這種情況的主要方法就是對串連的列進行索引。例如，兩個表：學生表（學號、姓名、年齡……）和選課表（學號、課程號、成績）。如果兩個表要做串連，就要在“學號”這個串連欄位上建立索引。   
還可以使用並集來避免順序存取。儘管在所有的檢查列上都有索引，但某些形式的where子句強迫最佳化器使用順序存取。下面的查詢將強迫對orders表執行順序操作：   
Select ＊ FROM orders Where (customer_num=104 AND order_num>1001) or order_num=1008   
雖然在customer_num和order_num上建有索引，但是在上面的語句中最佳化器還是使用順序存取路徑掃描整個表。因為這個語句要檢索的是分離的行的集合，所以應該改為如下語句：   
Select ＊ FROM orders Where customer_num=104 AND order_num>1001   
UNION   
Select ＊ FROM orders Where order_num=1008   
這樣就能利用索引路徑處理查詢。 4．避免相互關聯的子查詢   
一個列的標籤同時在主查詢和where子句中的查詢中出現，那麼很可能當主查詢中的列值改變之後，子查詢必須重新查詢一次。查詢嵌套層次越多，效率越低，因此應當盡量避免子查詢。如果子查詢不可避免，那麼要在子查詢中過濾掉儘可能多的行。 5．避免困難的正規運算式   
MATCHES和LIKE關鍵字支援萬用字元匹配，技術上叫正規運算式。但這種匹配特別耗費時間。例如：Select ＊ FROM customer Where zipcode LIKE “98_ _ _”   
即使在zipcode欄位上建立了索引，在這種情況下也還是採用順序掃描的方式。如果把語句改為Select ＊ FROM customer Where zipcode >“98000”，在執行查詢時就會利用索引來查詢，顯然會大大提高速度。   
另外，還要避免非開始的子串。例如語句：Select ＊ FROM customer Where zipcode[2，3] >“80”，在where子句中採用了非開始子串，因而這個語句也不會使用索引。 6．使用暫存資料表加速查詢   
把表的一個子集進行排序並建立暫存資料表，有時能加速查詢。它有助於避免多重排序操作，而且在其他方面還能簡化最佳化器的工作。例如：   
Select cust.name，rcvbles.balance，……other columns   
FROM cust，rcvbles   
Where cust.customer_id = rcvlbes.customer_id   
AND rcvblls.balance>0   
AND cust.postcode>“98000”   
orDER BY cust.name   
如果這個查詢要被執行多次而不止一次，可以把所有未付款的客戶找出來放在一個臨時檔案中，並按客戶的名字進行排序：   
Select cust.name，rcvbles.balance，……other columns   
FROM cust，rcvbles   
Where cust.customer_id = rcvlbes.customer_id   
AND rcvblls.balance>0   
orDER BY cust.name   
INTO TEMP cust_with_balance   
然後以下面的方式在暫存資料表中查詢：   
Select ＊ FROM cust_with_balance   
Where postcode>“98000”   
暫存資料表中的行要比主表中的行少，而且物理順序就是所要求的順序，減少了磁碟I/O，所以查詢工作量可以得到大幅減少。   
注意：暫存資料表建立後不會反映主表的修改。在主表中資料頻繁修改的情況下，注意不要遺失資料。 7．用排序來取代非順序存取   
非順序磁碟存取是最慢的操作，表現在磁碟存取臂的來回移動。SQL語句隱藏了這一情況，使得我們在寫應用程式時很容易寫出要求存取大量非順序頁的查詢。   
有些時候，用資料庫的排序能力來替代非順序的存取能改進查詢。 3.最佳化 tempdb 效能   
對 tempdb 資料庫的物理位置和資料庫選項設定的一般建議包括：   
使 tempdb 資料庫得以按需自動擴充。這確保在執行完成前不終止查詢，該查詢所產生的儲存在 tempdb 資料庫內的中間結果集比預期大得多。將 tempdb 資料庫檔案的初始大小設定為合理的大小，以避免當需要更多空間時檔案自動擴充。如果 tempdb 資料庫擴充得過於頻繁，效能會受不良影響。將檔案增長增量百分比設定為合理的大小，以避免 tempdb 資料庫檔案按太小的值增長。如果檔案增長幅度與寫入 tempdb 資料庫的資料量相比太小，則 tempdb 資料庫可能需要始終擴充，因而將妨害效能。將 tempdb 資料庫放在快速 I/O 子系統上以確保好的效能。在多個磁碟上條帶化 tempdb 資料庫以獲得更好的效能。將 tempdb 資料庫放在除使用者資料庫所使用的磁碟之外的磁碟上。有關更多資訊，請參見擴充資料庫。  
4.最佳化伺服器: 使用記憶體配置選項最佳化伺服器效能  
Microsoft® SQL Server™ 2000 的記憶體管理組件消除了對 SQL Server 可用的記憶體進行手工管理的需要。SQL Server 在啟動時根據作業系統和其它應用程式當前正在使用的記憶體量，動態確定應分配的記憶體量。當電腦和SQL Server 上的負荷更改時，分配的記憶體也隨之更改。有關更多資訊，請參見記憶體構架。下列伺服器配置選項可用於配置記憶體使用量並影響伺服器效能：   
min server memory  
max server memory  
max worker threads  
index create memory min memory per query   
min server memory 伺服器配置選項可用於確保 SQL Server 在達到該值後不會釋放記憶體。可以基於 SQL Server 的大小及活動將該配置選項設定為特定的值。如果選擇設定此選項，必須為作業系統和其他程式留出足夠的記憶體。如果作業系統沒有足夠的記憶體，會向 SQL Server 請求記憶體，從而導致影響 SQL Server 效能。 max server memory 伺服器配置選項可用於：在 SQL Server 啟動及運行時，指定 SQL Server 可以分配的最大記憶體量。如果知道有多個應用程式與 SQL Server 同時運行，而且想保障這些應用程式有足夠的記憶體運行，可以將該配置選項設定為特定的值。如果這些其它應用程式（如 Web 伺服器或電子郵件伺服器）只根據需要請求記憶體，則 SQL Server 將根據需要給它們釋放記憶體，因此不要設定 max server memory 伺服器配置選項。然而，應用程式通常在啟動時不假選擇地使用可用記憶體，而如果需要更多記憶體也不請求。如果有這種行為方式的應用程式與 SQL Server 同時運行在相同的電腦上，則將 max server memory 伺服器配置選項設定為特定的值，以保障應用程式所需的記憶體不由 SQL Server 分配出。  
不要將 min server memory 和 max server memory 伺服器配置選項設定為相同的值，這樣做會使分配給 SQL Server 的記憶體量固定。動態記憶體分配可以隨時間提供最佳的總體效能。有關更多資訊，請參見伺服器記憶體選項。 max worker threads 伺服器配置選項可用於指定為使用者串連到 SQL Server 提供支援的線程數。255 這一預設設定對一些配置可能稍微偏高，這要具體取決於並發使用者數。由於每個背景工作執行緒都已指派，因此即使線程沒有正在使用（因為並發串連比分配的背景工作執行緒少），可由其它操作（如高速緩衝儲存空間）更好地利用的記憶體資源也可能是未使用的。一般情況下，應將該配置值設定為並發串連數，但不能超過 32727。並發串連與使用者登入串連不同。SQL Server 執行個體的背景工作執行緒池只需要足夠大，以便為同時正在該執行個體中執行批處理的使用者串連提供服務。如果增加背景工作執行緒的數量超過預設值，會降低伺服器效能。有關更多資訊，請參見max worker threads 選項。  
說明  當 SQL Server 運行在 Microsoft Windows® 98 上時，最大背景工作執行緒伺服器配置選項不起作用。 index create memory 伺服器配置選項控制建立索引時排序操作所使用的記憶體量。在生產系統上建立索引通常是不常執行的任務，通常調度為在非峰值時間執行的作業。因此，不常建立索引且在非峰值時間時，增加該值可提高索引建立的效能。不過，最好將 min memory per query 配置選項保持在一個較低的值，這樣即使所有請求的記憶體都不可用，索引建立作業仍能開始。有關更多資訊，請參見 index create memory 選項。  
min memory per query 伺服器配置選項可用於指定分配給查詢執行的最小記憶體量。當系統內有許多查詢並發執行時，增大 min memory per query 的值有助於提高消耗大量記憶體的查詢（如大型排序和雜湊操作）的效能。不過，不要將 min memory per query 伺服器配置選項設定得太高，尤其是在很忙的系統上，因為查詢將不得不等到能確保佔有請求的最小記憶體、或等到超過 query wait 伺服器配置選項內所指定的值。如果可用記憶體比執行查詢所需的指定最小記憶體多，則只要查詢能對多出的記憶體加以有效利用，就可以使用多出的記憶體。有關更多資訊，請參見 min memory per query 選項和 query wait 選項。使用 I/O 配置選項最佳化伺服器效能  
下列伺服器配置選項可用於配置 I/O 的使用並影響伺服器效能： recovery interval   
recovery interval 伺服器配置選項控制 Microsoft® SQL Server™ 2000 在每個資料庫內發出檢查點的時間。預設情況下，SQL Server 確定執行檢查點操作的最佳時間。然而，若要確定這是否為適當的設定，需要使用 Windows NT 效能監控器監視資料庫檔案上的磁碟寫入活動。導致磁碟利用率達到 100% 的活動尖峰值會妨害效能。若更改該參數以使檢查點進程較少出現，通常可以提高這種情況下的總體效能。但仍須繼續監視效能以確定新值是否已對效能產生正面影響。有關更多資訊，請參見recovery interval 選項。