云计算百科
云计算领域专业知识百科平台

MyBatis-Plus 分页的 count 查询被降级了:那行 optimize this sql,不是让你关日志

/* MD / 富文本中的 .toc(含博客园搬家等嵌套结构);.toc-box 在侧栏,不受影响 */
#content_views .toc,
/* 编辑器常在目录前后插入空 p(:empty 仍占 20px),一并去掉避免顶空隙 */
#content_views.markdown_views > p:empty:has(+ .toc),
#content_views.markdown_views > .toc + p:empty,
/* 富文本旧版目录标记 */
#content_views.htmledit_views #main-toc,
#content_views.htmledit_views #hr-toc,
#content_views.htmledit_views p[id*=\”-toc\”] {
display: none !important;
}
/* 目录去掉后,紧跟的首个标题不再多出一块上边距 */
#content_views.markdown_views > .toc + h1,
#content_views.markdown_views > .toc + h2,
#content_views.markdown_views > .toc + h3,
#content_views.markdown_views > .toc + h4,
#content_views.markdown_views > .toc + p:empty + h1,
#content_views.markdown_views > .toc + p:empty + h2,
#content_views.markdown_views > .toc + p:empty + h3,
#content_views.markdown_views > .toc + p:empty + h4 {
margin-top: 0 !important;
}

分页接口突然慢下来,日志里却没有一条明显的慢 SQL,这种情况挺容易让人先去怀疑数据库或者连接池。

我这次遇到的现象是:日志里出现一行 optimize this sql to a count sql has exception,后面跟着一条很长的 SELECT,还有 JSqlParser 的 ParseException。分页结果是对的,总数也对,第一反应确实是想把这条 warning 关掉。

但后来有个列表接口慢到接近 3 秒。我翻了 MyBatis 的 Preparing 日志,发现慢的不是带 LIMIT 的分页 SQL,而是它前面那条 count。

我在本机搭了一张 200 万行的订单表和一张 400 万行的明细表,把几种常见写法分别跑了一遍。结果有点出乎意料:有 warning 的那种写法,count 大约 340ms;另一种没有任何 warning 的写法,count 却跑了 2.1 秒。

沿着这个问题深入探究了下,MyBatis-Plus 什么情况下会改写 count,什么情况下会直接降级,以及遇到以后可以怎么处理。

一、分页时,MyBatis-Plus 会先处理 count

使用 MyBatis-Plus 分页插件时,如果开启了总数查询,一次分页请求通常会执行两条 SQL:先查总数,再查当前页数据。

日志里一般能看到两条 Preparing:第一条是 count,第二条才是带 LIMIT 的分页查询。

==> Preparing: SELECT COUNT(*) FROM (SELECT id, user_id, biz_code … ) TOTAL
==> Parameters: 1(Integer), biz01(String)
==> Preparing: SELECT id, user_id, biz_code … LIMIT ?

这里有个容易忽略的地方,第一条 count 往往不是 mapper 里手写的 SQL,而是 MyBatis-Plus 分页插件在运行时生成的。

在 MyBatis-Plus 3.5.16 中,核心逻辑在 PaginationInnerInterceptor#autoCountSql。它会尝试解析原始 SELECT,然后根据 SQL 结构改写 count:例如把查询列替换成 COUNT(*),去掉不影响总数的 ORDER BY,再检查 DISTINCT、GROUP BY、JOIN 等情况。

可以先把它理解成两条路:

  • 能安全改写:生成更精简的 count SQL。
  • 不能改写或解析失败:退回到包装原 SQL 的方式。

关键逻辑大致如下,省略了中间的 SQL 结构判断:

try {
Select select = (Select) JsqlParserGlobal.parse(sql);

// 根据 SQL 结构判断是否能安全优化
// 省略 DISTINCT、GROUP BY、JOIN、ORDER BY 等处理
plainSelect.setSelectItems(COUNT_SELECT_ITEM);
return select.toString();
} catch (JSQLParserException e) {
logger.warn("optimize this sql to a count sql has exception, sql:{}", sql);
} catch (Exception e) {
logger.warn("optimize this sql to a count sql has error, sql:{}", sql);
}

