最好的数据库分页技术是......

👋 简介

数据库是每个应用程序的基石之一。它存储着应用程序需要记住、后续计算或在线显示给其他用户的所有数据。这一切都很有趣，直到数据库增长，应用程序开始卡顿，因为你试图一次性获取并渲染 1,000 条帖子。好吧，你是个聪明的工程师，对吧？你快速地点击了一下按钮解决了这个问题Show more。几周后，你收到了一个新的Timeout error！你前往 Stack Overflow，但很快意识到这个问题，Ctrl并且V由于过度使用而停止了工作🤦 由于没有其他选择，你开始调试，并发现每次用户打开你的应用程序时，数据库都会返回超过 50,000 条帖子！我们现在该怎么办？

为了避免这些可怕的情况，我们应该从一开始就意识到风险，因为准备充分的开发人员永远不会冒险。本文将指导您使用偏移量和游标分页来应对与数据库相关的性能问题。

“一分预防胜过十分治疗。” ——本杰明·富兰克林

📚 什么是分页？

分页是查询包含超过几百条记录的数据集时采用的一种策略。借助分页，我们可以将大型数据集拆分成多个块（或页面），然后逐步提取并显示给用户，从而减轻数据库的负载。分页还可以解决客户端和服务器端的许多性能问题！如果没有分页，您将不得不加载整个聊天记录才能阅读最新收到的消息。

如今，分页几乎已成为必需品，因为每个应用程序都很可能处理大量数据。这些数据可能是用户生成的内容、管理员或编辑添加的内容，也可能是自动生成的审核和日志。一旦您的列表增长到数千个项目以上，您的数据库将需要很长时间来处理每个请求，前端的速度和可访问性将受到影响。对于您的用户来说，他们的体验将是这样的。

现在我们知道了分页是什么，那么我们该如何使用它呢？为什么需要分页呢？

🔍 分页类型

有两种广泛使用的分页策略：offset和cursor。在深入研究并学习有关它们的一切之前，让我们先看看一些使用它们的网站。

首先，让我们访问 GitHub 的Stargazer 页面，注意标签页显示的是 和5,000+，而不是绝对数字。此外，他们没有使用标准页码，而是使用了Previous和Next按钮。

现在，让我们切换到亚马逊的产品列表，并注意结果的确切数量364，以及您可以点击的所有页码的标准分页1 2 3 ... 20。

很明显，两家科技巨头在哪个解决方案更好这个问题上无法达成一致！为什么？好吧，我们得用一个开发者讨厌的答案Because it depends。让我们来探索一下这两种方法，了解它们的优势、局限性以及对性能的影响。

偏移分页

大多数网站使用偏移分页，因为它简单且对用户来说直观。要实现偏移分页，我们通常需要两点信息：

• limit- 从数据库获取的行数
• offset- 要跳过的行数。偏移量类似于页码，但包含一些数学知识 ( offset = (page-1) * limit)

为了获取第一页数据，我们将限制设置为 10（因为我们希望一页包含 10 个项目），并将偏移量设置为 0（因为我们希望从第 0 个项目开始计数 10 个项目）。结果将包含 10 行数据。

为了获取第二页，我们将限制保留为 10（由于我们希望每页包含 10 行，因此此限制保持不变），并将偏移量设置为 10（返回第 10 行及之后的结果）。我们延续这种方法，从而允许最终用户分页浏览结果并查看所有内容。

在 SQL 世界中，这样的查询可以写为SELECT * FROM posts OFFSET 10 LIMIT 10。

一些实现偏移分页的网站还会显示最后一页的页码。他们是怎么做到的呢？除了每页的结果外，他们通常还会返回一个sum属性，告诉你总共有多少行。使用limit，sum并进行一些数学运算，你可以使用以下命令计算最后一页的页码：lastPage = ceil(sum / limit)

虽然此功能为用户带来了便利，但开发人员却难以扩展此类分页功能。查看sum属性，我们已经可以看到，将数据库中的所有行数精确到行数需要相当长的时间。此外，offset数据库中的实现方式是循环遍历行数来确定应该跳过多少行。这意味着偏移量越大，数据库查询所需的时间就越长。

偏移分页的另一个缺点是它无法很好地处理实时数据或经常变化的数据。偏移量表示我们想要跳过多少行，但不考虑行删除或新行的创建。这样的偏移量可能会导致显示重复数据或某些数据丢失。

光标分页

