501
技術社區[雲棲]
不再治標不治本，性能優化苦手們的良方來了！
作者介紹
吳虞，SQL專家雲團隊成員，擅長解決SQL Server數據庫性能、高可用、負載均衡等問題。
前言
性能優化是數據庫運維人員和中、高級軟件開發人員的必備技能，很多時候老司機和新司機的區別就在寫出的東西是否優化。
博主接觸過近千家客戶的係統，這些係統都存在著各種各樣的性能問題。那麼如何透徹地了解我們的數據庫性能問題？今天就用一個案例來說明性能優化的那點事兒。
PS：很多技術人員對優化有一套自己的理解，在閱讀本文前請放下你自己的理解。
正所謂：跟著博主不迷路，博主帶你上高速！
點開案例跟著博主的思路看看優化這些事兒 ： 本文案例Demo。
（https://www.zhuancloud.com/Index.html）
優化首先要知道數據庫在一個什麼樣的硬件/軟件環境下運行？配置是怎麼樣的？內存、CPU這些是否能完全被應用？數據庫體量多大？
首先我們先看一下係統的配置：
軟件層麵，我們要知道我們的操作係統版本，SQL Server版本，以及對應版本的硬件限製（如32位係統不開AWE無法使用超過4G內存、server 2008 標準版最大支持32G內存等）
本例中我們可以看出，係統環境沒有異常問題，SQL Server的補丁不是最新的，CPU資源不充足，可能CPU會成為係統的瓶頸。
全局層麵看性能
全局層麵看問題主要指綜合服務器的各種指標表象定位係統的瓶頸或問題，在性能優化中最忌諱的就是看到一個指標馬上就下手，針對一個指標的判斷是盲目的，很可能使問題偏離本身的根本原因，也可能使優化根本無法解決根本問題而隻是表象得到了緩解。
性能計數器
CPU：在大量時間內CPU的使用率達到100%，說明CPU已經成為瓶頸。
內存：內存計數器生命周期在11點時已經降到0，惰性寫入器也彪高，說明內存也存在壓力，而且比較嚴重。
磁盤：磁盤的平均隊列很高（一般係統最佳情況隊列應該低於2），並且讀隊列和寫隊列都很高。由於內存存在壓力，所以現在無法判斷磁盤的壓力是由於內存不足引起的還是磁盤速度不能滿足需要。
其他計數器：
可以看到係統中全表掃麵的次數比較多，這表明很多查詢沒有應用索引。
係統在11點左右和11點24左右發生大量鎖等待，並且等待時間很長（超過150s）
通過很多類計數器能綜合看出係統的問題。這裏不一一細說了。
係統等待
等待是另一個可以全局層麵看係統的指標，係統運行的卡慢問題很大部分是因為等待而引起的，那麼等待的類型也是可以很直觀的反映出係統的問題。
幾個主要等待：
-
ASNC_NETWORK_IO：一般反應出有部分查詢可能返回大量數據，請加查具體的等待語句是否需要返回如此多的數據。
-
WAITFOR ：可能是配置了CDC發布訂閱或程序中使用了語句waitfor delay
-
CXPACKET：CPU的調度等待。
-
LCK_M_U ：更新語句之間的語句阻塞。
-
WRITELOG：說明程序中有循環的插入跟新操作而頻繁的寫入日誌，磁盤速度不能滿足寫入頻率而造成。
綜合分析
綜合係統等待和性能計數器，我們基本可以判定出來係統存在以下問題：
係統的CPU、內存、磁盤均存在較大的壓力，尤其CPU負荷接近100%，係統中存在大量表掃麵可能缺失比多索引。係統中有的語句可能要返回大量的不必要數據，係統鎖情況嚴重，等待時間很長，語句執行時間也必然很長。
語句執行的整體情況：由於上述的問題影響，那麼係統中必然存在大量的長時間語句！
解決問題
問題的定義是很重要的一步，從全局的多項指標綜合分析，讓所有問題無所遁形。定位問題後我們先來看一下解決這些問題的基本步驟。
本案例是自己模擬的一個情況，所以雖然在表象上來看資源壓力很大，但實際在運行的語句不多，場景也有限，但在生產係統如果存在這樣的表象，那麼說明你的係統性能問題非常嚴重急需一次詳細的優化了。
下麵也介紹一下生產係統遇到這樣的問題應該怎麼優化，有哪些必要的步驟。
步驟一 針對係統問題對數據庫進行全麵的優化，提升整體效率
很多人優化可能直奔語句，認為語句就可以解決性能的所有問題，其實這樣的觀點是不全麵的，係統的配置，數據庫的配置，索引的規劃等都是解決性能的必要步驟。
例如：係統中的語句都是最佳的，數據庫運行還是很慢，可能就是因為你的CHECKDB配置的問題，也有可能因為你自動收縮沒有關閉而導致的性能問題。
優化操作係統配置
針對服務器進行配置檢查，查看是否有配置不合理或可以優化的配置項，比如是否配置了虛擬內存？服務器層麵是否限製的資源使用？服務器是否高性能模式運行？
優化數據庫層麵的配置
針對數據庫參數進行合理配置使硬件充分發揮硬件功能，優化不合理配置，降低對數據庫造成衝擊的可能性。比如：最大並行度？最大內存？
是否大量缺失索引
大量索引缺失必然導致語句性能不佳，並且消耗大量的係統資源，很可能就會造成上麵服務器高壓力的表象。
刪除無用索引
針對數據庫中無用的索引進行刪除。提升更新操作的時間。
刪除重複索引
針對數據庫中重複的索引進行刪除。提升更新操作的時間。
對重點語句建索引
針對係統中消耗大的語句或執行次數多的語句進行分析，評估語句性能問題，並建立合適的索引提，降低語句的資源消耗，升語句運行效率。
解決阻塞
解決語句間的阻塞，這需要分析語句的阻塞鏈，到底語句被什麼樣的操作阻塞了，為什麼會阻塞？
很多新手經常問的問題：為什麼我有的時候查很快有的時候查就很慢？ 答：大多數情況就是你的語句被阻塞了。
優化TempDB
針對TempDB調優，減少TempDB資源爭用導致的壓力。本例中可以死看到有TempDB的爭用等待，所以對TempDB的優化也是必要的。
優化日誌碎片
針對日誌增大，帶來的日誌碎片問題進行優化。
清除索引碎片
檢查係統的索引維護情況，並針對碎片過大的表進行碎片清除操作。主要體現在係統中有老化的索引，索引的老化導致索引的性能不高或失效。
一階段預期效果
一階段的優化是對性能的整體提升，性能提升也會很明顯，針對不同係統提升一般在2-3倍。
步驟二 處理熱點問題
處理熱點問題主要是在階段一的基本優化後針對重點的語句進行調優，可能包含創建索引，修改寫法，查詢提示，計劃向導等等。
在語句調優中請主要關注：是否有缺失索引，是否存在隱式轉換，語句的執行時間、CPU、邏輯讀寫量、物理讀寫量、占用TempDB空間等信息。
例：這樣一條語句經過第一階段的優化並沒有太大的提升，而且資源消耗依然很大，那麼我們可以針對這條語句進行詳細的二階段優化。
簡單的優化一下
隻是簡單的改了下語句的寫法時間有7秒變成1秒，內存消耗從300+MB 變成 1MB。
二階段預期效果
階段二的優化屬於細致的優化步驟，要針對更為具體的語句、具體的情況。經過本階段優化可以使係統中大部分語句從寫法、配置、運行指標都趨於優化值。
步驟三 針對業務
這個步驟需要配合開發人員，到底哪些功能依然慢？執行了哪些語句？是領導用的功能？還是一般可以慢的功能？如果大領導用的功能，那可能你就需要多花些心思了。這部分這裏就不展開說了。
三階段預期效果
第三階段屬於最細致的階段，可以結合業務真正點對點的消滅係統中存在問題。
導圖
針對性能優化奉上幾個圖，希望能幫助數據庫從業者梳理一下優化的思路（個人思路僅供參考，不完善的地方也請見諒）。
CPU：
內存：
磁盤：
等待：
總結
在性能優化中最忌諱的就是看到一個指標馬上就下手，針對一個指標的判斷是盲目的，很可能使問題偏離本身的根本原因，也可能使優化根本無法解決根本問題，而隻是表象得到了緩解。
本文隻是通過一個例子簡述一下優化的基本思路，希望幫助更多數據庫從業者，了解性能優化。
性能調優是一個持續性的工作，不是一次解決了問題以後就可以高枕無憂，定期的巡檢也是數據庫從業者必要的工作之一，做到及早發現及早解決。
原文發布時間為：2017-01-17
本文來自雲棲社區合作夥伴DBAplus
最後更新：2017-05-15 17:32:42