第286集MyBatis模糊查询，千万不要再用架构实战：MyBatis查询优化、性能提升与企业级MyBatis查询解决方案

前言

MyBatis模糊查询作为数据访问层的核心功能之一，直接影响着系统性能和用户体验。通过合理的查询优化和性能提升策略，能够构建一个高效、稳定、可扩展的MyBatis查询系统，确保系统的稳定运行。本文从MyBatis查询优化到性能提升，从基础实现到企业级应用，系统梳理MyBatis模糊查询优化的完整解决方案。

一、MyBatis模糊查询架构设计

1.1 MyBatis查询整体架构

1.2 MyBatis查询优化策略架构

二、MyBatis模糊查询问题分析

2.1 传统模糊查询的问题

2.1.1 性能问题分析

/**
 * 传统模糊查询问题分析
 */
@Component
public class TraditionalFuzzyQueryProblemAnalysis {

    /**
     * 问题1：LIKE查询无法使用索引
     */
    public void analyzeLikeQueryIndexProblem() {
        // 传统写法 - 无法使用索引
        String sql1 = "SELECT * FROM user WHERE name LIKE '%张%'";

        // 问题分析：
        // 1. 前导%导致无法使用索引
        // 2. 全表扫描，性能极差
        // 3. 数据量增大时性能急剧下降

        log.warn("LIKE查询无法使用索引，性能问题严重");
    }

    /**
     * 问题2：SQL注入风险
     */
    public void analyzeSqlInjectionRisk() {
        // 传统写法 - 存在SQL注入风险
        String name = "'; DROP TABLE user; --";
        String sql2 = "SELECT * FROM user WHERE name LIKE '%" + name + "%'";

        // 问题分析：
        // 1. 字符串拼接导致SQL注入
        // 2. 安全性问题严重
        // 3. 数据安全风险高

        log.error("字符串拼接存在SQL注入风险");
    }

    /**
     * 问题3：内存消耗过大
     */
    public void analyzeMemoryConsumptionProblem() {
        // 传统写法 - 内存消耗过大
        String sql3 = "SELECT * FROM user WHERE name LIKE '%张%'";

        // 问题分析：
        // 1. 返回大量数据占用内存
        // 2. 网络传输压力大
        // 3. 客户端处理压力大

        log.warn("模糊查询返回大量数据，内存消耗过大");
    }

    /**
     * 问题4：数据库压力过大
     */
    public void analyzeDatabasePressureProblem() {
        // 传统写法 - 数据库压力过大
        String sql4 = "SELECT * FROM user WHERE name LIKE '%张%'";

        // 问题分析：
        // 1. 全表扫描消耗大量CPU
        // 2. 磁盘IO压力大
        // 3. 影响其他查询性能

        log.error("模糊查询导致数据库压力过大");
    }
}

2.2 模糊查询性能测试

2.2.1 性能测试服务

/**
 * 模糊查询性能测试服务
 */
@Service
public class FuzzyQueryPerformanceTestService {

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private RedisTemplate<String, Object> redisTemplate;

    private final String PERFORMANCE_TEST_CACHE_PREFIX = "performance_test:";

    /**
     * 测试传统LIKE查询性能
     */
    public PerformanceTestResult testTraditionalLikeQuery(String keyword) {
        try {
            long startTime = System.currentTimeMillis();

            // 执行传统LIKE查询
            List<User> users = userMapper.findUsersByLikeKeyword(keyword);

            long endTime = System.currentTimeMillis();
            long executionTime = endTime - startTime;

            PerformanceTestResult result = new PerformanceTestResult();
            result.setQueryType("TRADITIONAL_LIKE");
            result.setKeyword(keyword);
            result.setResultCount(users.size());
            result.setExecutionTime(executionTime);
            result.setTestTime(LocalDateTime.now());

            // 缓存测试结果
            cachePerformanceTestResult(result);

            log.info("传统LIKE查询性能测试完成: 执行时间={}ms, 结果数量={}", executionTime, users.size());

            return result;

        } catch (Exception e) {
            log.error("传统LIKE查询性能测试失败", e);
            throw new PerformanceTestException("传统LIKE查询性能测试失败", e);
        }
    }

    /**
     * 测试优化后的查询性能
     */
    public PerformanceTestResult testOptimizedQuery(String keyword) {
        try {
            long startTime = System.currentTimeMillis();

            // 执行优化后的查询
            List<User> users = userMapper.findUsersByOptimizedKeyword(keyword);

            long endTime = System.currentTimeMillis();
            long executionTime = endTime - startTime;

            PerformanceTestResult result = new PerformanceTestResult();
            result.setQueryType("OPTIMIZED_QUERY");
            result.setKeyword(keyword);
            result.setResultCount(users.size());
            result.setExecutionTime(executionTime);
            result.setTestTime(LocalDateTime.now());

            // 缓存测试结果
            cachePerformanceTestResult(result);

            log.info("优化查询性能测试完成: 执行时间={}ms, 结果数量={}", executionTime, users.size());

            return result;

        } catch (Exception e) {
            log.error("优化查询性能测试失败", e);
            throw new PerformanceTestException("优化查询性能测试失败", e);
        }
    }

    /**
     * 缓存性能测试结果
     */
    private void cachePerformanceTestResult(PerformanceTestResult result) {
        try {
            String cacheKey = PERFORMANCE_TEST_CACHE_PREFIX + result.getQueryType() + ":" + result.getKeyword();
            redisTemplate.opsForValue().set(cacheKey, result, Duration.ofHours(1));

        } catch (Exception e) {
            log.error("缓存性能测试结果失败", e);
        }
    }

    /**
     * 批量性能测试
     */
    public List<PerformanceTestResult> batchPerformanceTest(List<String> keywords) {
        try {
            List<PerformanceTestResult> results = new ArrayList<>();

            for (String keyword : keywords) {
                try {
                    // 测试传统查询
                    PerformanceTestResult traditionalResult = testTraditionalLikeQuery(keyword);
                    results.add(traditionalResult);

                    // 测试优化查询
                    PerformanceTestResult optimizedResult = testOptimizedQuery(keyword);
                    results.add(optimizedResult);

                } catch (Exception e) {
                    log.error("批量性能测试失败: {}", keyword, e);
                }
            }

            return results;

        } catch (Exception e) {
            log.error("批量性能测试失败", e);
            throw new PerformanceTestException("批量性能测试失败", e);
        }
    }

