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