我們沒有給MySQL足夠的指令來生成一個確定性排序的結(jié)果集。我們要求按first_name排序，MySQL已經(jīng)忠實地執(zhí)行了操作，但返回的行順序可能不同。

生成確定性排序的最簡單方法是按一個唯一列排序，因為每個值都不重復(fù)，MySQL只能每次都以相同順序返回行。當(dāng)然，如果你需要按非唯一列排序，這種做法并不適用！在這種情況下，可以在排序中附加一個唯一列來解決問題。通常，添加id列是最好的選擇。

SELECT *  
FROM people  
ORDER BY first_name, id -- 添加 ID 以保證確定性排序  

在同一個first_name值情況下，MySQL會進一步查看id列來決定行的順序，從而實現(xiàn)確定性排序。確保分頁的前提是查詢結(jié)果的排序必須具有完全確定性，否則分頁結(jié)果可能會出現(xiàn)問題。

Offset/Limit分頁

Offset/Limit分頁可能是MySQL中最常見的分頁方式，因為它最簡單易用。利用這種分頁方式，可以使用兩個SQL關(guān)鍵字：OFFSET和LIMIT。LIMIT告訴MySQL需要返回多少行，而OFFSET告訴MySQL需要跳過多少行。

SELECT *  
FROM people  
ORDER BY first_name, id  
LIMIT 10 -- 只返回10行  
OFFSET 10 -- 跳過前10行  

在這個示例中，我們從people表中選擇所有用戶，按first_name和id排序，然后限定結(jié)果集為10行，同時跳過前10行，返回第11-20行。

要構(gòu)建一個Offset/Limit查詢，你需要知道頁面大?。╬a ge size）以及頁面編號（pa ge number）。頁面大小是你每頁想顯示的記錄數(shù)量，而頁面編號是你想展示的頁面。LIMIT由頁面大小決定，而OFFSET由頁面大小和頁面編號決定。

計算正確的OFFSET時，你可以用以下公式：

OFFSET = (pa ge_number - 1) * pa ge_size  

例如，第一頁的OFFSET為(1 - 1) * 10 = 0，即不跳過任何行；第二頁的OFFSET為(2 - 1) * 10 = 10，即跳過前10行。

完整的查詢示例如下：

SELECT *  
FROM people  
ORDER BY first_name, id  
LIMIT 10 -- 頁面大小  
OFFSET 10 -- (pa ge_number - 1) * pa ge_size  

Offset/Limit分頁的優(yōu)點

Offset/Limit分頁的一個顯著優(yōu)點是實現(xiàn)起來簡單易懂。它不需要長期維護任何狀態(tài)；每個請求都是獨立的。你不需要關(guān)心用戶之前訪問了哪些頁面。查詢構(gòu)造始終保持一致。數(shù)學(xué)計算簡單，查詢結(jié)構(gòu)也很直觀。

另一個優(yōu)點是，頁面直接可尋址。如果用戶想從頁面1直接跳到頁面10，只要你的接口提供頁面鏈接，便很容易實現(xiàn)。（游標分頁無法做到這一點。）

Offset/Limit分頁的缺點

數(shù)據(jù)漂移問題（Drifting Pa ges）

Offset/Limit分頁最大的問題是數(shù)據(jù)漂移。當(dāng)數(shù)據(jù)集發(fā)生變動（如新增或刪除記錄）時，用戶可能會看到不一致的頁面內(nèi)容。例如用戶瀏覽頁面1和頁面2時，某條記錄被刪除導(dǎo)致頁面2缺失此前屬于頁面內(nèi)容的數(shù)據(jù)。這一問題在游標分頁中也存在，但Offset/Limit分頁更容易發(fā)生。

我們來看一個例子。假設(shè)用戶正在瀏覽頁面1，頁面包含10條記錄。用戶在頁面1看到的最后一個人是"Judge Bins"，而頁面2的第一條記錄應(yīng)該是"Sonya Dickens"。

頁面1的記錄：

頁面2的記錄（緊接頁面1）：

但是，當(dāng)用戶正在瀏覽頁面1時，某個記錄被刪除了，比如id為2的"Aaron Francis"被刪除：

更新后的頁面1記錄：

更新后的頁面2記錄：

由于用戶無法直接感知行被刪除的變化，在跳轉(zhuǎn)到頁面2時會直接跳過"Sonya Dickens"。用戶無法看到她，除非再回退到頁面1。

這種行為在處理不斷變化的數(shù)據(jù)時非常常見。如果你的用例能夠容忍這一問題，那么Offset/Limit分頁或許仍是一個適當(dāng)?shù)倪x擇。不過即使游標分頁也會發(fā)生類似問題，但發(fā)生的概率較低。

性能缺陷

