一、引言

在處理大量數(shù)據(jù)時(shí)，分頁(yè)（Pagination） 是常用的技術(shù)手段。然而，使用 LIMIT 和 OFFSET 進(jìn)行分頁(yè)在數(shù)據(jù)量很大時(shí)可能會(huì)導(dǎo)致性能問(wèn)題。相反，使用 游標(biāo)（Cursor） 或 基于游標(biāo)的分頁(yè)（Cursor-based Pagination） 可以提高查詢效率。

二、為什么盡量使用游標(biāo)而非分頁(yè)？

1. 分頁(yè)的性能問(wèn)題

• 高偏移量性能低下：使用 LIMIT OFFSET 進(jìn)行分頁(yè)，當(dāng) OFFSET 值很大時(shí)，數(shù)據(jù)庫(kù)需要掃描大量數(shù)據(jù)才能跳過(guò)指定的記錄數(shù)，這會(huì)導(dǎo)致查詢性能顯著下降。

• 全表掃描風(fēng)險(xiǎn)：在沒(méi)有合適索引的情況下，分頁(yè)查詢可能導(dǎo)致全表掃描，增加了 I/O 開銷。

2. 游標(biāo)（基于游標(biāo)的分頁(yè)）的優(yōu)勢(shì)

• 更高效的查詢：基于游標(biāo)的分頁(yè)利用索引，直接定位數(shù)據(jù)的位置，不需要跳過(guò)大量記錄，查詢性能更高。

• 數(shù)據(jù)一致性：在數(shù)據(jù)頻繁變化的情況下，游標(biāo)分頁(yè)能更好地保持?jǐn)?shù)據(jù)的一致性，避免數(shù)據(jù)遺漏或重復(fù)。

• 避免重復(fù)和遺漏：基于唯一標(biāo)識(shí)（如 id）的游標(biāo)分頁(yè)，能夠精確地定位數(shù)據(jù)，避免分頁(yè)過(guò)程中出現(xiàn)的數(shù)據(jù)重復(fù)或遺漏問(wèn)題。

三、游標(biāo)分頁(yè)的原理

游標(biāo)分頁(yè)，也稱為 基于鍵集的分頁(yè)（Keyset Pagination），通過(guò)使用上一次查詢結(jié)果的唯一標(biāo)識(shí)（如自增 id、時(shí)間戳等），在下一次查詢時(shí)作為游標(biāo)進(jìn)行定位，獲取后續(xù)的數(shù)據(jù)。

• 基本思想：使用 WHERE 子句篩選出比上次最后一條記錄大的數(shù)據(jù)，配合 LIMIT 獲取下一頁(yè)數(shù)據(jù)。

• 優(yōu)點(diǎn)：利用索引查找，性能與數(shù)據(jù)量無(wú)關(guān)，查詢速度更快。

四、優(yōu)化后的示例（非存儲(chǔ)過(guò)程）

1. 數(shù)據(jù)表結(jié)構(gòu)

假設(shè)有一個(gè) users 表：

2. 傳統(tǒng)分頁(yè)的缺點(diǎn)

傳統(tǒng)分頁(yè)查詢（第 N 頁(yè)，每頁(yè) 15 條）：

SELECT id, name, email FROM users
ORDER BY id ASC
LIMIT 15 OFFSET (N - 1) * 15;

• 問(wèn)題：當(dāng) N 很大時(shí)，OFFSET 也很大，數(shù)據(jù)庫(kù)需要掃描和跳過(guò)大量記錄，性能下降。

3. 使用游標(biāo)分頁(yè)的優(yōu)化查詢

步驟：

1. 獲取初始游標(biāo)值：首次查詢時(shí)，無(wú)需游標(biāo)，或設(shè)置為 0。

2. 查詢數(shù)據(jù)：使用游標(biāo)值作為條件，獲取下一頁(yè)的數(shù)據(jù)。

3. 更新游標(biāo)值：將本次查詢結(jié)果的最后一條記錄的 id，作為下一次查詢的游標(biāo)。

示例：

• 首次查詢（初始游標(biāo)為 0）：

  SELECT id, name, email FROM users
  WHERE id > 0
  ORDER BY id ASC
  LIMIT 15;
  

• 后續(xù)查詢（假設(shè)上次最后一條記錄的 id 為 15）：

  SELECT id, name, email FROM users
  WHERE id > 15
  ORDER BY id ASC
  LIMIT 15;
  

• 更新游標(biāo)值：

  • 在應(yīng)用程序中，將本次查詢結(jié)果的最后一個(gè) id 保存為新的游標(biāo)值，用于下次查詢。

