优化sql分页查询性能的核心在于减少offset的使用，避免全表扫描。1.使用limit和offset适用于小数据量，但offset过大时会导致性能急剧下降；2.通过子查询或join先找到起始id再进行范围查询，在一定程度上提升性能；3.书签式分页基于唯一递增字段记录上一页最后一条数据id，直接从该位置开始查询，适合大数据量场景，但不支持跳页；4.选择方案需综合考虑数据量、是否需要跳页、是否存在唯一递增字段及数据库类型；5.为避免高并发下的数据重复或遗漏，可使用事务、快照隔离、锁定表或乐观锁等机制；6.orm框架如spring data jpa可通过pageable实现分页，但仍需关注底层sql性能。合理选择分页策略是高效获取数据的关键。
SQL分页查询，说白了，就是如何在数据库中高效地获取指定范围的数据。核心在于优化查询语句，避免全表扫描，提升查询效率。
分页查询是日常开发中高频使用的功能，尤其是在数据量巨大的情况下，用户不可能一次性浏览所有数据，所以分页显示是必须的。下面我们来聊聊SQL实现分页查询的几种常见方案。
如何优化SQL分页查询的性能？
分页查询的性能瓶颈通常在于offset的计算。比如，查询第100页，每页10条数据，数据库需要跳过前990条数据才能找到第100页的数据。当offset非常大时，这个跳过的过程会消耗大量资源。
方案一：使用LIMIT和OFFSET
这是最基础的分页方式，几乎所有数据库都支持。
SELECT * FROM table_name ORDER BY id LIMIT 10 OFFSET 990;
虽然简单，但正如前面所说，当OFFSET很大时，性能会急剧下降。 数据库需要先找到前990条数据，然后再丢弃，只返回后面的10条。
方案二：使用子查询或连接
这种方式避免了OFFSET的大量跳过操作。 核心思想是先找到起始位置的ID，然后基于这个ID进行范围查询。
SELECT * FROM table_name WHERE id > (SELECT id FROM table_name ORDER BY id LIMIT 990, 1) ORDER BY id LIMIT 10;
或者使用JOIN：
SELECT t1.* FROM table_name t1 JOIN (SELECT id FROM table_name ORDER BY id LIMIT 990, 1) t2 ON t1.id > t2.id ORDER BY t1.id LIMIT 10;
这种方法在很多情况下比LIMIT和OFFSET更快，因为它只需要找到起始ID，然后直接从该ID开始查询。
方案三：书签式分页（也称为游标分页）
书签式分页不使用OFFSET，而是记录上一页的最后一条数据的ID（或者其他唯一标识），然后在查询下一页时，直接从该ID之后开始查询。
假设上一页最后一条数据的ID是1000，查询下一页的SQL如下：
SELECT * FROM table_name WHERE id > 1000 ORDER BY id LIMIT 10;
这种方式避免了OFFSET的计算，性能非常高，尤其是在数据量非常大的情况下。 但是，书签式分页要求数据必须有一个唯一且递增的字段，比如ID。同时，它也不支持跳页查询，只能一页一页地往下翻。
性能对比
简单来说，LIMIT和OFFSET在数据量小的情况下够用，但数据量一大，性能就急剧下降。子查询或JOIN的方式在一定程度上可以缓解这个问题，但仍然存在一定的性能瓶颈。书签式分页在数据量非常大的情况下性能最好，但限制也比较多。
如何选择合适的分页方案？
选择合适的分页方案需要考虑以下几个因素：
- 数据量： 数据量越大，越需要选择性能更高的方案，比如书签式分页。
- 是否需要跳页查询： 如果需要跳页查询，书签式分页就无法满足需求，只能选择LIMIT和OFFSET或者子查询/JOIN。
- 是否有唯一且递增的字段： 书签式分页要求数据必须有一个唯一且递增的字段。
- 数据库类型： 不同的数据库对分页语法的支持可能略有不同。
分页查询时如何避免数据重复或遗漏？
这是一个很常见的问题，尤其是在高并发场景下。 比如，在两次分页查询之间，有新的数据插入或者删除，就可能导致数据重复或遗漏。
解决方案：
- 使用事务： 将分页查询放在一个事务中，确保在查询过程中数据不会被修改。 但这种方式可能会影响并发性能。
- 使用快照隔离： 某些数据库支持快照隔离，可以在查询开始时创建一个数据快照，然后基于该快照进行查询，避免数据被修改。
- 锁定表： 在查询期间锁定表，阻止其他事务修改数据。 这种方式对并发性能影响最大，应该尽量避免。
- 使用乐观锁或悲观锁： 在数据表中增加一个版本号字段，每次修改数据时都更新版本号。 在查询时，可以根据版本号来判断数据是否被修改。
选择哪种方案取决于具体的业务场景和性能要求。 一般来说，使用快照隔离或者乐观锁是比较好的选择，既能保证数据的一致性，又能兼顾并发性能。
如何在ORM框架中使用分页查询？
现在很多项目都使用ORM框架，比如mybatis、hibernate、Spring Data JPA等。 这些框架都提供了分页查询的支持。
以Spring Data JPA为例：
Pageable pageable = PageRequest.of(pageNumber, pageSize, Sort.by("id").ascending()); Page<Entity> page = repository.findAll(pageable);
Pageable对象包含了分页信息，比如页码、每页大小、排序方式等。 repository.findAll(pageable)方法会自动生成分页查询的sql语句。
ORM框架通常会对分页查询进行一定的优化，比如使用预编译SQL、缓存等。 但最终的性能仍然取决于底层的SQL语句。 因此，在使用ORM框架进行分页查询时，仍然需要关注SQL语句的性能。
总而言之，SQL分页查询是一个看似简单，实则需要深入理解的知识点。选择合适的分页方案，优化SQL语句，才能在海量数据中高效地获取所需信息。
