|
隨著Internet技術的發(fā)展，Web已越來越多的被應用到各行各業(yè)。傳統(tǒng)的基于大機或C/S結構的應用也正逐漸的為B/S（Browser/Server）結構所代替。而數(shù)據(jù)庫，作為保存著大量信息的容器，使得WEB應用能夠提供更加豐富多彩，及時、個性化的信息。在WEB應用中，我們經(jīng)常遇到需要從數(shù)據(jù)庫搜索出滿足某個特征的數(shù)據(jù)記錄，再顯示給特定用戶。常常這些滿足條件的記錄如此之多，一方面在同一個頁面顯示顯得異常臃腫而不切實際，另一方面用戶通常也不會對他們都感興趣，他們似乎更關心按一定規(guī)則排序出現(xiàn)在某些開始位置的若干記錄。這就要求我們對滿足條件的數(shù)據(jù)進行分頁，將用戶更關心的記錄放在首頁，同時給予是否繼續(xù)瀏覽（或跳躍式閱讀）到指定頁甚至最后一頁的自由。在這里，我們希望和大家討論一下使用ORACLE數(shù)據(jù)庫時的WEB分頁方法。 我們說，一個好的分頁方法，它應當滿足以下幾個要求： 1. 數(shù)據(jù)庫處理的數(shù)據(jù)量最??； 2. 數(shù)據(jù)庫與WEB應用服務器之間的數(shù)據(jù)量傳輸最??； 假定我們有如下的業(yè)務：行業(yè)產(chǎn)品表，10萬記錄，字段包括產(chǎn)品名稱，所在行業(yè)，市場價格。要求選擇某個行業(yè)時，列出該行業(yè)下所有產(chǎn)品，并按產(chǎn)品名稱排序，超過20條的，按每頁20條分頁： rudolf@TEST902>create table t nologging 2 as select object_name product_name,mod(object_id,4)*10 category, 3 object_id price,rpad(‘a(chǎn)‘,300,‘b‘) supplier 4 from all_objects order by 2,1 5 / Table created. rudolf@TEST902>select count(*) from t; COUNT(*) ---------- 21110 用以上語句，我們快速生成了一個行業(yè)產(chǎn)品表，其中all_objects為oracle的一個系統(tǒng)表（我們常?？梢允褂妙愃频姆椒ㄉ蓽y試數(shù)據(jù)）。接下來，我們創(chuàng)建了索引，并為使用CBO分析了表，分析顯示該表共用去1039個數(shù)據(jù)塊： rudolf@TEST902>create index t_category_pname_ind on t (category,product_name) 2 nologging 3 tablespace indx 4 / Index created. rudolf@TEST902>analyze table t compute statistics 2 for table 3 for all indexes 4 for all indexed columns 5 / Table analyzed. rudolf@TEST902>select table_name,blocks,empty_blocks from user_tables where table_name = ‘T‘; TABLE_NAME BLOCKS EMPTY_BLOCKS ------------------------------ ---------- ------------ T 1039 113 為了便于討論，我們先來看一下傳統(tǒng)的做法: rudolf@TEST902>select * from 2 ( select rownum rnm, a.* from 3 ( select * from t where category = &category_id 4 order by product_name 5 ) a 6 ) where rnm between &minrnm and &maxrnm 7 這里我們使用了三個變量，其中category_id表示用戶感興趣的行業(yè)，而minrnm,maxrnm則來模擬web程序控制分頁時傳入的最小、最大行號。我們希望選出行業(yè)為20，屬于第289頁的所有產(chǎn)品信息。我們猜測上述語句將按以下步驟執(zhí)行： 1. 取出所有滿足category=&category_id的記錄 2. 按product_name進行排序 3. 在排序完畢的結果集中取出第&minrnm到&maxrnm記錄之間的數(shù)據(jù) rudolf@TEST902>set autot trace rudolf@TEST902>/ Enter value for category_id: 20 Enter value for minrnm: 4981 Enter value for maxrnm: 5000 20 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=436 Card=5263 Bytes=1094704) 1 0 VIEW (Cost=436 Card=5263 Bytes=1094704) 2 1 COUNT 3 2 VIEW (Cost=436 Card=5263 Bytes=1026285) 4 3 SORT (ORDER BY) (Cost=436 Card=5263 Bytes=1010496) 5 4 TABLE ACCESS (BY INDEX ROWID) OF ‘T‘ (Cost=284 Card=5263 Bytes=1010496 ) 6 5 INDEX (RANGE SCAN) OF ‘T_CATEGORY_PNAME_IND‘ (NON-UNIQUE) (Cost=31 C ard=5263) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 284 consistent gets 0 physical reads 0 redo size 1829 bytes sent via SQL*Net to client 514 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 20 rows processed 我們可以根據(jù)執(zhí)行計劃第二列的數(shù)字來閱讀計劃，即數(shù)字大的最先執(zhí)行，如“5 index (range scan)”，數(shù)字相等時，按從上到下的順序執(zhí)行。上述執(zhí)行計劃顯示了與我們估計相同的順序，我們看到滿足where條件的記錄一共5263條左右（第4步中的 card=5263），它們?nèi)勘蝗〕?，并參與排序（第3步），并在將結果集返回給用戶前，一直在處理所有的5263條記錄。然而事實上用戶似乎只關心本頁即20條記錄。顯然它與我們關于數(shù)據(jù)庫處理量最小的要求相距甚遠。在分析部分，284個一致讀進一步說明數(shù)據(jù)庫處理了所有滿足條件的記錄（整個表占1039個數(shù)據(jù)塊，共4個擁有相近產(chǎn)品數(shù)的行業(yè)，則每個行業(yè)約占259個數(shù)據(jù)塊）。 現(xiàn)在，我們把上述語句換成： rudolf@TEST902>select * from t 2 where category = &category_id 3 order by product_name 4 將滿足條件的所有記錄取到客戶端（在這里為WEB應用服務器），然后利用編程語言對結果集分頁。以JAVA為例，可以使用ResultSet對象方法absolute直接定位記錄而方便地將結果集分頁。然而很顯然，它甚至滿足關于數(shù)據(jù)庫與WEB應用服務器之間的數(shù)據(jù)量傳輸最小的要求，很多情況下將明顯影響性能，嚴重時甚至會導致WEB應用服務器一端內(nèi)存溢出。言歸正傳，我們開始引入我們的方法。 方法一：同分析傳統(tǒng)做法類似，我們先列出我們的方法： rudolf@TEST902>select * from 2 ( select rownum rnm, a.* from 3 ( select * from t where category = &category_id 4 order by category,product_name 5 ) a where rownum <= &maxrnm 6 ) where rnm >= &minrnm 7 與傳統(tǒng)做法不同，我們把對最大行號的判斷從第三層移到了第二層。改變雖然簡單，然而它表達了一個完全不同的執(zhí)行意圖。內(nèi)部視圖： select rownum rnm, a.* from ( select * from t where category = &category_id order by category,product_name ) a where rownum <= &maxrnm 是8i引入的新操作，在執(zhí)行計劃中，它體現(xiàn)為stopkey。這種操作專門為提取TOP n的需求做了優(yōu)化。它需要排序字段預先建有索引，由于索引是已排序好的結構，因此取TOP n的問題，就變?yōu)閺乃饕兄苯訌念^提取n個索引關鍵字，然后再根據(jù)索引就可快速的找到記錄并返回給用戶。從而有效避免了檢索全部記錄的情況。 rudolf@TEST902>set autot trace rudolf@TEST902>set verify off Enter value for category_id: 20 Enter value for maxrnm: 20 Enter value for minrnm: 1 20 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=284 Card=20 Byte s=4160) 1 0 VIEW (Cost=284 Card=20 Bytes=4160) 2 1 COUNT (STOPKEY) 3 2 VIEW (Cost=284 Card=5263 Bytes=1026285) 4 3 TABLE ACCESS (BY INDEX ROWID) OF ‘T‘ (Cost=284 Card= 5263 Bytes=1010496) 5 4 INDEX (RANGE SCAN) OF ‘T_CATEGORY_PNAME_IND‘ (NON- UNIQUE) (Cost=31 Card=5263) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 7 consistent gets 0 physical reads 0 redo size 1848 bytes sent via SQL*Net to client 514 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 20 rows processed 應將count(stopkey)操作與table access(by index rowid)結合起來看，這樣一來，table access(by index rowid)實際上只處理了&maxrnm條記錄，這里為20條。它的執(zhí)行計劃可以解釋為： rnm := 1; for rec in (select * from t where category = &category_id order by category, product_name) loop rnm := rnm + 1; if rnm > [$maxrnm then exit loop] end if; fetch rec; end loop; filter rec where rownum < [$minrnm] 與傳統(tǒng)方法相比，它大大減小了數(shù)據(jù)庫處理的壓力：284個一致讀減小為7個，性能因此得到了改善。然而也許你注意到了，當用戶不停的向后翻頁，使得&maxrnm逐漸接近滿足條件的記錄數(shù)時，它的性能 也漸漸降低到與傳統(tǒng)方法相近的水平： rudolf@TEST902>set autot trace statistics rudolf@TEST902>select * from 2 ( select rownum rnm, a.* from 3 ( select * from t where category = &category_id 4 order by category,product_name 5 ) a where rownum <= &maxrnm 6 ) where rnm >= &minrnm 7 / Enter value for category_id: 20 Enter value for maxrnm: 5000 Enter value for minrnm: 4981 20 rows selected. Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 275 consistent gets 0 physical reads 0 redo size 1829 bytes sent via SQL*Net to client 514 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 20 rows processed rudolf@TEST902> 我們看到，當用戶瀏覽到第249頁時，這種方法共使用了275個一致讀，與傳統(tǒng)方法的284個一致讀已很接近了。幸運的是，在很多應用中，98%的用戶將只關心前5頁的數(shù)據(jù)，使得這些應用仍能得益于這個方法。當我們把order by子句改為order by ... desc，同時創(chuàng)建逆索引，我們甚至可以把某些用戶關心最后5頁數(shù)據(jù)的需求改變?yōu)殛P心前5頁。盡管如此，還是有某些應用，用戶瀏覽頁面更可能是隨機的，這時我們就可以用到第二種方法： 方法二： rudolf@TEST902>select * from t 2 where rowid in 3 ( select rid from 4 ( select rownum rno,rowid rid from 5 ( select rowid from t 6 where category = &category_id 7 order by category,product_name 8 ) where rownum <= &maxrnm 9 ) where rno >= &minrnm 10 ) 11 在這一方法中，我們考慮到索引與表相比，身材上大大小于后者（我們可以把它看作一個小表），因此我們試圖先在索引中搜索出某頁記錄的物理位置，然后根據(jù)這些物理位置（rowid）在表中直接取出相應的記錄，我們認為它將消除前一種方法中index range scan所有滿足條件記錄時帶來的高成本（到某一刻CBO甚至認為它高于FULL TABLE SCAN而選擇FULL TABLE SCAN）。 Enter value for category_id: 20 Enter value for maxrnm: 5000 Enter value for minrnm: 4981 20 rows selected. Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=5054 Card=5000 Bytes=1095000) 1 0 NESTED LOOPS (Cost=5054 Card=5000 Bytes=1095000) 2 1 VIEW (Cost=31 Card=5000 Bytes=100000) 3 2 SORT (UNIQUE) 4 3 COUNT (STOPKEY) 5 4 VIEW (Cost=31 Card=5263 Bytes=36841) 6 5 INDEX (RANGE SCAN) OF ‘T_CATEGORY_PNAME_IND‘ (NON-UNIQUE) (Cost=31 C ard=5263 Bytes=178942) 7 1 TABLE ACCESS (BY USER ROWID) OF ‘T‘ (Cost=1 Card=1 Bytes=199) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 50 consistent gets 0 physical reads 0 redo size 1551 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 20 rows processed 我們可以看到語句的執(zhí)行邏輯： rnm := 1; for rec in (select * from t_category_pname_ind where category = &category_id order by category, product_name) loop rnm := rnm + 1; if rnm > [$maxrnm then exit loop] end if; fetch rowid; end loop; filter rowid array where rownum < [$minrnm] select * from t where rowid in ( rowid array ); 基本上，無論用戶瀏覽哪頁，數(shù)據(jù)庫的數(shù)據(jù)處理量都較為相近，約為index fast full scan的成本加上20次access by rowid的成本。與前一種方法相比，當用戶只瀏覽前幾頁時，可能它的成本相對稍大，然而隨著用戶逐頁往后瀏覽，它的成本優(yōu)勢也迅速的顯現(xiàn)出來。同樣瀏覽第4981-5000條記錄，我們看到方法一產(chǎn)生了275個一致讀，而本方法僅僅產(chǎn)生了50個。對于我們“數(shù)據(jù)庫處理量最小”的要求而言，可說是大大邁進了一步。 綜上所述，由于用戶瀏覽特點、習慣不同，我們可以采用不同的分頁方法，以便更有效的利用資源。 我接觸過的最厲害的是Ebay的系統(tǒng)，他們的瀏覽量太大，以至于使用數(shù)據(jù)庫來做這種排序的功能，系統(tǒng)無法負擔。 結果他們采用的是直接在WebServer上面進行排序。 還有一個思路，就是如果訪問量實在很大，可以根據(jù)類別，把不同的列別干脆放到不同的表的里面，這樣，每個表的物理大小大大減小，每次的讀取，都會大大減少，從而降低數(shù)據(jù)庫服務器的壓力，提高相應時間。 如果頁面的訪問量非常大，但是更新非常小的話，還可以采用另外一個技術，一個表對應就是多個表，同樣的內(nèi)容，冗余到不同的表的里面，每個表都采用IOT技術（Index Organized Table)，原來的主鍵 ＋ 需要排序的字段，這樣，每次按照不同的排序方法，都可以非常迅速的得到需要的答案。但是這個需要應用程序?qū)懙纳晕碗s一點、 對于數(shù)據(jù)倉庫系統(tǒng).可以使用分析函數(shù)來進行一些簡化處理,在以后將給大家介紹 |
|
|
來自： usejava > 《數(shù)據(jù)庫》
