本篇介绍Mybatis分页插件PageHelper的使用,本质上是Mybatis拦截器起作用,在执行目标SQL之前添加count语句及拼接limit到目标SQL后面,这里不再赘述,Mybatis拦截器相关知识可参考Mybatis——拦截器Interceptor,本篇仅简单介绍如何让一个使用Mybatis+springboot的项目快速接入PageHelper;
背景:接到需求,需要对一个老工程新写几个分页查询及导出的接口;查看了这个工程的代码,发现历史的分页接口竟然是手动拼出来的,非常之丑陋,准备引入Mybatis自带的分页插件,顺带记录下遇到的问题和最终引入PageHelper的步骤;
遇到问题1:根据经验,只需要引入一个PageHelper的二方包,在springboot下,会自动注册Mybatis Interceptor并生效;发现SQL可以正常执行,但是分页参数都没有填充;所以问题应该是PageHelper并没有被正确的加入Mybatis拦截器列表,这一步可以通过打印原始的SQL来验证,如果工程没有开启SQL日志,可参考《编码技巧——Mybatis输出SQL日志》;
解决方案:继续引入pagehelper-spring-boot-starter并在配置类加载PageHelper的Bean;
遇到问题2:按照上述方案成功的引入了pagehelper,并且分页参数都被正常的填充;但是当把分页插件原生的com.github.pagehelper.Page
解决方案:自己封装分页返参实体类;
(1)引入依赖
以下依赖建议放到dal模块:
-
- <dependency>
- <groupId>com.github.pagehelpergroupId>
- <artifactId>pagehelperartifactId>
- <version>5.2.0version>
- dependency>
以下依赖建议放到启动/配置boot模块
- <dependency>
- <groupId>com.github.pagehelpergroupId>
- <artifactId>pagehelper-spring-boot-starterartifactId>
- <version>1.2.12version>
- dependency>
(2)配置Bean
- @Configuration
- public class PageHelperConfig {
-
- @Bean
- public PageHelper pageHelper() {
- PageHelper pageHelper = new PageHelper();
- Properties properties = new Properties();
- properties.setProperty("offsetAsPageNum", "true");
- properties.setProperty("rowBoundsWithCount", "true");
- properties.setProperty("reasonable", "true");
- properties.setProperty("dialect", "mysql");
- pageHelper.setProperties(properties);
- return pageHelper;
- }
- }
(3)封装返参
- /**
- * @author Akira
- * @description 默认的分页查询的封装类
- */
- @Data
- public class DefaultPageDTO
{ - /**
- * 页码
- */
- private Integer pageNum;
-
- /**
- * 页面大小,默认size为10
- */
- private Integer pageSize;
-
- /**
- * 总数
- */
- private Long total;
-
- /**
- * 总页数
- */
- private Integer pages;
-
- /**
- * 查询记录列表
- */
- private List
records; -
- /**
- * 将com.github.pagehelper.Page封装成能打印出分页参数的DefaultPageDTO
- *
- * @param page
- * @param
- * @return
- */
- public static
DefaultPageDTO init(Page page, Function converter) { - DefaultPageDTO
defaultPageDTO = new DefaultPageDTO<>(); - defaultPageDTO.setPageNum(page.getPageNum());
- defaultPageDTO.setPages(page.getPages());
- defaultPageDTO.setTotal(page.getTotal());
- defaultPageDTO.setPageSize(page.getPageSize());
- if (page.getTotal() > 0) {
- defaultPageDTO.setRecords(page.getResult().stream().map(converter).collect(Collectors.toList()));
- }
- return defaultPageDTO;
- }
-
- }
(1)定义好DAO接口、xml、实体,如使用Mybatis代码生成器生成;
(2)定义controller层的查询实体,以及数据库层的查询实体,二者可以使用Bean转换工具MapStruct做转换;数据库层的查询实体可以继承原实体,并附带上一些额外的查询条件,如批量查询、时间区间、排序规则等;示例如下:
- @Data
- public class UserAuditFlowQuery extends UserAuditFlowDO {
-
- /**
- * 时间区间
- */
- private Date submitTimeLeft;
- private Date submitTimeRight;
-
- /**
- * 批量查询
- */
- private List
flowIdList; -
- /**
- * 排序规则
- */
- private Boolean orderByFlowId;
-
- }
(3)在xml中使用
- /**
- * 分页查询
- *
- * @param pageQuery
- * @return
- */
- public DefaultPageDTO
pageQueryMyReading(AuditFlowMyReadingQuery pageQuery) { - PageHelper.startPage(pageQuery.getPage(), pageQuery.getPageSize());
- final UserAuditFlowQuery query = beanConvertMapper.convert2UserAuditFlowQuery(pageQuery);
- final Page
page = userAuditFlowDAO.pageQueryByCondition(query); - final DefaultPageDTO
defaultPageDTO = DefaultPageDTO.init(page, domain -> beanConvertMapper.convert2UserAuditFlowDTO(domain)); - return defaultPageDTO;
- }
Page pageQueryByCondition(@Param("condition") UserAuditFlowQuery userAuditFlowQuery);
- <select id="pageQueryByCondition" resultMap="BaseResultMap">
- select
- <include refid="Base_Column_List"/>
- from user_audit_flow
- <where>
- <if test="condition.title != null and condition.title != ''">
- and `title` like "%"#{condition.title,jdbcType=VARCHAR}"%"
- if>
- <if test="condition.workNumber != null and condition.workNumber != ''">
- and `work_number` = #{condition.workNumber,jdbcType=VARCHAR}
- if>
- <if test="condition.roleCode != null and condition.roleCode != ''">
- and `role_code` = #{condition.roleCode,jdbcType=VARCHAR}
- if>
- <if test="condition.flowId != null">
- and `flow_id` = #{condition.flowId,jdbcType=BIGINT}
- if>
- <if test="condition.secondSubModuleCode != null and condition.secondSubModuleCode != ''">
- and `second_sub_module_code` = #{condition.secondSubModuleCode,jdbcType=VARCHAR}
- if>
- <if test="condition.auditStatus != null">
- and `audit_status` = #{condition.auditStatus,jdbcType=INTEGER}
- if>
- <if test="condition.submitor != null and condition.submitor != ''">
- and `submitor` = #{condition.submitor,jdbcType=VARCHAR}
- if>
- <if test="condition.submitTimeLeft != null and condition.submitTimeRight != null">
- and `create_time` between #{condition.submitTimeLeft,jdbcType=TIMESTAMP} and
- #{condition.submitTimeRight,jdbcType=TIMESTAMP}
- if>
- where>
-
- <if test="condition.orderByFlowId != null">
- <if test="condition.orderByFlowId == true">
- order by flow_id
- if>
- <if test="condition.orderByFlowId == false">
- order by flow_id desc
- if>
- if>
- select>
使用limit语句遍历表,当表数据量很大时,limit扫描的行数会越来越多,导致查询变慢;比如说limit 10000,10,MYSQL会去查询10010条数据,然后返回最后10条数据;当表数据量很大时,越往后需要查询的数据量越来越大,导致时间越来越长;
常用的解决方案有:
1. 记录主键id,例如顺序查询遍历表数据的场景,从上次查询结果中记录最后一位主键id,在查询语句中使用主键范围条件,减小回表次数;如:
select * from tableA where id > 10000 order by id limit 10;
2. 嵌套子查询,先查主键id,再根据选好的pageSize数量的主键id回表查,减小扫描的数据量;在有二级索引的情况下,查主键id会遍历空间大小更小二级索引树而非聚簇索引树,扫描的速度更快;注意,这种语句可能较低的MySQL版本不支持,可以在代码中分别执行2句SQL或改为使用join语句;
select * from tableA where id in (select id from tableA order by id LIMIT 10000,10);
改为:
- select id from audit_flow_main order by id LIMIT 10000,10;
- select * from audit_flow_main where id IN (...);
以上语句也可以通过使用join语句改写,原理是一样的:
SELECT * FROM tableA a JOIN (SELECT b.id FROM tableA b order by id LIMIT 5000,10) AS c on a.id = c.id;
注意:如果要按照主键id遍历数据,则需要在limit前加上order by id,否则查出来的数据与扫描主键索引查出来的数据不一致!原因很简单,二级索引有序,二级索引的叶子节点存放的主键id无序;
3. 使用between语句,如果仅遍历表数据,对每页数据大小没有严格要求时,如执行一些扫描表的定时任务时,推荐使用对主键做between,即手动算出每次查询的主键id区间,这样扫描的行数是最小的,效率也很高;一个定时任务的代码示例如下:
- public ExecutionResult execute(ExecutionContext executionContext) {
- long maxId = auditMainFlowMapper.selectMaxId();
- // 执行时就确定好id上限,仅刷新当前时刻的所有数据
- long idRight = maxId;
- long idLeft = Math.max(1, idRight - pageSize + 1);
- log.warn("任务开始");
- while (true) {
- if (idRight <= 1) {
- log.warn("任务跳出");
- break;
- }
- final QueryCondition queryCondition = new QueryCondition();
- queryCondition.setIdLeft(idLeft);
- queryCondition.setIdRight(idRight);
- final List
pageData = mDataMapper.selectByCondition(queryCondition); - if (CollectionUtils.isNotEmpty(pageData)) {
- try {
- doTaskForCurrentPage(pageData);
- } catch (Exception e) {
- log.warn("当前批次执行失败, e);
- }
- }
- // 翻页
- idRight = idRight - pageSize;
- idLeft = Math.max(1, idRight - pageSize + 1);
- }
- log.warn("任务结束");
- return ExecutionResult.SUCCESS;
- }
- <select id="selectByCondition" resultMap="BaseResultMap">
- select
- <include refid="Base_Column_List"/>
- from my_data
- <where>
- <if test="condition.idLeft != null and condition.idRight != null">
- and `id` between #{condition.idLeft,jdbcType=BIGINT} and #{condition.idRight,jdbcType=BIGINT}
- if>
- where>
- select>