表情况:
USER 数据量 280w
Game_SCORE 数据量 1200w索引
User: UserID, CityGame_SCORE : USERID,TYPEID
select * from (select ROW_NUMBER() OVER(ORDER BY orderscore ) AS rownum,A.USERID,A.Nick,A.city,B.score,B.orderscore,B.updatedate from USER A, Game_Score B WHERE A.USERID=B.USERID AND B.USERID <>0 and orderscore > 0 and TypeID=6 AND city='岳阳') D where 1=1 and rownum BETWEEN 1 AND 20 order by orderscore:超时时间已到。在操作完成之前超时时间已过或服务器未响应。查询超过50秒有什么办法可以让速度更快吗?
USER 数据量 280w
Game_SCORE 数据量 1200w索引
User: UserID, CityGame_SCORE : USERID,TYPEID
select * from (select ROW_NUMBER() OVER(ORDER BY orderscore ) AS rownum,A.USERID,A.Nick,A.city,B.score,B.orderscore,B.updatedate from USER A, Game_Score B WHERE A.USERID=B.USERID AND B.USERID <>0 and orderscore > 0 and TypeID=6 AND city='岳阳') D where 1=1 and rownum BETWEEN 1 AND 20 order by orderscore:超时时间已到。在操作完成之前超时时间已过或服务器未响应。查询超过50秒有什么办法可以让速度更快吗?
orderscore
User: UserID, City Game_SCORE : USERID,TYPEID
加了索引
--try
;with t as
(
select ROW_NUMBER() OVER(ORDER BY orderscore ) AS rownum,A.USERID,A.Nick,A.city,B.score,B.orderscore,B.updatedate from USER A, Game_Score B WHERE A.USERID=B.USERID AND B.USERID <>0 and orderscore > 0 and TypeID=6 AND city='岳阳'
)
select * from twhere 1=1 and rownum BETWEEN 1 AND 20 order by orderscore
select top 20
A.USERID,
A.Nick,
A.city,
B.score,
B.orderscore,
B.updatedate
from [USER] A, Game_Score B
WHERE A.USERID=B.USERID AND B.USERID <>0
and orderscore > 0 and TypeID=6 AND city='岳阳'
order by orderscore
select userID,orderscore,typeID Into #temp from Game_Score
where orderscore > 0 and TypeID=6 AND city='岳阳'
Create cluster index ix_UserID on #temp(userID,orderscore,) include(typeID)select top 20
A.USERID,
A.Nick,
A.city,
B.score,
B.orderscore,
B.updatedate
from [USER] A, #temp B
WHERE A.USERID=B.USERID AND city='岳阳'
order by orderscore
User: City + UserID
Game_SCORE : TYPEID+USERID
注意:City 和TYPEID必须放在前面。这个,放在前面和后面作用其实是大不一样的。如果建成聚集索引效果会更好。
因为你的整个条件中,限制结果集的2个主要条件TypeID=6 AND city='岳阳'都放在了后面,导致无法有效利用到。这样建索引可以让SQL在第一步就将结果集锁定在一个很小的范围。
另外,你现有的索引仍然要保留。
order by orderscoreselect top 20
A.USERID,
A.Nick,
A.city,
B.score,
B.orderscore,
B.updatedate
from [USER] A, Game_Score B
WHERE B.USERID <>0 and orderscore > 0 and TypeID=6 AND city='岳阳'
and A.USERID=B.USERID