詳解分頁組件中查count總記錄優化
- 2020 年 3 月 18 日
- 筆記
1 背景
研究mybatis-plus（以下簡稱MBP），使用其分頁功能時。發現了一個JsqlParserCountOptimize的分頁優化處理類，官方對其未做詳細介紹，網上也未找到分析該類邏輯的隻言片語，這情況咱也不敢用呀，索性深度剖析一下，也方便他人。
2 原理
首先PaginationInterceptor分頁攔截器的原理這裡不累述（mybatis通用分頁封裝的實現原理挺簡單的，也就那麼回事），最終落實到查詢上基本是分為2個sql：查count總記錄數 + 查真實分頁記錄。而此類是用優化來其中的查count這步。這count查詢要怎麼優化？這裡上真實場景幫助大家理解: 假如有2張表user、user_address、user_account分別記錄用戶和用戶地址和用戶賬戶，1個用戶可能有多個地址即1對多關係；1個用戶只能有1個賬戶即1對1關係。
2.1 優化order by
先看下面的sql，放到分頁查詢下
select * from user order by age desc, update_time desc 傳統分頁組件往往是
查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發現問題了嗎？查count時的order by是完全可以去掉的！在複雜查詢、大表、非索引欄位排序等情況下查記錄已經很慢了，查count又要來一次！所以查count顯然希望優化為select count(1) from (select * from user)。
2.1.1 限制
但是也不是所有場景都可以優化的，比如帶group by的查詢
2.1.2 源碼
所以MBP源碼如下實現，沒有group by且有order by的語句，就把order by去掉
// 添加包含groupBy 不去除orderBy if (null == groupBy && CollectionUtils.isNotEmpty(orderBy)) { plainSelect.setOrderByElements(null); sqlInfo.setOrderBy(false); }2.2 優化join場景
在join操作時，也存在優化可能，看下面sql
select u.id,ua.account from user u left join user_account ua on u.id=ua.uid這時候分頁查count時，其實可以去掉left join直查user，因為user與user_account是1對1關係，如下
查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,502.2.1 限制
查count能否去掉join直查首表，還存在諸多限制，如下：
表記錄join後不能放大記錄數
從上面案例可知，如果left join後記錄數對比直查首表的總記錄數會放大，就不能進行這個優化。比如3個用戶每人各記錄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條）此時去掉left join去查count就會得到更少的總記錄數。注意這可能會變成一個坑，MBP無法自動判斷本次分頁查詢是否會進行記錄放大，所以join優化默認是關閉的，如果想開啟需要聲明自定義的JsqlParserCountOptimize bean，並設置optimizeJoin為true，如下
@Bean public PaginationInterceptor paginationInterceptor() { PaginationInterceptor paginationInterceptor = new PaginationInterceptor(); paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true)); return paginationInterceptor; }其實這裡源碼設計有些不合理，因為開了之後就得小心翼翼的審查自己各類left join的分頁程式碼了，如果有放大的話，只能構造Page對象時，設置optimizeCountSql為false（默認true），相當於關閉本次查詢所有count優化，那麼不光是join，包括order by等優化也都不進行了。建議可以改為從Page（或ThreadLocal?）中獲取optimizeJoin，變為每次查詢級別可配的配置，默認關，而經過開發人員確認可join優化的才主動在本次查詢級別設置開啟。
僅限left join
如果是inner join或right join往往都會放大記錄數，所以MBP優化會自動判斷如果多個join里出現任何非left join的，就不進行此優化，比如from a left join b .... right join c... left join d此時會直接不進行優化
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優化源碼大致如下，對應上面的優化和限制
List<Join> joins = plainSelect.getJoins(); // 是否全局開啟了optimizeJoin（這裡建議還可以從Page中按每次查詢設置） 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 優化select count(1)位置
傳統的分頁，往往是在原始查詢sql的外層套select count(1)，比如
select count(1) from (select * from user)而count真實目的是得到記錄數，完全不需要原始查詢里的select *產生額外耗時，所以可以優化為如下語句
select count(1) from user2.3.1 限制
同樣的，有一些場景不能進行count位置優化
select的欄位里包含參數
如果select中包含#{}、${}等待替換的參數，也不能進行此優化，因為後續佔位符替換真實值階段會由於佔位符個數減少導致報錯，比如
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去重的語句，若去除有可能導致count記錄數增大，所以不能進行此優化。比如
select count(1) from (select distinct(uid) from user_address) ua vs select count(1) from user_address ua #記錄數可能增大包含group by
包含group by的語句，由於select中往往會有聚合函數，所以count(1)內置語義變成了聚合函數，不能進行此優化。比如
select count(1) from (select uid,count(1) from user_address group by uid) ua #返回單行單列總記錄數 vs select count(1) from user_address group by uid #返回多行單列聚合count數2.3.2 源碼
MBP中相關源碼如下
//select的欄位里包含參數不優化 for (SelectItem item : plainSelect.getSelectItems()) { if (item.toString().contains(StringPool.QUESTION_MARK)) { return sqlInfo.setSql(SqlParserUtils.getOriginalCountSql(selectStatement.toString())); } } // 包含 distinct、groupBy不優化 if (distinct != null || null != groupBy) { return sqlInfo.setSql(SqlParserUtils.getOriginalCountSql(selectStatement.toString())); } ... // 優化 SQL，COUNT_SELECT_ITEM其實就是select count(1)語句 plainSelect.setSelectItems(COUNT_SELECT_ITEM);3 總結
本文其實是針對通用分頁組件中，對查count記錄數這一步驟的一些優化思路，回顧一下：
- 優化order by
- 優化join語句
- 優化select count(1)位置
- 注意以上優化對應的限制，否則可能導致業務錯誤（特別是join優化，比較隱藏）
其實並不局限於MBP，大家自定義的分頁攔截器也可以嘗試用上，對分頁時的優化還是效果顯著的
「用來記錄生命的演進，故事的迭代。期望做一個給大家帶來幫助和思考的平台」 ——深邃老夏
