Java慢SQL拦截案例

wen java案例 2026-08-03 184

Java慢SQL拦截方案详解

基于MyBatis拦截器的实现

@Component
@Intercepts({
    @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),
    @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})
})
public class SlowSqlInterceptor implements Interceptor {
    private static final Logger logger = LoggerFactory.getLogger(SlowSqlInterceptor.class);
    // 慢SQL阈值（毫秒）
    private static final long SLOW_SQL_THRESHOLD = 1000;
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        long startTime = System.currentTimeMillis();
        try {
            return invocation.proceed();
        } finally {
            long endTime = System.currentTimeMillis();
            long costTime = endTime - startTime;
            if (costTime > SLOW_SQL_THRESHOLD) {
                // 获取SQL信息
                MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
                Object parameter = invocation.getArgs()[1];
                BoundSql boundSql = mappedStatement.getBoundSql(parameter);
                String sql = boundSql.getSql();
                String sqlId = mappedStatement.getId();
                // 记录慢SQL
                logger.warn("Slow SQL detected - SQL ID: {}, Cost: {}ms, SQL: {}", 
                    sqlId, costTime, formatSql(sql));
                // 发送告警通知（可选）
                sendAlert(sqlId, costTime, sql);
            }
        }
    }
    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }
    @Override
    public void setProperties(Properties properties) {
        // 可以读取配置参数
    }
    // 格式化SQL
    private String formatSql(String sql) {
        return sql.replaceAll("\\s+", " ").trim();
    }
    // 发送告警（可扩展为钉钉、邮件等）
    private void sendAlert(String sqlId, long costTime, String sql) {
        // 异步发送告警
        CompletableFuture.runAsync(() -> {
            // 实现告警逻辑
            logger.info("Sending alert for slow SQL: {}", sqlId);
        });
    }
}

基于Spring AOP的实现

@Aspect
@Component
public class SlowQueryAspect {
    private static final Logger logger = LoggerFactory.getLogger(SlowQueryAspect.class);
    @Autowired
    private SlowSqlMonitor slowSqlMonitor;
    // 拦截Mapper接口
    @Around("execution(* com.example.mapper.*.*(..))")
    public Object aroundMapperMethods(ProceedingJoinPoint pjp) throws Throwable {
        long startTime = System.currentTimeMillis();
        try {
            return pjp.proceed();
        } finally {
            long costTime = System.currentTimeMillis() - startTime;
            if (costTime > slowSqlMonitor.getThreshold()) {
                String methodName = pjp.getSignature().getDeclaringTypeName() 
                    + "." + pjp.getSignature().getName();
                // 记录慢查询
                slowSqlMonitor.record(methodName, costTime, pjp.getArgs());
            }
        }
    }
}

完整监控实现（包括告警和统计）

@Service
public class SlowSqlMonitorService {
    private static final Logger logger = LoggerFactory.getLogger(SlowSqlMonitorService.class);
    // 慢SQL配置
    @Value("${slow.sql.threshold:1000}")
    private long slowSqlThreshold;
    @Value("${slow.sql.alert.enabled:true}")
    private boolean alertEnabled;
    // 慢SQL统计缓存
    private final Cache<String, SlowSqlStat> statsCache = CacheBuilder.newBuilder()
        .maximumSize(100)
        .expireAfterWrite(1, TimeUnit.HOURS)
        .build();
    @EventListener
    public void handleSlowSqlEvent(SlowSqlEvent event) {
        // 1. 记录日志
        logSlowSql(event);
        // 2. 更新统计
        updateStats(event);
        // 3. 发送告警
        if (alertEnabled && event.getCostTime() > slowSqlThreshold * 10) {
            sendAlertForVerySlowQuery(event);
        }
        // 4. 保存到数据库（可选）
        saveSlowSqlRecord(event);
    }
    private void logSlowSql(SlowSqlEvent event) {
        logger.warn("Slow SQL Alert - SQL ID: {}, Cost: {}ms, SQL: {}, Params: {}", 
            event.getSqlId(),
            event.getCostTime(),
            event.getSql(),
            JSON.toJSONString(event.getParams())
        );
    }
    private void updateStats(SlowSqlEvent event) {
        String key = event.getSqlId();
        try {
            statsCache.get(key, () -> {
                SlowSqlStat stat = new SlowSqlStat();
                stat.setSqlId(key);
                return stat;
            });
            // 更新统计信息
            SlowSqlStat stat = statsCache.getIfPresent(key);
            if (stat != null) {
                stat.incrementCount();
                stat.addTotalTime(event.getCostTime());
                stat.setMaxTime(Math.max(stat.getMaxTime(), event.getCostTime()));
                stat.setSql(event.getSql());
                // 如果超过告警阈值，执行告警
                if (stat.getCount() % 10 == 0) {
                    statsCache.put(key, stat);
                    logger.warn("Slow SQL frequency alert - {}: {} times, avg {}ms", 
                        key, stat.getCount(), stat.getAvgTime());
                }
            }
        } catch (Exception e) {
            logger.error("Failed to update stats", e);
        }
    }
    private void sendAlertForVerySlowQuery(SlowSqlEvent event) {
        // 集成钉钉/企业微信/邮件告警
        String content = String.format(
            "慢SQL告警\nSQL ID: %s\n执行时间: %dms\nSQL: %s",
            event.getSqlId(),
            event.getCostTime(),
            event.getSql()
        );
        // 异步发送告警
        CompletableFuture.runAsync(() -> {
            try {
                // 钉钉告警示例
                DingTalkClient.sendAlert(content);
                // 邮件告警
                mailService.sendAlertEmail(content);
            } catch (Exception e) {
                logger.error("Failed to send alert", e);
            }
        });
    }
    private void saveSlowSqlRecord(SlowSqlEvent event) {
        // 保存到数据库
        try {
            sqlLogMapper.insert(event.toEntity());
        } catch (Exception e) {
            logger.error("Failed to save slow sql record", e);
        }
    }
    // 慢SQL统计类
    @Data
    public static class SlowSqlStat {
        private String sqlId;
        private AtomicLong count = new AtomicLong(0);
        private AtomicLong totalTime = new AtomicLong(0);
        private volatile long maxTime;
        private String sql;
        public void incrementCount() {
            count.incrementAndGet();
        }
        public void addTotalTime(long time) {
            totalTime.addAndGet(time);
        }
        public long getAvgTime() {
            long c = count.get();
            return c == 0 ? 0 : totalTime.get() / c;
        }
    }
}
// 慢SQL事件类
@Data
public class SlowSqlEvent {
    private String sqlId;
    private String sql;
    private Object params;
    private long costTime;
    private Date timestamp;
    private String threadName;
}

