前言

在数据库性能优化中，慢查询是最常见的问题之一。一条未优化的 SQL 可能导致整个系统响应变慢，甚至引发雪崩效应。然而，很多团队对慢查询的处理往往是被动的——等到用户反馈问题才去排查。

本文将介绍一套完整的慢查询自动捕获和优化方案，实现：

• 自动捕获：实时监控慢 SQL，无需人工介入
• 智能分析：自动分析 SQL 执行计划，识别性能瓶颈
• 优化建议：基于规则引擎推送针对性优化方案
• 及时通知：通过多渠道通知开发人员

一、慢查询问题分析

1. 慢查询的危害

┌─────────────────────────────────────────────────────────────┐
│                    慢查询的危害链                            │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  单条慢 SQL                                                   │
│      │                                                      │
│      ▼                                                      │
│  数据库连接池耗尽                                              │
│      │                                                      │
│      ▼                                                      │
│  新请求无法获取连接                                            │
│      │                                                      │
│      ▼                                                      │
│  请求排队/超时                                                │
│      │                                                      │
│      ▼                                                      │
│  系统响应变慢 → 用户体验差 → 业务损失                          │
│                                                             │
└─────────────────────────────────────────────────────────────┘

2. 常见慢查询场景

3. 传统排查方式的痛点

┌─────────────────────────────────────────────────────────────┐
│                   传统排查方式痛点                            │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  1. 用户反馈慢                                               │
│      ↓                                                      │
│  2. 登录服务器查看日志                                        │
│      ↓                                                      │
│  3. 手动分析 SQL                                             │
│      ↓                                                      │
│  4. 执行 EXPLAIN 分析                                        │
│      ↓                                                      │
│  5. 尝试优化                                                 │
│      ↓                                                      │
│  6. 验证效果                                                 │
│                                                             │
│  问题：                                                       │
│  - 响应滞后：用户已经受到影响才处理                            │
│  - 效率低下：人工排查耗时耗力                                  │
│  - 容易遗漏：无法覆盖所有慢查询                                │
│  - 知识门槛：需要 DBA 专业知识                                 │
│                                                             │
└─────────────────────────────────────────────────────────────┘

二、整体架构设计

1. 系统架构

┌─────────────────────────────────────────────────────────────┐
│                   慢查询自动捕获系统架构                       │
├─────────────────────────────────────────────────────────────┤
│                                                             │
│  ┌─────────────────────────────────────────────────────┐   │
│  │                   数据采集层                         │   │
│  │  ┌──────────────┐  ┌──────────────┐                │   │
│  │  │ MySQL 慢日志  │  │ 应用层拦截   │                │   │
│  │  │ 监控         │  │ 器           │                │   │
│  │  └──────┬───────┘  └──────┬───────┘                │   │
│  │         │                 │                        │   │
│  │         └────────┬────────┘                        │   │
│  │                  ▼                                 │   │
│  │         ┌──────────────┐                          │   │
│  │         │ 慢查询队列   │                          │   │
│  │         └──────┬───────┘                          │   │
│  └────────────────┼──────────────────────────────────┘   │
│                   │                                        │
│                   ▼                                        │
│  ┌─────────────────────────────────────────────────────┐   │
│  │                   分析处理层                         │   │
│  │  ┌──────────────┐  ┌──────────────┐                │   │
│  │  │ SQL 解析器   │  │ 执行计划分析 │                │   │
│  │  └──────┬───────┘  └──────┬───────┘                │   │
│  │         │                 │                        │   │
│  │         └────────┬────────┘                        │   │
│  │                  ▼                                 │   │
│  │         ┌──────────────┐                          │   │
│  │         │ 优化建议引擎 │                          │   │
│  │         └──────┬───────┘                          │   │
│  └────────────────┼──────────────────────────────────┘   │
│                   │                                        │
│                   ▼                                        │
│  ┌─────────────────────────────────────────────────────┐   │
│  │                   通知推送层                         │   │
│  │  ┌──────────┐ ┌──────────┐ ┌──────────┐           │   │
│  │  │ 邮件通知 │ │ 钉钉通知 │ │ 日志记录 │           │   │
│  │  └──────────┘ └──────────┘ └──────────┘           │   │
│  └─────────────────────────────────────────────────────┘   │
│                                                             │
└─────────────────────────────────────────────────────────────┘

2. 核心组件

三、代码实现

1. 项目结构