游标是偏移分页的继承者，它解决了偏移分页的所有问题——性能、数据丢失和数据重复，因为它不像偏移分页那样依赖于行的相对顺序。相反，它依赖于数据库创建和管理的索引。要实现游标分页，我们需要以下信息：

• limit- 与之前相同，我们希望在一页上显示的行数
• cursor- 列表中引用元素的 ID。如果查询上一页，则此 ID 为第一项；如果查询下一页，则此 ID 为最后一项。
• cursorDirection- 如果用户点击Next或Previous（after或before）

请求第一页时，我们不需要提供任何参数，只需要提供 limit 10，说明我们想要获取多少行。结果，我们得到了 10 行。

为了获取下一页，我们使用最后一行的 ID 作为cursor并设置cursorDirection为after。

类似地，如果我们想转到上一页，我们使用第一行的 ID 作为cursor，并将方向设置为before。

为了进行比较，在 SQL 世界中，我们可以将查询写为SELECT * FROM posts WHERE id > 10 LIMIT 10 ORDER BY id DESC。

使用游标而非偏移的查询性能更佳，因为游标WHERE查询有助于跳过不需要的行，而偏移查询则OFFSET需要迭代这些行，从而导致全表扫描。如果您在 ID 上设置了适当的索引，使用游标跳过行的WHERE速度会更快。如果使用主键，则默认会创建索引。

不仅如此，您无需再担心行被插入或删除。如果您使用的偏移量为 10，则您预期当前页面之前会有 10 行数据。如果不满足此条件，您的查询将返回不一致的结果，从而导致数据重复甚至丢失行。如果当前页面之前的任何行被删除或添加了新行，就可能发生这种情况。游标分页通过使用您获取的最后一行的索引来解决这个问题，并且当您请求更多数据时，它知道从哪里开始查找。

事情并非总是那么美好。如果您需要自行在后端实现游标分页，那么它是一个非常复杂的问题。要实现游标分页，您需要在查询中使用WHEREandORDER BY子句。此外，您还需要使用WHERE子句来根据所需条件进行筛选。这很快就会变得非常复杂，最终可能会产生一个巨大的嵌套查询。除此之外，您还需要为所有需要查询的列创建索引。

太棒了！通过切换到游标分页，我们摆脱了重复和缺失的数据！但还有一个问题。由于出于安全考虑，您不应该向用户公开增量数字 ID，因此现在必须维护每个 ID 的哈希版本。每当您需要查询数据库时，您都会通过查看包含这些 ID 对的表，将此字符串 ID 转换为其数字 ID。如果这一行缺失了怎么办？如果您点击按钮Next，获取最后一行的 ID，并请求下一页，但数据库找不到该 ID，该怎么办？

这种情况非常罕见，仅当您要用作游标的行 ID 刚刚被删除时才会发生。我们可以通过尝试之前的行或重新获取先前请求的数据来解决这个问题，以便用新的 ID 更新最后一行，但这会带来全新的复杂性，开发人员需要理解一系列新概念，例如递归和适当的状态管理。值得庆幸的是，Appwrite等服务可以解决这个问题，因此您可以直接使用游标分页功能。

🚀 Appwrite 中的分页

Appwrite是一款开源的后端即服务 (Backend-as-a-service)，它通过提供一组 REST API 来满足您的核心后端需求，从而抽象化了构建现代应用程序所涉及的所有复杂性。Appwrite 可以处理用户身份验证和授权、数据库、文件存储、云函数、Webhook 等等！如果您有任何不足，可以使用您喜欢的后端语言来扩展 Appwrite。

Appwrite 数据库允许您存储任何需要与用户共享的文本数据。Appwrite 的数据库允许您创建多个集合（表）并在其中存储多个文档（行）。每个集合都配置了属性（列），以便为数据集提供合适的模式。您还可以配置索引，以提高搜索查询的性能。读取数据时，您可以使用一系列强大的查询，包括过滤、排序、限制结果数量以及分页。所有这些都是开箱即用的！

Appwrite 数据库更胜一筹之处在于它的分页支持，我们同时支持偏移分页和游标分页！假设我们有一个 ID 为 的集合articles，我们可以使用偏移分页或游标分页从该集合中获取文档：

// Setup
import { Appwrite, Query } from "appwrite";
const sdk = new Appwrite();

sdk
    .setEndpoint('https://demo.appwrite.io/v1') // Your API Endpoint
    .setProject('articles-demo') // Your project ID
;

// Offset pagination
sdk.database.listDocuments(
    'articles', // Collection ID
    [ Query.equal('status', 'published') ], // Filters
    10, // Limit
    500, // Offset, amount of documents to skip
).then((response) => {
    console.log(response);
});

