使用Mybatis Interceptor实现SQL执行时间记录
背景
为了优化项目体验，需要记录一下SQL的执行时间以便进行进行优化。
解决
新建SQL表
基于Mybatis Interceptor实现SQL的执行时间的记录，以便进行优化。新建一张sql_log表，用于存储执行的sql语句。
create table `sql_log`
(
`id` bigint auto_increment primary key comment '主键',
`method_name` varchar(255) not null comment '方法名',
`execute_time` varchar(20) null comment '执行时间',
`execute_sql` varchar(5000) null comment '执行SQL',
`sql_type` varchar(16) null comment 'SQL类型',
`create_time` datetime default current_timestamp() null comment '创建时间'
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='SQL日志表';
创建实体类
编写对应的java实体类
@Data
@TableName(value = "sql_log")
public class SqlLog {
// 主键
private Long id;
// 调用方法名称
private String methodName;
// 方法执行时间
private String executeTime;
// 执行SQL
private String executeSql;
// SQL类型
private String sqlType;
// 记录创建时间
private Date createTime;
}
编写Mybatis拦截器。
@Slf4j
@Intercepts({@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})})
// 配置文件读取是否启用此配置
@ConditionalOnProperty(prefix = "slow-sql-interceptor", name = "enabled", havingValue = "true")
@Component("sqlExecuteTimeInterceptor")
public class SqlExecuteTimeInterceptor implements Interceptor {
// 是否插入数据库标识
@Value("${slow-sql.insertDB:true}")
private boolean isInsertDB;
// 超时时间
@Value("${slow-sql.timeout:100}")
private Long timeout;
@Value("${log.queue.slow-sql}")
private String slowSqlQueue;
@Autowired
private RedisUtils redisUtils;
/**
* @Author thailandking
* @Date 2020/2/16 16:44
* @LastEditors thailandking
* @LastEditTime 2020/2/16 16:44
* @Description sql拦截器
*/
@Override
public Object intercept(Invocation invocation) throws Throwable {
Object target = invocation.getTarget();
long begin = System.currentTimeMillis();
StatementHandler statementHandler = (StatementHandler) target;
try {
return invocation.proceed();
} finally {
try {
long end = System.currentTimeMillis();
// 判断超时
if ((end - begin) > timeout) {
// 方法名称、类型
MetaObject metaObject = MetaObject.forObject(statementHandler, SystemMetaObject.DEFAULT_OBJECT_FACTORY,
SystemMetaObject.DEFAULT_OBJECT_WRAPPER_FACTORY, new DefaultReflectorFactory());
MappedStatement mappedStatement = (MappedStatement) metaObject.getValue("delegate.mappedStatement");
String methodName = mappedStatement.getId();
String sqlType = mappedStatement.getSqlCommandType().toString();
// 控制台打印日志
BoundSql boundSql = statementHandler.getBoundSql();
// 参数map
Object parameterObject = boundSql.getParameterObject();
// 参数列表
List<ParameterMapping> parameterMappingList = boundSql.getParameterMappings();
Configuration configuration = mappedStatement.getConfiguration();
// 格式化sql语句，去除换行符，替换参数
String sql = formatSQL(boundSql, parameterObject, parameterMappingList, configuration);
// 判断是否写入数据库
if (isInsertDB) {
SqlLog log = new SqlLog();
log.setMethodName(methodName);
log.setExecuteTime(String.valueOf(end - begin));
log.setExecuteSql(sql);
log.setSqlType(sqlType);
redisUtils.rpush(slowSqlQueue, JSONUtil.toJsonStr(log));
}
}
} catch (Exception e) {
log.error("执行 SQL 拦截器异常：{}", e.getMessage());
}
}
}
private String formatSQL(BoundSql boundSql, Object parameterObject, List<ParameterMapping> params,
Configuration configuration) {
String sql = boundSql.getSql();
if (StringUtils.isBlank(sql)) {
return "";
}
// SQL中多个空格使用一个空格代替
sql = sql.replaceAll("[\\s]+", " ");
if (!ObjectUtils.isEmpty(params) && !ObjectUtils.isEmpty(parameterObject)) {
// TypeHandlerRegistry 是 MyBatis 用来管理 TypeHandler 的注册器 TypeHandler 用于在 Java 类型和 JDBC 类型之间进行转换
TypeHandlerRegistry typeHandlerRegistry = configuration.getTypeHandlerRegistry();
// 如果参数对象的类型有对应的 TypeHandler，则使用 TypeHandler 进行处理
if (typeHandlerRegistry.hasTypeHandler(parameterObject.getClass())) {
sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(parameterObject)));
} else {
// 否则，逐个处理参数映射
for (ParameterMapping param : params) {
// 获取参数的属性名
String propertyName = param.getProperty();
MetaObject metaObject = configuration.newMetaObject(parameterObject);
// 检查对象中是否存在该属性的 getter 方法，如果存在就取出来进行替换
if (metaObject.hasGetter(propertyName)) {
Object obj = metaObject.getValue(propertyName);
sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj)));
// 检查 BoundSql 对象中是否存在附加参数
} else if (boundSql.hasAdditionalParameter(propertyName)) {
Object obj = boundSql.getAdditionalParameter(propertyName);
sql = sql.replaceFirst("\\?", Matcher.quoteReplacement(getParameterValue(obj)));
} else {
// SQL匹配不上，带上“缺失”方便找问题
sql = sql.replaceFirst("\\?", "缺失");
}
}
}
}
return sql;
}
private static String getParameterValue(Object object) {
String value = "";
if (object instanceof String) {
value = "'" + object + "'";
} else if (object instanceof Date) {
DateFormat format = DateFormat.getDateTimeInstance(DateFormat.DEFAULT, DateFormat.DEFAULT, Locale.CHINA);
value = "'" + format.format((Date) object) + "'";
} else if (!ObjectUtils.isEmpty(object)) {
value = object.toString();
}
return value;
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties) {
}
}
将拦截器注入Spring容器
@Configuration
@MapperScan(basePackages = {"com.iflytek.screen.dao.primary"}, sqlSessionFactoryRef = "sqlSessionFactoryMySQL")
public class MySQLDataSourceConfig {
@Bean(name = "dataSourceMySQL")
@ConfigurationProperties(prefix = "spring.datasource.primary")
public DataSource dataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "sqlSessionFactoryMySQL")
public SqlSessionFactory sqlSessionFactory(@Qualifier("dataSourceMySQL") DataSource dataSource,
@Qualifier("sqlExecuteTimeInterceptor") Interceptor interceptor) throws Exception {
MybatisSqlSessionFactoryBean bean = new MybatisSqlSessionFactoryBean();
bean.setDataSource(dataSource);
bean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/mysql/*.xml"));
MybatisConfiguration mybatisConfiguration = new MybatisConfiguration();
mybatisConfiguration.setMapUnderscoreToCamelCase(true);
mybatisConfiguration.setCacheEnabled(false);
// 配置打印sql语句
mybatisConfiguration.setLogImpl(StdOutImpl.class);
// 添加拦截器
mybatisConfiguration.addInterceptor(interceptor);
bean.setConfiguration(mybatisConfiguration);
return bean.getObject();
}
@Bean(name = "sqlSessionTemplateMySQL")
public SqlSessionTemplate sqlSessionTemplate(@Qualifier("sqlSessionFactoryMySQL") SqlSessionFactory sqlSessionFactory) {
return new SqlSessionTemplate(sqlSessionFactory);
}
@Bean(name = "transactionManagerMySQL")
public TransactionManager transactionManager(@Qualifier("dataSourceMySQL") DataSource dataSource) {
return new DataSourceTransactionManager(dataSource);
}
}
配置文件
# slow-sql
# 基于interceptor
slow-sql-interceptor:
enabled: true
# 日志是否插入数据库
slowSql:
insertDB: true
# 设置超时时间ms
timeout: 100
log:
queue:
slow-sql: slowSql
schedule:
switch: true
效果如下所示：