SpringBoot-SlowQuery-Demo/
├── src/
│   └── main/
│       ├── java/
│       │   └── com/
│       │       └── example/
│       │           └── slowquery/
│       │               ├── SlowQueryApplication.java
│       │               ├── config/
│       │               │   ├── SlowQueryConfig.java
│       │               │   └── MyBatisConfig.java
│       │               ├── interceptor/
│       │               │   └── SlowQueryInterceptor.java
│       │               ├── analyzer/
│       │               │   ├── SqlAnalyzer.java
│       │               │   ├── ExplainAnalyzer.java
│       │               │   └── OptimizationAdvisor.java
│       │               ├── entity/
│       │               │   ├── SlowQueryLog.java
│       │               │   ├── SqlAnalysisResult.java
│       │               │   └── OptimizationSuggestion.java
│       │               ├── repository/
│       │               │   └── SlowQueryLogRepository.java
│       │               ├── service/
│       │               │   ├── SlowQueryService.java
│       │               │   ├── NotificationService.java
│       │               │   └── UserService.java
│       │               ├── controller/
│       │               │   └── UserController.java
│       │               └── dto/
│       │                   └── ApiResponse.java
│       └── resources/
│           ├── application.yml
│           └── mapper/
│               └── UserMapper.xml
├── pom.xml
└── README.md

2. 慢查询配置

# application.yml
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/test?useSSL=false&serverTimezone=UTC
    username: root
    password: root
    driver-class-name: com.mysql.cj.jdbc.Driver
    
  slow-query:
    enabled: true
    threshold: 1000  # 慢查询阈值（毫秒）
    max-sql-length: 2000  # 最大 SQL 长度
    sample-rate: 1.0  # 采样率（1.0 表示全部采集）
    
    notification:
      enabled: true
      channels: email,dingtalk  # 通知渠道
      email:
        to: dev@example.com
        subject: "慢查询告警"
      dingtalk:
        webhook: https://oapi.dingtalk.com/robot/send?access_token=xxx
        
    analysis:
      enabled: true
      explain-timeout: 5000  # EXPLAIN 超时时间（毫秒）
      suggestion-level: all  # 建议级别：all/high/medium

3. 慢查询拦截器

@Component
@Slf4j
public class SlowQueryInterceptor implements Interceptor {

    @Autowired
    private SlowQueryService slowQueryService;

    @Value("${slow-query.threshold:1000}")
    private long threshold;

    @Value("${slow-query.enabled:true}")
    private boolean enabled;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        if (!enabled) {
            return invocation.proceed();
        }

        long startTime = System.currentTimeMillis();
        Object result = null;
        Throwable error = null;

        try {
            result = invocation.proceed();
            return result;
        } catch (Throwable e) {
            error = e;
            throw e;
        } finally {
            long cost = System.currentTimeMillis() - startTime;
            if (cost >= threshold) {
                captureSlowQuery(invocation, cost, error);
            }
        }
    }

    private void captureSlowQuery(Invocation invocation, long cost, Throwable error) {
        try {
            MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
            Object parameter = invocation.getArgs()[1];

            String sql = getSql(mappedStatement, parameter);
            String method = mappedStatement.getId();

            SlowQueryLog logEntry = SlowQueryLog.builder()
                .sql(sql)
                .method(method)
                .cost(cost)
                .threshold(threshold)
                .createTime(LocalDateTime.now())
                .error(error != null ? error.getMessage() : null)
                .build();

            slowQueryService.handleSlowQuery(logEntry);

        } catch (Exception e) {
            log.error("捕获慢查询失败", e);
        }
    }

    private String getSql(MappedStatement mappedStatement, Object parameter) {
        BoundSql boundSql = mappedStatement.getBoundSql(parameter);
        return boundSql.getSql().replaceAll("\\s+", " ").trim();
    }

}

4. SQL 分析器

@Service
@Slf4j
public class SqlAnalyzer {

    @Autowired
    private DataSource dataSource;

    @Value("${slow-query.analysis.explain-timeout:5000}")
    private int explainTimeout;

    public SqlAnalysisResult analyze(String sql) {
        SqlAnalysisResult result = new SqlAnalysisResult();
        result.setSql(sql);

        try {
            // 解析 SQL 类型
            parseSqlType(sql, result);

            // 获取执行计划
            ExplainResult explain = getExplainResult(sql);
            result.setExplainResult(explain);

            // 分析风险点
            analyzeRisks(sql, explain, result);

        } catch (Exception e) {
            log.error("SQL 分析失败: {}", sql, e);
            result.setError(e.getMessage());
        }

        return result;
    }

