sql 利用索引最佳化效能

     不良的sql往往來自於不恰當的索引設計、不充份的串連條件和不可最佳化的where子句。在對
它們進行適當的最佳化後，其運行速度有了明顯地提高！下面我將從這三個方面分別進行總結：

　　為了更直觀地說明問題，所有執行個體中的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,302)。

　

　　三、不可最佳化的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變得更加靈活和高效。

 

 

• 索引中的資料儘可能少，即窄索引更容易被選擇；
• 叢集索引碼要被包含在表的所有的非叢集索引中，所以叢集索引碼要儘可能短；
• 建立高選擇性的非叢集索引；
• 頻繁請求的列上不能建立叢集索引，應該建立非叢集索引，並且要儘可能使值惟一；
• 儘可能減少熱點資料。如果頻繁地對錶中的某些資料進行讀和寫，這些資料就是熱點資料，要想辦法將熱點資料分散；
• 監控磁碟的資料流量。如果利用率太高，就要考慮索引列並在多個磁碟上分布資料以減少I/O；
• 在至少有一個索引的表中，應該有一個叢集索引。包括的不同值的個數有限，返回一定範圍內值的列，查詢時返回大量結果的列考慮建立叢集索引；
• 分析經常使用的SQL語句的Where子句，得出經常取值的資料，考慮對這些資料列根據常見的查詢類型建立索引；
• 主碼如果涉及多個資料列，要將顯著變化的資料列放在首位。如果資料列的變化程度相當，將經常訪問的資料列放在首位；
• 有大量重複值、且經常有範圍查詢。如（between，>,<,>=,<=）、order by、group by發生的列，可考慮建立叢集索引；
• SQL查詢語句同時存取多列的資料，且每列都含有重複值，可以考慮建立覆蓋索引，覆蓋索引要盡量使關鍵查詢形成索引覆蓋，其前置列一定是使用最頻繁的列；
• 索引值較短的索引具有比較高的效率，因為每個索引頁上能存放較多的索引行，而且索引的層級也比較少。所以，緩衝中能防止更多的索引列，這樣也減少了I/O操作；
• 表上的索引過多會影響UPDATE、INSERT和DELETE的效能，因為所有的索引必須做響應的調整。另外，所有的分頁操作都被記錄在日誌中，這樣也會增加I/O操作；
• 一般不對經常被更新的列建立叢集索引，這樣會引起整行的移動，嚴重影響效能；
• 查詢很少或著資料很少的資料表一般不用建立索引；
• 與ORDER BY或GROUP BY一起使用的列一般使用建立叢集索引。如果ORDER BY 命令中用到的列上有叢集索引，就不會產生1個暫存資料表，因為行已經排序。GROUP BY命令則一定產生1個暫存資料表；
• 當有大量的行正在被插入表中時，要避免在本表一個自然增長（例如Identity列）的列上建立叢集索引。如果建立了叢集索引，那麼INSERT的效能就會大大降低，因為每個插入的行必須到表的最後一個資料頁面。

 

　　從以上這些例子可以看出，sql最佳化的實質就是在結果正確的前提下，用最佳化器可以識別的語句，充份利用索引，減少表掃描的i/o次數，盡量避免表搜尋的發生。其實sql的效能最佳化是一個複雜的過程，上述這些只是在應用程式層次的一種體現，深入研究還會涉及資料庫層的資源配置、網路層的流量控制以及作業系統層的總體設計。