分页操作中使用LIMIT和OFFSET后出现慢查询的原因分析

事情经过

最近在做批量数据处理的相关业务，在和下游对接时，发现拉取他们的业务数据刚开始很快，后面会越来越慢，40万数据一个小时都拉不完。经过排查后，发现对方用了很坑的分页查询方式 —— LIMIT OFFSET，导致数据量大的时候查询效率爆低。

简单分享下，为什么使用了 LIMIT OFFSET会导致查询效率变低 ——?

主要原因

1. 数据库扫描：当 OFFSET 值很大时，数据库引擎需要扫描和跳过 OFFSET 行之前的数据，这会导致数据库执行大量的读取和跳过操作，增加了查询的开销。

2. 数据库排序：通常情况下，使用 LIMIT 和 OFFSET 进行分页需要对查询结果进行排序。当 OFFSET 值很大时，需要对大量的数据进行排序操作，这也会增加查询的时间和资源消耗。

3. 内存使用：OFFSET 值较大时，数据库可能需要将大量的数据加载到内存中进行排序和跳过操作，这会增加内存的使用量，对数据库性能产生影响。

解决方案

为了解决这个问题，可以考虑使用其他分页技术，例如基于游标（Cursor-based）的分页。游标分页不依赖于 OFFSET 值，而是使用基于某个唯一标识符（例如主键）的游标来获取下一页的数据。这种方式可以避免数据库扫描和排序的开销，提高查询性能。下游最终也是采取了使用主键id进行了分页。

另外，对于大型数据集的分页查询，还可以考虑使用索引优化、缓存技术、分库分表等方法来提高查询性能。

总结一下，使用 LIMIT 和 OFFSET 进行分页时，当 OFFSET 值很大时可能会导致查询变慢，因为需要扫描和跳过大量的数据。为了提高性能，可以考虑使用游标分页或其他优化技术来替代使用 OFFSET 进行分页。