鑒于人手嚴(yán)重不足（當(dāng)時(shí)算兩個(gè)半人的資源），打消了逐個(gè)庫(kù)手動(dòng)去改的念頭。當(dāng)前的程序結(jié)構(gòu)不允許搞革命的做法，只能搞搞改良，所以準(zhǔn)備搞個(gè)自動(dòng)化工具去處理。原型剛開(kāi)發(fā)完，開(kāi)會(huì)的時(shí)候以拿出來(lái)就遭到運(yùn)維DBA團(tuán)隊(duì)強(qiáng)烈抵制，具體原因不詳。最后無(wú)限延期。這里把思路分享下。歡迎拍磚。

　　整個(gè)思路是這樣的，索引都是為查詢(xún)和更新服務(wù)的，但是不合適的索引又會(huì)對(duì)插入和更新帶來(lái)負(fù)面影響。面對(duì)表上現(xiàn)有的索引想識(shí)別那些是有效的不太可能。那么根據(jù)現(xiàn)有的數(shù)據(jù)使用情況重建所有的新索引不就解決了嘛。根據(jù)查詢(xún)生成全新索引，然后和現(xiàn)有對(duì)比，不吻合的全部刪除，原來(lái)沒(méi)有的創(chuàng)建。雖然說(shuō)對(duì)于正在運(yùn)行的系統(tǒng)來(lái)說(shuō)風(fēng)險(xiǎn)還是蠻大的。但是可以做臨界測(cè)試嘛。
　　
具體解決方案如下：

　　首先在熱備的數(shù)據(jù)庫(kù)服務(wù)器上定期抓取緩存的執(zhí)行計(jì)劃（原本想抓取SQL發(fā)現(xiàn)有些SQL實(shí)在摻不忍睹，沒(méi)有自動(dòng)化解析的可能性），然后連同該執(zhí)行的執(zhí)行次數(shù)即表的統(tǒng)計(jì)信息一起down到一個(gè)備用服務(wù)器的數(shù)據(jù)表中。

　　執(zhí)行計(jì)劃積累幾次后，開(kāi)始解析。由于執(zhí)行計(jì)劃是格式良好的XML文件，加上微軟提供執(zhí)行計(jì)劃的XSD文件。我們可以反向推出各節(jié)點(diǎn)對(duì)應(yīng)的SQL謂詞（這個(gè)XSD到現(xiàn)在都沒(méi)找到官方的說(shuō)明，只能反向推出關(guān)聯(lián)）。例如建立索引我們比較關(guān)心三類(lèi)謂詞，分別為：Select，Join，Where。 只要拿到這些我們就能建立良好的索引。原理很簡(jiǎn)單，Join和Where都是索引鍵的依據(jù)，而Select可以斟請(qǐng)?zhí)砑拥絀ndex的Include中。
　　
　　解析的時(shí)候也不是針對(duì)單個(gè)執(zhí)行計(jì)劃，而是將所有執(zhí)行計(jì)劃全分解后進(jìn)行統(tǒng)計(jì)處理。好處就是能夠知道那些表字段被引用的最多，那些是外鍵列。那些數(shù)據(jù)被反復(fù)查詢(xún)。例如可以得出TableA的Col1列在一天的業(yè)務(wù)過(guò)程中被Join了10W次，被Where2W次。而Col2則被Select了10W次，僅僅被Where了100次。這樣我們建立索引的基礎(chǔ)就是基于表的而不是基于單個(gè)查詢(xún)的。最終生成的Index將權(quán)衡查詢(xún)頻率和查詢(xún)的重要性，如果某個(gè)業(yè)務(wù)查詢(xún)特別重要，但執(zhí)行頻率不高我們可以提供權(quán)重，優(yōu)先建立索引。當(dāng)然創(chuàng)建Index還要參考表的數(shù)據(jù)分布以決定Index中字段的順序。

