PageHelper自定义Count查询及其优化

PageHelper自定义Count查询及其优化

一：背景

PageHelper默认情况下会帮我们根据查询语句自动生成COUNT查询SQL，但是有些情况下，PageHelper自动生成COUNT查询SQL存在效率问题。比如，其中使用了GROUP BY语句，多表关联，生成的COUNT查询SQL查询效率很慢

1.1、解决方法

1.count()有优化空间的直接优化，Count执行条件慢无外乎索引是否命中，执行SQL是否多表关联

2.count()没办法优化的，只能从业务入手，取消关联的一些条件查询

3.不返回count总条数，只能一页一页往下翻

4.缓存总条数，实时性保证不保证

5.异步查询加载，前端后端一起优化

也就是点击一次请求查询两个接口，list接口肯定很快返回，可以直接进行列展示，供用户操作查看等；
count接口返回较慢，分页插件下面展示loading（提示正在加载），异步执行完成后告诉前端

6.彻底解决：引入ES或其他数据库

7.代码层面：投机取巧（二：利用反射判断请求参数是否有模糊查询）

每次请求分页接口，没有任何条件查询的时候，count会计算总条数，数据量大时非常耗时；但是模糊查询的话，速度还可以接受；是否有一种方法可以：如果只是有pageIndex和pageSize参数的时候，我用自定义的count；如果有其他模糊条件查询的时候，我选择pageHelper自带的count，执行原有复杂SQL语句，维持count准确性

二：利用反射判断请求参数是否有模糊查询

2.1、分页不执行count

PageHelper.startPage(req.getCurPage(), req.getPageSize(),false);
如果将此参数设置为false，pageHlper不执行count方法；

2.2、思路

1.只有分页参数--》不执行默认count方法，设置为false--》自定义count--》返回自定义count总条数2.有模糊查询分页参数--》执行默认count方法，设置为true--》pageHelper自带count--》count参数pageHelper会返回

2.3、代码示例

 // 定义一个私有静态列表来存储需要忽略的字段名称,填写当前类的字段private static final List<String> IGNORED_FIELDS = Arrays.asList("curPage", "pageSize");//用作分页count选择,如果原始分页,选择全表差,反之用条件查public boolean areAllFieldsEmptyExceptIgnored() {//获取父类的字段//Field[] declaredFields = this.getClass().getSuperclass().getDeclaredFields();//只获取了当前类的字段for (Field field : this.getClass().getDeclaredFields()) {// 忽略静态字段和transient字段if (java.lang.reflect.Modifier.isStatic(field.getModifiers()) ||java.lang.reflect.Modifier.isTransient(field.getModifiers())) {continue;}// 忽略配置列表中的字段if (IGNORED_FIELDS.contains(field.getName())) {continue;}// 确保私有字段也可以访问field.setAccessible(true);try {// 获取字段值Object value = field.get(this);//检查字段值是否为空// 检查字段值是否为空if (value instanceof String && ((String) value).isEmpty()) {//字段为空字符串，这是允许的，继续检查下一个字段continue;} else if (value instanceof String) {// 字段为非空字符串return false;} else if (value instanceof List && ((List<?>) value).isEmpty()) {// 字段为非空列表，这是允许的，继续检查下一个字段continue;}else if (value instanceof List) {//字段为非空集合return false;}  else if (value != null) {//字段为非空对象return false;}} catch (IllegalAccessException e) {// 处理可能的非法访问异常throw new RuntimeException("Error accessing field: " + field.getName(), e);}}//所有字段都是空的return true;}

