根據設計慣例查詢的時候主子表通過關鍵詞字段關聯查詢查詢語句如下
select top awordaqueryurlairankatitleabaiduurlaitrafficaitrafficbibaiduindex from zibiao a
with(nolock)
inner join zhubiao b with(nolock) on aword=bword
where aqueryurl=
order by bibaiduindex descairank
發現速度很慢快的時候瞬間慢的時候長達幾分鐘分析sql server的查詢執行計劃如下
分析這個執行計劃圖主要資源開銷是在主表的聚集索引查找應該是子表根據網址找到關鍵詞後再到主表中查找關鍵詞對應的指數的這一步比較耗時
因這個表的更新頻率非常高以為是更新頻率太快導致索引效率降低於是想到用數據庫讀寫分離的方案專門拿了一台備用服務器通過發布訂閱的方法 將這兩張表發布到備用服務器上專門在備用服務器上做查詢發現速度並沒有得到提升
在csdn上發了一個討論帖網友討論很熱烈sql server的很多版主都有參與討論給出的方案也很多有人說是IO讀寫瓶頸於是升級了下服務器硬盤采用raid的固態硬盤內存更是升級到了G不過效果仍舊不明顯同樣的語句慢的時候還是需要幾十秒
有網友給出的方案是建議把主表字段放到子表冗余以減少關聯的資源消耗再結合以上的執行計劃圖確實瓶頸是在主表的的聚集索引查找上於是單獨執行了下子表的irank排序語句
select top awordaqueryurlairankatitleabaiduurlaitrafficaitraffic from zibiao a
with(nolock)
where aqueryurl=
order by airank
發現執行速度非常快幾乎秒開了當然irank字段上有建索引的
測試成功於是將主表的ibaiduindex字段放到子表冗余並且建立好索引(這裡的索引建立是有技巧的)執行語句
select top awordaqueryurlairankatitleabaiduurlaitrafficaitrafficaibaiduindex from zibiao a
with(nolock)
where aqueryurl=
order by aibaiduindex descairank
查詢瞬間出來結果執行計劃如下
這裡拿以上這個sql語句來分析我們該如何建立索引索引建立包括字段的先後順序字段的排序方法include項都很重要任何一個弄錯都不能達到好的優化效果
我們肯定需要建立一個組合索引那麼應該組合字段用哪幾個呢?這裡我的組合字段是queryurl(這個字段第一 因為我們最先是根據這個字段進行篩選的)ibaiduindexirank
這裡要注意下因為我的固定排序就是ibaiduindex descirank asc所以字段的排列順序應該是queryurlibaiduindexirank同時排序的類別是ibaiduindex desc irank asc這裡我一開始沒注意ibaiduindex的排序是asc結果發現執行上面那個SQL語句仍然需要秒
索引包含項也很重要如果不用包含項索引查找到主鍵後 還要根據主鍵去查找其他字段所以我們需要設置索引包含性列把除掉索引字段中剩余的其他字段都加進去
優化後的效果就如上面的執行計劃圖所示一次非聚集索引查找就找到我們的數據而且都不需要排序耗時因為我們的索引已經按照順序排列好了當然這裡要說明下索引包含確實好用但是代價就是磁盤的空間加了索引包含項數據庫空間增加了幾十個G
From:http://tw.wingwit.com/Article/program/SQLServer/201311/22262.html
