测试积点老人 发表于 2019-12-17 13:20:52

sql查询性能低下

sql查询性能低下
With ZBS AS(
      selectzb.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
      from ZbsUserInfo as zb   
      whereZUser_AddTime between '2015/10/14 0:00:00'
      and '2015/10/15 23:59:59'
      and zb.ZUser_StayTotalSecs>=-1
      and zb.Sp_Id in ( 1,2,3,4,5,6,7,8,9,-1)
      and zb.ZUser_Int1 >0
    )
    select * from
    (
      select ROW_NUMBER() OVER (ORDER BYAddTime   desc ) as ZS,Count(*) over() as Total,*
      from ZBS
      where ID in (select min(ID)
      fromZBS
      group by UserKeyhaving count(UserKey )>=1 )
    )
    as T where ZS between 1 and 30

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

政是在下 发表于 2019-12-17 15:30:17

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

qqq911 发表于 2019-12-18 11:01:57

加上索引

jingzizx 发表于 2019-12-18 12:20:17

索引是最起码的

litingting0214 发表于 2019-12-18 13:01:48

优化性能吧

bellas 发表于 2019-12-18 13:41:41

加索引
页: [1]
查看完整版本: sql查询性能低下