4. 示例代碼（Java 實(shí)現(xiàn)）

假設(shè)使用 JDBC 進(jìn)行數(shù)據(jù)庫(kù)操作：

public List<User> getUsersAfterId(int lastId, int pageSize) throws SQLException {
    String sql = "SELECT id, name, email FROM users WHERE id > ? ORDER BY id ASC LIMIT ?";
    List<User> userList = new ArrayList<>();

    try (Connection conn = dataSource.getConnection();
         PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setInt(1, lastId);
        pstmt.setInt(2, pageSize);

        try (ResultSet rs = pstmt.executeQuery()) {
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setName(rs.getString("name"));
                user.setEmail(rs.getString("email"));
                userList.add(user);
            }
        }
    }
    return userList;
}

使用方法：

• 首次查詢（lastId = 0）：

  List<User> usersPage1 = getUsersAfterId(0, 15);
  

• 后續(xù)查詢（使用上次的最后一個(gè) id）：

  int lastId = usersPage1.get(usersPage1.size() - 1).getId();
  List<User> usersPage2 = getUsersAfterId(lastId, 15);
  

5. 前端參數(shù)的傳遞

• 首次請(qǐng)求：/api/users?lastId=0

• 響應(yīng)數(shù)據(jù)：

  {
      "data": [/* 用戶數(shù)據(jù)數(shù)組 */],
      "lastId": 15
  }
  

• 下一次請(qǐng)求：使用上次返回的 lastId

  /api/users?lastId=15

五、游標(biāo)分頁(yè)的注意事項(xiàng)

1. 必須有連續(xù)且有序的唯一標(biāo)識(shí)符：如自增的 id、唯一的時(shí)間戳等。

2. 適用于固定排序的情況：游標(biāo)分頁(yè)通?；谀硞€(gè)字段的排序（如 id ASC），在排序方式固定的情況下效果最佳。

3. 無(wú)法直接跳轉(zhuǎn)到任意頁(yè)：游標(biāo)分頁(yè)更適合“上一頁(yè)”“下一頁(yè)”的翻頁(yè)方式，而不適合直接跳轉(zhuǎn)到指定頁(yè)碼。

4. 數(shù)據(jù)的一致性：在數(shù)據(jù)可能被插入或刪除的情況下，游標(biāo)分頁(yè)能更好地保持?jǐn)?shù)據(jù)的一致性，避免傳統(tǒng)分頁(yè)可能出現(xiàn)的記錄重復(fù)或遺漏。

六、游標(biāo)分頁(yè)與 B+ 樹索引的關(guān)系

• 利用索引：游標(biāo)分頁(yè)使用了索引字段（如 id）進(jìn)行篩選和排序，數(shù)據(jù)庫(kù)可以利用 B+ 樹索引高效地定位和讀取數(shù)據(jù)。

• 避免全表掃描：相比高偏移量的 LIMIT OFFSET，游標(biāo)分頁(yè)的查詢條件可以使數(shù)據(jù)庫(kù)直接跳轉(zhuǎn)到指定位置，避免掃描不必要的行。

七、總結(jié)

• 性能優(yōu)勢(shì)：在數(shù)據(jù)量較大的情況下，游標(biāo)分頁(yè)比傳統(tǒng)的 LIMIT OFFSET 分頁(yè)性能更高。

• 實(shí)現(xiàn)簡(jiǎn)單：無(wú)需使用存儲(chǔ)過(guò)程，可以在應(yīng)用程序?qū)用鎸?shí)現(xiàn)，代碼簡(jiǎn)單易懂。

• 適用場(chǎng)景：適用于需要高效處理大量數(shù)據(jù)且只需“上一頁(yè)”“下一頁(yè)”導(dǎo)航的場(chǎng)景。

• 優(yōu)先選擇游標(biāo)分頁(yè)：在可能的情況下，應(yīng)盡量使用游標(biāo)分頁(yè)來(lái)替代傳統(tǒng)的分頁(yè)方式，以獲得更好的性能和數(shù)據(jù)一致性。

• 結(jié)合實(shí)際場(chǎng)景：根據(jù)具體的業(yè)務(wù)需求和數(shù)據(jù)規(guī)模，選擇合適的分頁(yè)策略，確保系統(tǒng)的性能和穩(wěn)定性。

參考資料

• MySQL官方文檔：優(yōu)化查詢
• 高性能MySQL（第3版）
• Efficient Pagination Using MySQL