共计 8512 个字符，预计需要花费 22 分钟才能阅读完成。
前因
我的项目始终应用的是 PageHelper 实现分页性能，项目前期数据量较少始终没有什么问题。随着业务扩增，数据库扩增 PageHelper 呈现了显著的性能问题。
几十万甚至上百万的单表数据查问性能迟缓，须要几秒乃至十几秒的查问工夫。故此顺便钻研了一下 PageHelper 源码，查找 PageHelper 分页的实现形式。
一段较为简单的查问，追随 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(页数)、pageSize(每页显示数量) 和 count(是否进行 count(0) 查问 ) 信息。
这里是简略的创立 page 并保留以后线程的变量正本心里，不做深究。
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;
}开始执行真正的 select 语句
public <E> Page<E> doSelectPage(ISelect select) {select.doSelect();
return this;
}进入 MapperProxy 类执行 invoke 办法获取到办法名称及参数值
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 办法执行 execute 语句，判断是增、删、改、查。判断返回值是多个，进入 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;
}这个办法开始调用 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;
}开始进入 PageHelper 的真正实现，Plugin 通过实现 InvocationHandler 进行动静代理获取到相干信息
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 实现 Mybatis 的 Interceptor 接口，进行拦挡
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();
}
}转到 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 获取到对应的 Page 对象
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);
}进入到 MySqlDialect 类的 getPageSql 办法进行 SQL 封装，依据 page 对象信息减少 Limit。分页的信息就是这么拼装起来的
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 执行查问并返回
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();}
} 至此整个查问过程实现，原来 PageHelper 的分页性能是通过 Limit 拼接 SQL 实现的。查问效率低的问题也找进去了，那么应该如何解决。
首先剖析 SQL 语句，limit 在数据量少或者页数比拟靠前的时候查问效率是比拟高的。(单表数据量百万进行测试)
select * from user where age = 10 limit 1,10; 结果显示 0.43s当 where 条件后的后果集较大并且页数达到一个量级整个 SQL 的查问效率就非常低下 (哪怕 where 的条件加上了索引也不行)。
select * from user where age = 10 limit 100000,10; 结果显示 4.73s那有什么解决方案呢？mysql 就不能单表数据量超百万乃至千万嘛？答案是 NO，显然是能够的。
SELECT a.* FROM USER a
INNER JOIN
(SELECT id FROM USER WHERE age = 10 LIMIT 100000,10) b
ON a.id = b.id;后果 0.53s
完满解决了查问效率问题！！！其中须要对 where 条件减少索引，id 因为是主键自带索引。select 返回缩小回表能够晋升查问性能, 所以采纳查问主键字段后进行关联大幅度晋升了查问效率。
PageHelper 想要优化须要在拦截器的拼接 SQL 局部进行重构，因为博主能力无限暂未实现。能力较强的读者能够本人进行重构。另外，关注公众号 Java 技术栈，在后盾回复：面试，能够获取我整顿的 MySQL 系列面试题和答案，十分齐全。
附上 PageHelper 的 git 地址：
https://github.com/pagehelper…
原文链接：https://blog.csdn.net/baidu_3…
版权申明：本文为 CSDN 博主「岁月坦然」的原创文章，遵循 CC 4.0 BY-SA 版权协定，转载请附上原文出处链接及本申明。
近期热文举荐：
1.600+ 道 Java 面试题及答案整顿 (2021 最新版)
2. 终于靠开源我的项目弄到 IntelliJ IDEA 激活码了，真香！
3. 阿里 Mock 工具正式开源，干掉市面上所有 Mock 工具！
4.Spring Cloud 2020.0.0 正式公布，全新颠覆性版本！
5.《Java 开发手册（嵩山版）》最新公布，速速下载！
感觉不错，别忘了顺手点赞 + 转发哦！
正文完
