MySQL Optimization---order query optimization (2): Asynchronous Paging processing

Order Paged Query:

The old code executes the query data sequentially and calculates the total number of records, but if the conditions are complex (such as the associated child table), the time to query is more than 20s

     Public StaticPagedlist<map<string, object>>Query (itemplateservice service, Identity tenantId, Identity userId, String EntityName, Map<string, object>params, String columns, tcondition cond) {        intpage = Webhelper.getpageno (params); intPageSize = Webhelper.getpagesize (params); String Ordercolumn= (String)params.Get(Jqgridconstant.order_column);//Sort FieldsString Ordersord = (string)params.Get(Jqgridconstant.order_method);//sort by, desc or ASCHandleorderbycolumn (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 NewPagedlist<>(page, pageSize, TotalCount, list); }

Optimization method:

1. Start a thread at the same time to do two SQL queries that need to be executed sequentially, and then wait for all the computations to complete and return uniformly

2. For some particularly complex conditions of the query, if the content of the number of bars less than pagesize, then calculate the total number of SQL is not required to execute, you can use the returned list of Szie as the total number of records

 Public StaticPagedlist<map<string, object>>Queryasyn (itemplateservice service, Identity tenantId, Identity userId, String EntityName, Map<string, object>params, String columns, tcondition cond) {        intpage = Webhelper.getpageno (params); intPageSize = Webhelper.getpagesize (params); String Ordercolumn= (String)params.Get(Jqgridconstant.order_column);//Sort FieldsString Ordersord = (string)params.Get(Jqgridconstant.order_method);//sort by, desc or ASCExecutorservice slaver =Executors.newsinglethreadexecutor (); Futuretask<Integer> totalcountfuture =NewFuturetask<> (Newtotalcountjob (service, TENANTID, EntityName, cond));        Slaver.execute (totalcountfuture);        Handleorderbycolumn (Cond, Ordercolumn, Ordersord);        Slaver.shutdown (); //main thread fetching data        LongTime1 =System.nanotime (); List <map<string, object>> list = service.query (columns, entityname, cond, TenantId, UserId, PageSize, page); LongTime2 =System.nanotime (); Longdiff = time2-time1; Logger.debug ("Query Scenario Statistics-----Query Page list, spents: {}s, condition: {}", Translatetosecond (diff), cond); Integer TotalCount=NULL; intListsize =list.size (); if(Listsize <pageSize) {Logger.info ("This query does not require SQL for count operations"); TotalCount= listsize + (page-1) *pageSize;        Slaver.shutdownnow (); } Else {            Try {                //wait until you're done.TotalCount = Totalcountfuture.Get(); } Catch(Exception e) {totalcountfuture.cancel (true); Logger.error ("TotalCount An exception occurred", E);        }} translator.prepare (list, tenantId, service); return NewPagedlist<>(page, pageSize, TotalCount, list); }    Private Static DoubleTranslatetosecond (Longdiff) {        returndiff *0.000000001; }    Static classTotalcountjob Implements Callable<integer> {        PrivateString TableName; Privatetcondition condition; PrivateIdentity tenantId; PrivateItemplateservice Service;  Publictotalcountjob (itemplateservice service, Identity tenantId, String tableName, tcondition condition) { This. Service =Service;  This. TableName =TableName;  This. Condition =condition;  This. TenantId =tenantId; } @Override PublicInteger Call () throws Exception {LongTime1 =System.nanotime (); Integer totalcount = Service.getbaseservice (). Query4count (TenantId, tableName, condition); LongTime2 =System.nanotime (); Longdiff = time2-time1; Logger.debug ("Query Scenario Statistics-----query pagination Count section, spents: {}s, condition: {}", Translatetosecond (diff), condition); returnTotalCount; }    }

This is the first optimized article, Welcome to visit:

Http://www.cnblogs.com/victor2302/p/6073821.html

　　

MySQL Optimization---order query optimization (2): Asynchronous Paging processing