mysql里怎么實(shí)現(xiàn)分頁(yè)啊

記得我還在念大學(xué)的時(shí)候，一位教我們單片機(jī)的老師說(shuō)了一句話："學(xué)習(xí)編程剛開(kāi)始你就得照葫蘆畫(huà)瓢...",以前我在mysql中分頁(yè)都是用的 limit 100000,20這樣的方式，我相信你也是吧，但是要提高效率，讓分頁(yè)的代碼效率更高一些，更快一些,那我們又該怎么做呢?

我們提供的服務(wù)有：成都做網(wǎng)站、網(wǎng)站建設(shè)、微信公眾號(hào)開(kāi)發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認(rèn)證、韶關(guān)ssl等。為1000多家企事業(yè)單位解決了網(wǎng)站和推廣的問(wèn)題。提供周到的售前咨詢(xún)和貼心的售后服務(wù)，是有科學(xué)管理、有技術(shù)的韶關(guān)網(wǎng)站制作公司

第一部分：看一下分頁(yè)的基本原理：

第一部分：看一下分頁(yè)的基本原理：

mysql explain SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20

***************** 1. row **************

id: 1

select_type: SIMPLE

table: message

type: index

possible_keys: NULL

key: PRIMARY

key_len: 4

ref: NULL

rows: 10020

Extra:

1 row in set (0.00 sec) 對(duì)上面的mysql語(yǔ)句說(shuō)明：limit 10000,20的意思掃描滿(mǎn)足條件的10020行，扔掉前面的10000行，返回最后的20行，問(wèn)題就在這里，如果是limit 100000,100，需要掃描100100行，在一個(gè)高并發(fā)的應(yīng)用里，每次查詢(xún)需要掃描超過(guò)10W行，性能肯定大打折扣。文中還提到limit n性能是沒(méi)問(wèn)題的，因?yàn)橹粧呙鑞行。

第二部分：根據(jù)雅虎的幾位工程師帶來(lái)了一篇Efficient Pagination Using MySQL的報(bào)告內(nèi)容擴(kuò)展：在文中提到一種clue的做法，給翻頁(yè)提供一些線索，比如還是SELECT * FROM message ORDER BY id DESC，按id降序分頁(yè)，每頁(yè)20條，當(dāng)前是第10頁(yè)，當(dāng)前頁(yè)條目id最大的是1020，最小的是1000，如果我們只提供上一頁(yè)、下一頁(yè)這樣的跳轉(zhuǎn)（不提供到第N頁(yè)的跳轉(zhuǎn)），那么在處理上一頁(yè)的時(shí)候SQL語(yǔ)句可以是：

完整請(qǐng)到：

mysql如何做分頁(yè)查詢(xún)？

很多應(yīng)用往往只展示最新或最熱門(mén)的幾條記錄，但為了舊記錄仍然可訪問(wèn)，所以就需要個(gè)分頁(yè)的導(dǎo)航欄。然而，如何通過(guò)MySQL更好的實(shí)現(xiàn)分頁(yè)，始終是比較令人頭疼的問(wèn)題。雖然沒(méi)有拿來(lái)就能用的解決辦法，但了解數(shù)據(jù)庫(kù)的底層或多或少有助于優(yōu)化分頁(yè)查詢(xún)。

我們先從一個(gè)常用但性能很差的查詢(xún)來(lái)看一看。

SELECT *

FROM city

ORDER BY id DESC

LIMIT 0, 15

這個(gè)查詢(xún)耗時(shí)0.00sec。So，這個(gè)查詢(xún)有什么問(wèn)題呢？實(shí)際上，這個(gè)查詢(xún)語(yǔ)句和參數(shù)都沒(méi)有問(wèn)題，因?yàn)樗玫搅讼旅姹淼闹麈I，而且只讀取15條記錄。