    private void parseSqlType(String sql, SqlAnalysisResult result) {
        String upperSql = sql.toUpperCase();
        if (upperSql.startsWith("SELECT")) {
            result.setSqlType("SELECT");
        } else if (upperSql.startsWith("INSERT")) {
            result.setSqlType("INSERT");
        } else if (upperSql.startsWith("UPDATE")) {
            result.setSqlType("UPDATE");
        } else if (upperSql.startsWith("DELETE")) {
            result.setSqlType("DELETE");
        }
    }

    private ExplainResult getExplainResult(String sql) throws SQLException {
        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement("EXPLAIN " + sql)) {
            
            stmt.setQueryTimeout(explainTimeout / 1000);
            
            try (ResultSet rs = stmt.executeQuery()) {
                ExplainResult result = new ExplainResult();
                List<ExplainRow> rows = new ArrayList<>();
                
                while (rs.next()) {
                    ExplainRow row = new ExplainRow();
                    row.setId(rs.getString("id"));
                    row.setSelectType(rs.getString("select_type"));
                    row.setTable(rs.getString("table"));
                    row.setType(rs.getString("type"));
                    row.setPossibleKeys(rs.getString("possible_keys"));
                    row.setKey(rs.getString("key"));
                    row.setKeyLen(rs.getString("key_len"));
                    row.setRef(rs.getString("ref"));
                    row.setRows(rs.getLong("rows"));
                    row.setExtra(rs.getString("Extra"));
                    rows.add(row);
                }
                
                result.setRows(rows);
                return result;
            }
        }
    }

    private void analyzeRisks(String sql, ExplainResult explain, SqlAnalysisResult result) {
        List<String> risks = new ArrayList<>();
        
        for (ExplainRow row : explain.getRows()) {
            // 检查全表扫描
            if ("ALL".equals(row.getType())) {
                risks.add("表 '" + row.getTable() + "' 发生全表扫描");
            }
            
            // 检查索引失效
            if (row.getKey() == null && row.getPossibleKeys() != null) {
                risks.add("表 '" + row.getTable() + "' 未使用索引，可能索引失效");
            }
            
            // 检查大表扫描
            if (row.getRows() > 100000) {
                risks.add("表 '" + row.getTable() + "' 扫描行数过多: " + row.getRows());
            }
            
            // 检查 Using filesort
            if (row.getExtra() != null && row.getExtra().contains("Using filesort")) {
                risks.add("表 '" + row.getTable() + "' 使用了文件排序");
            }
            
            // 检查 Using temporary
            if (row.getExtra() != null && row.getExtra().contains("Using temporary")) {
                risks.add("表 '" + row.getTable() + "' 使用了临时表");
            }
        }
        
        result.setRisks(risks);
        result.setRiskLevel(risks.isEmpty() ? "LOW" : (risks.size() > 2 ? "HIGH" : "MEDIUM"));
    }

}

5. 优化建议引擎

@Service
@Slf4j
public class OptimizationAdvisor {

