大數(shù)據(jù)量下 PageHelper 分頁(yè)查詢(xún)性能問(wèn)題的解決辦法

前因

項(xiàng)目一直使用的是PageHelper實(shí)現(xiàn)分頁(yè)功能，項(xiàng)目前期數(shù)據(jù)量較少一直沒(méi)有什么問(wèn)題。隨著業(yè)務(wù)擴(kuò)增，數(shù)據(jù)庫(kù)擴(kuò)增PageHelper出現(xiàn)了明顯的性能問(wèn)題。

幾十萬(wàn)甚至上百萬(wàn)的單表數(shù)據(jù)查詢(xún)性能緩慢，需要幾秒乃至十幾秒的查詢(xún)時(shí)間。故此特地研究了一下PageHelper源碼，查找PageHelper分頁(yè)的實(shí)現(xiàn)方式。

一段較為簡(jiǎn)單的查詢(xún)，跟隨debug開(kāi)始源碼探尋之旅。

public?ResultContent?select(Integer?id)?{
????????Page?blogPage?=?PageHelper.startPage(1,3).doSelectPage(?()?->?testDao.select(id));
????????List?test?=?(List)blogPage.getResult();
????????return?new?ResultContent(0,?"success",?test);
????}

主要保存由前端傳入的pageNum(頁(yè)數(shù))、pageSize(每頁(yè)顯示數(shù)量)和count(是否進(jìn)行count(0)查詢(xún))信息。

這里是簡(jiǎn)單的創(chuàng)建page并保存當(dāng)前線(xiàn)程的變量副本心里，不做深究。

public?static??Page?startPage(int?pageNum,?int?pageSize)?{
????????return?startPage(pageNum,?pageSize,?DEFAULT_COUNT);
????}
?
????public?static??Page?startPage(int?pageNum,?int?pageSize,?boolean?count)?{
????????return?startPage(pageNum,?pageSize,?count,?(Boolean)null,?(Boolean)null);
????}
?
????public?static??Page?startPage(int?pageNum,?int?pageSize,?String?orderBy)?{
????????Page?page?=?startPage(pageNum,?pageSize);
????????page.setOrderBy(orderBy);
????????return?page;
????}
?
????public?static??Page?startPage(int?pageNum,?int?pageSize,?boolean?count,?Boolean?reasonable,?Boolean?pageSizeZero)?{
????????Page?page?=?new?Page(pageNum,?pageSize,?count);
????????page.setReasonable(reasonable);
????????page.setPageSizeZero(pageSizeZero);
????????Page?oldPage?=?getLocalPage();
????????if(oldPage?!=?null?&&?oldPage.isOrderByOnly())?{
????????????page.setOrderBy(oldPage.getOrderBy());
????????}
?
????????setLocalPage(page);
????????return?page;
????}

開(kāi)始執(zhí)行真正的select語(yǔ)句

public??Page?doSelectPage(ISelect?select)?{
????????select.doSelect();
????????return?this;
????}

進(jìn)入MapperProxy類(lèi)執(zhí)行invoke方法獲取到方法名稱(chēng)及參數(shù)值

public?Object?invoke(Object?proxy,?Method?method,?Object[]?args)?throws?Throwable?{
????if?(Object.class.equals(method.getDeclaringClass()))?{
??????try?{
????????return?method.invoke(this,?args);
??????}?catch?(Throwable?t)?{
????????throw?ExceptionUtil.unwrapThrowable(t);
??????}
????}
????final?MapperMethod?mapperMethod?=?cachedMapperMethod(method);
????return?mapperMethod.execute(sqlSession,?args);
??}

接著是MapperMethod方法執(zhí)行execute語(yǔ)句，判斷是增、刪、改、查。判斷返回值是多個(gè)，進(jìn)入executeForMany方法

