别再用 offset 和 limit 分页了,性能太差!
发布网友
发布时间：2024-10-04 09:12
共1个回答
热心网友
时间：2024-10-05 16:38
传统的分页方法，如使用 OFFSET 和 LIMIT，虽然对于小型项目和数据量不大的场景尚可使用，但在处理大规模数据时，这种方式变得低效且不可靠。当数据库中的数据量超过服务器内存的存储能力，对所有数据进行分页时，问题尤为凸显。
每次收到分页请求时，数据库需要进行全表扫描，这是一种已知最慢的扫描方式，涉及大量磁盘 I/O 和内存传输开销。例如，对于拥有1亿个用户的数据库，OFFSET 为5千万时，数据库需要获取所有这些记录（包括大量不必要的数据），将其加载到内存中，然后再获取指定的20条结果。这使得获取一页数据的成本变得非常高昂。
考虑一个简单的例子：db-fiddle.com/f/3JSpBxV...
在该例子中，左边的 Schema SQL 插入了10万行数据，而右边的查询采用了两种不同的方式。仅需点击顶部的 Run 即可比较它们的执行时间。第一个查询的运行时间至少是第二个查询的30倍。数据量越大，这种差异就越明显。
为了解决这个问题，可以采用基于指针的分页方法。这种方法使用本地保存的上一次接收到的主键（通常是 ID），而不是 OFFSET 和 LIMIT。通过明确告知数据库最新行的位置，数据库可以准确地从哪里开始搜索，无需考虑目标范围之外的记录。这极大地提高了查询效率。
基于游标的分页在某些情况下可能无法满足特定条件，如没有唯一序列字段的情况。此时，应权衡每种解决方案的利弊，并考虑执行哪种查询。对于处理大量数据的查询操作，Rick James 的文章提供了更深入的指导。
当数据库表不包含主键，例如在多对多关系的场景中，可以考虑使用传统的 OFFSET/LIMIT 方式，尽管这可能引入潜在的慢查询问题。建议在需要分页的表中使用自动递增的主键，即使只是为了分页操作。通过这种方式，可以确保在处理大规模数据时，数据库性能得到优化，为高效的数据检索提供坚实的基础。
