1 背景
研究 mybatis-plus(以下简称 MBP), 使用其分页功能时. 发现了一个的分页优化处理类, 官方对其未做详细介绍, 网上也未找到分析该类逻辑的只言片语, 这情况咱也不敢用呀, 索性深度剖析一下, 也方便他人.
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, 大家自定义的分页拦截器也可以尝试用上, 对分页时的优化还是效果显著的
"用来记录生命的演进, 故事的迭代. 期望做一个给大家带来帮助和思考的平台" -- 深邃老夏
来源: https://www.cnblogs.com/deepsummer/p/12517172.html