如何讓你的SQL運行得更快    
  ----   人們在使用SQL時往往會陷入一個誤區，即太關注于所得的結果是否正確，而忽略  
  了不同的實現方法之間可能存在的性能差異，這種性能差異在大型的或是復雜的數據庫  
  環境中（如聯機事務處理OLTP或決策支持系統DSS）中表現得尤為明顯。筆者在工作實踐  
  中發現，不良的SQL往往來自于不恰當的索引設計、不充份的連接條件和不可優化的whe  
  re子句。在對它們進行適當的優化后，其運行速度有了明顯地提高！下面我將從這三個  
  方面分別進行總結：  
  ----   為了更直觀地說明問題，所有實例中的SQL運行時間均經過測試，不超過１秒的均  
  表示為（<   1秒）。  
  ----   測試環境--  
  ----   主機：HP   LH   II  
  ----   主頻：330MHZ  
  ----   內存：128兆  
  ----   操作系統：Operserver5.0.4  
  ----數據庫：Sybase11.0.3  
  一、不合理的索引設計  
  ----例：表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語句隱藏了這一情況，使得我們在寫應用程序時很容易寫出要求存取大量非順序頁的查詢。    
  有些時候，用數據庫的排序能力來替代非順序的存取能改進查詢。   

   4.優化服務器:  
   
  使用內存配置選項優化服務器性能  
  Microsoft&reg;   SQL   Server&#8482;   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&reg;   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&reg;   SQL   Server&#8482;   2000   在每個數據庫內發出檢查點的時間。默認情況下，SQL   Server   確定執行檢查點操作的最佳時間。然而，若要確定這是否為適當的設置，需要使用   Windows   NT   性能監視器監視數據庫文件上的磁盤寫入活動。導致磁盤利用率達到   100%   的活動尖峰值會妨害性能。若更改該參數以使檢查點進程較少出現，通常可以提高這種情況下的總體性能。但仍須繼續監視性能以確定新值是否已對性能產生正面影響。有關更多信息，請參見recovery   interval   選項。  

   
  對   tempdb   數據庫的物理位置和數據庫選項設置的一般建議包括：    
  使   tempdb   數據庫得以按需自動擴展。這確保在執行完成前不終止查詢，該查詢所生成的存儲在   tempdb   數據庫內的中間結果集比預期大得多。  
   
  將   tempdb   數據庫文件的初始大小設置為合理的大小，以避免當需要更多空間時文件自動擴展。如果   tempdb   數據庫擴展得過于頻繁，性能會受不良影響。  
   
  將文件增長增量百分比設置為合理的大小，以避免   tempdb   數據庫文件按太小的值增長。如果文件增長幅度與寫入   tempdb   數據庫的數據量相比太小，則   tempdb   數據庫可能需要始終擴展，因而將妨害性能。  
   
  將   tempdb   數據庫放在快速   I/O   子系統上以確保好的性能。在多個磁盤上條帶化   tempdb   數據庫以獲得更好的性能。將   tempdb   數據庫放在除用戶數據庫所使用的磁盤之外的磁盤上。有關更多信息，請參見擴充數據庫。

zjcxc（鄒建） http://topic.csdn.net/t/20040420/13/2987172.html