SQL效能最佳化

標籤：

引言：　　

　　以前在面試的過程中，總有面試官問道：你做過sql效能最佳化嗎？對此，我的回覆是沒有。一次沒有不是自己的錯誤，兩次也不是，但如果是多次呢？今天痛下決心，把有關sql效能最佳化的相關知識總結一下，以便在不久的將來，我的回答不是“沒有”，總能多多少少說一些東西。算是長進吧。說到效能最佳化，本人感覺到有必要先瞭解sql語句的執行順序，因為對最佳化或多或少的會有些協助。

 

sql語句執行順序：　　

　　sql語句和其他相關的程式設計語言最大不同的地方應該是執行順序。對於大多數程式設計語言來說都是按照順序進行執行，但對於sql語句，儘管select是最開始出現，但幾乎總是最後一個執行，最開始執行的往往是from子句。每一步驟產生一個虛擬表，這些虛擬表對於調用者來說是不能用的，僅僅作用於下一步驟，而只有最後的查詢結果表才能被調用者所使用。當有步驟沒有出現時便跳過該執行步驟。下面上代碼：

(8)SELECT (9)DISTINCT  (11)<Top Num> <select list>(1)FROM [left_table](3)<join_type> JOIN <right_table>(2)        ON <join_condition>(4)WHERE <where_condition>(5)GROUP BY <group_by_list>(6)WITH <CUBE | RollUP>(7)HAVING <having_condition>(10)ORDER BY <order_by_list>

邏輯查詢處理階段簡介：

1）from：對FROM子句中的前兩個表執行笛卡爾積（Cartesian product)(交叉聯結），產生虛擬表VT1

2）on：對VT1應用ON篩選器。只有那些使<join_condition>為真的行才被插入VT2

3）outer(join)：如 果指定了OUTER JOIN（相對於CROSS JOIN 或(INNER JOIN),保留表（preserved table：左方外部聯結把左表標記為保留表，右方外部聯結把右表標記為保留表，完整外部聯結把兩個表都標記為保留表）中未找到匹配的行將作為外部行添加到 VT2,產生VT3.如果FROM子句包含兩個以上的表，則對上一個聯結產生的結果表和下一個表重複執行步驟1到步驟3，直到處理完所有的表為止。

4）where：對VT3應用WHERE篩選器。只有使<where_condition>為true的行才被插入VT4.

5）group by：按GROUP BY子句中的列列表對VT4中的行分組，產生VT5.

6）cube|roolup:把超組(Suppergroups)插入VT5,產生VT6.

7)having:對VT6應用HAVING篩選器。只有使<having_condition>為true的組才會被插入VT7.

8)select:處理SELECT列表，產生VT8.

9)distinct:將重複的行從VT8中移除，產生VT9.

10)order by:將VT9中的行按ORDER BY 子句中的列列表排序，產生遊標（VC10)

11)top:從VC10的開始處選擇指定數量或比例的行，產生表VT11,並返回調用者。

註：

步驟10，按ORDER BY子句中的列列表排序上步返回的行，返回遊標VC10.這一步是第一步也是唯一一步可以使用SELECT列表中的列別名的步驟。這一步不同於其它步驟的 是，它不返回有效表，而是返回一個遊標。SQL是基於集合理論的。集合不會預先對它的行排序，它只是成員的邏輯集合，成員的順序無關緊要。對錶進行排序 的查詢可以返回一個對象，包含按特定物理順序組織的行。ANSI把這種對象稱為遊標。理解這一步是正確理解SQL的基礎。

因為這一步不返回表（而是返回遊標），使用了ORDER BY子句的查詢不能用作表運算式。表運算式包括：視圖、內聯資料表值函式、子查詢、派生表和共用運算式。它的結果必須返回給期望得到物理記錄的用戶端應用程式。

　　在SQL中，表運算式中不允許使用帶有ORDER BY子句的查詢，而在T—SQL中卻有一個例外（應用TOP選項）。所以要記住，不要為表中的行假設任何特定的順序。換句話說，除非你確定要有序行，否則不要指定ORDER BY 子句。排序是需要成本的，SQL Server需要執行有序索引掃描或使用排序運行符。

 　　sql語句執行時是按照從右至左的順序處理from子句中的表名，from子句中寫在最後的表也即是基礎資料表將被最先處理，因此在from子句中包含多個表的情況下，選擇記錄條數最少的表作為基礎資料表，在某種程度上將會極大的提高其效能。如果有3個以上的表，則選擇交叉表作為基礎資料表。此處對效能最佳化來說相當重要。

　　

執行計畫：

說完執行順序後，便討論下執行計畫：

執行計畫是資料庫根據SQL語句和相關表的統計資訊作出的一個查詢方案，這個方案是由查詢最佳化工具自動分析產生的，比如一條SQL語句如果用來從一個 10萬條記錄的表中查1條記錄，那查詢最佳化工具會選擇“索引尋找”方式，如果該表進行了歸檔，當前只剩下5000條記錄了，那查詢最佳化工具就會改變方案，採用 “全表掃描”方式。

