工作中经常碰到需要统计分析方法执行耗时的情形,以此来确定优化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、针对内部查询和业务处理逻辑优化重写代码等等。