主頁(yè) > 知識(shí)庫(kù) > mysql、mssql及oracle分頁(yè)查詢(xún)方法詳解

mysql、mssql及oracle分頁(yè)查詢(xún)方法詳解

熱門(mén)標(biāo)簽:ai電話(huà)電話(huà)機(jī)器人 怎么去開(kāi)發(fā)一個(gè)電銷(xiāo)機(jī)器人 怎么申請(qǐng)400熱線(xiàn)電話(huà) 泗洪正規(guī)電話(huà)機(jī)器人找哪家 湖南保險(xiǎn)智能外呼系統(tǒng)產(chǎn)品介紹 簡(jiǎn)單的智能語(yǔ)音電銷(xiāo)機(jī)器人 南昌呼叫中心外呼系統(tǒng)哪家好 小程序智能電話(huà)機(jī)器人 河北便宜電銷(xiāo)機(jī)器人軟件

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

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

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

注:

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

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

方法一:

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

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

方法二:

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

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

方法三:

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

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

其實(shí)該查詢(xún)方式是部分解決了方式二的問(wèn)題,但如果當(dāng)前在第2頁(yè),需要查詢(xún)第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;

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

方式五:

復(fù)制代碼 代碼如下:
select * from table where id > (select id from table order by id limit m, 1) limit n;

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

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

方法一:

適用于 SQL Server 2000/2005

SELECT TOP 頁(yè)大小 *
 FROM table1
 WHERE id NOT IN
      (
      SELECT TOP 頁(yè)大小*(頁(yè)數(shù)-1) id FROM table1 ORDER BY id
      )
 ORDER BY id

方法二:

適用于 SQL Server 2000/2005

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

 SELECT TOP 頁(yè)大小 *
 FROM table1
 WHERE id >=
 (
 SELECT ISNULL(MAX(id),0) 
 FROM 
 (
 SELECT TOP 頁(yè)大小*(頁(yè)數(shù)-1)+1 id FROM table1 ORDER BY id
 ) A
 )
 ORDER BY id

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

 SELECT TOP 頁(yè)大小 *
 FROM table1
 WHERE id =
 (
 SELECT ISNULL(MIN(id),0) 
 FROM 
 (
 SELECT TOP 頁(yè)大小*(頁(yè)數(shù)-1)+1 id FROM table1 ORDER BY id Desc
 ) A
 )
 ORDER BY id Desc

方法三:

適用于 SQL Server 2005

 SELECT TOP 頁(yè)大小 * 
 FROM 
     (
     SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1
     ) A
 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í)候,加上主鍵和索引,查詢(xún)效率會(huì)提高。

通過(guò)SQL 查詢(xún)分析器,顯示比較:我的結(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è)查詢(xún)

方法一:

SELECT * FROM 
( SELECT A.*, ROWNUM RN FROM 
  (SELECT * FROM tab) A 
   WHERE ROWNUM = 40 ) 
     WHERE RN >= 21;

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

方法二:

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

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

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

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

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

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

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

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

您可能感興趣的文章:
  • mysql、oracle默認(rèn)事務(wù)隔離級(jí)別的說(shuō)明
  • Mysql、Oracle中常用的多表修改語(yǔ)句總結(jié)
  • 淺談Mysql、SqlServer、Oracle三大數(shù)據(jù)庫(kù)的區(qū)別
  • SQL實(shí)現(xiàn)時(shí)間序列錯(cuò)位還原案列

標(biāo)簽:威海 江蘇 那曲 荊門(mén) 景德鎮(zhèn) 淮安 瀘州 柳州

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《mysql、mssql及oracle分頁(yè)查詢(xún)方法詳解》,本文關(guān)鍵詞  mysql,mssql,及,oracle,分頁(yè),;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無(wú)關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《mysql、mssql及oracle分頁(yè)查詢(xún)方法詳解》相關(guān)的同類(lèi)信息!
  • 本頁(yè)收集關(guān)于mysql、mssql及oracle分頁(yè)查詢(xún)方法詳解的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章