springboot 查询count分页 效率问题

单表500w数据，列表查询 count /跨越式翻页 Limt offset 都会引起 慢查询的问题。

问题1 count 耗时

[2023-11-08 11:21:30] [statement-0] [耗时: 2657ms] 执行SQL:
select count(*) from biz_notice n1_0 where n1_0.title like '通知公告%' escape '!' and n1_0.parent_id is null
[2023-11-08 11:21:30] [statement-0] [耗时: 1ms] 执行SQL:
select n1_0.id,n1_0.create_by,n1_0.create_time,n1_0.json_str,n1_0.notice_time,n1_0.parent_id,n1_0.title,n1_0.update_by,n1_0.update_time from biz_notice n1_0 where n1_0.title like '通知公告%' escape '!' and n1_0.parent_id is null limit 20
1
2
3
4

count语句 耗时2.657s ，分页查询仅耗时 1ms，此时 select count 造成了严重的性能问题。

避免查询count总数，采取前端业务优化。

[2023-11-08 11:49:46] [statement-6] [耗时: 5ms] 执行SQL:
select n1_0.id,n1_0.create_by,n1_0.create_time,n1_0.json_str,n1_0.notice_time,n1_0.parent_id,n1_0.title,n1_0.update_by,n1_0.update_time from biz_notice n1_0 where n1_0.title like '通知公告%' escape '!' and n1_0.parent_id is null limit 4900,20
1
2

limit 4900,20，获取20条，查询sql耗时 5ms，前端30毫秒内加载表格数据。

问题2 limit分页 offset大幅度跳过记录 查询慢

执行结果 1.2秒。跳页更大之后 越来越慢。

select * from biz_notice order by id limit 1000000,20

1. 使用子查询 仅返回主键，耗时 0.9秒

select * from biz_notice where id > (
select id from biz_notice where title like ‘通知%’ ORDER BY id LIMIT 1000000,1
) order by id limit 20;

2.使用上次记录中的id， 然后where id < 最小id ,使用order by的排序规则, 耗时 0.039秒。（升序降序都可）

select * from biz_notice where id < 1722077379488935657 order by id desc limit 20;

建议在需要分页的表中使用有一个序列字段 (或多个)，可以使自增 ID、雪花ID 或时间戳，即便仅仅只是为了分页。

总结

看业务场景 总的来说就是，调整 业务的查询展现方式。要注意 limit 超大offset 也会慢。其他 sql本身的查询条件 索引、表关联 等优化sql。

其他解决方法思路

1.限制查询结果的不准确分页

必须要有分页信息，但是要求又不高，比如有些业务需要做排行，比如某些场景 仅需要前1000，配合sql实现查询即可。

select count(*) from (select id from product where category=? order by id asc limit 5000);

2.数据量大又必须有精确分页的业务需求

采用强大的硬件来支撑或者使用分布式方案，投入高端硬件的同时，项目架构也要改进，总之实现大数据、高并发的成本非常高。