本次的分页主要用的是pageHelper插件完成，将分页信息回来给前端，进行分页的总数、数据展现

一、引入相关依靠

<!--分页插件-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.1.10</version>
        </dependency>

二、分页优化

2.1、SQL优化

首要咱们先看下面的分页SQL：

<select id="selectOrderList" resultType="com.guazi.tax.entity.response.TaxInvoiceOrderRespDTO" parameterType="com.guazi.tax.entity.request.TaxInvoiceOrderParam">
     select  o.order_no,o.order_type,o.id,o.order_name,o.open_amount,o.order_time,o.buyer_name,o.surplus_amount,o.order_amount,o.seller_tax_no,o.seller_name,o.surplus_amount 'apply_amount'
     ,cost.cost_id,s.name as 'costName',g.goods_code as 'spbm',g.goods_name as 'spmc' from tax_invoice_order o
     join tax_invoice_order_cost cost on cost.order_no = o.order_no and cost.order_type=o.order_type
     join tax_ws.sys_dict s on s.value=cost.cost_id
     join tax_cost_goods g on g.cost_id=cost.cost_id
     <where>
         <if test="taxCategory !='' and taxCategory!=null">
             o.order_type=#{taxCategory} and
         </if>
         <if test="orderNo !='' and orderNo!=null">
             o.order_no=#{orderNo} and
         </if>
         <if test="costId !='' and costId!=null">
             cost.cost_id=#{costId} and
         </if>
         <if test="buyerName !=null and buyerName !=''">
             o.buyer_name like CONCAT('%',#{buyerName}, '%') and
         </if>
         <if test="orderStartTime !=null and orderStartTime !=''">
             o.order_time &gt;= #{orderStartTime} and
         </if>
         <if test="orderEndTime !=null and orderEndTime !=''">
             o.order_time &lt;= #{orderEndTime} and
         </if>
         o.surplus_amount!=0 and s.code='TAX_COST_ITEM'
         and s.code='TAX_COST_ITEM'  and s.isdel=0 and cost.del_flag=0
     </where>

这条SQL相关了多张表、其间大表数据在25W+，首要会想到的是加索引，咱们先看现在SQL的剖析

运用MySQL自带的剖析explain，如上图能够看到type中类型为ref，走了索引其间cost表是联合索引，进行了索引覆盖rows影响行数11w+,能够看到还有个all全表扫描的情况，虽然是个字典表目前看到的是走的全表扫描，咱们能够在恰当的优化下，extra中为join buffer这种咱们是需求优化的。

当时的这条SQL全量数据运用了4秒多，如图

在进行分页数据时，咱们是否需求全量数据回来呢？答案是不需求的，当时我的场景是只需求每页展现5条数据就行了，那只需求回来5条数据就好了，咱们先看看运用limit来完成

     ,cost.cost_id,s.name as 'costName',g.goods_code as 'spbm',g.goods_name as 'spmc' from tax_invoice_order o
     join tax_invoice_order_cost cost on cost.order_no = o.order_no and cost.order_type=o.order_type
     join tax_ws.sys_dict s on s.value=cost.cost_id
     join tax_cost_goods g on g.cost_id=cost.cost_id
     where
             o.order_type='CLGH' and
         o.surplus_amount!=0 and s.code='TAX_COST_ITEM'
         and s.code='TAX_COST_ITEM'  and s.isdel=0 and cost.del_flag=0
  limit 1,5;

主页耗时800毫秒，好像还行，那么当limit 4000,5时时长就会很长。

通过剖析，咱们能够通过ID索引进行优化下，再看看耗时多长。

select  o.order_no,o.order_type,o.id,o.order_name,o.open_amount,o.order_time,o.buyer_name,o.surplus_amount,o.order_amount,o.seller_tax_no,o.seller_name,o.surplus_amount 'apply_amount'
     ,cost.cost_id,s.name as 'costName',g.goods_code as 'spbm',g.goods_name as 'spmc' from tax_invoice_order o
     join tax_invoice_order_cost cost on cost.order_no = o.order_no and cost.order_type=o.order_type
     join tax_ws.sys_dict s on s.value=cost.cost_id
     join tax_cost_goods g on g.cost_id=cost.cost_id
     where
             o.order_type='CLGH' and
         o.surplus_amount!=0 and s.code='TAX_COST_ITEM'
         and s.code='TAX_COST_ITEM'  and s.isdel=0 and cost.del_flag=0
#   limit 47525,5;
         and o.id >= (select id from tax_invoice_order order by id  limit 1,1) limit 5

主页耗时不超过200毫秒，咱们先通过ID确定详细的ID，然后在进行分页提升了很大的功能。

咱们再试试，定位到40000页的时候，耗时怎么样？

select  o.order_no,o.order_type,o.id,o.order_name,o.open_amount,o.order_time,o.buyer_name,o.surplus_amount,o.order_amount,o.seller_tax_no,o.seller_name,o.surplus_amount 'apply_amount'
     ,cost.cost_id,s.name as 'costName',g.goods_code as 'spbm',g.goods_name as 'spmc' from tax_invoice_order o
     join tax_invoice_order_cost cost on cost.order_no = o.order_no and cost.order_type=o.order_type
     join tax_ws.sys_dict s on s.value=cost.cost_id
     join tax_cost_goods g on g.cost_id=cost.cost_id
     where
             o.order_type='CLGH' and
         o.surplus_amount!=0 and s.code='TAX_COST_ITEM'
         and s.code='TAX_COST_ITEM'  and s.isdel=0 and cost.del_flag=0
#   limit 47525,5;
         and o.id >= (select id from tax_invoice_order order by id  limit 40000,1) limit 5

整体来看，优化了很多，减少了时长，优化之后explain下看看，如下图。

三、自界说分页

通过刚刚SQL的优化，那咱们想回来详细的主页、末页，分页数，就需求用到分页插件了。

咱们知道运用分页会给咱们的SQL后边拼接上limit pageNum，pageSize，但是咱们现在优化后的是这样的，如下：

 and o.id >= (select id from tax_invoice_order order by id  limit 40000,1) limit 5

自界说分页源码如下：

/**
 * @Author: toby
 * @Description: 自界说pageHelper的limit分页
 * @Date: 2022/12/21 下午6:10
 * @Version: V1.0
 */
public class MySqlDialectUtils extends MySqlDialect {
    //正则表达式
    private static final String pattern = "([\\s|\\S]*?)/\\*\\s*END\\s*\\*/\\s*([\\s|\\S]*)";
    private static final Pattern PATTERN = Pattern.compile(pattern, Pattern.CASE_INSENSITIVE);
    /**
     * 把limit语句放到 MAPPINGLIMIT符号所在的位置，也就是主表的位置，对主表进行分页
     *
     * @return 加limit后的sql
     */
    @Override
    public String getPageSql(String sql, Page page, CacheKey pageKey) {
        //假如不匹配正则，走原始的sql
        if (!Pattern.matches(pattern, sql)) {
            return super.getPageSql(sql, page, pageKey);
        }
        String beforeLimitSql = "";
        Matcher m = PATTERN.matcher(sql);
        if (m.find()) {
            //符号前的sql语句
            beforeLimitSql = m.group(1);
        }
        String limitSql = " LIMIT ? ";
        String sqlString = beforeLimitSql + " " + limitSql;
        LoggerUtils.info("-----------自界说分页：替换后SQL：{}-----------",sqlString);
        return sqlString;
    }
    /**
     * 把分页参数放到参数列表里
     *
     * @return
     */
    @Override
    public Object processPageParameter(MappedStatement ms, Map<String, Object> paramMap, Page page, BoundSql boundSql, CacheKey pageKey) {
        //假如不匹配正则，走原始的sql设置
        if (!Pattern.matches(pattern, boundSql.getSql())) {
            return super.processPageParameter(ms, paramMap, page, boundSql, pageKey);
        }
        //设置参数
        paramMap.put(PAGEPARAMETER_FIRST, page.getStartRow());
        paramMap.put(PAGEPARAMETER_SECOND, page.getPageSize());
        pageKey.update(page.getStartRow());
        pageKey.update(page.getPageSize());
        //计算出来分页数据的放置位置
        Matcher m = PATTERN.matcher(boundSql.getSql());
        String beforeLimitSql = null;
        int limitIndex;
        if (m.find()) {
            //符号前的sql语句
            beforeLimitSql = m.group(1);
        }
        //计算sql里有几个参数，按数据位置添加page
        limitIndex = StringUtils.countMatches(beforeLimitSql, "?");
        if (boundSql.getParameterMappings() != null) {
            List<ParameterMapping> newParameterMappings = new ArrayList<ParameterMapping>(boundSql.getParameterMappings());
            newParameterMappings.add(limitIndex,new ParameterMapping.Builder(ms.getConfiguration(), PAGEPARAMETER_SECOND, int.class).build());
            MetaObject metaObject = MetaObjectUtil.forObject(boundSql);
            metaObject.setValue("parameterMappings", newParameterMappings);
        }
        return paramMap;
    }
}

将自界说的SQL拼接注册到分页插件中；PageAutoDialect.registerDialectAlias(“mysql”, MySqlDialectUtils.class);

PageAutoDialect.registerDialectAlias("mysql", MySqlDialectUtils.class);
        PageHelper.startPage(pageNum, pageSize);
        List<TaxInvoiceOrderRespDTO> taxInvoiceOrderRespDTOS = taxInvoiceOrderDao.selectOrderList(param);
        PageInfo<TaxInvoiceOrderRespDTO> pageInfo = new PageInfo<>(taxInvoiceOrderRespDTOS);

pageHelper在进行分页时，会将咱们当时的运行SQL拼接成 select count(*) from (咱们的分页SQL) tmp_count 下面代码为自界说计算分页总数，pageHelper自带的计算功能是比较差的，通过SQL的名字后边加上 _COUNT就能够履行自界说的计算

long selectOrderList_COUNT(TaxInvoiceOrderParam param);

<select id="selectOrderList_COUNT" resultType="java.lang.Long">
        select  count(o.id) from tax_invoice_order o
        join tax_invoice_order_cost cost on cost.order_no = o.order_no and cost.order_type=o.order_type
        join tax_cost_goods g on g.cost_id=cost.cost_id
        <where>
        <if test="taxCategory !='' and taxCategory!=null">
            o.order_type=#{taxCategory} and
        </if>
        <if test="orderNo !='' and orderNo!=null">
            o.order_no=#{orderNo} and
        </if>
        <if test="costId !='' and costId!=null">
            cost.cost_id=#{costId} and
        </if>
        <if test="buyerName !=null and buyerName !=''">
            o.buyer_name like CONCAT('%',#{buyerName}, '%') and
        </if>
        <if test="orderStartTime !=null and orderStartTime !=''">
            o.order_time &gt;= #{orderStartTime} and
        </if>
        <if test="orderEndTime !=null and orderEndTime !=''">
            o.order_time &lt;= #{orderEndTime} and
        </if>
         o.surplus_amount!=0 and cost.del_flag=0
        </where>
    </select>

通过自界说，咱们在SQL后边加上标识 END，插件会进行扫描，将limit按照咱们自界说的方法去拼接

     select  o.order_no,o.order_type,o.id,o.order_name,o.open_amount,o.order_time,o.buyer_name,o.surplus_amount,o.order_amount,o.seller_tax_no,o.seller_name,o.surplus_amount 'apply_amount'
     ,cost.cost_id,s.name as 'costName',g.goods_code as 'spbm',g.goods_name as 'spmc' from tax_invoice_order o
     join tax_invoice_order_cost cost on cost.order_no = o.order_no and cost.order_type=o.order_type
     join tax_ws.sys_dict s on s.value=cost.cost_id
     join tax_cost_goods g on g.cost_id=cost.cost_id
     <where>
         <if test="taxCategory !='' and taxCategory!=null">
             o.order_type=#{taxCategory} and
         </if>
         <if test="orderNo !='' and orderNo!=null">
             o.order_no=#{orderNo} and
         </if>
         <if test="costId !='' and costId!=null">
             cost.cost_id=#{costId} and
         </if>
         <if test="buyerName !=null and buyerName !=''">
             o.buyer_name like CONCAT('%',#{buyerName}, '%') and
         </if>
         <if test="orderStartTime !=null and orderStartTime !=''">
             o.order_time &gt;= #{orderStartTime} and
         </if>
         <if test="orderEndTime !=null and orderEndTime !=''">
             o.order_time &lt;= #{orderEndTime} and
         </if>
         o.surplus_amount!=0 and s.code='TAX_COST_ITEM'
         and s.code='TAX_COST_ITEM'  and s.isdel=0 and cost.del_flag=0
         and o.id >= (select id from tax_invoice_order order by id  limit #{page},1) /*END*/
     </where>

自界说分页插件后履行的SQL，如下：

四、分页插件pageHelper源码解析

pagehelper在履行SQL之前会进行拦截，判别是否手写的COUNT。在履行SQL时，会去获取SQL在后边拼接上_COUNT

String countMsId = countMsIdGen.genCountMsId(ms, parameter, boundSql, countSuffix);

pageHelper支持各种数据库，本文用的是MySQL，看看是怎么进行拼接limit的。源码如下：


/*
 * The MIT License (MIT)
 *
 * Copyright (c) 2014-2022 abel533@gmail.com
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in
 * all copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 * THE SOFTWARE.
 */
package com.github.pagehelper.dialect.helper;
import com.github.pagehelper.Page;
import com.github.pagehelper.dialect.AbstractHelperDialect;
import com.github.pagehelper.util.MetaObjectUtil;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.reflection.MetaObject;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
/**
 * @author liuzh
 */
public class MySqlDialect extends AbstractHelperDialect {
    @Override
    public Object processPageParameter(MappedStatement ms, Map<String, Object> paramMap, Page page, BoundSql boundSql, CacheKey pageKey) {
        paramMap.put(PAGEPARAMETER_FIRST, page.getStartRow());
        paramMap.put(PAGEPARAMETER_SECOND, page.getPageSize());
        //处理pageKey
        pageKey.update(page.getStartRow());
        pageKey.update(page.getPageSize());
        //处理参数装备
        if (boundSql.getParameterMappings() != null) {
            List<ParameterMapping> newParameterMappings = new ArrayList<ParameterMapping>(boundSql.getParameterMappings());
            if (page.getStartRow() == 0) {
                newParameterMappings.add(new ParameterMapping.Builder(ms.getConfiguration(), PAGEPARAMETER_SECOND, int.class).build());
            } else {
                newParameterMappings.add(new ParameterMapping.Builder(ms.getConfiguration(), PAGEPARAMETER_FIRST, long.class).build());
                newParameterMappings.add(new ParameterMapping.Builder(ms.getConfiguration(), PAGEPARAMETER_SECOND, int.class).build());
            }
            MetaObject metaObject = MetaObjectUtil.forObject(boundSql);
            metaObject.setValue("parameterMappings", newParameterMappings);
        }
        return paramMap;
    }
    @Override
    public String getPageSql(String sql, Page page, CacheKey pageKey) {
        StringBuilder sqlBuilder = new StringBuilder(sql.length() + 14);
        sqlBuilder.append(sql);
        if (page.getStartRow() == 0) {  // 假如是主页的话，就拼一个占位
            sqlBuilder.append("\n LIMIT ? ");
        } else {
            sqlBuilder.append("\n LIMIT ?, ? "); // 不然拼接两个占位
        }
        return sqlBuilder.toString();
    }
}

分页优化–pageHelper自定义分页–分析分页插件源码

一、引入相关依靠

二、分页优化

2.1、SQL优化

三、自界说分页

四、分页插件pageHelper源码解析

作者信息

推广

分页优化–pageHelper自定义分页–分析分页插件源码

一、引入相关依靠

二、分页优化

2.1、SQL优化

三、自界说分页

四、分页插件pageHelper源码解析

相关文章

Spring 源码阅读 21：循环依赖和三级缓存

JAVA实战：如何让单元测试覆盖率达到80%甚至以上

Java 并发编程解析 | 如何正确理解Java领域中的锁机制，我们一般需要掌握哪些理论知识？

Redis 底层数据结构大揭秘：五张图看透Redis数据结构

作者信息

推广