MyBatis慢查询监控插件

最新推荐文章于 2024-06-18 10:13:31 发布

fanfan_618

最新推荐文章于 2024-06-18 10:13:31 发布

阅读量2.2k

点赞数

分类专栏： java 文章标签：监控慢查询SQL

java 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

转自：http://blog.itpub.net/29254281/viewspace-1976044/

功能
1.定期统计每个SQL的执行次数,平均执行时间和平均返回行数,并且打印在日志中
2.如果有慢SQL或者返回行数很多的SQL,直接在日志中打印详细信息.

maven依赖

<dependencies>
<dependency>
<groupId>org.softee</groupId>
<artifactId>pojo-mbean</artifactId>
<version>1.1</version>
</dependency>
<dependency>
<groupId>commons-codec</groupId>
<artifactId>commons-codec</artifactId>
<version>1.9</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>jcl-over-slf4j</artifactId>
<version>1.7.9</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis</artifactId>
<version>3.2.8</version>
</dependency>
<dependency>
<groupId>org.mybatis</groupId>
<artifactId>mybatis-spring</artifactId>
<version>1.2.2</version>
</dependency>
</dependencies>

代码:

package com.vv.plugins;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.atomic.AtomicLong;
import javax.management.MalformedObjectNameException;
import org.apache.commons.codec.digest.DigestUtils;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.Interceptor;
import org.apache.ibatis.plugin.Intercepts;
import org.apache.ibatis.plugin.Invocation;
import org.apache.ibatis.plugin.Plugin;
import org.apache.ibatis.plugin.Signature;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.softee.management.annotation.MBean;
import org.softee.management.annotation.ManagedAttribute;
import org.softee.management.exception.ManagementException;
import org.softee.management.helper.MBeanRegistration;
@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 }) })
public class MapperInterceptor implements Interceptor {
public static Logger LOG = LoggerFactory.getLogger(MapperInterceptor.class);
private Properties properties;
private static Map MAP = new ConcurrentHashMap(400);
static {
Runnable r = new Runnable() {
@Override
public void run() {
while (true) {
Map oldMap = MAP;
MAP = new ConcurrentHashMap(400);
List list = new ArrayList(oldMap.values());
Collections.sort(list);
Iterator it = list.iterator();
while (it.hasNext()) {
SqlInfo sql = it.next();
long avgts = (sql.getTotalts().get() / sql.getTotalcount().get());
long avgrows = (sql.getTotalrows().get() / sql.getTotalcount().get());
LOG.info("接口 :" + sql.getMapper() + ",SQL_ID:" + sql.getMd5() + ",一共执行" + sql.getTotalcount()
+ "次,平均时间" + avgts + "毫秒," + "平均返回记录数" + avgrows);
}
try {
Thread.sleep(MonitorParameter.getInstance().getMonitorInterval() * 1000);
} catch (InterruptedException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
};
new Thread(r).start();
}
@Override
public Object intercept(Invocation invocation) throws Throwable {
long start = System.currentTimeMillis();
List returnValue = (List) invocation.proceed();
long end = System.currentTimeMillis();
Object[] args = invocation.getArgs();
MappedStatement ms = (MappedStatement) args[0];
Object p = args[1];
RowBounds bounds = (RowBounds) args[2];
String boundSql = ms.getBoundSql(p).getSql();
SqlInfo info = MAP.get(boundSql);
if (info == null) {
info = new SqlInfo(ms, p);
MAP.put(boundSql, info);
}
info.logSqlInfo(p, returnValue.size(), end - start);
return returnValue;
}
@Override
public Object plugin(Object target) {
return Plugin.wrap(target, this);
}
@Override
public void setProperties(Properties properties0) {
this.properties = properties0;
}
}
@MBean(objectName = "jsmx:type=MonitorParameter")
class MonitorParameter {
private int maxts = 300;
private int maxrows = 100;
private int monitorInterval = 30;
@ManagedAttribute
public int getMaxts() {
return maxts;
}
@ManagedAttribute
public void setMaxts(int maxts) {
this.maxts = maxts;
}
@ManagedAttribute
public int getMaxrows() {
return maxrows;
}
@ManagedAttribute
public void setMaxrows(int maxrows) {
this.maxrows = maxrows;
}
@ManagedAttribute
public int getMonitorInterval() {
return monitorInterval;
}
@ManagedAttribute
public void setMonitorInterval(int monitorInterval) {
this.monitorInterval = monitorInterval;
}
private static class SingletonHandler {
private static MonitorParameter obj;
static {
obj = new MonitorParameter();
try {
new MBeanRegistration(obj).register();
} catch (MalformedObjectNameException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ManagementException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
public static MonitorParameter getInstance() {
return SingletonHandler.obj;
}
}
class SqlInfo implements Comparable {
public static Logger LOG = LoggerFactory.getLogger(SqlInfo.class);
private String mapper;
private String sql;
private String md5;
private AtomicLong totalts = new AtomicLong(0);
private AtomicLong totalcount = new AtomicLong(0);
private AtomicLong totalrows = new AtomicLong(0);
public void setMapper(String mapper) {
this.mapper = mapper;
}
public SqlInfo(MappedStatement mappedStatement, Object parameterObject) {
this.sql = mappedStatement.getBoundSql(parameterObject).getSql();
this.mapper = mappedStatement.getId();
this.md5 = DigestUtils.md5Hex(sql);
}
public void logSqlInfo(Object para, int rows, long ts) {
totalts.addAndGet(ts);
totalcount.getAndIncrement();
totalrows.addAndGet(rows);
if (ts > MonitorParameter.getInstance().getMaxts() || rows > MonitorParameter.getInstance().getMaxrows()) {
LOG.warn("接口:" + this.mapper);
LOG.warn("SQL_ID:" + this.md5);
LOG.warn("SQL:" + this.sql);
LOG.warn("参数:" + para);
LOG.warn("执行时间:" + ts);
LOG.warn("返回记录:" + rows);
}
}
public String getSql() {
return sql;
}
public String getMd5() {
return md5;
}
public AtomicLong getTotalts() {
return totalts;
}
public AtomicLong getTotalcount() {
return totalcount;
}
public AtomicLong getTotalrows() {
return totalrows;
}
public String getMapper() {
return mapper;
}
@Override
public int compareTo(SqlInfo o) {
Long ts = this.getTotalcount().get();
Long ots = o.getTotalcount().get();
return ts.compareTo(ots);
}
}

使用:
将插件加入Spring管理,并作为插件添加到MyBatis

默认情况下,每30S打印SQL的统计信息,比如
17:56:29.023 [Thread-1] INFO com.vv.plugins.MapperInterceptor - 接口 :com.vv.songod.mapper.SodMapper.getAllUser,SQL_ID:9f683436fe68935af28708d8f2920792,一共执行1次,平均时间35毫秒,平均返回记录数513
17:56:29.023 [Thread-1] INFO com.vv.plugins.MapperInterceptor - 接口 :com.vv.songod.mapper.SodMapper.getUserByName,SQL_ID:e65912a2d6863ac310e66cbf3459ff46,一共执行2次,平均时间134毫秒,平均返回记录数1

默认如果有超过300ms执行时间或者返回行数超过100行的SQL,则直接打印在日志中.

另外程序添加了JMX支持,可以直接修改阈值而不需要重启程序.

超过 maxrows的返回行数,直接在日志打印
超过 maxts的执行时间,直接在日志打印
monitorInterval表示每个多少S打印一次统计信息.

如果需要打印所有SQL的详细信息,则可以把maxts直接设置为0

itpub把泛型替换了,文件下载:MapperInterceptor.txt

参考:
http://www.cnblogs.com/fangjian0423/p/mybatis-interceptor.html
http://www.cnblogs.com/babyhhcsy/p/4500884.html

fanfan_618

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MyBatis慢查询监控插件

转自：http://blog.itpub.net/29254281/viewspace-1976044/功能 1.定期统计每个SQL的执行次数,平均执行时间和平均返回行数,并且打印在日志中 2.如果有慢SQL或者返回行数很多的SQL,直接在日志中打印详细信息.maven依赖<dependencies> <dependen...
复制链接

扫一扫

专栏目录