    public List<OptimizationSuggestion> generateSuggestions(SqlAnalysisResult analysis) {
        List<OptimizationSuggestion> suggestions = new ArrayList<>();
        String sql = analysis.getSql();
        
        // 检查 SELECT *
        if (sql.matches("(?i)SELECT\\s+\\*\\s+FROM")) {
            suggestions.add(OptimizationSuggestion.builder()
                .type("SELECT_FIELDS")
                .severity("MEDIUM")
                .title("避免使用 SELECT *")
                .description("SELECT * 会查询所有字段，增加 IO 和内存消耗")
                .suggestion("只查询需要的字段，例如：SELECT id, name FROM table")
                .example("SELECT id, name, email FROM users WHERE status = 1")
                .build());
        }
        
        // 检查 LIMIT 大偏移量
        if (sql.matches("(?i).*LIMIT\\s+\\d+,\\s*\\d+")) {
            suggestions.add(OptimizationSuggestion.builder()
                .type("PAGINATION")
                .severity("HIGH")
                .title("优化大分页查询")
                .description("大偏移量的 LIMIT 查询性能差")
                .suggestion("使用覆盖索引或游标分页")
                .example("SELECT id FROM users WHERE id > 1000000 LIMIT 10")
                .build());
        }
        
        // 检查函数导致索引失效
        if (sql.matches("(?i).*WHERE\\s+\\w+\\s*\\(.*\\).*=")) {
            suggestions.add(OptimizationSuggestion.builder()
                .type("INDEX_INVALID")
                .severity("HIGH")
                .title("避免在索引列上使用函数")
                .description("函数会导致索引失效，触发全表扫描")
                .suggestion("改写 SQL，避免在 WHERE 条件中使用函数")
                .example("WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'")
                .build());
        }
        
        // 检查隐式类型转换
        if (sql.matches("(?i).*WHERE\\s+\\w+_id\\s*=\\s*['\"].*['\"]")) {
            suggestions.add(OptimizationSuggestion.builder()
                .type("IMPLICIT_CAST")
                .severity("MEDIUM")
                .title("避免隐式类型转换")
                .description("字符串和数字比较会导致索引失效")
                .suggestion("确保类型一致，不要给数字类型的字段传字符串")
                .example("WHERE user_id = 123 (而不是 '123')")
                .build());
        }
        
        // 检查 IN 子句数量
        if (sql.matches("(?i).*IN\\s*\\([^)]{500,}\\)")) {
            suggestions.add(OptimizationSuggestion.builder()
                .type("IN_CLAUSE")
                .severity("MEDIUM")
                .title("优化大 IN 子句")
                .description("IN 子句元素过多会影响性能")
                .suggestion("使用临时表或 JOIN 替代大 IN 子句")
                .example("JOIN temp_table ON t.id = temp_table.id")
                .build());
        }
        
        // 检查 OR 条件
        if (sql.toUpperCase().contains(" OR ")) {
            suggestions.add(OptimizationSuggestion.builder()
                .type("OR_CONDITION")
                .severity("LOW")
                .title("考虑优化 OR 条件")
                .description("OR 条件可能导致索引失效")
                .suggestion("考虑使用 UNION ALL 或改写为 IN")
                .example("SELECT * FROM t WHERE id IN (1, 2, 3)")
                .build());
        }
        
        // 根据执行计划风险添加建议
        for (String risk : analysis.getRisks()) {
            if (risk.contains("全表扫描")) {
                suggestions.add(OptimizationSuggestion.builder()
                    .type("FULL_TABLE_SCAN")
                    .severity("HIGH")
                    .title("添加合适的索引")
                    .description(risk)
                    .suggestion("为查询条件列添加索引")
                    .example("CREATE INDEX idx_status ON table(status)")
                    .build());
            }
        }
        
        return suggestions;
    }

    public String formatSuggestions(List<OptimizationSuggestion> suggestions) {
        if (suggestions.isEmpty()) {
            return "暂无优化建议";
        }
        
        StringBuilder sb = new StringBuilder();
        sb.append("## 优化建议\n\n");
        
        for (int i = 0; i < suggestions.size(); i++) {
            OptimizationSuggestion s = suggestions.get(i);
            sb.append(String.format("### %d. %s [%s]\n", i + 1, s.getTitle(), s.getSeverity()));
            sb.append(String.format("- **问题**: %s\n", s.getDescription()));
            sb.append(String.format("- **建议**: %s\n", s.getSuggestion()));
            sb.append(String.format("- **示例**: `%s`\n", s.getExample()));
            sb.append("\n");
        }
        
        return sb.toString();
    }

}

6. 通知服务

@Service
@Slf4j
public class NotificationService {

    @Value("${slow-query.notification.enabled:false}")
    private boolean enabled;

    @Autowired
    private JavaMailSender mailSender;

    @Value("${slow-query.notification.email.to:}")
    private String emailTo;

    public void notifySlowQuery(SlowQueryLog slowQuery, 
                                SqlAnalysisResult analysis,
                                List<OptimizationSuggestion> suggestions) {
        if (!enabled) {
            return;
        }

        try {
            // 发送邮件通知
            sendEmailNotification(slowQuery, analysis, suggestions);
            
            // 发送钉钉通知
            sendDingTalkNotification(slowQuery, analysis, suggestions);
            
        } catch (Exception e) {
            log.error("发送慢查询通知失败", e);
        }
    }

    private void sendEmailNotification(SlowQueryLog slowQuery, 
                                      SqlAnalysisResult analysis,
                                      List<OptimizationSuggestion> suggestions) {
        try {
            MimeMessage message = mailSender.createMimeMessage();
            MimeMessageHelper helper = new MimeMessageHelper(message, true, "UTF-8");
            
            helper.setTo(emailTo);
            helper.setSubject("【慢查询告警】" + slowQuery.getMethod());
            
            String content = buildEmailContent(slowQuery, analysis, suggestions);
            helper.setText(content, true);
            
            mailSender.send(message);
            log.info("慢查询邮件通知已发送: {}", slowQuery.getMethod());
            
        } catch (Exception e) {
            log.error("发送邮件通知失败", e);
        }
    }

