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 RankedProductsWHERE RowNum BETWEEN 21 AND 30;
这个查询首先使用 ROW_NUMBER() 根据 product_id 对 products 表进行排序，并为每一行分配一个 RowNum。然后，它选择 RowNum 在 21 到 30 之间的记录。注意，ORDER BY 子句至关重要，因为它决定了行的排序方式，进而影响 ROW_NUMBER() 的结果。没有 ORDER BY，结果将是不可预测的。
使用游标（不推荐，效率较低）
稿定AI文案
小红书笔记、公众号、周报总结、视频脚本等智能文案生成平台
169 查看详情
游标允许你逐行处理结果集。虽然可以用于分页，但通常效率较低，不建议在高并发或大数据量的场景中使用。这里不提供具体代码示例，因为更推荐使用前两种方式。
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 是 100SELECT * 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() 更高效。
以上就是SQL分页查询怎么写 分页查询的3种实现方式的详细内容，更多请关注创想鸟其它相关文章！
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容， 请发送邮件至 chuangxiangniao@163.com 举报，一经查实，本站将立刻删除。
发布者：程序猿，转转请注明出处：https://www.chuangxiangniao.com/p/1091747.html
微信扫一扫 
支付宝扫一扫 