Offset關(guān)鍵字的工作原理是舍棄結(jié)果集中的前n行，而非直接跳過這些行進行定位。實際上，它需要讀取這些行并丟棄它們。這意味著當(dāng)分頁較深時，查詢性能會顯著下降，因為數(shù)據(jù)庫必須讀取并丟棄更多行。

對于非常深的頁面，查詢可能需要數(shù)秒才能完成加載。這是Offset/Limit分頁的一個重大問題，也正是游標分頁被廣泛使用的原因之一。游標分頁沒有這種性能缺陷，因為它不依賴OFFSET。

使用延遲聯(lián)結(jié)優(yōu)化性能

針對Offset/Limit分頁，有一種稱為延遲聯(lián)結(jié)（Deferred Join）的技術(shù)可以優(yōu)化性能。

延遲聯(lián)結(jié)是一種分頁優(yōu)化解決方案，它優(yōu)先在子查詢中過濾出一部分數(shù)據(jù)，然后再將這部分數(shù)據(jù)與原始表進行聯(lián)結(jié)。這種延遲操作可以避免直接對整個表進行分頁，從而提高查詢效率。

示例查詢：

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  

這種技術(shù)已經(jīng)被廣泛采用，并在流行的Web框架中有相關(guān)庫支持，比如Rails中的FastPage和Laravel中的FastPaginate。

對比延遲聯(lián)結(jié)與標準Offset/Limit分頁的性能，可以看到延遲聯(lián)結(jié)在處理深度頁面時的優(yōu)勢。

以下是一個性能對比圖（來自介紹FastPage的博客文章）：

如果你決定在項目中使用Offset/Limit分頁，建議考慮使用延遲聯(lián)結(jié)優(yōu)化你的查詢。

游標分頁

上面已經(jīng)了解了Offset/Limit分頁的工作原理，接下來聊聊游標分頁。游標分頁是一種通過“游標”（cursor）決定下一頁結(jié)果的分頁方式。需要注意的是，此處的游標概念與數(shù)據(jù)庫游標不同。在分頁上下文中，游標指的是指針、標識符、令牌或定位 器。

游標分頁的工作原理

游標分頁的核心思想是記錄用戶最后看到的記錄，并基于此記錄下一批數(shù)據(jù)。當(dāng)用戶請求下一頁數(shù)據(jù)時，需要提供游標信息，利用游標構(gòu)建查詢以確定從哪開始返回下一頁數(shù)據(jù)。

與Offset/Limit分頁不同的是，游標分頁利用WHERE條件來過濾掉用戶已經(jīng)看過的數(shù)據(jù)，而不是使用OFFSET跳過。

首次分頁的簡單示例

假設(shè)有一個用戶表，按id逐行分頁。當(dāng)用戶請求數(shù)據(jù)的第一頁時，沒有游標，因此返回前10行：

SELECT *  
FROM people  
ORDER BY id  
LIMIT 10  

返回結(jié)果如：

將游標發(fā)送到前端：游標通常為用戶看到的最后一條記錄的標志。在本例中，該游標為id=10。通常游標會進行base64編碼，但為了簡單起見，我們不做此處理。

返回給前端的數(shù)據(jù)結(jié)構(gòu)：

{
  "next_page": "(id=10)",
  "records": [
    // 第一頁的記錄
  ]
}

當(dāng)用戶請求下一頁時，需要提供游標信息，服務(wù)端利用此游標確定下一頁的記錄。

高級排序的游標分頁

如果需要按多個列排序，游標不僅需要記錄最后一條記錄的ID，還需記錄其他列的排序值。例如如下情況：

假設(shè)我們按first_name和id兩列排序，用戶看到的最后一條記錄是(first_name=Aaron, id=25995)，下一頁的游標為(first_name=Aaron, id=25995)。查詢?nèi)缦拢?/p>

SELECT *  
FROM people  
WHERE  
  (
    (first_name > 'Aaron')  
    OR  
    (first_name = 'Aaron' AND id > 25995)  
  )  
ORDER BY first_name, id  
LIMIT 10  

總結(jié)

分頁方式的選擇需依據(jù)具體應(yīng)用場景與性能要求。如果你的應(yīng)用允許寬松的精確度或需要支持隨機頁面訪問，Offset/Limit分頁可能是不錯的選擇。然而對于深度分頁或大數(shù)據(jù)場景，游標分頁表現(xiàn)更為優(yōu)秀，尤其是在動態(tài)數(shù)據(jù)集上避免了數(shù)據(jù)漂移問題。兩者并無絕對優(yōu)劣，最重要的是根據(jù)業(yè)務(wù)需求選擇最適合的實現(xiàn)方式。

以上就是MySQL的兩種分頁方式之Offset/Limit分頁和游標分頁詳解的詳細內(nèi)容，更多關(guān)于MySQL分頁方式Offset/Limit和游標的資料請關(guān)注腳本之家其它相關(guān)文章！

最新評論