这段反射说明：如果只有分页参数，会返回true；如果有模糊查询参数，会返回false；需要忽略的字段，支持自己设置

        boolean status = req.areAllFieldsEmptyExceptIgnored();PageHelper.startPage(req.getCurPage(), req.getPageSize(),!status);List<GoodsSpu> resultList =goodsSpuMapper.goodsSpuList(daoReq);PageInfo<GoodsSpu> pageInfo = new PageInfo<>(resultList);if (status) {//count全查pageInfo.setTotal(goodsSpuMapper.goodsSpuListCount(daoReq));}else{//count条件查,走默认分页的count}

<select id="goodsSpuList" parameterType="com.kkd.goods.model.in.GoodsSpuListDaoReq" resultType="com.kkd.goods.entity.GoodsSpu">select DISTINCT r.id dis_id,r.* from(select gsp.* from gd_goods_spu gspLEFT JOIN gd_goods_sku gsk on gsp.spu=gsk.spuLEFT JOIN gd_goods_sku_upc gsu on gsp.spu=gsu.spuLEFT JOIN gd_goods_shop_category_relation gscr on gsp.spu=gscr.spuwheregsp.is_delete=0and gsp.org_id=#{orgId}<if test="name != null and name != '' ">and gsp.`name`like concat('%',#{name},'%')</if><if test="spuList != null and spuList.size > 0">AND  gsp.spu IN<foreach item="id" index="index" collection="spuList" open="(" separator="," close=")">#{id}</foreach></if><if test="skuList != null and skuList.size > 0">AND  gsk.sku IN<foreach item="id" index="index" collection="skuList" open="(" separator="," close=")">#{id}</foreach></if><if test="upcList != null and upcList.size > 0">AND  gsu.upc IN<foreach item="id" index="index" collection="upcList" open="(" separator="," close=")">#{id}</foreach></if><if test="categoryCodes != null and categoryCodes.size > 0">AND  gsp.category_code IN<foreach item="id" index="index" collection="categoryCodes" open="(" separator="," close=")">#{id}</foreach></if><if test="shopCategoryIds != null and shopCategoryIds.size > 0">AND  gscr.shop_category_id IN<foreach item="id" index="index" collection="shopCategoryIds" open="(" separator="," close=")">#{id}</foreach></if><if test="ePlatformCategoryId != null">and gsp.e_platform_category_id=#{ePlatformCategoryId}</if><if test="isNormal != null">and gsp.normal=#{isNormal}</if><if test="imageEmpty != null  and imageEmpty == 1 ">and gsp.images is null</if><if test="isMaster != null ">and gsp.`master` = #{isMaster}</if><if test="masterSpu != null and masterSpu !='' ">and gsp.`master_spu` = #{masterSpu}</if>ORDER BY gsp.update_time desc) r
</select>

<select id="goodsSpuListCount" resultType="java.lang.Long" parameterType="com.kkd.goods.model.in.GoodsSpuListDaoReq">select count(*) from gd_goods_spu gspwheregsp.is_delete=0and gsp.org_id=#{orgId}
</select>

三：自定义COUNT查询SQL(只适用于单表)

3.1、局限性

1.对于单表查询:分页执行的sql执行效率都慢，count执行的时候首先考虑命中索引，如果拆分出来效率能得到提升再用
2.对于多表查询：如果查询条件仅仅只是主表中的条件，此方法适用如果查询条件需要从表中的条件，自定义的这个count就不满足
3.缓存count，业务上总数实时性要求不高，或者总数变化不快的情况下可以使用

<dependency><groupId>com.github.pagehelper</groupId><artifactId>pagehelper</artifactId><version>5.0.4</version>
</dependency>

3.2、使用方式

原有的代码不需要动，只需要在Mybatis的xml文件里添加一个count查询
这里注意以下三点即可：

1. id和对应的查询语句保持一致，并且以 _COUNT 结尾
2. 入参和对应的查询语句保持一致
3. 出参为 resultType=“Long”

<select id="goodsSpuList_COUNT" parameterType="com.kkd.goods.model.in.GoodsSpuListDaoReq" resultType="java.lang.Long">select count(*)from gd_goods_spu gspwhere gsp.is_delete = 0and gsp.org_id = #{orgId}
</select>

四：各种模糊查询XML中示例

    <select id="goodsSpuList" parameterType="com.kkd.goods.model.in.GoodsSpuListDaoReq" resultType="com.kkd.goods.entity.GoodsSpu">select DISTINCT r.id dis_id,r.* from(select gsp.* from gd_goods_spu gspLEFT JOIN gd_goods_sku gsk on gsp.spu=gsk.spuLEFT JOIN gd_goods_sku_upc gsu on gsp.spu=gsu.spuLEFT JOIN gd_goods_shop_category_relation gscr on gsp.spu=gscr.spuwheregsp.is_delete=0and gsp.org_id=#{orgId}<if test="name != null and name != '' ">and gsp.`name`like concat('%',#{name},'%')</if><if test="spuList != null and spuList.size > 0">AND  gsp.spu IN<foreach item="id" index="index" collection="spuList" open="(" separator="," close=")">#{id}</foreach></if><if test="skuList != null and skuList.size > 0">AND  gsk.sku IN<foreach item="id" index="index" collection="skuList" open="(" separator="," close=")">#{id}</foreach></if><if test="upcList != null and upcList.size > 0">AND  gsu.upc IN<foreach item="id" index="index" collection="upcList" open="(" separator="," close=")">#{id}</foreach></if><if test="categoryCodes != null and categoryCodes.size > 0">AND  gsp.category_code IN<foreach item="id" index="index" collection="categoryCodes" open="(" separator="," close=")">#{id}</foreach></if><if test="shopCategoryIds != null and shopCategoryIds.size > 0">AND  gscr.shop_category_id IN<foreach item="id" index="index" collection="shopCategoryIds" open="(" separator="," close=")">#{id}</foreach></if><if test="ePlatformCategoryId != null">and gsp.e_platform_category_id=#{ePlatformCategoryId}</if><if test="isNormal != null">and gsp.normal=#{isNormal}</if><if test="imageEmpty != null  and imageEmpty == 1 ">and gsp.images is null</if><if test="isMaster != null ">and gsp.`master` = #{isMaster}</if><if test="masterSpu != null and masterSpu !='' ">and gsp.`master_spu` = #{masterSpu}</if>ORDER BY gsp.update_time desc) r</select>

参考：Pagehelper自定义count查询