mysql分页的sql性能优化

背景：因为公司的报表需求，需要批量统计数千万数据，单个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 

• 测试数据量共70w，我们可以看到查到最后非常缓慢，这里分页时，索引需要翻页，查询的页越往后，翻的页越多，导致越来越慢。

• 由于项目使用了主键自增，所以想到用当前页的最大Id来进行分页

- 采用主键自增id分页

踩坑：LIMIT #{pageNum},

由于id并非完全按照主键自增排序的（参考上一篇文章），此处id会出现跳过数据的情况，我们发现这里第一条数据，竟然从1139559开始

我们将子查询id进行order by和主查询都order by

终于拿到了我们想要的值
但大部分情况我们是需要条件的，于是

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页码的值=页码+页数，如果要查询的值得总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);