　　好了，準(zhǔn)備工作完成，開(kāi)始建索引。當(dāng)前擁有的條件，表數(shù)據(jù)分布，表字段分別被查詢(xún)引用次數(shù)（Select，Join，Where），以及這些SQL謂詞出現(xiàn)的次數(shù)。根據(jù)這些如何創(chuàng)建索引開(kāi)始的想法是逐個(gè)分析，考慮所有可能性然后創(chuàng)建。發(fā)現(xiàn)這種方式只適合人腦，讓電腦做得先讓電腦的智商增長(zhǎng)到120以上才有可行性。發(fā)現(xiàn)逆向思維這里同樣大有用處，既然不能一下子創(chuàng)建最合適的，那我們就根據(jù)執(zhí)行計(jì)劃得出的組合創(chuàng)建所有的Index組合。凡是Join和Where都放到Index的Key里。例如：
　　select t1.A, t1.B, t1.C, t2.J, t2.k from Table1 t1 Join Table1 t2 on t1.A = t2.j Where t1.A = 'param'

草創(chuàng)的索引就是：

　　Index（A，B）includ(C) 和 Index（j）include（j,k）

關(guān)于Select如果是小數(shù)據(jù)類(lèi)型且Alter的執(zhí)行計(jì)劃中該數(shù)據(jù)修改頻率很小的都放到Include里去進(jìn)去。大數(shù)據(jù)類(lèi)型和修改比較頻繁的就算了。這樣我們剔除相互覆蓋的。部分重疊的，部分重疊到底保留那一個(gè)參考執(zhí)行頻率和查詢(xún)重要性。差異很小的就合并并為一個(gè)，如：

　　1.Index （A，B，C）Include（D）
　　2.Index（A，B，D）Include（C）

直接合并為：

　　Index（A，B）Include（C，D）

當(dāng)然如果Alert的特別少也可以合并成Index（A，B，C，D）這個(gè)要參考C，D字段的修改頻率。和主鍵重疊的剔除。這樣留下的基本上就是我們需要的索引了。
　　
　　對(duì)比現(xiàn)有索引進(jìn)行甄別覆蓋的過(guò)程就略過(guò)。簡(jiǎn)單的拉出來(lái)Create　Index　進(jìn)行解析處理就好了。發(fā)布的時(shí)候很簡(jiǎn)單。寫(xiě)個(gè)腳本在業(yè)務(wù)比較少的時(shí)候做Drop和Create就完成了。項(xiàng)目源代碼因?yàn)樵O(shè)計(jì)到公司的保密問(wèn)題就不上傳了。一個(gè)注意的地方對(duì)于簡(jiǎn)單查詢(xún)的SQL執(zhí)行計(jì)劃緩存的時(shí)候會(huì)比較短且一旦緩存不夠就會(huì)被清理掉。要注意這些SQL的執(zhí)行頻率的誤差。

　　SqlserverR2 XSD：http://schemas.microsoft.com/sqlserver/2004/07/showplan/sql2008/showplanxml.xsd
　
　總結(jié)的節(jié)點(diǎn)映射列舉如下：

　　　　查詢(xún)sql執(zhí)行計(jì)劃都包含在節(jié)點(diǎn)“StmtSimple”中，如果沒(méi)有這個(gè)節(jié)點(diǎn)一般就是其它類(lèi)型的SQL的執(zhí)行計(jì)劃。

　　　　Join關(guān)聯(lián)的節(jié)點(diǎn)和自身類(lèi)型有關(guān)一般包含在Hash，Marger中，如何Join同時(shí)又是Where條件的話則會(huì)出現(xiàn)在SeekKey和Compare節(jié)點(diǎn)中，因?yàn)镴oin的列都是成對(duì)出現(xiàn)，這里很容易識(shí)別，有一個(gè)是參數(shù)（@開(kāi)頭）或常量（type="Const"）則必定是Where條件。
　　　　
　　　　Select最終輸出字段比較容易找到，第一個(gè)OutputList節(jié)點(diǎn)就是。

　　　　需要注意的是有因?yàn)橐话懔忻總€(gè)ColumnReference都包含庫(kù)名，表名，列信息，但是系統(tǒng)表則不會(huì)。注意剔除。

最新評(píng)論