public?Object?execute(SqlSession?sqlSession,?Object[]?args)?{
????Object?result;
????if?(SqlCommandType.INSERT?==?command.getType())?{
??????Object?param?=?method.convertArgsToSqlCommandParam(args);
??????result?=?rowCountResult(sqlSession.insert(command.getName(),?param));
????}?else?if?(SqlCommandType.UPDATE?==?command.getType())?{
??????Object?param?=?method.convertArgsToSqlCommandParam(args);
??????result?=?rowCountResult(sqlSession.update(command.getName(),?param));
????}?else?if?(SqlCommandType.DELETE?==?command.getType())?{
??????Object?param?=?method.convertArgsToSqlCommandParam(args);
??????result?=?rowCountResult(sqlSession.delete(command.getName(),?param));
????}?else?if?(SqlCommandType.SELECT?==?command.getType())?{
??????if?(method.returnsVoid()?&&?method.hasResultHandler())?{
????????executeWithResultHandler(sqlSession,?args);
????????result?=?null;
??????}?else?if?(method.returnsMany())?{
????????result?=?executeForMany(sqlSession,?args);
??????}?else?if?(method.returnsMap())?{
????????result?=?executeForMap(sqlSession,?args);
??????}?else?{
????????Object?param?=?method.convertArgsToSqlCommandParam(args);
????????result?=?sqlSession.selectOne(command.getName(),?param);
??????}
????}?else?if?(SqlCommandType.FLUSH?==?command.getType())?{
????????result?=?sqlSession.flushStatements();
????}?else?{
??????throw?new?BindingException("Unknown?execution?method?for:?"?+?command.getName());
????}
????if?(result?==?null?&&?method.getReturnType().isPrimitive()?&&?!method.returnsVoid())?{
??????throw?new?BindingException("Mapper?method?'"?+?command.getName()?
??????????+?"?attempted?to?return?null?from?a?method?with?a?primitive?return?type?("?+?method.getReturnType()?+?").");
????}
????return?result;
??}

這個(gè)方法開(kāi)始調(diào)用SqlSessionTemplate、DefaultSqlSession等類(lèi)獲取到Mapper.xml文件的SQL語(yǔ)句

private??Object?executeForMany(SqlSession?sqlSession,?Object[]?args)?{
????List?result;
????Object?param?=?method.convertArgsToSqlCommandParam(args);
????if?(method.hasRowBounds())?{
??????RowBounds?rowBounds?=?method.extractRowBounds(args);
??????result?=?sqlSession.selectList(command.getName(),?param,?rowBounds);
????}?else?{
??????result?=?sqlSession.selectList(command.getName(),?param);
????}
????//?issue?#510?Collections?&?arrays?support
????if?(!method.getReturnType().isAssignableFrom(result.getClass()))?{
??????if?(method.getReturnType().isArray())?{
????????return?convertToArray(result);
??????}?else?{
????????return?convertToDeclaredCollection(sqlSession.getConfiguration(),?result);
??????}
????}
????return?result;
??}

開(kāi)始進(jìn)入PageHelper的真正實(shí)現(xiàn)，Plugin通過(guò)實(shí)現(xiàn)InvocationHandler進(jìn)行動(dòng)態(tài)代理獲取到相關(guān)信息

public?Object?invoke(Object?proxy,?Method?method,?Object[]?args)?throws?Throwable?{
????try?{
??????Set?methods?=?signatureMap.get(method.getDeclaringClass());
??????if?(methods?!=?null?&&?methods.contains(method))?{
????????return?interceptor.intercept(new?Invocation(target,?method,?args));
??????}
??????return?method.invoke(target,?args);
????}?catch?(Exception?e)?{
??????throw?ExceptionUtil.unwrapThrowable(e);
????}
??}

PageInterceptor 實(shí)現(xiàn)Mybatis的Interceptor 接口，進(jìn)行攔截

