主流數據庫分頁查詢介紹，主流數據庫分頁

主流數據庫分頁查詢介紹，主流數據庫分頁

1 背景概述

     由於在項目中需要在頁面上顯示數量非常多的數據， 在進行數據庫查詢時首先會把所有的數據都查詢出來，然後在進行顯示，這時候分頁查詢的操作就必不可少了，本文介紹Mysql、Oracle、sql Server 三種數據庫進行分頁查詢的用法。

2 預期讀者

1. 數通暢聯內部員工

2. 廣大計算機愛好者

3 名詞解釋

• 分頁查詢

    就是將將過多的結果在有限的界面上分多頁來顯示，一般將分頁查詢分為兩類：邏輯分頁、物理分頁。
    邏輯分頁是在用戶第一次訪問時，將數據庫的所有記錄全部查詢出來，添加到一個大集合中，然後存放在session對象，再通過頁碼計算出當前頁需要顯示的數據內容，存儲到一個小的list的集合中，並將其存儲到request對象中，跳轉到JSP頁面，進行遍歷顯示。 當用戶第二次訪問時，只要不關閉浏覽器，還會從session中獲取數據，來進行顯示。因為此種方法是在內存的session對象中進行計算分頁顯示的，而不是真正的將我們數據庫進行分頁的,所以叫做邏輯分頁。
    缺點：如果需要查詢的數據量過大，session將耗費大量的內存；因為是在session中獲取數據，如果第二次或者更多此的不關閉浏覽器訪問，會直接訪問session，從而不能保證數據是最新的。
    優點：統一代碼處理方式，較容易跨數據庫做遷移。
    物理分頁，使用數據庫自身所帶的分頁機制，例如，Oracle數據庫的rownum，或者Mysql數據庫中的limit等機制來完成分頁操作。因為是對數據庫的數據進行分頁條件查詢，所以叫物理分頁。每一次物理分頁都會去連接數據庫。
優點：數據能夠保證最新，由於根據分頁條件會查詢出少量的數據，所以不會占用太多的內存。

• CTE（Common Table Expression，公用表表達式）

    該表達式源自簡單查詢，可以認為是在單個 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 語句的執行范圍內定義的臨時結果集。CTE 與派生表類似，具體表現在不存儲為對象，並且只在查詢期間有效。與派生表的不同之處在於，CTE 可自引用，還可在同一查詢中引用多次。

4 實現思路

    通過物理分頁的方法進行數據庫查詢。

5 實現步驟

    首先通過開發平台新建一個工程，使用新工程中的系統日志模塊作為樣例，進行mysql和oracle的分頁查詢功能

5.1 Mysql

• 在數據庫中進行操作：

    mysql的分頁查詢是最簡單的，借助關鍵字limit即可實現查詢，查詢語句通用形式：

select o.* from (sql) o limit firstIndex,pageSize

    其中的sql可以是單表查詢的結果也可以是多表查詢的結果
    firstIndex為顯示結果的起始位置（mysql是從0作為起始位置的）
    pageSize為顯示記錄數
    直接對表進行查詢如下，我們可以看到查詢時間是0.005s


    采用分頁查詢，一頁顯示15條數據，查詢時間是0.001s

• 在工程中體現：

    首先在工程中找到SystemLogQueryImpl.java這個類


    其中有一點需要注意，方法的返回值需要是PageList而不是List


    在開發平台中這個類是日志功能的服務實現類，


    其中，ec_p為頁數，ec_rd為顯示記錄數
    staratNum為起始索引，endNum為結束索引
    由於MySQL的起始索引是從0開始的，需要對得到的起始索引減一，顯示記錄數的計算方法為： 結束索引-起始索引+1
    在sqlMap中的體現如圖：


    在頁面上的顯示：


    可以在頁面選擇顯示的頁碼和記錄數

5.2 Oracle

    Oracle的查詢方法有兩種：ROWNUM、row_number()

5.2.1 ROWNUM

• 在數據庫中進行操作：

    查詢語句通用形式：

select * from(select o.*,ROWNUM num from(sql) o where ROWNUM<=(endIndex)) where num>=firstIndex

    直接對表進行查詢，耗時16msecs


    采用分頁算法進行查詢是，耗時7msecs

• 在工程中體現：

    找到對應的服務實現類：


    服務實現類的內容：


    startNum為起始索引，endNum為結束索引
    因為ROWNUM方法中使用的兩個參數一個是起始索引，一個是結束索引，所以可以直接使用。
    在sql中的體現：


    在頁面上的效果：

5.2.2 row_number()

    查詢語句通用形式：

select * from(select * from(select t.*,row_number() over(order by orderColumn) as rownumber from(sql) t) p where p.rownumber>firstIndex) where rownum<=pageSize

    直接對表進行查詢，耗時22msecs


    使用分頁查詢語句進行查詢，耗時12msece

• 在工程中體現：

    服務實現類的內容和ROWNUM一樣，區別在sql中


    由於在sql中添加了order by排序函數，查詢速率會變慢，所以在開發平台中不采用這種方法。
    對於oracle的分頁查詢，他們的主要區別是:使用rownum進行排序的時候是先對結果集加入偽列rownum然後再進行排序，而函數row_number()在包含排序從句後是先排序再計算行號碼。

5.3 Sqlserver

    由於sqlserver版本比較多，分頁查詢的方式也有不同之處

5.3.1 Sqlserver 2005/2008中使用row_number()

    查詢通用形式：

SELECT * FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY orderColumn) AS RowNumber FROM tableName) EmployeePage WHERE RowNumber > =startIndex AND RowNumber <= endIndex ORDER BY orderColumn
GO

    直接查詢表，如圖
 

    使用分頁查詢，如圖

5.3.2 SQL 2005/2008用CTE的方式實現

     查詢通用形式：

WITH EmployeePage AS (SELECT *,ROW_NUMBER() OVER (ORDER BY orderColumn) AS RowNumber FROM tableName)
SELECT *FROM EmployeePage WHERE RowNumber > =firstIndex AND RowNumber <= endIndex ORDER BY orderColumn
GO

     使用分頁查詢，如圖：

5.3.3 SQL SERVER 2012支持了OFFSET

    查詢通用形式，如下：

SELECT * FROM tableName
ORDER BY orderColumn
OFFSET （page-1） ROWS FETCH NEXT size ROWS ONLY

    Page指需要顯示的頁數
    Size指需要顯示的記錄數
     由於本人現在沒有sqlserver 2012的數據庫，暫時無法做性能的測試

6 附件及說明

    附件中有兩個樣例工程pagingquery4mysql和pagingquery4oracle，使用最新版的aeai dp開發平台導入後查看使用。

主流數據庫分頁查詢介紹文檔及附件       下載