    private String buildEmailContent(SlowQueryLog slowQuery, 
                                    SqlAnalysisResult analysis,
                                    List<OptimizationSuggestion> suggestions) {
        StringBuilder sb = new StringBuilder();
        sb.append("<h2>慢查询告警</h2>");
        sb.append("<table border='1' cellpadding='5'>");
        sb.append("<tr><td>执行方法</td><td>").append(slowQuery.getMethod()).append("</td></tr>");
        sb.append("<tr><td>执行时间</td><td>").append(slowQuery.getCost()).append(" ms</td></tr>");
        sb.append("<tr><td>阈值</td><td>").append(slowQuery.getThreshold()).append(" ms</td></tr>");
        sb.append("<tr><td>风险等级</td><td>").append(analysis.getRiskLevel()).append("</td></tr>");
        sb.append("</table>");
        
        sb.append("<h3>SQL</h3>");
        sb.append("<pre>").append(slowQuery.getSql()).append("</pre>");
        
        if (!analysis.getRisks().isEmpty()) {
            sb.append("<h3>风险点</h3>");
            sb.append("<ul>");
            for (String risk : analysis.getRisks()) {
                sb.append("<li>").append(risk).append("</li>");
            }
            sb.append("</ul>");
        }
        
        if (!suggestions.isEmpty()) {
            sb.append("<h3>优化建议</h3>");
            for (int i = 0; i < suggestions.size(); i++) {
                OptimizationSuggestion s = suggestions.get(i);
                sb.append("<h4>").append(i + 1).append(". ").append(s.getTitle());
                sb.append(" [").append(s.getSeverity()).append("]</h4>");
                sb.append("<p><b>问题:</b> ").append(s.getDescription()).append("</p>");
                sb.append("<p><b>建议:</b> ").append(s.getSuggestion()).append("</p>");
                sb.append("<p><b>示例:</b> <code>").append(s.getExample()).append("</code></p>");
            }
        }
        
        return sb.toString();
    }

    private void sendDingTalkNotification(SlowQueryLog slowQuery, 
                                         SqlAnalysisResult analysis,
                                         List<OptimizationSuggestion> suggestions) {
        // 实现钉钉机器人通知
        log.info("发送钉钉通知: {}", slowQuery.getMethod());
    }

}

7. 慢查询处理服务

@Service
@Slf4j
public class SlowQueryService {

    @Autowired
    private SlowQueryLogRepository slowQueryLogRepository;

    @Autowired
    private SqlAnalyzer sqlAnalyzer;

    @Autowired
    private OptimizationAdvisor optimizationAdvisor;

    @Autowired
    private NotificationService notificationService;

    @Async
    public void handleSlowQuery(SlowQueryLog slowQuery) {
        log.warn("发现慢查询: method={}, cost={}ms", 
                slowQuery.getMethod(), slowQuery.getCost());

        // 保存慢查询日志
        slowQueryLogRepository.save(slowQuery);

        // 分析 SQL
        SqlAnalysisResult analysis = sqlAnalyzer.analyze(slowQuery.getSql());
        slowQuery.setAnalysisResult(analysis);

        // 生成优化建议
        List<OptimizationSuggestion> suggestions = 
            optimizationAdvisor.generateSuggestions(analysis);
        slowQuery.setSuggestions(suggestions);

        // 发送通知
        notificationService.notifySlowQuery(slowQuery, analysis, suggestions);

        // 记录日志
        logSlowQueryDetails(slowQuery, analysis, suggestions);
    }

    private void logSlowQueryDetails(SlowQueryLog slowQuery, 
                                    SqlAnalysisResult analysis,
                                    List<OptimizationSuggestion> suggestions) {
        log.warn("\n========== 慢查询详情 ==========");
        log.warn("方法: {}", slowQuery.getMethod());
        log.warn("耗时: {} ms", slowQuery.getCost());
        log.warn("SQL: {}", slowQuery.getSql());
        log.warn("风险等级: {}", analysis.getRiskLevel());
        
        if (!analysis.getRisks().isEmpty()) {
            log.warn("风险点:");
            for (String risk : analysis.getRisks()) {
                log.warn("  - {}", risk);
            }
        }
        
        if (!suggestions.isEmpty()) {
            log.warn("优化建议:");
            for (int i = 0; i < suggestions.size(); i++) {
                OptimizationSuggestion s = suggestions.get(i);
                log.warn("  {}. {} [{}]", i + 1, s.getTitle(), s.getSeverity());
                log.warn("     问题: {}", s.getDescription());
                log.warn("     建议: {}", s.getSuggestion());
            }
        }
        log.warn("================================\n");
    }

