INF:如何撰寫較好的查詢,並將它最佳化以用於 SQL Server 7.0

目前我們常見的效能問題之一,常常是因某個執行不良的查詢耗用太多系統資源 (例如 I/O 與 CPU 時間) 所造成。查詢最佳化對於今日複雜的資料庫與商業環境而言,仍然是一項有待挑戰的工作。


SQL Server 7.0 與 2000 完全重新寫成的查詢最佳化器接受了此項挑戰,它內建精密的智慧邏輯,可幫助使用者選擇某特定陳述式的最佳查詢計劃,並加入新的聯結計劃。此最佳化器也採用剪除探索法,以確認不會花太多時間在最佳化查詢卻沒有選用並執行簡單的計劃。這樣可以避免發生過度最佳化;然而,換得的是有時候會產生沒那麼最佳化的計劃。這也是為何要提供最佳化提示的原因,當遇到較複雜的查詢時,使用者就可以提供其他協助給最佳化器。除了有較聰明的最佳化器外,7.0 版也引用了新的索引微調精靈,協助管理員微調效能,並找出是否有任何新的索引可以幫助加速目前的處理程序。


所有這些新功能與精靈都是用來達成一個目標,就是能有效率的執行特定陳述式。然而,如果以沒有效率的方式來撰寫陳述式,即使是最佳化器與精靈也無法以任何方式來加速執行效率。


因此,牢牢記住基本的最佳化原則,可幫您在一開始就撰寫較好的陳述式。這樣最佳化器就能發揮它所有的潛力,而查詢作業在一開始也會有穩固的效能基礎,如果還需要進行微調,最終也會讓微調工作進行的較輕鬆容易。

結論

原則 1. 使用 SARG 讓結果集盡可能小一點。




最佳化查詢的定義是,經由存取最少量的資料就能完成陳述式的執行。如果需要擷取或處理表格中的所有列,那麼就不太需要最佳化。如同在 Online Transaction Process (OLTP) 中,大部分的處理程序不會用到表格中大部分的資料,而只用到有關特定索引鍵的一小組資料。因此,要如何直接到達要求的資料集就是查詢的重點,我們可以使用 WHERE 子句以及一個或多個搜尋引數 (SARG) 來將結果集縮減到最少。


請記住,只有關鍵字 AND 可以幫您減少結果集。另一方面,關鍵字 OR 會幫您擴增結果集。此外,運算子 = 最能幫您判斷某個索引是否可以使用;而關鍵字 BETWEEN、> 與 < 也是很有用的 SARG。使用 LIKE 與只用一個前碼引數會很有用,因為現在已經支援全文搜尋,如果需要用很多 LIKE,可以考慮採用這種方式。然而,全文搜尋仍然無法支援像 %word 這樣的後碼搜尋,只支援 word% 這樣的前碼搜尋。如果一個子句可以用來作為搜尋引數,我們就將它當成是可做搜尋引數的 (Sargable) 或可最佳化的,這是開始建立最佳化查詢的關鍵。使用 NOT 會引發表格掃瞄作業,如果必須使用子查詢,請記得也在子查詢中使用 SARG,絕不可像這樣寫:

where exists (select id from customer)

應該要寫成這樣:

where exists (select id from customer where id = custid)

原則 2. 將表格與檢視表標準化以減少過度聯結。




資料標準化一直是資料庫設計師的棘手議題。一個極端的作法是將所有東西都放到一個或少數表格中,這樣確實可以將聯結減少到只剩一些或沒有聯結,但卻也會傷害到並行規則,並造成過度的 I/O,因為這不是關聯式模型。另一方面,如果將每件資訊單獨放到表格中,虛耗的索引鍵所佔的空間可能比資料還大,並且對於選擇最佳化查詢計劃來說是最糟的情況,因為要存取完整的資料集就必須聯結許多表格。因此,在決定要將資料存放在何處以及如何存放資料之前,也要先想想如何擷取資料。不要只為了需要個別更新與擷取資料就將資料從表格分開。這樣可以幫助改善存取資料的速度。


使用檢視表是一個好方法,可以隱藏表格之間的關係與限制未經授權的資料存取,如果大部分的選取作業是來自檢視表而非來自表格,這會是一個重要的設計問題。沒有需要經由建立更多檢視表來修正此設計問題,最好是重新設計表格,而不是以檢視表來修正問題。如果查詢時常常有需要聯結兩個或更多檢視表,請檢查是否有設計上的問題,或需要新增至少一個檢視表。如果以檢視表聯結,最佳化可能會複雜很多。如果我們在其他檢視表上建立檢視表,並且每一個檢視表都包含多個表格或檢視表,事情就會變得很糟,因為這樣會讓手動最佳化變成不可能。所以基本原則是,如果可能請只使用一個檢視表,並且在沒有現存檢視表可以含蓋查詢而又必須使用檢視表時,才能建立新的檢視表。否則,無論何時只要可能請只在查詢中使用表格。避免使用 Driving Table,這也可能會讓查詢計劃變得複雜。


此原則和 FROM 子句有關,即使用較少的表格可以幫助簡化查詢計劃。如果使用 ANSI 格式,請只在那兒放置聯結索引鍵,並將所有限制子句放到它們實際屬於的 WHERE 子句中。如果有需要聯結,請注意最佳化器所選取的聯結類型對效能的影響會有很大的不同。最好的可能是合併聯結,兩個表格會依相同的索引鍵排序,亦即聯結索引鍵在兩個表格上都是叢集式索引鍵,所以在建立使用叢集式索引鍵的表格時,請將這點考慮進去。


