本文介绍了物理分页和逻辑分页的区别，并详细探讨了MybatisPageHelper和PageHelper组件的分页实现。重点讨论了MySQL中的SQL_CALC_FOUND_ROWS与count(*)在性能上的对比，指出在特定情况下SQL_CALC_FOUND_ROWS可能具有性能优势，但在MySQL8.x版本中已不推荐使用。文章提供了相关配置和代码示例，以及MyISAM和InnoDB引擎的性能对比。
温故而知新，这里记录一下
这篇文章资料来自于网络，对部分知识整理，这里只是记录一下，仅供参考。
一、引言
分页查询每个人程序猿几乎都使用过，但是有部分同学不懂什么是物理分页和逻辑分页。
物理分页：
相当于执行了limit分页语句，返回部分数据。物理分页只返回部分数据占用内存小，能够获取数据库最新的状态，实施性比较强，一般适用于数据量比较大，数据更新比较频繁的场景。
逻辑分页：
一次性把全部的数据取出来，通过程序进行筛选数据。如果数据量大的情况下会消耗大量的内存，由于逻辑分页只需要读取数据库一次，不能获取数据库最新状态，实施性比较差，适用于数据量小，数据稳定的场合。
二、分页处理方式
2.1、MybatisPageHelper组件
常用的分页组件有MybatisPageHelper等，分页原理为先执行原SQL+limit语句，再执行select count(*) from xxx where xxx语句。
一共执行了两次，进行了两次数据的筛选和过滤。
2.1.1、MybatisPlusConfig配置
创建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();
}
}
2.1.2、具体分页实现
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可以看出，质量量两条查询语句：
- SELECT COUNT(1) FROM user WHERE name LIKE ? AND age < ?
- SELECT id,name,age,email,manager_id,create_time FROM user WHERE name LIKE ? AND age < ? LIMIT ?,?
2.2、PageHelper组件
2.2.1. 添加 Maven 依赖
1 2 3 4 5 6 | <!--MyBatis 分页插件: MyBatis PageHelper-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.5</version>
</dependency>
|
2.2.2. 添加配置
在 application.properties 配置文件中添加 MyBatis PageHelper 的配置项
1 2 3 4 5 | # PageHelper 分页插件配置 pagehelper.helperDialect=mysql pagehelper.reasonable=true pagehelper.supportMethodsArguments=true pagehelper.params=count=countSql |
2.2.3 分页查询
通过 MyBatis PageHelper 进行分页查询实际上非常简单，只需在 service (或 mapper) 方法执行查询前，调用一次 PageHelper.startPage (pageNum,pageSize) 来设置分页查询参数即可，其中 pageNum 为记录页数，pageSize 为单页记录数量。此时 service (或 mapper) 方法的查询结果就是分页后的结果了。如果期望获得相关的分页信息，还可以将查询结果封装到 PageInfo 对象中，以获得总页数、总记录数、当前页数等相关分页信息
现在通过一个实际示例，来具体演示操作，这里我们提供了一个分页查询的 Controller
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | /**
* 分页查询
* @param pageNum 记录页数
* @param pageSize 单页记录数量
* @return
*/
@ResponseBody
@RequestMapping("/findPage")
public List<Student> findPage(@RequestParam int pageNum, @RequestParam int pageSize) {
// 设置分页查询参数
PageHelper.startPage(pageNum,pageSize);
List<Student> studentList = studentService.findList();
for(Student student : studentList) {
System.out.println("element : " + student);
}
// 封装分页查询结果到 PageInfo 对象中以获取相关分页信息
PageInfo pageInfo = new PageInfo( studentList );
System.out.println("总页数: " + pageInfo.getPages());
System.out.println("总记录数: " + pageInfo.getTotal());
System.out.println("当前页数: " + pageInfo.getPageNum());
System.out.println("当前页面记录数量: " + pageInfo.getSize());
return pageInfo.getList();
}
|
service 方法中所调用的查询 SQL 如下所示，可以看到，SQL 中无需使用 limit 语句
1 2 3 4 5 6 7 8 9 10 11 12 | ...
<resultMap id="studentResultMap" type="com.aaron.springbootdemo.pojo.Student">
<id property="id" column="id" jdbcType="INTEGER"/>
<result property="username" column="username" jdbcType="VARCHAR"/>
<result property="sex" column="sex" jdbcType="VARCHAR"/>
<result property="address" column="address" jdbcType="VARCHAR"/>
</resultMap>
<select id="findList" parameterType="String" resultMap="studentResultMap">
SELECT * FROM user WHERE sex = ?
</select>
... |
还是两个SQL查询：
[main] [DEBUG] [c.n.b.s.r.C.getUser_COUNT] ==> Preparing: SELECT count(0) FROM (SELECT * FROM user WHERE sex = ?) table_count
[main] [DEBUG] [c.n.b.s.r.C.getUser_COUNT] ==> Parameters: sex(String)
[main] [DEBUG] [c.n.b.s.r.C.getUser_COUNT] <== Total: 1
[main] [DEBUG] [c.n.b.s.r.C.getUser] ==> Preparing: SELECT * FROM user WHERE sex = ? order by time asc LIMIT ?
[main] [DEBUG] [c.n.b.s.r.C.getUser] ==> Parameters: sex(String), 5(Integer)
[main] [DEBUG] [c.n.b.s.r.C.getUser] <== Total: 5
2.3 mysql SQL_CALC_FOUND_ROWS & FOUND_ROWS
可以使用MySQL “SQL_CALC_FOUND_ROWS & FOUND_ROWS()” ，只需进行一次数据的筛选和过滤。（FOUND_ROWS()函数用于统计limit之前查找到的行数）。
2.3.1 例子
实际项目中使用
（1）UserMapper.xml
<resultMap type="com.sys.entity.User" id="UserResult">
<id property="userId" column="user_id" />
<result property="deptId" column="dept_id" />
<result property="userName" column="user_name" />
<result property="nickName" column="nick_name" />
<result property="roleIds" column="role_ids" />
<result property="password" column="password" />
<result property="createBy" column="create_by" />
<result property="createTime" column="create_time" />
<result property="updateBy" column="update_by" />
<result property="updateTime" column="update_time" />
<result property="remark" column="remark" />
</resultMap>
<resultMap type="java.lang.Integer" id="count">
<result column="total"/>
</resultMap>
<select id="selectList" resultMap="UserResult,count">
SELECT SQL_CALC_FOUND_ROWS a.user_id,a.user_name,a.nick_name,dept_name ,c.role_name
FROM sys_user a
LEFT JOIN sys_dept b on a.dept_id=b.dept_id
LEFT JOIN sys_role c on a.role_ids=c.role_id
<where>
<if test="userName != null and userName != ''">
AND u.user_name like concat('%', #{userName}, '%')
</if>
<if test="deptName != null and deptName != ''">
AND d.dept_name like concat('%', #{deptName}, '%')
</if>
</where>
order by create_time desc
LIMIT ${pageSize * (pageNum-1)},${pageSize};
SELECT FOUND_ROWS() AS total;
</select>
（2）UserMapper.java
List<Object> selectList(@Param("pageSize") Integer pageSize,
@Param("pageNum") Integer pageNum,
@Param("userName") String userName,
@Param("deptName") String deptName);
（3）UserService.java
public List<Object> selectList(Integer pageSize,Integer pageNum,String userName,String deptName) {
return userMapper.selectList(pageSize,pageNum,userName,deptName);
}
（4）UserController.java
/**
* 获取用户列表
* @return 用户列表
*/
@GetMapping("/list")
public ResultVo list(Integer pageSize, Integer pageNum,String userName,String deptName){
List<Object> objList = userService.selectList(pageSize, pageNum, userName,deptName);
List<User> list = (List<User>) objList.get(0);
int count = ((List<Integer>) objList.get(1)).get(0);//总数
Page<User> page = new Page<>(pageSize, pageNum);
page.setSize(pageSize);
page.setCurrent(pageNum);
page.setTotal(count);
//计算分页总页数
page.setPages(count %10 == 0 ? count/10 :count/10+1);
page.setRecords(list);
return ResultVoUtil.success(page);
}
最后，还需要在yml或propertis配置文件中，配置数据库连接池那里，在 url 中，加上 allowMultiQueries=true
2.4、mysql-mybatis-pagination组件
mysql-mybatis-pagination是一个基于MySQL方言 “SQL_CALC_FOUND_ROWS & FOUND_ROWS()” 的轻量级分页组件。
<dependency>
<groupId>io.github.flashvayne</groupId>
<artifactId>mysql-mybatis-pagination</artifactId>
<version>1.0.1</version>
</dependency>
项目github地址：mysql-mybatis-pagination
MySQL分页组件mysql-mybatis-pagination|Springboot|Mybatis | Vayne的博客
三、SQL_CALC_FOUND_ROWS与count(*) 性能对比
例子
mysql的SQL_CALC_FOUND_ROWS 使用 类似count(*) 使用性能更高
在很多分页的程序中都这样写:
SELECT COUNT(*) from `table` WHERE ......; 查出符合条件的记录总数
SELECT * FROM `table` WHERE ...... limit M,N; 查询当页要显示的数据
这样的语句可以改成:
SELECT SQL_CALC_FOUND_ROWS * FROM `table` WHERE ...... limit M, N;
SELECT FOUND_ROWS();
这样只要执行一次较耗时的复杂查询可以同时得到与不带limit同样的记录条数
第二个 SELECT返回一个数字，指示了在没有LIMIT子句的情况下，第一个SELECT返回了多少行 (若上述的 SELECT语句不包括 SQL_CALC_FOUND_ROWS 选项,则使用LIMIT 和不使用时，FOUND_ROWS() 可能会返回不同的结果）。http://blog.youkuaiyun.com/cuew1987/article/details/17393319
1.创建表：
//此处使用了覆盖索引
CREATE TABLE IF NOT EXISTS `Ben` (
`aa` int(10) unsigned NOT NULL AUTO_INCREMENT,
`bb` int(10) unsigned NOT NULL,
`cc` varchar(100) NOT NULL,
PRIMARY KEY (`aa`),
KEY `bar` (`bb`,`aa`)
) ENGINE=MyISAM;
//无覆盖索引
DROP TABLE IF EXISTS `ben`;
CREATE TABLE IF NOT EXISTS `ben` (
`aa` int(10) unsigned NOT NULL AUTO_INCREMENT,
`bb` int(10) unsigned NOT NULL,
`cc` varchar(100) NOT NULL,
PRIMARY KEY (`aa`),
KEY `bar` (`bb`)
) ENGINE=MyISAM;2.插入100万条数据：
//插入数据
function insertData(){
//数据表中插入100万条数据
$ben = new Model();
for($i = 0; $i < 1000000 ; $i++){
$a = $i % 2 == 0 ? 1 : 0;
$sql="insert into ben values (null,".$a.",'".md5($i)."')";
$aa = $ben->query($sql);
}
}3.读取数据，测试程序执行时间：
function read01(){
$start = microtime(true);
$ben = new Model();
$ben->query("select count(*) from ben where bb=1");
$ben->query("select aa from ben where bb=1 limit 100,10");
$end = microtime(true);
echo $end - $start; //覆盖索引：0.018204927444458，无覆盖索引：0.017701148986816
}
function read02(){
$start = microtime(true);
$ben = new Model();
$ben->query("select SQL_CALC_FOUND_ROWS aa from ben where bb=1 limit 100,10");
$ben->query("select FOUND_ROWS()");
$end = microtime(true);
echo $end - $start; //覆盖索引：0.017460823059082，无覆盖索引：0.20762395858765
}4.结论：
- 使用覆盖索引情况下，使用SQL_CALC_FOUND_ROWS性能较高。但是大多数情况，SQL_CALC_FOUND_ROWS 和 COUNT(*)的效率差不多；
- 无覆盖索引情况下，使用count(*)性能较高。在没有索引覆盖的情况下，SQL_CALC_FOUND_ROWS的效率要远低于COUNT(*)的效率，大概只有COUNT(*)的十分之一，甚至更低
- 另使用innoDB引擎比MyISAM低。
mysql8.x版本，建议不适用SQL_CALC_FOUND_ROWS
在mysql8.0版本的官方文档中，不推荐使用SQL_CALC_FOUND_ROWS选项，并计划在后续版本中废弃。
MySQL :: MySQL 8.0 Reference Manual :: 12.15 Information Functions
5、附：
资料一：覆盖索引
select aa from ben where bb = 1;
1)统计总数： select count(*) from ben where bb=1; 当我们在bb建立索引后，这个查询使用的就是覆盖索引（Covering Index）。
2)在分页操作中： select id,title,content from ben order by createtTime desc limit 10000,10;
通常这样的查询会把索引建在createdTime字段（其中id是主键），不过当limit偏移很大时，查询效率仍然很低,采用覆盖索引来提升性能： CREATE INDEX indexName ON ben ('createTime','id')，可以使用explain 查看是否是覆盖索引，出现extra:using index 就是
资料二：引擎MyISAM 和 InnoDB区别
InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型，这两个表类型各有优劣，视具体应用而定。基本的差别为：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。
MyISAM类型的表强调的是性能，其执行速度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持已经外部键等高级数据库功能。
以下是一些细节和具体实现的差别：
◆1.InnoDB不支持FULLTEXT类型的索引。
◆2.InnoDB 中不保存表的具体行数，也就是说，执行select count(*) from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的
是，当count(*)语句包含 where条件时，两种表的操作是一样的。
◆3.对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。
◆4.DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。
◆5.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性(例如外键)的表不
适用。
另外，InnoDB表的行锁也不是绝对的，假如在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 where name like “%aaa%”
两种类型最主要的差别就是Innodb 支持事务处理与外键和行级锁.而MyISAM不支持.所以MyISAM往往就容易被人认为只适合在小项目中使用。
我作为使用MySQL的用户角度出发，Innodb和MyISAM都是比较喜欢的，但是从我目前运维的数据库平台要达到需求：99.9%的稳定性，方便的扩展性和高可用性来说的话，MyISAM绝对是我
的首选。
原因如下：
1、首先我目前平台上承载的大部分项目是读多写少的项目，而MyISAM的读性能是比Innodb强不少的。
2、MyISAM的索引和数据是分开的，并且索引是有压缩的，内存使用率就对应提高了不少。能加载更多索引，而Innodb是索引和数据是紧密捆绑的，没有使用压缩从而会造成Innodb比
MyISAM体积庞大不小。
3、从平台角度来说，经常隔1，2个月就会发生应用开发人员不小心update一个表where写的范围不对，导致这个表没法正常用了，这个时候MyISAM的优越性就体现出来了，随便从当天拷
贝的压缩包取出对应表的文件，随便放到一个数据库目录下，然后dump成sql再导回到主库，并把对应的binlog补上。如果是Innodb，恐怕不可能有这么快速度，别和我说让Innodb定期用导出
xxx.sql机制备份，因为我平台上最小的一个数据库实例的数据量基本都是几十G大小。
4、从我接触的应用逻辑来说，select count(*) 和order by 是最频繁的，大概能占了整个sql总语句的60%以上的操作，而这种操作Innodb其实也是会锁表的，很多人以为Innodb是行级
锁，那个只是where对它主键是有效，非主键的都会锁全表的。
5、还有就是经常有很多应用部门需要我给他们定期某些表的数据，MyISAM的话很方便，只要发给他们对应那表的frm.MYD,MYI的文件，让他们自己在对应版本的数据库启动就行，而
Innodb就需要导出xxx.sql了，因为光给别人文件，受字典数据文件的影响，对方是无法使用的。
6、如果和MyISAM比insert写操作的话，Innodb还达不到MyISAM的写性能，如果是针对基于索引的update操作，虽然MyISAM可能会逊色Innodb,但是那么高并发的写，从库能否追的上也是
一个问题，还不如通过多实例分库分表架构来解决。
7、如果是用MyISAM的话，merge引擎可以大大加快应用部门的开发速度，他们只要对这个merge表做一些select count(*)操作，非常适合大项目总量约几亿的rows某一类型(如日志，调查
统计)的业务表。
当然Innodb也不是绝对不用，用事务的项目如模拟炒股项目，我就是用Innodb的，活跃用户20多万时候，也是很轻松应付了，因此我个人也是很喜欢Innodb的，只是如果从数据库平台应
用出发，我还是会首选MyISAM。
另外，可能有人会说你MyISAM无法抗太多写操作，但是我可以通过架构来弥补，说个我现有用的数据库平台容量：主从数据总量在几百T以上，每天十多亿 pv的动态页面，还有几个大项目是通过数据接口方式调用未算进pv总数，(其中包括一个大项目因为初期memcached没部署,导致单台数据库每天处理 9千万的查询)。而我的整体数据库服务器平均负载都在0.5-1左右。
四、参考
MyBatis或MyBatis-plus中分页查询同时查询数据和总数量_mybatisplus分页查询到的总数据量-优快云博客
MySQL分页组件mysql-mybatis-pagination|Springboot|Mybatis | Vayne的博客
https://www.cnblogs.com/FlyGoldfish/articles/16615357.html
扫一扫
1202
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
