一同僚跟我回報他遇到了一個SQL性能問題,他說全表隻有69條記錄,用戶端執行耗費了兩分多鐘,這不科學呀。要我分析一下原因并解決。我按照類似表結構,構造了一個案例,測試截圖如下所示
這個表有13800KB(也就是13M多大小),因為該表将圖檔儲存到資料庫(Item_Photo字段為iamge類型),這個是曆史原因,暫且不噴這種的設計。看來這個SQL執行時間長的性能問題不在于IO和SQL本身執行計劃是否有問題,而是在網絡資料傳時間上(伺服器與用戶端位于異地,兩地專線帶寬6M,不過很多應用、郵件、系統都依賴此專線)sp_spaceused 'Item_Test'
name rows reserved data index_size unused
----------- ------------- ---------- -------------- ----------- -------------
Item_Test 69 13864 KB 13800 KB 16 KB 48 KB
為了驗證我的想法,我在伺服器本機測試時間為2秒,如下截圖所示
從上面我們知道在用戶端執行完該SQL語句,總共耗費了2分23秒。那麼用戶端的到底擷取了多少位元組資料,資料傳輸耗費了多長時間呢? 能否檢視這些DETAIL資訊呢? 答案是可以。在SSMS工具欄,勾選“Include Client Statistics”或使用快捷鍵SHIFT+ALT+S,然後執行SQL語句,就能得到如下截圖的相關資訊。
Client Statistics(用戶端統計資訊)包含三大塊: Query Profile Statistics, Network Statistics, Time Statistics。
這些部分的内容很容易了解,無需多說,那麼我們來看看吧
Network Statistics(網絡統計資訊)
Number of server roundtrips: 伺服器往返的次數
TDS packets sent from client: 從用戶端發送的TDS資料包(個數)
TDS packets received from server: 從服務端接收的TDS資料包(個數)
Bytes sent from client: 從用戶端發送的位元組數
Bytes received from server: 從伺服器接收的位元組數
Time Stattistics:(時間統計資訊)
Client processing time: 用戶端處理時間
Total execution time: 總執行時間
Wait time on server replies: 伺服器應答等待時間
從用戶端發送的位元組和從服務端接收的資料大小都很清晰、明了,那麼資料從伺服器端發送給用戶端所需的時間這裡沒有,其實它基本上接近用戶端處理時間(Client processing time),我們也可以将用戶端處理時間權當網絡資料傳輸時間,從上面案例,我們可以看到這個時間耗費了140秒(140132 ms),可以肯定這個SQL性能慢在網絡資料傳輸上,而不是慢在資料庫那一塊(Server Processing Time).
我們來看看下圖,這個是SQL SERVER的請求接收和資料輸出的一個大緻流程圖,當用戶端發送請求開始,當伺服器接收用戶端發來的最後一個TDS包,資料庫引擎開始處理請求,請求完成後,将資料發送給用戶端,從圖中可以看出,用戶端接收伺服器端傳回的資料也是需要一個過程的(或者說時間)
我們在SQL優化過程中,如果一個SQL出現性能問題時,我們應該站在一個全局的角度來分析問題,從CPU資源、網絡帶寬、磁盤IO、執行計劃等多方面來分析,這樣才能有助于你分析、定位問題根源,而不要隻要SQL響應很慢時,就一味條件反射式先入為主:這是資料庫問題。資料庫也不能老背這個黑鍋。
在資料庫等待事件中,ASYNC_NETWORK_IO可以從另外一個側面反映網絡性能問題。關于ASYNC_NETWORK_IO等待類型:
This waittype indicates that the SPID is waiting for the client application to fetch the data before the SPID can send more results to the client application.
那麼回到如何優化這個SQL的問題上來,我們可以從下面幾個方面來進行優化。
1: SQL隻取必須的字段資料
像這個案例,其實它根本不需要Item_Photo字段資料,那麼我們可以修改SQL,隻取我們需要的字段資料,就可以避免這個問題,提高SQL性能,另外根據我的經驗,開發人員習慣性使用SELECT *,從不管那些資料是需要還是不需要的,先全部取過來再說,這種習慣性行為确實不是一個好習慣。
2:避免這種腦殘設計
圖檔應該以檔案形式儲存在應用伺服器上,資料庫隻儲存其路徑資訊,這種将圖檔儲存到資料庫的設計純屬腦殘行為。
------------------------------------------------------------分割線-------------------------------------------------------------
看到很多網友說沒有給出解決方案和結果,我就很納悶,解決方法我明明不是已經在上文交代嗎,後面陸陸續續好幾個都這樣說,不淡定了,看來我的表述能力還是有問題,好吧,補充如下:
因為這個案例,根本不需要用到Item_Photo這個字段(儲存的圖檔),那麼我就取所需字段就好了,如下所示
如上截圖所示,Client processing time(用戶端處理時間)為18毫秒,伺服器端傳送過來的資料隻有7512位元組,也就是7KB大小,對比上下兩者的差距,我想資料能說明一切了,關于我噴圖檔儲存在資料庫的這種設計,出乎我意料,很多人不認同,好吧,不多說也不偏激,自己測試、權衡吧。事實勝于雄辯!