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

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 不去除orderby
if (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条）
vs
select 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}) ua
vs
select 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
vs
select 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 #返回单行单列总记录数
vs
select 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，大家自定义的分页拦截器也可以尝试用上，对分页时的优化还是效果显著的

“用来记录生命的演进，故事的迭代。期望做一个给大家带来帮助和思考的平台” ——深邃老夏