    /**
     * 生成性能测试报告
     */
    public PerformanceTestReport generatePerformanceTestReport(List<PerformanceTestResult> results) {
        try {
            PerformanceTestReport report = new PerformanceTestReport();
            report.setGenerateTime(LocalDateTime.now());

            // 统计传统查询性能
            List<PerformanceTestResult> traditionalResults = results.stream()
                    .filter(r -> "TRADITIONAL_LIKE".equals(r.getQueryType()))
                    .collect(Collectors.toList());

            if (!traditionalResults.isEmpty()) {
                report.setTraditionalAverageTime(traditionalResults.stream()
                        .mapToLong(PerformanceTestResult::getExecutionTime)
                        .average()
                        .orElse(0.0));
                report.setTraditionalMaxTime(traditionalResults.stream()
                        .mapToLong(PerformanceTestResult::getExecutionTime)
                        .max()
                        .orElse(0L));
                report.setTraditionalMinTime(traditionalResults.stream()
                        .mapToLong(PerformanceTestResult::getExecutionTime)
                        .min()
                        .orElse(0L));
            }

            // 统计优化查询性能
            List<PerformanceTestResult> optimizedResults = results.stream()
                    .filter(r -> "OPTIMIZED_QUERY".equals(r.getQueryType()))
                    .collect(Collectors.toList());

            if (!optimizedResults.isEmpty()) {
                report.setOptimizedAverageTime(optimizedResults.stream()
                        .mapToLong(PerformanceTestResult::getExecutionTime)
                        .average()
                        .orElse(0.0));
                report.setOptimizedMaxTime(optimizedResults.stream()
                        .mapToLong(PerformanceTestResult::getExecutionTime)
                        .max()
                        .orElse(0L));
                report.setOptimizedMinTime(optimizedResults.stream()
                        .mapToLong(PerformanceTestResult::getExecutionTime)
                        .min()
                        .orElse(0L));
            }

            // 计算性能提升比例
            if (report.getTraditionalAverageTime() > 0 && report.getOptimizedAverageTime() > 0) {
                double improvementRatio = (report.getTraditionalAverageTime() - report.getOptimizedAverageTime()) 
                        / report.getTraditionalAverageTime() * 100;
                report.setPerformanceImprovementRatio(improvementRatio);
            }

            return report;

        } catch (Exception e) {
            log.error("生成性能测试报告失败", e);
            throw new PerformanceTestException("生成性能测试报告失败", e);
        }
    }
}

三、MyBatis查询优化方案

3.1 索引优化方案

3.1.1 索引优化服务

/**
 * MyBatis索引优化服务
 */
@Service
public class MyBatisIndexOptimizationService {

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private RedisTemplate<String, Object> redisTemplate;

    private final String INDEX_OPTIMIZATION_CACHE_PREFIX = "index_optimization:";

    /**
     * 方案1：使用全文索引
     */
    public List<User> searchUsersByFullTextIndex(String keyword) {
        try {
            // 使用全文索引查询
            List<User> users = userMapper.findUsersByFullTextIndex(keyword);

            log.info("全文索引查询完成: 关键词={}, 结果数量={}", keyword, users.size());

            return users;

        } catch (Exception e) {
            log.error("全文索引查询失败", e);
            throw new MyBatisOptimizationException("全文索引查询失败", e);
        }
    }

    /**
     * 方案2：使用前缀索引
     */
    public List<User> searchUsersByPrefixIndex(String keyword) {
        try {
            // 使用前缀索引查询
            List<User> users = userMapper.findUsersByPrefixIndex(keyword + "%");

            log.info("前缀索引查询完成: 关键词={}, 结果数量={}", keyword, users.size());

            return users;

        } catch (Exception e) {
            log.error("前缀索引查询失败", e);
            throw new MyBatisOptimizationException("前缀索引查询失败", e);
        }
    }

    /**
     * 方案3：使用复合索引
     */
    public List<User> searchUsersByCompositeIndex(String keyword, String category) {
        try {
            // 使用复合索引查询
            List<User> users = userMapper.findUsersByCompositeIndex(keyword, category);

            log.info("复合索引查询完成: 关键词={}, 分类={}, 结果数量={}", keyword, category, users.size());

            return users;

        } catch (Exception e) {
            log.error("复合索引查询失败", e);
            throw new MyBatisOptimizationException("复合索引查询失败", e);
        }
    }

    /**
     * 方案4：使用覆盖索引
     */
    public List<User> searchUsersByCoveringIndex(String keyword) {
        try {
            // 使用覆盖索引查询
            List<User> users = userMapper.findUsersByCoveringIndex(keyword);

            log.info("覆盖索引查询完成: 关键词={}, 结果数量={}", keyword, users.size());

            return users;

        } catch (Exception e) {
            log.error("覆盖索引查询失败", e);
            throw new MyBatisOptimizationException("覆盖索引查询失败", e);
        }
    }

    /**
     * 创建优化索引
     */
    public void createOptimizedIndexes() {
        try {
            // 创建全文索引
            userMapper.createFullTextIndex();

            // 创建前缀索引
            userMapper.createPrefixIndex();

            // 创建复合索引
            userMapper.createCompositeIndex();

            // 创建覆盖索引
            userMapper.createCoveringIndex();

            log.info("优化索引创建完成");

        } catch (Exception e) {
            log.error("创建优化索引失败", e);
            throw new MyBatisOptimizationException("创建优化索引失败", e);
        }
    }