// Cursor pagination
sdk.database.listDocuments(
    'articles', // Collection ID
    [ Query.equal('status', 'published') ], // Filters
    10, // Limit
    undefined, // Not using offset
    '61d6eb2281fce3650c2c' // ID of document I want to paginate after
).then((response) => {
    console.log(response);
});

首先，我们导入 Appwrite SDK 库，并设置一个连接到特定 Appwrite 实例和特定项目的实例。然后，我们使用偏移分页列出 10 个文档，并使用过滤器仅显示已发布的文档。紧接着，我们编写完全相同的列出文档查询，但这次使用游标分页而不是偏移分页。

📊 基准

在本文中，我们多次使用“性能”一词，但并未提供任何实际数据，所以让我们一起创建一个基准测试吧！我们将使用 Appwrite 作为后端服务器，因为它支持偏移分页和游标分页，并使用 Node.JS 编写基准测试脚本。毕竟，JavaScript 很容易上手。

您可以在GitHub 存储库中找到完整的源代码。

首先，我们设置 Appwrite，注册一个用户，创建一个项目，并创建一个名为 的集合，该集合posts具有集合级权限，并将读取权限设置为role:all。要了解有关此过程的更多信息，请访问Appwrite 文档。现在，我们应该可以使用 Appwrite 了。

我们暂时无法进行基准测试，因为我们的数据库是空的！让我们用一些数据填充表格。我们使用以下脚本将数据加载到 MariadDB 数据库中，并为基准测试做好准备。

const config = {};
// Don't forget to fill config variable with secret information

console.log("🤖 Connecting to database ...");

const connection = await mysql.createConnection({
    host: config.mariadbHost,
    port: config.mariadbPost,
    user: config.mariadbUser,
    password: config.mariadbPassword,
    database: `appwrite`,
});

const promises = [];

console.log("🤖 Database connection established");
console.log("🤖 Preparing database queries ...");

let index = 1;
for(let i = 0; i < 100; i++) {
    const queryValues = [];

    for(let l = 0; l < 10000; l++) {
        queryValues.push(`('id${index}', '[]', '[]')`);
        index++;
    }

    const query = `INSERT INTO _project_${config.projectId}_collection_posts (_uid, _read, _write) VALUES ${queryValues.join(", ")}`;
    promises.push(connection.execute(query));
}

console.log("🤖 Pushing data. Get ready, this will take quite some time ...");

await Promise.all(promises);

console.error(`🌟 Successfully finished`);

我们使用了两层 for 循环来提升脚本速度。第一层 for 循环创建需要等待的查询执行，第二层循环创建一个包含多个插入请求的长查询。理想情况下，我们希望在一个请求中完成所有操作，但由于 MySQL 的配置，这无法实现，因此我们将其拆分成 100 个请求。

我们在不到一分钟的时间内插入了 100 万个文档，现在我们准备开始基准测试了。本次演示中我们将使用k6负载测试库。

首先，让我们对著名且广泛使用的偏移分页进行基准测试。在每个测试场景中，我们尝试从数据集的不同部分获取包含 10 个文档的页面。我们将从偏移量 0 开始，以 100k 为增量，一直到偏移量 900k。基准测试的编写方式是每次只发出一个请求，以尽可能保证准确性。我们还将运行相同的基准测试十次，并测量平均响应时间以确保统计显著性。我们将使用 k6 的 HTTP 客户端向 Appwrite 的 REST API 发出请求。

// script_offset.sh

import http from 'k6/http';

// Before running, make sure to run setup.js
export const options = {
    iterations: 10,
    summaryTimeUnit: "ms",
    summaryTrendStats: ["avg"]
};

const config = JSON.parse(open("config.json"));

export default function () {
    http.get(`${config.endpoint}/database/collections/posts/documents?offset=${__ENV.OFFSET}&limit=10`, {
        headers: {
            'content-type': 'application/json',
            'X-Appwrite-Project': config.projectId
        }
    });
}

为了使用不同的偏移配置运行基准测试并将输出存储在 CSV 文件中，我创建了一个简单的 Bash 脚本。该脚本执行 k6 十次，每次使用不同的偏移配置。输出将以控制台输出的形式提供。

#!/bin/bash
# benchmark_offset.sh

