背景：
在排查无忧行后台服务日志时，一个请求可能会包含多个执行的sql，经常会遇到SQL语句与对应参数不连续显示，或者参数较多需要逐个匹配的情况。这种情况下，如果需要还原完整SQL语句就会比较耗时。因此，我希望能优化这一流程。
正文：
在平时排查sql执行情况时，对于sql查询参数比较多或者多个sql在同一个请求下执行的时候会比较麻烦，需要查看每个参数的值一一对应检查是否有异常，有时候还需要将查询sql的问号还原去库里面查询结果，就类似下面的日志：
一个一个参数还原比较麻烦，因此针对这种问题，我找到了一个解决办法，重写MybatisPlus的InnerInterceptor，将sql日志重新组装，那么在查看sql日志时候就可以直接查看赋值好参数的sql了，将上面截图的sql整合之后可以转化为下面的sql：
具体的思路以及实现方案，以无忧行项目为例
实现思路，利用mybatisplus提供的接口InnerInterceptor，重写sql日志，并将重写之后的类注入到sqlSessionFactory中，在执行sql之前捕获到并打印。
解释一下InnerInterceptor类：InnerInterceptor 是 MyBatis-Plus 框架中的一个核心拦截器接口，它是 MyBatis 拦截器机制的扩展，用于在 SQL 执行过程中进行拦截和增强。
InnerInterceptor工作原理：InnerInterceptor 通过 MyBatis 的插件机制工作，在以下时机进行拦截：
- beforeQuery：查询操作执行前
- beforeUpdate：更新操作执行前
- beforePrepare：SQL 准备阶段
针对查询sql执行的sql重整，使用的是beforeQuery，针对更新sql执行的重整，使用的是beforeUpdate，以下是具体实现：
1.首先，添加依赖，
maven依赖：
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.0</version>
</dependency>
或者gradle依赖：
compile("com.baomidou:mybatis-plus-generator:3.4.0")
2.重写InnerInterceptor
package com.cmi.jego.micro.flight.admin.service.config;
import com.baomidou.mybatisplus.extension.plugins.inner.InnerInterceptor;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
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.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 java.sql.SQLException;
import java.text.DateFormat;
import java.util.Date;
import java.util.List;
import java.util.Locale;
import java.util.regex.Matcher;
/**
* sql日志重写
*
* @author zhouxy
* @date 2025年05月08日 11:27
*/
@Slf4j
@org.springframework.context.annotation.Configuration
public class MybatisPlusLogRewrite implements InnerInterceptor {
@Override
public void beforeQuery(Executor executor, MappedStatement ms, Object parameter, RowBounds rowBounds, ResultHandler resultHandler, BoundSql boundSql) throws SQLException {
log.info("beforeQuery");
logInfo(boundSql, ms, parameter);
}
@Override
public void beforeUpdate(Executor executor, MappedStatement ms, Object parameter) throws SQLException {
log.info("beforeUpdate");
BoundSql boundSql = ms.getBoundSql(parameter);
logInfo(boundSql, ms, parameter);
}
private static void logInfo(BoundSql boundSql, MappedStatement ms, Object parameter) {
try {
// 获取到节点的id,即sql语句的id
String sqlId = ms.getId();
// 获取节点的配置
Configuration configuration = ms.getConfiguration();
// 获取到最终的sql语句
String sql = getSql(configuration, boundSql, sqlId);
log.info("完整的sql:{}", sql);
} catch (Exception e) {
log.error("异常:{}", e.getLocalizedMessage(), e);
}
}
// 封装了一下sql语句，使得结果返回完整xml路径下的sql语句节点id + sql语句
public static String getSql(Configuration configuration, BoundSql boundSql, String sqlId) {
return sqlId + ":" + showSql(configuration, boundSql);
}
// 进行？的替换
public static String showSql(Configuration configuration, BoundSql boundSql) {
// 获取参数
Object parameterObject = boundSql.getParameterObject();
List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
// log.info("boundSql:{}",boundSql.getSql());
// sql语句中多个空格都用一个空格代替
String sql = boundSql.getSql().replaceAll("[\\s]+", " ");
if (CollectionUtils.isNotEmpty(parameterMappings) && parameterObject != null) {
// 获取类型处理器注册器，类型处理器的功能是进行java类型和数据库类型的转换
TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
// 如果根据parameterObject.getClass(）可以找到对应的类型，则替换
if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
sql = sql.replaceFirst("\\?",
Matcher.quoteReplacement(getParameterValue(parameterObject)));
} else {
// MetaObject主要是封装了originalObject对象，提供了get和set的方法用于获取和设置originalObject的属性值,主要支持对JavaBean、Collection、Map三种类型对象的操作
MetaObject metaObject = configuration.newMetaObject(parameterObject);
for (ParameterMapping parameterMapping : parameterMappings) {
String propertyName = parameterMapping.getProperty();
if (metaObject.hasGetter(propertyName)) {
Object obj = metaObject.getValue(propertyName);
sql = sql.replaceFirst("\\?",
Matcher.quoteReplacement(getParameterValue(obj)));
} else if (boundSql.hasAdditionalParameter(propertyName)) {
// 该分支是动态sql
Object obj = boundSql.getAdditionalParameter(propertyName);
sql = sql.replaceFirst("\\?",
Matcher.quoteReplacement(getParameterValue(obj)));
} else {
// 打印出缺失，提醒该参数缺失并防止错位
sql = sql.replaceFirst("\\?", "缺失");
}
}
}
}
return sql;
}
// 如果参数是String，则添加单引号， 如果是日期，则转换为时间格式器并加单引号； 对参数是null和不是null的情况作了处理
private static String getParameterValue(Object obj) {
String value;
if (obj instanceof String) {
value = "'" + obj.toString() + "'";
} else if (obj instanceof Date) {
DateFormat formatter = DateFormat.getDateTimeInstance(DateFormat.DEFAULT,
DateFormat.DEFAULT, Locale.CHINA);
value = "'" + formatter.format(new Date()) + "'";
} else {
if (obj != null) {
value = obj.toString();
} else {
value = "";
}
}
return value;
}
}
3.将当前的重写类MybatisPlusLogRewrite注入到sqlSessionFactory中。
package com.cmi.jego.micro.flight.admin.service.config;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import javax.annotation.PostConstruct;
import java.util.List;
/**
* @author zhouxy
* @date 2025年05月09日 15:58
*/
@Configuration
public class MyBatisPlusConfig {
@Autowired
private List<SqlSessionFactory> sqlSessionFactoryList;
@Autowired
private MybatisPlusLogRewrite myInnerInterceptor;
/**
* 添加Mybatis拦截器
*
* @author zhouxy
* @date 2025/5/15 16:29
*/
@PostConstruct
public void addMybatisInterceptor() {
for (SqlSessionFactory sqlSessionFactory : sqlSessionFactoryList) {
org.apache.ibatis.session.Configuration configuration = sqlSessionFactory.getConfiguration();
//将sql拦截器添加到MybatisPlusInterceptor拦截器链
MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
mybatisPlusInterceptor.addInnerInterceptor(myInnerInterceptor);
configuration.addInterceptor(mybatisPlusInterceptor);
}
}
}
关闭其他sql打印日志，执行查询sql，只打印当前重写之后的sql日志如下：
2025-05-14 18:03:01.353 INFO 34718 --- [ main] c.c.j.m.f.a.service.handler.LogHandler : function:[OrderRefundServiceImpl.queryOrderListStatusCount], request: {"lang":"zh_CN","pageNum":1,"pageSize":10}
2025-05-14 18:03:01.385 INFO 34718 --- [ main] c.c.j.m.f.a.s.c.MybatisPlusLogRewrite : beforeQuery
2025-05-14 18:03:01.387 INFO 34718 --- [ main] c.c.j.m.f.a.s.c.MybatisPlusLogRewrite : 完整的sql:com.cmi.jego.micro.flight.admin.service.mapper.jegotrip.TicketRefundMapper.queryListStatusCount:select distinct(tr.id) as refundId, tr.status from ticket_refund tr left join ticket_order td on tr.order_id = td.order_id left join ticket_passenger tp on tr.order_id = tp.order_id left join ticket_order_detail tod on tr.order_id = tod.order_id WHERE ( td.search_channel in ( 'hbgj-api-domestic-custom' ) or (td.order_source is null and td.search_channel is null) or td.order_source = '0' )
2025-05-14 18:03:01.395 WARN 34718 --- [ main] c.a.druid.pool.DruidAbstractDataSource : discard long time none received connection. , jdbcUrl : jdbc:mysql://localhost:3306/ticket_jegotrip?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8, version : 1.2.5, lastPacketReceivedIdleMillis : 263261
2025-05-14 18:03:01.395 WARN 34718 --- [ main] c.a.druid.pool.DruidAbstractDataSource : discard long time none received connection. , jdbcUrl : jdbc:mysql://localhost:3306/ticket_jegotrip?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8, version : 1.2.5, lastPacketReceivedIdleMillis : 263912
2025-05-14 18:03:01.396 WARN 34718 --- [ main] c.a.druid.pool.DruidAbstractDataSource : discard long time none received connection. , jdbcUrl : jdbc:mysql://localhost:3306/ticket_jegotrip?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8, version : 1.2.5, lastPacketReceivedIdleMillis : 263933
2025-05-14 18:03:01.396 WARN 34718 --- [ main] c.a.druid.pool.DruidAbstractDataSource : discard long time none received connection. , jdbcUrl : jdbc:mysql://localhost:3306/ticket_jegotrip?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8, version : 1.2.5, lastPacketReceivedIdleMillis : 263958
2025-05-14 18:03:01.396 WARN 34718 --- [ main] c.a.druid.pool.DruidAbstractDataSource : discard long time none received connection. , jdbcUrl : jdbc:mysql://localhost:3306/ticket_jegotrip?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8, version : 1.2.5, lastPacketReceivedIdleMillis : 263986
2025-05-14 18:03:01.851 INFO 34718 --- [ main] c.c.j.m.f.a.s.c.MybatisPlusLogRewrite : beforeQuery
2025-05-14 18:03:01.851 INFO 34718 --- [ main] c.c.j.m.f.a.s.c.MybatisPlusLogRewrite : 完整的sql:com.cmi.jego.micro.flight.admin.service.mapper.jegotrip.TicketRefundMoneyMapper.queryFailedByRefundIds:SELECT id , order_id, user_id, refund_id, pay_order_id, supplier_order_id, type, price, refund_price, status, refund_time, limit_time, refund_no, create_time, update_time, remark FROM ticket_refund_money WHERE refund_id IN ( 1295 , 1296 , 1423 , 1424 ) AND status = 4
2025-05-14 18:03:01.871 INFO 34718 --- [ main] c.c.j.m.f.a.service.handler.LogHandler : class:[OrderRefundServiceImpl.queryOrderListStatusCount] response: {"bizCode":0,"bizMsg":"SUCCESS","data":{"statusCounts":[{"count":1,"status":-1,"statusDesc":"已取消"},{"count":2,"status":3,"statusDesc":"已退票退款"},{"count":1,"status":4,"statusDesc":"退票异常"}]},"rpcCode":0,"rpcMsg":"SUCCESS"}, cost: 521,
2025-05-14 18:03:01.876 INFO 34718 --- [ main] c.c.j.m.f.a.s.s.OrderRefundServiceTest : {"bizCode":0,"bizMsg":"SUCCESS","data":{"statusCounts":[{"count":1,"status":-1,"statusDesc":"已取消"},{"count":2,"status":3,"statusDesc":"已退票退款"},{"count":1,"status":4,"statusDesc":"退票异常"}]},"rpcCode":0,"rpcMsg":"SUCCESS"}
性能评估方面
重写InnerInterceptor类其实是在SqlSessionFactory中添加了拦截器，那么性能如何呢？下面是我这边实际测试的时长对比
不添加sql重写：
添加sql重写：
根据测试时间可以看出，加了sql重写会比未加的时候的时间长了11ms。
因为涉及到拦截以及反射，性能比不加的时候会稍微耗时一些，
如果是访问量不高的情况下，用户对于加没加sql重写的主观感应时长其实差不多，因此这种sql重写方案适合后台访问并发量不高的情况使用。
如果是并发量较高且比较注重性能的情况下，不建议加。