    /**
     * 分析索引使用情况
     */
    public IndexUsageAnalysis analyzeIndexUsage() {
        try {
            IndexUsageAnalysis analysis = new IndexUsageAnalysis();
            analysis.setAnalysisTime(LocalDateTime.now());

            // 分析全文索引使用情况
            analysis.setFullTextIndexUsage(analyzeFullTextIndexUsage());

            // 分析前缀索引使用情况
            analysis.setPrefixIndexUsage(analyzePrefixIndexUsage());

            // 分析复合索引使用情况
            analysis.setCompositeIndexUsage(analyzeCompositeIndexUsage());

            // 分析覆盖索引使用情况
            analysis.setCoveringIndexUsage(analyzeCoveringIndexUsage());

            return analysis;

        } catch (Exception e) {
            log.error("分析索引使用情况失败", e);
            throw new MyBatisOptimizationException("分析索引使用情况失败", e);
        }
    }

    /**
     * 分析全文索引使用情况
     */
    private IndexUsage analyzeFullTextIndexUsage() {
        try {
            IndexUsage usage = new IndexUsage();
            usage.setIndexType("FULL_TEXT");
            usage.setUsageCount(1000L);
            usage.setAverageQueryTime(50.0);
            usage.setHitRate(0.95);

            return usage;

        } catch (Exception e) {
            log.error("分析全文索引使用情况失败", e);
            return new IndexUsage();
        }
    }

    /**
     * 分析前缀索引使用情况
     */
    private IndexUsage analyzePrefixIndexUsage() {
        try {
            IndexUsage usage = new IndexUsage();
            usage.setIndexType("PREFIX");
            usage.setUsageCount(2000L);
            usage.setAverageQueryTime(30.0);
            usage.setHitRate(0.98);

            return usage;

        } catch (Exception e) {
            log.error("分析前缀索引使用情况失败", e);
            return new IndexUsage();
        }
    }

    /**
     * 分析复合索引使用情况
     */
    private IndexUsage analyzeCompositeIndexUsage() {
        try {
            IndexUsage usage = new IndexUsage();
            usage.setIndexType("COMPOSITE");
            usage.setUsageCount(1500L);
            usage.setAverageQueryTime(40.0);
            usage.setHitRate(0.97);

            return usage;

        } catch (Exception e) {
            log.error("分析复合索引使用情况失败", e);
            return new IndexUsage();
        }
    }

    /**
     * 分析覆盖索引使用情况
     */
    private IndexUsage analyzeCoveringIndexUsage() {
        try {
            IndexUsage usage = new IndexUsage();
            usage.setIndexType("COVERING");
            usage.setUsageCount(800L);
            usage.setAverageQueryTime(20.0);
            usage.setHitRate(0.99);

            return usage;

        } catch (Exception e) {
            log.error("分析覆盖索引使用情况失败", e);
            return new IndexUsage();
        }
    }
}

3.2 SQL优化方案

3.2.1 SQL优化服务

/**
 * MyBatis SQL优化服务
 */
@Service
public class MyBatisSqlOptimizationService {

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private RedisTemplate<String, Object> redisTemplate;

    private final String SQL_OPTIMIZATION_CACHE_PREFIX = "sql_optimization:";

    /**
     * 方案1：使用EXISTS替代IN
     */
    public List<User> searchUsersByExists(String keyword) {
        try {
            // 使用EXISTS查询
            List<User> users = userMapper.findUsersByExists(keyword);

            log.info("EXISTS查询完成: 关键词={}, 结果数量={}", keyword, users.size());

            return users;

        } catch (Exception e) {
            log.error("EXISTS查询失败", e);
            throw new MyBatisOptimizationException("EXISTS查询失败", e);
        }
    }

    /**
     * 方案2：使用JOIN替代子查询
     */
    public List<User> searchUsersByJoin(String keyword) {
        try {
            // 使用JOIN查询
            List<User> users = userMapper.findUsersByJoin(keyword);

            log.info("JOIN查询完成: 关键词={}, 结果数量={}", keyword, users.size());

            return users;

        } catch (Exception e) {
            log.error("JOIN查询失败", e);
            throw new MyBatisOptimizationException("JOIN查询失败", e);
        }
    }

    /**
     * 方案3：使用UNION替代OR
     */
    public List<User> searchUsersByUnion(String keyword1, String keyword2) {
        try {
            // 使用UNION查询
            List<User> users = userMapper.findUsersByUnion(keyword1, keyword2);

            log.info("UNION查询完成: 关键词1={}, 关键词2={}, 结果数量={}", keyword1, keyword2, users.size());

            return users;

        } catch (Exception e) {
            log.error("UNION查询失败", e);
            throw new MyBatisOptimizationException("UNION查询失败", e);
        }
    }

    /**
     * 方案4：使用LIMIT分页
     */
    public PageResult<User> searchUsersByLimit(String keyword, int pageNum, int pageSize) {
        try {
            // 计算偏移量
            int offset = (pageNum - 1) * pageSize;

            // 使用LIMIT分页查询
            List<User> users = userMapper.findUsersByLimit(keyword, offset, pageSize);

            // 查询总数
            long total = userMapper.countUsersByKeyword(keyword);

            PageResult<User> result = new PageResult<>();
            result.setData(users);
            result.setTotal(total);
            result.setPageNum(pageNum);
            result.setPageSize(pageSize);
            result.setTotalPages((int) Math.ceil((double) total / pageSize));

            log.info("LIMIT分页查询完成: 关键词={}, 页码={}, 页大小={}, 结果数量={}", 
                    keyword, pageNum, pageSize, users.size());

            return result;

        } catch (Exception e) {
            log.error("LIMIT分页查询失败", e);
            throw new MyBatisOptimizationException("LIMIT分页查询失败", e);
        }
    }

    /**
     * 方案5：使用参数化查询
     */
    public List<User> searchUsersByParameterizedQuery(String keyword) {
        try {
            // 使用参数化查询
            List<User> users = userMapper.findUsersByParameterizedQuery(keyword);

            log.info("参数化查询完成: 关键词={}, 结果数量={}", keyword, users.size());

            return users;

        } catch (Exception e) {
            log.error("参数化查询失败", e);
            throw new MyBatisOptimizationException("参数化查询失败", e);
        }
    }

