Limitching's Blog

MySQL分頁方式大解析：Offset/Limit 與 Cursor

Limit
Limit
Gentle and strong, Limit shines.
Cover Image for MySQL分頁方式大解析：Offset/Limit 與 Cursor

分頁的基本概念

在開始之前，我們先來了解什麼是分頁，以及為什麼需要分頁。

當資料庫中的資料量很大時，一次性查詢並返回所有資料是不切實際的。這不僅會消耗大量的記憶體和網路資源，還會導致用戶端無法快速獲取結果。為了提升用戶體驗，我們通常會將資料分成多個「頁」，每次只查詢和顯示一頁的內容。

假設我們有一個名為 tbl_customers 的 Table，裡面有我們建立好的 1000000 筆假資料，其中 Primary Key 為 id。

而資料大概長這個樣子：

| id  | first_name | last_name   | email                      | created_time |
|-----|------------|-------------|----------------------------|--------------|
| 1   | Timmy      | Ernser      | Aileen.Harvey@yahoo.com    | 1729566959   |
| 2   | Bette      | Wintheiser  | Murl.Jones@yahoo.com       | 1729566959   |
| 3   | Montana    | Cassin      | Emelia.Jaskoli@hotmail.com | 1729566959   |
| 4   | Madaline   | Heller      | Izabella31@gmail.com       | 1729566959   |
| 5   | Jennings   | Little      | Tyrese.Grimes@yahoo.com    | 1729566959   |

 

Offset/Limit 分頁

基本原理

OFFSET 和 LIMIT 是 SQL 中用於分頁的標準關鍵字。LIMIT 用於指定返回的記錄數量，OFFSET 用於指定跳過的記錄數量。

例如，要獲取第 3 頁的資料（假設每頁顯示 10 條記錄），可以使用以下查詢：

SELECT * FROM tbl_customers ORDER BY id LIMIT 10 OFFSET 20;

這表示跳過前 20 條記錄，然後取出接下來的 10 條記錄。

| id  | first_name | last_name   | email                             | created_time |
|-----|------------|-------------|-----------------------------------|--------------|
| 21  | Agustin    | Runolfsson  | Cloyd32@hotmail.com               | 1729566959   |
| 22  | Jalyn      | Koepp       | Alford76@hotmail.com              | 1729566959   |
| 23  | Mayra      | Auer        | Marcel7@hotmail.com               | 1729566959   |
| 24  | Lou        | Aufderhar   | Stanley.Conroy@gmail.com          | 1729566959   |
| 25  | Keegan     | Weber       | Al_Gislason42@hotmail.com         | 1729566959   |
| 26  | Orland     | Windler     | Cassandra11@hotmail.com           | 1729566959   |
| 27  | Brycen     | Rau         | Mckenna14@yahoo.com               | 1729566959   |
| 28  | Adrianna   | Paucek      | Zechariah.Keebler75@gmail.com     | 1729566959   |
| 29  | Brooke     | Streich     | Liliane97@hotmail.com             | 1729566959   |
| 30  | Arnoldo    | King        | Gennaro44@hotmail.com             | 1729566959   |

優點

  • 簡單直觀：使用起來非常簡單，易於理解和實現。
  • 通用性高：幾乎所有的資料庫都支持 OFFSET 和 LIMIT。

缺點

  • 效能問題：當 OFFSET 值很大時，資料庫需要掃描並丟棄大量的記錄，導致查詢速度變慢。
  • 資料不一致性：如果在分頁期間有資料被插入或刪除，可能會導致資料重複或遺漏。
效能問題

對效能問題有興趣的朋友，可以看看這篇文章 -> 使用 OFFSET 和 LIMIT 會導致 MySQL 查詢速度變慢?

資料不一致性

關於資料不一致性的問題，讓我們來看一個範例：

阿晴是一名超級業務，他透過公司內部系統的潛在客戶資料進行 email 的陌生開發，公司內部系統用的是 OFFSET/LIMIT 的分頁方式，一頁有十筆客戶資料。

