sql查询性能低下

测试积点老人

sql查询性能低下

1. With ZBS AS(
   
2.         select  zb.ZUser_RefererUrl as RefererUrl,zb.ZUser_Id as ID, zb.ZUser_Key as UserKey,zb.ZUser_SousuoKeyword as SousuoKeyword,zb.ZUser_BMobile as BMobile,zb.ZUser_Mobile as Mobile, zb.ZUser_Email as Email,zb.ZUser_Int1 as QQ,zb.ZUser_Str1 as UserPUK,zb.ZUser_Nickname as Nickname,zb.ZUser_StayTotalSecs as StayTotalSecs,ZUser_AddTime as AddTime
   
3.         from ZbsUserInfo as zb     
   
4.         where  ZUser_AddTime between '2015/10/14 0:00:00'
   
5.         and '2015/10/15 23:59:59'
   
6.         and zb.ZUser_StayTotalSecs>=-1  
   
7.         and zb.Sp_Id in ( 1,2,3,4,5,6,7,8,9,-1)
   
8.         and zb.ZUser_Int1 >0  
   
9.     )
   
10.     select * from
    
11.     (
    
12.         select ROW_NUMBER() OVER (ORDER BY  AddTime   desc ) as ZS,Count(*) over() as Total,*
    
13.         from ZBS
    
14.         where ID in (select min(ID)
    
15.         from  ZBS  
    
16.         group by UserKey  having count(UserKey )>=1 )  
    
17.     )
    
18.     as T where ZS between 1 and 30

复制代码

ZbsUserInfo 这个表的数据量有几十万..然后放在数据库查询的时候需要几十秒.
.单表查询性能都这么地下.欲哭无泪..导致web服务器都奔溃.搜了sql性能化相关资料发现大多都是尽量使用索引查询不要使sql语句导致全表查询.本人才疏学浅.特此来寻求优化解决方法.或者其他解决方法.

bellas

加索引

litingting0214

优化性能吧

jingzizx

索引是最起码的

qqq911

加上索引

政是在下

建议你把建表语句也贴出来