    /**
     * 方案6：使用预编译语句
     */
    public List<User> searchUsersByPreparedStatement(String keyword) {
        try {
            // 使用预编译语句查询
            List<User> users = userMapper.findUsersByPreparedStatement(keyword);

            log.info("预编译语句查询完成: 关键词={}, 结果数量={}", keyword, users.size());

            return users;

        } catch (Exception e) {
            log.error("预编译语句查询失败", e);
            throw new MyBatisOptimizationException("预编译语句查询失败", e);
        }
    }

    /**
     * 分析SQL执行计划
     */
    public SqlExecutionPlan analyzeSqlExecutionPlan(String sql) {
        try {
            SqlExecutionPlan plan = new SqlExecutionPlan();
            plan.setSql(sql);
            plan.setAnalysisTime(LocalDateTime.now());

            // 分析执行计划
            plan.setExecutionSteps(analyzeExecutionSteps(sql));
            plan.setIndexUsage(analyzeIndexUsage(sql));
            plan.setCostEstimate(analyzeCostEstimate(sql));

            return plan;

        } catch (Exception e) {
            log.error("分析SQL执行计划失败", e);
            throw new MyBatisOptimizationException("分析SQL执行计划失败", e);
        }
    }

    /**
     * 分析执行步骤
     */
    private List<ExecutionStep> analyzeExecutionSteps(String sql) {
        try {
            List<ExecutionStep> steps = new ArrayList<>();

            // 模拟执行步骤分析
            ExecutionStep step1 = new ExecutionStep();
            step1.setStepOrder(1);
            step1.setOperation("INDEX_SCAN");
            step1.setTable("user");
            step1.setCost(100.0);
            steps.add(step1);

            ExecutionStep step2 = new ExecutionStep();
            step2.setStepOrder(2);
            step2.setOperation("FILTER");
            step2.setTable("user");
            step2.setCost(50.0);
            steps.add(step2);

            return steps;

        } catch (Exception e) {
            log.error("分析执行步骤失败", e);
            return new ArrayList<>();
        }
    }

    /**
     * 分析索引使用情况
     */
    private IndexUsage analyzeIndexUsage(String sql) {
        try {
            IndexUsage usage = new IndexUsage();
            usage.setIndexType("COMPOSITE");
            usage.setUsageCount(1L);
            usage.setAverageQueryTime(30.0);
            usage.setHitRate(0.95);

            return usage;

        } catch (Exception e) {
            log.error("分析索引使用情况失败", e);
            return new IndexUsage();
        }
    }

    /**
     * 分析成本估算
     */
    private CostEstimate analyzeCostEstimate(String sql) {
        try {
            CostEstimate estimate = new CostEstimate();
            estimate.setTotalCost(150.0);
            estimate.setCpuCost(100.0);
            estimate.setIoCost(50.0);
            estimate.setMemoryCost(10.0);

            return estimate;

        } catch (Exception e) {
            log.error("分析成本估算失败", e);
            return new CostEstimate();
        }
    }
}

3.3 缓存优化方案

3.3.1 缓存优化服务

/**
 * MyBatis缓存优化服务
 */
@Service
public class MyBatisCacheOptimizationService {

    @Autowired
    private UserMapper userMapper;

    @Autowired
    private RedisTemplate<String, Object> redisTemplate;

    @Autowired
    private CaffeineCache localCache;

    private final String CACHE_OPTIMIZATION_PREFIX = "cache_optimization:";
    private final long CACHE_EXPIRE_TIME = 3600; // 1小时

    /**
     * 方案1：使用MyBatis一级缓存
     */
    public List<User> searchUsersByFirstLevelCache(String keyword) {
        try {
            // 使用一级缓存查询
            List<User> users = userMapper.findUsersByFirstLevelCache(keyword);

            log.info("一级缓存查询完成: 关键词={}, 结果数量={}", keyword, users.size());

            return users;

        } catch (Exception e) {
            log.error("一级缓存查询失败", e);
            throw new MyBatisOptimizationException("一级缓存查询失败", e);
        }
    }

    /**
     * 方案2：使用MyBatis二级缓存
     */
    public List<User> searchUsersBySecondLevelCache(String keyword) {
        try {
            // 使用二级缓存查询
            List<User> users = userMapper.findUsersBySecondLevelCache(keyword);

            log.info("二级缓存查询完成: 关键词={}, 结果数量={}", keyword, users.size());

            return users;

        } catch (Exception e) {
            log.error("二级缓存查询失败", e);
            throw new MyBatisOptimizationException("二级缓存查询失败", e);
        }
    }

    /**
     * 方案3：使用Redis缓存
     */
    public List<User> searchUsersByRedisCache(String keyword) {
        try {
            String cacheKey = CACHE_OPTIMIZATION_PREFIX + "redis:" + keyword;

            // 先从Redis缓存获取
            List<User> cachedUsers = (List<User>) redisTemplate.opsForValue().get(cacheKey);
            if (cachedUsers != null) {
                log.info("Redis缓存命中: 关键词={}, 结果数量={}", keyword, cachedUsers.size());
                return cachedUsers;
            }

            // 缓存未命中，查询数据库
            List<User> users = userMapper.findUsersByKeyword(keyword);

            // 存入Redis缓存
            redisTemplate.opsForValue().set(cacheKey, users, Duration.ofSeconds(CACHE_EXPIRE_TIME));

            log.info("Redis缓存查询完成: 关键词={}, 结果数量={}", keyword, users.size());

            return users;

        } catch (Exception e) {
            log.error("Redis缓存查询失败", e);
            throw new MyBatisOptimizationException("Redis缓存查询失败", e);
        }
    }

    /**
     * 方案4：使用本地缓存
     */
    public List<User> searchUsersByLocalCache(String keyword) {
        try {
            String cacheKey = CACHE_OPTIMIZATION_PREFIX + "local:" + keyword;

            // 先从本地缓存获取
            List<User> cachedUsers = (List<User>) localCache.getIfPresent(cacheKey);
            if (cachedUsers != null) {
                log.info("本地缓存命中: 关键词={}, 结果数量={}", keyword, cachedUsers.size());
                return cachedUsers;
            }

            // 缓存未命中，查询数据库
            List<User> users = userMapper.findUsersByKeyword(keyword);

            // 存入本地缓存
            localCache.put(cacheKey, users);

            log.info("本地缓存查询完成: 关键词={}, 结果数量={}", keyword, users.size());

            return users;

        } catch (Exception e) {
            log.error("本地缓存查询失败", e);
            throw new MyBatisOptimizationException("本地缓存查询失败", e);
        }
    }