public?Object?intercept(Invocation?invocation)?throws?Throwable?{
????????try?{
????????????Object[]?args?=?invocation.getArgs();
????????????MappedStatement?ms?=?(MappedStatement)args[0];
????????????Object?parameter?=?args[1];
????????????RowBounds?rowBounds?=?(RowBounds)args[2];
????????????ResultHandler?resultHandler?=?(ResultHandler)args[3];
????????????Executor?executor?=?(Executor)invocation.getTarget();
????????????CacheKey?cacheKey;
????????????BoundSql?boundSql;
????????????if(args.length?==?4)?{
????????????????boundSql?=?ms.getBoundSql(parameter);
????????????????cacheKey?=?executor.createCacheKey(ms,?parameter,?rowBounds,?boundSql);
????????????}?else?{
????????????????cacheKey?=?(CacheKey)args[4];
????????????????boundSql?=?(BoundSql)args[5];
????????????}
?
????????????this.checkDialectExists();
????????????List?resultList;
????????????if(!this.dialect.skip(ms,?parameter,?rowBounds))?{
????????????????if(this.dialect.beforeCount(ms,?parameter,?rowBounds))?{
????????????????????Long?count?=?this.count(executor,?ms,?parameter,?rowBounds,?resultHandler,?boundSql);
????????????????????if(!this.dialect.afterCount(count.longValue(),?parameter,?rowBounds))?{
????????????????????????Object?var12?=?this.dialect.afterPage(new?ArrayList(),?parameter,?rowBounds);
????????????????????????return?var12;
????????????????????}
????????????????}
?
????????????????resultList?=?ExecutorUtil.pageQuery(this.dialect,?executor,?ms,?parameter,?rowBounds,?resultHandler,?boundSql,?cacheKey);
????????????}?else?{
????????????????resultList?=?executor.query(ms,?parameter,?rowBounds,?resultHandler,?cacheKey,?boundSql);
????????????}
?
????????????Object?var16?=?this.dialect.afterPage(resultList,?parameter,?rowBounds);
????????????return?var16;
????????}?finally?{
????????????this.dialect.afterAll();
????????}
????}

轉(zhuǎn)到ExecutorUtil抽象類(lèi)的pageQuery方法

public?static??List?pageQuery(Dialect?dialect,?Executor?executor,?MappedStatement?ms,?Object?parameter,?RowBounds?rowBounds,?ResultHandler?resultHandler,?BoundSql?boundSql,?CacheKey?cacheKey)?throws?SQLException?{
????????if(!dialect.beforePage(ms,?parameter,?rowBounds))?{
????????????return?executor.query(ms,?parameter,?RowBounds.DEFAULT,?resultHandler,?cacheKey,?boundSql);
????????}?else?{
????????????parameter?=?dialect.processParameterObject(ms,?parameter,?boundSql,?cacheKey);
????????????String?pageSql?=?dialect.getPageSql(ms,?boundSql,?parameter,?rowBounds,?cacheKey);
????????????BoundSql?pageBoundSql?=?new?BoundSql(ms.getConfiguration(),?pageSql,?boundSql.getParameterMappings(),?parameter);
????????????Map?additionalParameters?=?getAdditionalParameter(boundSql);
????????????Iterator?var12?=?additionalParameters.keySet().iterator();
?
????????????while(var12.hasNext())?{
????????????????String?key?=?(String)var12.next();
????????????????pageBoundSql.setAdditionalParameter(key,?additionalParameters.get(key));
????????????}
?
????????????return?executor.query(ms,?parameter,?RowBounds.DEFAULT,?resultHandler,?cacheKey,?pageBoundSql);
????????}
????}

在抽象類(lèi)AbstractHelperDialect的getPageSql獲取到對(duì)應(yīng)的Page對(duì)象

