目录

一、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

注意：这个方法有个局限，就是需要deptno是连续的，也就是中间的记录不能有删除。（可以用删除标志代替真正的物理删除）

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

三、 查询优化

子查询优化原理：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

五、欢迎大家提出错误和新方案

欢迎大家指出错误、隐患、缺陷或提出补充、更好的方案，相互探讨。

UESTC

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

一、效率高的写法
1.无ORDER BY排序的写法。(效率最高)

(经过测试，此方法成本最低，只嵌套一层，速度最快！即使查询的数据量再大，也几乎不受影响，速度依然！)
SELECT *

  FROM (SELECT ROWNUM AS rowno, t.*

          FROM emp t

         WHERE hire_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')

                             AND TO_DATE ('20060731', 'yyyymmdd')

           AND ROWNUM <= 20) table_alias

 WHERE table_alias.rowno >= 10;

2.有ORDER BY排序的写法。(效率较高)

(经过测试，此方法随着查询范围的扩大，速度也会越来越慢哦！)
SELECT *

  FROM (SELECT tt.*, ROWNUM AS rowno

          FROM (  SELECT t.*

                    FROM emp t

                   WHERE hire_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')

                                       AND TO_DATE ('20060731', 'yyyymmdd')

                ORDER BY create_time DESC, emp_no) tt

         WHERE ROWNUM <= 20) table_alias

 WHERE table_alias.rowno >= 10;

二、效率垃圾但又似乎很常用的分页写法
3.无ORDER BY排序的写法。(建议使用方法1代替)

(此方法随着查询数据量的扩张，速度会越来越慢哦！)
SELECT *

  FROM (SELECT ROWNUM AS rowno, t.*

          FROM k_task t

         WHERE flight_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')

                               AND TO_DATE ('20060731', 'yyyymmdd')) table_alias

 WHERE table_alias.rowno <= 20 AND table_alias.rowno >= 10;

--TABLE_ALIAS.ROWNO  between 10 and 100;

4.有ORDER BY排序的写法.(建议使用方法2代替)

(此方法随着查询范围的扩大，速度会越来越慢哦！)
SELECT *

  FROM (SELECT tt.*, ROWNUM AS rowno

          FROM (  SELECT *

                    FROM k_task t

                   WHERE flight_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')

                                         AND TO_DATE ('20060531', 'yyyymmdd')

                ORDER BY fact_up_time, flight_no) tt) table_alias

 WHERE table_alias.rowno BETWEEN 10 AND 20;

5.另类语法。(有ORDER BY写法）

(语法风格与传统的SQL语法不同，不方便阅读与理解，为规范与统一标准，不推荐使用。)
WITH partdata AS

     (

        SELECT ROWNUM AS rowno, tt.*

          FROM (  SELECT *

                    FROM k_task t

                   WHERE flight_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')

                                         AND TO_DATE ('20060531', 'yyyymmdd')

                ORDER BY fact_up_time, flight_no) tt

         WHERE ROWNUM <= 20)

SELECT *

  FROM partdata

 WHERE rowno >= 10;

 

--6另类语法 。(无ORDER BY写法）

WITH partdata AS

     (

        SELECT ROWNUM AS rowno, t.*

          FROM k_task t

         WHERE flight_date BETWEEN TO_DATE ('20060501', 'yyyymmdd')

                               AND TO_DATE ('20060531', 'yyyymmdd')

           AND ROWNUM <= 20)

SELECT *

  FROM partdata

 WHERE rowno >= 10;

三、分析
Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用。
分页查询格式：
SELECT *

  FROM (SELECT a.*, ROWNUM rn

          FROM (SELECT *

                  FROM table_name) a

         WHERE ROWNUM <= 40)

 WHERE rn >= 21

其中最内层的查询SELECT * FROM TABLE_NAME表示不进行翻页的原始查询语句。ROWNUM <= 40和RN >= 21控制分页查询的每页的范围。
上面给出的这个分页查询语句，在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小，将结果尽快的返回。在上面的分页查询语句中，这种考虑主要体现在WHERE ROWNUM <= 40这句上。
选择第21到40条记录存在两种方法，一种是上面例子中展示的在查询的第二层通过ROWNUM <= 40来控制最大值，在查询的最外层控制最小值。而另一种方式是去掉查询第二层的WHERE ROWNUM <= 40语句，在查询的最外层控制分页的最小值和最大值。这是，查询语句如下：
SELECT *

  FROM (SELECT a.*, ROWNUM rn

          FROM (SELECT *

                  FROM table_name) a)

 WHERE rn BETWEEN 21 AND 40

对比这两种写法，绝大多数的情况下，第一个查询的效率比第二个高得多。
这是由于CBO优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。对于第一个查询语句，第二层的查询条件WHERE ROWNUM <= 40就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。
而第二个查询语句，由于查询条件BETWEEN 21 AND 40是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道RN代表什么）。因此，对于第二个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。
上面分析的查询不仅仅是针对单表的简单查询，对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效。
这里就不对包含排序的查询进行说明了，下一篇文章会通过例子来详细说明。
下面简单讨论一下多表联合的情况。
对于最常见的等值表连接查询，CBO一般可能会采用两种连接方式NESTED LOOP和HASH JOIN（MERGE JOIN效率比HASH JOIN效率低，一般CBO不会考虑）。在这里，由于使用了分页，因此指定了一个返回的最大记录数，NESTED LOOP在返回记录数超过最大值时可以马上停止并将结果返回给中间层，而HASH JOIN必须处理完所有结果集（MERGE JOIN也是）。那么在大部分的情况下，对于分页查询选择NESTED LOOP作为查询的连接方法具有较高的效率（分页查询的时候绝大部分的情况是查询前几页的数据，越靠后面的页数访问几率越小）。
因此，如果不介意在系统中使用HINT的话，可以将分页的查询语句改写为：
SELECT *

  FROM (SELECT a.*, ROWNUM rn

          FROM (SELECT *

                  FROM table_name) a

         WHERE ROWNUM <= 40)

 WHERE rn >= 21

一、引言

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

物理分页：相当于执行了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