    /**
     * 方案5：使用多级缓存
     */
    public List<User> searchUsersByMultiLevelCache(String keyword) {
        try {
            String cacheKey = CACHE_OPTIMIZATION_PREFIX + "multi:" + keyword;

            // 第一级：本地缓存
            List<User> cachedUsers = (List<User>) localCache.getIfPresent(cacheKey);
            if (cachedUsers != null) {
                log.info("本地缓存命中: 关键词={}, 结果数量={}", keyword, cachedUsers.size());
                return cachedUsers;
            }

            // 第二级：Redis缓存
            cachedUsers = (List<User>) redisTemplate.opsForValue().get(cacheKey);
            if (cachedUsers != null) {
                // 存入本地缓存
                localCache.put(cacheKey, cachedUsers);
                log.info("Redis缓存命中: 关键词={}, 结果数量={}", keyword, cachedUsers.size());
                return cachedUsers;
            }

            // 第三级：数据库查询
            List<User> users = userMapper.findUsersByKeyword(keyword);

            // 存入多级缓存
            localCache.put(cacheKey, users);
            redisTemplate.opsForValue().set(cacheKey, users, Duration.ofSeconds(CACHE_EXPIRE_TIME));

            log.info("多级缓存查询完成: 关键词={}, 结果数量={}", keyword, users.size());

            return users;

        } catch (Exception e) {
            log.error("多级缓存查询失败", e);
            throw new MyBatisOptimizationException("多级缓存查询失败", e);
        }
    }

    /**
     * 方案6：使用缓存预热
     */
    public void warmupCache(List<String> keywords) {
        try {
            for (String keyword : keywords) {
                try {
                    // 预热多级缓存
                    searchUsersByMultiLevelCache(keyword);

                } catch (Exception e) {
                    log.error("缓存预热失败: {}", keyword, e);
                }
            }

            log.info("缓存预热完成: 关键词数量={}", keywords.size());

        } catch (Exception e) {
            log.error("缓存预热失败", e);
        }
    }

    /**
     * 清理缓存
     */
    public void clearCache(String keyword) {
        try {
            String cacheKey = CACHE_OPTIMIZATION_PREFIX + "multi:" + keyword;

            // 清理本地缓存
            localCache.invalidate(cacheKey);

            // 清理Redis缓存
            redisTemplate.delete(cacheKey);

            log.info("缓存清理完成: 关键词={}", keyword);

        } catch (Exception e) {
            log.error("缓存清理失败", e);
        }
    }

    /**
     * 分析缓存命中率
     */
    public CacheHitRateAnalysis analyzeCacheHitRate() {
        try {
            CacheHitRateAnalysis analysis = new CacheHitRateAnalysis();
            analysis.setAnalysisTime(LocalDateTime.now());

            // 分析本地缓存命中率
            analysis.setLocalCacheHitRate(analyzeLocalCacheHitRate());

            // 分析Redis缓存命中率
            analysis.setRedisCacheHitRate(analyzeRedisCacheHitRate());

            // 分析多级缓存命中率
            analysis.setMultiLevelCacheHitRate(analyzeMultiLevelCacheHitRate());

            return analysis;

        } catch (Exception e) {
            log.error("分析缓存命中率失败", e);
            throw new MyBatisOptimizationException("分析缓存命中率失败", e);
        }
    }

    /**
     * 分析本地缓存命中率
     */
    private double analyzeLocalCacheHitRate() {
        try {
            // 模拟本地缓存命中率分析
            return 0.85; // 85%

        } catch (Exception e) {
            log.error("分析本地缓存命中率失败", e);
            return 0.0;
        }
    }

    /**
     * 分析Redis缓存命中率
     */
    private double analyzeRedisCacheHitRate() {
        try {
            // 模拟Redis缓存命中率分析
            return 0.90; // 90%

        } catch (Exception e) {
            log.error("分析Redis缓存命中率失败", e);
            return 0.0;
        }
    }

    /**
     * 分析多级缓存命中率
     */
    private double analyzeMultiLevelCacheHitRate() {
        try {
            // 模拟多级缓存命中率分析
            return 0.95; // 95%

        } catch (Exception e) {
            log.error("分析多级缓存命中率失败", e);
            return 0.0;
        }
    }
}

四、企业级MyBatis查询方案

4.1 MyBatis查询管理服务

4.1.1 MyBatis查询管理服务

/**
 * MyBatis查询管理服务
 */
@Service
public class MyBatisQueryManagementService {

    @Autowired
    private MyBatisIndexOptimizationService indexOptimizationService;

    @Autowired
    private MyBatisSqlOptimizationService sqlOptimizationService;

    @Autowired
    private MyBatisCacheOptimizationService cacheOptimizationService;

    @Autowired
    private RedisTemplate<String, Object> redisTemplate;

    private final String QUERY_MANAGEMENT_CACHE_PREFIX = "query_management:";
    private final long QUERY_MANAGEMENT_CACHE_EXPIRE = 3600; // 1小时

    /**
     * 智能选择查询优化策略
     */
    public List<User> executeOptimizedQuery(QueryOptimizationRequest request) {
        try {
            // 1. 验证请求
            validateQueryOptimizationRequest(request);

            // 2. 选择优化策略
            QueryOptimizationStrategy strategy = selectQueryOptimizationStrategy(request);

            // 3. 执行优化策略
            List<User> result = executeQueryOptimizationStrategy(request, strategy);

            // 4. 记录查询统计
            recordQueryStatistics(request, strategy);

            return result;

        } catch (Exception e) {
            log.error("执行优化查询失败: {}", request.getRequestId(), e);
            throw new MyBatisOptimizationException("执行优化查询失败", e);
        }
    }

