面试重要程度：⭐⭐⭐⭐⭐

常见提问方式：动态SQL标签使用、批量操作优化、SQL性能调优

预计阅读时间：30分钟

开场白

兄弟，动态SQL绝对是MyBatis的核心特性！面试官特别喜欢考这个，因为它直接体现你对复杂业务场景的处理能力。特别是批量操作优化，这在实际项目中太重要了！

今天我们就把动态SQL的各种标签用法和性能优化技巧全部搞定，让你在面试中展现出对数据访问层的深度理解。

🔄 动态SQL标签详解

核心标签全解析

面试必问：

面试官："MyBatis的动态SQL有哪些标签？如何处理复杂的查询条件？"

1. if标签 - 条件判断

<mapper namespace="com.example.mapper.UserMapper">
    
    <!-- 基础条件查询 -->
    <select id="selectByCondition" resultType="User">
        SELECT * FROM user
        WHERE 1=1
        <if test="name != null and name != ''">
            AND name LIKE CONCAT('%', #{name}, '%')
        </if>
        <if test="age != null">
            AND age = #{age}
        </if>
        <if test="status != null">
            AND status = #{status}
        </if>
        <if test="createTimeStart != null">
            AND create_time >= #{createTimeStart}
        </if>
        <if test="createTimeEnd != null">
            AND create_time <= #{createTimeEnd}
        </if>
    </select>
    
    <!-- 复杂条件判断 -->
    <select id="selectByComplexCondition" resultType="User">
        SELECT * FROM user
        WHERE 1=1
        <if test="condition != null">
            <if test="condition.name != null and condition.name.trim() != ''">
                AND name LIKE CONCAT('%', #{condition.name}, '%')
            </if>
            <if test="condition.ageRange != null">
                <if test="condition.ageRange.min != null">
                    AND age >= #{condition.ageRange.min}
                </if>
                <if test="condition.ageRange.max != null">
                    AND age <= #{condition.ageRange.max}
                </if>
            </if>
            <if test="condition.statusList != null and condition.statusList.size() > 0">
                AND status IN
                <foreach collection="condition.statusList" item="status" open="(" separator="," close=")">
                    #{status}
                </foreach>
            </if>
        </if>
    </select>
    
</mapper>

2. where标签 - 智能WHERE处理

<!-- where标签自动处理AND/OR -->
<select id="selectByConditionWithWhere" resultType="User">
    SELECT * FROM user
    <where>
        <if test="name != null and name != ''">
            AND name LIKE CONCAT('%', #{name}, '%')
        </if>
        <if test="age != null">
            AND age = #{age}
        </if>
        <if test="status != null">
            AND status = #{status}
        </if>
        <if test="email != null and email != ''">
            AND email = #{email}
        </if>
    </where>
    ORDER BY create_time DESC
</select>

<!-- 对比：不使用where标签的问题 -->
<select id="badExample" resultType="User">
    SELECT * FROM user
    WHERE 
    <if test="name != null and name != ''">
        name LIKE CONCAT('%', #{name}, '%')
    </if>
    <if test="age != null">
        AND age = #{age}  <!-- 如果name为空，这里会出现语法错误 -->
    </if>
</select>

3. choose/when/otherwise - 多分支选择

<!-- 类似Java的switch-case -->
<select id="selectByPriority" resultType="User">
    SELECT * FROM user
    <where>
        <choose>
            <when test="id != null">
                id = #{id}
            </when>
            <when test="email != null and email != ''">
                email = #{email}
            </when>
            <when test="phone != null and phone != ''">
                phone = #{phone}
            </when>
            <when test="username != null and username != ''">
                username = #{username}
            </when>
            <otherwise>
                status = 1  <!-- 默认查询激活用户 -->
            </otherwise>
        </choose>
    </where>
</select>

<!-- 复杂业务场景应用 -->
<select id="selectByUserType" resultType="User">
    SELECT 
        <choose>
            <when test="userType == 'VIP'">
                id, name, email, vip_level, vip_expire_time
            </when>
            <when test="userType == 'ADMIN'">
                id, name, email, role, permissions
            </when>
            <otherwise>
                id, name, email
            </otherwise>
        </choose>
    FROM user
    <where>
        <choose>
            <when test="userType == 'VIP'">
                vip_level > 0 AND vip_expire_time > NOW()
            </when>
            <when test="userType == 'ADMIN'">
                role IN ('ADMIN', 'SUPER_ADMIN')
            </when>
            <otherwise>
                status = 1
            </otherwise>
        </choose>
    </where>
</select>

4. foreach标签 - 循环处理

<!-- IN查询 -->
<select id="selectByIds" resultType="User">
    SELECT * FROM user
    WHERE id IN
    <foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

<!-- 批量条件查询 -->
<select id="selectByMultipleConditions" resultType="User">
    SELECT * FROM user
    WHERE 
    <foreach collection="conditions" item="condition" separator=" OR ">
        (name = #{condition.name} AND age = #{condition.age})
    </foreach>
</select>

<!-- 动态表名查询（注意SQL注入风险） -->
<select id="selectFromMultipleTables" resultType="Map">
    <foreach collection="tableNames" item="tableName" separator=" UNION ALL ">
        SELECT '${tableName}' as table_name, COUNT(*) as count FROM ${tableName}
    </foreach>
</select>

<!-- 批量插入VALUES -->
<insert id="batchInsert" parameterType="list">
    INSERT INTO user (name, age, email, create_time)
    VALUES
    <foreach collection="list" item="user" separator=",">
        (#{user.name}, #{user.age}, #{user.email}, NOW())
    </foreach>
</insert>

5. set标签 - 动态更新

<!-- 动态更新字段 -->
<update id="updateSelective">
    UPDATE user
    <set>
        <if test="name != null and name != ''">
            name = #{name},
        </if>
        <if test="age != null">
            age = #{age},
        </if>
        <if test="email != null and email != ''">
            email = #{email},
        </if>
        <if test="phone != null and phone != ''">
            phone = #{phone},
        </if>
        <if test="status != null">
            status = #{status},
        </if>
        update_time = NOW()  <!-- 总是更新修改时间 -->
    </set>
    WHERE id = #{id}
</update>

<!-- 批量更新不同字段 -->
<update id="batchUpdateSelective">
    <foreach collection="users" item="user" separator=";">
        UPDATE user
        <set>
            <if test="user.name != null and user.name != ''">
                name = #{user.name},
            </if>
            <if test="user.age != null">
                age = #{user.age},
            </if>
            <if test="user.email != null and user.email != ''">
                email = #{user.email},
            </if>
            update_time = NOW()
        </set>
        WHERE id = #{user.id}
    </foreach>
</update>

6. trim标签 - 自定义格式化

<!-- 自定义前缀和后缀处理 -->
<select id="selectByConditionWithTrim" resultType="User">
    SELECT * FROM user
    <trim prefix="WHERE" prefixOverrides="AND |OR ">
        <if test="name != null and name != ''">
            AND name = #{name}
        </if>
        <if test="age != null">
            AND age = #{age}
        </if>
        <if test="status != null">
            OR status = #{status}
        </if>
    </trim>
</select>

<!-- 复杂的trim应用 -->
<insert id="insertSelective">
    INSERT INTO user
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="name != null">name,</if>
        <if test="age != null">age,</if>
        <if test="email != null">email,</if>
        <if test="phone != null">phone,</if>
        create_time,
    </trim>
    <trim prefix="VALUES (" suffix=")" suffixOverrides=",">
        <if test="name != null">#{name},</if>
        <if test="age != null">#{age},</if>
        <if test="email != null">#{email},</if>
        <if test="phone != null">#{phone},</if>
        NOW(),
    </trim>
</insert>

SQL片段复用

面试加分项：

<!-- 定义可复用的SQL片段 -->
<sql id="userColumns">
    id, name, age, email, phone, status, create_time, update_time
</sql>

<sql id="userConditions">
    <where>
        <if test="name != null and name != ''">
            AND name LIKE CONCAT('%', #{name}, '%')
        </if>
        <if test="ageMin != null">
            AND age >= #{ageMin}
        </if>
        <if test="ageMax != null">
            AND age <= #{ageMax}
        </if>
        <if test="statuses != null and statuses.size() > 0">
            AND status IN
            <foreach collection="statuses" item="status" open="(" separator="," close=")">
                #{status}
            </foreach>
        </if>
        <if test="createTimeStart != null">
            AND create_time >= #{createTimeStart}
        </if>
        <if test="createTimeEnd != null">
            AND create_time <= #{createTimeEnd}
        </if>
    </where>
</sql>

<!-- 使用SQL片段 -->
<select id="selectByComplexCondition" resultType="User">
    SELECT <include refid="userColumns"/>
    FROM user
    <include refid="userConditions"/>
    <if test="orderBy != null and orderBy != ''">
        ORDER BY ${orderBy}
    </if>
    <if test="limit != null and limit > 0">
        LIMIT #{offset}, #{limit}
    </if>
</select>

<select id="countByComplexCondition" resultType="int">
    SELECT COUNT(*)
    FROM user
    <include refid="userConditions"/>
</select>

<!-- 带参数的SQL片段 -->
<sql id="paginationSql">
    <if test="pageSize != null and pageSize > 0">
        LIMIT #{offset}, #{pageSize}
    </if>
</sql>

<sql id="orderBySql">
    <choose>
        <when test="orderBy != null and orderBy != ''">
            ORDER BY ${orderBy}
        </when>
        <otherwise>
            ORDER BY create_time DESC
        </otherwise>
    </choose>
</sql>

⚡ 批量操作优化

批量插入优化策略

面试重点：

面试官："如何优化MyBatis的批量插入性能？有哪些方案？"

方案对比分析：

@Service
public class BatchInsertService {
    
    @Autowired
    private UserMapper userMapper;
    
    @Autowired
    private SqlSessionFactory sqlSessionFactory;
    
    // 方案1：单条SQL批量插入（推荐）
    @Transactional
    public void batchInsertOptimal(List<User> users) {
        if (users == null || users.isEmpty()) {
            return;
        }
        
        // 分批处理，避免SQL过长
        int batchSize = 1000;
        for (int i = 0; i < users.size(); i += batchSize) {
            int endIndex = Math.min(i + batchSize, users.size());
            List<User> batch = users.subList(i, endIndex);
            
            // 单条SQL插入多行数据
            userMapper.batchInsert(batch);
        }
    }
    
    // 方案2：使用ExecutorType.BATCH
    @Transactional
    public void batchInsertWithBatchExecutor(List<User> users) {
        try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            
            for (int i = 0; i < users.size(); i++) {
                mapper.insert(users.get(i));
                
                // 每1000条提交一次
                if (i % 1000 == 0) {
                    sqlSession.flushStatements();
                }
            }
            
            sqlSession.flushStatements();
            sqlSession.commit();
        }
    }
    
    // 方案3：多条SQL批量插入（不推荐）
    @Transactional
    public void batchInsertMultipleSQL(List<User> users) {
        // 会产生多条INSERT语句，性能较差
        userMapper.batchInsertMultiple(users);
    }
    
    // 性能测试对比
    public void performanceTest() {
        List<User> users = generateTestUsers(10000);
        
        // 测试方案1
        long start1 = System.currentTimeMillis();
        batchInsertOptimal(users);
        long time1 = System.currentTimeMillis() - start1;
        System.out.println("方案1耗时: " + time1 + "ms");
        
        // 测试方案2
        long start2 = System.currentTimeMillis();
        batchInsertWithBatchExecutor(users);
        long time2 = System.currentTimeMillis() - start2;
        System.out.println("方案2耗时: " + time2 + "ms");
        
        // 结果：方案1 < 方案2 < 方案3
    }
}

批量插入XML配置：

<!-- 方案1：推荐的批量插入 -->
<insert id="batchInsert" parameterType="list">
    INSERT INTO user (name, age, email, create_time)
    VALUES
    <foreach collection="list" item="user" separator=",">
        (#{user.name}, #{user.age}, #{user.email}, NOW())
    </foreach>
</insert>

<!-- 方案2：支持返回主键的批量插入 -->
<insert id="batchInsertWithKeys" parameterType="list" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO user (name, age, email, create_time)
    VALUES
    <foreach collection="list" item="user" separator=",">
        (#{user.name}, #{user.age}, #{user.email}, NOW())
    </foreach>
</insert>

<!-- 方案3：ON DUPLICATE KEY UPDATE -->
<insert id="batchInsertOrUpdate" parameterType="list">
    INSERT INTO user (id, name, age, email, create_time)
    VALUES
    <foreach collection="list" item="user" separator=",">
        (#{user.id}, #{user.name}, #{user.age}, #{user.email}, NOW())
    </foreach>
    ON DUPLICATE KEY UPDATE
        name = VALUES(name),
        age = VALUES(age),
        email = VALUES(email),
        update_time = NOW()
</insert>

批量更新优化

批量更新策略：

@Service
public class BatchUpdateService {
    
    // 方案1：CASE WHEN批量更新（推荐）
    public void batchUpdateOptimal(List<User> users) {
        if (users == null || users.isEmpty()) {
            return;
        }
        
        userMapper.batchUpdateByCase(users);
    }
    
    // 方案2：分批执行单条更新
    @Transactional
    public void batchUpdateSeparate(List<User> users) {
        try (SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH)) {
            UserMapper mapper = sqlSession.getMapper(UserMapper.class);
            
            for (User user : users) {
                mapper.updateById(user);
            }
            
            sqlSession.flushStatements();
            sqlSession.commit();
        }
    }
    
    // 方案3：临时表方案（大数据量）
    @Transactional
    public void batchUpdateWithTempTable(List<User> users) {
        // 1. 创建临时表
        userMapper.createTempTable();
        
        // 2. 批量插入临时表
        userMapper.batchInsertToTempTable(users);
        
        // 3. 关联更新
        userMapper.updateFromTempTable();
        
        // 4. 删除临时表
        userMapper.dropTempTable();
    }
}

批量更新XML实现：

<!-- 方案1：CASE WHEN批量更新 -->
<update id="batchUpdateByCase" parameterType="list">
    UPDATE user
    SET
        name = CASE id
            <foreach collection="list" item="user">
                WHEN #{user.id} THEN #{user.name}
            </foreach>
        END,
        age = CASE id
            <foreach collection="list" item="user">
                WHEN #{user.id} THEN #{user.age}
            </foreach>
        END,
        email = CASE id
            <foreach collection="list" item="user">
                WHEN #{user.id} THEN #{user.email}
            </foreach>
        END,
        update_time = NOW()
    WHERE id IN
    <foreach collection="list" item="user" open="(" separator="," close=")">
        #{user.id}
    </foreach>
</update>

<!-- 方案2：临时表方案 -->
<update id="createTempTable">
    CREATE TEMPORARY TABLE temp_user_update (
        id BIGINT PRIMARY KEY,
        name VARCHAR(100),
        age INT,
        email VARCHAR(100)
    )
</update>

<insert id="batchInsertToTempTable" parameterType="list">
    INSERT INTO temp_user_update (id, name, age, email)
    VALUES
    <foreach collection="list" item="user" separator=",">
        (#{user.id}, #{user.name}, #{user.age}, #{user.email})
    </foreach>
</insert>

<update id="updateFromTempTable">
    UPDATE user u
    INNER JOIN temp_user_update t ON u.id = t.id
    SET u.name = t.name,
        u.age = t.age,
        u.email = t.email,
        u.update_time = NOW()
</update>

<update id="dropTempTable">
    DROP TEMPORARY TABLE temp_user_update
</update>

🚀 SQL性能优化

分页查询优化

面试热点：

面试官："大数据量分页查询如何优化？深分页问题怎么解决？"

分页优化方案：

@Service
public class PaginationOptimizationService {
    
    // 方案1：传统LIMIT分页（小数据量）
    public PageResult<User> selectByPage(int pageNum, int pageSize, UserQuery query) {
        int offset = (pageNum - 1) * pageSize;
        
        List<User> users = userMapper.selectByPageLimit(query, offset, pageSize);
        int total = userMapper.countByQuery(query);
        
        return new PageResult<>(users, total, pageNum, pageSize);
    }
    
    // 方案2：游标分页（推荐）
    public PageResult<User> selectByPageCursor(Long lastId, int pageSize, UserQuery query) {
        List<User> users = userMapper.selectByPageCursor(query, lastId, pageSize);
        
        // 不需要查询总数，提高性能
        return new PageResult<>(users, null, null, pageSize);
    }
    
    // 方案3：延迟关联分页（大数据量）
    public PageResult<User> selectByPageDeferred(int pageNum, int pageSize, UserQuery query) {
        int offset = (pageNum - 1) * pageSize;
        
        // 先查询ID
        List<Long> ids = userMapper.selectIdsByPage(query, offset, pageSize);
        
        if (ids.isEmpty()) {
            return new PageResult<>(Collections.emptyList(), 0, pageNum, pageSize);
        }
        
        // 再根据ID查询详细信息
        List<User> users = userMapper.selectByIds(ids);
        int total = userMapper.countByQuery(query);
        
        return new PageResult<>(users, total, pageNum, pageSize);
    }
}

分页查询XML实现：

<!-- 方案1：传统LIMIT分页 -->
<select id="selectByPageLimit" resultType="User">
    SELECT * FROM user
    <include refid="userConditions"/>
    ORDER BY create_time DESC
    LIMIT #{offset}, #{pageSize}
</select>

<!-- 方案2：游标分页 -->
<select id="selectByPageCursor" resultType="User">
    SELECT * FROM user
    <where>
        <if test="lastId != null">
            AND id > #{lastId}
        </if>
        <include refid="userConditions"/>
    </where>
    ORDER BY id ASC
    LIMIT #{pageSize}
</select>

<!-- 方案3：延迟关联分页 -->
<select id="selectIdsByPage" resultType="Long">
    SELECT id FROM user
    <include refid="userConditions"/>
    ORDER BY create_time DESC
    LIMIT #{offset}, #{pageSize}
</select>

<select id="selectByIds" resultType="User">
    SELECT * FROM user
    WHERE id IN
    <foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
    ORDER BY FIELD(id, 
        <foreach collection="ids" item="id" separator=",">
            #{id}
        </foreach>
    )
</select>

查询条件优化

索引友好的查询：

<!-- 优化前：不走索引 -->
<select id="badQuery" resultType="User">
    SELECT * FROM user
    WHERE YEAR(create_time) = #{year}  <!-- 函数导致不走索引 -->
    AND name LIKE '%${keyword}%'       <!-- 前缀模糊查询不走索引 -->
    AND age + 1 > #{minAge}           <!-- 计算导致不走索引 -->
</select>

<!-- 优化后：索引友好 -->
<select id="goodQuery" resultType="User">
    SELECT * FROM user
    WHERE create_time >= #{yearStart}     <!-- 范围查询走索引 -->
    AND create_time < #{yearEnd}
    AND name LIKE CONCAT(#{keyword}, '%') <!-- 后缀模糊查询走索引 -->
    AND age > #{minAge}                   <!-- 直接比较走索引 -->
</select>

<!-- 复合索引优化 -->
<select id="optimizedComplexQuery" resultType="User">
    SELECT * FROM user
    WHERE status = #{status}        <!-- 第一个索引字段 -->
    AND create_time >= #{startTime} <!-- 第二个索引字段 -->
    AND create_time <= #{endTime}   <!-- 范围查询 -->
    <if test="name != null and name != ''">
        AND name = #{name}          <!-- 精确匹配 -->
    </if>
    ORDER BY create_time DESC       <!-- 利用索引排序 -->
</select>

查询结果优化：

<!-- 只查询需要的字段 -->
<select id="selectUserSummary" resultType="UserSummary">
    SELECT id, name, status FROM user  <!-- 避免SELECT * -->
    <include refid="userConditions"/>
</select>

<!-- 使用EXISTS替代IN -->
<select id="selectUsersWithOrders" resultType="User">
    SELECT * FROM user u
    WHERE EXISTS (
        SELECT 1 FROM orders o 
        WHERE o.user_id = u.id 
        AND o.status = 'PAID'
    )
</select>

<!-- 合理使用UNION -->
<select id="selectActiveUsers" resultType="User">
    SELECT * FROM user WHERE status = 'ACTIVE'
    UNION ALL
    SELECT * FROM user WHERE vip_level > 0 AND vip_expire_time > NOW()
</select>

💡 面试重点总结

高频面试题

1. 动态SQL标签使用

必答要点：
- if标签：条件判断，注意null和空字符串
- where标签：自动处理AND/OR前缀
- foreach标签：循环处理，支持IN查询和批量操作
- choose/when/otherwise：多分支选择
- set标签：动态更新字段
- trim标签：自定义前缀后缀处理

2. 批量操作优化

必答要点：
- 单条SQL批量插入性能最优
- ExecutorType.BATCH适合大量单条操作
- CASE WHEN实现批量更新
- 分批处理避免SQL过长
- 临时表方案适合超大数据量

3. 分页查询优化

必答要点：
- 深分页使用游标分页
- 延迟关联减少数据传输
- 避免COUNT(*)查询
- 利用覆盖索引优化
- 缓存总数减少重复计算

答题技巧

技巧1：结合性能数据

"通过批量插入优化，我们的数据导入性能提升了80%..."

技巧2：对比不同方案

"相比逐条插入，批量插入减少了网络交互次数..."

技巧3：提及实际应用

"在处理百万级数据导入时，我们采用了分批+事务的策略..."

本节核心要点：

• ✅ 动态SQL六大标签的使用场景和最佳实践
• ✅ SQL片段复用提高代码维护性
• ✅ 批量插入和更新的多种优化方案
• ✅ 分页查询的性能优化策略
• ✅ 索引友好的查询条件编写

下一节预告： MyBatis-Plus扩展功能 - 代码生成器、条件构造器、分页插件