基于JDBC Connection拦截

@Configuration
public class JdbcInterceptorConfig {
    @Bean
    public HikariCPConnectionInterceptor hikariCPConnectionInterceptor() {
        return new HikariCPConnectionInterceptor();
    }
    public static class HikariCPConnectionInterceptor {
        private static final Logger logger = LoggerFactory.getLogger(HikariCPConnectionInterceptor.class);
        @Bean
        public ProxyFactoryBean proxyFactoryBean(DataSource dataSource) {
            ProxyFactoryBean pfb = new ProxyFactoryBean();
            pfb.setTarget(dataSource);
            pfb.setInterceptorNames("jdbcInterceptor");
            return pfb;
        }
        @Bean
        public JdbcInterceptor jdbcInterceptor() {
            return new JdbcInterceptor() {
                @Override
                public Object invoke(MethodInvocation methodInvocation) throws Throwable {
                    String methodName = methodInvocation.getMethod().getName();
                    if ("prepareStatement".equals(methodName)) {
                        Object[] args = methodInvocation.getArguments();
                        String sql = (String) args[0];
                        // 对SQL进行包装
                        args[0] = new SlowSqlPreparedStatement(sql);
                        methodInvocation.getArguments()[0] = args[0];
                    }
                    return methodInvocation.proceed();
                }
            };
        }
    }
    // 自定义PreparedStatement包装器
    public static class SlowSqlPreparedStatement implements InvocationHandler {
        private final String sql;
        private final long startTime;
        public SlowSqlPreparedStatement(String sql) {
            this.sql = sql;
            this.startTime = System.currentTimeMillis();
        }
        @Override
        public Object invoke(Object proxy, Method method, Object[] args) throws Throwable {
            if ("executeQuery".equals(method.getName()) || "executeUpdate".equals(method.getName())) {
                long elapsed = System.currentTimeMillis() - startTime;
                if (elapsed > 1000) {
                    logger.warn("Slow SQL: {} - {}ms", sql, elapsed);
                }
            }
            return method.invoke(proxy, args);
        }
    }
}

使用配置和扩展点

# application.yml配置
spring:
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    hikari:
      connection-timeout: 30000
      validation-timeout: 5000
      idle-timeout: 600000
      max-lifetime: 1800000
      maximum-pool-size: 20
      minimum-idle: 5
# 自定义慢SQL配置
slow-sql:
  enabled: true
  threshold: 1000  # 毫秒
  alert-enabled: true
  alert-frequency: 10  # 每10次同SQL告警
  sql-log:
    enabled: true
    max-length: 2000
    save-to-db: true

// 使用示例
@Service
public class UserService {
    @Autowired
    private UserMapper userMapper;
    @Autowired
    private SlowSqlMonitorService slowSqlMonitor;
    public User getUserWithComplexQuery(Long id) {
        long startTime = System.currentTimeMillis();
        try {
            // 可能产生慢查询的业务逻辑
            List<User> users = userMapper.findComplexUsers(id);
            return users.stream().findFirst().orElse(null);
        } finally {
            long costTime = System.currentTimeMillis() - startTime;
            if (costTime > 1000) {
                // 手动记录慢查询
                slowSqlMonitor.recordSlowSql(
                    "UserService.getUserWithComplexQuery",
                    costTime,
                    "SELECT ... FROM user WHERE ..."
                );
            }
        }
    }
}

监控效果

// 使用示例 - 输出效果
// [WARN] Slow SQL detected - SQL ID: com.example.mapper.UserMapper.selectUsers, 
// Cost: 1500ms, SQL: SELECT * FROM users WHERE status = 1 ORDER BY create_time DESC
// 
// [INFO] Slow SQL stats - UserMapper.selectUsers: 5 times, avg 1200ms, max 2000ms

通过以上实现，可以有效监控Java应用中的慢SQL问题,及时发现问题并进行优化。

Java慢SQL拦截案例

本文地址： https://www.bmjup.com/post/37159.html