請注意,只有外面的表格可以使用 SARG 索引,其他的都是裡面的表格,只能使用聯結索引鍵的索引,而這些表格上的 SARG 只會用來作為篩選器,無法幫助減少邏輯讀取。所以在 FROM 子句中使用具有 SARG 的表格作為第一個表格,外面的表格,可以幫您將結果集減少至最少列。接下來則是先列出 (List) INNOR JOIN 的表格,這或許可以幫助減少來自聯結的結果集,然後是 OUTER JOIN,將它們放在最後。


在這裡還要提到另一件事,就是在 SARG 中使用變數。因為在編譯期間無法得知變數的值,所以它會導致最佳化器去使用較無用的統計資料,結果產生不是最好的計劃。但在 SARG 中使用預存程序參數應該是沒問題,因為直到第一次以每個參數的特定值呼叫預存程序後,才會編譯預存程序。

步驟 3. 判斷結果欄位。




在這裡需要指出的唯一一件事,就是使用結果欄位的子查詢是不好的事,需要避免。因為這樣會造成每一個傳回來的列都會再執行一次子查詢,如果結果集不只一些,就會很浪費時間與成本。如果是大量的結果集,就會降低整個伺服器的效率。應該要使用 Driven Table 來取代子查詢,並聯結子查詢和結果集。

步驟 4. 檢查查詢計劃。




如果陳述式是簡單的查詢或資料集很小就可以跳過此步驟。但,如果某個查詢聯結超過 3、4 個表格,並且資料集很大,最好是在 Query Analyzer 中執行查詢,按一下 [目前的連線選項] 按鈕,接著選取 [顯示查詢計劃],然後執行查詢計劃以檢查它是否是屬於比較簡單的查詢計劃,如果有聯結時,請檢查它是使用哪種聯結。現在使用多種聯結類型,比較沒有絕對的好聯結或壞聯結,但我們仍會有值得注意的事項:


如果是大型表格,請避免執行表格掃瞄與雜湊聯結。最好是使用合併聯結。如果可以維持較低的掃描次數,則使用迴圈 (Loop) 聯結也不錯。如果查詢計劃中出現表格多工緩衝處理 (Table Spool),就表示需要有暫存表格來放置中間結果,可能也表示需要許多額外的 IO,所以如果可能請避免發生這種情形。如果查詢計劃很龐大,有 30、40 或更多行使用許多不同的聯結與排序或暫存表格,最好是不要在工作場合中執行查詢測試,因為可能會用掉太多系統資源,把每個人的效率都降低了。您應該先在測試伺服器或下班時間來測試此查詢,看看它會對伺服器造成哪種影響。SQL Server 7.0 可讓您聯結多達 256 個表格,且表格中有足夠的資料,因此若要撰寫一個聯結很多表格的查詢,並在執行很長一段時間後還是不見結果,這應該是不太難的事。這種查詢會將工作用的伺服器拖垮,所以要測試看起來不簡單的新查詢時,必須非常小心。

步驟 5. 微調與疑難排解。




測試查詢之後,就不該再有語法錯誤,且會傳回結果集。如果驗證之後,所有結果都正確,就得到一個可執行的查詢。不要在此處打住,請回答接下來的問題:此查詢的效能好嗎?還可以改善嗎?


要期待每一個查詢都能在一秒內傳回結果是不切實際的,所以我們要如何得知它的效能是否夠好?且此查詢是否還可以改進?我們可以依據下面資料來找出答案:


  • 時間統計資料


    這是目前的連線選項之一,選取此選項就能以毫秒來顯示查詢所花費的時間。對效能來講這不是很好的指標,因為它不會是常數,全要看執行查詢時系統的忙碌程度。然而,您還是可以用來當成相對性的指標,作為一般性的回應時間。作為一個線上應用程式,任何事只要少於一秒就可能被認為是效能優異。但如果是大批工作,若某個陳述式必須執行上千次,那麼就算差一毫秒都算數。從此計數器,我們可以知道查詢是否花費太長時間,以及時間有多長。如果執行緩慢的查詢計劃,我們也可以找出查詢是在哪裡花費了大部分的時間。



  • IO 統計資料


    這是 Query Analyzer 的另一個連線選項,它是效能微調的主要指標。從此統計資料,我們可以看出查詢在每個表格上執行多少次讀取。當讀取的次數和傳回的列數很相近時,那麼除了減少結果集之外,就不需再做任何最佳化了,而減少結果集是一開始就要執行的第一步驟。如果讀取次數比傳回的列數還要多很多,那麼試圖減少讀取次數可能會增進效能。請查看查詢計劃,看看是否有方法可以減少掃瞄次數,經由建立叢集式索引來將索引搜尋改成索引掃瞄。在 7.0 中,如果表格上有叢集式索引,我們就會使用叢集式索引鍵來建立非叢集式索引,所以如果來自大型表格的結果集並不小,而表格的索引層次也很高,則執行索引搜尋會比直接執行表格掃瞄要付出更多的成本。因此請務必將效能微調的目標設定為降低查詢的 IO。和舊版本不同的是,要找出正確的查詢索引已經不再是個問題。



如果依循此處所述的每一個步驟,還是無法最佳化執行緩慢的查詢,請使用下面 KB 文件,以取得有關疑難排解的詳細資訊:

243589資訊:在 SQL Server 7.0 或更新版本上的執行緩慢查詢疑難排解 Microsoft Press 出版的《Inside Microsoft SQL Server 7.0》也是很好的參考書籍,可幫您瞭解 SQL Server 7.0 中每件事是如何運作的原理。

其他相關資訊

本文件是根據 Microsoft Knowledgebase 文件編號 Q256078 翻譯的. 若要參考原始英文文件內容, 請至以下網址:


http://support.microsoft.com/support/kb/articles/Q256/0/78.asp

参考

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Insiders

Was this information helpful?

Thank you for your feedback!

×