PageHelper大表分页查询优化

mysql在对大数据量的表进行LIMIT查询且查询分页靠后时，很容易就会导致扫描大量的表，从而导致查询耗时很长。有关这方面的实验请参考mysql limit 性能优化 (网上很多讨论此内容的)

对于一名Java开发者来说, MyBatis 的分页插件PageHelper(GitHub)是一个可以帮助我们以很简单的方式实现优雅的分页, 但是PageHelper默认的分页是使用直接在sql末尾直接添加LIMIT ?,?这种形式,在大数据量时性能通常很差, 因此我通过查阅文档和一些博客总结出使用SpringBoot集成PageHelper时使用优化LIMIT查询的方法。

一、案例

以我博客的日志系统作为案例，演示默认方式的分页查询

PageHelper简单用法

引入SpringBoot Starter:

<!-- 分页助手启动器 -->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.2.13</version>
</dependency>

简单使用:

PageHelper.startPage(page, size).doSelectPageInfo(
                sysLogMapper::selectAllOrderByTime);

sql语句:

<select id="selectAllOrderByTime" resultType="top.vergessen.blog.domain.SysLog">
    SELECT ip,addr,detail,create_by,remark,operate_url,operate_by
    FROM sys_log
    ORDER BY create_by DESC
</select>

为了演示大数据量的情况我首先向日志表插入了30w条数据

问题发现

启动服务, 进行几次查询测试

可以很明显的看到页码很大时查询效率严重下降, 这是因为进行大页码查询时, Mysql扫描行过多造成大量磁盘操作。

二、SQL优化

首先对create_by索引, 提高对时间倒序排序的优化:

ALTER TABLE sys_log ADD INDEX create_by(create_by desc);

原始查询

原始方式的查询用时1s以上

EXPLAIN分析该查询使用了全表扫描(由于排序), 且进行了文件排序(Using filesort), 因此性能非常差

性能优化

使用JOIN方式, 首先对create_by索引进行分页排序查询,查找到要查询的日志的id

查询用时0.1s,相比未优化的时性能提高了10倍, 这个差距在数据量增大或者数据库表的数据行的大小更大时的性能差距将更加明显

使用EXPLAIN分析可知此次查询并没有使用文件排序以及全表扫描, 并且索引文件仅仅保存了TimeStamp格式的创建时间以及日志id, 索引文件大小远小于数据库文件, 因此查询速度很快。

三、PageHelper方言重写

默认方言

PageHelper默认Mysql的方言为直接在末尾拼接LIMIT,源码如下:

public class MySqlDialect extends AbstractHelperDialect {
    @Override
    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();
    }
}

重写Mysql方言

如果想用JOIN方式进行分页需要重写对默认Mysql方言进行拼接的方式:

/**
 * 实现自定义Mysql分页方言，优化大表查询时默认配置查询时间长的问题
 * @author Vergessen
 * @date 2020/9/23 20:03.
 */
public class MyMysqlDialect extends MySqlDialect {

    private static final String LIMIT_SELF = "limit_table";
    private static final String LEFT_BRACKET = ")";

    @Override
    public String getPageSql(String sql, Page page, CacheKey pageKey) {
        StringBuilder sqlBuilder = new StringBuilder(sql.length() + 14);

        // 查询是否为优化分页
        int limitIndex = sql.lastIndexOf(LIMIT_SELF);
        if (limitIndex == -1){
            sqlBuilder.append(sql);
            if (page.getStartRow() == 0) {
                sqlBuilder.append(" LIMIT ? ");
            } else {
                sqlBuilder.append(" LIMIT ?, ? ");
            }
        }
        // 优化分页情况
        else {
            String prefix = sql.substring(0, limitIndex);
            int bracketIndex = prefix.lastIndexOf(LEFT_BRACKET);
            sqlBuilder.append(sql, 0, bracketIndex);
            if (page.getStartRow() == 0) {
                sqlBuilder.append(" LIMIT ? ");
            } else {
                sqlBuilder.append(" LIMIT ?, ? ");
            }
            sqlBuilder.append(sql, bracketIndex, sql.length());
        }

        return sqlBuilder.toString();
    }
}

基本实现就是当我们用JOIN进行分页时要把连接表命名为limit_table, 然后会把此连接表查询的sql末尾添加上LIMIT进行分页, 分页效果就等同于上面进行sql分析时的优化查询方式

如果我们想让这个新的方言生效需要在application.yml添加如下配置:

pagehelper:
  ## helper-dialect 值为新的方言的全类名
  helper-dialect: top.vergessen.blog.config.MyMysqlDialect

新的Mybatis sql如下:

<select id="selectAllOrderByTime" resultType="top.vergessen.blog.domain.SysLog">
    SELECT ip,addr,detail,create_by,remark,operate_url,operate_by
    FROM sys_log AS a
      JOIN (
        SELECT id
        FROM sys_log
        ORDER BY create_by DESC
      ) AS limit_table ON a.id = limit_table.id
</select>

效果测试

重新启动项目进行测试:

经过简单测试, 我们发现性能提升还是非常大的(正常应该是100ms左右, 10几ms的应该是有数据库缓存的缘故)

通过控制台打印的sql语句也可以看出语句拼接成功

参考博客: https://my.oschina.net/anur/blog/1546471 实现较为繁琐, 没有使用PageHelper提供的方言修改接口,可供参考