    /**
     * 验证查询优化请求
     */
    private void validateQueryOptimizationRequest(QueryOptimizationRequest request) {
        if (request == null) {
            throw new IllegalArgumentException("查询优化请求不能为空");
        }

        if (request.getRequestId() == null) {
            throw new IllegalArgumentException("请求ID不能为空");
        }

        if (request.getKeyword() == null) {
            throw new IllegalArgumentException("关键词不能为空");
        }
    }

    /**
     * 选择查询优化策略
     */
    private QueryOptimizationStrategy selectQueryOptimizationStrategy(QueryOptimizationRequest request) {
        try {
            if (request.getStrategy() != null) {
                return request.getStrategy();
            }

            // 根据查询特征选择策略
            if (request.isUseCache()) {
                return QueryOptimizationStrategy.CACHE_OPTIMIZATION;
            } else if (request.isUseIndex()) {
                return QueryOptimizationStrategy.INDEX_OPTIMIZATION;
            } else if (request.isUseSqlOptimization()) {
                return QueryOptimizationStrategy.SQL_OPTIMIZATION;
            } else {
                return QueryOptimizationStrategy.CACHE_OPTIMIZATION; // 默认策略
            }

        } catch (Exception e) {
            log.error("选择查询优化策略失败", e);
            return QueryOptimizationStrategy.CACHE_OPTIMIZATION; // 默认策略
        }
    }

    /**
     * 执行查询优化策略
     */
    private List<User> executeQueryOptimizationStrategy(QueryOptimizationRequest request, QueryOptimizationStrategy strategy) {
        try {
            switch (strategy) {
                case INDEX_OPTIMIZATION:
                    return executeIndexOptimizationStrategy(request);
                case SQL_OPTIMIZATION:
                    return executeSqlOptimizationStrategy(request);
                case CACHE_OPTIMIZATION:
                    return executeCacheOptimizationStrategy(request);
                default:
                    throw new IllegalArgumentException("不支持的查询优化策略: " + strategy);
            }

        } catch (Exception e) {
            log.error("执行查询优化策略失败", e);
            throw new MyBatisOptimizationException("执行查询优化策略失败", e);
        }
    }

    /**
     * 执行索引优化策略
     */
    private List<User> executeIndexOptimizationStrategy(QueryOptimizationRequest request) {
        try {
            // 根据索引类型选择优化方案
            if (request.isUseFullTextIndex()) {
                return indexOptimizationService.searchUsersByFullTextIndex(request.getKeyword());
            } else if (request.isUsePrefixIndex()) {
                return indexOptimizationService.searchUsersByPrefixIndex(request.getKeyword());
            } else if (request.isUseCompositeIndex()) {
                return indexOptimizationService.searchUsersByCompositeIndex(request.getKeyword(), request.getCategory());
            } else {
                return indexOptimizationService.searchUsersByCoveringIndex(request.getKeyword());
            }

        } catch (Exception e) {
            log.error("执行索引优化策略失败", e);
            throw new MyBatisOptimizationException("执行索引优化策略失败", e);
        }
    }

    /**
     * 执行SQL优化策略
     */
    private List<User> executeSqlOptimizationStrategy(QueryOptimizationRequest request) {
        try {
            // 根据SQL优化类型选择优化方案
            if (request.isUseExists()) {
                return sqlOptimizationService.searchUsersByExists(request.getKeyword());
            } else if (request.isUseJoin()) {
                return sqlOptimizationService.searchUsersByJoin(request.getKeyword());
            } else if (request.isUseUnion()) {
                return sqlOptimizationService.searchUsersByUnion(request.getKeyword(), request.getKeyword2());
            } else {
                return sqlOptimizationService.searchUsersByParameterizedQuery(request.getKeyword());
            }

        } catch (Exception e) {
            log.error("执行SQL优化策略失败", e);
            throw new MyBatisOptimizationException("执行SQL优化策略失败", e);
        }
    }

    /**
     * 执行缓存优化策略
     */
    private List<User> executeCacheOptimizationStrategy(QueryOptimizationRequest request) {
        try {
            // 根据缓存类型选择优化方案
            if (request.isUseMultiLevelCache()) {
                return cacheOptimizationService.searchUsersByMultiLevelCache(request.getKeyword());
            } else if (request.isUseRedisCache()) {
                return cacheOptimizationService.searchUsersByRedisCache(request.getKeyword());
            } else if (request.isUseLocalCache()) {
                return cacheOptimizationService.searchUsersByLocalCache(request.getKeyword());
            } else {
                return cacheOptimizationService.searchUsersBySecondLevelCache(request.getKeyword());
            }

        } catch (Exception e) {
            log.error("执行缓存优化策略失败", e);
            throw new MyBatisOptimizationException("执行缓存优化策略失败", e);
        }
    }

    /**
     * 记录查询统计
     */
    private void recordQueryStatistics(QueryOptimizationRequest request, QueryOptimizationStrategy strategy) {
        try {
            QueryStatistics statistics = new QueryStatistics();
            statistics.setRequestId(request.getRequestId());
            statistics.setStrategy(strategy);
            statistics.setKeyword(request.getKeyword());
            statistics.setExecutionTime(LocalDateTime.now());

            // 异步记录统计
            CompletableFuture.runAsync(() -> {
                try {
                    saveQueryStatistics(statistics);
                } catch (Exception e) {
                    log.error("保存查询统计失败", e);
                }
            });

        } catch (Exception e) {
            log.error("记录查询统计失败", e);
        }
    }

    /**
     * 保存查询统计
     */
    private void saveQueryStatistics(QueryStatistics statistics) {
        // 实现统计保存逻辑
        log.info("保存查询统计: {}", statistics.getRequestId());
    }

