Mysql分頁有哪些方式

假如收到客戶端pageNo:5，pagesize:10

成都創(chuàng)新互聯(lián)主要從事成都網(wǎng)站設(shè)計、做網(wǎng)站、網(wǎng)頁設(shè)計、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)灤平,十余年網(wǎng)站建設(shè)經(jīng)驗(yàn),價格優(yōu)惠、服務(wù)專業(yè),歡迎來電咨詢建站服務(wù):18982081108

假設(shè)主鍵或者唯一索引為 good_id

1.limit的基本方式

select?*?from?table?limit?(pageNo-1)*pageSize,?pageSize;

2.基于主鍵或者唯一索引

select?*?from?table?where?good_id??(pageNo-1)*pageSize?limit?pageSize;

3.基于數(shù)據(jù)再排序

select?*?from?table?where?good_id??(pageNo-1)*pageSize?order?by?good_id?limit?pageSize;

mysql如何做分頁查詢？

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

我們先從一個常用但性能很差的查詢來看一看。

SELECT *

FROM city

ORDER BY id DESC

LIMIT 0, 15

這個查詢耗時0.00sec。So，這個查詢有什么問題呢？實(shí)際上，這個查詢語句和參數(shù)都沒有問題，因?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;

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

SELECT *

FROM city

ORDER BY id DESC

LIMIT 100000, 15;

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

對于分頁請求，還有一個信息也很重要，就是總共的記錄數(shù)。我們可以通過下面的查詢很容易的獲取總的記錄數(shù)。

SELECT COUNT(*)

FROM city;

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

SELECT SQL_CALC_FOUND_ROWS *

FROM city

ORDER BY id DESC

LIMIT 100000, 15;

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

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

高效的計算行數(shù)

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

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

SELECT COUNT(*)

FROM city

USE INDEX(PRIMARY);

獲取記錄

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

CREATE TABLE news(

id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,

title VARCHAR(128) NOT NULL

) ENGINE=InnoDB;

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

SELECT *

FROM news WHERE id $last_id

ORDER BY id DESC

LIMIT $perpage

查詢上一頁的語句類似，只不過需要傳入當(dāng)前頁的第一個Id，并且要逆序。

SELECT *

FROM news WHERE id $last_id

ORDER BY id ASC

LIMIT $perpage

上面的查詢方式適合實(shí)現(xiàn)簡易的分頁，即不顯示具體的頁數(shù)導(dǎo)航，只顯示“上一頁”和“下一頁”，例如博客中頁腳顯示“上一頁”，“下一頁”的按鈕。但如果要實(shí)現(xiàn)真正的頁面導(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;

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

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

SET p:= 0;

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

當(dāng)然，也可以新增一個專用于分頁的表，可以用個后臺程序來維護(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)在想獲取任意一頁的元素就很簡單了：

SELECT *

FROM news A

JOIN pagination B ON A.id=B.ID

WHERE page=$offset;

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

接下來就可以向下面一樣執(zhí)行分頁查詢了。

SELECT *

FROM _tmp

WHERE OFFSET = $offset

ORDER BY OFFSET

LIMIT $perpage;

簡單來說，對于分頁的優(yōu)化就是。。。避免數(shù)據(jù)量大時掃描過多的記錄。

mysql如何實(shí)現(xiàn)分頁

Mysql的分頁關(guān)鍵點(diǎn)在查詢時的 limit $iStart,$iEnd;//起初值與總長度

舉例：selece * from myTable1 order by id desc limit 0,10;

從0開始取前10條數(shù)據(jù)，取第二頁的內(nèi)容時，limit 10,10;即可

如有疑問去博客加好友，不清楚的再問我，有時間我再寫幾篇這樣的文章

MySQL分頁的sql語言怎么寫？

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

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

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

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

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

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

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

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

mysql如何實(shí)現(xiàn)高效分頁

先看一下分頁的基本原理（我拿的是CSDN那個百萬級數(shù)據(jù)庫來測試?。篠ELECT * FROM `csdn` ORDER BY id DESC LIMIT 100000,2000;

耗時： 0.813ms分析：對上面的mysql語句說明：limit 100000,2000的意思掃描滿足條件的102000行，扔掉前面的100000行，返回最后的2000行。問題就在這里，如果是limit 100000,20000，需要掃描120000行，在一個高并發(fā)的應(yīng)用里，每次查詢需要掃描超過100000行，性能肯定大打折扣。在《efficient pagination using mysql》中提出的clue方式。利用clue方法，給翻頁提供一些線索，比如還是SELECT * FROM `csdn` order by id desc，按id降序分頁，每頁2000條，當(dāng)前是第50頁，當(dāng)前頁條目id最大的是102000，最小的是100000。如果我們只提供上一頁、下一頁這樣的跳轉(zhuǎn)（不提供到第N頁的跳轉(zhuǎn)）。那么在處理上一頁的時候SQL語句可以是：

SELECT * FROM `csdn` WHERE id=102000 ORDER BY id DESC LIMIT 2000; #上一頁

耗時：0.015ms處理下一頁的時候SQL語句可以是：

耗時：0.015ms這樣，不管翻多少頁，每次查詢只掃描20行。效率大大提高了！但是，這樣分頁的缺點(diǎn)是只能提供上一頁、下一頁的鏈接形式。

mysql 數(shù)據(jù)量大的表如何做分頁查詢

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

select * from product limit start, count

當(dāng)起始頁較小時，查詢沒有性能問題，我們分別看下從10， 100， 1000， 10000開始分頁的執(zhí)行時間（每頁取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)看出隨著起始記錄的增加，時間也隨著增大， 這說明分頁語句limit跟起始頁碼是有很大關(guān)系的，那么我們把起始記錄改為40w看下（也就是記錄的一般左右） select * from product limit 400000, 20 3.229秒

再看我們?nèi)∽詈笠豁撚涗浀臅r間

select * from product limit 866613, 20 37.44秒

難怪搜索引擎抓取我們頁面的時候經(jīng)常會報超時，像這種分頁最大的頁碼頁顯然這種時

間是無法忍受的。

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

1）limit語句的查詢時間與起始記錄的位置成正比

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

當(dāng)前標(biāo)題：mysql怎么分頁,mysql的分頁
文章URL：http://aaarwkj.com/article6/dssgpig.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供動態(tài)網(wǎng)站、域名注冊、云服務(wù)器、用戶體驗(yàn)、企業(yè)建站、網(wǎng)站導(dǎo)航

廣告

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