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

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

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

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

Offset/Limit分頁

Offset/Limit分頁可能是MySQL中最常見的分頁方式，因?yàn)樗詈唵我子谩＠眠@種分頁方式，可以使用兩個(gè)SQL關(guān)鍵字：OFFSET和LIMIT。LIMIT告訴MySQL需要返回多少行，而OFFSET告訴MySQL需要跳過多少行。

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

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

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

計(jì)算正確的OFFSET時(shí)，你可以用以下公式：

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)點(diǎn)

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

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

Offset/Limit分頁的缺點(diǎn)

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

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

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

頁面1的記錄：

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

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

更新后的頁面1記錄：

更新后的頁面2記錄：

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

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

性能缺陷

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

對(duì)于非常深的頁面，查詢可能需要數(shù)秒才能完成加載。這是Offset/Limit分頁的一個(gè)重大問題，也正是游標(biāo)分頁被廣泛使用的原因之一。游標(biāo)分頁沒有這種性能缺陷，因?yàn)樗灰蕾?code>OFFSET。

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

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

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

示例查詢：

SELECT *  
FROM people  
INNER JOIN (
  -- 僅對(duì)一個(gè)子查詢進(jìn)行分頁，而不是對(duì)整個(gè)表分頁
  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。

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

以下是一個(gè)性能對(duì)比圖（來自介紹FastPage的博客文章）：

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

游標(biāo)分頁

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

游標(biāo)分頁的工作原理

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

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

首次分頁的簡單示例

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

SELECT *  
FROM people  
ORDER BY id  
LIMIT 10  

返回結(jié)果如：

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

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

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

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

高級(jí)排序的游標(biāo)分頁

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

假設(shè)我們按first_name和id兩列排序，用戶看到的最后一條記錄是(first_name=Aaron, id=25995)，下一頁的游標(biāo)為(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)用允許寬松的精確度或需要支持隨機(jī)頁面訪問，Offset/Limit分頁可能是不錯(cuò)的選擇。然而對(duì)于深度分頁或大數(shù)據(jù)場景，游標(biāo)分頁表現(xiàn)更為優(yōu)秀，尤其是在動(dòng)態(tài)數(shù)據(jù)集上避免了數(shù)據(jù)漂移問題。兩者并無絕對(duì)優(yōu)劣，最重要的是根據(jù)業(yè)務(wù)需求選擇最適合的實(shí)現(xiàn)方式。

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

最新評(píng)論