這篇文章主要介紹了mysql、mssql及oracle分頁(yè)查詢方法,實(shí)例分析了數(shù)據(jù)庫(kù)分頁(yè)的實(shí)現(xiàn)技巧,非常具有實(shí)用價(jià)值,需要的朋友可以參考下

本文實(shí)例講述了mysql、mssql及oracle分頁(yè)查詢方法。分享給大家供大家參考。具體分析如下：

分頁(yè)查詢?cè)趙eb開(kāi)發(fā)中是最常見(jiàn)的一種技術(shù)，最近在通過(guò)查資料，有一點(diǎn)自己的心得

一、mysql中的分頁(yè)查詢

注：

1. m=(pageNum-1)*pageSize;n= pageSize; 

pageNum是要查詢的頁(yè)碼，pageSize是每次查詢的數(shù)據(jù)量，

方法一：

1. select * from table order by id limit m, n; 

該語(yǔ)句的意思為，查詢m+n條記錄，去掉前m條，返回后n條記錄。無(wú)疑該查詢能夠?qū)崿F(xiàn)分頁(yè)功能，但是如果m的值越大，查詢的性能會(huì)越低(越后面的頁(yè)數(shù)，查詢性能越低)，因?yàn)镸ySQL同樣需要掃描過(guò)m+n條記錄。

方法二：

1. select * from table where id > #max_id# order by id limit n; 

該查詢每次會(huì)返回n條記錄，卻無(wú)需像方式1掃描過(guò)m條記錄，在大數(shù)據(jù)量的分頁(yè)情況下，性能可以明顯好于方式1，但該分頁(yè)查詢必須要每次查詢時(shí)拿到上一次查詢(上一頁(yè))的一個(gè)最大id(或最小id)。該查詢的問(wèn)題就在于，我們有時(shí)沒(méi)有辦法拿到上一次查詢(上一頁(yè))的最大id(或最小id)，比如當(dāng)前在第3頁(yè)，需要查詢第5頁(yè)的數(shù)據(jù)，該查詢方法便愛(ài)莫能助了。

方法三：

為了避免能夠?qū)崿F(xiàn)方式二不能實(shí)現(xiàn)的查詢，就同樣需要使用到limit m, n子句，為了性能，就需要將m的值盡力的小，比如當(dāng)前在第3頁(yè)，需要查詢第5頁(yè)，每頁(yè)10條數(shù)據(jù)，當(dāng)前第3頁(yè)的最大id為#max_id#：

1. select * from table where id > #max_id# order by id limit 20,10; 

其實(shí)該查詢方式是部分解決了方式二的問(wèn)題，但如果當(dāng)前在第2頁(yè)，需要查詢第100頁(yè)或1000頁(yè)，性能仍然會(huì)較差。

方法四：

復(fù)制代碼代碼如下:

select * from table as a inner join (select id from table order by id limit m, n) as b on a.id = b.id order by a.id;

該查詢同方式一 一樣，m的值可能很大，但由于內(nèi)部的子查詢只掃描了字段id，而不是整張表，所以性能要強(qiáng)于方式一查詢，并且該查詢能夠解決方式二和方式三不能解決的問(wèn)題。

方式五：

復(fù)制代碼代碼如下:

select * from table where id > (select id from table order by id limit m, 1) limit n;

該查詢方式同方式四，同樣通過(guò)子查詢掃描字段id，效果同方式四。至于性能的話，方式五的性能會(huì)略好于方式四，因?yàn)榉绞?不需要在進(jìn)行表的關(guān)聯(lián)，而是一個(gè)簡(jiǎn)單的比較。

二、Sql Server分頁(yè)查詢

方法一：

適用于 SQL Server 2000/2005

1. SELECT TOP 頁(yè)大小 * 
2. FROM table1 
3. WHERE id NOT IN 
4. ( 
5. SELECT TOP 頁(yè)大小*(頁(yè)數(shù)-1) id FROM table1 ORDER BY id 
6. ) 
7. ORDER BY id 

方法二：

適用于 SQL Server 2000/2005

--順序?qū)懛ǎ?/p>

1. SELECT TOP 頁(yè)大小 * 
2. FROM table1 
3. WHERE id >= 
4. ( 
5. SELECT ISNULL(MAX(id),0)  
6. FROM 
7. ( 
8. SELECT TOP 頁(yè)大小*(頁(yè)數(shù)-1)+1 id FROM table1 ORDER BY id 
9. ) A 
10. ) 
11. ORDER BY id 