return lowLevelCountSql(sql);

降级方法本身很简单:

protected String lowLevelCountSql(String originalSql) {
return SqlParserUtils.getOriginalCountSql(originalSql);
}

public static String getOriginalCountSql(String originalSql) {
return String.format(
"SELECT COUNT(*) FROM (%s) TOTAL", originalSql);
}

也就是说,原本想生成一条更精简的 count,结果没有改写成功,就把原查询包在子查询外面,再统计子查询的行数。

代价差别就在这里,改写成功时,count 只需要扫索引、数行数。改写失败时,数据库要先把原查询的结果跑出来、物化成一个派生表,再数这张表有多少行。原查询如果带了 ORDER BY、很宽的 SELECT 列表或者 JOIN,这些代价就全部落到 count 上。

两个 catch 都不会让分页请求直接失败,打印 warning 后继续走降级。所以看到这行日志,分页结果不一定有问题,但 count 的执行方式可能已经变了。

还有一个容易误用的开关,page.setOptimizeCountSql(false)。这个选项默认是 true;手动关闭后,会直接使用低级 count 包装逻辑,不需要先解析,自然也不会出现解析失败的 warning。它不是"让日志干净"的开关,关闭后反而可能让每次 count 都走子查询。

接口变慢时,先把 count 和 page 两条 SQL 分开看。不要把它们的耗时混在一起。

二、一个 BINARY,让 count 从 7ms 变成 340ms

先看一组最直观的对比。两条 SQL 只有一个地方不同。

普通等值查询:

WHERE status = #{status}
AND biz_code = #{biz}

给列加上 BINARY,加BINARY 它通常出现在这样的场景:biz_code 这类编码由运营后台填写,或者从第三方系统同步,值不是系统自己生成的,大小写并不统一,而列建表时用了大小写不敏感的排序规则,于是 WHERE biz_code = ‘biz01’ 会把 ‘BIZ01’ 也查出来。改列的排序规则会影响整张表,写 COLLATE utf8mb4_bin 又啰嗦,最省事的办法就是在 SQL 里加一个 BINARY,强制按字节比较。代价是这一列在查询里不再走普通索引:

WHERE status = #{status}
AND BINARY biz_code = #{biz}

第一条 SQL 的 count 可以被改写成:

SELECT COUNT(*) AS total
FROM orders
WHERE status = ?
AND biz_code = ?

第二条没有改写成功,实际生成的是外层包装:

SELECT COUNT(*)
FROM (
SELECT id, user_id, biz_code, status, amount, created_at
FROM orders
WHERE status = ?
AND BINARY biz_code = ?
ORDER BY created_at DESC
) TOTAL

表上有三个索引:(biz_code, status)、(user_id, status, created_at) 和 (created_at),没有专门为 BINARY 那条查询建立的 (status, biz_code) 索引。

下面提到的耗时都是 MySQL 服务端记录的语句执行时间,不包含 HikariCP 获取连接的等待,也不是完整的 JDBC 往返时间。每组跑 5 次,取中位数。

普通等值查询的两个条件都是等值条件,(biz_code, status) 这两列都能用上;where 里两个条件的先后顺序不影响能不能用上索引。它的 count 中位数约 7ms。

加上 BINARY 之后,比较表达式变了。原索引按列本身的排序规则组织,不能再按原来的普通等值条件直接定位。本次执行计划退回到 Table scan on orders,扫描约 200 万行,过滤后剩下 33,348 行,count 中位数约 340ms。

这里的 EXPLAIN 估算行数和实际行数有差别:普通等值查询那条估算 65,624 行,实际命中 33,348 行。数据刚灌完、统计信息还没更新时,估算可能不准;要看实际扫描量,应该结合 EXPLAIN ANALYZE 的 actual rows。

