Spring Boot中写一个MyBatis全表扫描拦截器

Spring Boot中写一个MyBatis全表扫描拦截器

全表扫描、内容全部加载到内存等学徒工常见操作防不胜防。例如之前发生的故事：
https://lizhiyong.blog.csdn.net/article/details/149923948

在大内存服务器可以使用JDK17+ZGC，堆内存怼到TB级GC时间也可以控制在10ms，这种情况就不易发生OOM了。我256GB服务器双击打开40GB的log灰常有信心。

但是在多应用混部的小内存服务器，堆内存动辄2GB、4GB就会很难过。稍大一点的对象就得斟酌了。。。这种情况也不是容器化部署等方式能解决的。

古人写了很多的Mybatis表操作，有xml有@注解，一个个去翻看逻辑肯定是要搞到天荒地老。。。

故笔者搞了个全局的MyBatis全表扫描拦截器，第一阶段先把触发全表扫描的情况记录下来，第二阶段就是整治这些异常情况了【例如默认写死个where 1=1，不满足条件的直接抛异常或者发告警】

代码实现

配置类

由于使用了@Component，可以不用写配置类了

package com.zhiyong;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.springframework.context.annotation.Bean;

import javax.sql.DataSource;

public class MyBatisInterCeptorConfig {

    @Bean
    public MybatisNoWhereSelectInterceptor1 mybatisNoWhereSelectInterceptor1() {
        return new MybatisNoWhereSelectInterceptor1();
    }

    @Bean
    public SqlSessionFactory sqlSessionFactory(DataSource dataSource, MybatisNoWhereSelectInterceptor1 mybatisNoWhereSelectInterceptor1) throws Exception {
        org.apache.ibatis.session.Configuration configuration = new org.apache.ibatis.session.Configuration();
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dataSource);
        factoryBean.setConfiguration(configuration);
        factoryBean.setPlugins(mybatisNoWhereSelectInterceptor1);
        return factoryBean.getObject();
    }
}

拦截逻辑

package com.zhiyong;

import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.springframework.stereotype.Component;

import java.sql.Connection;


@Slf4j
@Data
@Intercepts({
        @Signature(type = StatementHandler.class,
                method = "prepare",
                args = {Connection.class, Integer.class})
})
@Component
public class MybatisNoWhereSelectInterceptor1 implements Interceptor {
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        long t1 = System.currentTimeMillis();
        StatementHandler handler = (StatementHandler) PluginUtils.realTarget(invocation.getTarget());
        MetaObject metaHandler = SystemMetaObject.forObject(handler);

        MappedStatement mappedStatement = (MappedStatement) metaHandler.getValue("delegate.mappedStatement");
        SqlCommandType commandType = mappedStatement.getSqlCommandType();

        if (SqlCommandType.SELECT.equals(commandType)) {
            BoundSql boundSql = handler.getBoundSql();
            if (!boundSql.getSql().toLowerCase().contains("where")) {
                System.err.println("没有where触发全表扫描的sql【" + boundSql.getSql() + "】");
                log.error("没有where触发全表扫描的sql【{}】" + boundSql.getSql());
                long t2 = System.currentTimeMillis();
                log.info("没有where触发全表扫描,拦截器耗时{}ms", t2 - t1);
            }
        }


        return invocation.proceed();
    }

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

在预编译阶段先给把log打出来记录下，后续再补充逻辑校验，例如：

写了join不写on

select *

in套in

join次数过多

from和join的都是大表

学徒工是真的吓人。。。

获取原生插件

package com.zhiyong;

import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;

import java.lang.reflect.Proxy;


public class PluginUtils {

    public static Object realTarget(Object target) {
        if (Proxy.isProxyClass(target.getClass())) {
            MetaObject metaObject = SystemMetaObject.forObject(target);
            return realTarget(metaObject.getValue("h.target"));
        }
        return target;
    }
}

验证

controller

package com.ruoyi.form.controller;

import com.zhiyong.mapper.FunctionTestMapper;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletResponse;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

@Controller
@RequestMapping("/Function/Test")
@Slf4j
public class FunctionTestController {

    @Autowired
    FunctionTestMapper functionTestMapper;

    @PostMapping("/test1")
    @ApiOperation("测试功能1")
    @ResponseBody
    public Map test1(@RequestBody String s1) {
        List<String> strings = functionTestMapper.test1(s1);
        return success(strings);
    }

    @PostMapping("/test2")
    @ApiOperation("测试功能2")
    @ResponseBody
    public Map test2(@RequestBody String s1) {
        List<String> strings = functionTestMapper.test2(s1);
        return success(strings);
    }

    @PostMapping("/test3")
    @ApiOperation("测试功能3")
    @ResponseBody
    public Map test3(@RequestBody String s1) {
        List<String> strings = functionTestMapper.test3(s1);
        return success(strings);
    }

    @PostMapping("/select")
    @ApiOperation("数据查询")
    @ResponseBody
    public Map selectBySql(@RequestBody String sql) {
        List<Map<String, String>> strings = functionTestMapper.selectBySql(sql);
        return success(strings);
    }
}

Mapper接口

package com.zhiyong.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;


public interface FunctionTestMapper extends BaseMapper {

    @Select("select taskNo from machine where id=${s1}")
    List<String> test1(@Param("s1") String s1);

    @Select("select taskNo from machine;")
    List<String> test2(@Param("s1") String s1);

    List<String> test3(String s3);

    @Select("${sql}")
    List<Map<String, String>> selectBySql(@Param("sql") String sql);
}

注解方式，通过接口验证后打出了log

Mapper的xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zhiyong.mapper.FunctionTestMapper">

    <resultMap type="String" id="FunctionTestResult">
        <result property="taskNo"    column="taskNo"    />
    </resultMap>

    <select id="test3" resultType="String">
        select taskNo from machine
    </select>

    <select id="test4" parameterType="String" resultType="String">
        select taskNo from machine where flag= #{flag}
    </select>

</mapper>

xml方式，通过接口验证后打出了log。

全局异常捕获

package com.exception;

/**
 * 全局异常
 */
@RestControllerAdvice
public class GlobalExceptionHandler {    @ExceptionHandler(Exception.class)
    public String handleException(Exception e) {
        log.error("全局异常{}", e.getMessage());
        return "服务器错误，请联系管理员xxx";
    }

为了便于快速从log中直接看出引发OOM的堆栈，而无需去机房拿Dump文件，可以用Spring Boot搞个全局的异常捕获。

转载请注明出处：https://lizhiyong.blog.csdn.net/article/details/149924010