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问题,及时发现问题并进行优化。