    /**
     * 获取查询优化统计
     */
    public QueryOptimizationStatisticsResult getQueryOptimizationStatistics(Date startTime, Date endTime) {
        try {
            QueryOptimizationStatisticsResult result = new QueryOptimizationStatisticsResult();
            result.setStartTime(startTime);
            result.setEndTime(endTime);

            // 统计查询次数
            result.setTotalQueries(10000L); // 实际应用中需要从数据库统计

            // 统计策略使用情况
            Map<QueryOptimizationStrategy, Long> strategyCount = new HashMap<>();
            strategyCount.put(QueryOptimizationStrategy.INDEX_OPTIMIZATION, 3000L);
            strategyCount.put(QueryOptimizationStrategy.SQL_OPTIMIZATION, 2000L);
            strategyCount.put(QueryOptimizationStrategy.CACHE_OPTIMIZATION, 5000L);
            result.setStrategyCount(strategyCount);

            // 统计成功率
            result.setSuccessRate(0.99); // 99%

            // 统计平均执行时间
            result.setAverageExecutionTime(100.0); // 100ms

            return result;

        } catch (Exception e) {
            log.error("获取查询优化统计失败", e);
            throw new MyBatisOptimizationException("获取查询优化统计失败", e);
        }
    }
}

五、性能优化与监控

5.1 性能优化

5.1.1 MyBatis性能优化

/**
 * MyBatis性能优化服务
 */
@Service
public class MyBatisPerformanceOptimizationService {

    @Autowired
    private RedisTemplate<String, Object> redisTemplate;

    @Autowired
    private CaffeineCache localCache;

    private final String MYBATIS_PERFORMANCE_CACHE_PREFIX = "mybatis_performance:";

    /**
     * 优化MyBatis性能
     */
    public MyBatisOptimizationResult optimizeMyBatisPerformance(MyBatisOptimizationRequest request) {
        try {
            MyBatisOptimizationResult result = new MyBatisOptimizationResult();
            result.setRequestId(request.getRequestId());
            result.setStartTime(new Date());

            // 1. 分析MyBatis性能模式
            MyBatisPerformancePatternAnalysis patternAnalysis = analyzeMyBatisPerformancePattern(request);
            result.setPatternAnalysis(patternAnalysis);

            // 2. 优化连接池策略
            ConnectionPoolOptimizationResult connectionPoolOptimization = optimizeConnectionPoolStrategy(request, patternAnalysis);
            result.setConnectionPoolOptimization(connectionPoolOptimization);

            // 3. 优化SQL策略
            SqlOptimizationResult sqlOptimization = optimizeSqlStrategy(request, patternAnalysis);
            result.setSqlOptimization(sqlOptimization);

            result.setStatus(MyBatisOptimizationStatus.COMPLETED);
            result.setEndTime(new Date());

            return result;

        } catch (Exception e) {
            log.error("优化MyBatis性能失败", e);
            throw new MyBatisOptimizationException("优化MyBatis性能失败", e);
        }
    }

    /**
     * 分析MyBatis性能模式
     */
    private MyBatisPerformancePatternAnalysis analyzeMyBatisPerformancePattern(MyBatisOptimizationRequest request) {
        try {
            MyBatisPerformancePatternAnalysis analysis = new MyBatisPerformancePatternAnalysis();
            analysis.setRequestId(request.getRequestId());

            // 分析查询频率
            analysis.setQueryFrequency(analyzeQueryFrequency(request.getQueryType()));

            // 分析性能指标
            analysis.setPerformanceMetrics(analyzePerformanceMetrics(request.getQueryType()));

            return analysis;

        } catch (Exception e) {
            log.error("分析MyBatis性能模式失败", e);
            throw new MyBatisOptimizationException("分析MyBatis性能模式失败", e);
        }
    }

    /**
     * 分析查询频率
     */
    private QueryFrequency analyzeQueryFrequency(String queryType) {
        try {
            QueryFrequency frequency = new QueryFrequency();
            frequency.setQueryType(queryType);
            frequency.setDailyCount(10000L);
            frequency.setHourlyCount(1000L);
            frequency.setMinuteCount(100L);

            return frequency;

        } catch (Exception e) {
            log.error("分析查询频率失败", e);
            return new QueryFrequency();
        }
    }

    /**
     * 分析性能指标
     */
    private PerformanceMetrics analyzePerformanceMetrics(String queryType) {
        try {
            PerformanceMetrics metrics = new PerformanceMetrics();
            metrics.setQueryType(queryType);
            metrics.setAverageExecutionTime(50.0); // 50ms
            metrics.setSuccessRate(0.99); // 99%
            metrics.setThroughput(1000.0); // 1000次/秒

            return metrics;

        } catch (Exception e) {
            log.error("分析性能指标失败", e);
            return new PerformanceMetrics();
        }
    }

    /**
     * 优化连接池策略
     */
    private ConnectionPoolOptimizationResult optimizeConnectionPoolStrategy(MyBatisOptimizationRequest request, 
            MyBatisPerformancePatternAnalysis analysis) {
        try {
            ConnectionPoolOptimizationResult result = new ConnectionPoolOptimizationResult();
            result.setRequestId(request.getRequestId());

            // 根据查询频率优化连接池策略
            if (analysis.getQueryFrequency().getDailyCount() > 100000) { // 大于10万次/天
                result.setRecommendedInitialSize(20);
                result.setRecommendedMaxActive(100);
                result.setRecommendedMaxIdle(50);
                result.setRecommendedMinIdle(10);
            } else if (analysis.getQueryFrequency().getDailyCount() > 10000) { // 大于1万次/天
                result.setRecommendedInitialSize(10);
                result.setRecommendedMaxActive(50);
                result.setRecommendedMaxIdle(25);
                result.setRecommendedMinIdle(5);
            } else {
                result.setRecommendedInitialSize(5);
                result.setRecommendedMaxActive(20);
                result.setRecommendedMaxIdle(10);
                result.setRecommendedMinIdle(2);
            }

            return result;

        } catch (Exception e) {
            log.error("优化连接池策略失败", e);
            throw new MyBatisOptimizationException("优化连接池策略失败", e);
        }
    }