可見，執行計畫並不是固定的，它是“個人化的”。產生一個正確的“執行計畫”有兩點很重要：

(1)    SQL語句是否清晰地告訴查詢最佳化工具它想幹什嗎？

(2)    查詢最佳化工具得到的資料庫統計資訊是否是最新的、正確的？

 

最佳化偵查工具：

　　基礎知識介紹完畢了，開始效能最佳化，但是我們怎麼才能知道該系統中的那些sql語句應該進行效能最佳化，該語句是否應該進行系統最佳化，查看相關資料，針對sqlserver，找到sqlserver資料庫對應的有個sql server profiler，使用該工具可以找到針對某個資料庫表來說，有什麼樣的操作行為拉低了其效能。

開啟系統主菜單--sqlserver幾---效能工具--->>sql server profiler；

然後檔案--建立跟蹤--顯示跟蹤屬性視窗；

首先那個select%是個篩選監測的TextData。那個%是個萬用字元，他的意思就是篩選select開口的語句。當然這你自己可以隨便定義，如update%，delete%....。

把那個排除不包含值的行也給帶上，然後確定，運行。然後在資料庫中運行一句select。你會發現他檢測到啦。

1.尋找期間最長的查詢

一般情況下，最長查詢時間的查詢語句就是最影響效能的原因存在。它不僅佔用資料庫引擎大量的時間，還浪費系統資源，還影響資料庫應用系統的互動速度。再對資料用應用系統進行最佳化時，先找出他，對其最佳化，在建立跟蹤時，勾上TSQL-SQL:BatchCompleted.跟Stored Procedures-RPC:completed。這樣就能找出來這個最長時間查詢然後對其進行分析最佳化。

select TextData,Duration,CPU from <跟蹤的表>where EventClass=12 -- 等於12表示BatchCompleted事件and CPU<(0.4*Duration)  --如果cpu的佔用時間，小於執行sql語句時間的40%，說明該語句等待時間過長

2.最佔用系統資源的查詢

就是佔用cpu時間，跟讀寫IO的次數。建議事件包含Connect、Disconnect、ExistingConnection、SQL:BatchCompleted、RPC:completed，列包含writes，reads，cpu。

3.檢測死結

在訪問量，並發量都很大的資料庫中，如果設計稍不合理，就有可能造成死結，給系統效能帶來影響。事件包含：RPC:Starting、SQL:BatchStarting、Lock：DeadLock（死結事件）、Lock：DeadLockChaining（死結的事件序列）。

查閱SqlServer效能檢測和最佳化工具使用詳細

 

Database Engine Tuning Advisor

和sql server profiler相對於的有個“Database Engine Tuning Advisor”，也是一個與效能最佳化有關的工具，可以抽時間瞭解瞭解。瞭解後再補充吧。

 

sql效能最佳化常見經驗：

下面總結下載網上各個大牛們認為進行sql最佳化應該操作的事項：

1、模糊查詢like。

使用like進行模糊查詢時應該特別注意，這個很基本，基本上大家都知道。呵呵

select*from contact where username like ‘%yue%’

關鍵詞%yue%，由於yue前面用到了“%”，因此該查詢必然走全表掃描，除非必要，否則不要在關鍵詞前加%。

2、where條件查詢

盡量避免使用in,not in,having,可以使用 exist 和not exist代替 in和not in。不要以字元格式設定聲明數字，要以數字格式聲明字元值。

3、前面提到的from子句中有多個表進行關聯查詢時

在from子句中包含多個表的情況下，選擇記錄條數最少的表作為基礎資料表，在某種程度上將會極大的提高其效能。如果有3個以上的表，則選擇交叉表作為基礎資料表

4、select *查詢

盡量不要使用

select * from tablename

取而代之的則是：

select columnname1,columnname2 from tablename

5、排序操作

避免使用耗費資源的操作，帶有DISTINCT,UNION,MINUS,INTERSECT,ORDER BY的SQL語句會啟動SQL引擎 執行，耗費資源的排序(SORT)功能. DISTINCT需要一次排序操作, 而其他的至少需要執行兩次排序。

6、索引表操作

對於此處，個人還沒有弄明白，首先對於索引還不明白，那麼效能最佳化更談不上了。反正很多大牛都是操作索引表，需要特別注意。以後明白了再補充吧。

...

7、統一規範sql語句

編寫規範的sql語句，這一點是最重要的一點，不管對於系統還是個人來說，都是相當的重要。

不規範的有：

很複雜的sql語句，對於編寫者自己都暈了。

大小寫隨意編寫，對於系統來說是個小麻煩。

肯定還有，就是平時多注意就ok了。

 

參考：

SqlServer效能檢測和最佳化工具使用詳細高手詳解SQL效能最佳化十條經驗最佳化SQL查詢：如何寫出高效能SQL語句

 

SQL效能最佳化