MyBatis 复杂动态 SQL 的性能优化与安全实践

题目

MyBatis 复杂动态 SQL 的性能优化与安全实践

信息

• 类型：问答
• 难度：⭐⭐⭐

考点

动态SQL深度应用,性能优化策略,SQL注入防范,高级映射处理

快速回答

在处理复杂动态SQL时，需要重点关注：

• 使用<where>和<set>标签智能处理空条件
• 通过<foreach>的batchSize分批次处理避免数据库IN列表限制
• 优先使用#{}防止SQL注入，避免${}的误用
• 利用二级缓存和fetchSize优化大数据查询
• 使用<bind>预处理复杂表达式提升可读性

场景描述

假设需要实现一个电商订单的多条件动态查询：支持订单状态、时间范围、商品ID列表（最多5000个）、用户属性等20+条件的任意组合查询，同时要求防范SQL注入并保证高并发下的性能。

核心解决方案

1. 动态SQL构建（XML映射文件）

<select id="searchOrders" resultMap="orderDetailMap">
  <bind name="validStartDate" value="startDate != null ? startDate : '1970-01-01'" />
  SELECT /*+ INDEX(orders idx_status) */ o.*, u.name 
  FROM orders o
  JOIN users u ON o.user_id = u.id
  <where>
    <if test="status != null">
      o.status = #{status}
    </if>
    <if test="productIds != null and productIds.size() > 0">
      AND o.id IN (
        SELECT order_id FROM order_items
        WHERE product_id IN
        <foreach collection="productIds" item="id" open="(" separator="," close=")">
          #{id}
        </foreach>
      )
    </if>
    AND o.created_time BETWEEN #{validStartDate} AND #{endDate}
  </where>
  ORDER BY o.created_time DESC
  <if test="limit != null">
    LIMIT #{limit}
  </if>
</select>

2. 性能优化策略

• 分批次IN查询：当productIds超过1000时进行分组处理

  // Java代码处理大列表
  List<Long> queryProductIds(productIds) {
    return Lists.partition(productIds, 1000).stream()
      .flatMap(chunk -> orderMapper.findOrderIdsByProducts(chunk).stream())
      .distinct()
      .collect(Collectors.toList());
  }

• 强制索引提示：通过/*+ INDEX() */引导优化器
• 分页优化：避免offset过大，改用WHERE id > last_seen_id
• 连接池配置：使用HikariCP并设置合理的maximumPoolSize

3. 安全防护措施

• 所有动态参数必须使用#{}占位符
• 禁止拼接SQL片段：ORDER BY ${fieldName}改为白名单校验

  // 安全排序字段校验
  private static final Set<String> SAFE_SORT_FIELDS = Set.of("id", "amount");
  
  if (!SAFE_SORT_FIELDS.contains(fieldName)) {
    throw new IllegalArgumentException("Invalid sort field");
  }

• 复杂表达式使用<bind>预处理

4. 高级映射技巧

<resultMap id="orderDetailMap" type="Order">
  <id property="id" column="order_id"/>
  <result property="amount" column="order_amount"/>
  <association property="user" javaType="User">
    <result property="name" column="user_name"/>
  </association>
  <collection property="items" select="selectItemsByOrder" column="order_id"
              fetchType="lazy"/>
</resultMap>

最佳实践

• 动态SQL原则：保持SQL可读性，避免过度动态化
• 批处理：大数据写入使用ExecutorType.BATCH
• 监控：启用MyBatis SQL日志并接入APM工具
• 缓存策略：结合Redis实现二级缓存，注意缓存击穿保护

常见错误

• 在<foreach>中误用${}导致SQL注入
• N+1查询问题：未合理配置fetchType或@FetchMode
• 动态条件缺少<where>标签导致WHERE关键字错误
• 忽略大列表查询的数据库限制（如Oracle IN列表上限1000）

扩展知识

• MyBatis-Spring事务管理：@Transactional的传播机制
• 插件开发：通过Interceptor实现SQL改写/分表逻辑
• 多租户方案：结合ThreadLocal自动添加tenant_id条件
• Kotlin DSL：使用MyBatis动态SQL DSL替代XML配置