由DB2分頁想到的,關於JDBC ResultSet 處理大資料量

標籤：根據   postgres   內容   first   order by   session   功能   ret   osi   

   最近在處理DB2 ,查詢中，發現如下問題。如果一個查詢 count(*),有幾十萬行，分頁如何?

            select row_number() over (order by fid desc ) as row_number,  other_field

            from   loaddata

           如果這個查詢的結果會返回幾十萬行，如何分頁：

           1  order by fid desc   中 fid 這個欄位一定要建立索引，且建立索引時， 要根據sql中的排序方式保持一致

           2 如何分頁

          方法1    select * from (

                    select row_number() over (order by fid desc ) as row_number,  other_field

                     from   loaddata

              )  a

              where a.   row_number >  500 

              fetch first 100 rows only

              解釋：

                    500  :這個值，根據當前頁數及每頁的數量進行計算得到

                    關鍵在於，定位到起始行

                    而   fetch first 100 rows only  ,假設每頁顯赫  10資料，那麼，用fetch ，是非常有重要的效能提升方法

             方法2 如果用如下的 sql  

                 select * from (

                    select row_number() over (order by fid desc ) as row_number,  other_field

                     from   loaddata

              )  a

              where a.   row_number >  500   and  row_number  <510 

             --- fetch first 100 rows only

              隨著表中數量增大，和翻頁數量的增加，這個查詢的速度是會顯著的下降。

               方法1 在分頁上百萬的資料，只需花費 100多毫秒。

               而方法2 當分頁總數在 20多萬行，查詢row_number在  5000到 5010之間的記錄時時會達到 1分鐘以上下           

 

--------------------------------------------------------------------------------------------------------------------------------------------------

           如果用JDBC查詢這上百萬條資料，速度如何。分批查詢。這合我想到了ResultSet的原理。各個驅動不盡相同。

           用戶端記憶體是否會想當然的溢出？

            於是用JDBC實驗了一下。發現用戶端記憶體並的消耗並沒有顯著提升。

            事實上用JDBC ResultSet查詢如此多的資料,會存在 連線逾時的問題

------------------------------------------------------------------找到如下資料說明 --------------------------------------------------------------------------

 

           問題描述：在通常的三層構架下，客戶通過Browser請求Web伺服器查詢資料庫，而查詢結果是上千條甚至是上百萬條記錄，要求查詢結果傳送到用戶端瀏覽器並分頁顯示。

　　考慮因素：　　

       1. Web伺服器的資源消耗，包括：記憶體（用來儲存查詢結果），資料庫相關資源（資料庫連接對象，ResultSet對象等等）；

　　2. DB伺服器資源的消耗，包括遊標，會話等等。

　　3. 網路開銷，包括與資料庫建立會話，傳輸查詢結果等等。

　　JDBC中的幾個重要Class:

　　A ResultSet object maintains a cursor pointing to its current row of data. Initially the cursor is positioned before the first row. The next method moves the cursor to the next row, and because it returns false when there are no more rows in the ResultSet object, it can be used in a while loop to iterate through the result set.

　　ResultSet是直接在資料庫上建立遊標，然後通過ResultSet的行位置定位介面來獲得指定行位置的記錄。當使用者通過get方法擷取具體紀錄的內容時，ResultSet才從資料庫把所需資料讀到用戶端。

　　Oracle的ResultSet實現似乎會在本機快取使用者讀取過的資料，導致記憶體消耗會隨讀取資料的增加而增加，這樣，如果一次查詢並讀取海量資料，即使讀出資料後馬上丟棄（比如直接寫入檔案），記憶體消耗也會隨查詢結果的增加而遞增。

　　The RowSet interface extends the standard java.sql.ResultSet interface. A RowSet object may make a connection with a data source and maintain that connection throughout its life cycle, in which case it is called a connected rowset. A rowset may also make a connection with a data source, get data from it, and then close the connection. Such a rowset is called a disconnected rowset. A disconnected rowset may make changes to its data while it is disconnected and then send the changes back to the original source of the data, but it must reestablish a connection to do so.

