专栏/详解分页组件中查count总记录优化

详解分页组件中查count总记录优化

2020-03-17 17:41--阅读 · --喜欢 · --评论
粉丝:1293文章:2

1 背景

研究mybatis-plus(以下简称MBP),使用其分页功能时。发现了一个JsqlParserCountOptimize的分页优化处理类,官方对其未做详细介绍,网上也未找到分析该类逻辑的只言片语,这情况咱也不敢用呀,索性深度剖析一下,也方便他人。

2 原理

首先PaginationInterceptor分页拦截器的原理这里不累述(mybatis通用分页封装的实现原理挺简单的,也就那么回事),最终落实到查询上基本是分为2个sql:查count总记录数 + 查真实分页记录。而此类是用优化来其中的查count这步。这count查询要怎么优化?这里上真实场景帮助大家理解: 假如有2张表user、user_address、user_account分别记录用户和用户地址和用户账户,1个用户可能有多个地址即1对多关系;1个用户只能有1个账户即1对1关系。

2.1 优化order by

先看下面的sql,放到分页查询下

select * from user order by age desc, update_time desc

传统分页组件往往是

查count: select count(1) from (select * from user order by age desc, update_time desc) 查记录:select * from user order by age desc, update_time desc limit 0,50

发现问题了吗?查count时的order by是完全可以去掉的!在复杂查询、大表、非索引字段排序等情况下查记录已经很慢了,查count又要来一次!所以查count显然希望优化为select count(1) from (select * from user)

2.1.1 限制

但是也不是所有场景都可以优化的,比如带group by的查询

2.1.2 源码

所以MBP源码如下实现,没有group by且有order by的语句,就把order by去掉

// 添加包含groupBy 不去除orderByif (null == groupBy && CollectionUtils.isNotEmpty(orderBy)) {        plainSelect.setOrderByElements(null);        sqlInfo.setOrderBy(false); }

2.2 优化join场景

在join操作时,也存在优化可能,看下面sql

select u.id,ua.account from user u left join user_account ua on u.id=ua.uid

这时候分页查count时,其实可以去掉left join直查user,因为user与user_account是1对1关系,如下

查count: select count(1) from user u 查记录: select u.id,ua.account from user u left join user_account ua on u.id=ua.uid limit 0,50

2.2.1 限制

查count能否去掉join直查首表,还存在诸多限制,如下:

表记录join后不能放大记录数

从上面案例可知,如果left join后记录数对比直查首表的总记录数会放大,就不能进行这个优化。比如3个用户每人各记录2条地址

select u.id,ua.address from user u left join user_address ua on u.id=ua.uid (6条) vsselect count(1) from user u (3条)

此时去掉left join去查count就会得到更少的总记录数。注意这可能会变成一个坑,MBP无法自动判断本次分页查询是否会进行记录放大,所以join优化默认是关闭的,如果想开启需要声明自定义的JsqlParserCountOptimize bean,并设置optimizeJoin为true,如下

@Bean    public PaginationInterceptor paginationInterceptor() {        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();        paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));        return paginationInterceptor;    }

其实这里源码设计有些不合理,因为开了之后就得小心翼翼的审查自己各类left join的分页代码了,如果有放大的话,只能构造Page对象时,设置optimizeCountSql为false(默认true),相当于关闭本次查询所有count优化,那么不光是join,包括order by等优化也都不进行了。建议可以改为从Page(或ThreadLocal?)中获取optimizeJoin,变为每次查询级别可配的配置,默认关,而经过开发人员确认可join优化的才主动在本次查询级别设置开启。

仅限left join

如果是inner join或right join往往都会放大记录数,所以MBP优化会自动判断如果多个join里出现任何非left join的,就不进行此优化,比如from a left join b .... right join c... left join d此时会直接不进行优化

on语句有查询条件

比如

select u.id,ua.account from user u left join user_account ua on u.id=ua.uid and ua.account > ?

where语句包含连接表的条件

比如

select u.id,ua.account from user u left join user_account ua on u.id=ua.uid where ua.account > ?

2.2.2 源码

MBP的join优化源码大致如下,对应上面的优化和限制

List<Join> joins = plainSelect.getJoins();// 是否全局开启了optimizeJoin(这里建议还可以从Page中按每次查询设置)if (optimizeJoin && CollectionUtils.isNotEmpty(joins)) {    boolean canRemoveJoin = true;    String whereS = Optional.ofNullable(plainSelect.getWhere()).map(Expression::toString).orElse(StringPool.EMPTY);    for (Join join : joins) {            // 仅限left join            if (!join.isLeft()) {                    canRemoveJoin = false;                    break;            }            Table table = (Table) join.getRightItem();            String str = Optional.ofNullable(table.getAlias()).map(Alias::getName).orElse(table.getName()) + StringPool.DOT;            String onExpressionS = join.getOnExpression().toString();            /* 如果 join 里包含 ?(代表on语句有查询条件)            或者            where语句包含连接表的条件            就不移除 join */            if (onExpressionS.contains(StringPool.QUESTION_MARK) || whereS.contains(str)) {                    canRemoveJoin = false;                    break;            }    }    if (canRemoveJoin) {            plainSelect.setJoins(null);    } }

