大数据量下 MyBatis PageHelper 分页查询性能问题的解决办法

花名提莫 2021-02-18 11:28:49 ⋅ 16 阅读

前因

项目一直使用的是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部分进行重构,由于博主能力有限暂未实现。能力较强的读者可以自己进行重构


全部评论: 0

    我有话说:

    什么情况才需要分库表?

    我请教一下,我模拟测试,搞了几张表都是3千万左右数据,带主键查询秒级响应,jmeter插入能够达到每秒6、700条左右,我业务量每秒就是300条左右插入,这个场景是否不需要考虑分库问题

    专业解决 MySQL 查询速度慢与性能

    什么影响了数据库查询速度?关于数据库性能并不是DBA才关心事。

    为什么说作为程序员分库必要性一定要掌握?

      互联网大厂程序员必须掌握海量数据和高并发问题处理技能,期望进入程序员一定要仔细看这篇! MySQL 分库表是做什么? 相信很多程序员对 MySQL 都比较熟悉了,目前国内

    2018 国产开源软件新秀Kooteam 0.1.2 发布,解决低配服务器性能问题

    KooTeam是一款轻量级在线团队协作工具,提供各类文档工具、在线思维导图、在线流程图、项目管理、任务发,知识库管理等工具。

    Linux 5.10.8 发布,最终解决了 Btrfs 性能问题

    Linux 内核 5.10.8 已经发布。作为 Linux LTS 5.10 系列最新版本,此次更新解决了 Btrfs 文件系统性能问题。 Btrfs 是一种支持写入时复制

    移动H5前端五性能优化方案(实战篇)

    移动H5前端五性能优化方案(实战篇)

    分库表这样玩,可以永不迁移数据、避免热点

    中大型项目中,一旦遇到数据比较,小伙伴应该都知道就应该对数据进行拆了。有垂直和水平两种。

    性能与架构」MySQL 8 查询优化新工具 Explain Analyze

    Explain 是我们常用查询分析工具,可以对查询语句执行方式进行评估,给出很多有用线索。

    「轻阅读」亿级用户分布式数据存储解决方案

    分布式数据库和分布式存储是分布式系统中难度最、挑战最,也是最容易出问题地方。互联网公司只有解决分布式数据存储问题,才能支撑更多次亿级用户涌入。

    Apache Spark 3.0 发布,包含3400 多个补丁,TPC-DS 性能提升2倍。

    Spark 是用于数据处理,数据科学,机器学习和数据分析等领域统一引擎。

    架构实战篇:使用MyBatis延迟加载模式为数据库减压,附演示实例

    MyBatis延迟加载,也称为懒加载,是指在进行关联查询时,按照设置延迟规则推迟对关联对象select查询。延迟加载可以有效减少数据库压力......

    iOS TableView性能优化

    TableView性能优化非常考验开发基本功,之前做项目实战时候经常被这个问题困扰

    前端性能分析工具-Keepfast

    能够分析网站资源构建性能和页面性能,生成性能报告并提供优化建议,让性能监控更方便。

    【实战解析】基于HBase数据存储在京东应用场景

    作者就职于京东商城京麦平台组,从事京东商家开放平台相关开发工作。热爱技术,熟悉各种常用开源框架,有丰富大型分布式系统、高并发系统开发经验。热衷于对数据研究,对Hadoop、HBase以及

    微信小程序微商城(三):电商首福利专场无限拉刷新动态API数据实现

    电商首第5个模块-福利专场,通过https调用API数据模型来实现无限拉刷新......

    大厂技术佬:用大白话给你解释Zookeeper选举机制

    Zookeeper 是一个分布式服务框架,主要是用来解决分布式应用中遇到一些数据管理问题如:统一命名服务、状态同步服务、集群管理、分布式应用配置项管理等。 我们可以简单把 

    Fluid 0.3 正式发布:实现云原生场景通用化数据加速

    简介 为了解决数据、AI 等数据密集型应用在云原生计算存储分离场景,存在数据访问延时高、联合分析难、多维管理杂等痛点问题,南京大学 PASALab、阿里巴巴、Alluxio 在 2020 年