本次的分页主要用的是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 >= #{orderStartTime} and
</if>
<if test="orderEndTime !=null and orderEndTime !=''">
o.order_time <= #{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 >= #{orderStartTime} and
</if>
<if test="orderEndTime !=null and orderEndTime !=''">
o.order_time <= #{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 >= #{orderStartTime} and
</if>
<if test="orderEndTime !=null and orderEndTime !=''">
o.order_time <= #{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();
}
}