一次查询耗时的分析过程

测试环境数据移植到生产环境 一个查询从秒查到11s 

控制器代码

@RequestMapping(value = "list", method = RequestMethod.GET)
public ModelAndView list(SearchRevitVO vo, Integer sortType , HttpServletRequest request,@Language String language) throws Exception {


String searchKey = StringUtils.trimToEmpty(vo.getKey());

Integer pageSize = vo.getPageSize();
Integer pageNo = vo.getPageNo();
String sortString = "addTime" ;//默认时间
if(sortType == null || sortType==0){
sortString =""; //默认不排序 就是最相关
}else if(sortType==1) {
sortString = "addTime"; //时间
}else if(sortType==2) {
sortString = "downTimes"; //下载次数
}else if(sortType==3) {
sortString = "score"; // 评级
}else if(sortType==4) {
sortString = "money";
}
String[] tagIds = null;
Long[] tagIdsTmp = null;
if (vo.getTagId() != null && !vo.getTagId().equals("")) {
tagIds = vo.getTagId().split("-");
tagIdsTmp = new Long[tagIds.length];
for (int i = 0; i < tagIds.length; i++) {
tagIdsTmp[i] = Long.parseLong(tagIds[i]);
}
}
//String str1=StringUtils.join(tagIdsTmp, ",");
//分两次查询 查一次选中多个type 后 过滤出来的插件id

Long[] revitIds =  revitNewEntityService.getChooseMoreThanOneTypeId(tagIdsTmp);
long time1 = System.currentTimeMillis()-begin; 
System.out.println("time1: "+Double.parseDouble(String.valueOf(time1))/1000);
//获得数据库本来的值type
ServletContext servletContext = request.getSession().getServletContext();
List<RevitType> revitTypeList = (List<RevitType>) servletContext.getAttribute("revitTypeList");

long time2 = System.currentTimeMillis()-begin;
System.out.println("time2: "+ Double.parseDouble(String.valueOf(time2))/1000);
List<RevitNewEntity> result = (List<RevitNewEntity>) revitNewEntityService.getRevitAddInList(vo, pageSize,
pageNo,sortString,revitIds); //加个参数 type 类型分类可能多个 再传个数组

long time3 = System.currentTimeMillis()-begin;
System.out.println("time3: "+ Double.parseDouble(String.valueOf(time3))/1000);
int dataCount = (int)revitNewEntityService.getRevitAddInCount(vo, revitIds) ;//数据行数
int pageCount = dataCount % pageSize == 0 ? dataCount / pageSize
: dataCount / pageSize + 1;
long time4 = System.currentTimeMillis()-begin;
System.out.println("time4: "+ Double.parseDouble(String.valueOf(time4))/1000);
Paging<RevitNewEntity> page = new Paging<RevitNewEntity>(vo.getPageSize(),vo.getPageNo());
page.setCount(dataCount);
page.setDataList(result);
page.setPageCount(pageCount);
ModelAndView view = new ModelAndView();
..............
}

信息: Server startup in 8250 ms

time1: 11s
time2: 11s
time3: 11s
time4: 11s

说明time1 处理的这个函数 耗时 实现如下

@Override
public Long[] getChooseMoreThanOneTypeId(Long[] tagId) {
StringBuilder sqlBuilder = new StringBuilder("select r.* from revit_new_entity r ");
if (null != tagId) {
sqlBuilder.append("inner join revit_type_relation rt on r.id=rt.revitId ");
}
sqlBuilder.append("where 1=1   ");

if (null != tagId && tagId.length > 0) {
sqlBuilder.append(" and rt.typeId in (");
for (int t = 0; t < tagId.length; t++) {
if (t != tagId.length - 1) {
sqlBuilder.append(tagId[t] + ",");
}
if (t == tagId.length - 1) {
sqlBuilder.append(tagId[t] + ")  ");
}
}
}


if (null != tagId && tagId.length > 0) {
sqlBuilder.append(" group by rt.revitId HAVING count(rt.revitId)=" + tagId.length);
}
//sqlBuilder.append(" order by createTime desc");


//  猜测下面这个查询耗时 因为按照上面逻辑 如果Long[] tagId 为空，下面这个查询是一个查全表数据 得到list 仅仅是为了得到list每一个对象的主键 

//内存里面加载了太多对象
SQLQuery sqlQuery = getCurrentSession().createSQLQuery(sqlBuilder.toString()).addEntity(RevitNewEntity.class);


List<RevitNewEntity> list = sqlQuery.list();
Long[] revitIds = new Long[list.size()];
for(int i=0;i<list.size();i++){
revitIds[i] = list.get(i).getId();
}
return revitIds;
}

对应代码修改

{

StringBuilder sqlBuilder = new StringBuilder("select r.id from revit_new_entity r "); //这里只查 主键一个字段

............................ 中间不变

SQLQuery sqlQuery = getCurrentSession().createSQLQuery(sqlBuilder.toString());
List<Object> list = (ArrayList<Object>)sqlQuery.list();
List<Long> result = new ArrayList<Long>();

for (Object obj : list) {
//Object[] o = (Object[]) obj; 这个要注释 不然提示转换报错  因为l只查一个字段 ist每一个对象就不再是一个数组 而是一个值可以直接toString()
result.add(Long.parseLong(obj.toString()));
}

Long[] revitIds = new Long[]{};
if(result!=null){
revitIds = new Long[result.size()];
for(int i= 0;i< result.size();i++){
revitIds[i]= result.get(i);
}
}

return revitIds;

}

修改后查询时间

time1: 0.046
time2: 0.047
time3: 0.102
time4: 0.144

time1: 0.05
time2: 0.05
time3: 0.489
time4: 0.538

time1: 0.048
time2: 0.048
time3: 0.419
time4: 0.782

基本达到查询要求  单次查询需求