詳解數據庫分頁操作
導讀：數據庫操作過程中有通用的分頁存儲過程，高效的分頁存儲過程。但是，這些并沒有從根本上解決性能問題。我們知道對于相同的查詢，如果你限制每頁返回10條記錄和每頁返回20條記錄比，雖然10條記錄在網絡和返回結果時會比20條記錄要稍稍占一點優勢。但是它要花比20條記錄時2倍的訪問次數，因此從總的資源消耗來看10條記錄會占用更多的資源。但是用戶的操作你永遠是無法預測的，它可能只是看了第1頁然后就退出了。我想一般用戶也很少會去查看第20頁之后的信息吧，除非他是釣魚愛好者！因此，在確定每頁多少條記錄時沒有標準。同時，一般的分頁過程的查詢條件都是動態的，用戶可以任意的排序。因此，這樣的查詢你無法確定應該在哪些字段上創建索引合適。這樣的查詢一般來說都是很低效的。因為動態，所以你的分頁過程可能每次都需要重新編譯才能得到最優的執行計劃。
那么是不是我們把每頁返回的記錄數調整的越大越好，或是一次把全部的結果返回給客戶？我們知道SQLServer會把最終的結果保存到數據緩沖區中。你每次請求的SQL語句在執行之前會在服務端預先分配估計結果集大小的可用物理內存，除非你查詢的結果集已經全部存在于緩存中了。如果遲遲不能預先分配能夠保存最終結果的可用物理內存，則這個查詢最終就會以超時而結束。同時，分頁過程中一般都會對結果集進行排序。而排序、Hash聯結、Hash聚合等操作都需要占用大量內存，這無疑是雪上加霜。如果你使用的是SQL2005，在SQLSERVER的安裝目錄下的LOG文件夾下會看到默認跟蹤所創建的trc文件，如果你看到Sort Warning、Hash Warning，說明你排序的結果集太大了，排序操作正在等待分配可用的內存。或是hash操作時發生了遞歸哈希聯接或哈希援助，詳見聯機文檔。
在數據庫的設置中有兩個選項：最小查詢內存和查詢等待時間，我們一般都不需要去調整這兩個選項。前者就是你的查詢在排序、Hash等需要額外內存操作時，應該分配到的最小可用內存。后者是控制這個查詢在沒有分配到相關的資源時所等待的最長時間。如果是-1，則等待25倍于估計執行時間的時長。否則，超時退出。
有時候，你在測試機上運行的SQL語句是很高效的。但是，當正式服務器資源緊張時，你的這些高效SQL語句將會以一種不可思議的方式執行，因為SQL優化引擎所選擇的最優執行計劃是根據服務器當前負載而定的。歸根結底，都是因為內存不足導致你的查詢在事先分配可用內存時產生等待。而內存不足的原因很大程度上是因為有大量的表掃描所引起的。如果生產機同時為幾個系統提供服務，如果有一個系統的SQL很糟糕。它就會把其它的系統都拖跨。面對這樣的系統你也只能望洋興嘆了！
關于分頁過程中top的使用，我在前面的總結中已經提到過。因為top操作是非關系操作，我們應該限制非關系操作的結果集大小。因此你應該先根據排序的字段，把排序字段所在表的主鍵值查找出來后，再去和其它的表關聯查找相關信息。詳見 對數據進行排序 的第二部分介紹。
基于上面介紹的頁記錄數和訪問次數之間的矛盾，取一個折中的辦法。我們能不能在用戶第一次查詢時把返回的結果保存到一個被持久化的表中呢？表的名稱你可以用登錄用戶的名稱加一些什么標志。也許你會擔心向這些用戶表中插入記錄會影響到數據庫的日志操作？我們一般都會使用SELECT INTO來創建這些用戶表。如果數據庫的恢復模式為FULL，頻繁的寫日志操作被排隊，肯定會對性能造成負面影響。那么我們可不可以為這些用戶表單獨創建一個數據庫，這個數據庫的恢復模式simple。此時的SELECT INTO會按最小日志記錄，因此不會受日志的太大影響。你的分頁的存儲過程中引用此數據庫中的相關的表。
SELECT TOP 400 C1.*,ROW_NUMBER() OVER(ORDER BY C1.CompanyName) AS Line
INTO tom_fu FROM dbo.Customers C1
我之所以加了一個400是不想發生太多的I/O操作，就像上面說的用戶一般不會查看第20頁后的記錄。但如果你要返回總的記錄數用于計算總共的頁數時，使用TOP n進行填充表時就不行了。你可能只有再運行一次查詢count()的操作了，即便這樣也不要帶著order by來取count。如果用戶不是特別關心記錄的總數，最好就是忽略掉這一操作。或者你根據你查詢返回的記錄數多少，有計劃的把TOP n省略掉，把全部的記錄都填充至臨時的用戶表中。有了ROW_NUMBER()的編號，你就可以找到相應的頁的記錄。這時，你的過程里不但要傳入每頁的記錄數、當前的頁數還有查詢的條件之外，還要另外傳入一個是否創建這個用戶表的標志。當用戶改變了查詢條件、排序規則時重新查詢原始表用于創建這個臨時用戶表。如果排序規則的改變不會影響結果集，這時你也可以加一排序規則改變的標志，直接對已經查詢出的結果進行一次排序。不管怎樣，接下來的查詢，將只對這個臨時用戶表進行查詢，從而提高了查詢速度。因為這個臨時創建的用戶表記錄數并不多，表掃描這時和索引查找性能不相上下。同時，如果你要對這個臨時表創建索引，也會增加額外的資源開銷，因此我沒有想在上面創建索引的想法。當查詢不同的頁時，直接對表進行掃描就可以了。然后，我們能不能把已經返回給客戶的頁面記錄緩存起來？這時，當然不能用session，因為這要占用大量WEB服務器內存。你只能把返回的結果保存到客戶的機器上，cookie看來是個不錯的選擇。通過編程的方式來記錄這些記錄所在的頁面。這時，你首先在cookie中判斷此頁內容是否存在，如果是已經訪問過的頁面，不用再訪問服務器。用戶高興，服務器也輕松！
上面是創建表相關的操作，那如何刪除這些臨時創建的用戶表呢？可以在頁面跳轉時，可以在session過期時，可以在你確定用戶不在需要這些臨時用戶表時發送一個刪除此臨時用戶表的命令。但是，如果用戶意外關閉時，可能這個臨時的用戶表就不可能被我們顯示的刪除了。因此，可能還需要一個類似.NET垃圾收集的機制來定期的刪除這些不再使用的臨時用戶表。可以通過程序，也可以通過SQLServer的作業，我們知道你創建的表在sys.tables中都會得到它的create_date和modify_date，我們可以用來清除這些被物化的表。不管怎樣，你都應該想辦法確保盡早的刪除這些臨時用戶表，以防止占用太多的磁盤空間。
這就是我要為大家介紹的數據庫分頁操作的全部內容，相信通過上文的學習，現在大家對數據庫分頁的操作已經有了很多的了解，希望大家都能夠從中有所收獲。
【編輯推薦】
