@Interceptors 统计DB数据库执行用时毫秒数ms过滤类

最新推荐文章于 2022-05-03 19:45:13 发布

原创最新推荐文章于 2022-05-03 19:45:13 发布 · 483 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍了一种简单的方法来记录SQL语句或DAO操作的执行耗时，并通过示例展示了如何使用AOP（面向切面编程）来实现这一功能。通过分析日志输出，可以快速定位到耗时较长的操作并进行优化。

工作中经常碰到需要统计分析方法执行耗时的情形，以此来确定优化SQL语句或者DAO操作逻辑。

很简单，写一个类就OK，完全可以搞定。

public class DBOperationRecorder {
	
	private final Logger log = CMSLoggerFactory.getInstacne().getLogger(this.getClass());
	
	@AroundInvoke
	public Object recordDBOperation(InvocationContext ctx) throws Exception{
		
		Object result = null;
		
		long startTime = new Date().getTime();
		
		result = ctx.proceed();
		
		long endTime = new Date().getTime();
		
		StringBuffer sb = new StringBuffer();
		
		sb.append("DB execution time: ").append(endTime - startTime).append("ms").append(" Method: " + ctx.getMethod());
		
		log.debug(sb.toString());
		
		return result;
	}

}

在需要使用的地方加上注解，比如EJB3.0使用方法如下：

@Stateless
@Interceptors({DBOperationRecorder.class})
public class DeployHistoryDAOImpl extends JPABaseDAO<DeployHistoryDTO> implements DeployHistoryDAO{
 method.......
}

日志文件打印出来的最终效果如下：

10:40:43.271 [http-0.0.0.0-36300-6] DEBUG com.cup.cms.infrastructure.core.aop.DBOperationRecorder - DB execution time: 2ms Method: p
ublic java.util.List com.cup.cms.infrastructure.persistence.dao.jpa.MenuDAOImpl.getSubMenuList(long)
10:40:43.284 [http-0.0.0.0-36300-6] DEBUG com.cup.cms.infrastructure.core.aop.DBOperationRecorder - DB execution time: 8ms Method: p
ublic java.util.List com.cup.cms.infrastructure.persistence.dao.jpa.ContentTypeGroupDAOImpl.findContentTypeGroupList()
10:40:43.298 [http-0.0.0.0-36300-6] DEBUG com.cup.cms.infrastructure.core.aop.DBOperationRecorder - DB execution time: 12ms Method: 
public java.util.List com.cup.cms.infrastructure.persistence.dao.jpa.ContentTypeDAOImpl.findContentTypeListByGroupID(java.lang.Strin
g,long)
10:40:44.529 [http-0.0.0.0-36300-6] DEBUG com.cup.cms.infrastructure.core.aop.DBOperationRecorder - DB execution time: 1227ms Method
: public com.cup.cms.infrastructure.entity.ChmgmTicketCouponInf com.cup.cms.infrastructure.persistence.dao.jpa.ChmgmTicketCouponInfD
AOImpl.findChmgmTicketCouponInfWithoutPreferMchntById(java.lang.String)
10:40:44.604 [http-0.0.0.0-36300-6] DEBUG com.cup.cms.infrastructure.core.aop.DBOperationRecorder - DB execution time: 71ms Method: 
public com.cup.cms.infrastructure.entity.BusinessContentDTO com.cup.cms.infrastructure.persistence.dao.jpa.BusinessContentDAOImpl.fi
ndBusinessContent(long)
10:40:44.628 [http-0.0.0.0-36300-6] DEBUG com.cup.cms.infrastructure.core.aop.DBOperationRecorder - DB execution time: 20ms Method: 
public com.cup.cms.infrastructure.entity.AssemblyDTO com.cup.cms.infrastructure.persistence.dao.jpa.AssemblyDAOImpl.findAssemblyByAs
semblyID(long)
10:40:44.798 [http-0.0.0.0-36300-6] DEBUG com.cup.cms.infrastructure.core.aop.DBOperationRecorder - DB execution time: 11ms Method: 
public java.util.List com.cup.cms.infrastructure.persistence.dao.jpa.MaktgEventDAOImpl.findTopLvlTblChmgmMaktgEvents()
10:40:44.802 [http-0.0.0.0-36300-6] DEBUG com.cup.cms.infrastructure.core.aop.DBOperationRecorder - DB execution time: 2ms Method: p
ublic java.util.List com.cup.cms.infrastructure.persistence.dao.jpa.MaktgEventDAOImpl.findTblChmgmMaktgEvents(java.lang.String)
10:40:44.807 [http-0.0.0.0-36300-6] DEBUG com.cup.cms.infrastructure.core.aop.DBOperationRecorder - DB execution time: 3ms Method: p
ublic com.cup.cms.infrastructure.entity.PictureBoxDTO com.cup.cms.infrastructure.persistence.dao.jpa.PictureBoxDAOImpl.findPictureBo
xByName(java.lang.String)
10:40:44.810 [http-0.0.0.0-36300-6] DEBUG com.cup.cms.infrastructure.core.aop.DBOperationRecorder - DB execution time: 2ms Method: p
ublic com.cup.cms.infrastructure.entity.PictureBoxDTO com.cup.cms.infrastructure.persistence.dao.jpa.PictureBoxDAOImpl.findPictureBo
xByName(java.lang.String)
10:40:44.811 [http-0.0.0.0-36300-6] DEBUG com.cup.cms.web.framework.handler.RoledNavigaitonHandler - fromAction: #{NavigationControl
ler.menuNavigation} outcome: orgCouponDetailManage

可以看出来.ChmgmTicketCouponInfD
AOImpl.findChmgmTicketCouponInfWithoutPreferMchntById(java.lang.String)这个方法耗时较长，可以查看代码，进行针对性的优化。

1、数据库表频繁查询字段加索引

2、针对内部查询和业务处理逻辑优化重写代码等等。