程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQL SERVER 的SQL語句優化方式小結

SQL SERVER 的SQL語句優化方式小結

編輯:關於SqlServer
1、SQL SERVER 2005的性能工具中有SQL Server Profiler和數據庫引擎優化顧問,極好的東東,必須熟練使用。

2、查詢SQL語句時打開“顯示估計的執行計劃”,分析每個步驟的情況

3、初級做法,在CPU占用率高的時候,打開SQL Server Profiler運行,將跑下來的數據存到文件中,然後打開數據庫引擎優化顧問調用那個文件進行分析,由SQL SERVER提供索引優化建議。采納它的INDEX索引優化部分。

4、但上面的做法經常不會跑出你所需要的,在最近的優化過程中CPU占用率極高,但根本提不出我需要的優化建議,特別是有些語句是在存儲過程中並且多表聯立。這時就需要用中級做法來定位占用CPU高的語句。

5、還是運行SQL Server Profiler,將運行結果保存到某個庫的新表中(隨便起個名字系統會自己建)。讓它運行一段時間,然後可以用
select top 100 * from test where textdata is not null order by duration desc
這個可以選出運行時間長的語句,在ORDER BY 中可以替換成CPU、READS,來選出CPU占用時間長和讀數據過多的語句。
定位出問題的語句之後就可以具體分析了。有些語句在執行計劃中很明顯可以看出問題所在。
常見的有沒有建索引或索引建立不合理,會出現table scan或index scan,凡是看到SCAN,就意味著會做全表或全索引掃描,這是帶來的必然是讀次數過多。我們期望看到的是seek或鍵查找。

6、怎麼看SQL語句執行的計劃很有講究,初學者會過於關注裡面顯示的開銷比例,而實際上這個有時會誤導。我在實際優化過程中就被發現,一個index scan的執行項開銷只占25%,另一個鍵查找的開銷占50%,而鍵查找部分根本沒有可優化的,SEEK謂詞就是ID=XXX這個建立在主鍵上的查找。而仔細分析可以看到,後者CPU開銷0.00015,I/O開銷0.0013。而前者呢,CPU開銷1.4xxxx,I/O開銷也遠大於後者。因此,優化重點應該放在前者。

7、如何優化單個部分,一個復雜的SQL語句,SQL SERVER會很聰明地重組WHERE後的語句,試圖匹配索引。選中帶優化的步驟,選擇旁邊的‘屬性”,再選擇其中的“謂詞”,將其中部分復制下來,這部分就是分解後的WHERE 語句,然後在查詢界面中select * from 表 where 剛才復制下來的“謂詞”。這個就是需要優化的部分,既然已經走到這一步了,大部分人應該能手動建立索引了,因為這裡的WHERE語句比之前的肯定簡單不少。(在我項目中原始SELECT語句的WHERE部分有10個條件組合,涉及6個字段,提取出來要優化的部分就4個條件,涉及到3個字段。新的索引建立後,CPU占用率一下子就降低了,而且新建立的索引涉及的字段屬於不常UPDATE的部分,頻繁的讀寫操作不會影響UPDATE的效率)

8、以上就是優化的思路,最後提一些優化過程或是系統設計時中需要注意的問題。
A、盡量避免用select * from xxx where abc like '%xxx'類型的模糊查詢,因為%在前面的話是無法利用到索引,必然會引起全量SCAN操作。應該找尋替代方式或用前置條件語句把like查找之前的行數減到最低。
B、盡量避免對大表數據進行select top n * from xxx where xxxx order by newid()的取隨機記錄的操作。newid()操作會讀全量數據後再排序。也會占用大量CPU和讀操作。可以考慮用RAND()函數來實現,這方面我還在研究中,對於整表操作比較好弄,比如id>=(select max(id) from table)*rand()。但如果取局部數據的隨機記錄還需要思量。
C、在SQL Server Profiler記錄中會看到Audit Logout會占用大量CPU和讀寫等操作。查了一些資料稱是某個鏈接在某次連接過程中執行SQL語句產生的總數,不用過於擔心。看下來的確似乎這樣,很多Audit Logout的CPU和IO消耗量和之前優化的語句基本一致。所以在第5點我提的SQL語句用textdata is not null條件把Audit Logout給隱去。
D、兩個不同字段OR語句會導致全表掃描。例如 where m=1 or n=1。如果建立一個索引是m和n,同樣會引起scan,解決方法是給m和n分別建立索引。測試12萬條數據的表,索引建立錯誤的情況下IO開銷高達 10.xxx,分別建立索引後,全部變成0.003,這個反差是非常巨大的。雖然會引起INSERT操作的性能問題,但畢竟大部分瓶頸在SELECT的讀操作上。
E、索引查找(Index Seek)和索引掃描(Index Scan),我們需要的是前者,而引起後者的原因通常是某個索引裡的字段多余要查找的,例如索引建立在A和B兩個字段,而我們只要查找A,則會導致 INDEX SCAN。建議針對單獨的A建立索引,以形成索引查找。
F、對於小表不建議建立索引,特別是幾百的數據量,只有上千上萬級別的數據建立索引才有效果。

數據庫優化是很深的學問,在數據庫設計時就應該注意,特別是最後提到的A、B兩點,盡可能在設計初期避免。
  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved