數(shù)據(jù)庫(kù)分頁(yè)查詢(xún)方法
2020-10-29 21:53:56
供稿：網(wǎng)友
可能會(huì)有人說(shuō)這些網(wǎng)上都有，但我的主要目的是把這些知識(shí)通過(guò)我實(shí)際的應(yīng)用總結(jié)歸納一下，以方便大家查詢(xún)使用。
下面就分別給大家介紹、講解一下三種數(shù)據(jù)庫(kù)實(shí)現(xiàn)分頁(yè)查詢(xún)的方法。
一、 MySQL 數(shù)據(jù)庫(kù)分頁(yè)查詢(xún)
MySQL數(shù)據(jù)庫(kù)實(shí)現(xiàn)分頁(yè)比較簡(jiǎn)單，提供了LIMIT函數(shù)。一般只需要直接寫(xiě)到sql語(yǔ)句后面就行了。
LIMIT子句可以用來(lái)限制由SELECT語(yǔ)句返回過(guò)來(lái)的數(shù)據(jù)數(shù)量，它有一個(gè)或兩個(gè)參數(shù)，如果給出兩個(gè)參數(shù)， 第一個(gè)參數(shù)指定返回的第一行在所有數(shù)據(jù)中的位置，從0開(kāi)始（注意不是1），第二個(gè)參數(shù)指定最多返回行數(shù)。例如：
select * from table WHERE … LIMIT 10; #返回前10行
select * from table WHERE … LIMIT 0,10; #返回前10行
select * from table WHERE … LIMIT 10,20; #返回第10-20行數(shù)據(jù)
二、 SQLServer數(shù)據(jù)庫(kù)分頁(yè)查詢(xún)
SQLServer數(shù)據(jù)庫(kù)又分為SQLServer2000和SQLServer2005。一般比較簡(jiǎn)單的方法是通過(guò)TOP函數(shù)來(lái)實(shí)現(xiàn)。如下：
SELECT TOP 10 * FROM sql WHERE (
code NOT IN (SELECT TOP 20 code FROM TestTable ORDER BY id))
ORDER BY ID
這條語(yǔ)句，從理論上講，整條語(yǔ)句的執(zhí)行時(shí)間應(yīng)該比子句的執(zhí)行時(shí)間長(zhǎng)，但事實(shí)相反。因?yàn)椋泳鋱?zhí)行后返回的是20條記錄，而整條語(yǔ)句僅返回10條語(yǔ)句，所以影響數(shù)據(jù)庫(kù)響應(yīng)時(shí)間最大的因素是物理I/O操作。而限制物理I/O操作此處的最有效方法之一就是使用TOP關(guān)鍵詞了。TOP關(guān)鍵詞是SQL SERVER中經(jīng)過(guò)系統(tǒng)優(yōu)化過(guò)的一個(gè)用來(lái)提取前幾條或前幾個(gè)百分比數(shù)據(jù)的詞。
以上語(yǔ)句的有一個(gè)致命的缺點(diǎn)，就是它含有NOT IN字樣，要換成用not exists來(lái)代替not in，二者的執(zhí)行效率實(shí)際上是沒(méi)有區(qū)別的。
在以上分頁(yè)算法中，影響我們查詢(xún)速度的關(guān)鍵因素有兩點(diǎn)：TOP和NOT IN。TOP可以提高我們的查詢(xún)速度，而NOT IN會(huì)減慢我們的查詢(xún)速度，所以要提高我們整個(gè)分頁(yè)算法的速度，就要徹底改造NOT IN，同其他方法來(lái)替代它。
我們知道，幾乎任何字段，我們都可以通過(guò)max(字段)或min(字段)來(lái)提取某個(gè)字段中的最大或最小值，所以如果這個(gè)字段不重復(fù)，那么就可以利用這些不重復(fù)的字段的max或min作為分水嶺，使其成為分頁(yè)算法中分開(kāi)每頁(yè)的參照物。在這里，我們可以用操作符“>”或“<”號(hào)來(lái)完成這個(gè)使命。如：
Select top 10 * from table1 where id>200
于是就有了如下分頁(yè)方案：
select top 頁(yè)大小 *
from table1
where id>
(select max (id) from
(select top ((頁(yè)碼-1)*頁(yè)大小) id from table1 order by id) as T
)
order by id
這種方法執(zhí)行多少始終沒(méi)有大的降勢(shì)，后勁仍然很足。尤其對(duì)于數(shù)據(jù)量大的時(shí)候，該方法執(zhí)行速度一點(diǎn)也不會(huì)降低。
使用TOP要求主鍵必須唯一，不能是聯(lián)合主鍵。如果是聯(lián)合主鍵，則查詢(xún)出的結(jié)果會(huì)亂序的。
目前SQLServer2005提供了一個(gè)row_number()函數(shù)。ROW_NUMBER() 就是生成一個(gè)順序的行號(hào),而他生成順序的標(biāo)準(zhǔn),就是后面緊跟的OVER(ORDER BY ReportID)，其中ReportID可以是聯(lián)合主鍵。下面,我們看看怎么具體應(yīng)用這個(gè)RowNo進(jìn)行分頁(yè).
SELECT TOP 10 * FROM
(
SELECT top 10 ROW_NUMBER() OVER (ORDER BY ReportID) AS RowNo
FROM TABLE
) AS A
WHERE RowNo > " + pageIndex*10
pageIndex就是我們需要數(shù)據(jù)的頁(yè)數(shù).
但對(duì)于SQLServer2000的話(huà)，如果是聯(lián)合主鍵，我還沒(méi)有解決辦法，如果大家有可跟我聯(lián)系。謝謝大家了。
三、 ORCALE數(shù)據(jù)庫(kù)分頁(yè)查詢(xún)
ORCALE數(shù)據(jù)庫(kù)實(shí)現(xiàn)分頁(yè)查詢(xún)可以使用row_number()函數(shù)或者使用rownum 虛列兩種方法。
第一種：利用分析函數(shù)row_number() 方法
select * from(
select t.*,row_number() over (order by t1.id) rowno from TABLE1
)
where rowno between 21 and 40;
第二種：直接使用rownum 虛列
select * from
(select t.*,rownum as rowno from TABLE1 )
where rowno between 10 and 20
這兩種方法比較，顯然第二種方法比較好。因?yàn)椴挥胦rder by語(yǔ)句，會(huì)提高檢索數(shù)據(jù)的速度的，尤其數(shù)據(jù)量越大時(shí)，第二種方法快速檢索數(shù)據(jù)越明顯。
最后提醒大家：oracle中慎用帶有order by的分頁(yè)。尤其是在oracle10g中，會(huì)出現(xiàn)會(huì)引起混亂，即相同記錄會(huì)出現(xiàn)在不同頁(yè)中。
結(jié)束語(yǔ)：
希望這篇文章不僅能夠給大家的工作帶來(lái)一定的幫助，也希望能讓大家能夠體會(huì)到分析問(wèn)題的方法；最重要的是，希望這篇文章能夠拋磚引玉，掀起大家的學(xué)習(xí)和討論的興趣，以共同促進(jìn)。還有其中紅色的字如果誰(shuí)知道解決辦法請(qǐng)告訴我，我會(huì)盡快補(bǔ)上的。