--降序?qū)懛ǎ?/p>

1. SELECT TOP 頁(yè)大小 * 
2. FROM table1 
3. WHERE id <= 
4. ( 
5. SELECT ISNULL(MIN(id),0)  
6. FROM 
7. ( 
8. SELECT TOP 頁(yè)大小*(頁(yè)數(shù)-1)+1 id FROM table1 ORDER BY id Desc 
9. ) A 
10. ) 
11. ORDER BY id Desc 

方法三：

適用于 SQL Server 2005

1. SELECT TOP 頁(yè)大小 *  
2. FROM 
3. ( 
4. SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1 
5. ) A 
6. WHERE RowNumber > 頁(yè)大小*(頁(yè)數(shù)-1) 

說(shuō)明，頁(yè)大?。好宽?yè)的行數(shù);頁(yè)數(shù)：第幾頁(yè)。使用時(shí)，請(qǐng)把“頁(yè)大小”和“頁(yè)大小*(頁(yè)數(shù)-1)”替換成數(shù)字。

其它的方案：如果沒(méi)有主鍵，可以用臨時(shí)表，也可以用方案三做，但是效率會(huì)低。

建議優(yōu)化的時(shí)候，加上主鍵和索引，查詢效率會(huì)提高。

通過(guò)SQL 查詢分析器，顯示比較：我的結(jié)論是:

分頁(yè)方案二：(利用ID大于多少和SELECT TOP分頁(yè))效率最高，需要拼接SQL語(yǔ)句

分頁(yè)方案一：(利用Not In和SELECT TOP分頁(yè)) 效率次之，需要拼接SQL語(yǔ)句

分頁(yè)方案三：(利用SQL的游標(biāo)存儲(chǔ)過(guò)程分頁(yè)) 效率最差，但是最為通用

三、oracle分頁(yè)查詢

方法一：

1. SELECT * FROM 
2. ( SELECT A.*, ROWNUM RN FROM 
3. (SELECT * FROM tab) A  
4. WHERE ROWNUM <= 40 )  
5. WHERE RN >= 21; 

這個(gè)分頁(yè)比下面的執(zhí)行時(shí)間少，效率高。當(dāng)數(shù)據(jù)量較大時(shí)oracle會(huì)自動(dòng)優(yōu)化!

方法二：

1. select * from 
2. (select c.*,rownum rn from tab c) where rn between 21 and 40 

對(duì)比這兩種寫法，絕大多數(shù)的情況下，第一個(gè)查詢的效率比第二個(gè)高得多。

這是由于CBO優(yōu)化模式下，Oracle可以將外層的查詢條件推到內(nèi)層查詢中，以提高內(nèi)層查詢的執(zhí)行效率。

對(duì)于第一個(gè)查詢語(yǔ)句，第二層的查詢條件WHERE ROWNUM <= 40就可以被Oracle推入到內(nèi)層查詢中，這樣Oracle查詢的結(jié)果一旦超過(guò)了ROWNUM限制條件，就終止查詢將結(jié)果返回了。

而第二個(gè)查詢語(yǔ)句，由于查詢條件BETWEEN 21 AND 40是存在于查詢的第三層，而Oracle無(wú)法將第三層的查詢條件推到最內(nèi)層

(即使推到最內(nèi)層也沒(méi)有意義，因?yàn)樽顑?nèi)層查詢不知道RN代表什么)。因此，對(duì)于第二個(gè)查詢語(yǔ)句，Oracle最內(nèi)層返回給中間層的是所有滿足條件的數(shù)據(jù)，而中間層返回給最外層的也是所有數(shù)據(jù)。數(shù)據(jù)的過(guò)濾在最外層完成，顯然這個(gè)效率要比第一個(gè)查詢低得多。

上面分析的查詢不僅僅是針對(duì)單表的簡(jiǎn)單查詢，對(duì)于最內(nèi)層查詢是復(fù)雜的多表聯(lián)合查詢或最內(nèi)層查詢包含排序的情況一樣有效。

希望本文所述對(duì)大家的數(shù)據(jù)庫(kù)程序設(shè)計(jì)有所幫助。