MyBatis-Plus复杂查询实战与优化技巧

1. MyBatis-Plus 复杂查询场景解析在真实业务开发中我们经常会遇到需要突破简单CRUD边界的查询需求。上周我刚接手一个电商促销系统改造就遇到了这样的典型场景需要根据用户等级、历史消费金额、商品类目等12个动态条件筛选商品同时还要关联查询库存表和促销活动表。这种场景下MyBatis-Plus的基础方法就显得力不从心了。MyBatis-Plus虽然提供了强大的Wrapper条件构造器但在处理以下三类场景时会遇到瓶颈多表关联查询特别是需要自定义关联条件时动态SQL片段拼接根据不同参数组合生成不同查询逻辑特殊SQL语法需求如WITH子句、窗口函数等提示当你的查询条件超过5个且存在动态组合时就该考虑自定义SQL方案了2. 自定义SQL的三种实现方式2.1 XML映射文件方案这是最传统也是功能最完整的方案。我在商品中心项目中使用的典型配置如下!-- 商品多条件搜索 -- select idsearchProducts resultTypeProductVO SELECT p.*, s.stock_count, a.activity_name FROM product p LEFT JOIN stock s ON p.id s.product_id LEFT JOIN activity a ON p.id a.product_id where if testparams.categoryId ! null AND p.category_id #{params.categoryId} /if if testparams.minPrice ! null AND p.price #{params.minPrice} /if !-- 动态标签可自由组合 -- /where ORDER BY choose when testparams.sortType price_ascp.price ASC/when when testparams.sortType sales_descp.sales DESC/when otherwisep.create_time DESC/otherwise /choose /select优势完整的动态SQL支持if/choose/foreach等标签清晰的SQL与Java代码分离支持复杂的结果集映射痛点需要维护额外的XML文件参数传递需要包装成Map或DTO重构时容易产生僵尸SQL2.2 注解方式实现对于简单的自定义SQL可以使用Select等注解直接编写Select(SELECT * FROM user WHERE dept_id #{deptId} AND status #{status}) ListUser findUsersByDept(Param(deptId) Long deptId, Param(status) Integer status);适用场景单表简单查询SQL语句固定不变的场景快速原型开发阶段注意当SQL超过3行时建议改用XML方式维护2.3 Wrapper结合自定义SQL这是我最推荐的折中方案在最近的门店管理系统中有成功实践Select(SELECT s.* FROM store s ${ew.customSqlSegment}) PageStoreVO selectStoresWithWrapper(Param(Constants.WRAPPER) QueryWrapperStore wrapper, PageStoreVO page); // 调用示例 wrapper.select(id,name,address) .like(name, 旗舰) .apply(distance(lat, lng, {0}, {1}) 5000, userLat, userLng) .orderByAsc(score);技术要点使用${ew.customSqlSegment}注入Wrapper条件Param(Constants.WRAPPER)注解声明Wrapper参数仍然可以使用Wrapper的select()方法指定返回字段3. 复杂查询实战技巧3.1 动态字段处理在数据报表导出功能中我遇到了需要根据用户权限动态显示字段的需求。解决方案public String buildDynamicSelect(SetString allowedFields) { String[] safeFields {id, create_time}; // 基础字段 ListString fieldList new ArrayList(Arrays.asList(safeFields)); // 权限校验 allowedFields.forEach(field - { if (VALID_FIELDS.contains(field)) { fieldList.add(field); } }); return String.join(,, fieldList); } // 在Wrapper中使用 wrapper.select(buildDynamicSelect(userFields));3.2 多表联查优化联查性能是复杂查询的常见瓶颈。我的经验是字段精确控制// 错误做法 - 查询全部字段 wrapper.select(*); // 正确做法 - 只查需要的字段 wrapper.select(u.id, u.name, d.dept_name);索引命中检查EXPLAIN SELECT ... -- 开发阶段必须检查执行计划关联查询替代方案// 先查主表 ListOrder orders orderMapper.selectList(orderWrapper); // 批量查询关联数据 ListLong orderIds orders.stream().map(Order::getId).collect(Collectors.toList()); MapLong, ListOrderItem itemsMap orderItemMapper.selectBatchIds(orderIds) .stream().collect(Collectors.groupingBy(OrderItem::getOrderId));3.3 分页查询陷阱MyBatis-Plus的分页在复杂SQL中可能会失效我采用的解决方案// 自定义分页拦截器 Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor new MybatisPlusInterceptor(); interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL){ Override protected void optimizeCount(IPage? page, MappedStatement ms, BoundSql boundSql) { // 重写count查询优化逻辑 } }); return interceptor; }4. 性能优化与监控4.1 SQL执行监控我在项目中集成了P6Spy进行SQL监控配置示例# application.yml spring: datasource: driver-class-name: com.p6spy.engine.spy.P6SpyDriver url: jdbc:p6spy:mysql://localhost:3306/db4.2 二级缓存策略对于相对静态的数据采用二级缓存CacheNamespace public interface ProductMapper extends BaseMapperProduct { Select(SELECT * FROM product WHERE category_id #{categoryId}) Options(useCache true, flushCache Options.FlushCachePolicy.FALSE) ListProduct findByCategory(Param(categoryId) Long categoryId); }4.3 批量操作优化处理十万级数据导入时我的优化方案Transactional public void batchInsert(ListData list) { SqlSession session sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH); try { DataMapper mapper session.getMapper(DataMapper.class); for (int i 0; i list.size(); i) { mapper.insert(list.get(i)); if (i % 500 0 || i list.size() - 1) { session.commit(); session.clearCache(); } } } finally { session.close(); } }5. 复杂查询设计模式5.1 查询条件构建器模式借鉴领域驱动设计思想我封装了这样的条件构建器public class ProductQueryBuilder { private ListLong categoryIds; private PriceRange priceRange; public ProductQueryBuilder withCategory(ListLong ids) { this.categoryIds ids; return this; } public QueryWrapperProduct build() { QueryWrapperProduct wrapper new QueryWrapper(); if (CollectionUtils.isNotEmpty(categoryIds)) { wrapper.in(category_id, categoryIds); } if (priceRange ! null) { wrapper.between(price, priceRange.getMin(), priceRange.getMax()); } return wrapper; } }5.2 装饰器模式增强Wrapper对于需要动态添加条件的场景public class WrapperDecorator { public static QueryWrapperUser withOrgFilter(QueryWrapperUser wrapper, Long orgId) { return wrapper.apply(EXISTS (SELECT 1 FROM org WHERE id {0} AND path LIKE CONCAT(%, user.org_path, %)), orgId); } }5.3 策略模式处理不同数据源在混合查询场景下public interface QueryStrategy { PageResult query(QueryParams params); } Component RequiredArgsConstructor public class QueryService { private final MapString, QueryStrategy strategyMap; public PageResult query(String sourceType, QueryParams params) { return strategyMap.get(sourceType QueryStrategy).query(params); } }6. 常见问题排查6.1 SQL注入防护在使用自定义SQL时必须注意// 危险做法 wrapper.apply(date_format(create_time,%Y-%m) month ); // 安全做法 wrapper.apply(date_format(create_time,%Y-%m) {0}, month);6.2 N1查询问题在测试环境发现的一个性能陷阱// 错误示例 ListOrder orders orderMapper.selectList(null); orders.forEach(order - { ListOrderItem items orderItemMapper.selectByOrderId(order.getId()); // ... }); // 解决方案使用TableField(select false)延迟加载 TableField(select false) private ListOrderItem items;6.3 枚举处理技巧对于数据库值与枚举的映射Getter public enum Status { ACTIVE(1), INACTIVE(0); private final int code; JsonCreator public static Status fromCode(int code) { return Arrays.stream(values()) .filter(e - e.code code) .findFirst() .orElse(null); } } // 在实体类中使用 TableField(typeHandler EnumValueTypeHandler.class) private Status status;7. 高级特性应用7.1 存储过程调用处理对账业务时的实践Select(CALL reconcile_accounts(#{date, modeIN}, #{result, modeOUT, jdbcTypeINTEGER})) Options(statementType StatementType.CALLABLE) void callReconcileProcedure(Param(date) String date, Param(result) Integer result);7.2 游标处理大数据量导出百万数据时的解决方案Select(SELECT * FROM large_table) Options(resultSetType ResultSetType.FORWARD_ONLY, fetchSize 1000) void streamData(ResultHandlerData handler);7.3 自定义类型处理器处理JSON字段的典型方案public class JsonTypeHandler extends BaseTypeHandlerMapString, Object { private final ObjectMapper mapper new ObjectMapper(); Override public void setNonNullParameter(PreparedStatement ps, int i, MapString, Object parameter, JdbcType jdbcType) { ps.setString(i, mapper.writeValueAsString(parameter)); } // 其他方法实现... } // 实体类中使用 TableField(typeHandler JsonTypeHandler.class) private MapString, Object attributes;8. 项目实战经验在最近的门店管理系统中我设计了一个动态报表查询模块核心设计如下查询配置表设计CREATE TABLE report_query ( id BIGINT PRIMARY KEY, query_name VARCHAR(100), base_sql TEXT, condition_fields JSON, result_fields JSON, created_time DATETIME );动态查询引擎public PageMapString, Object executeDynamicQuery(Long queryId, MapString, Object params) { ReportQuery query queryMapper.selectById(queryId); String finalSql buildFinalSql(query, params); return pageHelper.executeNativeQuery(finalSql, params); }SQL构建器private String buildFinalSql(ReportQuery query, MapString, Object params) { StringBuilder sql new StringBuilder(query.getBaseSql()); JSONArray conditions query.getConditionFields(); if (conditions ! null !conditions.isEmpty()) { sql.append( WHERE 11 ); for (int i 0; i conditions.size(); i) { JsonNode field conditions.get(i); String fieldName field.get(name).asText(); if (params.containsKey(fieldName)) { sql.append( AND ).append(fieldName) .append( ).append(field.get(operator).asText()) .append( #{).append(fieldName).append(}); } } } return sql.toString(); }这个方案最终支持了业务部门自主配置近百种报表查询减少了80%的重复开发工作。关键点在于将SQL模板与参数分离使用JSON存储字段配置严格的参数过滤防止注入查询结果缓存机制