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

一、分頁

1. 什么是分頁

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

2. 真分頁

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

3. 假分頁

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

4. 緩存層

真分頁和假分頁都要和數(shù)據(jù)庫進行交互，對于真分頁來說不需要擔心數(shù)據(jù)同步的問題，因為每次都是查詢出最新的，但是數(shù)據(jù)庫的負擔會很重，尤其是用戶量大的情況下。

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

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

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

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

1. LIMIT用法

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

• 單參數(shù)用法

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

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

• 雙參數(shù)用法

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

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

2. 分頁公式

• 總頁數(shù)計算

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

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

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

在實際操作中，我們能夠得到的信息有當前所在頁以及每頁的數(shù)據(jù)量，同時要注意一下是否超出了最大頁數(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所需要的兩個參數(shù)計算公式如下：

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

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

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

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

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

—原因/缺點: 全表掃描,速度會很慢 且 有的數(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ù)上萬)。

—原因: 索引掃描,速度會很快。有朋友提出因為數(shù)據(jù)查詢出來并不是按照pk_id排序的，所以會有漏掉數(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后的列對象是主鍵或唯一所以,使得ORDERBY操作能利用索引被消除但結(jié)果集是穩(wěn)定的(穩(wěn)定的含義,參見方法1)。

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

方法4: 基于索引使用prepare

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

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

代碼如下:

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

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

—原因: 索引掃描,速度會很快. prepare語句又比一般的查詢語句快一點。

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

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

代碼如下:

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

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

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

利用子查詢示例:

代碼如下:

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: 存儲過程類（最好融合上述方法5/6）

—語句樣式: 不再給出

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

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

方法8: 反面方法

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

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

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

最新評論