动态SQL怎么提升性能

动态 SQL 本身并不会直接“提升性能”，用得不好反而会降低性能。所谓“提升性能”，通常是指通过动态 SQL 实现更合理的 SQL 生成、减少无效计算、利用索引、降低锁竞争等。下面从原理 + 常见优化手段 + 注意事项系统说明。

一、先澄清一个误区

❌ 动态 SQL ≠ 一定更快
✅ 动态 SQL = 更灵活，但性能取决于生成的 SQL 是否合理

例如：

• 错：动态拼接导致全表扫描
• 对：动态条件避免不必要的 OR 1=1、无用字段

二、动态 SQL 提升性能的典型场景

1️⃣ 避免“万能 SQL”导致的性能浪费

❌ 常见低效写法（MyBatis 示例）：

SELECT * FROM orders
WHERE 1=1
<if test="status != null">
  AND status = #{status}
</if>
<if test="createTime != null">
  AND create_time >= #{createTime}
</if>

问题：

• 1=1 无意义
• 条件复杂时优化器难选索引

✅ 优化写法：

SELECT * FROM orders
<where>
  <if test="status != null">
    AND status = #{status}
  </if>
  <if test="createTime != null">
    AND create_time >= #{createTime}
  </if>
</where>

✅ 好处：

• 减少无效条件
• SQL 更贴近真实查询
• 优化器更容易命中索引

2️⃣ 根据条件生成不同索引路径（关键）

✅ 场景：

• 有 status 索引
• 有 create_time 索引

❌ 一个 SQL 覆盖所有条件 → 索引失效
✅ 动态 SQL 拆成不同分支

<choose>
  <when test="status != null">
    SELECT * FROM orders WHERE status = #{status}
  </when>
  <when test="createTime != null">
    SELECT * FROM orders WHERE create_time >= #{createTime}
  </when>
  <otherwise>
    SELECT * FROM orders LIMIT 100
  </otherwise>
</choose>

✅ 好处：

• 每个分支都能走最优索引
• 避免 OR、function(status) 导致索引失效

3️⃣ 避免无效更新，减少锁和日志

❌ 全字段更新：

UPDATE user
SET name=#{name},
    age=#{age},
    email=#{email}
WHERE id=#{id}

✅ 动态更新（只更新变化字段）：

UPDATE user
<set>
  <if test="name != null">name=#{name},</if>
  <if test="age != null">age=#{age},</if>
  <if test="email != null">email=#{email},</if>
</set>
WHERE id=#{id}

✅ 好处：

• 减少 redo / undo 日志
• 减少行锁时间
• 减少主从复制压力

4️⃣ 动态分页 + 避免大分页

❌ 深分页：

SELECT * FROM orders
LIMIT 1000000, 20

✅ 动态 SQL + 游标分页：

SELECT * FROM orders
WHERE id > #{lastId}
ORDER BY id
LIMIT 20

✅ 好处：

• 避免 OFFSET 扫描大量数据
• 性能从 O(N) → O(1)

5️⃣ 动态表 / 分区选择（大数据量）

✅ 按时间 / 租户动态选择表：

SELECT * FROM order_${year}_${month}
WHERE user_id = #{userId}

✅ 好处：

• 减少单表数据量
• 提高索引效率
• 方便历史数据归档

三、动态 SQL 本身带来的性能风险（必须注意）

❌ 1. SQL 无法复用，硬解析增加

数据库对 完全相同 SQL 会缓存执行计划
动态 SQL 变化太多 → 执行计划缓存失效

✅ 对策：

• 控制动态分支数量
• 避免无意义差异（如空格、顺序）

❌ 2. 预编译失效（字符串拼接）

❌ 危险写法：

"SELECT * FROM user WHERE name = '" + name + "'"

✅ 正确写法（参数化）：

SELECT * FROM user WHERE name = #{name}

✅ 好处：

• 防 SQL 注入
• 利用预编译
• 提高性能

❌ 3. IN 条件过长

AND id IN
<foreach collection="ids" open="(" close=")" item="id">
  #{id}
</foreach>

✅ 优化：

• 限制 IN 数量（如 ≤ 1000）
• 拆批查询
• 用临时表 / JOIN

四、总结：动态 SQL 提升性能的核心原则

✅ 不是动态 SQL 更快，而是：

如果你愿意，可以告诉我：

• 用的是 MyBatis / JPA / JDBC / XML
• 具体 SQL 场景（查询 / 更新 / 分页 / 多条件）

我可以直接帮你改写并给出性能对比方案。