1 背景
研究mybatis-plus（以下簡(jiǎn)稱MBP），使用其分頁功能時(shí)鄙煤。發(fā)現(xiàn)了一個(gè)JsqlParserCountOptimize的分頁優(yōu)化處理類，官方對(duì)其未做詳細(xì)介紹，網(wǎng)上也未找到分析該類邏輯的只言片語梯轻，這情況咱也不敢用呀，索性深度剖析一下尽棕，也方便他人喳挑。
2 原理
首先PaginationInterceptor分頁攔截器的原理這里不累述（mybatis通用分頁封裝的實(shí)現(xiàn)原理挺簡(jiǎn)單的，也就那么回事）滔悉，最終落實(shí)到查詢上基本是分為2個(gè)sql：查count總記錄數(shù) + 查真實(shí)分頁記錄伊诵。而此類是用優(yōu)化來其中的查count這步。這count查詢要怎么優(yōu)化回官？這里上真實(shí)場(chǎng)景幫助大家理解: 假如有2張表user曹宴、user_address、user_account分別記錄用戶和用戶地址和用戶賬戶歉提，1個(gè)用戶可能有多個(gè)地址即1對(duì)多關(guān)系笛坦；1個(gè)用戶只能有1個(gè)賬戶即1對(duì)1關(guān)系。
2.1 優(yōu)化order by
先看下面的sql苔巨，放到分頁查詢下
select * from user order by age desc, update_time desc
傳統(tǒng)分頁組件往往是
查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
發(fā)現(xiàn)問題了嗎版扩？查count時(shí)的order by是完全可以去掉的！在復(fù)雜查詢侄泽、大表礁芦、非索引字段排序等情況下查記錄已經(jīng)很慢了，查count又要來一次蔬顾！所以查count顯然希望優(yōu)化為select count(1) from (select * from user)宴偿。
2.1.1 限制
但是也不是所有場(chǎng)景都可以優(yōu)化的，比如帶group by的查詢
2.1.2 源碼
所以MBP源碼如下實(shí)現(xiàn)诀豁，沒有g(shù)roup by且有order by的語句窄刘，就把order by去掉
// 添加包含groupBy 不去除orderBy
if (null == groupBy && CollectionUtils.isNotEmpty(orderBy)) {
plainSelect.setOrderByElements(null);
sqlInfo.setOrderBy(false);
}
2.2 優(yōu)化join場(chǎng)景
在join操作時(shí)，也存在優(yōu)化可能舷胜，看下面sql
select u.id,ua.account from user u left join user_account ua on u.id=ua.uid
這時(shí)候分頁查count時(shí)娩践，其實(shí)可以去掉left join直查user活翩，因?yàn)閡ser與user_account是1對(duì)1關(guān)系，如下
查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后不能放大記錄數(shù)
從上面案例可知，如果left join后記錄數(shù)對(duì)比直查首表的總記錄數(shù)會(huì)放大吨岭，就不能進(jìn)行這個(gè)優(yōu)化拉宗。比如3個(gè)用戶每人各記錄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條）
此時(shí)去掉left join去查count就會(huì)得到更少的總記錄數(shù)。注意這可能會(huì)變成一個(gè)坑辣辫，MBP無法自動(dòng)判斷本次分頁查詢是否會(huì)進(jìn)行記錄放大旦事，所以join優(yōu)化默認(rèn)是關(guān)閉的，如果想開啟需要聲明自定義的JsqlParserCountOptimize bean急灭，并設(shè)置optimizeJoin為true姐浮，如下
@Bean
public PaginationInterceptor paginationInterceptor() {
PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
return paginationInterceptor;
}
其實(shí)這里源碼設(shè)計(jì)有些不合理，因?yàn)殚_了之后就得小心翼翼的審查自己各類left join的分頁代碼了葬馋，如果有放大的話卖鲤，只能構(gòu)造Page對(duì)象時(shí)，設(shè)置optimizeCountSql為false（默認(rèn)true）畴嘶，相當(dāng)于關(guān)閉本次查詢所有count優(yōu)化蛋逾，那么不光是join，包括order by等優(yōu)化也都不進(jìn)行了掠廓。建議可以改為從Page（或ThreadLocal?）中獲取optimizeJoin换怖，變?yōu)槊看尾樵兗?jí)別可配的配置甩恼，默認(rèn)關(guān)蟀瞧，而經(jīng)過開發(fā)人員確認(rèn)可join優(yōu)化的才主動(dòng)在本次查詢級(jí)別設(shè)置開啟。
僅限left join
如果是inner join或right join往往都會(huì)放大記錄數(shù)条摸，所以MBP優(yōu)化會(huì)自動(dòng)判斷如果多個(gè)join里出現(xiàn)任何非left join的悦污，就不進(jìn)行此優(yōu)化，比如from a left join b .... right join c... left join d此時(shí)會(huì)直接不進(jìn)行優(yōu)化
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優(yōu)化源碼大致如下钉蒲，對(duì)應(yīng)上面的優(yōu)化和限制
List<Join> joins = plainSelect.getJoins();
// 是否全局開啟了optimizeJoin（這里建議還可以從Page中按每次查詢?cè)O(shè)置）
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 優(yōu)化select count(1)位置
傳統(tǒng)的分頁切端，往往是在原始查詢sql的外層套select count(1)，比如
select count(1) from (select * from user)
而count真實(shí)目的是得到記錄數(shù)顷啼，完全不需要原始查詢里的select *產(chǎn)生額外耗時(shí)踏枣，所以可以優(yōu)化為如下語句
select count(1) from user
2.3.1 限制
同樣的，有一些場(chǎng)景不能進(jìn)行count位置優(yōu)化
select的字段里包含參數(shù)
如果select中包含#{}钙蒙、${}等待替換的參數(shù)茵瀑，也不能進(jìn)行此優(yōu)化，因?yàn)楹罄m(xù)占位符替換真實(shí)值階段會(huì)由于占位符個(gè)數(shù)減少導(dǎo)致報(bào)錯(cuò)躬厌，比如
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去重的語句马昨，若去除有可能導(dǎo)致count記錄數(shù)增大，所以不能進(jìn)行此優(yōu)化。比如
select count(1) from (select distinct(uid) from user_address) ua
vs
select count(1) from user_address ua #記錄數(shù)可能增大
包含group by
包含group by的語句鸿捧，由于select中往往會(huì)有聚合函數(shù)屹篓，所以count(1)內(nèi)置語義變成了聚合函數(shù)，不能進(jìn)行此優(yōu)化匙奴。比如
select count(1) from (select uid,count(1) from user_address group by uid) ua #返回單行單列總記錄數(shù)
vs
select count(1) from user_address group by uid #返回多行單列聚合count數(shù)
2.3.2 源碼
MBP中相關(guān)源碼如下
//select的字段里包含參數(shù)不優(yōu)化
for (SelectItem item : plainSelect.getSelectItems()) {
if (item.toString().contains(StringPool.QUESTION_MARK)) {
return sqlInfo.setSql(SqlParserUtils.getOriginalCountSql(selectStatement.toString()));
}
}
// 包含 distinct堆巧、groupBy不優(yōu)化
if (distinct != null || null != groupBy) {
return sqlInfo.setSql(SqlParserUtils.getOriginalCountSql(selectStatement.toString()));
}
...
// 優(yōu)化 SQL，COUNT_SELECT_ITEM其實(shí)就是select count(1)語句
plainSelect.setSelectItems(COUNT_SELECT_ITEM);
3 總結(jié)
本文其實(shí)是針對(duì)通用分頁組件中泼菌，對(duì)查count記錄數(shù)這一步驟的一些優(yōu)化思路恳邀，回顧一下：
- 優(yōu)化order by
- 優(yōu)化join語句
- 優(yōu)化select count(1)位置
- 注意以上優(yōu)化對(duì)應(yīng)的限制，否則可能導(dǎo)致業(yè)務(wù)錯(cuò)誤（特別是join優(yōu)化灶轰，比較隱藏）
其實(shí)并不局限于MBP谣沸，大家自定義的分頁攔截器也可以嘗試用上，對(duì)分頁時(shí)的優(yōu)化還是效果顯著的
用來記錄生命的演進(jìn)笋颤，故事的迭代乳附。期望做一個(gè)給大家?guī)韼椭退伎嫉钠脚_(tái) --深邃老夏
