SpringBoot分页其实很简单

分页其实很简单

一、数据库Limit

Limit的使用

Limit子句可以被用于强制 SELECT 语句返回指定的记录数。

Limit接受一个或两个数字参数，参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。

LIMIT offset,length

//初始记录行的偏移量是 0(而不是 1)：
mysql> SELECT * FROM table LIMIT 5,10; //检索记录行6-15

//为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：
mysql> SELECT * FROM table LIMIT 5,-1; // 检索记录行 6-last

//如果只给定一个参数，它表示返回最大的记录行数目。换句话说，LIMIT n 等价于 LIMIT 0,n：
mysql> SELECT * FROM table LIMIT 5;     //检索前 5 个记录行
1
2
3
4
5
6
7
8
9
10

Limit的效率

Limit的执行效率高，是对于一种特定条件下来说的：即数据库的数量很大，但是只需要查询一部分数据的情况。原理是：避免全表扫描，提高查询效率。

比如：每个用户的phone是唯一的，如果用户使用phone作为用户名登录的话，就需要查询出phone对应的一条记录。

SELECT * FROM t_user WHERE phone=?;
上面的语句实现了查询phone对应的一条用户信息，但是由于phone这一列没有加索引，会导致全表扫描，效率会很低。
SELECT * FROM t_user WHERE email=? LIMIT 1;
加上LIMIT 1，只要找到了对应的一条记录，就不会继续向下扫描了，效率会大大提高。

在一种情况下，使用limit效率低，那就是：只使用limit来查询语句，并且偏移量特别大的情况

做以下实验：
语句1：
　　select * from table limit 150000,1000;
　　语句2:
　　select * from table while id>=150000 limit 1000;
语句1为0.2077秒；语句2为0.0063秒。两条语句的时间比是：语句1/语句2≈33

比较以上的数据时，我们可以发现采用where...limit....性能基本稳定，受偏移量和行数的影响不大，而单纯采用limit的话，受偏移量的影响很大，当偏移量大到一定后性能开始大幅下降。不过在数据量不大的情况下，两者的区别不大。

所以应当先使用where等查询语句，配合limit使用，效率才高

注意，在sql语句中，limt关键字是最后才被处理的，是对查询好的结果进行分页。以下条件的处理顺序一般是：where->group by->having-order by->limit

优化LIMIT

在使用limit之前，先对数据进行一定的处理，比如先用where语句，减少数据的总量之后再分页，或者order by子句用上索引。总之，思路就是优化limit操作对象的检索速度。

二、PageHelper

2.1 PageHelper的使用

依赖引入

<dependency>
    <groupId>com.github.pagehelpergroupId>
    <artifactId>pagehelperartifactId>
    <version>5.2.0version>
dependency>
1
2
3
4
5

配置

#分页插件
pagehelper:
  #标识是数据库方言
  helperDialect: mysql
  #启用合理化，如果pageNum<1会查询第一页，如果pageNum>pages会查询最后一页
  reasonable: true
  #为了支持startPage(Object params)方法，增加了该参数来配置参数映射，用于从对象中根据属性名取值， 可以配置pageNum,pageSize,count,pageSizeZero,reasonable，不配置映射的用默认值， 默认值为pageNum=pageNum;pageSize=pageSize;count=countSql;reasonable=reasonable;pageSizeZero=pageSizeZero
  params: count=countSql
  #支持通过 Mapper 接口参数来传递分页参数，默认值false，分页插件会从查询方法的参数值中，自动根据上面 params 配置的字段中取值，查找到合适的值时就会自动分页
  supportMethodsArguments: true
  #如果 pageSize=0 就会查询出全部的结果（相当于没有执行分页查询）
  pageSizeZero: true
1
2
3
4
5
6
7
8
9
10
11
12

使用

// 开始分页
PageHelper.startPage(pageNum, pageSize);
// 查询
List<User> userList = userService.getUserList();
// 封装分页对象
PageInfo<User> pageInfo = new PageInfo<>(userList);
1
2
3
4
5
6

