sql分页查询的3种实现方式为：1.limit offset（适用于mysql等），通过指定limit返回记录数量和offset起始行数实现，如select from products limit 10 offset 20可获取第21-30条记录；2.row_number() over（适用于sql server等），使用窗口函数为每行分配唯一序号并基于该序号过滤，如通过rownum在21到30之间筛选；3.游标（不推荐），虽可用于分页但效率低，不适合高并发或大数据场景。优化策略包括索引优化、避免select 、使用覆盖索引、缓存、预编译sql语句以及避免在where中使用函数。limit offset的性能瓶颈在于大offset值会导致数据库扫描大量行后丢弃，解决方法有书签法（利用上一页最后记录的排序值）、覆盖索引和避免深度分页。不同数据库分页语法存在差异：mysql/mariadb/postgresql使用limit offset，postgresql还支持fetch first n rows only；sql server支持row_number()或offset fetch（2012及以上版本）；oracle旧版用rownum，新版支持offset fetch；sqlite使用limit offset，实际应用时需考虑各数据库的索引与查询优化器特性以提升性能。
SQL分页查询，简单来说，就是从数据库中提取特定范围内的数据记录，而不是一次性加载整个数据集。这对于处理大型数据集至关重要，可以显著提高查询效率和用户体验。
分页查询的3种实现方式
LIMIT OFFSET (适用于MySQL等)
这是最常见且易于理解的分页方法。LIMIT 指定要返回的记录数量，OFFSET 指定从哪一行开始返回。
SELECT * FROM products LIMIT 10 OFFSET 20;
这条SQL语句会返回 products 表中从第21条记录开始的10条记录（即第21-30条记录）。OFFSET 从0开始计数。
使用ROW_NUMBER() OVER() (适用于SQL Server等)
这种方法利用窗口函数 ROW_NUMBER() 为结果集中的每一行分配一个唯一的序号。然后，你可以基于这个序号进行过滤。
WITH RankedProducts AS (
SELECT
*,
ROW_NUMBER() OVER (ORDER BY product_id) AS RowNum
FROM
products
)
SELECT *
FROM RankedProducts
WHERE RowNum BETWEEN 21 AND 30;这个查询首先使用 ROW_NUMBER() 根据 product_id 对 products 表进行排序，并为每一行分配一个 RowNum。然后，它选择 RowNum 在 21 到 30 之间的记录。注意，ORDER BY 子句至关重要，因为它决定了行的排序方式，进而影响 ROW_NUMBER() 的结果。没有 ORDER BY，结果将是不可预测的。
使用游标（不推荐，效率较低）
游标允许你逐行处理结果集。虽然可以用于分页，但通常效率较低，不建议在高并发或大数据量的场景中使用。这里不提供具体代码示例，因为更推荐使用前两种方式。
SQL分页查询优化有哪些策略？
-
索引优化： 确保用于排序和过滤的列（例如
product_id、RowNum）上建有索引。这可以显著加快查询速度。对于LIMIT OFFSET方法，如果OFFSET值很大，数据库可能需要扫描大量的行才能找到起始位置。在这种情况下，可以考虑使用 "书签" 或 "延迟连接" 技术。 - *避免SELECT ：** 只选择需要的列，减少数据传输量。
- 使用覆盖索引： 如果查询只需要索引中的列，数据库可以直接从索引中获取数据，而无需访问表本身。
- 缓存： 对于频繁访问的分页数据，可以考虑使用缓存来减少数据库负载。
- 预编译SQL语句： 如果分页查询的结构不变，只是参数不同，可以使用预编译的SQL语句来提高效率。
-
避免在WHERE子句中使用函数： 在
WHERE子句中使用函数会导致索引失效。
LIMIT OFFSET分页的性能瓶颈是什么？
LIMIT OFFSET 的主要性能瓶颈在于 OFFSET。当 OFFSET 值很大时，数据库需要扫描大量的行才能找到起始位置。例如，LIMIT 10 OFFSET 1000000 意味着数据库需要扫描 1000010 行，然后丢弃前 1000000 行，只返回最后的 10 行。这显然是非常低效的。
解决 OFFSET 性能瓶颈的一些方法：
-
使用书签（也称为 "延迟连接" 或 "Seek Method"）： 记录上一页的最后一条记录的排序字段值，然后在下一页查询中使用
WHERE子句来过滤掉之前的记录。例如：
-- 假设上一页的最后一条记录的 product_id 是 100 SELECT * FROM products WHERE product_id > 100 ORDER BY product_id LIMIT 10;
这种方法避免了扫描大量的行，而是直接从指定的位置开始读取。
- 使用覆盖索引： 如果查询只需要索引中的列，数据库可以直接从索引中获取数据，而无需访问表本身。这可以显著提高查询速度。
- 避免深度分页： 尽量避免用户浏览到非常靠后的页码。可以考虑使用 "加载更多" 或 "无限滚动" 等技术来替代传统的分页。
不同数据库系统在分页查询上的差异有哪些？
不同的数据库系统在分页查询的语法和性能上存在一些差异。
-
MySQL/MariaDB/PostgreSQL： 使用
LIMIT OFFSET语法。PostgreSQL 还支持FETCH FIRST n ROWS ONLY语法，效果与LIMIT n相同。 -
SQL Server： 使用
ROW_NUMBER() OVER()窗口函数或者OFFSET FETCH语法（SQL Server 2012 及更高版本）。OFFSET FETCH语法类似于LIMIT OFFSET，但更加标准化。
SELECT * FROM products ORDER BY product_id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
-
Oracle： 在旧版本中使用
ROWNUM伪列，新版本（12c 及更高版本）也支持OFFSET FETCH语法。 -
SQLite： 使用
LIMIT OFFSET语法。
需要注意的是，不同数据库系统在索引优化、查询优化器等方面也存在差异，因此在实际应用中需要根据具体情况进行调整。例如，MySQL 的 LIMIT OFFSET 在大 OFFSET 值时性能较差，而 SQL Server 的 OFFSET FETCH 语法在某些情况下可能比 ROW_NUMBER() OVER() 更高效。
