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

yogurt_studio
2,888 阅读5分钟

本次的分页主要用的是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.entity.response.TaxInvoiceOrderRespDTO" parameterType="com.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的分析

图片.png

图片.png

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

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

图片.png

在进行分页数据时，我们是否需要全量数据返回呢？ 答案是不需要的，当前我的场景是只需要每页展示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时时长就会很长。

图片.png

经过分析，我们可以通过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

图片.png

首页耗时不超过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

图片.png

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

图片.png

三、自定义分页

经过刚刚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，如下：

图片.png

四、分页插件pageHelper源码解析

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

图片.png

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

图片.png

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

图片.png


/*
 * 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();
    }

}
avatar
文章
阅读
粉丝
相关推荐
得嘞，分页插件PageHelper返回记录总数total竟然出错了！
688阅读
 · 
0点赞
MySql分页查询优化: 基于令牌的分页比基于偏移量的分页性能更好？
1.3k阅读
 · 
11点赞
分页合理化是什么？
3.6k阅读
 · 
18点赞
讲解 CSS At Rules 分页媒体 — @page
1.1k阅读
 · 
8点赞
.NET PAGE分页
332阅读
 · 
3点赞
一个烂分页，踩了三个坑！
22k阅读
 · 
180点赞
【java】使pagehelper分页与mybatis-plus分页兼容存在
976阅读
 · 
3点赞
前端滚动分页
7.0k阅读
 · 
63点赞
简化Mybatis分页操作，让我们来聊聊 PageHelper 及实现原理
2.3k阅读
 · 
7点赞
MySQL百万数据深度分页优化思路分析
14k阅读
 · 
132点赞

友情链接：