这组差异其实有两个原因,最好分开理解:

  • MyBatis-Plus 没有成功改写 count。 JSqlParser 5.2 解析这条 SQL 时,在 BINARY biz_code 附近报错,于是打印 warning 并降级。
  • MySQL 没法按原索引条件直接定位。 BINARY 改变了比较方式,执行计划从索引覆盖扫描退回到全表扫描。
  • warning 里能看到具体 token:

    Encountered unexpected token: "biz_code" <S_IDENTIFIER>

    所以这条 warning 还是有用的:它能告诉你哪条 SQL 没有被解析成功。但让这次 count 变慢的,是降级后的 SQL,以及随之改变的执行计划。

    如果想单独确认 JSqlParser 能不能解析某条 SQL,不需要启动 Spring,写个简单的 main 就可以:

    public static void main(String[] args) {
    String sql = "SELECT id FROM orders "
    + "WHERE BINARY biz_code = 'biz01'";

    try {
    JsqlParserGlobal.parse(sql);
    System.out.println("parsed");
    } catch (Throwable e) {
    System.out.println(e.getClass().getName());
    System.out.println(e.getMessage());
    }
    }

    我也把十几种常见写法跑了一遍,包括 ->>、GROUP_CONCAT … SEPARATOR、MATCH … AGAINST、FORCE INDEX、GROUP BY … WITH ROLLUP、窗口函数、DATE_SUB 和优化器 hint。在这组版本和测试 SQL 里,只有 BINARY 触发了异常。不是 MySQL 方言一定会炸,是这一种写法踩到了解析器的空位。

    三、没有 warning,count 也可能已经降级

    前面 BINARY 的情况比较容易发现:解析失败会打印 warning。

    但还有一些 SQL,MyBatis-Plus 会根据 SQL 结构主动放弃改写,直接走低级 count。它认为直接替换成 COUNT(*) 可能改变原查询的统计结果,所以不会打印解析异常的 warning。

    常见情况包括:

    • DISTINCT:去重会改变结果行数。
    • GROUP BY:count 需要统计分组后的行数,不是原表记录数。
    • UNION / UNION ALL:需要考虑合并后的结果集。
    • select 列表中存在参数占位符:插件无法简单确认替换后是否仍然保持原来的行数语义。

    看两个例子。

    GROUP BY:count 数的是分组数。

    SELECT user_id, COUNT(*) AS cnt, SUM(amount) AS total
    FROM orders
    WHERE status = #{status}
    GROUP BY user_id
    ORDER BY total DESC

    status = 1 命中约 33 万行,按 user_id 分成 11,716 组。分页总数应该是分组后的 11,716,而不是原始的 33 万行。数据库需要完成分组,再统计分组结果,所以这条 count 约 2.1 秒。

    UNION ALL:count 数的是合并后的行数。

    SELECT id, amount FROM orders WHERE status = 1
    UNION ALL
    SELECT id, amount FROM orders WHERE status = 2

    本次两边各有约 33 万行,合并后约 66 万行。count 需要统计合并结果,实测约 850ms。

    这里要注意:GROUP BY 那条的 2.1 秒和普通等值查询的 7ms 不是同一种数据量级。普通等值查询同时使用 status 和 biz_code,只命中 33,348 行;GROUP BY 那条只有 status 条件,参与分组的有约 33 万行。不能把这两个耗时简单理解成 SQL 改写本身带来的差异。

    所以排查时要区分两种情况:

    • 解析失败导致的降级:通常会打印 optimize this sql … has exception。
    • 结构判断后主动降级:可能没有 warning,但最终同样会生成外层 count 子查询。

    如果分页慢、日志里又没有 warning,可以先检查 mapper 中有没有 DISTINCT、GROUP BY、UNION,以及 select 列表里的参数。也可以从 MyBatis 的 Preparing 日志中搜索 COUNT(*) FROM (,看最终执行的 count 是不是外层包装形式。

    四、LEFT JOIN 有时能从 count 里移除,INNER JOIN 通常会保留

    再看一组 JOIN 的对比。这里主要想说明:分页 SQL 里有 JOIN,不代表 count 一定会按原样执行,但 JOIN 被保留时,count 也要把明细表一起算进来,耗时明显增加。

    — LEFT JOIN:本次测试中 count 优化时移除了明细表
    SELECT o.id, o.amount, i.product_id
    FROM orders o
    LEFT JOIN order_items i ON o.id = i.order_id
    WHERE o.status = ?

    — INNER JOIN:本次测试中 count 保留了明细表
    SELECT o.id, o.amount, i.qty
    FROM orders o
    INNER JOIN order_items i ON o.id = i.order_id
    WHERE o.status = ?

    left join 那组的原始 SQL 里,右表字段没出现在 where,on 里也没有参数,count 改写时整段 join 就被摘掉了,耗时 63ms。这个数字还是比普通等值查询的 7ms 高一截,但原因跟 join 没关系,join 都已经删了,剩下的 SQL 只有 status 一个条件,没用上 (biz_code, status) 联合索引的最左列,只能按 status 去扫。

    inner join 那组反过来,明细表关联原封不动留在 count 里,要把 400 万行明细真的过一遍,645ms。

    插件判断的依据其实很简单:这条 join 是不是 left ,右表的别名有没有在 where 里出现过,它是拿 别名. 去 where 里做字符串匹配,所以 where 里不写别名的条件它认不出来,on 里有没有问号占位符,带问号说明这段 SQL 吃参数,删掉 join 参数就对不上了。

    但这些都只是语法层面的判断。插件不连数据库,不知道 orders 和 order_items 是一对多还是多对一。我这两张表正是一对多,如果哪天列表页写成 left join 明细、where 只筛主表、on 里也没参数,它照样会把 join 删掉,count 就从明细行数变成订单数。不报错,也不 warning,你只会发现分页的总数比实际少了一截。

    所以这类列表我更愿意拆开查:先按条件分页 orders,拿到当前页那几十个 id,再用 in 把明细一次捞回来。总数查询只面对主表,不用为了展示几个字段把整张明细表拖进 count。

    想知道它到底删没删,也不用猜,把 SQL 日志打开,看它实际生成的那条 count 就行。

    五、四种修法,分别解决不同的问题

    前面看了几种 count 变慢的情况,下面是本次实验里尝试的四种处理方式。它们不是四个可以随便互换的开关,适用条件不一样。

    方案一:把 BINARY 改成 COLLATE

    原来:

    "… AND BINARY biz_code = #{biz} …"

    调整后:

    "… AND biz_code = #{biz} COLLATE utf8mb4_bin …"

    MySQL 可以继续使用 idx_biz_status 覆盖索引,JSqlParser 也能解析这条 SQL,count 从约 340ms 降到了 9ms。

    这个方案需要注意比较语义。COLLATE utf8mb4_bin 是二进制排序规则比较,大小写敏感,但它和 BINARY 并不是所有细节都完全相同。

    更彻底的方式是调整列本身的排序规则,让 SQL 不必每次写 BINARY 或 COLLATE。但这会影响整张表其他查询的字符串比较语义,改之前要确认业务是否依赖原来的大小写不敏感行为。

    方案二:为这条查询单独写 count

    如果列表 SQL 本身有复杂分组,而业务又能明确写出等价的统计 SQL,可以使用 MyBatis-Plus 的 countId 指定自定义 count。

    Page<Map<String, Object>> page = new Page<>(1, 20);
    page.setCountId("selectGroupCount");

    对应的 mapper XML:

    <select id="selectGroupCount" resultType="java.lang.Long">
    SELECT COUNT(DISTINCT user_id)
    FROM orders
    WHERE status = #{status}
    </select>

    GROUP BY 查询的 count 从约 2.1 秒降到了 50ms。这里能用 COUNT(DISTINCT user_id),是因为原查询按 user_id 分组,总数就是不同 user_id 的数量。

    这个方案不能照搬到所有分组查询。例如按天分组展示订单,但页面总数要显示订单条数,那么 COUNT(DISTINCT user_id) 就完全不是同一个统计口径。改之前先把原查询结果的行数和自定义 count 对一遍,确保语义相同。

    还有个小细节:countId 指向的语句要放在同一个 mapper namespace 下,并且它和分页方法共用参数对象。分页方法接收 status,自定义 count 里也要使用对应的 #{status},参数名不一致会直接出现绑定异常。

    另外,count 从 2.1 秒降到 50ms,不代表整个分页接口就只剩 50ms。本次分页查询本身仍然需要约 0.6~0.8 秒,自定义 count 只解决了 count 这一半。

    方案三:补一个联合索引

    ALTER TABLE orders
    ADD INDEX idx_status_biz (status, biz_code);

    BINARY 查询的 count 从约 340ms 降到了 108ms,有改善,但没有回到 10ms 以内。

    从执行计划看,原来的全表扫描变成了通过 status 做 Index lookup,估算候选行从 200 万缩小到约 65.7 万;但 BINARY biz_code 仍然是过滤条件,不能像普通等值条件那样直接利用索引定位,实际过滤后剩 33,348 行。

    所以补索引有帮助,但没有消除 BINARY 带来的问题。索引也不是免费的:写入时需要额外维护索引,200 万行的表增加索引还要安排合适的变更窗口。

    方案四:如果页面不需要总数,就不要查 count

    Page<Map<String, Object>> page = new Page<>(1, 20);
    page.setSearchCount(false);

    关闭 count 后,原来约 340ms 的 count 开销就没有了,分页数据查询约 5ms。

    这适合无限滚动、只需要"加载更多"的列表,或者总数由另一个统计接口提供的场景。如果产品页面需要展示"共 N 条",就不能简单关闭 count。

    四种方案放在一起看一下

    方案对应写法count 耗时主要代价或限制
    原始普通等值查询 对照组 7ms —
    换 COLLATE BINARY 查询(340ms) 9ms 比较语义需要核对
    自定义 count GROUP BY 查询(2.1s) 50ms 必须保证统计口径等价
    补索引 BINARY 查询(340ms) 108ms 仍需扫描和过滤较多候选行
    关闭 count BINARY 查询(340ms) 不执行 count 页面不再直接获得总数

    这几种方案里,如果业务允许修改 SQL,优先考虑把 SQL 写法调整到既能被解析器处理、又能正常使用索引的形式。自定义 count 适合统计口径明确的复杂查询;补索引可以缓解扫描范围,但不一定能解决表达式导致的索引使用问题;只有产品不需要总数时,才考虑关闭 count。

    六、实际排查时,我会先看这几处

    如果项目里也出现了 optimize this sql to a count sql has exception,可以按下面的顺序查:

  • 先找 count SQL。 看 warning 后面打印的是哪条 SQL,再从 MyBatis Preparing 日志里找到对应的 count 语句。
  • 看最终有没有降级。 如果执行 SQL 是 SELECT COUNT(*) FROM (…) TOTAL,说明使用了外层包装形式。
  • 看为什么没改写。 有 warning 时,先定位 JSqlParser 报错的 token;没有 warning,则检查 DISTINCT、GROUP BY、UNION 等结构分支。
  • 看数据库实际怎么执行。 对 count SQL 单独执行 EXPLAIN ANALYZE,确认是索引扫描、全表扫描、分组还是 JOIN 带来了主要开销。
  • 最后再选修法。 能改 SQL 就先改 SQL;统计口径能单独表达就考虑自定义 count;确认页面不需要总数,再关闭 count。
  • 有个地方值得记住:日志没有 warning,不代表 count 一定优化成功。静默降级的场景,直接看最终执行的 SQL 更可靠。线上不方便逐条翻日志时,可以搜索 COUNT(*) FROM ( 作为线索,再结合 mapper 和执行计划确认。

    七、小结

    分页慢的时候,不要只盯着带 LIMIT 的那条 SQL。先看 count 最终生成了什么,再看执行计划;有 warning 查解析失败的原因,没有 warning 也要留意静默降级。

    如果你也遇到过 MyBatis-Plus 分页 count 特别慢,可以先看一下 mapper 里的 SQL 有没有 BINARY、DISTINCT、GROUP BY 或 UNION。很多时候,问题不在分页插件突然变慢,而是它为了保证统计结果正确,选择了一条更保守、但执行代价更高的 count。

    系列:相关 ai-launch-check 实测:LangChain4j 仓上线前扫描,25 条命中 2 条是它看错|AI 客服系统的审计日志:记什么、存多久、怎么查|技术纵深系列索引

    赞(0)
    未经允许不得转载:网硕互联帮助中心 » MyBatis-Plus 分页的 count 查询被降级了:那行 optimize this sql,不是让你关日志
    分享到: 更多 (0)

    评论 抢沙发

    评论前必须登录!