一同僚跟我回報他遇到了一個sql性能問題,他說全表隻有69條記錄,用戶端執行耗費了兩分多鐘,這不科學呀。要我分析一下原因并解決。我按照類似表結構,構造了一個案例,測試截圖如下所示
這
個表有13800kb(也就是13m多大小),因為該表将圖檔儲存到資料庫(item_photo字段為iamge類型),這個是曆史原因,暫且不噴這種
的設計。看來這個sql執行時間長的性能問題不在于io和sql本身執行計劃是否有問題,而是在網絡資料傳時間上(伺服器與用戶端位于異地,兩地專線帶寬
6m,不過很多應用、郵件、系統都依賴此專線)
為了驗證我的想法,我在伺服器本機測試時間為2秒,如下截圖所示
從上面我們知道在用戶端執行完該sql語句,總共耗費了2分23秒。那
麼用戶端的到底擷取了多少位元組資料,資料傳輸耗費了多長時間呢? 能否檢視這些detail資訊呢?
答案是可以。在ssms工具欄,勾選“include client
statistics”或使用快捷鍵shift+alt+s,然後執行sql語句,就能得到如下截圖的相關資訊。
client statistics(用戶端統計資訊)包含三大塊: query profile statistics, network statistics, time statistics。
這些部分的内容很容易了解,無需多說,那麼我們來看看吧
從
用戶端發送的位元組和從服務端接收的資料大小都很清晰、明了,那麼資料從伺服器端發送給用戶端所需的時間這裡沒有,其實它基本上接近用戶端處理時間
(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大小,對比上下兩者的差距,我想資料能說明一切了,關于我
噴圖檔儲存在資料庫的這種設計,出乎我意料,很多人不認同,好吧,不多說也不偏激,自己測試、權衡吧。事實勝于雄辯!