關於數據庫優化問題收集匯總

筆者在工作實踐中發現不良的SQL往往來自於不恰當的索引設計不充份的連接條件和不可優化的where子句以下就對數據庫優化問題進行了介紹需要的朋友可以參考下  

　　人們在使用SQL時往往會陷入一個誤區即太關注於所得的結果是否正確而忽略了不同的實現方法之間可能存在的性能差異這種性能差異在大型的或是復雜的數據庫環境中（如聯機事務處理OLTP或決策支持系統DSS）中表現得尤為明顯

筆者在工作實踐中發現不良的SQL往往來自於不恰當的索引設計不充份的連接條件和不可優化的where子句

在對它們進行適當的優化後其運行速度有了明顯地提高！ 下面將從這三個方面分別進行總結 為了更直觀地說明問題所有實例中的SQL運行時間均經過測試不超過１秒的均表示為（< 秒） 測試環境: 主機HP LH II 主頻MHZ 內存兆 操作系統Operserver 數據庫Sybase   一不合理的索引設計 例表record有行試看在不同的索引下下面幾個 SQL的運行情況 在date上建有一非個群集索引 select count(*) from record where date > and date < and amount > (秒) select date sum(amount) from record group by date(秒) select count(*) from record where date > and place in (BJSH) (秒)  分析 date上有大量的重復值在非群集索引下數據在物理上隨機存放在數據頁上在范圍查找時必須執行一次表掃描才能找到這一范圍內的全部行 在date上的一個群集索引 select count(*) from record where date > and date < and amount > （秒） select datesum(amount) from record group by date（秒） select count(*) from record where date > and place in (BJSH)（秒）  分析 在群集索引下數據在物理上按順序在數據頁上重復值也排列在一起因而在范圍查找時可以先找到這個范圍的起末點且只在這個范圍內掃描數據頁避免了大范圍掃描提高了查詢速度 在placedateamount上的組合索引 select count(*) from record where date > and date < and amount > （秒） select datesum(amount) from record group by date（秒） select count(*) from record where date > and place in (BJ SH)（< 秒）  分析 這是一個不很合理的組合索引因為它的前導列是place第一和第二條SQL沒有引用place因此也沒有利用上索引第三個SQL使用了place且引用的所有列都包含在組合索引中形成了索引覆蓋所以它的速度是非常快的 在dateplaceamount上的組合索引 select count(*) from record where date > and date < and amount >(< 秒) select datesum(amount) from record group by date（秒） select count(*) from record where date > and place in (BJSH)（< 秒）  分析 這是一個合理的組合索引它將date作為前導列使每個SQL都可以利用索引並且在第一和第三個SQL中形成了索引覆蓋因而性能達到了最優 總結 缺省情況下建立的索引是非群集索引但有時它並不是最佳的合理的索引設計要建立在對各種查詢的分析和預測上 一般來說 ①有大量重復值且經常有范圍查詢（between >< >=< =）和order bygroup by發生的列可考慮建立群集索引 ②經常同時存取多列且每列都含有重復值可考慮建立組合索引 ③組合索引要盡量使關鍵查詢形成索引覆蓋其前導列一定是使用最頻繁的列   二不充份的連接條件 例表card有行在card_no上有一個非聚集索引表account有行在account_no上有一個非聚集索引試看在不同的表連接條件下兩個SQL的執行情況 select sum(aamount) from account acard b where acard_no = bcard_no（秒） select sum(aamount) from account acard b where acard_no = bcard_no and aaccount_no=baccount_no（< 秒）  分析 在第一個連接條件下最佳查詢方案是將account作外層表card作內層表利用card上的索引其I/O次數可由以下公式估算為 外層表account上的頁+（外層表account的行*內層表card上對應外層表第一行所要查找的頁）=次I/O 在第二個連接條件下最佳查詢方案是將card作外層表account作內層表利用account上的索引其I/O次數可由以下公式估算為外層表card上的頁+（外層表card的行*內層表account上對應外層表每一行所要查找的頁）= 次I/O 可見只有充份的連接條件真正的最佳方案才會被執行 總結 多表操作在被實際執行前查詢優化器會根據連接條件列出幾組可能的連接方案並從中找出系統開銷最小的最佳方案連接條件要充份考慮帶有索引的表行數多的表內外表的選擇可由公式外層表中的匹配行數*內層表中每一次查找的次數確定乘積最小為最佳方案 查看執行方案的方法 用set showplanon打開showplan選項就可以看到連接順序使用何種索引的信息想看更詳細的信息需用sa角色執行dbcc()   三不可優化的where子句 例下列SQL條件語句中的列都建有恰當的索引但執行速度卻非常慢 select * from record wheresubstring(card_no)=(秒) select * from record whereamount/< （秒） select * from record whereconvert(char()date)=（秒） 分析 where子句中對列的任何操作結果都是在SQL運行時逐列計算得到的因此它不得不進行表搜索而沒有使用該列上面的索引 如果這些結果在查詢編譯時就能得到那麼就可以被SQL優化器優化使用索引避免表搜索因此將SQL重寫成下面這樣 select * from record where card_no like%（< 秒） select * from record where amount< *（< 秒） select * from record where date= //（< 秒） 你會發現SQL明顯快起來！ 例表stuff有行id_no上有非群集索引請看下面這個SQL select count(*) from stuff where id_no in()（秒） 分析 where條件中的in在邏輯上相當於or所以語法分析器會將in ()轉化為id_no = or id_no=來執行 我們期望它會根據每個or子句分別查找再將結果相加這樣可以利用id_no上的索引 但實際上（根據showplan）它卻采用了"OR策略"即先取出滿足每個or子句的行存入臨時數據庫的工作表中再建立唯一索引以去掉重復行最後從這個臨時表中計算結果因此實際過程沒有利用id_no上索引並且完成時間還要受tempdb數據庫性能的影響 實踐證明表的行數越多工作表的性能就越差當stuff有行時執行時間竟達到秒！還不如將or子句分開 select count(*) from stuff where id_no=select count(*) from stuff where id_no= 得到兩個結果再作一次加法合算因為每句都使用了索引執行時間只有秒在行下時間也只有秒 或者用更好的方法寫一個簡單的存儲過程 create proc count_stuff asdeclare @a intdeclare @b intdeclare @c intdeclare @d char()beginselect @a=count(*) from stuff where id_no=select @b=count(*) from stuff where id_no=endselect @c=@a+@bselect @d=convert(char()@c)print @d 直接算出結果執行時間同上面一樣快！    總結 
可見所謂優化即where子句利用了索引不可優化即發生了表掃描或額外開銷 任何對列的操作都將導致表掃描它包括數據庫函數計算表達式等等查詢時要盡可能將操作移至等號右邊 inor子句常會使用工作表使索引失效如果不產生大量重復值可以考慮把子句拆開拆開的子句中應該包含索引 要善於使用存儲過程它使SQL變得更加靈活和高效 從以上這些例子可以看出SQL優化的實質就是在結果正確的前提下用優化器可以識別的語句充份利用索引減少表掃描的I/O次數盡量避免表搜索的發生其實SQL的性能優化是一個復雜的過程上述這些只是在應用層次的一種體現深入研究還會涉及數據庫層的資源配置網絡層的流量控制以及操作系統層的總體設計   開發人員如果用到其他庫的Table或View務必在當前庫中建立View來實現跨庫操作最好不要直接使用“databsedbotable_name”因為sp_depends不能顯示出該SP所使用的跨庫table或view不方便校驗

