mysql最佳化---訂單查詢最佳化(2):非同步分頁處理

標籤：nts   code   關聯   private   asc   try   cal   except   slave   

訂單分頁查詢:

老的代碼是順序執行查詢資料和計算總記錄數,但是如果條件複雜的話(比如關聯子表)查詢的時間要超過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):非同步分頁處理