Spring Mybatis 慢 SQL 监控

问题背景

系统经常卡顿，找不到原因。有时列表查询时间过长，造成系统无法正常访问

实现方案

在 Mybatis 的插件内做拦截。添加守护线程池，执行数据库查询之前启动守护线程。数据库查询执行完，尝试中断守护线程。若守护线程在超时时间段内未被中断，则记录日志提示 SQL 慢查询。

// ...
import org.apache.ibatis.mapping.*;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis..*;
import org.apache.ibatis.session.*;
import org.apache.logging.log4j.*;

import java.util.concurrent.*;

@Intercepts({
    @Signature(method="query", type= Executor.class, args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})
})
public class MyInterceptor implements Interceptor {

    private static ThreadPoolExecutor daemonThreadPoolExecutor = new ThreadPoolExecutor(1, 10, 1, TimeUnit.MILLISECONDS, new ArrayBlockingQueue<>(1));
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        if (invocation.getTarget() instanceof Executor) {
            long slowSqlExecuteTime = 3000L;
            TimeoutDaemonTask task = new TimeoutDaemontask(slowSqlExecuteTime, "traceId");
            Future<Long> daemonFuture = null;
            try {
                daemonFuture = daemonThreadPoolExecutor.submit(timeoutDaemonTask);
            } catch (RejectedExecutionException e) {
                logger.error("数据库慢查询监控失败");
                return invocation.proceed();
            }
            Object result = invocatoin.proceed();
            if (!daemonFUture.isDone() && !daemonFuture.isCancelled()) {
                daemonFuture.cancel(true);
            } else {
                logger.error("数据库执行时长：{}ms", timeoutDaemonTask.getExecuteTime());
            }
        }
    }
    
    private class TimeoutDaemonTask implements Callable<Long> {
        private long start = SYstem.currentTimeMillis();
        private long executeTime = -1;
        private long slowSqlExecuteTime = 5000L;
        private String traceId;
        public TimeoutDaemonTask(long slowSqlExecuteTime, String traceId) {
            this.slowSqlExecuteTime = slowSqlExecuteTime;
            this.traceId = traceId;
        }
        public long getExecuteTime() {
            return System.currentTimeMills() - start;
        }
        @Override
        public Long call() throws Execption {
            try {
                ThreadContext.put("TRACE_ID", traceId);
                Thread.sleep(slowSqlExecuteTime);
                logger.error("数据库超时");
                return executeTime;
            } catch (Execption e) {
                executeTime = System.currentTimeMills() - start;
                return executeTime;
            }
        }
    }
}

参考链接

• https://blog.csdn.net/Vincent2014Linux/article/details/110475093