| id  | first_name | last_name   | email                             |
|-----|------------|-------------|-----------------------------------| 分頁線 (第一頁開始)
| 1   | Timmy      | Ernser      | Aileen.Harvey@yahoo.com           |
| 2   | Bette      | Wintheiser  | Murl.Jones@yahoo.com              |
| 3   | Montana    | Cassin      | Emelia.Jaskolski@hotmail.com      |
| 4   | Madaline   | Heller      | Izabella31@gmail.com              |
| 5   | Jennings   | Little      | Tyrese.Grimes@yahoo.com           |
| 6   | Torey      | Weissnat    | Annamarie.Ortiz29@yahoo.com       |
| 7   | Hailee     | Nolan       | Hester46@yahoo.com                |
| 8   | Nayeli     | Mueller     | Braxton_Bashirian36@hotmail.com   |
| 9   | Jaylon     | Sanford     | Nia.Kihn87@hotmail.com            |
| 10  | Darren     | Big         | Michel37@yahoo.com                |
|-----|------------|-------------|-----------------------------------| 分頁線 (第二頁開始)
| 11  | Dylan      | Chen        | Dayana44@yahoo.com                | <-- Dylan 原本是第二頁的開頭
| 12  | Neil       | Yundt       | Rhianna27@hotmail.com             |
| 13  | Ernestina  | O'Keefe     | Gwen79@hotmail.com                |
| 14  | Shayne     | Daugherty   | Fred.Thompson@yahoo.com           |
| 15  | Mandy      | Schmeler    | Elbert.Collins82@gmail.com        |
| 16  | Tomasa     | Johnston    | Reginald63@yahoo.com              |
| 17  | Anissa     | Mertz       | Ocie21@gmail.com                  |
| 18  | Barrett    | Bosco       | Keith_Romaguera@hotmail.com       |
| 19  | Ally       | Rohan       | Tiara47@gmail.com                 |
| 20  | Kimberly   | Zieme       | Sarai15@gmail.com                 |

阿晴聯繫完第一頁的潛在客戶後，正要切換到第二頁時，有一位同事於此時刪掉了 id = 10 的這筆資料 （Darren Big） [討論1] （Darren Big），這時，分頁的 SQL 語句所形成的分頁如下：

| id  | first_name | last_name   | email                             |
|-----|------------|-------------|-----------------------------------| 分頁線 (第一頁開始)
| 1   | Timmy      | Ernser      | Aileen.Harvey@yahoo.com           |
| 2   | Bette      | Wintheiser  | Murl.Jones@yahoo.com              |
| 3   | Montana    | Cassin      | Emelia.Jaskolski@hotmail.com      |
| 4   | Madaline   | Heller      | Izabella31@gmail.com              |
| 5   | Jennings   | Little      | Tyrese.Grimes@yahoo.com           |
| 6   | Torey      | Weissnat    | Annamarie.Ortiz29@yahoo.com       |
| 7   | Hailee     | Nolan       | Hester46@yahoo.com                |
| 8   | Nayeli     | Mueller     | Braxton_Bashirian36@hotmail.com   |
| 9   | Jaylon     | Sanford     | Nia.Kihn87@hotmail.com            |
| 11  | Dylan      | Hsieh       | Dayana44@yahoo.com                | <-- Dylan 現在在第一頁了 😭
|-----|------------|-------------|-----------------------------------| 分頁線 (第二頁開始)
| 12  | Neil       | Chen        | Rhianna27@hotmail.com             | <-- Neil 現在是第二頁的開頭了！
| 13  | Ernestina  | O'Keefe     | Gwen79@hotmail.com                |
| 14  | Shayne     | Daugherty   | Fred.Thompson@yahoo.com           |
| 15  | Mandy      | Schmeler    | Elbert.Collins82@gmail.com        |
| 16  | Tomasa     | Johnston    | Reginald63@yahoo.com              |
| 17  | Anissa     | Mertz       | Ocie21@gmail.com                  |
| 18  | Barrett    | Bosco       | Keith_Romaguera@hotmail.com       |
| 19  | Ally       | Rohan       | Tiara47@gmail.com                 |
| 20  | Kimberly   | Zieme       | Sarai15@gmail.com                 |
| 21  | Agustin    | Runolfsson  | Cloyd32@hotmail.com               |

由於 id = 10 的這筆資料 （Darren Big）被刪除了，這時，阿晴看到第二頁的第一筆資料是 Neil Chen，因為我們 OFFSET 了 10 筆資料，所以 Dylan 完全被跳過了，可憐的 Dylan ，除非切回第一頁，否則業務們不會看到他...

 