public?String?getPageSql(MappedStatement?ms,?BoundSql?boundSql,?Object?parameterObject,?RowBounds?rowBounds,?CacheKey?pageKey)?{
????????String?sql?=?boundSql.getSql();
????????Page?page?=?this.getLocalPage();
????????String?orderBy?=?page.getOrderBy();
????????if(StringUtil.isNotEmpty(orderBy))?{
????????????pageKey.update(orderBy);
????????????sql?=?OrderByParser.converToOrderBySql(sql,?orderBy);
????????}
?
????????return?page.isOrderByOnly()?sql:this.getPageSql(sql,?page,?pageKey);
????}

進(jìn)入到MySqlDialect類(lèi)的getPageSql方法進(jìn)行SQL封裝，根據(jù)page對(duì)象信息增加Limit。分頁(yè)的信息就是這么拼裝起來(lái)的

public?String?getPageSql(String?sql,?Page?page,?CacheKey?pageKey)?{
????????StringBuilder?sqlBuilder?=?new?StringBuilder(sql.length()?+?14);
????????sqlBuilder.append(sql);
????????if(page.getStartRow()?==?0)?{
????????????sqlBuilder.append("?LIMIT???");
????????}?else?{
????????????sqlBuilder.append("?LIMIT??,???");
????????}
?
????????return?sqlBuilder.toString();
????}

將最后拼裝好的SQL返回給DefaultSqlSession執(zhí)行查詢(xún)并返回

public??List?selectList(String?statement,?Object?parameter,?RowBounds?rowBounds)?{
????try?{
??????MappedStatement?ms?=?configuration.getMappedStatement(statement);
??????return?executor.query(ms,?wrapCollection(parameter),?rowBounds,?Executor.NO_RESULT_HANDLER);
????}?catch?(Exception?e)?{
??????throw?ExceptionFactory.wrapException("Error?querying?database.??Cause:?"?+?e,?e);
????}?finally?{
??????ErrorContext.instance().reset();
????}

至此整個(gè)查詢(xún)過(guò)程完成，原來(lái)PageHelper的分頁(yè)功能是通過(guò)Limit拼接SQL實(shí)現(xiàn)的。查詢(xún)效率低的問(wèn)題也找出來(lái)了，那么應(yīng)該如何解決。

推薦：MySQL：為什么用limit時(shí)，offset很大會(huì)影響性能

首先分析SQL語(yǔ)句，limit在數(shù)據(jù)量少或者頁(yè)數(shù)比較靠前的時(shí)候查詢(xún)效率是比較高的。(單表數(shù)據(jù)量百萬(wàn)進(jìn)行測(cè)試)

select?*?from?user?where?age?=?10?limit?1,10;結(jié)果顯示0.43s

當(dāng)where條件后的結(jié)果集較大并且頁(yè)數(shù)達(dá)到一個(gè)量級(jí)整個(gè)SQL的查詢(xún)效率就十分低下(哪怕where的條件加上了索引也不行)。

select?*?from?user?where?age?=?10?limit?100000,10;結(jié)果顯示4.73s

那有什么解決方案呢？mysql就不能單表數(shù)據(jù)量超百萬(wàn)乃至千萬(wàn)嘛？答案是NO，顯然是可以的。

SELECT?a.*?FROM?USER?a
INNER?JOIN?
????(SELECT?id?FROM?USER?WHERE?age?=?10?LIMIT?100000,10)?b?
ON?a.id?=?b.id;

結(jié)果0.53s

完美解決了查詢(xún)效率問(wèn)題！！！其中需要對(duì)where條件增加索引，id因?yàn)槭侵麈I自帶索引。select返回減少回表可以提升查詢(xún)性能,所以采用查詢(xún)主鍵字段后進(jìn)行關(guān)聯(lián)大幅度提升了查詢(xún)效率。

PageHelper想要優(yōu)化需要在攔截器的拼接SQL部分進(jìn)行重構(gòu)，由于博主能力有限暫未實(shí)現(xiàn)。能力較強(qiáng)的讀者可以自己進(jìn)行重構(gòu)

附上PageHelper的git地址：

https://github.com/pagehelper/Mybatis-PageHelper/