一種理想的大資料量分頁查詢方案

 本文旨在介紹一種對資料庫中的大資料量表格進行分頁查詢的實現方法，該方法對應用伺服器、資料庫伺服器、查詢用戶端的cpu和記憶體佔用都較低，查詢速度較快，是一個較為理想的分頁查詢實現方案。
1．問題的提出
在軟體開發中，大資料量的查詢是一個常見的問題，經常會遇到對大量資料進行查詢的情境。常見的對大資料量查詢的解決方案有以下兩種：一、將全部資料先查詢到記憶體中，然後在記憶體中進行分頁，這種方式對記憶體佔用較大，必須限制一次查詢的資料量。二、採用預存程序在資料庫中進行分頁，這種方式對資料庫的依賴較大，不同的資料庫實現機制不通，並且查詢效率不夠理想。以上兩種方式對使用者來說都不夠友好。
2．解決思路
通過在待查詢的資料庫表上增加一個用於查詢的自增長欄位，然後採用該欄位進行分頁查詢，可以很好地解決這個問題。下面舉例說明這種分頁查詢方案。
一、在待查詢的表格上增加一個long型的自增長列，取名為“queryId”,mssql、sybase直接支援自增長欄位，oracle可以用sequence和trigger來實現。然後在該列上加上一個索引。添加queryId列的語句如下：
Mssql: [QUERYID] [bigint] IDENTITY (1, 1)
Sybase: QUERYID  numeric(19)  identity www.yueluo.net 
Oracle:
CREATE SEQUENCE queryId_S
         INCREMENT BY 1
         START WITH 1
            MAXVALUE 999999999999999 MINVALUE 1
            CYCLE
            CACHE 20
            ORDER;
CREATE OR REPLACE TRIGGER queryId_T BEFORE INSERT
             ON "test_table"
             FOR EACH ROW
BEGIN
      select queryId_S.nextval into :new.queryId from dual;
END;
二、在查詢第一頁時，先按照大小順序的倒序查出所有的queryId，語句如下：select queryId from test_table where + 查詢條件 +order by queryId desc 。因為只是查詢queryId欄位，即使表格中的資料量很大，該查詢也會很快得到結果。然後將得到的queryId儲存在應用伺服器的一個數組中。 字串9 
三、使用者在用戶端進行翻頁操作時，用戶端將待查詢的頁號作為參數傳遞給應用伺服器，伺服器通過頁號和queyId數組算出待查詢的queyId最大和最小值，然後進行查詢。
算出queyId最大和最小值的演算法如下,其中page為待查詢的頁號，pageSize為每頁的大小，queryIds為第二步產生的queryId數組：
        int startRow = (page - 1) * pageSize;
        int endRow = page * pageSize - 1;
        if (endRow >=queryIds.length)
        {
            endRow = this.queryIds.length - 1;
        }
        long startId =queryIds[startRow];
        long endId =queryIds[endRow];
查詢語句如下：
String sql = "select * from test_table" + 查詢條件 + "(queryId <= " + startId + " and queryId >= " + endId + ")"; 
字串7

3．效果評價
該分頁查詢方法對所有資料庫都適用，對應用伺服器、資料庫伺服器、查詢用戶端的cpu和記憶體佔用都較低，查詢速度較快，是一個較為理想的分頁查詢實現方案。經過測試，查詢4百萬條資料，可以在3分鐘內顯示出首頁資料，以後每一次翻頁操作基本在2秒以內。記憶體和cpu佔用無明顯增長。

本文來自: 月落網(www.yueluo.net) 詳細出處參考：http://www.yueluo.net/Mysql/20070828/1551.html