分页SQL

博客涉及SQL分页相关内容,SQL是后端开发中常用的数据库查询语言,分页功能在数据展示等场景中十分重要,能有效处理大量数据的分段显示。

Mysql数据库 : 

--表名:可以是单表的查询语句,也可以是多表的联合查询语句; firstIndex:其实的索引;pageSize:每页显示的记录数。

select o.* from (表名) o limit firstIndex,pageSize;

例子:

select l.* from lduser l limit 0,20;  ---- 查询lduser表,从第1条开始,查询20条

 

Oracle数据库:

   1.  rownum 查询通式
   --  表 : 可以为单表,也可以是查询SQL;firstIndex :起始索引;pageSize:每页显示的数量

select * from(select l.*,rownum rn from lduser l where ROWNUM<=(firstIndex+pageSize)) where rn>firstIndex
(ROWNUM<=(firstIndex+pageSize),rn>firstIndex;作为参数传入)
或是
select l.* from (select rownum rn,l.* from lduser l ) where l.rn > 10 and l.rn <= 20 order by l.rn;
(l.rn > 10 and l.rn <= 20,作为参数传入)

2. row_number()解析函数分页查询通式:
-- firstIndex:起始索引;pageSize:每页显示的数量;orderColumn:排序的字段名
-- sql:可以是简单的单表查询语句,也可以是复杂的多表联合查询语句
select * from (select * from (select l.*, row_number() over(order by userid) as rownums from lduser l) p 
where p.rownums > firstIndex) where rownum <= pageSize

Sqlserver数据库:

--  查询lduser表,从第11条数据开始,查询10条数据

select top 10 l.* from (select row_number() over(order by userid) as rownums, * from lduser ) l where rownums >10
rownums >10作为参数传入,进行分页

 

### 优化分页 SQL 查询性能的方法 #### 1. **通过索引优化分页查询** 深度分页时,传统的 `LIMIT offset, size` 方式会导致数据库扫描大量不需要的记录,从而影响性能。优化方法是通过主键或索引字段进行分页查询,减少扫描的数据量。例如,将 `SELECT * FROM products LIMIT 199980, 20` 优化为 `SELECT * FROM products WHERE id > 199980`。这种方式通过索引直接定位到需要查询的记录范围,从而提升查询效率[^1]。 #### 2. **细化查询条件,减少数据量** 在分页查询中,可以通过添加更多的筛选条件来缩小查询范围,从而减少数据库需要扫描的数据量。例如,在查询中加入时间范围、状态等条件,确保查询的数据与业务相关性更高,减少不必要的数据处理[^1]。 #### 3. **避免深度分页,限制分页深度** 深度分页的查询性能通常较差,因为随着分页的深入,数据库需要扫描的记录数会增加。实际业务场景中,用户通常不会翻到太深的页面,因此可以限制分页的最大页数,如最多分100页[^1]。 #### 4. **使用延迟关联(Deferred Join)优化分页** 延迟关联是一种优化策略,先通过子查询获取主键,然后再进行回表查询。例如: ```sql SELECT * FROM table WHERE id IN ( SELECT id FROM table WHERE date BETWEEN '2022-05-01' AND '2022-05-31' ORDER BY date LIMIT 10000, 20 ); ``` 这种方法可以减少不必要的回表操作,从而提升查询性能[^3]。 #### 5. **使用游标分页(Cursor-based Pagination)** 游标分页是一种高效的分页方式,通过记录上一页的最后一个游标(如主键或排序字段)来获取下一页的数据。例如: ```sql SELECT * FROM products WHERE created_at > '2022-05-01' ORDER BY created_at LIMIT 20; ``` 这种方式避免了传统分页中 `OFFSET` 带来的性能问题,尤其适合大数据量场景。 #### 6. **使用 Elasticsearch 等搜索引擎优化分页** 对于需要复杂查询和深度分页的场景,可以将数据同步到 Elasticsearch 等搜索引擎中。Elasticsearch 在处理大数据量的分页查询时性能更优,同时支持更复杂的查询条件和排序。 #### 7. **MyBatis 分页插件优化** MyBatis 提供了分页插件,通过拦截 SQL 并重写实现分页功能。虽然分页插件在生产环境中可能存在性能问题,但在合适的场景下,如数据量较小或性能测试通过的情况下,仍然可以使用。如果对性能有较高要求,建议自定义分页逻辑,避免使用插件带来的额外开销[^4]。 #### 8. **减少不必要的字段查询** 在分页查询中,尽量避免使用 `SELECT *`,而是只查询需要的字段。这样可以减少数据库的 I/O 操作和网络传输开销,提高查询效率。 #### 9. **使用缓存优化高频分页查询** 对于频繁访问的分页数据,可以使用缓存(如 Redis)来存储查询结果,减少数据库的查询压力。特别是对于静态或变化较少的数据,缓存可以显著提升查询性能。 #### 10. **定期优化表和索引** 定期对数据库表进行优化(如 `OPTIMIZE TABLE`)和索引重建,确保索引的有效性。同时,避免过多的索引,因为索引会增加写入操作的开销,影响性能。 ### 示例代码 #### 使用游标分页SQL 示例: ```sql -- 假设上一页的最后一条记录的 created_at 是 '2022-05-01 10:00:00' SELECT * FROM products WHERE created_at > '2022-05-01 10:00:00' ORDER BY created_at LIMIT 20; ``` #### 使用延迟关联的 SQL 示例: ```sql -- 先获取主键,再进行回表查询 SELECT * FROM orders WHERE order_id IN ( SELECT order_id FROM orders WHERE order_date BETWEEN '2022-05-01' AND '2022-05-31' ORDER BY order_date LIMIT 10000, 20 ); ``` ###
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

QY别说话

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值