一条SQL语句的优化

最近发现系统中当数据量达到百万级别的时候一些SQL查询的速度慢的让人无法忍受，这个时候就要对相应SQL进行相应的调优。

 

1、如果是单表查询一般很简单，合理的优化SQL语句及建立索引或者联合索引基本上能够搞定

 

2、对于多表联合查询

 

     这里有一个视图VB_PAR_PARCELINFO，它由PAR_PARCELINFO ，PAR_PARCELADDRESS ，PAR_PARCELCONNECTION共用主键的三张表建立视图而来。

 

select * from ( 
select rownumber() over(order by t.CREATE_TIME desc) as rownumber_,
id,exp_Waybill_Num,cosign_Flag,take_Task_Num,send_Task_Num,goods_Payment_Method,rec_Countyname,rec_Address,
take_Member,send_Member,tracking_Status,arrive_Net_Time,arrive_Send_Time,take_task_time,send_task_time,SEND_FLAG,
APPOINT_STATUS 
from SCM_WULIU.VB_PAR_PARCELINFO t
where   CREATE_TIME >= '2000-01-01 00:00:00' and CREATE_TIME <= '2100-12-31 23:59:59' 
AND t.EXP_WAYBILL_NUM!='@'
and LOCATION =1922201 order by create_Time desc)
as temp_ where rownumber_ >0
fetch first 20 rows only 	

 

 

如果直接通过视图在百万级别的视图中检索，达到30多秒

 

注意：分页查询使用 fetch first 20 rows only 比 between and的效率高

 

 

通过视图查询效率较低，我直接通过关联查询来试一下

 

 

select * from ( 
select rownumber() over(order by c.CREATE_TIME desc) as rownumber_,
A.id,exp_Waybill_Num,a.goods_Payment_Method,
c.cosign_Flag,c.take_Task_Num,c.send_Task_Num,
c.take_Member,c.send_Member,c.tracking_Status,c.arrive_Net_Time,c.arrive_Send_Time,
c.take_task_time,c.send_task_time,c.SEND_FLAG,c.APPOINT_STATUS ,
b.rec_Countyname,b.rec_Address
from SCM_WULIU.PAR_PARCELCONNECTION c
left join SCM_WULIU.PAR_PARCELINFO a on a.ID=c.iD
left join SCM_WULIU.PAR_PARCELADDRESS b on a.ID=b.ID
where c.create_Time >= '2000-01-01 00:00:00' and c.create_Time <= '2100-12-31 23:59:59' 
and c.LOCATION =1922201 and c.APPOINT_STATUS is not null 
order by c.CREATE_TIME desc)
as temp_ where rownumber_ >0
fetch first 20 rows only 	

 

 

此时查询时间略有提高，但是还是很慢

 

。。。。。。。。。。

 

 

最后的SQL语句如下

 

 

select 
id,exp_Waybill_Num,cosign_Flag,take_Task_Num,send_Task_Num,goods_Payment_Method,rec_Countyname,rec_Address,
take_Member,send_Member,tracking_Status,arrive_Net_Time,arrive_Send_Time,take_task_time,send_task_time,SEND_FLAG,
APPOINT_STATUS 
from SCM_WULIU.VB_PAR_PARCELINFO r where r.ID in 
  (
  select ID from ( 
select rownumber() over(order by c.CREATE_TIME desc) as rownumber_, 
c.ID
from SCM_WULIU.PAR_PARCELCONNECTION c
left join SCM_WULIU.PAR_PARCELINFO a on a.ID=c.iD
left join SCM_WULIU.PAR_PARCELADDRESS b on a.ID=b.ID
where c.create_Time >= '2000-01-01 00:00:00' and c.create_Time <= '2100-12-31 23:59:59' 
 and c.LOCATION =1922201 and c.APPOINT_STATUS is not null 
  order by c.CREATE_TIME desc)
as temp_ where rownumber_ >0
fetch first 20 rows only 
) 

 

 

 

 

此时的分页查询由刚才开始的30多秒提高到650ms