浅谈PageHelper插件分页实现原理及大数据量下SQL查询效率问题解决

前因:项目一直使用的是PageHelper实现分页功能,项目前期数据量较少一直没有什么问题。随着业务扩增,数据库扩增PageHelper出现了明显的性能问题。几十万甚至上百万的单表数据查询性能缓慢,需要几秒乃至十几秒的查询时间。故此特地研究了一下PageHelper源码,查找PageHelper分页的实现方式。

一段较为简单的查询,跟随debug开始源码探寻之旅。


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

主要保存由前端传入的pageNum(页数)、pageSize(每页显示数量)和count(是否进行count(0)查询)信息。

这里是简单的创建page并保存当前线程的变量副本心里,不做深究。


  1. public static <E> Page<E> startPage(int pageNum, int pageSize) {
  2. return startPage(pageNum, pageSize, DEFAULT_COUNT);
  3. }
  4. public static <E> Page<E> startPage(int pageNum, int pageSize, boolean count) {
  5. return startPage(pageNum, pageSize, count, (Boolean)null, (Boolean)null);
  6. }
  7. public static <E> Page<E> startPage(int pageNum, int pageSize, String orderBy) {
  8. Page<E> page = startPage(pageNum, pageSize);
  9. page.setOrderBy(orderBy);
  10. return page;
  11. }
  12. public static <E> Page<E> startPage(int pageNum, int pageSize, boolean count, Boolean reasonable, Boolean pageSizeZero) {
  13. Page<E> page = new Page(pageNum, pageSize, count);
  14. page.setReasonable(reasonable);
  15. page.setPageSizeZero(pageSizeZero);
  16. Page<E> oldPage = getLocalPage();
  17. if(oldPage != null && oldPage.isOrderByOnly()) {
  18. page.setOrderBy(oldPage.getOrderBy());
  19. }
  20. setLocalPage(page);
  21. return page;
  22. }

开始执行真正的select语句


  1. public <E> Page<E> doSelectPage(ISelect select) {
  2. select.doSelect();
  3. return this;
  4. }

进入MapperProxy类执行invoke方法获取到方法名称及参数值


  1. public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
  2. if (Object.class.equals(method.getDeclaringClass())) {
  3. try {
  4. return method.invoke(this, args);
  5. } catch (Throwable t) {
  6. throw ExceptionUtil.unwrapThrowable(t);
  7. }
  8. }
  9. final MapperMethod mapperMethod = cachedMapperMethod(method);
  10. return mapperMethod.execute(sqlSession, args);
  11. }

接着是MapperMethod方法执行execute语句,判断是增、删、改、查。判断返回值是多个,进入executeForMany方法


  1. public Object execute(SqlSession sqlSession, Object[] args) {
  2. Object result;
  3. if (SqlCommandType.INSERT == command.getType()) {
  4. Object param = method.convertArgsToSqlCommandParam(args);
  5. result = rowCountResult(sqlSession.insert(command.getName(), param));
  6. } else if (SqlCommandType.UPDATE == command.getType()) {
  7. Object param = method.convertArgsToSqlCommandParam(args);
  8. result = rowCountResult(sqlSession.update(command.getName(), param));
  9. } else if (SqlCommandType.DELETE == command.getType()) {
  10. Object param = method.convertArgsToSqlCommandParam(args);
  11. result = rowCountResult(sqlSession.delete(command.getName(), param));
  12. } else if (SqlCommandType.SELECT == command.getType()) {
  13. if (method.returnsVoid() && method.hasResultHandler()) {
  14. executeWithResultHandler(sqlSession, args);
  15. result = null;
  16. } else if (method.returnsMany()) {
  17. result = executeForMany(sqlSession, args);
  18. } else if (method.returnsMap()) {
  19. result = executeForMap(sqlSession, args);
  20. } else {
  21. Object param = method.convertArgsToSqlCommandParam(args);
  22. result = sqlSession.selectOne(command.getName(), param);
  23. }
  24. } else if (SqlCommandType.FLUSH == command.getType()) {
  25. result = sqlSession.flushStatements();
  26. } else {
  27. throw new BindingException("Unknown execution method for: " + command.getName());
  28. }
  29. if (result == null && method.getReturnType().isPrimitive() && !method.returnsVoid()) {
  30. throw new BindingException("Mapper method '" + command.getName()
  31. + " attempted to return null from a method with a primitive return type (" + method.getReturnType() + ").");
  32. }
  33. return result;
  34. }

这个方法开始调用SqlSessionTemplate、DefaultSqlSession等类获取到Mapper.xml文件的SQL语句


  1. private <E> Object executeForMany(SqlSession sqlSession, Object[] args) {
  2. List<E> result;
  3. Object param = method.convertArgsToSqlCommandParam(args);
  4. if (method.hasRowBounds()) {
  5. RowBounds rowBounds = method.extractRowBounds(args);
  6. result = sqlSession.<E>selectList(command.getName(), param, rowBounds);
  7. } else {
  8. result = sqlSession.<E>selectList(command.getName(), param);
  9. }
  10. // issue #510 Collections & arrays support
  11. if (!method.getReturnType().isAssignableFrom(result.getClass())) {
  12. if (method.getReturnType().isArray()) {
  13. return convertToArray(result);
  14. } else {
  15. return convertToDeclaredCollection(sqlSession.getConfiguration(), result);
  16. }
  17. }
  18. return result;
  19. }