k6 -e OFFSET=0 run script.js
k6 -e OFFSET=100000 run script.js
k6 -e OFFSET=200000 run script.js
k6 -e OFFSET=300000 run script.js
k6 -e OFFSET=400000 run script.js
k6 -e OFFSET=500000 run script.js
k6 -e OFFSET=600000 run script.js
k6 -e OFFSET=700000 run script.js
k6 -e OFFSET=800000 run script.js
k6 -e OFFSET=900000 run script.js

一分钟之内，所有基准测试都完成了，并提供了每种偏移配置的平均响应时间。结果符合预期，但并不令人满意。

我们可以看到，偏移量 0 非常快，响应时间不到 4 毫秒。我们第一次跳转到偏移量 10 万，变化非常剧烈，响应时间增加到 52 毫秒。随着偏移量的每次增加，响应时间也随之增加，导致偏移量达到 90 万个文档后，获取 10 个文档的时间几乎只有 500 毫秒。这太疯狂了！

现在让我们更新脚本以使用游标分页。我们将更新脚本以使用游标而不是偏移量，并更新 bash 脚本以提供游标（文档 ID）而不是偏移量数字。

// script_cursor.js
import http from 'k6/http';

// Before running, make sure to run setup.js
export const options = {
    iterations: 10,
    summaryTimeUnit: "ms",
    summaryTrendStats: ["avg"]
};

const config = JSON.parse(open("config.json"));

export default function () {
    http.get(`${config.endpoint}/database/collections/posts/documents?cursor=${__ENV.CURSOR}&cursorDirection=after&limit=10`, {
        headers: {
            'content-type': 'application/json',
            'X-Appwrite-Project': config.projectId
        }
    });
}

#!/bin/bash
# benchmark_cursor.sh

k6 -e CURSOR=id1 run script_cursor.js
k6 -e CURSOR=id100000 run script_cursor.js
k6 -e CURSOR=id200000 run script_cursor.js
k6 -e CURSOR=id300000 run script_cursor.js
k6 -e CURSOR=id400000 run script_cursor.js
k6 -e CURSOR=id500000 run script_cursor.js
k6 -e CURSOR=id600000 run script_cursor.js
k6 -e CURSOR=id700000 run script_cursor.js
k6 -e CURSOR=id800000 run script_cursor.js
k6 -e CURSOR=id900000 run script_cursor.js

运行脚本后，我们已经能够看出性能有所提升，因为响应时间有明显的差异。我们将结果放在一个表格中，以便并排比较这两种分页方法。

哇！游标分页太棒了！图表显示，游标分页不关心偏移量大小，每个查询的性能都与第一个或最后一个查询一样好。你能想象反复加载一个巨大列表的最后一页会造成多大的损失吗？😬

如果您有兴趣在自己的机器上运行测试，您可以在GitHub 仓库中找到完整的源代码。该仓库包含README.md安装和运行脚本的整个过程的解释。

👨‍🎓 摘要

偏移分页提供了一种众所周知的分页方法，您可以看到页码并点击浏览。这种直观的方法有很多缺点，例如偏移量较大时性能不佳，并且容易出现数据重复和丢失。

游标分页解决了所有这些问题，并带来了一个可靠的分页系统，该系统速度快，能够处理实时（经常变化）的数据。游标分页的缺点在于不显示页码、实现复杂，以及一系列需要克服的新挑战，例如缺少游标 ID。

现在让我们回到最初的问题，为什么 GitHub 使用游标分页，而 Amazon 却决定使用偏移分页？性能并不总是关键……用户体验比你的企业需要支付多少台服务器更有价值。

我认为亚马逊之所以选择偏移量，是因为它能提升用户体验，但这是另一个研究课题。我们已经注意到，如果我们访问amazon.com并搜索某个网站，它会显示“恰好pen有结果”，但你只能访问前七页（350 个结果）。 10 000

首先，结果远不止 1 万条，但亚马逊限制了结果数量。其次，你仍然可以访问前七页。如果你尝试访问第 8 页，则会显示 404 错误。正如我们所见，亚马逊知道偏移分页的性能问题，但仍然决定保留它，因为他们的用户更喜欢查看页码。他们不得不设置一些限制，但谁会去搜索结果的第 100 页呢？🤷

你知道还有什么比阅读分页教程更好吗？那就试试吧！我鼓励你两种方法都试试，因为亲身体验效果最佳。Appwrite 的设置只需几分钟，你就可以开始使用这两种分页方法。如有任何疑问，也可以通过我们的 Discord 服务器联系我们。

🔗 资源

• 偏移分页已死？为何游标分页正在取而代之
• 偏移分页 vs 游标分页
• 如何在 API 中实现分页游标
• 如何像专业人士一样实现光标分页