mybatis配置拦截器进行sql慢查询处理（附代码）

一.背景

        数据库Mysql自带的慢查询不好用，检索起来不方便。

        从程序层面找出慢查询的sql，主要以query，update，insert等，将相关的慢查询数据结构化存入到数据库，方便线上环境排查。       

二.代码

        当前代码的技术架构为：SpringBoot1.5x+Mybatis；未使用Mybatis-Plus；

        使用Mybatis的拦截器，实现Interceptor接口        

@Intercepts({
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
        @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),
//        @Signature(type = StatementHandler.class, method = "query", args = {Statement.class, ResultHandler.class}),
//        @Signature(type = StatementHandler.class, method = "update", args = {Statement.class}),
//        @Signature(type = StatementHandler.class, method = "batch", args = {Statement.class}),
//        @Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class,Integer.class}),
})
public class SlowQueryInterceptor implements Interceptor {

        在mybatis-config.xml中plugin作为插件配置如下：        

    <plugins>
        <plugin interceptor="xxx.SlowQueryInterceptor">
            <property name="slowQueryThreshold" value="1"/>
        </plugin>
		<plugin interceptor="xxx.PageHelper">
        	<property name="dialect" value="mysql"/>
		</plugin>
	</plugins>

  一般mybatis会使用PageHelper分页插件，引入方式：        

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.4.2</version>
</dependency>

---------分割线---------

注1：在mybatis-config.xml如果有多个拦截器，执行的顺序以在xml的中顺序一致。

注2：@Signature中，type的类型可以选择StatementHandler.class或者Executor.class，

区别为：StatementHandler.class直接打印sql语句，如下：

StatementHandler statementHandler = (StatementHandler) invocation;
sql = statementHandler.getBoundSql().getSql();

Executor.class需要通过Invocation invocation获取sql语句和相关参数。

注3：这里的method，要和type类中定义的一致，包括后面的args，具体如下：

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

<E> List<E> query(MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler) throws SQLException;

---------分割线---------
@Signature(type = StatementHandler.class, method = "update", args = {Statement.class}),

int update(Statement statement)
      throws SQLException;

注4：在同一个拦截器中Intercept中，Executor.class和StatementHandler.class不能同时使用，会重复执行。

注5：完整代码如下

package xxx.config;

import xxx.SpringContextHolder;
import org.apache.ibatis.executor.CachingExecutor;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.Method;
import java.util.Properties;

@Intercepts({
        @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
        @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class}),
//        @Signature(type = StatementHandler.class, method = "query", args = {Statement.class, ResultHandler.class}),
//        @Signature(type = StatementHandler.class, method = "update", args = {Statement.class}),
//        @Signature(type = StatementHandler.class, method = "batch", args = {Statement.class}),
//        @Signature(type = StatementHandler.class, method = "prepare", args = {Connection.class,Integer.class}),
})
public class SlowQueryInterceptor implements Interceptor {

    private final static Logger logger = LoggerFactory.getLogger(SlowQueryInterceptor.class);


    private static long SLOW_QUERY_THRESHOLD = 100; // 慢查询阈值为1000毫秒

    private static final String APP_CLASS_NAME = "appClass";

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        String appName = "";
        try {
            AppClass appClass = SpringContextHolder.getBean(APP_CLASS_NAME);
            appName = appClass.getAppName();
        } catch (Exception e) {
            logger.error("get app name error,",e);
        }
        long startTime = System.currentTimeMillis();
        try {
            //模拟耗时
            Thread.sleep(2000L);
            return invocation.proceed();
        } finally {
            long endTime = System.currentTimeMillis();
            long executionTime = endTime - startTime;
            String sql = "";
            if (executionTime > SLOW_QUERY_THRESHOLD) {
                Object obj = invocation.getTarget();
                Object[] args = invocation.getArgs();
                Method method = invocation.getMethod();

                String queryType = method.getName();

                MappedStatement mappedStatement = (MappedStatement) args[0];
                String xmlPath = mappedStatement.getResource();
                String xmlId   = mappedStatement.getId();
                String sqlText = mappedStatement.getSqlSource().getBoundSql(null).getSql();

                logger.info("------耗时sql信息------start------");
                logger.info("------appName={}",appName);
                logger.info("------queryType={}",queryType);
                logger.info("------xmlPath={}",xmlPath);
                logger.info("------xmlId={}",xmlId);
                logger.info("------sqlText={}",sqlText);
                logger.info("------params={}",args[1] == null?"":args[1].toString());
                logger.info("------耗时 {} 毫秒",executionTime);
                logger.info("------耗时sql信息------End------");
                logger.info("");
            }else {
                logger.info("耗时："+executionTime + "毫秒");
            }
        }
    }

    @Override
    public Object plugin(Object target) {
        return target instanceof Executor ? Plugin.wrap(target, this) : target;
    }

    @Override
    public void setProperties(Properties properties) {
        //通过配置文件设置慢查询阈值
        String threshold = properties.getProperty("slowQueryThreshold");
        if (threshold != null) {
            SLOW_QUERY_THRESHOLD = Long.parseLong(threshold);
        }
    }

}

效果图：

三.参考文章

Mybatis拦截器注解@Intercepts与@Signature注解属性说明-优快云博客

mybatis拦截器的注解说明_org.apache.ibatis.plugin.signature method-优快云博客

mybatis @Intercepts的用法-优快云博客

@Intercepts-mybatis拦截器-优快云博客

【MybBatis高级篇】MyBatis 拦截器_mybatis拦截器-优快云博客

Mybatis拦截器_@intercepts-优快云博客