一个查询及优化

Question

现在有数据表[stock]
里面有字段[id] int identity(1,1),[userid] int,[riqi] datetime
id是pk
需求:按userid分组搜出按riqi倒序,id 倒序的一条记录,取出前20条记录按riqi倒序,id倒序排列
考虑到stock的数据量比较大,不知该怎么建立索引和查询我现在是建立两个索引
idx_stock2:userid,riqi desc,id desc
idx_stock3:riqi desc,id desc
查询是
--DBCC DROPCLEANBUFFERS
--DBCC FREEPROCCACHE
--set statistics io on
select top 20 * from stock s with(index(idx_stock3)) where id in (select top 1 id from stock with(index(idx_stock2)) where userid=s.userid order by riqi desc,id desc) order by riqi desc,id desc
现在只有200多万数据,扫描的次数很夸张,期待好的解决方案

Answer 1

select top 20 * from stocks s
where not exists (select 1 from stocks where userid=s.userid and riqi>s.riqi)
order by riqi desc,id desc

Answer 2

to 2#:
riqi可能重复,这个查询结果可能不正确.

Answer 3

select top 20 * from stocks s
    where not exists (select 1 from stocks where userid=s.userid and id>s.id)
        order by riqi desc,id desc

Answer 4

--> [id] int identity(1,1)，那max(id)应该是最新的吧？
select top 20 * from stock s with(index(idx_stock3)) where id = (select max(id) from stock with(index(idx_stock2)) where userid=s.userid) order by riqi desc,id desc

Answer 5

[code=SQL]-->那就可以转化为：
select top 20 * from stocks s
    where not exists (select 1 from stocks where userid=s.userid and id>s.id)
        order by riqi desc,id desc[/code]

Answer 6

in   -->  =

Answer 7

select top 20 * 
from stocks s
where not exists (select 1 from stocks where userid=s.userid and id<>s.id and riqi>s.riqi)
order by riqi desc,id desc--注意 id<>s.id

Answer 8

直接建三个索引 
userid,
riqi desc,
id desc 

Answer 9

Try:select top 20 * from stock s
where not exists (select 1 from stocks where userid=s.userid and (riqi>s.riqi or riqi=s.riqi and id>s.id)) 
order by riqi desc,id desc 

Answer 10

select * from stock t
inner join 
(
    select max(id) as id
    from
    (
        select a.*
        from stock as a
        inner join 
        (
            select top 20 userid,max(riqi) as riqi
            from stock 
            group by userid
            order by riqi desc,id desc
        ) as b on a.userid=b.userid and a.riqi=b.riqi
    ) as a group by userid,riqi
) as a on t.id=a.id

Answer 11

select * from stock t
inner join 
(
    select top 20 max(id) as id
    from
    (
        select a.*
        from stock as a
        inner join 
        (
            select userid,max(riqi) as riqi
            from stock 
            group by userid
            order by riqi desc,id desc
        ) as b on a.userid=b.userid and a.riqi=b.riqi
    ) as a group by userid,riqi
) as a on t.id=a.id

Answer 12

搂主的索引和语句应该是最优的了，试下去掉强制指定索引，看看效果select top 20 * from stock s where id in (select top 1 id from stock where userid=s.userid order by riqi desc,id desc) order by riqi desc,id desc 

Answer 13

确实减少了很多扫描次数,不过还是差强人意.换了个想法,对于stock数据量比较大,扫描它都可能造成巨大的消耗,而结果集是对userid生成的,记录数对于stock来说是很少的,我现在用游标把userid分组,查询对应的一条id放于临时表,然后再用stock的pk,id去查需要的记录集合,测试了感觉效果还可以,不知道还有没有更好的解决方案?期待
DBCC DROPCLEANBUFFERS 
DBCC FREEPROCCACHE 
set statistics io on 
declare @t table(userid int,id int)
declare @userid int,@id int
declare  c cursor for select userid from stock group by userid
open c
FETCH NEXT FROM c into @userid
WHILE @@FETCH_STATUS = 0
 begin
     insert into @t select top 1 userid,id from stock with(index(idx_stock2)) where userid=@userid order by riqi desc,id desc
   FETCH NEXT FROM c into @userid
 end
DEALLOCATE c
select * from stock where id in (select id from @t)

Answer 14

还有in  -->  = 
我觉得等效

Answer 15

不如再建一个表,来保存你要查询的结果,这样最快给stock加一个触发器,每次i u d时,同步更新另外一个表 

Answer 16

declare  c cursor for select userid from stock group by userid
---------------------------------------------------------------
从功能上讲你少了排序，如果加了排序性能应该不如下面：
select top 20 * from stock s where id = (select top 1 id from stock where userid=s.userid order by riqi desc,id desc) order by riqi desc,id desc 
下面的也据应该很快：
select top 20 * from stock s where id = (select top 1 id from stock where userid=s.userid order by riqi desc,id desc) 

