java分页查询总条数_详解分页组件中查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 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 joins = joins();
// 是否全局开启了optimizejoin(这⾥建议还可以从page中按每次查询设置)
if (optimizejoin && collectionutils.isnotempty(joins)) {
boolean canremovejoin = true;
string wheres = optional.where()).map(expression::tostring).pty);
for (join join : joins) {
// 仅限left join
if (!join.isleft()) {
canremovejoin = false;
break;
}
table table = (table) ightitem();
string str = optional.alias()).map(alias::getname).ame()) + stringpool.dot;
string onexpressions = xpression().tostring();
/* 如果 join ⾥包含 ?(代表on语句有查询条件)
或者
where语句包含连接表的条件
就不移除 join */
if (ains(stringpool.question_mark) || ains(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
多表left joinvs
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 : selectitems()) {
if (string().contains(stringpool.question_mark)) {
return sqlinfo.string()));
}
}
// 包含 distinct、groupby不优化
if (distinct != null || null != groupby) {
return sqlinfo.string()));
}
...
// 优化 sql，count_select_item其实就是select count(1)语句
plainselect.setselectitems(count_select_item);
3 总结
本⽂其实是针对通⽤分页组件中，对查count记录数这⼀步骤的⼀些优化思路，回顾⼀下：
优化order by
优化join语句
优化select count(1)位置
注意以上优化对应的限制，否则可能导致业务错误(特别是join优化，⽐较隐藏)
其实并不局限于mbp，⼤家⾃定义的分页也可以尝试⽤上，对分页时的优化还是效果显著的
“⽤来记录⽣命的演进，故事的迭代。期望做⼀个给⼤家带来帮助和思考的平台” ——深邃⽼夏
如您对本⽂有疑问或者有任何想说的，请点击进⾏留⾔回复，万千⽹友为您解惑！

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。