Cursor 分頁

基本原理

Cursor 分頁不使用 OFFSET，而是基於某個唯一且連續的欄位（通常是 Primary Key 或 TimeStamp ）來進行分頁。每次查詢都記錄上一次查詢的最後一個值，下一次查詢時，以此作為起點。

例如：

-- 第 1 頁

SELECT * FROM tbl_customers WHERE id > 0 ORDER BY id LIMIT 10;

| id  | first_name | last_name   | email                             |
|-----|------------|-------------|-----------------------------------|
| 1   | Timmy      | Ernser      | Aileen.Harvey@yahoo.com           |
| 2   | Bette      | Wintheiser  | Murl.Jones@yahoo.com              |
| 3   | Montana    | Cassin      | Emelia.Jaskolski@hotmail.com      |
| 4   | Madaline   | Heller      | Izabella31@gmail.com              |
| 5   | Jennings   | Little      | Tyrese.Grimes@yahoo.com           |
| 6   | Torey      | Weissnat    | Annamarie.Ortiz29@yahoo.com       |
| 7   | Hailee     | Nolan       | Hester46@yahoo.com                |
| 8   | Nayeli     | Mueller     | Braxton_Bashirian36@hotmail.com   |
| 9   | Jaylon     | Sanford     | Nia.Kihn87@hotmail.com            |
| 10  | Darren     | Big         | Michel37@yahoo.com                |

-- 第 2 頁

搜尋第二頁時，會將第一頁查詢的最後一個值做為參數帶入查詢語句：

-- 假設第 1 頁的最後一個 id 是 10
SELECT * FROM tbl_customers WHERE id > 10 ORDER BY id LIMIT 10;

| id  | first_name | last_name   | email                             |
|-----|------------|-------------|-----------------------------------|
| 11  | Dylan      | Chen        | Dayana44@yahoo.com                |
| 12  | Neil       | Yundt       | Rhianna27@hotmail.com             |
| 13  | Ernestina  | O'Keefe     | Gwen79@hotmail.com                |
| 14  | Shayne     | Daugherty   | Fred.Thompson@yahoo.com           |
| 15  | Mandy      | Schmeler    | Elbert.Collins82@gmail.com        |
| 16  | Tomasa     | Johnston    | Reginald63@yahoo.com              |
| 17  | Anissa     | Mertz       | Ocie21@gmail.com                  |
| 18  | Barrett    | Bosco       | Keith_Romaguera@hotmail.com       |
| 19  | Ally       | Rohan       | Tiara47@gmail.com                 |
| 20  | Kimberly   | Zieme       | Sarai15@gmail.com                 |

優點

  • 效率高：避免了掃描和丟棄大量的記錄，查詢速度快且穩定。
  • 資料一致性：在資料變動的情況下，能夠保持資料的連續性，不會出現重複或遺漏。

缺點

  • 需要唯一且連續的欄位：必須基於索引或唯一欄位進行分頁。
  • 無法隨機跳頁：只能順序地向前或向後翻頁，無法直接跳轉到指定頁數。

 

用非唯一欄位進行分頁

若今天需求更改成分頁要依據 first_name 欄位排序，然後再依據 id 進行排序呢？

可是 first_name 這個欄位的資料是可重複的欸？怎麼辦？

如果使用 Cursor 分頁方式，這時就需要紀錄使用者看到的最後一筆的兩個值 （ first_name & id ）

用以下範例為例：

