PageHelper 分頁一直有性能問題？
點(diǎn)擊關(guān)注公眾號(hào)，Java干貨及時(shí)送達(dá)
前因
項(xiàng)目一直使用的是PageHelper實(shí)現(xiàn)分頁功能，項(xiàng)目前期數(shù)據(jù)量較少一直沒有什么問題。隨著業(yè)務(wù)擴(kuò)增，數(shù)據(jù)庫(kù)擴(kuò)增PageHelper出現(xiàn)了明顯的性能問題。
幾十萬甚至上百萬的單表數(shù)據(jù)查詢性能緩慢，需要幾秒乃至十幾秒的查詢時(shí)間。故此特地研究了一下PageHelper源碼，查找PageHelper分頁的實(shí)現(xiàn)方式。
一段較為簡(jiǎn)單的查詢，跟隨debug開始源碼探尋之旅。
public ResultContent select(Integer id) {
Page<Test> blogPage = PageHelper.startPage(1,3).doSelectPage( () -> testDao.select(id));
List<Test> test = (List<Test>)blogPage.getResult();
return new ResultContent(0, "success", test);
}
主要保存由前端傳入的pageNum(頁數(shù))、pageSize(每頁顯示數(shù)量)和count(是否進(jìn)行count(0)查詢)信息。
這里是簡(jiǎn)單的創(chuàng)建page并保存當(dāng)前線程的變量副本心里，不做深究。
public static <E> Page<E> startPage(int pageNum, int pageSize) {
return startPage(pageNum, pageSize, DEFAULT_COUNT);
}
public static <E> Page<E> startPage(int pageNum, int pageSize, boolean count) {
return startPage(pageNum, pageSize, count, (Boolean)null, (Boolean)null);
}
public static <E> Page<E> startPage(int pageNum, int pageSize, String orderBy) {
Page<E> page = startPage(pageNum, pageSize);
page.setOrderBy(orderBy);
return page;
}
public static <E> Page<E> startPage(int pageNum, int pageSize, boolean count, Boolean reasonable, Boolean pageSizeZero) {
Page<E> page = new Page(pageNum, pageSize, count);
page.setReasonable(reasonable);
page.setPageSizeZero(pageSizeZero);
Page<E> oldPage = getLocalPage();
if(oldPage != null && oldPage.isOrderByOnly()) {
page.setOrderBy(oldPage.getOrderBy());
}
setLocalPage(page);
return page;
}
public <E> Page<E> doSelectPage(ISelect select) {
select.doSelect();
return this;
}
進(jìn)入MapperProxy類執(zhí)行invoke方法獲取到方法名稱及參數(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語句，判斷是增、刪、改、查。判斷返回值是多個(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è)方法開始調(diào)用SqlSessionTemplate、DefaultSqlSession等類獲取到Mapper.xml文件的SQL語句
private <E> Object executeForMany(SqlSession sqlSession, Object[] args) {
List<E> result;
Object param = method.convertArgsToSqlCommandParam(args);
if (method.hasRowBounds()) {
RowBounds rowBounds = method.extractRowBounds(args);
result = sqlSession.<E>selectList(command.getName(), param, rowBounds);
} else {
result = sqlSession.<E>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;
}
public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
try {
Set<Method> 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)行攔截。Spring Boot + PageHelper 實(shí)現(xià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抽象類的pageQuery方法
public static <E> List<E> 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<String, Object> 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);
}
}
在抽象類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類的getPageSql方法進(jìn)行SQL封裝，根據(jù)page對(duì)象信息增加Limit。另外，關(guān)注公眾號(hào)Java技術(shù)棧，在后臺(tái)回復(fù)：面試，可以獲取我整理的 MySQL 系列面試題和答案，非常齊全。
分頁的信息就是這么拼裝起來的：
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í)行查詢并返回
public <E> List<E> 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è)查詢過程完成，原來PageHelper的分頁功能是通過Limit拼接SQL實(shí)現(xiàn)的。查詢效率低的問題也找出來了，那么應(yīng)該如何解決。
首先分析SQL語句，limit在數(shù)據(jù)量少或者頁數(shù)比較靠前的時(shí)候查詢效率是比較高的。(單表數(shù)據(jù)量百萬進(jìn)行測(cè)試)
select * from user where age = 10 limit 1,10;結(jié)果顯示0.43s
當(dāng)where條件后的結(jié)果集較大并且頁數(shù)達(dá)到一個(gè)量級(jí)整個(gè)SQL的查詢效率就十分低下(哪怕where的條件加上了索引也不行)。
select * from user where age = 10 limit 100000,10;結(jié)果顯示4.73s
那有什么解決方案呢？mysql就不能單表數(shù)據(jù)量超百萬乃至千萬嘛？答案是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
完美解決了查詢效率問題?。。∑渲行枰獙?duì)where條件增加索引，id因?yàn)槭侵麈I自帶索引。select返回減少回表可以提升查詢性能,所以采用查詢主鍵字段后進(jìn)行關(guān)聯(lián)大幅度提升了查詢效率。
PageHelper想要優(yōu)化需要在攔截器的拼接SQL部分進(jìn)行重構(gòu)，由于博主能力有限暫未實(shí)現(xiàn)。能力較強(qiáng)的讀者可以自己進(jìn)行重構(gòu)。另外，關(guān)注公眾號(hào)Java技術(shù)棧，在后臺(tái)回復(fù)：面試，可以獲取我整理的 MySQL 系列面試題和答案，非常齊全。
附上PageHelper的git地址：
https://github.com/pagehelper/Mybatis-PageHelper/
原文鏈接：https://blog.csdn.net/baidu_38083619/article/details/82463058
版權(quán)聲明：本文為CSDN博主「歲月安然」的原創(chuàng)文章，遵循CC 4.0 BY-SA版權(quán)協(xié)議，轉(zhuǎn)載請(qǐng)附上原文出處鏈接及本聲明。
關(guān)注Java技術(shù)?？锤喔韶?/strong>
