最近做的一个需求需要写复杂的 SQL
,且需要分页,我是非常懒的人,因为项目中使用了 mybatis-plus
,因此分页想着使用 mybatis-plus
的分页插件自动完成。但是测试时发现分页性能下降, sql
中的子查询并没有去掉,只是在原有 sql
的基础上包装了一层 select count(*)
。
我在前面一篇介绍 mybatis-plus
的文章中,就分析了它提供的分页插件的源码,并推荐大家在一般的分页情况下去使用这个分页插件。因为它会优化 sql
,优化后的查询总数的 sql
并不比自己写的查询总数的 sql
性能差。
但今天我调试发现并非如此,很是吃惊,然后我就想着不行就自己优化 sql
呗。 mybatis-plus
的分页插件 PaginationInterceptor
,支持自己写 sql
优化器,可通过自定义 sql
优化器提供分页插件的性能。
PaginationInterceptor interceptor = new PaginationInterceptor(); interceptor.setSqlParser(自定义的sql优化器); 复制代码
在创建分页插件时,如果不传 sql
优化器,则会使用 mybatis-plus
提供的默认优化器 JsqlParserCountOptimize
。而它提供的默认优化器正是使用 JsqlParser
这个开源的 sql
解析工具包实现的。
本篇其实也重点强调理解框架源码的重要性,只有对源码有足够的了解,遇到问题才能迎刃而解。本篇将介绍是什么原因导致的 mybatis-plus
分页插件性能下降,以及如何通过使用 JsqlParser
这个开源的 sql
解析工具包与 mybatis-plus
提供的自定义 sql
优化器功能,自己实现高性能的分页插件。
其实也在是在自己实现优化器的过程中,才发现在 SQL
解析失败的情况下,分页插件不会优化 SQL
,而是直接在原 sql
基础上直接包装一层 select count(*)
,导致性能下降。
如果不是因为刚接触 mybatis-plus
时,好奇去看了下它提供的分页插件的源码,今天估计就是自己实现分页查询了。
public SqlInfo optimizeSql(MetaObject metaObject, String sql) { SqlInfo sqlInfo = SqlInfo.newInstance(); try { // 通过优化器优化原sql ....... } catch (Throwable var11) { // SqlUtils.getOriginalCountSql(sql) 这句是给sql包装一层查询总数 sqlInfo.setSql(SqlUtils.getOriginalCountSql(sql)); return sqlInfo; } } 复制代码
这是 JsqlParserCountOptimize
的源码。在解析 sql
出错时,不会报错,而是直接在原 SQL
基础上直接包装一层 select count(*)
。
出现使用 JsqlParser
解析 sql
失败的情况,就需要去检查自己写的 sql
是否有问题,首先是排除 sql
中字符串是否使用了双引号。如
select ifnull(NAME,"") as name from user 复制代码
再检查 sql
是否使用了数据库提供的特殊函数,这种情况下 JsqlParser
也会解析失败,如下面这句 sql
,可能是因为使用了 IF
函数,导致 JsqlParser
解析 sql
失败。
concat(ifnull(a.NAME,''),IF(a.NAME is null,'','>'), ifnull(b.NAME,''),IF(b.NAME is null,'','>'), ifnull(c.NAME,'')) as name 复制代码
JsqlParser
解析 sql
失败时,会在异常中提示 sql
哪个地方解析出错,所以很容易找到原因。在找到原因后,我优化了下 sql
。
concat( (case when a.`NAME` is null then '' else concat(a.`NAME`,'>') end), (case when b.`NAME` is null then '' else concat(b.`NAME`,'>') end), (case when c.`NAME` is null then '' else c.`NAME` end) ) as name 复制代码
修改之后 mybatis-plus
的分页插件便能正常自动帮优化 sql
,也就不需要自己写优化器。
下面是教大家如果自己去实现一个简单的优化器,自己优化查询总数的 sql
。就是去掉 sql
中的子查询。虽然写出来了,但我并没有使用,既然问题已经解决,就不使用了,怕会导致项目中的某些分页查询异常。虽然用不上,但学习是快乐的,说不到以后会用到这个知识点。
@Bean @Order(10) public PaginationInterceptor paginationInterceptor() { PaginationInterceptor interceptor = new PaginationInterceptor(); interceptor.setSqlParser((metaObject, s) -> { try { Statement jSqlParser = CCJSqlParserUtil.parse(s); jSqlParser.accept(new StatementVisitorAdapter() { @Override public void visit(Select select) { select.getSelectBody().accept(new SelectVisitorAdapter() { @Override public void visit(PlainSelect plainSelect) { if (!CollectionUtils.isEmpty(plainSelect.getSelectItems())) { // 遍历select item // 如: a.ID, a.Name, .... // 去掉嵌套子查询 for (Iterator<SelectItem> iterator = plainSelect.getSelectItems().iterator(); iterator.hasNext(); ) { SelectItem item = iterator.next(); boolean[] flag = new boolean[]{false}; // 判断是否存在子查询 item.accept(new SelectItemVisitorAdapter() { @Override public void visit(SelectExpressionItem item) { item.getExpression().accept(new ExpressionVisitorAdapter() { @Override public void visit(SubSelect subSelect) { flag[0] = true; } }); } }); // 移除嵌套子查询 if (flag[0]) { iterator.remove(); } } } } }); } }); SqlInfo sqlInfo = SqlInfo.newInstance(); sqlInfo.setSql(SqlUtils.getOriginalCountSql(jSqlParser.toString())); return sqlInfo; } catch (JSQLParserException e) { SqlInfo sqlInfo = SqlInfo.newInstance(); sqlInfo.setSql(SqlUtils.getOriginalCountSql(s)); return sqlInfo; } }); return interceptor; } 复制代码
在解析 sql
异常时,不能抛出异常,而是跳过优化,直接使用原 sql
,毕竟业务功能第一,不能影响系统的正常运行,这也是 mybatis-plus
的分页插件性能会下降的原因。
jSqlParser
这个工具包使用了访问者模式让我们去修改 sql
, CCJSqlParserUtil.parse(s)
解析 sql
,之后就可以通过 accept
去访问 sql
的每个部分,因为我想去掉 sql
中 select
部分嵌套的子查询,因此第一步就是访问 select
部分。
jSqlParser.accept(new StatementVisitorAdapter() { @Override public void visit(Select select) { }); 复制代码
拿到 select
部分之后,可以继续 accept
去遍历每一个选项,查看是否存在子查询情况,如果存在则将这个选项移除掉。如
select a.id, (select b.`NAME` from b where b.`ID`=a.`B_ID`) as name from a 复制代码
去掉子查询后就是
select id from a 复制代码
拿优化后的 sql
再包装一层 select count(*)
就能自己实现一个简单的高性能分页插件。
比如
select a.`ID`,a.`NAME`, (select b.`NAME` from b where b.`ID`=a.`B_ID`) as bname from a where ..... 复制代码
使用自己写的优化器优化后的查询总数的 sql
select count(*) from ( select a.`ID`,a.`NAME` from a where ..... ) as total; 复制代码
而使用 mybatis-plus
提供的优化器优化后的查询总数的 sql
是
select count(1) from a where .....; 复制代码
关于 jSqlParser
这个工具包,实在不懂怎么去介绍,感兴趣可以自己去试错,去摸索。先从 StatementVisitor
这个访问器入手,在每个 visit
方式中下个断点,看下每个 visit
方法传递的参数都是 sql
的哪个部分,比如 select
部分,再继续看 SelectVisitor
这个访问器的所有 visit
方法...。这种方法虽然有点蠢,不过好过看英文的API文档。