SQL SERVER 的SQL語句最佳化方式小結

1、SQL SERVER 2005的效能工具中有SQL Server Profiler和Database Engine Tuning Advisor，極好的東東，必須熟練使用。

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

3、初級做法，在CPU佔用率高的時候，開啟SQL Server Profiler運行，將跑下來的資料存到檔案中，然後開啟Database Engine Tuning Advisor調用那個檔案進行分析，由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兩點，儘可能在設計初期避免。