前言：
优化SQL的前提是你已经发现了那些执行起来有问题或耗时很长的SQL。总有面试官会问你在平时生产/开发过程中你们是如何定位慢SQL的。
实现效果
1：自定义“开关”，自行决定是否开启慢SQL记录。
2：自定义时间阈值。即SQL执行时间超过此阈值则认定为慢SQL。
3：自定义记录慢SQL日志级别(info/warn)。
4：记录完整慢SQL信息(即记录的SQL带完整参数)，方便将SQL拷贝出来执行分析。
补充
抓取到慢SQL信息后你也可以选择将其写入Redis缓存中，再通过封装查询接口将缓存中的信息展示到自己的运维系统中。
Mybatis核心对象
Mybatis拦截器说明：
https://blog.csdn.net/wuyuxing24/article/details/89343951原理
Mybatis拦截器。通过Mybatis拦截器拦截Executor接口中的update/query/query/queryCursor方法。在拦截到当前SQL通过记录SQL执行时间与我们定义的慢SQL执行时间阈值来判断当前SQL执行是否超出定义的时间阈值。若超出则认定该SQL为慢SQL，再通过日志等方式记录当前执行SQL。
实现
1：依赖
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.4.0</version>
</parent>
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<exclusions>
<exclusion> <!-- 排除自带的日志系统 -->
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-logging</artifactId>
</exclusion>
</exclusions>
</dependency>
<!-- 日志依赖 -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-log4j2</artifactId>
</dependency>
<!-- Mybatis plus -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.1</version>
</dependency>
<!-- 连接驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<!-- Lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
</dependencies>2：配置
2.1：application.yml
说明：测试项目不再额外配置Mybatis/Mybatis-Plus，即只需引入Mybatis/Mybatis-Plus依赖即可。
server:
port: 80
spring:
datasource: # 数据库连接配置(线上外网测试库)
url: jdbc:mysql://127.0.0.1:3306/slow-sql?useSSL=true&useUnicode=true&characterEncoding=utf8&autoReconnect=true&zeroDateTimeBehavior=convertToNull&rewriteBatchedStatements=TRUE&serverTimezone=Asia/Shanghai
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
# 自定义慢SQL配置
mybatis:
sql:
log:
time: 3 # 慢SQL时间，单位/秒(SQL执行时间若超过此时间则认定为慢SQL)
logLevel: info # 以什么日志级别打印慢SQL信息
switch: true # 是否开启慢SQL打印开关
2.2：Mybatis拦截器配置
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.cache.CacheKey;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.ParameterMapping;
import org.apache.ibatis.mapping.ParameterMode;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.session.Configuration;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.ibatis.type.TypeHandlerRegistry;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.regex.Matcher;
/**
* @Intercepts：为Mybatis注解表示当前类为Mybatis拦截器。此拦截器参数为一个数组其参数@Signature注解用于指定拦截哪些Mybatis核心对象下的方法。
* 如下四个方法分别代表：
* update：执行update/insert/delete
* query：执行查询,先在缓存里面查找
* query：执行查询
* queryCursor：执行查询，查询结果放在Cursor里面
*/
@Slf4j
@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, CacheKey.class, BoundSql.class}),
@Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
@Signature(type = Executor.class, method = "queryCursor", args = {MappedStatement.class, Object.class, RowBounds.class})
})
public class ExecutorSqlInterceptor implements Interceptor {
/**
* 慢SQL时间阈值
*/
@Value("${mybatis.sql.log.time}")
private BigDecimal logTime;
/**
* 日志级别
*/
@Value("${mybatis.sql.log.logLevel}")
private String logLevel;
/**
* 日志记录开关，是否开启日志记录
*/
@Value("${mybatis.sql.log.switch}")
private Boolean logSwitch;
/**
* 对于时间类型参数，默认时间格式
*/
private static final DateFormat DATE_FORMAT = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
/**
* 代理对象每次调用的方法，就是要进行拦截的时候要执行的方法。在这个方法里面做我们自定义的逻辑处理
* invocation.getArgs()：返回值为数组。
* 该数组中有两个元素,0号元素存放执行当前SQL的MappedStatement对象。1号元素存放的是当前SQL的所有入参(键值对)形如：{key1=value1, key2=value2}
*/
@Override
public Object intercept(Invocation invocation) throws Throwable {
try {
if (!logSwitch){ // 是否开启对慢SQL日志记录
return invocation.proceed(); // 直接放行执行SQL
}
// 计时器 - 开始时间
long start = System.currentTimeMillis();
Object result = invocation.proceed();
// 计时器 - 结束时间
long end = System.currentTimeMillis();
// SQL执行耗时
long timing = end - start;
// SQL执行耗时
BigDecimal timingBigDecimal = new BigDecimal(timing);
// 慢SQL时间阈值
BigDecimal maxTime = logTime.multiply(new BigDecimal("1000")); // 3000ms
if (timingBigDecimal.compareTo(maxTime) >= 0) { // 当前SQL执行时间大于慢SQL阈值时间(表示此条SQL为慢SQL)
MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0]; // 获取执行当前SQL的MappedStatement对象
Object parameterObject = null;
if (invocation.getArgs().length > 1) {
parameterObject = invocation.getArgs()[1]; // 获取执行当前SQL的参数，形如：{key1=value1, key2=value2}
}
String statementId = mappedStatement.getId(); // 获取Mapper层当前执行SQL所在方法的全限定名形如：cn.slow.mapper.StuMapper.save
BoundSql boundSql = mappedStatement.getBoundSql(parameterObject); // BoundSql对象源码翻译：获取实际SQL字符串，SQL可以有SQL占位符“?”和一个参数映射列表(有序)，其中包含每个参数的附加信息(至少是要读取值的输入对象的属性名)。
Configuration configuration = mappedStatement.getConfiguration(); // Mybatis会在启动时读取所有的配置文件加载到内存中，Configuration对象就是承载整个配置的类。
String sql = getSql(boundSql, parameterObject, configuration);
switch (logLevel){ // 以配置文件指定的日志级别输出日志信息
case "debug":
if (log.isDebugEnabled()){
log.debug("执行sql耗时:{} ms - id:{} - Sql:{}", timing, statementId, sql);
}
break;
default:
if (log.isInfoEnabled()){
log.info("执行sql耗时:{} ms - id:{} - Sql:{}", timing, statementId, sql);
}
}
}
return result;
}catch (Exception e){
log.error("拦截sql异常:",e);
}
return invocation.proceed();
}
/**
* plugin方法是拦截器用于封装目标对象的，通过该方法我们可以返回目标对象本身，也可以返回一个它的代理
*
* 当返回的是代理的时候我们可以对其中的方法进行拦截来调用intercept方法 -- Plugin.wrap(target, this)
* 当返回的是当前对象的时候 就不会调用intercept方法，相当于当前拦截器无效
*/
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
/**
* 此方法用于将参数与SQL占位符对应起来
*/
private String getSql(BoundSql boundSql, Object parameterObject, Configuration configuration) {
String sql = boundSql.getSql().replaceAll("[\\s]+", " "); // 获取SQL
List<ParameterMapping> parameterMappings = boundSql.getParameterMappings(); // 获取SQL参数映射列表
TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry(); // 获取到类型处理器。类型处理器TypeHandlerRegistry用于处理javaType与jdbcType之间的类型转换用的处理器，Mybatis针对诸多Java类型与数据库类型进行了匹配处理
if (parameterMappings != null) { // 参数映射列表为空代表当前SQL没有从外部传入的参数。
for (int i = 0; i < parameterMappings.size(); i++) { // 循环参数列表
ParameterMapping parameterMapping = parameterMappings.get(i);
if (parameterMapping.getMode() != ParameterMode.OUT) { // 参数映射的类型有IN OUT INOUT，如果是OUT则不用做处理
Object value;
String propertyName = parameterMapping.getProperty(); // 获取映射参数的名称
if (boundSql.hasAdditionalParameter(propertyName)) { // 判断当前SQL中是否有此参数名,有的话根据参数名获取其值
value = boundSql.getAdditionalParameter(propertyName);
} else if (parameterObject == null) { // parameterObject用来存储映射关系形如：{key1=value1, key2=value2}。Map结构存储参数键值对
value = null;
} else if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
value = parameterObject;
} else {
MetaObject metaObject = configuration.newMetaObject(parameterObject); // 获取参数的元数据
value = metaObject.getValue(propertyName); // 获取元数据中的值
}
sql = replacePlaceholder(sql, value); // 根据参数类型拼接SQL入参
}
}
}
return sql;
}
/**
* 用参数将SQL中第一个占位符"?"替换掉。对于“字符串/时间”类型的参数会在其首尾拼接上单引号。
*/
private String replacePlaceholder(String sql, Object propertyValue) {
String result;
if (propertyValue != null) {
if (propertyValue instanceof String) {
result = "'" + propertyValue + "'";
} else if (propertyValue instanceof Date) {
result = "'" + DATE_FORMAT.format(propertyValue) + "'";
} else {
result = propertyValue.toString();
}
} else {
result = "null";
}
return sql.replaceFirst("\\?", Matcher.quoteReplacement(result)); // 把首个出现?的地方替换成指定值
}
}3：样例
为了方便测试。直接让controller访问mapper层，中间不再经由service到mapper。
controller
import cn.slow.mapper.StuMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.Map;
@RequestMapping("/stu")
@RestController
public class StuController {
@Autowired
private StuMapper stuMapper;
@PostMapping("/save")
public void save(@RequestBody Map map){
stuMapper.save(map);
}
}mapper
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import java.util.Map;
@Mapper
public interface StuMapper {
/** 保存 */
@Insert("INSERT INTO stu(name,age) VALUES(#{name},#{age})")
void save(Map map);
}特别说明：
上面2.2Mybatis拦截器配置，我们设定的慢SQL执行时间阈值为3000ms。显然我们目前SQL执行时间是不会超过3秒的。即可以将阈值改为3ms。
// 原慢SQL时间阈值 3000ms
BigDecimal maxTime = logTime.multiply(new BigDecimal("1000")); // 3000ms
// 现慢SQL时间阈值 3ms
BigDecimal maxTime = logTime.multiply(new BigDecimal("1")); // 3ms测试
模拟提交请求：
控制台输出：
785
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