Answer 17

idx_stock2:userid,riqi desc,id desc 
idx_stock3:riqi desc,id desc 
这个两个索引不好,他部删除
换成:
riqi desc,id desc,userid asc再试:select top 10 * from stock s 
where id in 
(select top 1 id from stock where userid=s.userid order by riqi desc,id desc) 
order by riqi desc,id desc 

Answer 18

先不用去,再加一个试试
create index idx_stock4 on stock(riqi desc,id desc,userid)去掉原sql里的with index没数据,不好测.

Answer 19

为了更能体现差别,我把查前20条改成查全部DBCC DROPCLEANBUFFERS 
DBCC FREEPROCCACHE 
set statistics io on 
set statistics time on
select * from stock s with(index(idx_stock3)) where id in (select top 1 id from stock with(index(idx_stock2)) where userid=s.userid order by riqi desc,id desc) order by riqi desc,id desc ---------------------------------DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。SQL Server 执行时间: 
   CPU 时间 = 0 毫秒，耗费时间 = 0 毫秒。（所影响的行数为 234 行）表 'stock'。扫描计数 2254903，逻辑读 15728850 次，物理读 274 次，预读 31196 次。SQL Server 执行时间: 
   CPU 时间 = 22109 毫秒，耗费时间 = 56347 毫秒。
总耗时约57s

Answer 20

DBCC DROPCLEANBUFFERS 
DBCC FREEPROCCACHE 
set statistics io on 
set statistics time on
declare @t table(userid int,id int)
declare @userid int
declare  c cursor for select userid from stock with(index(idx_stock2)) group by userid
open c
FETCH NEXT FROM c into @userid
WHILE @@FETCH_STATUS = 0
 begin
   insert into @t select top 1 userid,id from stock with(index(idx_stock2)) where userid=@userid order by riqi desc,id desc    
   FETCH NEXT FROM c into @userid
 end
DEALLOCATE c
select * from stock  where id in (select id from @t) order by riqi desc,id desc
结果行数太多,贴不下,抽取后面的（所影响的行数为 234 行）表 'stock'。扫描计数 234，逻辑读 704 次，物理读 211 次，预读 0 次。
表 '#48881BC0'。扫描计数 1，逻辑读 1 次，物理读 0 次，预读 0 次。SQL Server 执行时间: 
   CPU 时间 = 0 毫秒，耗费时间 = 835 毫秒。总耗时不到3s

Answer 21

加上这个
create index idx_stock4 on stock(riqi desc,id desc,userid) 把你的第一个查询 with(index都去掉再试试你第一种写法看来你的userid 不超过234个.

Answer 22

我从来不想当然,都是以执行计划和逻辑读来优化sql
但需要知道你的数据分布情况,不然靠猜,就不好说了..

Answer 23

加入索引,按照第一个查询DBCC DROPCLEANBUFFERS 
DBCC FREEPROCCACHE 
set statistics io on 
set statistics time on
select * from stock s where id in (select top 1 id from stock where userid=s.userid order by riqi desc,id desc) order by riqi desc,id desc 看到执行计划中还是用的idx_stock2
跟select * from stock s where id in (select top 1 id from stock with(index(idx_stock2)) where userid=s.userid order by riqi desc,id desc) order by riqi desc,id desc 结果一样表 'stock'。扫描计数 2254903，逻辑读 6794259 次，物理读 154 次，预读 27024 次。
SQL Server 执行时间: 
   CPU 时间 = 11344 毫秒，耗费时间 = 12783 毫秒。耗时约13s
开始指定用idx_stock3是不正确的!
然后指定idx_stock4来检索
select * from stock s where id in (select top 1 id from stock with(index(idx_stock4)) where userid=s.userid order by riqi desc,id desc) order by riqi desc,id desc
机器呈假死状态,等不到看结果,我把机器reset了.

Answer 24

除非同时指定了 TOP，否则 ORDER BY 子句在视图、内嵌函数、派生表和子查询中无效。
修改了一下DBCC DROPCLEANBUFFERS 
DBCC FREEPROCCACHE 
set statistics io on 
set statistics time on
select * from stock t
inner join 
(
    select max(id) as id
    from
    (
        select a.*
        from stock as a
        inner join 
        (
            select userid,max(riqi) as riqi
            from stock 
            group by userid
        ) as b on a.userid=b.userid and a.riqi=b.riqi
    ) as a group by userid,riqi
) as a on t.id=a.id结果（所影响的行数为 234 行）表 'stock'。扫描计数 469，逻辑读 9545 次，物理读 2 次，预读 6102 次。SQL Server 执行时间: 
   CPU 时间 = 1297 毫秒，耗费时间 = 2323 毫秒。这个是目前性能最好的了,看了下执行计划,也是用到了idx_stock2