2.3 优化select count(1)位置

传统的分页,往往是在原始查询sql的外层套select count(1),比如

select count(1) from (select * from user)

而count真实目的是得到记录数,完全不需要原始查询里的select *产生额外耗时,所以可以优化为如下语句

select count(1) from user

2.3.1 限制

同样的,有一些场景不能进行count位置优化

select的字段里包含参数

如果select中包含#{}、${}等待替换的参数,也不能进行此优化,因为后续占位符替换真实值阶段会由于占位符个数减少导致报错,比如

select count(1) from (select power(#{aSelectParam},2) from user_account where uid=#{uidParam}) uavsselect count(1) from user_account where uid=#{uidParam} ua

MBP官方issue#95登记了此问题

包含distinct

select中包含distinct去重的语句,若去除有可能导致count记录数增大,所以不能进行此优化。比如

select count(1) from (select distinct(uid) from user_address) ua vsselect count(1) from user_address ua  #记录数可能增大

包含group by

包含group by的语句,由于select中往往会有聚合函数,所以count(1)内置语义变成了聚合函数,不能进行此优化。比如

select count(1) from (select uid,count(1) from user_address group by uid) ua #返回单行单列总记录数 vsselect count(1) from user_address group by uid #返回多行单列聚合count

2.3.2 源码

MBP中相关源码如下

//select的字段里包含参数不优化for (SelectItem item : plainSelect.getSelectItems()) {        if (item.toString().contains(StringPool.QUESTION_MARK)) {                return sqlInfo.setSql(SqlParserUtils.getOriginalCountSql(selectStatement.toString()));        } }// 包含 distinct、groupBy不优化if (distinct != null || null != groupBy) {        return sqlInfo.setSql(SqlParserUtils.getOriginalCountSql(selectStatement.toString())); } ...// 优化 SQL,COUNT_SELECT_ITEM其实就是select count(1)语句plainSelect.setSelectItems(COUNT_SELECT_ITEM);

3 总结

本文其实是针对通用分页组件中,对查count记录数这一步骤的一些优化思路,回顾一下:

  • 优化order by

  • 优化join语句

  • 优化select count(1)位置

  • 注意以上优化对应的限制,否则可能导致业务错误(特别是join优化,比较隐藏)

其实并不局限于MBP,大家自定义的分页拦截器也可以尝试用上,对分页时的优化还是效果显著的


投诉或建议
推荐文章
更多精彩内容
学渣逆袭就靠它!数学YYDS
数学是高中学习科目里的重点,对于大多数同学来说都是短板。数学都是有套路的,所以有一套答题模板可以帮助你有条理的答题,不会落点,而且会节约很多时间。考试就是在有效的时间内高质量的完成试卷!学姐给大家整理了数学答题模板及解答题常考公式!
深圳市餐饮服务单位巡查详解(8.餐厨垃圾由有资质企业回收,无使用散装油脂,无现…)
餐厨垃圾是指居民日常生活及食品加工、饮食服务、单位供餐等活动中产生的垃圾,包括丢弃不用的菜叶、剩菜、剩饭、果皮、蛋壳、茶渣、骨头等,其主要来源为家庭厨房、餐厅、饭店、食堂、市场及其他与食品加工有关的行业。 俗称泔脚、泔水,也叫潲水又称馊水,主要是居民在生活消费过程中形成的生活废物,是食物垃圾中最主要的一种。特点是极易腐烂变质,散发恶臭,传播细菌和病毒。餐厨垃圾回收目的消除食品安全隐患(地沟油)实现餐厨垃圾的资源化利用提升城市形象 现场巡查餐饮服务单位的时候,我们是看不到餐厅怎么样处理餐厨垃圾,那么我们在检
解除微信接收文件只读属性的新方案,新版微信3.9.2.23也能用,适用范围更广
从3.9版本开始,微信PC端默认将接收到的Word、Excel等文件的文件属性设置为只读,这使得大家在打开文件后,对文件的修改只能以另存为新文件的方式进行保存,一时间很多小伙伴还不能接受这种设置。之前也给大家分享过一个解决方案,就是将version.dll文件复制到微信安装目录,这个方法小编亲测在最新版(3.9.2.xx)微信中已经失效了,该补丁在最新版微信中只能实现多开和防撤回,文件的只读属性已经无法解除了。网上目前最主流的解决方法就是对微信降级,降到3.8甚至更早版本来避免接收到的文件被修改为只读属性
评论