    public List<SlowQueryLog> getRecentSlowQueries(int limit) {
        return slowQueryLogRepository.findTopByOrderByCreateTimeDesc(
            PageRequest.of(0, limit));
    }

    public Map<String, Object> getStatistics() {
        Map<String, Object> stats = new HashMap<>();
        
        LocalDateTime today = LocalDateTime.now().with(LocalTime.MIN);
        stats.put("todayCount", slowQueryLogRepository.countByCreateTimeAfter(today));
        
        stats.put("highRiskCount", 
            slowQueryLogRepository.countByRiskLevel("HIGH"));
        
        stats.put("avgCost", slowQueryLogRepository.getAverageCost());
        
        return stats;
    }

}

四、高级功能

1. 慢查询统计报表

@RestController
@RequestMapping("/api/slow-query")
public class SlowQueryController {

    @Autowired
    private SlowQueryService slowQueryService;

    @GetMapping("/recent")
    public ApiResponse<List<SlowQueryLog>> getRecentQueries(
            @RequestParam(defaultValue = "20") int limit) {
        return ApiResponse.success(slowQueryService.getRecentSlowQueries(limit));
    }

    @GetMapping("/statistics")
    public ApiResponse<Map<String, Object>> getStatistics() {
        return ApiResponse.success(slowQueryService.getStatistics());
    }

    @GetMapping("/trend")
    public ApiResponse<List<Map<String, Object>>> getTrend(
            @RequestParam @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate start,
            @RequestParam @DateTimeFormat(iso = DateTimeFormat.ISO.DATE) LocalDate end) {
        return ApiResponse.success(slowQueryService.getTrend(start, end));
    }

}

2. 动态阈值调整

@Component
public class DynamicThresholdManager {

    @Autowired
    private SlowQueryLogRepository repository;

    private volatile long currentThreshold = 1000;

    @Scheduled(cron = "0 0 2 * * ?")
    public void adjustThreshold() {
        // 基于历史数据动态调整阈值
        double avgCost = repository.getAverageCostLast7Days();
        double p95Cost = repository.getP95CostLast7Days();
        
        // 新阈值 = P95 的 80%
        long newThreshold = (long) (p95Cost * 0.8);
        
        // 限制在合理范围
        newThreshold = Math.max(500, Math.min(newThreshold, 5000));
        
        if (Math.abs(newThreshold - currentThreshold) > 200) {
            log.info("调整慢查询阈值: {} -> {}", currentThreshold, newThreshold);
            currentThreshold = newThreshold;
        }
    }

    public long getCurrentThreshold() {
        return currentThreshold;
    }

}

五、最佳实践

1. 阈值设置建议

2. 采样策略

@Component
public class SamplingStrategy {

    @Value("${slow-query.sample-rate:1.0}")
    private double sampleRate;

    private final ThreadLocalRandom random = ThreadLocalRandom.current();

    public boolean shouldSample() {
        return random.nextDouble() < sampleRate;
    }

    // 根据 SQL 特征决定是否采样
    public boolean shouldSample(String sql) {
        // 重要 SQL 全量采集
        if (isCriticalSql(sql)) {
            return true;
        }
        
        // 普通 SQL 按采样率
        return shouldSample();
    }

    private boolean isCriticalSql(String sql) {
        String upperSql = sql.toUpperCase();
        return upperSql.contains("PAYMENT") || 
               upperSql.contains("ORDER") ||
               upperSql.contains("ACCOUNT");
    }

}

3. 监控指标

六、常见问题

Q1: 开启慢查询监控会影响性能吗？

A:

• 影响很小，主要是 SQL 执行时间的计算
• 可以通过采样率控制开销
• 建议生产环境采样率设置为 0.1-0.5

Q2: 如何避免误报？

A:

• 设置合理的阈值
• 排除定时任务等预期慢查询
• 使用白名单机制
• 基于历史数据动态调整阈值

Q3: 如何处理大量慢查询？

A:

• 按风险等级优先处理
• 集中处理同类问题
• 建立优化知识库
• 定期复盘和培训

更多技术文章，欢迎关注公众号"服务端技术精选"，及时获取最新动态。