mysql分页：select * from 表 limit (pageNo-1)*pageSize,pageSize;
oracle分页：select a.* (select 表.*,rowum rn from 表) a where rn>(pageNo-1)pageSize and rn <=pageNopageSize;

一、引言

分页查询每个人程序猿几乎都使用过，但是有部分同学不懂什么是物理分页和逻辑分页。

物理分页：相当于执行了limit分页语句，返回部分数据。物理分页只返回部分数据占用内存小，能够获取数据库最新的状态，实施性比较强，一般适用于数据量比较大，数据更新比较频繁的场景。

逻辑分页：一次性把全部的数据取出来，通过程序进行筛选数据。如果数据量大的情况下会消耗大量的内存，由于逻辑分页只需要读取数据库一次，不能获取数据库最新状态，实施性比较差，适用于数据量小，数据稳定的场合。

那么MP中的物理分页怎么实现呢？ 往下看往下看

二、配置

创建MybatisPlusConfig配置类，需要配置分页插件，小编使用的Spring boot配置方式。

/**
 * @Auther: IT贱男
 * @Date: 2019/6/12 15:06
 * @Description: MybatisPlus配置类
 */
@Configuration
public class MyBatisPlusConfig {

    /**
     * 分页插件
     * @return
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        return new PaginationInterceptor();
    }
}

三、具体分页实现

MP的Wrapper提供了两种分页查询的方式，源码如下：

    /**
     * 根据 entity 条件，查询全部记录（并翻页）
     *
     * @param page         分页查询条件（可以为 RowBounds.DEFAULT）
     * @param queryWrapper 实体对象封装操作类（可以为 null）
     */
    IPage<T> selectPage(IPage<T> page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

    /**
     * 根据 Wrapper 条件，查询全部记录（并翻页）
     *
     * @param page         分页查询条件
     * @param queryWrapper 实体对象封装操作类
     */
    IPage<Map<String, Object>> selectMapsPage(IPage<T> page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

可见两个分页方法参数都是一致的，只是返回参数略有不同，具体选择根据实际业务为准。

    /**
     * 分页查询
     */
    @Test
    public void selectByPage() {
        QueryWrapper<User> wrapper = new QueryWrapper();
        wrapper.like("name", "雨").lt("age", 40);

        Page<User> page = new Page<>(1,2);

        //IPage<User> userIPage = userMapper.selectPage(page, wrapper);

        IPage<Map<String, Object>> mapIPage = userMapper.selectMapsPage(page, wrapper);


        System.out.println("总页数"+mapIPage.getPages());
        System.out.println("总记录数"+mapIPage.getTotal());
        List<Map<String, Object>> records = mapIPage.getRecords();
        records.forEach(System.out::println);
    }

以上分页查询执行sql如下，先是查询了一次总记录数，然后在查询的数据。

DEBUG==>  Preparing: SELECT COUNT(1) FROM user WHERE name LIKE ? AND age < ? 
DEBUG==> Parameters: %雨%(String), 40(Integer)
TRACE<==    Columns: COUNT(1)
TRACE<==        Row: 2
DEBUG==>  Preparing: SELECT id,name,age,email,manager_id,create_time FROM user WHERE name LIKE ? AND age < ? LIMIT ?,? 
DEBUG==> Parameters: %雨%(String), 40(Integer), 0(Long), 2(Long)
TRACE<==    Columns: id, name, age, email, manager_id, create_time
TRACE<==        Row: 2, 张雨琪, 31, zjq@baomidou.com, 1088248166370832385, 2019-01-14 09:15:15
TRACE<==        Row: 3, 刘红雨, 31, lhm@baomidou.com, 1088248166370832385, 2019-01-14 09:48:16
DEBUG<==      Total: 2
总页数1
总记录数2

现在我们有需求只要查询数据即可， 不关心总记录数等，如果使用默认的方式就消耗不必要的性能。那么解决办法也是很简单的，只需要在创建page对象时传入第三个参数为false即可。

 Page<User> page = new Page<>(1,2,false);

四、自定义sql分页查询

有时候查询的数据难免会出现多表连接查询，或者是一些复杂的sql语句，但是这些语句也是需要支持分页查询的，

先定义查询接口，第一个参数要是分页的参数，小编这里演示就写简单的sql。

步骤一：在mapper文件中，编写对应的分页查询接口。

步骤二：在xml中编写对应的sql语句，小编这里演示的 “${ew.customSqlSegment}”，这个是如果你想自定义的sql语句，也想使用wrapper查询条件构造器，则需要在mapper接口中添加参数，以及xml中也要有固定。 

    /**
     * 自定义sql分页
     * @param page
     * @param queryWrapper 看这里看这里，如果自定义的方法中需要用到wrapper查询条件，需要这样写
     * @return
     */
    IPage<User> selectMyPage(IPage<User> page, @Param(Constants.WRAPPER) Wrapper<User> queryWrapper);

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.mapper.UserMapper">

    <select id="selectMyPage" resultType="com.example.demo.model.User">
        SELECT * FROM user ${ew.customSqlSegment}
    </select>

</mapper>

   /**
     * 自定义sql分页查询
     */
    @Test
    public void selectByMyPage() {
        QueryWrapper<User> wrapper = new QueryWrapper();
        wrapper.like("name", "雨").lt("age", 40);
        Page<User> page = new Page<>(1,2);
        IPage<User> mapIPage = userMapper.selectMyPage(page, wrapper);

        System.out.println("总页数"+mapIPage.getPages());
        System.out.println("总记录数"+mapIPage.getTotal());
        List<User> records = mapIPage.getRecords();
        records.forEach(System.out::println);
    }

五、多表sql分页查询

看评论有小伙伴反馈多表连接查询怎么分页，其实道理都是一样的。

小编以简单的为主，sql如下： his_ipd_encounter、his_user 两张表

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.mapper.UserMapper">

    <select id="selectByHisName" resultType="java.lang.String">
        select u.realname from his_ipd_encounter e, his_user u where e.his_uid = u.his_uid
    </select>
    

</mapper>

mapepr如下：需要传入分页的参数，返回的类型也需要是分页对象


/**
 * <p>
 * 用户 Mapper 接口
 * </p>
 *
 * @author IT贱男
 * @since 2019-06-14
 */
public interface UserMapper extends MyMapper<User> {


    /**
     * 多表查询分页
     * @param page
     * @return
     */
    IPage<String> selectByHisName(IPage<User> page);
}


测试如下：通过查看日志，执行的sql加了分页条件的。

   @Test
    public void select(){
        // 创建分页参数
        Page<User> page = new Page<>(1,2);
        IPage<String> result = userMapper.selectByHisName(page);
        // 获取数据
        List<String> records = result.getRecords();
        records.forEach(System.out::println);
        System.out.println("总页数 = "+ result.getPages());
    }


ARNWarn: Could not find @TableId in Class: com.example.demo.model.HisUser.
INFOStarted UserMapperTest in 2.428 seconds (JVM running for 2.959)
select u.realname from his_ipd_encounter e, his_user u where e.his_uid = u.his_uid
DEBUG==>  Preparing: SELECT COUNT(1) FROM his_ipd_encounter e, his_user u WHERE e.his_uid = u.his_uid 
DEBUG==> Parameters: 
TRACE<==    Columns: COUNT(1)
TRACE<==        Row: 117
DEBUG==>  Preparing: select u.realname from his_ipd_encounter e, his_user u where e.his_uid = u.his_uid LIMIT ?,? 
DEBUG==> Parameters: 0(Long), 2(Long)
TRACE<==    Columns: realname
TRACE<==        Row: 胡伯云
TRACE<==        Row: 安元慧
DEBUG<==      Total: 2
 Time：20 ms - ID：com.example.demo.mapper.UserMapper.selectByHisName
Execute SQL：
    com.p6spy.engine.wrapper.PreparedStatementWrapper@61bcbcce

胡伯云
安元慧
总页数 = 59


 

目录

一、limit分页公式、总页数公式

1 limit分页公式

2 总页数公式

二 、Mysql的三种分页方法

1 limit m,n分页语句

2 limit m语句

三、 查询优化

1 使用子查询优化

2 使用 id 限定优化（前提：id是连续递增，删除过记录不符合）

3 使用临时表优化

四、关于数据表的id说明

一、limit分页公式、总页数公式



1 limit分页公式



（1）limit分页公式：curPage是当前第几页；pageSize是一页多少条记录

limit  (curPage-1)*pageSize,pageSize

（2）用的地方：sql语句中

select * from student limit(curPage-1)*pageSize,pageSize;

2 总页数公式



（1）总页数公式：totalRecord是总记录数；pageSize是一页分多少条记录

int totalPageNum = (totalRecord +pageSize - 1) / pageSize;

（2）用的地方：前台UI分页插件显示分页码

（3）查询总条数：totalRecord是总记录数，SELECT COUNT(*) FROM tablename



二 、Mysql的三种分页方法

 

1 limit m,n分页语句

select * from dept order by deptno desc limit 3,3;
select * from dept order by deptno desc limit m,n;

limit 3,3的意思扫描满足条件的3+3行，撇去前面的3行，返回最后的3行，那么问题来了，如果是limit 200000,200，需要扫描200200行，如果在一个高并发的应用里，每次查询需要扫描超过20W行，效率十分低下。

测试见：https://www.cnblogs.com/youyoui/p/7851007.html

例如：

select * from orders_history where type=8 limit 100,100;
select * from orders_history where type=8 limit 1000,100;
select * from orders_history where type=8 limit 10000,100;
select * from orders_history where type=8 limit 100000,100;
select * from orders_history where type=8 limit 1000000,100;



2 limit m语句

 select * from dept where deptno >10 order by deptno asc limit n;//下一页
 select * from dept where deptno <60 order by deptno desc limit n//上一页

这种方式不管翻多少页只需要扫描n条数据。

但是，虽然扫描的数据量少了，但是在某些需要跳转到多少也得时候就无法实现，这时还是需要用到方法1，既然不能避免，那么我们可以考虑尽量减小m的值，因此我们可以给这条语句加上一个条件限制。是的每次扫描不用从第一条开始。这样就能尽量减少扫描的数据量。

例如：每页10条数据，当前是第10页，当前条目ID的最大值是109，最小值是100.(当前100-109)

那么跳到第9页：

select * from dept where deptno<100 order by deptno desc limit 0,10;   //倒序

那么跳到第8页：

select * from dept where deptno<100 order by deptno desc limit 10,10;

那么跳到第11页：

select * from dept where deptno>109 order by deptno asc limit 0,10;


最后附上参考文档网址：http://www.open-open.com/doc/view/2bda32bf64864e8e965e91686f5309d4

原文：https://blog.csdn.net/HADEWOKE/article/details/53996110

上面的方法还可以优化，见第三。

三、 查询优化

子查询优化原理：https://zhuanlan.zhihu.com/p/163658548

1 使用子查询优化

这种方式先定位偏移位置的 id，然后往后查询，这种方式适用于 id 递增的情况。

select * from orders_history where type=8 limit 100000,1;
select id from orders_history where type=8 limit 100000,1;

select * from orders_history where type=8 and 
id>=(select id from orders_history where type=8 limit 100000,1) 
limit 100;

select * from orders_history where type=8 limit 100000,100;


4条语句的查询时间如下：

第1条语句：3674ms
	
第2条语句：1315ms
	
第3条语句：1327ms
	
第4条语句：3710ms
针对上面的查询需要注意：

比较第1条语句和第2条语句：使用 select id 代替 select * 速度增加了3倍
	
比较第2条语句和第3条语句：速度相差几十毫秒
	
比较第3条语句和第4条语句：得益于 select id 速度增加，第3条语句查询速度增加了3倍
这种方式相较于原始一般的查询方法，将会增快数倍。

 

 

2 使用 id 限定优化（前提：id是连续递增，删除过记录不符合）

这种方式假设数据表的id是连续递增的，则我们根据查询的页数和查询的记录数可以算出查询的id的范围，可以使用 id between and 来查询：

select * from orders_history where type=2 
and id between 1000000 and 1000100 limit 100;


查询时间：15ms 12ms 9ms

这种查询方式能够极大地优化查询速度，基本能够在几十毫秒之内完成。限制是只能使用于明确知道id的情况，不过一般建立表的时候，都会添加基本的id字段，这为分页查询带来很多便利。

还可以有另外一种写法：

select * from orders_history where id >= 1000001 limit 100;


当然还可以使用 in 的方式来进行查询，这种方式经常用在多表关联的时候进行查询，使用其他表查询的id集合，来进行查询：

select * from orders_history where id in
(select order_id from trade_2 where goods = 'pen')
limit 100;


这种 in 查询的方式要注意：某些 mysql 版本不支持在 in 子句中使用 limit。

3 使用临时表优化

这种方式已经不属于查询优化，这儿附带提一下。

对于使用 id 限定优化中的问题，需要 id 是连续递增的，但是在一些场景下，比如使用历史表的时候，或者出现过数据缺失问题时，可以考虑使用临时存储的表来记录分页的id，使用分页的id来进行 in 查询。这样能够极大的提高传统的分页查询速度，尤其是数据量上千万的时候。

 

四、关于数据表的id说明

一般情况下，在数据库中建立表的时候，强制为每一张表添加 id 递增字段，这样方便查询。

如果像是订单库等数据量非常庞大，一般会进行分库分表。这个时候不建议使用数据库的 id 作为唯一标识，而应该使用分布式的高并发唯一 id 生成器来生成，并在数据表中使用另外的字段来存储这个唯一标识。

使用先使用范围查询定位 id （或者索引），然后再使用索引进行定位数据，能够提高好几倍查询速度。即先 select id，然后再 select *；

https://www.cnblogs.com/youyoui/p/7851007.html

 

目录：
文章目录
方法一：三重循环
思路
代码实现
查询出的结果及时间
方法二：利用max（主键）
代码实现
查询出的结果及时间
方法三：利用row_number关键字
SQL实现
查询出的结果及时间
第四种方法：offset /fetch next（2012版本及以上才有）
代码实现
结果及运行时间
封装的存储过程
总结


  这篇博客讲的是SQL server的分页方法，用的SQL server 2012版本。下面都用pageIndex表示页数，pageSize表示一页包含的记录。并且下面涉及到具体例子的，设定查询第2页，每页含10条记录。


  首先说一下SQL server的分页与MySQL的分页的不同，mysql的分页直接是用limit (pageIndex-1),pageSize就可以完成，但是SQL server 并没有limit关键字，只有类似limit的top关键字。所以分页起来比较麻烦。


  SQL server分页我所知道的就只有四种：三重循环；利用max（主键）;利用row_number关键字，offset/fetch next关键字（是通过搜集网上的其他人的方法总结的，应该目前只有这四种方法的思路，其他方法都是基于此变形的）。

要查询的学生表的部分记录


方法一：三重循环
思路
  先取前20页，然后倒序，取倒序后前10条记录，这样就能得到分页所需要的数据，不过顺序反了，之后可以将再倒序回来，也可以不再排序了，直接交给前端排序。


  还有一种方法也算是属于这种类型的，这里就不放代码出来了，只讲一下思路，就是先查询出前10条记录，然后用not in排除了这10条，再查询。
代码实现
-- 设置执行时间开始，用来查看性能的
set statistics time on ;
-- 分页查询（通用型）
select * 
from (select top pageSize * 
from (select top (pageIndex*pageSize) * 
from student 
order by sNo asc ) -- 其中里面这层，必须指定按照升序排序，省略的话，查询出的结果是错误的。
as temp_sum_student 
order by sNo desc ) temp_order
order by sNo asc

-- 分页查询第2页，每页有10条记录
select * 
from (select top 10 * 
from (select top 20 * 
from student 
order by sNo asc ) -- 其中里面这层，必须指定按照升序排序，省略的话，查询出的结果是错误的。
as temp_sum_student 
order by sNo desc ) temp_order
order by sNo asc
;

查询出的结果及时间



方法二：利用max（主键）
  先top前11条行记录，然后利用max（id）得到最大的id，之后再重新再这个表查询前10条，不过要加上条件，where id>max(id)。
代码实现
set statistics time on;
-- 分页查询（通用型）
select top pageSize * 
from student 
where sNo>=
(select max(sNo) 
from (select top ((pageIndex-1)*pageSize+1) sNo
from student 
order by  sNo asc) temp_max_ids) 
order by sNo;


-- 分页查询第2页，每页有10条记录
select top 10 * 
from student 
where sNo>=
(select max(sNo) 
from (select top 11 sNo
from student 
order by  sNo asc) temp_max_ids) 
order by sNo;


查询出的结果及时间



方法三：利用row_number关键字
  直接利用row_number() over(order by id)函数计算出行数，选定相应行数返回即可，不过该关键字只有在SQL server 2005版本以上才有。
SQL实现
set statistics time on;
-- 分页查询（通用型）
select top pageSize * 
from (select row_number() 
over(order by sno asc) as rownumber,* 
from student) temp_row
where rownumber>((pageIndex-1)*pageSize);

set statistics time on;
-- 分页查询第2页，每页有10条记录
select top 10 * 
from (select row_number() 
over(order by sno asc) as rownumber,* 
from student) temp_row
where rownumber>10;

查询出的结果及时间



第四种方法：offset /fetch next（2012版本及以上才有）
代码实现
set statistics time on;
-- 分页查询（通用型）
select * from student
order by sno 
offset ((@pageIndex-1)*@pageSize) rows
fetch next @pageSize rows only;

-- 分页查询第2页，每页有10条记录
select * from student
order by sno  
offset 10 rows
fetch next 10 rows only ;

offset A rows ,将前A条记录舍去，fetch next B rows only ，向后在读取B条数据。
结果及运行时间



封装的存储过程
最后，我封装了一个分页的存储过程，方便大家调用，这样到时候写分页的时候，直接调用这个存储过程就可以了。
分页的存储过程
create procedure paging_procedure
(	@pageIndex int, -- 第几页
	@pageSize int  -- 每页包含的记录数
)
as
begin 
	select top (select @pageSize) *     -- 这里注意一下，不能直接把变量放在这里，要用select
	from (select row_number() over(order by sno) as rownumber,* 
			from student) temp_row 
	where rownumber>(@pageIndex-1)*@pageSize;
end

-- 到时候直接调用就可以了，执行如下的语句进行调用分页的存储过程
exec paging_procedure @pageIndex=2,@pageSize=10;

总结
  根据以上四种分页的方法执行的时间可以知道，以上四种分页方法中，第二，第三，第三四种方法性能是差不多的，但是第一种性能很差，不推荐使用。还有就是这篇博客这是测试了小量数据，还没有分页大量数据，所以不清楚在大量数据要分页时哪种方法的性能更加好。我这里推荐第四种，毕竟第四种是SQL server公司升级后推出的新方法，所以应该理论上性能和可读性都会更加好。