開發人員在提交SP前必須已經使用set showplan on分析過查詢計劃做過自身的查詢優化檢查

高程序運行效率優化應用程序在SP編寫過程中應該注意以下幾點

a) SQL的使用規范

i 盡量避免大事務操作慎用holdlock子句提高系統並發能力

ii 盡量避免反復訪問同一張或幾張表尤其是數據量較大的表可以考慮先根據條件提取數據到臨時表中然後再做連接

iii 盡量避免使用游標因為游標的效率較差如果游標操作的數據超過萬行那麼就應該改寫如果使用了游標就要盡量避免在游標循環中再進行表連接的操作

iv 注意where字句寫法必須考慮語句順序應該根據索引順序范圍大小來確定條件子句的前後順序盡可能的讓字段順序與索引順序相一致范圍從大到小

v 不要在where子句中的“=”左邊進行函數算術運算或其他表達式運算否則系統將可能無法正確使用索引

vi 盡量使用exists代替select count()來判斷是否存在記錄count函數只有在統計表中所有行數時使用而且count()比count(*)更有效率

vii 盡量使用“>=”不要使用“>”

viii 注意一些or子句和union子句之間的替換

ix 注意表之間連接的數據類型避免不同類型數據之間的連接

x 注意存儲過程中參數和數據類型的關系

xi 注意insertupdate操作的數據量防止與其他應用沖突如果數據量超過個數據頁面（k）那麼系統將會進行鎖升級頁級鎖會升級成表級鎖

b) 索引的使用規范

i 索引的創建要與應用結合考慮建議大的OLTP表不要超過個索引

ii 盡可能的使用索引字段作為查詢條件尤其是聚簇索引必要時可以通過index index_name來強制指定索引

iii 避免對大表查詢時進行table scan必要時考慮新建索引

iv 在使用索引字段作為條件時如果該索引是聯合索引那麼必須使用到該索引中的第一個字段作為條件時才能保證系統使用該索引否則該索引將不會被使用

v 要注意索引的維護周期性重建索引重新編譯存儲過程

c) tempdb的使用規范

i 盡量避免使用distinctorder bygroup byhavingjoin***pute因為這些語句會加重tempdb的負擔

ii 避免頻繁創建和刪除臨時表減少系統表資源的消耗

iii 在新建臨時表時如果一次性插入數據量很大那麼可以使用select into代替create table避免log提高速度如果數據量不大為了緩和系統表的資源建議先create table然後insert

iv 如果臨時表的數據量較大需要建立索引那麼應該將創建臨時表和建立索引的過程放在單獨一個子存儲過程中這樣才能保證系統能夠很好的使用到該臨時表的索引

v 如果使用到了臨時表在存儲過程的最後務必將所有的臨時表顯式刪除先truncate table然後drop table這樣可以避免系統表的較長時間鎖定

vi 慎用大的臨時表與其他大表的連接查詢和修改減低系統表負擔因為這種操作會在一條語句中多次使用tempdb的系統表

d) 合理的算法使用   根據上面已提到的SQL優化技術和ASE Tuning手冊中的SQL優化內容結合實際應用采用多種算法進行比較以獲得消耗資源最少效率最高的方法具體可用ASE調優命令set statistics io on set statistics time on set showplan on 等

From:http://tw.wingwit.com/Article/program/MySQL/201311/29522.html