在項(xiàng)目開(kāi)發(fā)當(dāng)中，經(jīng)常要實(shí)現(xiàn)分頁(yè)功能，在面試時(shí)也會(huì)經(jīng)常被問(wèn)到：什么是分頁(yè)。這是因?yàn)樵谝粋€(gè)頁(yè)面上能夠顯示的數(shù)據(jù)是有限的，而存放在數(shù)據(jù)庫(kù)中的數(shù)據(jù)往往很多，我們必須將這些數(shù)據(jù)安放到不同的頁(yè)面中去。

一、分頁(yè)

1. 什么是分頁(yè)

一般在客戶端實(shí)現(xiàn)分頁(yè)功能的時(shí)候，要顯示當(dāng)前頁(yè)的數(shù)據(jù)、當(dāng)前所在頁(yè)數(shù)、臨近頁(yè)面的按鈕以及總頁(yè)數(shù)等等。這些數(shù)據(jù)隨著翻頁(yè)的進(jìn)行能夠動(dòng)態(tài)的變化，為了實(shí)現(xiàn)這樣的效果，一般會(huì)采取兩種辦法：真分頁(yè)和假分頁(yè)。這樣的劃分方式是從與數(shù)據(jù)庫(kù)的交互方式出發(fā)的，是每次翻頁(yè)時(shí)都進(jìn)行查詢還是一次性查出所有的數(shù)據(jù)。

2. 真分頁(yè)

真分頁(yè)指的是每次在進(jìn)行翻頁(yè)時(shí)都只查詢出當(dāng)前頁(yè)面的數(shù)據(jù)，特點(diǎn)就是與數(shù)據(jù)庫(kù)的交互次數(shù)較多，但是每次查詢的數(shù)據(jù)量較少，數(shù)據(jù)也不需要一直保存在內(nèi)存中。適用于數(shù)據(jù)量比較大的場(chǎng)景，數(shù)據(jù)不適合全量查出的情況。

3. 假分頁(yè)

假分頁(yè)指的是對(duì)于要顯示的數(shù)據(jù)一次性全部查出，一直存在在服務(wù)端或客戶端，在前端進(jìn)行分頁(yè)或由服務(wù)端控制分頁(yè)。將根據(jù)當(dāng)前所在頁(yè)來(lái)計(jì)算應(yīng)該顯示的數(shù)據(jù)所在下標(biāo)，用循環(huán)取出目標(biāo)數(shù)據(jù)。只有當(dāng)會(huì)話斷開(kāi)或頁(yè)面關(guān)閉，相應(yīng)的資源才會(huì)被釋放。

4. 緩存層

真分頁(yè)和假分頁(yè)都要和數(shù)據(jù)庫(kù)進(jìn)行交互，對(duì)于真分頁(yè)來(lái)說(shuō)不需要擔(dān)心數(shù)據(jù)同步的問(wèn)題，因?yàn)槊看味际遣樵兂鲎钚碌?，但是?shù)據(jù)庫(kù)的負(fù)擔(dān)會(huì)很重，尤其是用戶量大的情況下。

假分頁(yè)可以在一定程度上減輕數(shù)據(jù)庫(kù)的壓力，但是數(shù)據(jù)不能及時(shí)得到同步，除非重新請(qǐng)求或頁(yè)面刷新。

一般在企業(yè)中會(huì)有緩存層的存在，既能有效降低數(shù)據(jù)庫(kù)的壓力，又能及時(shí)的進(jìn)行數(shù)據(jù)同步。在對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)進(jìn)行修改后，要將變更后的數(shù)據(jù)及時(shí)同步到緩存層，在進(jìn)行數(shù)據(jù)查詢時(shí)從緩存層獲取。

二、MySQL實(shí)現(xiàn)分頁(yè)

本文將介紹如何通過(guò)真分頁(yè)的方式，每次取出所需數(shù)據(jù)。對(duì)于不同的數(shù)據(jù)，實(shí)現(xiàn)分頁(yè)有不同的方式，在MySQL中可以使用LIMIT來(lái)限制查詢出的數(shù)據(jù)。

1. LIMIT用法

LIMIT出現(xiàn)在查詢語(yǔ)句的最后，可以使用一個(gè)參數(shù)或兩個(gè)參數(shù)來(lái)限制取出的數(shù)據(jù)。其中第一個(gè)參數(shù)代表偏移量：offset（可選參數(shù)），第二個(gè)參數(shù)代表取出的數(shù)據(jù)條數(shù)：rows。

• 單參數(shù)用法

當(dāng)指定一個(gè)參數(shù)時(shí)，默認(rèn)省略了偏移量，即偏移量為0，從第一行數(shù)據(jù)開(kāi)始取，一共取rows條。

/* 查詢前5條數(shù)據(jù) */
SELECT * FROM Student LIMIT 5;

• 雙參數(shù)用法

當(dāng)指定兩個(gè)參數(shù)時(shí)，需要注意偏移量的取值是從0開(kāi)始的，此時(shí)可以有兩種寫(xiě)法：

/* 查詢第1-10條數(shù)據(jù) */
SELECT * FROM Student LIMIT 0,10;
/* 查詢第11-20條數(shù)據(jù) */
SELECT * FROM Student LIMIT 10 OFFSET 10;

2. 分頁(yè)公式

• 總頁(yè)數(shù)計(jì)算

在進(jìn)行分頁(yè)之前，我們需要先根據(jù)數(shù)據(jù)總量來(lái)得出總頁(yè)數(shù)，這需要用到COUNT函數(shù)和向上取整函數(shù)CEIL，SQL如下：

/* 獲得數(shù)據(jù)總條數(shù) */
SELECT COUNT(*) FROM Student;
/* 假設(shè)每頁(yè)顯示10條，則直接進(jìn)行除法運(yùn)算，然后向上取整 */
SELECT CEIL(COUNT(*) / 10) AS pageTotal FROM Student;

