最近做的一個需求需要寫複雜的SQL，且需要分頁，我是非常懶的人，因為項目中使用了mybatis-plus，因此分頁想著使用mybatis-plus的分頁插件自動完成。但是測試時發現分頁性能下降，sql中的子查詢並沒有去掉，只是在原有sql的基礎上包裝了一層select count(*)。
但今天我調試發現並非如此，很是吃驚，然後我就想著不行就自己優化sql唄。mybatis-plus的分頁插件PaginationInterceptor，支持自己寫sql優化器，可通過自定義sql優化器提供分頁插件的性能。
<code> PaginationInterceptor interceptor = new PaginationInterceptor();
interceptor.setSqlParser(自定義的sql優化器);/<code>
在創建分頁插件時，如果不傳sql優化器，則會使用mybatis-plus提供的默認優化器JsqlParserCountOptimize。而它提供的默認優化器正是使用JsqlParser這個開源的sql解析工具包實現的。
本篇其實也重點強調理解框架源碼的重要性，只有對源碼有足夠的瞭解，遇到問題才能迎刃而解。本篇介紹是什麼原因導致的mybatis-plus分頁插件性能下降，以及如何通過使用JsqlParser這個開源的sql解析工具包與mybatis-plus提供的自定義sql優化器功能，自己實現高性能的分頁插件。
其實也在是在自己實現優化器的過程中，才發現在SQL解析失敗的情況下，分頁插件不會優化SQL，而是直接在原sql基礎上直接包裝一層select count(*)，導致性能下降。
如果不是因為剛接觸mybatis-plus時，好奇去看了下它提供的分頁插件的源碼，今天估計就是自己實現分頁查詢了。
<code>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;
}
}/<code>這是JsqlParserCountOptimize的源碼。在解析sql出錯時，不會報錯，而是直接在原SQL基礎上直接包裝一層select count(*)。
出現使用JsqlParser解析sql失敗的情況，就需要去檢查自己寫的sql是否有問題，首先是排除sql中字符串是否使用了雙引號。如
<code>select ifnull(NAME,"") as name from user/<code>
再檢查sql是否使用了數據庫提供的特殊函數，這種情況下JsqlParser也會解析失敗，如下面這句sql，可能是因為使用了IF函數，導致JsqlParser解析sql失敗。
<code> concat(ifnull(a.NAME,''),IF(a.NAME is null,'','>'),
ifnull(b.NAME,''),IF(b.NAME is null,'','>'),
ifnull(c.NAME,'')) as name/<code>
JsqlParser解析sql失敗時，會在異常中提示sql哪個地方解析出錯，所以很容易找到原因。在找到原因後，我優化了下sql。
<code>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/<code>
修改之後mybatis-plus的分頁插件便能正常自動幫優化sql，也就不需要自己寫優化器。
下面是教大家如果自己去實現一個簡單的優化器，自己優化查詢總數的sql。就是去掉sql中的子查詢。雖然寫出來了，但我並沒有使用，既然問題已經解決，就不使用了，怕會導致項目中的某些分頁查詢異常。雖然用不上，但學習是快樂的，說不到以後會用到這個知識點。
<code> @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;
}/<selectitem>/<code>
在解析sql異常時，不能拋出異常，而是跳過優化，直接使用原sql。畢竟業務功能第一，不能影響系統的正常運行，這也是mybatis-plus的分頁插件性能會下降的原因。
jSqlParser這個工具包使用了訪問者模式讓我們去修改sql，CCJSqlParserUtil.parse(s)解析sql，之後就可以通過accept去訪問sql的每個部分，因為我想去掉sql中select部分嵌套的子查詢，因此第一步就是訪問select部分。
<code>jSqlParser.accept(new StatementVisitorAdapter() {
@Override
public void visit(Select select) {
});/<code>拿到select部分之後，可以繼續accept去遍歷每一個選項，查看是否存在子查詢情況，如果存在則將這個選項移除掉。如
<code>select a.id,
(select b.`NAME` from b where b.`ID`=a.`B_ID`) as name
from a/<code>
去掉子查詢後就是
<code>select id from a/<code>
拿優化後的sql再包裝一層select count(*)就能自己實現一個簡單的高性能分頁插件。
比如
<code>select count(*) from (
select a.`ID`,a.`NAME`,
(select b.`NAME` from b where b.`ID`=a.`B_ID`) as bname
from a
where .....
) as total;/<code>
使用自己寫的優化器優化後的查詢總數的sql
<code>select count(*) from (
select a.`ID`,a.`NAME`
from a
where .....
) as total;/<code>
而使用mybatis-plus提供的優化器優化後的查詢總數的sql是
<code>select count(*) from (
select a.`ID`
from a
where .....
) as total;/<code>
關於jSqlParser這個工具包，實在不懂怎麼去介紹，感興趣可以自己去試錯，去摸索。先從StatementVisitor這個訪問器入手，在每個visit方式中下個斷點，看下每個visit方法傳遞的參數都是sql的哪個部分，比如select部分，再繼續看SelectVisitor這個訪問器的所有visit方法...。這種方法雖然有點蠢，不過好過看英文的API文檔。
閱讀更多 路人甲java 的文章