开始进入PageHelper的真正实现,Plugin通过实现InvocationHandler进行动态代理获取到相关信息


  1. public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
  2. try {
  3. Set<Method> methods = signatureMap.get(method.getDeclaringClass());
  4. if (methods != null && methods.contains(method)) {
  5. return interceptor.intercept(new Invocation(target, method, args));
  6. }
  7. return method.invoke(target, args);
  8. } catch (Exception e) {
  9. throw ExceptionUtil.unwrapThrowable(e);
  10. }
  11. }

PageInterceptor 实现Mybatis的Interceptor 接口,进行拦截


  1. public Object intercept(Invocation invocation) throws Throwable {
  2. try {
  3. Object[] args = invocation.getArgs();
  4. MappedStatement ms = (MappedStatement)args[0];
  5. Object parameter = args[1];
  6. RowBounds rowBounds = (RowBounds)args[2];
  7. ResultHandler resultHandler = (ResultHandler)args[3];
  8. Executor executor = (Executor)invocation.getTarget();
  9. CacheKey cacheKey;
  10. BoundSql boundSql;
  11. if(args.length == 4) {
  12. boundSql = ms.getBoundSql(parameter);
  13. cacheKey = executor.createCacheKey(ms, parameter, rowBounds, boundSql);
  14. } else {
  15. cacheKey = (CacheKey)args[4];
  16. boundSql = (BoundSql)args[5];
  17. }
  18. this.checkDialectExists();
  19. List resultList;
  20. if(!this.dialect.skip(ms, parameter, rowBounds)) {
  21. if(this.dialect.beforeCount(ms, parameter, rowBounds)) {
  22. Long count = this.count(executor, ms, parameter, rowBounds, resultHandler, boundSql);
  23. if(!this.dialect.afterCount(count.longValue(), parameter, rowBounds)) {
  24. Object var12 = this.dialect.afterPage(new ArrayList(), parameter, rowBounds);
  25. return var12;
  26. }
  27. }
  28. resultList = ExecutorUtil.pageQuery(this.dialect, executor, ms, parameter, rowBounds, resultHandler, boundSql, cacheKey);
  29. } else {
  30. resultList = executor.query(ms, parameter, rowBounds, resultHandler, cacheKey, boundSql);
  31. }
  32. Object var16 = this.dialect.afterPage(resultList, parameter, rowBounds);
  33. return var16;
  34. } finally {
  35. this.dialect.afterAll();
  36. }
  37. }

转到ExecutorUtil抽象类的pageQuery方法


  1. public static <E> List<E> pageQuery(Dialect dialect, Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql, CacheKey cacheKey) throws SQLException {
  2. if(!dialect.beforePage(ms, parameter, rowBounds)) {
  3. return executor.query(ms, parameter, RowBounds.DEFAULT, resultHandler, cacheKey, boundSql);
  4. } else {
  5. parameter = dialect.processParameterObject(ms, parameter, boundSql, cacheKey);
  6. String pageSql = dialect.getPageSql(ms, boundSql, parameter, rowBounds, cacheKey);
  7. BoundSql pageBoundSql = new BoundSql(ms.getConfiguration(), pageSql, boundSql.getParameterMappings(), parameter);
  8. Map<String, Object> additionalParameters = getAdditionalParameter(boundSql);
  9. Iterator var12 = additionalParameters.keySet().iterator();
  10. while(var12.hasNext()) {
  11. String key = (String)var12.next();
  12. pageBoundSql.setAdditionalParameter(key, additionalParameters.get(key));
  13. }
  14. return executor.query(ms, parameter, RowBounds.DEFAULT, resultHandler, cacheKey, pageBoundSql);
  15. }
  16. }

在抽象类AbstractHelperDialect的getPageSql获取到对应的Page对象


  1. public String getPageSql(MappedStatement ms, BoundSql boundSql, Object parameterObject, RowBounds rowBounds, CacheKey pageKey) {
  2. String sql = boundSql.getSql();
  3. Page page = this.getLocalPage();
  4. String orderBy = page.getOrderBy();
  5. if(StringUtil.isNotEmpty(orderBy)) {
  6. pageKey.update(orderBy);
  7. sql = OrderByParser.converToOrderBySql(sql, orderBy);
  8. }
  9. return page.isOrderByOnly()?sql:this.getPageSql(sql, page, pageKey);
  10. }

进入到MySqlDialect类的getPageSql方法进行SQL封装,根据page对象信息增加Limit。分页的信息就是这么拼装起来的


  1. public String getPageSql(String sql, Page page, CacheKey pageKey) {
  2. StringBuilder sqlBuilder = new StringBuilder(sql.length() + 14);
  3. sqlBuilder.append(sql);
  4. if(page.getStartRow() == 0) {
  5. sqlBuilder.append(" LIMIT ? ");
  6. } else {
  7. sqlBuilder.append(" LIMIT ?, ? ");
  8. }
  9. return sqlBuilder.toString();
  10. }

将最后拼装好的SQL返回给DefaultSqlSession执行查询并返回


  1. public <E> List<E> selectList(String statement, Object parameter, RowBounds rowBounds) {
  2. try {
  3. MappedStatement ms = configuration.getMappedStatement(statement);
  4. return executor.query(ms, wrapCollection(parameter), rowBounds, Executor.NO_RESULT_HANDLER);
  5. } catch (Exception e) {
  6. throw ExceptionFactory.wrapException("Error querying database. Cause: " + e, e);
  7. } finally {
  8. ErrorContext.instance().reset();
  9. }

至此整个查询过程完成,原来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部分进行重构,由于博主能力有限暂未实现。能力较强的读者可以自己进行重构

附上PageHelper的git地址:https://github.com/pagehelper/Mybatis-PageHelper/

上一篇:获取本地ip地址 C#


下一篇:一步一步创建ASP.NET MVC5程序[Repository+Autofac+Automapper+SqlSugar](九)