MySQL的兩種分頁方式：Offset/Limit分頁和游標分頁
對于數據庫查詢，優秀的開發和DBA始終遵循一個核心原則：只提取需要的數據。這一原則的意義不僅在于優化查詢性能，也在于減少傳輸和處理不必要的數據。比如，除非確實需要所有列，否則應避免使用SELECT *語句，而是選擇性地提取所需的列。
同樣，這一原則在分頁中也得到了完美體現，區別在于分頁限制的是行數而非列數。通過分頁，我們可以僅向用戶展示一個頁面的數據，而不是加載整個數據集。
在MySQL中，分頁的實現主要有兩種方式：Offset/Limit分頁和游標分頁。選擇具體方式通常取決于業務場景和性能需求。這兩種方法各有優劣，不存在絕對的最佳選擇。下面就帶大家一起來分析這兩種方式各自的特性。
確定性排序的重要性
討論分頁之前，首先需要確保查詢結果具備確定性排序（Deterministic Ordering）。所謂確定性排序，是指查詢結果集在任何情況下都按照完全一致的順序排列。沒有確定性排序的結果集可能因數據庫的內部行為而返回不同的順序，給分頁帶來問題。
看下面的實例，有一個表people，其中有多個名為“Aaron”的人：
id | first_name | last_name |
1 | Aaron | Francis |
2 | Aaron | Smith |
3 | Aaron | Jones |
執行以下查詢：
SELECT *
FROM people
ORDER BY first_name;因為first_name列的值相同，MySQL可能會以不同的順序返回這些行。例如，以下兩組結果都符合查詢條件：
結果集 1：
id | first_name | last_name |
2 | Aaron | Smith |
1 | Aaron | Francis |
3 | Aaron | Jones |
結果集 2：
id | first_name | last_name |
3 | Aaron | Jones |
2 | Aaron | Smith |
1 | Aaron | Francis |
我們沒有給MySQL足夠的指令來生成一個確定性排序的結果集。我們要求按first_name排序，MySQL已經忠實地執行了操作，但返回的行順序可能不同。
生成確定性排序的最簡單方法是按一個唯一列排序，因為每個值都不重復，MySQL只能每次都以相同順序返回行。當然，如果你需要按非唯一列排序，這種做法并不適用！在這種情況下，可以在排序中附加一個唯一列來解決問題。通常，添加id列是最好的選擇。
SELECT *
FROM people
ORDER BY first_name, id -- 添加 ID 以保證確定性排序在同一個first_name值情況下，MySQL會進一步查看id列來決定行的順序，從而實現確定性排序。確保分頁的前提是查詢結果的排序必須具有完全確定性，否則分頁結果可能會出現問題。
Offset/Limit分頁
Offset/Limit分頁可能是MySQL中最常見的分頁方式，因為它最簡單易用。利用這種分頁方式，可以使用兩個SQL關鍵字：OFFSET和LIMIT。LIMIT告訴MySQL需要返回多少行，而OFFSET告訴MySQL需要跳過多少行。
SELECT *
FROM people
ORDER BY first_name, id
LIMIT 10 -- 只返回10行
OFFSET 10 -- 跳過前10行在這個示例中，我們從people表中選擇所有用戶，按first_name和id排序，然后限定結果集為10行，同時跳過前10行，返回第11-20行。
要構建一個Offset/Limit查詢，你需要知道頁面大小（page size）以及頁面編號（page number）。頁面大小是你每頁想顯示的記錄數量，而頁面編號是你想展示的頁面。LIMIT由頁面大小決定，而OFFSET由頁面大小和頁面編號決定。
計算正確的OFFSET時，你可以用以下公式：
OFFSET = (page_number - 1) * page_size例如，第一頁的OFFSET為(1 - 1) * 10 = 0，即不跳過任何行；第二頁的OFFSET為(2 - 1) * 10 = 10，即跳過前10行。
完整的查詢示例如下：
SELECT *
FROM people
ORDER BY first_name, id
LIMIT 10 -- 頁面大小
OFFSET 10 -- (page_number - 1) * page_sizeOffset/Limit分頁的優點
Offset/Limit分頁的一個顯著優點是實現起來簡單易懂。它不需要長期維護任何狀態；每個請求都是獨立的。你不需要關心用戶之前訪問了哪些頁面。查詢構造始終保持一致。數學計算簡單，查詢結構也很直觀。
另一個優點是，頁面直接可尋址。如果用戶想從頁面1直接跳到頁面10，只要你的接口提供頁面鏈接，便很容易實現。（游標分頁無法做到這一點。）
Offset/Limit分頁的缺點
數據漂移問題（Drifting Pages）
Offset/Limit分頁最大的問題是數據漂移。當數據集發生變動（如新增或刪除記錄）時，用戶可能會看到不一致的頁面內容。例如用戶瀏覽頁面1和頁面2時，某條記錄被刪除導致頁面2缺失此前屬于頁面內容的數據。這一問題在游標分頁中也存在，但Offset/Limit分頁更容易發生。
我們來看一個例子。假設用戶正在瀏覽頁面1，頁面包含10條記錄。用戶在頁面1看到的最后一個人是"Judge Bins"，而頁面2的第一條記錄應該是"Sonya Dickens"。
頁面1的記錄：
id | first_name | last_name |
1 | Phillip | Yundt |
2 | Aaron | Francis |
3 | Amelia | West |
4 | Jennifer | Becker |
5 | Macy | Lind |
6 | Simon | Lueilwitz |
7 | Tyler | Cummerata |
8 | Suzanne | Skiles |
9 | Zoe | Hill |
10 | Judge | Bins |
頁面2的記錄（緊接頁面1）：
id | first_name | last_name |
11 | Sonya | Dickens |
12 | Hope | Streich |
13 | Kristian | Kerluke |
14 | Stanton | Fisher |
15 | Rasheed | Little |
但是，當用戶正在瀏覽頁面1時，某個記錄被刪除了，比如id為2的"Aaron Francis"被刪除：
更新后的頁面1記錄：
id | first_name | last_name |
1 | Phillip | Yundt |
3 | Amelia | West |
4 | Jennifer | Becker |
5 | Macy | Lind |
6 | Simon | Lueilwitz |
7 | Tyler | Cummerata |
8 | Suzanne | Skiles |
9 | Zoe | Hill |
10 | Judge | Bins |
更新后的頁面2記錄：
id | first_name | last_name |
11 | Sonya | Dickens |
12 | Hope | Streich |
13 | Kristian | Kerluke |
由于用戶無法直接感知行被刪除的變化，在跳轉到頁面2時會直接跳過"Sonya Dickens"。用戶無法看到她，除非再回退到頁面1。
這種行為在處理不斷變化的數據時非常常見。如果你的用例能夠容忍這一問題，那么Offset/Limit分頁或許仍是一個適當的選擇。不過即使游標分頁也會發生類似問題，但發生的概率較低。
性能缺陷
Offset關鍵字的工作原理是舍棄結果集中的前n行，而非直接跳過這些行進行定位。實際上，它需要讀取這些行并丟棄它們。這意味著當分頁較深時，查詢性能會顯著下降，因為數據庫必須讀取并丟棄更多行。
對于非常深的頁面，查詢可能需要數秒才能完成加載。這是Offset/Limit分頁的一個重大問題，也正是游標分頁被廣泛使用的原因之一。游標分頁沒有這種性能缺陷，因為它不依賴OFFSET。
使用延遲聯結優化性能
針對Offset/Limit分頁，有一種稱為延遲聯結（Deferred Join）的技術可以優化性能。
延遲聯結是一種分頁優化解決方案，它優先在子查詢中過濾出一部分數據，然后再將這部分數據與原始表進行聯結。這種延遲操作可以避免直接對整個表進行分頁，從而提高查詢效率。
示例查詢：
SELECT *
FROM people
INNER JOIN (
-- 僅對一個子查詢進行分頁，而不是對整個表分頁
SELECT id FROM people ORDER BY first_name, id LIMIT 10 OFFSET 450000
) AS tmp USING (id)
ORDER BY first_name, id這種技術已經被廣泛采用，并在流行的Web框架中有相關庫支持，比如Rails中的FastPage和Laravel中的FastPaginate。
對比延遲聯結與標準Offset/Limit分頁的性能，可以看到延遲聯結在處理深度頁面時的優勢。
以下是一個性能對比圖（來自介紹FastPage的博客文章）：
深度頁面數 | 標準分頁耗時 | 延遲聯結耗時 |
1000 | >5秒 | <1秒 |
2000 | >10秒 | 幾乎線性性能 |
如果你決定在項目中使用Offset/Limit分頁，建議考慮使用延遲聯結優化你的查詢。
游標分頁
上面已經了解了Offset/Limit分頁的工作原理，接下來聊聊游標分頁。游標分頁是一種通過“游標”（cursor）決定下一頁結果的分頁方式。需要注意的是，此處的游標概念與數據庫游標不同。在分頁上下文中，游標指的是指針、標識符、令牌或定位器。
游標分頁的工作原理
游標分頁的核心思想是記錄用戶最后看到的記錄，并基于此記錄下一批數據。當用戶請求下一頁數據時，需要提供游標信息，利用游標構建查詢以確定從哪開始返回下一頁數據。
與Offset/Limit分頁不同的是，游標分頁利用WHERE條件來過濾掉用戶已經看過的數據，而不是使用OFFSET跳過。
首次分頁的簡單示例
假設有一個用戶表，按id逐行分頁。當用戶請求數據的第一頁時，沒有游標，因此返回前10行：
SELECT *
FROM people
ORDER BY id
LIMIT 10返回結果如：
id | first_name | last_name |
1 | Phillip | Yundt |
2 | Aaron | Francis |
3 | Amelia | West |
4 | Jennifer | Becker |
5 | Macy | Lind |
6 | Simon | Lueilwitz |
7 | Tyler | Cummerata |
8 | Suzanne | Skiles |
9 | Zoe | Hill |
10 | Judge | Bins |
將游標發送到前端：游標通常為用戶看到的最后一條記錄的標志。在本例中，該游標為id=10。通常游標會進行base64編碼，但為了簡單起見，我們不做此處理。
返回給前端的數據結構：
{
"next_page": "(id=10)",
"records": [
// 第一頁的記錄
]
}當用戶請求下一頁時，需要提供游標信息，服務端利用此游標確定下一頁的記錄。
高級排序的游標分頁
如果需要按多個列排序，游標不僅需要記錄最后一條記錄的ID，還需記錄其他列的排序值。例如如下情況：
假設我們按first_name和id兩列排序，用戶看到的最后一條記錄是(first_name=Aaron, id=25995)，下一頁的游標為(first_name=Aaron, id=25995)。查詢如下：
SELECT *
FROM people
WHERE
(
(first_name > 'Aaron')
OR
(first_name = 'Aaron' AND id > 25995)
)
ORDER BY first_name, id
LIMIT 10總結
分頁方式的選擇需依據具體應用場景與性能要求。如果你的應用允許寬松的精確度或需要支持隨機頁面訪問，Offset/Limit分頁可能是不錯的選擇。然而對于深度分頁或大數據場景，游標分頁表現更為優秀，尤其是在動態數據集上避免了數據漂移問題。兩者并無絕對優劣，最重要的是根據業務需求選擇最適合的實現方式。