• 核心信息
  • 當(dāng)前頁(yè)：pageNumber
  • 每頁(yè)數(shù)據(jù)量：pageSize

在實(shí)際操作中，我們能夠得到的信息有當(dāng)前所在頁(yè)以及每頁(yè)的數(shù)據(jù)量，同時(shí)要注意一下是否超出了最大頁(yè)數(shù)。以每頁(yè)10條為例，則前三頁(yè)的數(shù)據(jù)應(yīng)為：

• 第1頁(yè)：第1~10條，SQL寫(xiě)法：LIMIT 0,10
• 第2頁(yè)：第11~20條，SQL寫(xiě)法：LIMIT 10,10
• 第3頁(yè)：第21~30條，SQL寫(xiě)法：LIMIT 20,10

據(jù)此我們可以總結(jié)出，LIMIT所需要的兩個(gè)參數(shù)計(jì)算公式如下：

• offset：(pageNumber - 1) * pageSize
• rows：pageSize

8種MySQL分頁(yè)方法總結(jié)

方法1: 直接使用數(shù)據(jù)庫(kù)提供的SQL語(yǔ)句

—語(yǔ)句樣式: MySQL中,可用如下方法: SELECT * FROM 表名稱 LIMIT M,N。

—適應(yīng)場(chǎng)景: 適用于數(shù)據(jù)量較少的情況(元組百/千級(jí))。

—原因/缺點(diǎn): 全表掃描,速度會(huì)很慢 且 有的數(shù)據(jù)庫(kù)結(jié)果集返回不穩(wěn)定(如某次返回1,2,3,另外的一次返回2,1,3)。Limit限制的是從結(jié)果集的M位置處取出N條輸出,其余拋棄。

方法2: 建立主鍵或唯一索引, 利用索引(假設(shè)每頁(yè)10條)

—語(yǔ)句樣式: MySQL中,可用如下方法:

代碼如下:

SELECT * FROM 表名稱 WHERE id_pk > (pageNum*10) LIMIT M

—適應(yīng)場(chǎng)景: 適用于數(shù)據(jù)量多的情況(元組數(shù)上萬(wàn))。

—原因: 索引掃描,速度會(huì)很快。有朋友提出因?yàn)閿?shù)據(jù)查詢出來(lái)并不是按照pk_id排序的，所以會(huì)有漏掉數(shù)據(jù)的情況，只能方法3。

方法3: 基于索引再排序

—語(yǔ)句樣式: MySQL中,可用如下方法: SELECT * FROM 表名稱 WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M。

—適應(yīng)場(chǎng)景: 適用于數(shù)據(jù)量多的情況(元組數(shù)上萬(wàn)). 最好ORDER BY后的列對(duì)象是主鍵或唯一所以,使得ORDERBY操作能利用索引被消除但結(jié)果集是穩(wěn)定的(穩(wěn)定的含義,參見(jiàn)方法1)。

—原因: 索引掃描,速度會(huì)很快. 但MySQL的排序操作,只有ASC沒(méi)有DESC(DESC是假的,未來(lái)會(huì)做真正的DESC,期待)。

方法4: 基于索引使用prepare

（第一個(gè)問(wèn)號(hào)表示pageNum，第二個(gè)？表示每頁(yè)元組數(shù)）

—語(yǔ)句樣式: MySQL中,可用如下方法:

代碼如下:

PREPARE stmt_name FROM SELECT * FROM 表名稱 WHERE id_pk > (？* ？) ORDER BY id_pk
ASC LIMIT M

—適應(yīng)場(chǎng)景: 大數(shù)據(jù)量。

—原因: 索引掃描,速度會(huì)很快. prepare語(yǔ)句又比一般的查詢語(yǔ)句快一點(diǎn)。

方法5:利用MySQL支持ORDER操作可以利用索引快速定位部分元組,避免全表掃描

—比如: 讀第1000到1019行元組(pk是主鍵/唯一鍵)。

代碼如下:

—SELECT * FROM your_table WHERE pk>=1000 ORDER BY pk ASC LIMIT 0,20

方法6: 利用"子查詢/連接+索引"快速定位元組的位置,然后再讀取元組. 道理同方法5

—如(id是主鍵/唯一鍵,藍(lán)色字體時(shí)變量):

利用子查詢示例:

代碼如下:

SELECT* FROMyour_table WHEREid <=
(SELECTid FROMyour_table ORDER
BYid descLIMIT (p a g e ? 1 ) ? page-1)*page?1)?pagesize ORDERBYid desc
LIMIT $pagesize

利用連接示例:

代碼如下:

SELECT* FROMyour_table ASt1
JOIN(SELECTid FROMyour_table ORDERBY
id descLIMIT (p a g e ? 1 ) ? page-1)*page?1)?pagesize ASt2
WHERE
t1.id <= t2.id ORDERBYt1.id descLIMIT $pagesize;

方法7: 存儲(chǔ)過(guò)程類（最好融合上述方法5/6）

—語(yǔ)句樣式: 不再給出

—適應(yīng)場(chǎng)景: 大數(shù)據(jù)量. 作者推薦的方法

—原因: 把操作封裝在服務(wù)器，相對(duì)更快一些。

方法8: 反面方法

—網(wǎng)上有人寫(xiě)使用 SQL_CALC_FOUND_ROWS。 沒(méi)有道理，勿模仿 。

基本上,可以推廣到所有數(shù)據(jù)庫(kù),道理是一樣的。但方法5未必能推廣到其他數(shù)據(jù)庫(kù),推廣的前提是,其他數(shù)據(jù)庫(kù)支持ORDER BY操作可以利用索引直接完成排序。 

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論