Could add smart count logic like Mybatis Plus in PageHelper Plugin?

[nikbobo]

Could add smart count logic like Mybatis Plus in Page Helper Plugin?
Mybatis Plus has a logic which will get smarting but may not accurate sql by auto optimize left join, but Mybatis PageHelper Plugin not has that.
Would you add it in plugin by optional option to enable or how to add it in plugin?

可以添加智能 count sql 优化逻辑吗？
Mybatis Plus 有个智能的 count 优化逻辑，会优化 left join，虽然在一些情况下可能会导致不准确的分页，但大多数场景还是很好用的，可以在 Mybatis PageHelper Plugin 里面添加它吗？作为一个可选开启的选项。或者如何扩展/修改插件使它支持这个功能？

参考代码

    /**
     * 获取自动优化的 countSql
     *
     * @param page 参数
     * @param sql  sql
     * @return countSql
     */
    protected String autoCountSql(IPage<?> page, String sql) {
        if (!page.optimizeCountSql()) {
            return lowLevelCountSql(sql);
        }
        try {
            Select select = (Select) JsqlParserGlobal.parse(sql);
            SelectBody selectBody = select.getSelectBody();
            // https://github.com/baomidou/mybatis-plus/issues/3920  分页增加union语法支持
            if (selectBody instanceof SetOperationList) {
                return lowLevelCountSql(sql);
            }
            PlainSelect plainSelect = (PlainSelect) select.getSelectBody();
            Distinct distinct = plainSelect.getDistinct();
            GroupByElement groupBy = plainSelect.getGroupBy();
            List<OrderByElement> orderBy = plainSelect.getOrderByElements();

            if (CollectionUtils.isNotEmpty(orderBy)) {
                boolean canClean = true;
                if (groupBy != null) {
                    // 包含groupBy 不去除orderBy
                    canClean = false;
                }
                if (canClean) {
                    for (OrderByElement order : orderBy) {
                        // order by 里带参数,不去除order by
                        Expression expression = order.getExpression();
                        if (!(expression instanceof Column) && expression.toString().contains(StringPool.QUESTION_MARK)) {
                            canClean = false;
                            break;
                        }
                    }
                }
                if (canClean) {
                    plainSelect.setOrderByElements(null);
                }
            }
            //#95 Github, selectItems contains #{} ${}, which will be translated to ?, and it may be in a function: power(#{myInt},2)
            for (SelectItem item : plainSelect.getSelectItems()) {
                if (item.toString().contains(StringPool.QUESTION_MARK)) {
                    return lowLevelCountSql(select.toString());
                }
            }
            // 包含 distinct、groupBy不优化
            if (distinct != null || null != groupBy) {
                return lowLevelCountSql(select.toString());
            }
            // 包含 join 连表,进行判断是否移除 join 连表
            if (optimizeJoin && page.optimizeJoinOfCountSql()) {
                List<Join> joins = plainSelect.getJoins();
                if (CollectionUtils.isNotEmpty(joins)) {
                    boolean canRemoveJoin = true;
                    String whereS = Optional.ofNullable(plainSelect.getWhere()).map(Expression::toString).orElse(StringPool.EMPTY);
                    // 不区分大小写
                    whereS = whereS.toLowerCase();
                    for (Join join : joins) {
                        if (!join.isLeft()) {
                            canRemoveJoin = false;
                            break;
                        }
                        FromItem rightItem = join.getRightItem();
                        String str = "";
                        if (rightItem instanceof Table) {
                            Table table = (Table) rightItem;
                            str = Optional.ofNullable(table.getAlias()).map(Alias::getName).orElse(table.getName()) + StringPool.DOT;
                        } else if (rightItem instanceof SubSelect) {
                            SubSelect subSelect = (SubSelect) rightItem;
                            /* 如果 left join 是子查询，并且子查询里包含 ?(代表有入参) 或者 where 条件里包含使用 join 的表的字段作条件,就不移除 join */
                            if (subSelect.toString().contains(StringPool.QUESTION_MARK)) {
                                canRemoveJoin = false;
                                break;
                            }
                            str = subSelect.getAlias().getName() + StringPool.DOT;
                        }
                        // 不区分大小写
                        str = str.toLowerCase();

                        if (whereS.contains(str)) {
                            /* 如果 where 条件里包含使用 join 的表的字段作条件,就不移除 join */
                            canRemoveJoin = false;
                            break;
                        }

                        for (Expression expression : join.getOnExpressions()) {
                            if (expression.toString().contains(StringPool.QUESTION_MARK)) {
                                /* 如果 join 里包含 ?(代表有入参) 就不移除 join */
                                canRemoveJoin = false;
                                break;
                            }
                        }
                    }

                    if (canRemoveJoin) {
                        plainSelect.setJoins(null);
                    }
                }
            }
            // 优化 SQL
            plainSelect.setSelectItems(COUNT_SELECT_ITEM);
            return select.toString();
        } catch (JSQLParserException e) {
            // 无法优化使用原 SQL
            logger.warn("optimize this sql to a count sql has exception, sql:\"" + sql + "\", exception:\n" + e.getCause());
        } catch (Exception e) {
            logger.warn("optimize this sql to a count sql has error, sql:\"" + sql + "\", exception:\n" + e);
        }
        return lowLevelCountSql(sql);
    }

相关文档描述

生成 countSql 会在 left join 的表不参与 where 条件的情况下,把 left join 优化掉
所以建议任何带有 left join 的sql,都写标准sql,即给于表一个别名,字段也要 别名.字段

[pagehelper]

CountSqlParser改为接口，允许通过countSqlParser参数替换为自己的实现，支持 #772