使用MySQL如何實現分頁查詢

在專案開發當中，經常要實現分頁功能，在面試時也會經常被問到：什麼是分頁。這是因為在一個頁面上能夠顯示的資料是有限的，而存放在資料庫中的資料往往很多，我們必須將這些資料安放到不同的頁面中去。

一、分頁

1. 什麼是分頁

一般在使用者端實現分頁功能的時候，要顯示當前頁的資料、當前所在頁數、臨近頁面的按鈕以及總頁數等等。這些資料隨著翻頁的進行能夠動態的變化，為了實現這樣的效果，一般會採取兩種辦法：真分頁和假分頁。這樣的劃分方式是從與資料庫的互動方式出發的，是每次翻頁時都進行查詢還是一次性查出所有的資料。

2. 真分頁

真分頁指的是每次在進行翻頁時都只查詢出當前頁面的資料，特點就是與資料庫的互動次數較多，但是每次查詢的資料量較少，資料也不需要一直儲存在記憶體中。適用於資料量比較大的場景，資料不適合全量查出的情況。

3. 假分頁

假分頁指的是對於要顯示的資料一次性全部查出，一直存在在伺服器端或使用者端，在前端進行分頁或由伺服器端控制分頁。將根據當前所在頁來計算應該顯示的資料所在下標，用迴圈取出目標資料。只有當對談斷開或頁面關閉，相應的資源才會被釋放。

4. 快取層

真分頁和假分頁都要和資料庫進行互動，對於真分頁來說不需要擔心資料同步的問題，因為每次都是查詢出最新的，但是資料庫的負擔會很重，尤其是使用者量大的情況下。

假分頁可以在一定程度上減輕資料庫的壓力，但是資料不能及時得到同步，除非重新請求或頁面重新整理。

一般在企業中會有快取層的存在，既能有效降低資料庫的壓力，又能及時的進行資料同步。在對資料庫中的資料進行修改後，要將變更後的資料及時同步到快取層，在進行資料查詢時從快取層獲取。

二、MySQL實現分頁

本文將介紹如何通過真分頁的方式，每次取出所需資料。對於不同的資料，實現分頁有不同的方式，在MySQL中可以使用LIMIT來限制查詢出的資料。

1. LIMIT用法

LIMIT出現在查詢語句的最後，可以使用一個引數或兩個引數來限制取出的資料。其中第一個引數代表偏移量：offset（可選引數），第二個引數代表取出的資料條數：rows。

• 單引數用法

當指定一個引數時，預設省略了偏移量，即偏移量為0，從第一行資料開始取，一共取rows條。

/* 查詢前5條資料 */
SELECT * FROM Student LIMIT 5;

• 雙引數用法

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

/* 查詢第1-10條資料 */
SELECT * FROM Student LIMIT 0,10;
/* 查詢第11-20條資料 */
SELECT * FROM Student LIMIT 10 OFFSET 10;

2. 分頁公式

• 總頁數計算

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

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

• 核心資訊
  • 當前頁：pageNumber
  • 每頁資料量：pageSize

在實際操作中，我們能夠得到的資訊有當前所在頁以及每頁的資料量，同時要注意一下是否超出了最大頁數。以每頁10條為例，則前三頁的資料應為：

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

據此我們可以總結出，LIMIT所需要的兩個引數計算公式如下：

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

8種MySQL分頁方法總結

方法1: 直接使用資料庫提供的SQL語句

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

—適應場景: 適用於資料量較少的情況(元組百/千級)。

—原因/缺點: 全表掃描,速度會很慢 且 有的資料庫結果集返回不穩定(如某次返回1,2,3,另外的一次返回2,1,3)。Limit限制的是從結果集的M位置處取出N條輸出,其餘拋棄。

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

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

程式碼如下:

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

—適應場景: 適用於資料量多的情況(元組數上萬)。

—原因: 索引掃描,速度會很快。有朋友提出因為資料查詢出來並不是按照pk_id排序的，所以會有漏掉資料的情況，只能方法3。

方法3: 基於索引再排序

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

—適應場景: 適用於資料量多的情況(元組數上萬). 最好ORDER BY後的列物件是主鍵或唯一所以,使得ORDERBY操作能利用索引被消除但結果集是穩定的(穩定的含義,參見方法1)。

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

方法4: 基於索引使用prepare

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

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

程式碼如下:

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

—適應場景: 巨量資料量。

—原因: 索引掃描,速度會很快. 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）

—語句樣式: 不再給出

—適應場景: 巨量資料量. 作者推薦的方法

—原因: 把操作封裝在伺服器，相對更快一些。

方法8: 反面方法

—網上有人寫使用 SQL_CALC_FOUND_ROWS。 沒有道理，勿模仿 。

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

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。