一高級掃描使用舉例
通常情況下在數據查詢的時候數據庫會利用索引或者通過全表掃描來查找數據但是如果需要的數據在數據庫中存儲不連續或者需要查找的記錄比較多時此時索引的效果就會大打折扣在這種情況下數據庫查詢優化器可能會采用全表掃描來代替索引但是眾所周知全表掃描的效率是比較低下的為此在SQL Server數據庫的企業版中提出了一個高級掃描的處理方式簡單的說高級掃描可以讓多項查詢任務共享完全表掃描筆者先給大家舉一個例子然後再跟大家談談隱藏在其背後的秘密
如在上圖中一個表中的記錄比較多有頁用戶甲需要查詢這個表中的記錄假設其采用了全表掃描當數據庫查詢到頁的時候用戶乙也需要這個表中的數據那麼又觸發了一個全表掃描此時如果沒有采用高級掃描技術的話則用戶乙的SQL語句必須要等到用戶甲的執行完畢後才會執行而如果采用了高級掃描技術的話則數據庫在從頁開始的全表掃描中會把掃描的結果分成兩個副本分別給用戶甲與乙然後當第頁的時候用戶丙也參與進來了同理數據庫引擎會把從頁開始的掃描結果分為三個副本分別給三個用戶當整個表掃描完成之後數據庫引擎就會把結果返回給用戶甲然後再從頭開始掃描當掃描到頁的時候就會把上次掃描的頁到頁的結果合並起來然後返回給用戶乙掃描到頁的時候就會把與上次掃描到的結果合並起來返回給用戶丙
可見如果在不同高級掃描功能的話則不同用戶在不同時刻的查詢請求可能需要對某個表進行全表掃描三次而在上面這個案例中則知需要對這個表掃描次都不到為此當多個對同一個表進行全表掃描時高級掃描工具可以明顯提高數據庫的運行性能
二高級掃描實現的秘密
可見高級掃描其主要就是通過共享全表掃描技術來實現的也就是說當SQL語句的執行計劃需要掃描表中的數據頁(即全表掃描)並且數據庫引擎檢測到其他查詢執行計劃正在掃描這個表中的時候(如上例中用戶乙丙參與進來)則數據庫引擎就會在第二個掃描的當前位置將第二個掃描插入到第一個掃描中(此時數據庫引擎會會把掃描的結果產生一個副本)數據庫引起會一次讀取一頁並加每一頁的行傳遞給多個執行計劃一直到當前掃描結束
此時第一個掃描(用戶甲)已經完全結束數據庫引擎就會把掃描的結果傳遞給用戶甲的進程但是此時數據庫乙還不能夠把結果返回給用戶乙因為在用戶甲開始查詢到用戶乙遞交SQL語句中間可能會有用戶對前面幾頁的數據進行修改為此數據庫引擎需要對先前的頁進行重新掃描以防止數據的誤讀為此第二個查詢計劃必須發起第二個全表掃描檢索第二個執行計劃加入第一次掃描正在進行的掃描之前讀取的數據頁即第二個執行計劃的掃描將繞回到第一個數據頁並從這裡開始掃描直到其加入到第一個掃描時的位置然後數據庫引擎會把掃描到的結果返回給第二個查詢計劃依次類推在實際工作中可以按這種方式組合任意數量的掃描其實這種掃描很想走馬燈為此我們又把高級掃描戲稱為全表掃描可見在這種情況下如果多個用戶在一次全表掃描的過程中查詢同一個表則可以減少全表掃描的次數如果在沒有高級掃描的情況下像上面的用戶甲乙丙都必須要爭用緩沖區空間並因此導致硬盤或者內存的爭用等等然後數據庫引擎會分別為每一個用戶讀取依次相同的頁而不是每次讀取的結果有多個用戶共享顯然跟高級掃描比起來這種處理方式其效率會低很多
三高級掃描的弊端與解決方式
雖然高級掃描會提高數據庫的查詢性能但是這種處理機制也會有一個弊端即會導致查詢結果記錄順序的混亂如上面這個例子中如果三個用戶采用的都是同一個查詢語句的話則其最後返回的結果雖然記錄的內容是相同的但是顯示的記錄順序是不同的(假設沒有采用排序語句)這可能會給用戶一種誤解以為各自查到的是不同的內容為什麼會產生這種情況呢?為了說們這個問題的原因筆者就對表中的內容進行簡化假設某一張表中有三條記錄序號分別為
現在用戶甲需要查詢這個表中的內容進行了一次全表掃描當第一條記錄查詢完畢之後用戶乙也需要查詢這個表從這次開始的後續查詢中數據庫引擎會把結果同時發送給用戶甲與乙兩個查詢計劃也就是說用戶乙此時掃描的第一個結果是序號為的記錄然後用戶丙又插了進來那麼這個時候數據庫引擎返回給用戶丙執行計劃的第一條記錄就是序號為的記錄了第一次掃描完畢後再重新進行第二次掃描然後把序號為的記錄返回給用戶乙最後用戶甲顯示的記錄順序為;而用戶乙顯示的記錄順序為;用戶丙顯示的記錄順序為當記錄比較少的時候用戶還可以一目了然的指導查詢結果是相同的只是順序顛倒了而已但是如果記錄比較多的情況下則用戶丙很可能會誤認為其找到的記錄跟甲是不同的因為順序混亂所以不能夠清楚的判斷所查找的記錄是否相同
為此在實際工作中需要克服這個弊端最簡單的方式就是采用order by語句對查詢的結果進行掃描但是眾所周知對記錄進行排序會增加數據庫額外的開銷會抵消高級掃描所帶來的性能提升的效果故通常情況下對於可能需要用到高級掃描的SQL語句不會采用order by等排序語句除非用戶非常明確的有這方面的需要才會把這個語句加入進去另外需要注意的是有些匯總語句如Group By等也會對記錄進行自動排序這也會增加額外的負擔但是一般來說即使是需要對查詢結果進行排序那麼排序過程中的開銷相比多次全表掃描的開銷來說還是要小的多也就是說在高級掃描後進行排序來解決這個記錄顯示順序不一致的情況仍然是可行的
四影響高級掃描效果的因素
如上的分析中在一個查詢計劃的執行過程中如果越多的查詢計劃插入到其中來那麼這個高級掃描技術的效果就越佳相反如果一個查詢計劃完成後仍然沒有用戶加入到這個查詢計劃中那麼這個高級掃描的功能就根本沒有發揮出來此時查詢就只是一個簡單的全表掃描為此對這個高級掃描的效果直接跟用戶的參與度相關如果在一個比較短的時間間隔內比較多的用戶發起了對一個表的查詢那麼高級掃描的效果才能夠體現出來為此數據庫管理員需要知道並不是在任何時候數據庫系統上實現高級掃描就可以實現比較高的數據庫性能而是需要跟數據庫的實際應用以及員工的作業有關
為此企業如果比較多的用戶需要對某張表進行查詢的時候那麼就需要考慮是否能夠采用高級掃描如在一個ERP系統中其產品信息有幾百萬條有多個用戶需要查詢這個產品信息表中的內容需要把查票信息導出來以作他用此時各個部門的用戶如果在前後時間間隔不是很大的情況下對這個表發起查詢作業那麼此時就可以利用高級掃描工具來共享掃描對結果減少全表掃描此時提高掃描結果
除了用戶人數之外還需要注意的是記錄的內容多少也跟這個高級掃描的效果有關如高紀錄比較到則這個全表掃描的時間就比較長而執行計劃長了則在這個執行計劃的執行過程中參與的用戶可能會越多那無疑也可以提高高級掃描的效果此時可以起到一個累加的效果用戶總的等待時間會隨著參與到這個查詢計劃中來的用戶數量而減少人數越多用戶總的等待時間比全表掃描需要花費的時間少的會更多
From:http://tw.wingwit.com/Article/program/SQL/201311/16355.html
