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

一、分頁

1. 什么是分頁

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

2. 真分頁

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

3. 假分頁

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

4. 緩存層

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

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

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

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

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

1. LIMIT用法

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

• 單參數(shù)用法

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

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

• 雙參數(shù)用法

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

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

2. 分頁公式

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

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

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

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

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

• 第1頁：第1~10條，SQL寫法：LIMIT 0,10
• 第2頁：第11~20條，SQL寫法：LIMIT 10,10
• 第3頁：第21~30條，SQL寫法：LIMIT 20,10

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

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

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

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

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

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

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

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

—語句樣式: MySQL中,可用如下方法:

代碼如下:

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

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

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

方法3: 基于索引再排序

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

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

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

方法4: 基于索引使用prepare

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

—語句樣式: MySQL中,可用如下方法:

代碼如下:

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

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

—原因: 索引掃描,速度會(huì)很快. prepare語句又比一般的查詢語句快一點(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ǔ)過程類（最好融合上述方法5/6）

—語句樣式: 不再給出

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

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

方法8: 反面方法

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

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

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

最新評(píng)論