　　RowSet是JDBC2.0中提供的介面,Oracle對該介面有相應實現，其中很有用的是 oracle.jdbc.rowset.OracleCachedRowSet。 OracleCachedRowSet實現了ResultSet中的所有方法，但與ResultSet不同的是，OracleCachedRowSet中的資料在Connection關閉後仍然有效。

　　解決方案一：直接使用ResultSet來處理

　　從ResultSet中將查詢結果讀入collection,緩衝在HttpSession或有狀態bean中，翻頁的時候從緩衝中取出一頁資料顯示。這種方法有兩個主要的缺點：一是使用者可能看到的是到期資料；二是如果資料量非常大時第一次查詢遍曆結果集會耗費很長時間，並且緩衝的資料也會佔用大量記憶體，效率明顯下降。

　　對上述方法的一種改進是當使用者第一請求資料查詢時，就執行SQL語句查詢，獲得的ResultSet對象及其要使用的連線物件都儲存到其對應的會話對象中。以後的分頁查詢都通過第一次執行SQL獲得的ResultSet對象定位取得指定頁的記錄（使用rs.last();rs.getRow()獲得總計錄條數，使用rs.absolute()定位到本頁起始記錄）。最後在使用者不再進行分頁查詢時或會話關閉時，釋放資料庫連接和ResultSet對象等資料庫訪問資源。每次翻頁都只從ResultSet中取出一頁資料。這種方式在某些資料庫(如oracle)的JDBC實現中差不多也是回緩衝所有記錄而佔用大量記憶體，同時速度也非常慢。

　　在用例分頁查詢的整個會話期間，一個使用者的分頁查詢就要佔用一個資料庫連接對象和結果集的遊標，這種方式對資料庫的訪問資源佔用比較大，並且其利用率不是很高。

　　優點：減少了資料庫連接對象的多次分配擷取，減少了對資料庫的SQL查詢執行。

　　缺點：佔用資料庫訪問資源－資料庫連接對象，並佔用了資料庫上的資源－遊標；會消耗大量記憶體；

　　解決方案二：定位行集SQL查詢

　　使用資料庫產品提供的對查詢的結果集可定位行範圍的SQL介面技術。在使用者的分頁面查詢請求中，每次可取得查詢請求的行範圍的參數，然後使用這些參數生產取得指定行範圍的的SQL查詢語句，然後每次請求獲得一個資料庫連接對象並執行SQL查詢，把查詢的結果返回給使用者，最後釋放說有的資料庫訪問資源。

　　這種方式需要每次請求時都要執行資料庫的SQL查詢語句；對資料庫的訪問資源是使用完就立即釋放，不白白佔用資料庫訪問資源。 對特定（提供了對查詢結果集可定位功能的）的資料庫產品，如：Oracle（rowid或rownum ）,DB2（rowid或rownum ()）, PostgreSQL（LIMIT 和 OFFSET）,mySQL（Limit）等。(MS SQL Server 沒有提供此技術。)

　　下面是在oracle下的查詢語句樣本：

　　SELECT * FROM ( SELECT row_.*, rownum rownum_ FROM (...... ) row_ WHERE rownum <= {pageNumber*rowsPerPage}) WHERE rownum_ > {(pageNumber-1)*rowsPerPage}

　　優點：對資料庫的訪問資源（資料庫連接對象，資料庫遊標等）沒有浪費，這些資源的充分重複的利用。

　　缺點：對每次分頁面查詢請求要頻繁的從Web容器中獲得資料庫訪問資源（資料庫連接對象和資料庫遊標）並建立串連；要依賴於具體的資料庫產品的支援。

由DB2分頁想到的,關於JDBC ResultSet 處理大資料量