CREATE TABLE city (

id int(10) unsigned NOT NULL AUTO_INCREMENT,

city varchar(128) NOT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB;

真正的問(wèn)題在于offset(分頁(yè)偏移量)很大的時(shí)候，像下面這樣：

SELECT *

FROM city

ORDER BY id DESC

LIMIT 100000, 15;

上面的查詢(xún)?cè)谟?M行記錄時(shí)需要0.22sec，通過(guò)EXPLAIN查看SQL的執(zhí)行計(jì)劃可以發(fā)現(xiàn)該SQL檢索了100015行，但最后只需要15行。大的分頁(yè)偏移量會(huì)增加使用的數(shù)據(jù)，MySQL會(huì)將大量最終不會(huì)使用的數(shù)據(jù)加載到內(nèi)存中。就算我們假設(shè)大部分網(wǎng)站的用戶(hù)只訪問(wèn)前幾頁(yè)數(shù)據(jù)，但少量的大的分頁(yè)偏移量的請(qǐng)求也會(huì)對(duì)整個(gè)系統(tǒng)造成危害。Facebook意識(shí)到了這一點(diǎn)，但Facebook并沒(méi)有為了每秒可以處理更多的請(qǐng)求而去優(yōu)化數(shù)據(jù)庫(kù)，而是將重心放在將請(qǐng)求響應(yīng)時(shí)間的方差變小。

對(duì)于分頁(yè)請(qǐng)求，還有一個(gè)信息也很重要，就是總共的記錄數(shù)。我們可以通過(guò)下面的查詢(xún)很容易的獲取總的記錄數(shù)。

SELECT COUNT(*)

FROM city;

然而，上面的SQL在采用InnoDB為存儲(chǔ)引擎時(shí)需要耗費(fèi)9.28sec。一個(gè)不正確的優(yōu)化是采用 SQL_CALC_FOUND_ROWS,SQL_CALC_FOUND_ROWS 可以在能夠在分頁(yè)查詢(xún)時(shí)事先準(zhǔn)備好符合條件的記錄數(shù)，隨后只要執(zhí)行一句 select FOUND_ROWS(); 就能獲得總記錄數(shù)。但是在大多數(shù)情況下，查詢(xún)語(yǔ)句簡(jiǎn)短并不意味著性能的提高。不幸的是，這種分頁(yè)查詢(xún)方式在許多主流框架中都有用到，下面看看這個(gè)語(yǔ)句的查詢(xún)性能。

SELECT SQL_CALC_FOUND_ROWS *

FROM city

ORDER BY id DESC

LIMIT 100000, 15;

這個(gè)語(yǔ)句耗時(shí)20.02sec，是上一個(gè)的兩倍。事實(shí)證明使用 SQL_CALC_FOUND_ROWS 做分頁(yè)是很糟糕的想法。

下面來(lái)看看到底如何優(yōu)化。文章分為兩部分，第一部分是如何獲取記錄的總數(shù)目，第二部分是獲取真正的記錄。

高效的計(jì)算行數(shù)

如果采用的引擎是MyISAM，可以直接執(zhí)行COUNT(*)去獲取行數(shù)即可。相似的，在堆表中也會(huì)將行數(shù)存儲(chǔ)到表的元信息中。但如果引擎是InnoDB情況就會(huì)復(fù)雜一些，因?yàn)镮nnoDB不保存表的具體行數(shù)。

我們可以將行數(shù)緩存起來(lái)，然后可以通過(guò)一個(gè)守護(hù)進(jìn)程定期更新或者用戶(hù)的某些操作導(dǎo)致緩存失效時(shí)，執(zhí)行下面的語(yǔ)句：

SELECT COUNT(*)

FROM city

USE INDEX(PRIMARY);

獲取記錄

下面進(jìn)入這篇文章最重要的部分，獲取分頁(yè)要展示的記錄。上面已經(jīng)說(shuō)過(guò)了，大的偏移量會(huì)影響性能，所以我們要重寫(xiě)查詢(xún)語(yǔ)句。為了演示，我們創(chuàng)建一個(gè)新的表“news”，按照時(shí)事性排序(最新發(fā)布的在最前面)，實(shí)現(xiàn)一個(gè)高性能的分頁(yè)。為了簡(jiǎn)單，我們就假設(shè)最新發(fā)布的新聞的Id也是最大的。

CREATE TABLE news(

id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

title VARCHAR(128) NOT NULL

) ENGINE=InnoDB;