    /**
     * 优化SQL策略
     */
    private SqlOptimizationResult optimizeSqlStrategy(MyBatisOptimizationRequest request, 
            MyBatisPerformancePatternAnalysis analysis) {
        try {
            SqlOptimizationResult result = new SqlOptimizationResult();
            result.setRequestId(request.getRequestId());

            // 根据性能指标优化SQL策略
            if (analysis.getPerformanceMetrics().getAverageExecutionTime() > 1000) { // 大于1秒
                result.setRecommendedUseIndex(true);
                result.setRecommendedUseCache(true);
                result.setRecommendedBatchSize(100);
                result.setRecommendedUsePreparedStatement(true);
            } else if (analysis.getPerformanceMetrics().getAverageExecutionTime() > 100) { // 大于100ms
                result.setRecommendedUseIndex(true);
                result.setRecommendedUseCache(false);
                result.setRecommendedBatchSize(50);
                result.setRecommendedUsePreparedStatement(true);
            } else {
                result.setRecommendedUseIndex(false);
                result.setRecommendedUseCache(false);
                result.setRecommendedBatchSize(20);
                result.setRecommendedUsePreparedStatement(false);
            }

            return result;

        } catch (Exception e) {
            log.error("优化SQL策略失败", e);
            throw new MyBatisOptimizationException("优化SQL策略失败", e);
        }
    }
}

5.2 监控告警

5.2.1 MyBatis监控指标

/**
 * MyBatis监控指标
 */
@Component
public class MyBatisMetrics {

    private final MeterRegistry meterRegistry;

    public MyBatisMetrics(MeterRegistry meterRegistry) {
        this.meterRegistry = meterRegistry;
    }

    /**
     * 记录MyBatis查询次数
     */
    public void recordMyBatisQueryCount(String queryType, String status) {
        Counter.builder("mybatis.query.count")
                .description("MyBatis查询次数")
                .tag("query_type", queryType)
                .tag("status", status)
                .register(meterRegistry)
                .increment();
    }

    /**
     * 记录MyBatis查询时间
     */
    public void recordMyBatisQueryTime(String queryType, String status, long duration) {
        Timer.builder("mybatis.query.time")
                .description("MyBatis查询时间")
                .tag("query_type", queryType)
                .tag("status", status)
                .register(meterRegistry)
                .record(duration, TimeUnit.MILLISECONDS);
    }

    /**
     * 记录MyBatis查询成功率
     */
    public void recordMyBatisQuerySuccessRate(String queryType, double successRate) {
        Gauge.builder("mybatis.query.success.rate")
                .description("MyBatis查询成功率")
                .tag("query_type", queryType)
                .register(meterRegistry, successRate);
    }

    /**
     * 记录MyBatis查询失败率
     */
    public void recordMyBatisQueryFailureRate(String queryType, double failureRate) {
        Gauge.builder("mybatis.query.failure.rate")
                .description("MyBatis查询失败率")
                .tag("query_type", queryType)
                .register(meterRegistry, failureRate);
    }

    /**
     * 记录MyBatis查询吞吐量
     */
    public void recordMyBatisQueryThroughput(String queryType, double throughput) {
        Gauge.builder("mybatis.query.throughput")
                .description("MyBatis查询吞吐量")
                .tag("query_type", queryType)
                .register(meterRegistry, throughput);
    }

    /**
     * 记录MyBatis查询异常次数
     */
    public void recordMyBatisQueryExceptionCount(String queryType, String exceptionType) {
        Counter.builder("mybatis.query.exception.count")
                .description("MyBatis查询异常次数")
                .tag("query_type", queryType)
                .tag("exception_type", exceptionType)
                .register(meterRegistry)
                .increment();
    }
}

5.2.2 MyBatis告警规则

# prometheus-rules.yml
groups:
- name: mybatis_alerts
  rules:
  - alert: HighMyBatisQueryTime
    expr: mybatis_query_time{quantile="0.95"} > 5000
    for: 2m
    labels:
      severity: warning
    annotations:
      summary: "MyBatis查询时间过长"
      description: "MyBatis查询时间P95超过5秒，当前值: {{ $value }}ms"
  
  - alert: HighMyBatisQueryFailureRate
    expr: mybatis_query_failure_rate > 0.05
    for: 2m
    labels:
      severity: warning
    annotations:
      summary: "MyBatis查询失败率过高"
      description: "MyBatis查询失败率超过5%，当前值: {{ $value }}"
  
  - alert: LowMyBatisQueryThroughput
    expr: mybatis_query_throughput < 100
    for: 5m
    labels:
      severity: warning
    annotations:
      summary: "MyBatis查询吞吐量过低"
      description: "MyBatis查询吞吐量低于100次/秒，当前值: {{ $value }}"
  
  - alert: HighMyBatisQueryExceptionCount
    expr: rate(mybatis_query_exception_count[5m]) > 10
    for: 2m
    labels:
      severity: critical
    annotations:
      summary: "MyBatis查询异常次数过多"
      description: "MyBatis查询异常频率超过10次/分钟，当前值: {{ $value }}"
  
  - alert: MyBatisServiceDown
    expr: up{job="mybatis-service"} == 0
    for: 1m
    labels:
      severity: critical
    annotations:
      summary: "MyBatis服务宕机"
      description: "MyBatis服务已宕机超过1分钟"

六、总结

MyBatis模糊查询作为数据访问层的核心功能之一，通过合理的查询优化和性能提升策略，能够构建一个高效、稳定、可扩展的MyBatis查询系统。本文从MyBatis查询优化到性能提升，从基础实现到企业级应用，系统梳理了MyBatis模糊查询优化的完整解决方案。

6.1 关键要点

查询优化：通过索引优化、SQL优化、缓存优化等手段优化查询性能
性能提升：通过连接池优化、预编译语句、批量操作等手段提升性能
安全防护：通过参数化查询、预编译语句等手段防止SQL注入
监控告警：建立完善的监控体系，及时发现和处理问题
企业级方案：提供完整的企业级部署和监控方案

6.2 最佳实践

避免使用LIKE：尽量避免使用前导%的LIKE查询
使用索引：合理使用全文索引、前缀索引、复合索引等
使用缓存：合理使用MyBatis缓存、Redis缓存、本地缓存等
参数化查询：使用参数化查询防止SQL注入
监控告警：建立完善的监控体系，确保MyBatis查询服务稳定运行

通过以上措施，可以构建一个高效、稳定、可扩展的MyBatis查询系统，为企业的各种业务场景提供数据查询支持。