数据库查询优化

select pr.cid, case when pr.type = 2 then ac.auto_cancel else ca.auto_cancel end auto_cancel, pr.proxy_id proxy_id from crm_scm_cust_proxy_rel pr LEFT JOIN crm_company_config_air ca on pr.cid = ca.cid and ca.auto_cancel > 0 LEFT JOIN pf_air_config ac on ac.face_cid = pr.cid and ac.auto_cancel > 0 where pr.status = 1 and (( pr.type = 1 and ca.auto_cancel > 0 ) or (pr.type = 2 and ac.auto_cancel > 0 ));

改进后：(execution: 156 ms, fetching: 41 ms)

 EXPLAIN
  select  
    pr.cid,
      case   when  pr.type  =   2   then  ac.auto_cancel  else  ca.auto_cancel  end   auto_cancel,
    pr.proxy_id proxy_id
  from  
    crm_scm_cust_proxy_rel pr   LEFT   JOIN  
    (  select  ca. *   from  crm_company_config_air ca    where  ca.auto_cancel  >  0 )ca   on  pr.cid  =  ca.cid   LEFT   JOIN  
    (  select  ac. *   from  pf_air_config   ac  where    ac.auto_cancel  >  0 ) ac  on  ac.face_cid  =   pr.cid
  where  
        pr.status   =   1   and  
    (( pr.type   =   1   and  ca.auto_cancel  >  0 )  or  (pr.type  =   2   and  ac.auto_cancel  >  0 ));

第二种思路：

 explain
  select   pr.cid,
         case   when  pr.type  =   2   then  ac.auto_cancel  else  ca.auto_cancel  end   auto_cancel,
       pr.proxy_id                                                       proxy_id
  from   crm_scm_cust_proxy_rel pr
           LEFT   JOIN  
     crm_company_config_air ca   on  pr.cid  =  ca.cid  and  ca.auto_cancel  >   0 
          LEFT   JOIN  
     pf_air_config ac   on  ac.face_cid  =  pr.cid  and  ac.auto_cancel  >   0 
 where  pr.status  =   1 
   and  ((pr.type  =   1   and  ca.auto_cancel  >   0  ))
  union   all 
 select   pr.cid,
         case   when  pr.type  =   2   then  ac.auto_cancel  else  ca.auto_cancel  end   auto_cancel,
       pr.proxy_id                                                       proxy_id
  from   crm_scm_cust_proxy_rel pr
           LEFT   JOIN  
     crm_company_config_air ca   on  pr.cid  =  ca.cid  and  ca.auto_cancel  >   0 
          LEFT   JOIN  
     pf_air_config ac   on  ac.face_cid  =  pr.cid  and  ac.auto_cancel  >   0 
 where  pr.status  =   1 
   and  (pr.type  =   2   and  ac.auto_cancel  >   0 );

 

数据库查询优化

标签：cut   lse   pre   语句   HERE   思路   ase   cancel   explain   

查看更多关于数据库查询优化的详细内容...