其中，pageNum 表示要查询的页码，pageSize 表示每页的记录数。调用 startPage 方法之后，PageHelper 会自动将下一次查询作为分页查询，并且会在查询之后返回一个 Page 对象，然后可以将这个对象转换为 PageInfo 对象，从而获得分页相关的信息。

2.2 底层原理

• 首先调用 PageHelper 的 startPage 方法开启分页，方法中会将分页参数存到一个变量 ThreadLocal LOCAL_PAGE中；
• 然后调用 mapper 进行查询，这里实际上会被 PageInterceptor 类拦截，执行其重写的 interceptor 方法，该方法中主要做了以下两件事：
  • 获取到 MappedStatement，拿到业务写好的 sql，将 sql 改造成 select count(0) 并执行查询，并将执行结果存到 LOCAL_PAGE 里的Page 中的 total 属性，表示总条数
  • 获取到 xml 中的 sql 语句，并 append 一些分页 sql 段，然后执行，将执行结果存到 LOCAL_PAGE 里的 Page 中的 list 属性，这里的Page 类实际是 ArrayList 的子类。
• 结果是封装到了 Page 中，最后交由 PageInfo，从中可以获取到总条数、总页数等参数。

1.分页参数储存

首先看PageHelper.startPage的源码：

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
    Page<E> oldPage = getLocalPage();
    //判断是否存在旧分页数据
    if (oldPage != null && oldPage.isOrderByOnly()) {
        //当只存在orderBy参数时,即为true，也就是说,当存在旧分页数据并且旧分页数据只有排序参数时,就将旧分页数据的排序参数列入新分页数据的排序参数
        page.setOrderBy(oldPage.getOrderBy());
    }
	//将新的分页数据page存入本地线程变量中
    setLocalPage(page);
    return page;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

其实主要就是把分页参数给到 Page ,然后将实例 Page 存储到 ThreadLocal 中。

public abstract class PageMethod {
    protected static final ThreadLocal<Page> LOCAL_PAGE = new ThreadLocal();

    public PageMethod() {
    }

    protected static void setLocalPage(Page page) {
        LOCAL_PAGE.set(page);
    }
}
1
2
3
4
5
6
7
8
9
10

2.拦截器改造SQL

1) 统计总数

PageHelper 是通过拦截器底层执行 sql，对应的拦截器是 PageInterceptor，可以看出拦截了 Executor 的 query方法，毕竟 Mybatis 底层查询实际是借助 SqlSeesion 调用 Executor#query。

@Intercepts({@Signature(
    type = Executor.class,
    method = "query",
    args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}
), @Signature(
    type = Executor.class,
    method = "query",
    args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class, CacheKey.class, BoundSql.class}
)})
public class PageInterceptor implements Interceptor {
1
2
3
4
5
6
7
8
9
10

看下在哪里进行了总条数查询

进入 count 方法内看下：

继续追踪，进入 executeAutoCount方法内

继续追踪，进入 getSmartCountSql方法内

在追踪代码执行过程中，发现进入 executeAutoCount 方法内，这个方法内有个变量为 countSql，其内容正是select count(0)...，说明 PageHelper 在此处进行了总条数查询。

2) 分页查询

再看下 intercept 方法中如何进行如何分页查询：

在 pageQuery 方法中进行实际查询操作：

方法中的 pageSql 即为分页查询语句，看下 getPageSql 是如何实现的：


很明显看出 PageHelper 在分页查询时对每一个查询 sql 末尾都增加了 limit 子句。

值得注意的是，在 intercept 方法末尾的 finally 中调用 afferAll 方法对 ThreadLocal 进行 remove。

public void afterAll() {
    AbstractHelperDialect delegate = this.autoDialect.getDelegate();
    if (delegate != null) {
        delegate.afterAll();
        this.autoDialect.clearDelegate();
    }

    clearPage();
}

public static void clearPage() {
    LOCAL_PAGE.remove();
}
1
2
3
4
5
6
7
8
9
10
11
12
13

3) PageInfo

