mysql分页的sql性能优化
发布时间：2022-09-21 14:57:56 所属栏目：MySql教程 来源：
导读： 背景：因为公司的报表需求，需要批量统计数千万数据，单个sql无法满足使用场景，于是分页将数据查询出来并进行处理。
- 采用传统分页
return lambdaQuery().le(GuOrder::getGuaranteeS
- 采用传统分页
return lambdaQuery().le(GuOrder::getGuaranteeS
|
背景：因为公司的报表需求，需要批量统计数千万数据，单个sql无法满足使用场景，于是分页将数据查询出来并进行处理。 - 采用传统分页 return lambdaQuery().le(GuOrder::getGuaranteeStartDate,date) .select(GuOrder::*, GuOrder::*, GuOrder::*, GuOrder::*, GuOrder::*, GuOrder::*) .page(new Page<>(pageNum, pageSize,false)).getRecords(); .............省略 耗时：1567,数量：5000 耗时：1868,数量：5000 耗时：1976,数量：5000 耗时：2527,数量：5000 耗时：2927,数量：5000 耗时：3539,数量：5000 耗时：4539,数量：5000 耗时：4963,数量：5000 耗时：5509,数量：5000 耗时：5544,数量：5000 - 采用主键自增id分页 踩坑：LIMIT #{pageNum}, 在这里插入图片描述 由于id并非完全按照主键自增排序的（参考上一篇文章），此处id会出现跳过数据的情况，我们发现这里第一条数据，竟然从1139559开始 在这里插入图片描述 终于拿到了我们想要的值 但大部分情况我们是需要条件的，于是 select * from vc_gu_order where id >= ( SELECT a.id FROM vc_gu a WHERE a.is_deleted = 0 AND a.date between '2022-05-01' and '2022-05-31' order by id LIMIT 5000, 1 ) and a.date between '2022-05-01' and '2022-05-31' limit 5000; 我们可以看到如果这样查询需要主查询和子查询都需要走一次索引，而date是非聚簇索引，这样查询数据又要多回表2次 查询耗时，也会多了许多 继续改造，怎么能少走一次索引，根据上面Sql我们发现，翻页时，如果要查询的id符合顺序，next页码的值=页码+页数mysql分页，如果要查询的值得总id范围=1，10000，那么最小id一定是1,最大id一定是10000，那我们可不可以在1,10000中进行分页查询呢？这样我们只需要求一次最小id和最大id即可 SELECT max(id) as maxId, min(id) as minId FROM table where date BETWEEN '2022-05-01' AND '2022-05-31' 最大id8080261 最小id7392515 根据最小id和最大查询id查询分页 select * from table where id > 7397515 and date BETWEEN '2022-05-01' AND '2022-05-31' ORDER BY id limit 5000; 我们可以看到这个id在700w数据之后，查询的效率提升了却很多倍，有人问最大id不是已经查出来了，为什么不直接id between min and max，这个问题，还是和一开始一样，id和date其实是不连续的，即使某个id符合最大最小的id范围，但是id依然不属于这个date范围。 附分页代码 Integer pageSize = 5000; MaxAndMinIdDTO maxAndMinId = guService.getMaxAndMinId(date); if (Objects.isNull(maxAndMinId)){ return new ArrayList<>(); } Long minId = maxAndMinId.getMinId(); Long maxId = maxAndMinId.getMaxId(); if (maxAndMinId.getMinId()==null){ return new ArrayList<>(); } minId = minId-1; List<GU> gu; List<GU> gus= new ArrayList<>(); boolean flag; do{ gus= guService.pageBeforeDate(date, minId, pageSize); if (CollUtil.isNotEmpty(gus)){ } minId = guOrders.get(guOrders.size()-1).getId(); flag = maxId > minId; }while (flag); （编辑：拼字网 - 核心网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