一個(gè)比較高效的方式是基于用戶(hù)展示的最后一個(gè)新聞Id。查詢(xún)下一頁(yè)的語(yǔ)句如下，需要傳入當(dāng)前頁(yè)面展示的最后一個(gè)Id。

SELECT *

FROM news WHERE id $last_id

ORDER BY id DESC

LIMIT $perpage

查詢(xún)上一頁(yè)的語(yǔ)句類(lèi)似，只不過(guò)需要傳入當(dāng)前頁(yè)的第一個(gè)Id，并且要逆序。

SELECT *

FROM news WHERE id $last_id

ORDER BY id ASC

LIMIT $perpage

上面的查詢(xún)方式適合實(shí)現(xiàn)簡(jiǎn)易的分頁(yè)，即不顯示具體的頁(yè)數(shù)導(dǎo)航，只顯示“上一頁(yè)”和“下一頁(yè)”，例如博客中頁(yè)腳顯示“上一頁(yè)”，“下一頁(yè)”的按鈕。但如果要實(shí)現(xiàn)真正的頁(yè)面導(dǎo)航還是很難的,下面看看另一種方式。

SELECT id

FROM (

SELECT id, ((@cnt:= @cnt + 1) + $perpage - 1) % $perpage cnt

FROM news

JOIN (SELECT @cnt:= 0)T

WHERE id $last_id

ORDER BY id DESC

LIMIT $perpage * $buttons

)C

WHERE cnt = 0;

通過(guò)上面的語(yǔ)句可以為每一個(gè)分頁(yè)的按鈕計(jì)算出一個(gè)offset對(duì)應(yīng)的id。這種方法還有一個(gè)好處。假設(shè)，網(wǎng)站上正在發(fā)布一片新的文章，那么所有文章的位置都會(huì)往后移一位，所以如果用戶(hù)在發(fā)布文章時(shí)換頁(yè)，那么他會(huì)看見(jiàn)一篇文章兩次。如果固定了每個(gè)按鈕的offset Id，這個(gè)問(wèn)題就迎刃而解了。Mark Callaghan發(fā)表過(guò)一篇類(lèi)似的博客，利用了組合索引和兩個(gè)位置變量，但是基本思想是一致的。

如果表中的記錄很少被刪除、修改，還可以將記錄對(duì)應(yīng)的頁(yè)碼存儲(chǔ)到表中，并在該列上創(chuàng)建合適的索引。采用這種方式，當(dāng)新增一個(gè)記錄的時(shí)候，需要執(zhí)行下面的查詢(xún)重新生成對(duì)應(yīng)的頁(yè)號(hào)。

SET p:= 0;

UPDATE news SET page=CEIL((p:= p + 1) / $perpage) ORDER BY id DESC;

當(dāng)然，也可以新增一個(gè)專(zhuān)用于分頁(yè)的表，可以用個(gè)后臺(tái)程序來(lái)維護(hù)。

UPDATE pagination T

JOIN (

SELECT id, CEIL((p:= p + 1) / $perpage) page

FROM news

ORDER BY id

)C

ON C.id = T.id

SET T.page = C.page;

現(xiàn)在想獲取任意一頁(yè)的元素就很簡(jiǎn)單了：

SELECT *

FROM news A

JOIN pagination B ON A.id=B.ID

WHERE page=$offset;

還有另外一種與上種方法比較相似的方法來(lái)做分頁(yè)，這種方式比較試用于數(shù)據(jù)集相對(duì)小，并且沒(méi)有可用的索引的情況下—比如處理搜索結(jié)果時(shí)。在一個(gè)普通的服務(wù)器上執(zhí)行下面的查詢(xún)，當(dāng)有2M條記錄時(shí)，要耗費(fèi)2sec左右。這種方式比較簡(jiǎn)單，創(chuàng)建一個(gè)用來(lái)存儲(chǔ)所有Id的臨時(shí)表即可(這也是最耗費(fèi)性能的地方)。

CREATE TEMPORARY TABLE _tmp (KEY SORT(random))

SELECT id, FLOOR(RAND() * 0x8000000) random

FROM city;