实际代码中进行分页查询得到list 之后，还要将其封装进 PageInfo 类中，才能获取到分页信息。我们关注下 PageInfo 中的构造器：

在这段代码中，将 list强转为 Page，Page 类实际上是 ArrayList 的子类，且 Page 类中包含了分页的具体信息，而分页查询返回的 list 实际类型就是 Page，所以将其封装为 PageInfo 再返回

2.3 安全问题

PageHelper 的 startPage 方法使用了静态的 ThreadLocal 参数，分页参数和线程是绑定的。 只要保证在 startPage 方法调用后紧跟 MyBatis 查询方法，这就是安全的。因为 PageHelper 在 finally 代码段中自动清除了 ThreadLocal存储的对象。但是例如下面这样的代码，就是不安全的用法：

PageHelper.startPage(1, 10);
List<User> list;
if (param1 != null) {
    list = userMapper.selectIf(param1);
} else {
    list = new ArrayList<User>();
}
1
2
3
4
5
6
7

这种情况下由于 param1 存在 null 的情况，就会导致 PageHelper 生产了一个分页参数，但是没有被消费，这个参数就会一直保留在这个线程上。当这个线程再次被使用时，就可能导致不该分页的方法去消费这个分页参数，这就产生了莫名其妙的分页。正确写法如下：

List<User> list;
if (param1 != null) {
    PageHelper.startPage(1, 10);
    list = userMapper.selectIf(param1);
} else {
    list = new ArrayList<User>();
}
1
2
3
4
5
6
7

三、PageHelper应该什么场合用

由于pageHelper拦截我们写的sql语句，自己重新包装一层，在后面添加limit，这在数据量小的时候，比在每个mapper.xml中自己添加并且封装分页结果要方便很多。

百万数据量SQL，在进行分页查询时会出现性能问题，例如我们使用PageHelper时，由于分页查询时，PageHelper会拦截查询的语句会进行两个步骤

• 添加 select count(0) from (原查询sql)，用于统计查询的总数

• 拼接 limit startPage，pageSize用于分页

此时有两个问题

第一个问题是：

用于统计的 select count(0) from (原查询sql)在数据量大时速度慢。

第二个问题是：

limit startPage，pageSize 在大数据量时效率低。

对于第二个问题：注意：一旦数据量过大，分页时limit的偏移量必然增大 ，不可避免的，查询的时间就会呈几何倍数增长。此时应该放弃使用插件，采用方法一中Limit的优化写法

PageHelper在统计总数时的优化

话接第一个问题，数据库的表共有10000条数据，分页查询时，在数据库中是很快，但是在使用Mybatis分页插件的时候居然8s多！

原因：PageHelper在做分页查询的时候，select count(0) 会先把原来的查询语句全部重新查询一遍（未做数量限制），然后再执行select count(0)操作，相当于查询两遍，导致查询速度慢。

例如：

select count(*) from ("原来的查询sql,相当于多查了一遍") user
// 如下
SELECT count(0)
 FROM 
 	(SELECT *
     FROM (SELECT A.X FROM t1 AS A
     LEFT JOIN t2 AS P
     ON P.X = A.X
     LEFT JOIN t3 AS T
     ON A.X = T.X ) AS V
     LEFT JOIN t4 AS B
     ON XXX）
1
2
3
4
5
6
7
8
9
10
11
12

优化方案

在原来的查询方法后面，加上”_COUNT“，重写总数量查询方法，优化sql，覆盖默认的数量查询方法。

例如：原来的mapper层里面的查询方法：

Response getUserInfo(Map map);
1

重写一个方法，getUserInfo后面加“_COUNT”：返回类型必须为Long

Long getUserInfo_COUNT();
1

然后在xml中写具体sql：

SELECT count(1) FROM t1;
1

方法里面的sql自定义完成，尽量减少不必要的表关联，提高查询速率。

总结：

分页插件中的计算Count的sql语句，会将需要分页的所有数据查出来然后生成一个临时表，再去计算Count。如果数据量比较大，生产临时表就多查询了一次表，会导致最后Count计算会十分慢，从而影响分页的结果。