Ruoyi-vue-plus-5.x第二篇MyBatis-Plus数据持久层技术:2.2 分页与性能优化
👋 大家好,我是 阿问学长
!专注于分享优质开源项目
解析、毕业设计项目指导
支持、幼小初高
的教辅资料
推荐等,欢迎关注交流!🚀
分页与性能优化
前言
在企业级应用中,数据分页和性能优化是数据访问层的重要组成部分。MyBatis-Plus提供了强大的分页插件和多种性能优化策略。RuoYi-Vue-Plus框架充分利用了这些特性,实现了高效的数据分页和查询优化。本文将深入探讨MyBatis-Plus的分页机制、性能优化技巧以及在实际项目中的应用。
PaginationInterceptor分页插件
分页插件配置
在RuoYi-Vue-Plus中,分页插件的配置如下:
@Configuration
@EnableTransactionManagement(proxyTargetClass = true)
public class MybatisPlusConfig {@Beanpublic MybatisPlusInterceptor mybatisPlusInterceptor() {MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();// 分页插件PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();// 设置数据库类型paginationInnerInterceptor.setDbType(DbType.MYSQL);// 设置最大单页限制数量,默认 500 条,-1 不受限制paginationInnerInterceptor.setMaxLimit(500L);// 溢出总页数后是否进行处理paginationInnerInterceptor.setOverflow(true);// 生成 countSql 优化掉 join 现在只支持 left joinpaginationInnerInterceptor.setOptimizeJoin(true);interceptor.addInnerInterceptor(paginationInnerInterceptor);// 多租户插件TenantLineInnerInterceptor tenantLineInnerInterceptor = new TenantLineInnerInterceptor();tenantLineInnerInterceptor.setTenantLineHandler(tenantLineHandler());interceptor.addInnerInterceptor(tenantLineInnerInterceptor);// 数据权限插件DataPermissionInterceptor dataPermissionInterceptor = new DataPermissionInterceptor();dataPermissionInterceptor.setDataPermissionHandler(dataPermissionHandler());interceptor.addInnerInterceptor(dataPermissionInterceptor);// 乐观锁插件OptimisticLockerInnerInterceptor optimisticLockerInnerInterceptor = new OptimisticLockerInnerInterceptor();interceptor.addInnerInterceptor(optimisticLockerInnerInterceptor);// 阻断插件BlockAttackInnerInterceptor blockAttackInnerInterceptor = new BlockAttackInnerInterceptor();interceptor.addInnerInterceptor(blockAttackInnerInterceptor);return interceptor;}/*** 分页插件自定义参数*/@Bean@ConditionalOnMissingBeanpublic PaginationInnerInterceptor paginationInnerInterceptor() {PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();// 分页合理化paginationInnerInterceptor.setOverflow(true);// 单页分页条数限制paginationInnerInterceptor.setMaxLimit(500L);// 数据库类型paginationInnerInterceptor.setDbType(DbType.MYSQL);return paginationInnerInterceptor;}
}
分页查询基础用法
@Service
public class SysUserServiceImpl extends ServiceImpl<SysUserMapper, SysUser> implements ISysUserService {/*** 基础分页查询*/public Page<SysUser> selectUserPage(int current, int size, SysUser user) {// 创建分页对象Page<SysUser> page = new Page<>(current, size);// 构建查询条件LambdaQueryWrapper<SysUser> wrapper = new LambdaQueryWrapper<>();wrapper.like(StringUtils.isNotBlank(user.getUserName()), SysUser::getUserName, user.getUserName());wrapper.eq(StringUtils.isNotBlank(user.getStatus()), SysUser::getStatus, user.getStatus());wrapper.orderByDesc(SysUser::getCreateTime);// 执行分页查询return baseMapper.selectPage(page, wrapper);}/*** 自定义分页查询*/public Page<SysUserVo> selectPageUserList(PageQuery pageQuery, SysUserBo user) {LambdaQueryWrapper<SysUser> lqw = buildQueryWrapper(user);Page<SysUserVo> result = baseMapper.selectPageUserList(pageQuery.build(), lqw);return result;}/*** 不查询总数的分页*/public Page<SysUser> selectUserPageWithoutCount(int current, int size, SysUser user) {// 创建分页对象,不查询总数Page<SysUser> page = new Page<>(current, size, false);LambdaQueryWrapper<SysUser> wrapper = new LambdaQueryWrapper<>();wrapper.like(StringUtils.isNotBlank(user.getUserName()), SysUser::getUserName, user.getUserName());return baseMapper.selectPage(page, wrapper);}
}
Page对象与分页参数
分页参数封装
/*** 分页查询实体类*/
@Data
public class PageQuery {/*** 分页大小*/@NotNull(message = "分页大小不能为空")@Min(value = 1, message = "分页大小最小值为 1")@Max(value = 100, message = "分页大小最大值为 100")private Integer pageSize;/*** 当前页数*/@NotNull(message = "页码不能为空")@Min(value = 1, message = "页码最小值为 1")private Integer pageNum;/*** 排序列*/private String orderByColumn;/*** 排序的方向desc或者asc*/private String isAsc;/*** 当前记录起始索引 默认值*/public static final int DEFAULT_PAGE_NUM = 1;/*** 每页显示记录数 默认值 默认查询所有*/public static final int DEFAULT_PAGE_SIZE = 20;/*** 构建分页对象*/public <T> Page<T> build() {Integer pageNum = ObjectUtil.defaultIfNull(getPageNum(), DEFAULT_PAGE_NUM);Integer pageSize = ObjectUtil.defaultIfNull(getPageSize(), DEFAULT_PAGE_SIZE);if (pageNum <= 0) {pageNum = DEFAULT_PAGE_NUM;}Page<T> page = new Page<>(pageNum, pageSize);// 处理排序List<OrderItem> orderItems = buildOrderItem();if (CollUtil.isNotEmpty(orderItems)) {page.addOrder(orderItems);}return page;}/*** 构建排序*/private List<OrderItem> buildOrderItem() {if (StringUtils.isBlank(orderByColumn) || StringUtils.isBlank(isAsc)) {return Collections.emptyList();}String orderBy = SqlUtil.escapeOrderBySql(orderByColumn);orderBy = StringUtils.toUnderScoreCase(orderBy);// 兼容前端排序类型isAsc = StringUtils.replaceEach(isAsc, new String[]{"ascending", "descending"}, new String[]{"asc", "desc"});OrderItem orderItem = new OrderItem();orderItem.setColumn(orderBy);orderItem.setAsc("asc".equals(isAsc));return Collections.singletonList(orderItem);}
}
分页结果封装
/*** 表格分页数据对象*/
@Data
public class TableDataInfo<T> implements Serializable {private static final long serialVersionUID = 1L;/*** 总记录数*/private long total;/*** 列表数据*/private List<T> rows;/*** 消息状态码*/private int code;/*** 消息内容*/private String msg;/*** 分页*/public static <T> TableDataInfo<T> build(Page<T> page) {TableDataInfo<T> rspData = new TableDataInfo<>();rspData.setCode(HttpStatus.SUCCESS);rspData.setMsg("查询成功");rspData.setRows(page.getRecords());rspData.setTotal(page.getTotal());return rspData;}public static <T> TableDataInfo<T> build(List<T> list) {TableDataInfo<T> rspData = new TableDataInfo<>();rspData.setCode(HttpStatus.SUCCESS);rspData.setMsg("查询成功");rspData.setRows(list);rspData.setTotal(list.size());return rspData;}public static <T> TableDataInfo<T> build() {TableDataInfo<T> rspData = new TableDataInfo<>();rspData.setCode(HttpStatus.SUCCESS);rspData.setMsg("查询成功");rspData.setRows(Collections.emptyList());rspData.setTotal(0);return rspData;}
}
Controller层分页使用
@RestController
@RequestMapping("/system/user")
public class SysUserController extends BaseController {@Autowiredprivate ISysUserService userService;/*** 获取用户列表*/@SaCheckPermission("system:user:list")@GetMapping("/list")public TableDataInfo<SysUserVo> list(SysUserBo user, PageQuery pageQuery) {return userService.selectPageUserList(user, pageQuery);}/*** 导出用户列表*/@SaCheckPermission("system:user:export")@PostMapping("/export")public void export(SysUserBo user, HttpServletResponse response) {List<SysUserVo> list = userService.selectUserList(user);ExcelUtil.exportExcel(list, "用户数据", SysUserVo.class, response);}
}
自定义分页SQL优化
复杂查询分页优化
@Mapper
public interface SysUserMapper extends BaseMapper<SysUser> {/*** 自定义分页查询 - 关联查询优化*/@Select("""<script>SELECT u.user_id, u.user_name, u.nick_name, u.email, u.phonenumber,u.sex, u.avatar, u.status, u.create_time, u.remark,d.dept_name, d.leaderFROM sys_user uLEFT JOIN sys_dept d ON u.dept_id = d.dept_id<where>u.del_flag = '0'<if test="queryWrapper.sqlSegment != null and queryWrapper.sqlSegment != ''">AND ${queryWrapper.sqlSegment}</if></where>ORDER BY u.create_time DESC</script>""")@DataPermission({@DataColumn(key = "deptName", value = "d.dept_id"),@DataColumn(key = "userName", value = "u.user_id")})Page<SysUserVo> selectPageUserList(@Param("page") Page<SysUser> page, @Param("queryWrapper") Wrapper<SysUser> queryWrapper);/*** 统计查询优化*/@Select("""SELECT COUNT(DISTINCT u.user_id) as total,COUNT(CASE WHEN u.status = '0' THEN 1 END) as activeCount,COUNT(CASE WHEN u.status = '1' THEN 1 END) as inactiveCountFROM sys_user uLEFT JOIN sys_dept d ON u.dept_id = d.dept_idWHERE u.del_flag = '0'${ew.customSqlSegment}""")UserStatisticsVo selectUserStatistics(@Param("ew") Wrapper<SysUser> queryWrapper);/*** 大数据量分页查询优化 - 使用子查询*/@Select("""SELECT u.*, d.dept_name FROM sys_user uLEFT JOIN sys_dept d ON u.dept_id = d.dept_idWHERE u.user_id IN (SELECT user_id FROM (SELECT user_id FROM sys_user WHERE del_flag = '0' ${ew.customSqlSegment}LIMIT #{page.offset}, #{page.size}) temp)ORDER BY u.create_time DESC""")List<SysUserVo> selectPageUserListOptimized(@Param("page") Page<SysUser> page, @Param("ew") Wrapper<SysUser> queryWrapper);
}
分页性能监控
@Component
public class PagePerformanceInterceptor implements Interceptor {private static final Logger log = LoggerFactory.getLogger(PagePerformanceInterceptor.class);@Overridepublic Object intercept(Invocation invocation) throws Throwable {long startTime = System.currentTimeMillis();try {Object result = invocation.proceed();long endTime = System.currentTimeMillis();long executionTime = endTime - startTime;// 记录慢查询if (executionTime > 1000) { // 超过1秒的查询MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];String sqlId = mappedStatement.getId();log.warn("慢查询检测: SQL ID: {}, 执行时间: {}ms", sqlId, executionTime);// 发送告警sendSlowQueryAlert(sqlId, executionTime);}return result;} catch (Exception e) {log.error("SQL执行异常", e);throw e;}}private void sendSlowQueryAlert(String sqlId, long executionTime) {// 实现告警逻辑SlowQueryAlert alert = new SlowQueryAlert();alert.setSqlId(sqlId);alert.setExecutionTime(executionTime);alert.setTimestamp(new Date());// 异步发送告警AsyncManager.me().execute(() -> {alertService.sendSlowQueryAlert(alert);});}@Overridepublic Object plugin(Object target) {return Plugin.wrap(target, this);}@Overridepublic void setProperties(Properties properties) {// 设置属性}
}
大数据量分页性能调优
深分页优化策略
@Service
public class LargeDataPageService {@Autowiredprivate SysUserMapper userMapper;/*** 传统分页 - 性能较差*/public Page<SysUser> traditionalPage(int pageNum, int pageSize) {Page<SysUser> page = new Page<>(pageNum, pageSize);LambdaQueryWrapper<SysUser> wrapper = new LambdaQueryWrapper<>();wrapper.eq(SysUser::getDelFlag, "0");wrapper.orderByDesc(SysUser::getCreateTime);return userMapper.selectPage(page, wrapper);}/*** 游标分页 - 适用于大数据量*/public List<SysUser> cursorPage(Long lastId, int pageSize) {LambdaQueryWrapper<SysUser> wrapper = new LambdaQueryWrapper<>();wrapper.eq(SysUser::getDelFlag, "0");if (lastId != null) {wrapper.lt(SysUser::getUserId, lastId);}wrapper.orderByDesc(SysUser::getUserId);wrapper.last("LIMIT " + pageSize);return userMapper.selectList(wrapper);}/*** 基于时间的游标分页*/public List<SysUser> timeCursorPage(Date lastCreateTime, int pageSize) {LambdaQueryWrapper<SysUser> wrapper = new LambdaQueryWrapper<>();wrapper.eq(SysUser::getDelFlag, "0");if (lastCreateTime != null) {wrapper.lt(SysUser::getCreateTime, lastCreateTime);}wrapper.orderByDesc(SysUser::getCreateTime);wrapper.last("LIMIT " + pageSize);return userMapper.selectList(wrapper);}/*** 子查询优化分页*/public Page<SysUserVo> optimizedPage(int pageNum, int pageSize, SysUserBo user) {// 先查询ID列表Page<Long> idPage = new Page<>(pageNum, pageSize);LambdaQueryWrapper<SysUser> idWrapper = new LambdaQueryWrapper<>();idWrapper.select(SysUser::getUserId);idWrapper.eq(SysUser::getDelFlag, "0");// 添加查询条件if (StringUtils.isNotBlank(user.getUserName())) {idWrapper.like(SysUser::getUserName, user.getUserName());}if (StringUtils.isNotBlank(user.getStatus())) {idWrapper.eq(SysUser::getStatus, user.getStatus());}idWrapper.orderByDesc(SysUser::getCreateTime);Page<SysUser> idResult = userMapper.selectPage(idPage, idWrapper);if (CollUtil.isEmpty(idResult.getRecords())) {return new Page<>(pageNum, pageSize, 0);}// 根据ID列表查询完整数据List<Long> userIds = idResult.getRecords().stream().map(SysUser::getUserId).collect(Collectors.toList());List<SysUserVo> userList = userMapper.selectUserListByIds(userIds);// 构建分页结果Page<SysUserVo> result = new Page<>(pageNum, pageSize, idResult.getTotal());result.setRecords(userList);return result;}/*** 缓存分页结果*/@Cacheable(value = "userPage", key = "#pageNum + '_' + #pageSize + '_' + #user.hashCode()")public Page<SysUserVo> cachedPage(int pageNum, int pageSize, SysUserBo user) {return selectPageUserList(pageNum, pageSize, user);}/*** 异步预加载下一页*/@Asyncpublic CompletableFuture<Page<SysUserVo>> preloadNextPage(int currentPage, int pageSize, SysUserBo user) {int nextPage = currentPage + 1;Page<SysUserVo> nextPageData = selectPageUserList(nextPage, pageSize, user);// 缓存下一页数据String cacheKey = "userPage:" + nextPage + "_" + pageSize + "_" + user.hashCode();redisTemplate.opsForValue().set(cacheKey, nextPageData, Duration.ofMinutes(5));return CompletableFuture.completedFuture(nextPageData);}
}
索引优化建议
-- 用户表索引优化
-- 1. 主键索引(自动创建)
ALTER TABLE sys_user ADD PRIMARY KEY (user_id);-- 2. 唯一索引
ALTER TABLE sys_user ADD UNIQUE INDEX uk_user_name (user_name);
ALTER TABLE sys_user ADD UNIQUE INDEX uk_phone (phonenumber);
ALTER TABLE sys_user ADD UNIQUE INDEX uk_email (email);-- 3. 普通索引
ALTER TABLE sys_user ADD INDEX idx_dept_id (dept_id);
ALTER TABLE sys_user ADD INDEX idx_status (status);
ALTER TABLE sys_user ADD INDEX idx_create_time (create_time);
ALTER TABLE sys_user ADD INDEX idx_del_flag (del_flag);-- 4. 复合索引(根据查询条件组合)
ALTER TABLE sys_user ADD INDEX idx_status_dept_create (status, dept_id, create_time);
ALTER TABLE sys_user ADD INDEX idx_del_status_create (del_flag, status, create_time);-- 5. 覆盖索引(包含查询所需的所有字段)
ALTER TABLE sys_user ADD INDEX idx_cover_list (del_flag, status, dept_id, user_id, user_name, nick_name, create_time);-- 6. 分页查询优化索引
ALTER TABLE sys_user ADD INDEX idx_page_optimize (del_flag, create_time, user_id);
分页缓存策略
@Service
public class PageCacheService {@Autowiredprivate RedisTemplate<String, Object> redisTemplate;private static final String PAGE_CACHE_PREFIX = "page:cache:";private static final Duration CACHE_DURATION = Duration.ofMinutes(10);/*** 获取分页缓存*/public <T> Page<T> getPageCache(String cacheKey, Class<T> clazz) {try {Object cached = redisTemplate.opsForValue().get(PAGE_CACHE_PREFIX + cacheKey);if (cached != null) {return (Page<T>) cached;}} catch (Exception e) {log.warn("获取分页缓存失败: {}", e.getMessage());}return null;}/*** 设置分页缓存*/public <T> void setPageCache(String cacheKey, Page<T> page) {try {redisTemplate.opsForValue().set(PAGE_CACHE_PREFIX + cacheKey, page, CACHE_DURATION);} catch (Exception e) {log.warn("设置分页缓存失败: {}", e.getMessage());}}/*** 清除分页缓存*/public void clearPageCache(String pattern) {try {Set<String> keys = redisTemplate.keys(PAGE_CACHE_PREFIX + pattern + "*");if (CollUtil.isNotEmpty(keys)) {redisTemplate.delete(keys);}} catch (Exception e) {log.warn("清除分页缓存失败: {}", e.getMessage());}}/*** 智能分页缓存*/public <T> Page<T> smartPageCache(String baseKey, Supplier<Page<T>> pageSupplier) {String cacheKey = generateCacheKey(baseKey);// 尝试从缓存获取Page<T> cachedPage = getPageCache(cacheKey, null);if (cachedPage != null) {return cachedPage;}// 缓存未命中,执行查询Page<T> page = pageSupplier.get();// 只缓存前几页的数据if (page.getCurrent() <= 5) {setPageCache(cacheKey, page);}return page;}private String generateCacheKey(String baseKey) {// 生成缓存键的逻辑return baseKey + ":" + System.currentTimeMillis() / (5 * 60 * 1000); // 5分钟为一个时间段}
}
分页查询最佳实践
分页参数校验
@Component
public class PageValidator {private static final int MAX_PAGE_SIZE = 500;private static final int DEFAULT_PAGE_SIZE = 20;/*** 校验分页参数*/public PageQuery validatePageQuery(PageQuery pageQuery) {if (pageQuery == null) {pageQuery = new PageQuery();}// 校验页码if (pageQuery.getPageNum() == null || pageQuery.getPageNum() < 1) {pageQuery.setPageNum(1);}// 校验页大小if (pageQuery.getPageSize() == null || pageQuery.getPageSize() < 1) {pageQuery.setPageSize(DEFAULT_PAGE_SIZE);}if (pageQuery.getPageSize() > MAX_PAGE_SIZE) {pageQuery.setPageSize(MAX_PAGE_SIZE);}// 校验排序字段if (StringUtils.isNotBlank(pageQuery.getOrderByColumn())) {String orderBy = SqlUtil.escapeOrderBySql(pageQuery.getOrderByColumn());pageQuery.setOrderByColumn(orderBy);}return pageQuery;}/*** 检查是否为深分页*/public boolean isDeepPaging(PageQuery pageQuery) {return pageQuery.getPageNum() * pageQuery.getPageSize() > 10000;}/*** 深分页警告*/public void warnDeepPaging(PageQuery pageQuery, String operation) {if (isDeepPaging(pageQuery)) {log.warn("深分页查询警告: 操作={}, 页码={}, 页大小={}, 偏移量={}", operation, pageQuery.getPageNum(), pageQuery.getPageSize(),pageQuery.getPageNum() * pageQuery.getPageSize());}}
}
分页性能监控
@Aspect
@Component
public class PagePerformanceAspect {private static final Logger log = LoggerFactory.getLogger(PagePerformanceAspect.class);@Around("execution(* *..*Service.selectPage*(..))")public Object monitorPageQuery(ProceedingJoinPoint joinPoint) throws Throwable {long startTime = System.currentTimeMillis();String methodName = joinPoint.getSignature().getName();Object[] args = joinPoint.getArgs();try {Object result = joinPoint.proceed();long endTime = System.currentTimeMillis();long executionTime = endTime - startTime;// 记录分页查询性能logPagePerformance(methodName, args, executionTime, result);return result;} catch (Exception e) {log.error("分页查询异常: method={}, args={}", methodName, args, e);throw e;}}private void logPagePerformance(String methodName, Object[] args, long executionTime, Object result) {if (result instanceof Page) {Page<?> page = (Page<?>) result;// 记录性能指标PagePerformanceMetrics metrics = new PagePerformanceMetrics();metrics.setMethodName(methodName);metrics.setExecutionTime(executionTime);metrics.setPageNum(page.getCurrent());metrics.setPageSize(page.getSize());metrics.setTotal(page.getTotal());metrics.setRecordCount(page.getRecords().size());// 慢查询告警if (executionTime > 2000) {log.warn("分页查询性能告警: {}", metrics);sendPerformanceAlert(metrics);} else {log.debug("分页查询性能: {}", metrics);}}}private void sendPerformanceAlert(PagePerformanceMetrics metrics) {// 发送性能告警AsyncManager.me().execute(() -> {performanceAlertService.sendPagePerformanceAlert(metrics);});}
}/*** 分页性能指标*/
@Data
public class PagePerformanceMetrics {private String methodName;private long executionTime;private long pageNum;private long pageSize;private long total;private int recordCount;private Date timestamp = new Date();
}
总结
本文深入介绍了MyBatis-Plus的分页与性能优化,包括:
- 分页插件配置:PaginationInnerInterceptor的详细配置和使用
- 分页对象使用:Page对象、PageQuery参数封装、TableDataInfo结果封装
- 自定义分页SQL:复杂查询的分页优化策略
- 大数据量优化:深分页问题的解决方案,包括游标分页、子查询优化等
- 性能监控:分页查询的性能监控和告警机制
- 最佳实践:分页参数校验、缓存策略、索引优化等
通过这些优化策略,可以显著提升大数据量场景下的分页查询性能,为用户提供更好的使用体验。
在下一篇文章中,我们将探讨MyBatis-Plus的多租户与数据权限功能。
参考资料
- MyBatis-Plus分页插件文档
- MySQL分页优化最佳实践
- RuoYi-Vue-Plus分页实现源码