ALTER TABLE _tmp ADD OFFSET INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, DROP INDEX SORT,ORDER BY random;

接下來(lái)就可以向下面一樣執(zhí)行分頁(yè)查詢(xún)了。

SELECT *

FROM _tmp

WHERE OFFSET = $offset

ORDER BY OFFSET

LIMIT $perpage;

簡(jiǎn)單來(lái)說(shuō)，對(duì)于分頁(yè)的優(yōu)化就是。。。避免數(shù)據(jù)量大時(shí)掃描過(guò)多的記錄。

mysql 數(shù)據(jù)量大的表如何做分頁(yè)查詢(xún)

直接用limit start, count分頁(yè)語(yǔ)句， 也是我程序中用的方法：

select * from product limit start, count

當(dāng)起始頁(yè)較小時(shí)，查詢(xún)沒(méi)有性能問(wèn)題，我們分別看下從10， 100， 1000， 10000開(kāi)始分頁(yè)的執(zhí)行時(shí)間（每頁(yè)取20條）， 如下：

select * from product limit 10, 20 0.016秒

select * from product limit 100, 20 0.016秒

select * from product limit 1000, 20 0.047秒

select * from product limit 10000, 20 0.094秒

我們已經(jīng)看出隨著起始記錄的增加，時(shí)間也隨著增大， 這說(shuō)明分頁(yè)語(yǔ)句limit跟起始頁(yè)碼是有很大關(guān)系的，那么我們把起始記錄改為40w看下（也就是記錄的一般左右） select * from product limit 400000, 20 3.229秒

再看我們?nèi)∽詈笠豁?yè)記錄的時(shí)間

select * from product limit 866613, 20 37.44秒

難怪搜索引擎抓取我們頁(yè)面的時(shí)候經(jīng)常會(huì)報(bào)超時(shí)，像這種分頁(yè)最大的頁(yè)碼頁(yè)顯然這種時(shí)

間是無(wú)法忍受的。

從中我們也能總結(jié)出兩件事情：

1）limit語(yǔ)句的查詢(xún)時(shí)間與起始記錄的位置成正比

2）mysql的limit語(yǔ)句是很方便，但是對(duì)記錄很多的表并不適合直接使用。

Mysql 數(shù)據(jù)庫(kù)怎么實(shí)現(xiàn)分頁(yè)，要說(shuō)的通俗一點(diǎn)兒

一個(gè)不帶limit 一個(gè)帶limit。以php+mysql為例首先，連接數(shù)據(jù)庫(kù)，寫(xiě)一條sql語(yǔ)句把你要查詢(xún)的信息總量查找出來(lái)sql = select count(*) from tb，$all_page ；設(shè)定每頁(yè)顯示條數(shù), $display 。然后，當(dāng)前頁(yè)為$page ；在寫(xiě)一句sql = select * from tb limit $dispaly*($page - 1),$display;最后,在頁(yè)面顯示分頁(yè)信息把當(dāng)前頁(yè)傳回給分頁(yè)處理頁(yè)，一定要把相關(guān)的條件一起傳回去，get 方式傳值，否則查詢(xún)條件改變查詢(xún)信息就不正確。 網(wǎng)上有好多封裝好的分頁(yè)類(lèi)。我也有一個(gè)很好用的分頁(yè)類(lèi)，如果請(qǐng)我吃肉就發(fā)給你一份哈。。\(^o^)/~ 追問(wèn)： 這個(gè)$all_page用在哪兒，怎么將當(dāng)前頁(yè)傳回給分頁(yè)處理頁(yè)。顯示的時(shí)候那些“首頁(yè)”“上一頁(yè)”“下一頁(yè)”“末頁(yè)”是鏈接嗎、鏈到什么地方，還是別的什么 回答： $all_page是查詢(xún)總數(shù)，總是頁(yè)數(shù)等于查詢(xún)總數(shù)除以每頁(yè)顯示的信息。$num_page = ceil($all_page/$display); 用get方式把當(dāng)前頁(yè)傳給分頁(yè)處理頁(yè)，就是a href = "連接到本頁(yè)或著不寫(xiě)也就是當(dāng)前頁(yè)？page=當(dāng)前頁(yè)碼"/a標(biāo)簽 別的我也想不起來(lái)，讓我自己寫(xiě)分頁(yè)，我只會(huì)最簡(jiǎn)單的那種，一般我都是調(diào)用一個(gè)現(xiàn)成的分頁(yè)類(lèi)。只需傳個(gè)參數(shù)就Ok，連樣式都不用寫(xiě)的。。。

