前段接手了個優化項目大概要求是對公司現有的W多張表進行索引優化完善現有的剔除無效的索引鑒於人手嚴重不足(當時算兩個半人的資源)打消了逐個庫手動去改的念頭當前的程序結構不允許搞革命的做法只能搞搞改良所以准備搞個自動化工具去處理原型剛開發完開會的時候以拿出來就遭到運維DBA團隊強烈抵制具體原因不詳最後無限延期這裡把思路分享下歡迎拍磚
整個思路是這樣的索引都是為查詢和更新服務的但是不合適的索引又會對插入和更新帶來負面影響面對表上現有的索引想識別那些是有效的不太可能那麼根據現有的數據使用情況重建所有的新索引不就解決了嘛根據查詢生成全新索引然後和現有對比不吻合的全部刪除原來沒有的創建雖然說對於正在運行的系統來說風險還是蠻大的但是可以做臨界測試嘛
具體解決方案如下
首先在熱備的數據庫服務器上定期抓取緩存的執行計劃(原本想抓取SQL發現有些SQL實在摻不忍睹沒有自動化解析的可能性)然後連同該執行的執行次數即表的統計信息一起down到一個備用服務器的數據表中
執行計劃積累幾次後開始解析由於執行計劃是格式良好的XML文件加上微軟提供執行計劃的XSD文件我們可以反向推出各節點對應的SQL謂詞(這個XSD到現在都沒找到官方的說明只能反向推出關聯)例如建立索引我們比較關心三類謂詞分別為SelectJoinWhere 只要拿到這些我們就能建立良好的索引原理很簡單Join和Where都是索引鍵的依據而Select可以斟請添加到Index的Include中
解析的時候也不是針對單個執行計劃而是將所有執行計劃全分解後進行統計處理好處就是能夠知道那些表字段被引用的最多那些是外鍵列那些數據被反復查詢例如可以得出TableA的Col列在一天的業務過程中被Join了W次被WhereW次而Col則被Select了W次僅僅被Where了次這樣我們建立索引的基礎就是基於表的而不是基於單個查詢的最終生成的Index將權衡查詢頻率和查詢的重要性如果某個業務查詢特別重要但執行頻率不高我們可以提供權重優先建立索引當然創建Index還要參考表的數據分布以決定Index中字段的順序
好了准備工作完成開始建索引當前擁有的條件表數據分布表字段分別被查詢引用次數(SelectJoinWhere)以及這些SQL謂詞出現的次數根據這些如何創建索引開始的想法是逐個分析考慮所有可能性然後創建發現這種方式只適合人腦讓電腦做得先讓電腦的智商增長到以上才有可行性發現逆向思維這裡同樣大有用處既然不能一下子創建最合適的那我們就根據執行計劃得出的組合創建所有的Index組合凡是Join和Where都放到Index的Key裡例如
select tA tB tC tJ tk from Table t Join Table t on tA = tj Where tA = param
草創的索引就是
Index(AB)includ(C) 和 Index(j)include(jk)
關於Select如果是小數據類型且Alter的執行計劃中該數據修改頻率很小的都放到Include裡去進去大數據類型和修改比較頻繁的就算了這樣我們剔除相互覆蓋的部分重疊的部分重疊到底保留那一個參考執行頻率和查詢重要性差異很小的就合並並為一個如
Index (ABC)Include(D)
Index(ABD)Include(C)
直接合並為
Index(AB)Include(CD)
當然如果Alert的特別少也可以合並成Index(ABCD)這個要參考CD字段的修改頻率和主鍵重疊的剔除這樣留下的基本上就是我們需要的索引了
對比現有索引進行甄別覆蓋的過程就略過簡單的拉出來Create Index 進行解析處理就好了發布的時候很簡單寫個腳本在業務比較少的時候做Drop和Create就完成了項目源代碼因為設計到公司的保密問題就不上傳了一個注意的地方對於簡單查詢的SQL執行計劃緩存的時候會比較短且一旦緩存不夠就會被清理掉要注意這些SQL的執行頻率的誤差
SqlserverR XSD:schemasmicrosoft/sqlserver///showplan/sql/showplanxmlxsd
總結的節點映射列舉如下
查詢sql執行計劃都包含在節點"StmtSimple"中如果沒有這個節點一般就是其它類型的SQL的執行計劃
Join關聯的節點和自身類型有關一般包含在HashMarger中如何Join同時又是Where條件的話則會出現在SeekKey和Compare節點中因為Join的列都是成對出現這裡很容易識別有一個是參數(@開頭)或常量(type="Const")則必定是Where條件
Select最終輸出字段比較容易找到第一個OutputList節點就是
需要注意的是有因為一般列每個ColumnReference都包含庫名表名列信息但是系統表則不會注意剔除
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22277.html