| id     | first_name | last_name      | email                                  |
|--------|------------|----------------|---------------------------------------|
| 3147   | Azole      | Collier        | Natalie_Cormier@hotmail.com           |
| 4830   | Azole      | Streich        | Efren_Little@yahoo.com                |
| 5882   | Azole      | Cummings       | Katlynn30@hotmail.com                 |
| 6118   | Azole      | Oberbrunner    | Yessenia.Mertz@yahoo.com              |
| 6493   | Azole      | Hermann-Orn    | Aiyana49@yahoo.com                    |
| 9761   | Azole      | Haag           | Brenda87@hotmail.com                  |
| 11701  | Azole      | Prosacco       | Ursula_Ward@yahoo.com                 |
| 13488  | Azole      | Schaefer       | Dewitt_Thompson84@yahoo.com           |
| 17181  | Azole      | Bednar         | Maybell.Waters93@gmail.com            |
| 18166  | Azole      | Maggio         | Daphnee.Balistreri43@yahoo.com        |
|--------|------------|----------------|---------------------------------------| 分頁線 (第二頁開始)
| 19283  | Azole      | Thiel          | Christine70@hotmail.com               |
| 21004  | Azole      | Blanda         | Keenan80@gmail.com                    |
| 23605  | Azole      | Fahey          | Edmund3@gmail.com                     |
| 25686  | Azole      | Stiedemann     | Estella_Morar54@yahoo.com             |
| 34392  | Azole      | Auer           | Gretchen.Hauck@hotmail.com            |
| 34915  | Azole      | Jacobs         | Domenick_Cartwright96@yahoo.com       |
| 36143  | Azole      | Lang           | Gladys94@yahoo.com                    |
| 36583  | Azole      | Koss           | Mya.Wisoky28@yahoo.com                |
| 38666  | Azole      | Runolfsson     | Oren_OKon77@gmail.com                 |
| 40412  | Azole      | Wyman          | Oscar52@hotmail.com                   |

在此範例中，使用者在第 1 頁看到的最後一筆記錄的 id 為 18166。第 1 頁最後一筆記錄的 Cursor 是 (first_name = Azole, id = 18166)。

當我們獲得 Cursor 時，我們可以撰寫一個 WHERE 子句來過濾掉使用者已經看到的所有資料。這次， SQL 的查詢語句會變得複雜，因為我們希望按 first_name 和 id 兩個欄位的順序進行排序。

SELECT
  *
FROM
  test_db.tbl_customers
WHERE
  (
    (first_name > 'Azole')                -- Names after Azole
    OR
    (first_name = 'Azole' AND id > 18166) -- Azole, but after the last id that the user saw
  )
ORDER BY
  first_name, id
LIMIT
    10

可以看到我在 WHERE 子句中加入了兩個條件，並且聯集起來：

  1. first_name > 'Azole'
  2. first_name = 'Azole' AND id > 18166

原因是為了要顯示「Azole」之後的任何名稱，但由於 first_name 有許多重複的資料，因此我們得增加另一個 id 的過濾來顯示在用戶看到的最後一個 id 之後的任何「Azole」。

由上述範例可以得知，若使用非唯一的欄位作為分頁的條件，WHERE 子句將會變得十分複雜，若排序條件有多個非唯一欄位時，Cursor 分頁的實作將會變得非常複雜，我們必須確保在 WHERE 子句中，不僅要過濾掉已經看到的資料，還要保持正確的排序，這樣才能維持分頁的準確性。

 

討論

刪除資料

讓使用者有權限從資料庫刪除資料是好的系統設計嗎？

對會不斷變動的資料進行分頁的確是是一個大難題，但直接讓使用者有權限能夠刪除資料庫裡面的資料，這一定不是一個好的系統設計。

再者，公司的 DevOps 應該不會同意 RD 拿 root 或 admin 的 DB 權限給系統使用。很多企業都會有對應的資料管理政策，因此現今主流的系統不太會真的讓使用者刪除資料，反而會利用 Data Model 上的設計來達成類似刪除資料的效果，例如在資料欄位上加上一個 delete marker 的 flag 來判斷資料是否被刪除，撈取資料時就過濾掉具有此標記的資料，如此一來便能達成類似刪除的效果。

 

結論

Offset/Limit 適用場合

  • 資料量較小：在資料量不大的情況下，性能影響不明顯。
  • 需要隨機跳頁：用戶需要能夠直接跳轉到指定的頁數。

Cursor 分頁適用場合

  • 資料量巨大：在大資料量下，性能優勢明顯。
  • 順序瀏覽：用戶按順序瀏覽資料，例如社交媒體的動態流。

選擇合適的分頁方式

  • 性能考量：如果性能是主要考量，且可以接受無法隨機跳頁，建議使用 Cursor 分頁。
  • 用戶體驗：如果用戶需要能夠隨機跳頁，且資料量不大，Offset/Limit 可能更適合。

上一篇

← 為什麼想要建立屬於自己的Blog?

下一篇

使用 OFFSET/LIMIT 會導致 MySQL 查詢速度變慢? →