SQL分页查询怎么写 分页查询的3种实现方式

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分页查询优化有哪些策略？

1. 索引优化： 确保用于排序和过滤的列（例如 product_id、RowNum）上建有索引。这可以显著加快查询速度。对于 LIMIT OFFSET 方法，如果 OFFSET 值很大，数据库可能需要扫描大量的行才能找到起始位置。在这种情况下，可以考虑使用 “书签” 或 “延迟连接” 技术。
2. *避免SELECT ：** 只选择需要的列，减少数据传输量。
3. 使用覆盖索引： 如果查询只需要索引中的列，数据库可以直接从索引中获取数据，而无需访问表本身。
4. 缓存： 对于频繁访问的分页数据，可以考虑使用缓存来减少数据库负载。
5. 预编译SQL语句： 如果分页查询的结构不变，只是参数不同，可以使用预编译的SQL语句来提高效率。
6. 避免在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() 更高效。