为什么OFFSET和LIMIT在大数据量下会变慢？
为什么OFFSET和LIMIT在大数据量下会变慢？
在大数据时代，传统的OFFSET和LIMIT分页方式可能会带来严重的性能问题。本文将深入探讨这个问题，并提出一种更高效的分页解决方案。
不需要担心数据库性能优化问题的日子已经一去不复返了。随着企业对数据规模的要求越来越高，作为开发人员，我们需要不断优化API，提供可靠且高效的端点，以便轻松处理海量数据。
如果你曾经使用过OFFSET和LIMIT进行数据库分页，那么你可能需要重新思考这个问题。虽然这种方式在数据量较小的情况下还能"应付"，但当数据量达到一定规模时，它可能会成为系统性能的瓶颈。
1.为什么OFFSET和LIMIT会带来性能问题？
当数据库中的数据量超过服务器内存的存储能力时，使用OFFSET和LIMIT进行分页就会出现问题。每次收到分页请求时，数据库都需要进行低效的全表扫描。
什么是全表扫描？全表扫描（又称顺序扫描）就是在数据库中逐行扫描，顺序读取表中的每一行记录，然后检查各个列是否符合查询条件。这种扫描方式非常低效，因为它需要进行大量的磁盘I/O操作，而且从磁盘到内存的传输开销也很大。
假设你有1亿个用户，OFFSET是5千万，那么它需要获取所有这些记录（包括那么多根本不需要的数据），将它们放入内存，然后获取LIMIT指定的20条结果。也就是说，为了获取一页的数据：
10万行中的第5万行到第5万零20行
需要先获取5万行。这种做法是多么低效？你可以通过以下链接查看一个具体的性能对比示例：
左边的Schema SQL将插入10万行数据，右边有一个性能很差的查询和一个较好的解决方案。只需单击顶部的Run，就可以比较它们的执行时间。第一个查询的运行时间至少是第二个查询的30倍。
数据越多，情况就越糟。看看我对10万行数据进行的PoC：
现在你应该知道这背后都发生了什么：OFFSET越高，查询时间就越长。
2.基于游标的分页解决方案
更好的做法是使用基于游标的分页方式。你需要在本地保存上一次接收到的主键（通常是一个ID）和LIMIT，而不是OFFSET和LIMIT。每一次的查询可能都与此类似：
为什么？因为通过显式告知数据库最新行，数据库就确切地知道从哪里开始搜索（基于有效的索引），而不需要考虑目标范围之外的记录。
比较这个查询：
和优化的版本：
返回同样的结果，第一个查询使用了12.80秒，而第二个仅用了0.01秒。
要使用这种基于游标的分页，需要有一个唯一的序列字段（或多个），比如唯一的整数ID或时间戳，但在某些特定情况下可能无法满足这个条件。
我的建议是，不管怎样都要考虑每种解决方案的优缺点，以及需要执行哪种查询。如果需要基于大量数据做查询操作，可以参考Rick James的文章：
如果我们的表没有主键，比如是具有多对多关系的表，那么就使用传统的OFFSET/LIMIT方式，只是这样做存在潜在的慢查询问题。我建议在需要分页的表中使用自动递增的主键，即使只是为了分页。