oracle和mysql的分頁(yè)查詢(xún)?cè)趺磳?xiě)？

可以根據(jù)下面操作進(jìn)行編寫(xiě)。

1.#返回前5行 Mssql 2000分頁(yè)采用top關(guān)鍵字(20005以上版本也支持關(guān)鍵字Select top 10 * from t_order where id not in (select id from t_order where id5 )。

2.Oracle分頁(yè)采用rownum關(guān)鍵字(三層嵌套) SELECT * FROM( SELECT A.*,ROWNUM num FROM (SELECT * FROM t_order)A WHERE ROWNUM=15) WHERE num=5;--返回第5-15行數(shù)據(jù)。

3.采用row_number解析函數(shù)進(jìn)行分頁(yè)(效率更高) SELECT xx.* FROM --返回第5-15行數(shù)據(jù) 解析函數(shù)能用格式 函數(shù)over(pertion by 字段 order by 字段)。

4.Pertion 按照某個(gè)字段分區(qū) Order 按照勒個(gè)字段排序。

MySQL分頁(yè)的sql語(yǔ)言怎么寫(xiě)？

1、首先我們建立一個(gè)表表的數(shù)據(jù)，這個(gè)表里有25條數(shù)據(jù)，id從1到25。（下圖是部分截圖）

2、要分頁(yè)數(shù)據(jù)，首先我們假設(shè)一頁(yè)有10條數(shù)據(jù)，我們可以用mysql的limit關(guān)鍵字來(lái)限定返回多少條數(shù)據(jù)。并且用orderby來(lái)排序數(shù)據(jù)，這里用id來(lái)排序。所以第一頁(yè)的sql可以如圖這樣寫(xiě)。

3、執(zhí)行后得到的數(shù)據(jù)如圖，就是id從1到10的前10條數(shù)據(jù)，因?yàn)槲覀兪前磇d升序來(lái)排序的。

4、上面第一頁(yè)的sql是簡(jiǎn)化的寫(xiě)法，完整的寫(xiě)法如圖，得到的結(jié)果和上圖的一模一樣。代碼里limit0,10的意思是從第一條數(shù)據(jù)開(kāi)始，取10條數(shù)據(jù)。（注意的是第一條數(shù)據(jù)是從0開(kāi)始的）

5、那么第二頁(yè)的數(shù)據(jù)，關(guān)鍵是要知道是從哪一條數(shù)據(jù)開(kāi)始，可以用這個(gè)公式得到：(頁(yè)碼-1)?*每頁(yè)顯示多少條，即（2-1)*10=10,所以sql語(yǔ)句如圖,limit10,10。

6、執(zhí)行后，結(jié)果正確，得到id從11到20的10條數(shù)據(jù)。

7、同理第三頁(yè)數(shù)據(jù)的sql如圖，br/就是limit20,10。

8、查詢(xún)的結(jié)果如圖，因?yàn)檫@頁(yè)只剩下5條數(shù)據(jù)了，所以只顯示5條數(shù)據(jù)。如果你有更多頁(yè)的數(shù)據(jù)，后面的數(shù)據(jù)只需要按上面的公式，得到從哪行開(kāi)始，就可以寫(xiě)對(duì)應(yīng)的sql語(yǔ)句了。

網(wǎng)站名稱(chēng)：mysql數(shù)據(jù)怎么分頁(yè) mysql分頁(yè)的幾種方式
當(dāng)前網(wǎng)址：http://jinyejixie.com/article8/dodhsop.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供營(yíng)銷(xiāo)型網(wǎng)站建設(shè)、服務(wù)器托管、微信公眾號(hào)、關(guān)鍵詞優(yōu)化、外貿(mào)建站、定制網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)