主流數據庫分頁查詢介紹
1 背景概述
由於在項目中需要在頁面上顯示數量非常多的數據, 在進行數據庫查詢時首先會把所有的數據都查詢出來,然後在進行顯示,這時候分頁查詢的操作就必不可少了,本文介紹Mysql、Oracle、sql Server 三種數據庫進行分頁查詢的用法。
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的數據庫,暫時無法做性能的測試