首页 > 代码库 > mysql优化---订单查询优化(2):异步分页处理
mysql优化---订单查询优化(2):异步分页处理
订单分页查询:
老的代码是顺序执行查询数据和计算总记录数,但是如果条件复杂的话(比如关联子表)查询的时间要超过20s种
public static PagedList<Map<String, Object>> query(ITemplateService service, Identity tenantId, Identity userId, String entityName, Map<String, Object> params, String columns, TCondition cond) { int page = WebHelper.getPageNo(params); int pageSize = WebHelper.getPageSize(params); String orderColumn = (String) params.get(JqgridConstant.ORDER_COLUMN); // 排序字段 String orderSord = (String) params.get(JqgridConstant.ORDER_METHOD); // 排序方式，desc or asc handleOrderByColumn(cond, orderColumn, orderSord); int totalCount = service.getBaseService().query4Count(tenantId, entityName, cond); List<Map<String, Object>> list = service.query(columns, entityName, cond, tenantId, userId, pageSize, page); Translator.prepare(list, tenantId, service); // TODO return new PagedList<>(page, pageSize, totalCount, list); }
优化方法:
1.通过新启动一个线程来同时做之前需要顺序执行的两个Sql查询,最后等待全部计算完成,统一进行返回
2.对于一些特别复杂的条件的查询,如果内容的条数少于PageSize,那么计算总条数的sql就是不需要执行,可以用返回的list的szie当做总记录数
public static PagedList<Map<String, Object>> queryAsyn(ITemplateService service, Identity tenantId, Identity userId, String entityName, Map<String, Object> params, String columns, TCondition cond) { int page = WebHelper.getPageNo(params); int pageSize = WebHelper.getPageSize(params); String orderColumn = (String) params.get(JqgridConstant.ORDER_COLUMN); // 排序字段 String orderSord = (String) params.get(JqgridConstant.ORDER_METHOD); // 排序方式，desc or asc ExecutorService slaver = Executors.newSingleThreadExecutor(); FutureTask<Integer> totalCountFuture = new FutureTask<>(new TotalCountJob(service, tenantId, entityName, cond)); slaver.execute(totalCountFuture); handleOrderByColumn(cond, orderColumn, orderSord); slaver.shutdown(); //主线程来取数据 long time1 = System.nanoTime(); List<Map<String, Object>> list = service.query(columns, entityName, cond, tenantId, userId, pageSize, page); long time2 = System.nanoTime(); long diff = time2 - time1; logger.debug("查询方案统计-----查询分页list部分,用时:{}s,条件:{}", translateToSecond(diff), cond); Integer totalCount = null; int listSize = list.size(); if (listSize < pageSize) { logger.info("本次查询不需要sql进行count操作"); totalCount = listSize + (page - 1) * pageSize; slaver.shutdownNow(); } else { try { //没做完就等着 totalCount = totalCountFuture.get(); } catch (Exception e) { totalCountFuture.cancel(true); logger.error("totalCount发生异常", e); } } Translator.prepare(list, tenantId, service); return new PagedList<>(page, pageSize, totalCount, list); } private static double translateToSecond(long diff) { return diff * 0.000000001; } static class TotalCountJob implements Callable<Integer> { private String tableName; private TCondition condition; private Identity tenantId; private ITemplateService service; public TotalCountJob(ITemplateService service, Identity tenantId, String tableName, TCondition condition) { this.service = service; this.tableName = tableName; this.condition = condition; this.tenantId = tenantId; } @Override public Integer call() throws Exception { long time1 = System.nanoTime(); Integer totalCount = service.getBaseService().query4Count(tenantId, tableName, condition); long time2 = System.nanoTime(); long diff = time2 - time1; logger.debug("查询方案统计-----查询分页count部分,用时:{}s,条件:{}", translateToSecond(diff), condition); return totalCount; } }
这是第一次优化的文章,欢迎访问:
http://www.cnblogs.com/victor2302/p/6073821.html
mysql优化---订单查询优化(2):异步分页处理
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。