关于好友动态显示查询优化

Question

本查询用于显示好友动态。 
好友动态表设计如下 用户具体动作表 
表名：userAction 
Id 自增ID Int 
UID 用户数字ID Int 
UserId 用户昵称 Nvarhcar(20) 
uaType 动态类型 
uaMes 动态文本 Text 
uaDate 动态时间戳 Datetime 哪些好友有动作的表 
表名：uaUsers 
id 
uaId 动态ID Int 需要索引 
uauUId 接收人数字ID Int 需要索引 
uauUserId 接收人昵称 Nvarhcar(20) 
uauDate 创建时间 Datetime 我查询好友动态的语句如下：（5个不重复的好友的动态）
select top 5 * from  userAction a
where a.id in (select top 1 b.id from userAction b,uaUsers c where b.UID=a.UID and c.uaId=b.id and c.uauUId=@UId order by b.uadate desc)  and DATEDIFF(dd,uaDate, getdate() )<= 2
order by a.uadate desc
已经建立存储过程，传入用户ID为参数以上语句经过测试并未发现问题。后来好友动态功能上线，这个语句几乎超时。我count两个数据库，记录数都上万。只要清楚了7天前的好友动态，但是速度还是不理想。使用查询分析器查看执行计划，如下图（为方便查看，图略做了修改）
我的索引部分几乎没用到。我的索引如下
求教大虾帮我分析下原因提供一些较好的解决办法。感激不尽。

Answer 1

sql会根据情况来选择要不要使用索引。如果你想让用的话，可以自己制定要使用的索引。

Answer 2

各位大虾，并不是想讨论索引，我是想问我的语句有什么优化的办法。上面的图被CSDN自动隐藏了，可以拉到新窗口中看大图。

Answer 3

最好给出完整的表结构,测试数据,计算方法和正确结果.发帖注意事项
http://topic.csdn.net/u/20091130/21/fb718680-98ff-4afb-98d8-cff2f8293ed5.html?24281

Answer 4

--先试试这个
SELECT TOP 5 * FROM userAction a
WHERE NOT EXISTS(
SELECT TOP 1 1 
FROM userAction b,uaUsers c 
WHERE b.UID=a.UID 
AND c.uaId=b.id 
AND c.uauUId=@UId 
AND b.uadate>a.uadate
)
AND DATEDIFF(dd,uaDate, getdate() ) <= 2 

Answer 5

--这个.
SELECT * INTO #tmpUser FROM uaUsers WHERE uauUId=@UId select top 5 * from  userAction a 
where a.id in (
select top 1 b.id 
from userAction b,#tmpUser c 
where b.UID=a.UID and c.uaId=b.id 
and c.uauUId=@UId 
order by b.uadate desc
)  and DATEDIFF(dd,uaDate, getdate() ) <= 2 
order by a.uadate desc DROP TABLE #tmpUser

Answer 6

--这个
select top 5 * from  userAction a 
where a.id in (select top 1 b.id from userAction b,uaUsers c where b.UID=a.UID and c.uaId=b.id and c.uauUId=@UId order by b.uadate desc)  and DATEDIFF(dd,uaDate, getdate() ) <= 2 
order by a.uadate desc 
OPTION(HASH JOIN)
不加索引的情况下，你一个个的试吧，若效果都不理想，那就考虑加索引了。

Answer 7

用到#tmpUser的那个也要10几秒? 

Answer 8

信息有限，问题较多，说几点：
1.
实现这样的需求，目前的表设计个人感觉不到位。
很多应用似乎都有类似的需求，应该有更好的设计，勤思考吧。2.
查询计划表明主要的消耗点在table scan 和 sort，那就对症下药吧。试试下面的方法：
首先在userAction 表上建一个索引：CREATE INDEX idx_test ON userAction(uauDate,UID,id)将查询改写成：select top 5 * from  userAction a 
where a.id = (select top 1 b.id from userAction b,uaUsers c 
where b.UID=a.UID and c.uaId=b.id and c.uauUId=@UId order by b.uadate desc)  
and uaDate >= DATEADD(d,DATEDIFF(d,0,GETDATE()-2),0)
order by a.uadate desc 其中2点说明：
1)改写uaDate条件让其符合SARG
2)既然已经是 TOP 1 了就直接写等号吧(写IN看起来有点怪)3.
#6楼查询效率高的原因是主查询没了order by子句，缺少order by子句
查询的语义也就变了。同理LZ写的查询去掉order by子句也会快不少的。
此时实际的数据分布会对查询速度有较大影响，简单说就是有的用户会
比较快有的用户会比较慢。6楼的改写还依赖一个事实：userAction表的
(UID,uaDate)组合必须是unique的，否则结果就不对了，即使加上order BY
子句。换句话说，如果userAction表能满足这样一种假设：
因为Id是自增长的，对于同一个UID/UserId,MAX(Id)是否可以作为该用户的
最新动态呢？如果可以，那么下面的查询也许对于实现这个需求有点帮助。SELECT UID,MAX(id) AS id
FROM userAction
WHERE uaDate >= DATEADD(d,DATEDIFF(d,0,GETDATE()-2),0)
GROUP BY UID
4.
userAction表uaMes字段一定要用text类型嘛？
根据目前的定义(uaType数据类型未知)估计，uaMes定义成nvarchar(3500)应该是没有问题的。
如果一定要用text类型，并且字段数据长度较小的行又占有一定的比例的话，考虑
开启表的 text in row 选项，对查询会有一点帮助的。5.升级到SQL Server 2005/2008 T-SQL增强会带来更好的实现方式。

Answer 9

nono..#6 已经不需要 order by b.uadate desc 了，因为取的是最大的uadate

Answer 10

明白了，原来是说外层的查询啊.~问一下，如果内层已经按uaDate排序过，外层有没有可能也已经排序好?计划中是Nested loop ,应该不会改变数据的排序吧?

Answer 11

SELECT TOP 5 * FROM userAction a
WHERE NOT EXISTS(
    SELECT TOP 1 1 
    FROM userAction b,uaUsers c 
    WHERE    b.UID=a.UID 
            AND c.uaId=b.id 
            AND c.uauUId=@UId 
            AND b.uadate>a.uadate
            )
    AND DATEDIFF(dd,uaDate, getdate() ) <= 2 ORDER BY uadate DESC

Answer 12

加了order by 和lz的查询效率基本一致。还是无法避免 table scan 和 sort

Answer 13

#6的不加 order by 一样会table scan和sort.打赌吧，加了order by 後我说速度跟#6的会差不多。

Answer 14

1.6楼的查询，table scan 是肯定有的，但是完成查询并不一定要需要扫描完整张表。
sort是肯定没有了，不然不会快的。
2.加了order by 后就必须要扫描完整张表，而且要必须要排序。

Answer 15

1.#6的一定会scan完整张表,然後接着一个sort(按uadate)，而不是楼主原来的2个sort, 而且会少了table spool等緩衝.2.加上order by 後，可能会出现2个sort，也可能只有1个sort,但肯定没有spool等操作.

Answer 16

现在要解决的是 67%的table scan 和 15%的sort的问题，其他的运算暂且忽略，先抓重点，一步一步来。

Answer 17

下面有个方案lz可以试一下效果。
表：userAction : ID 设为 聚集index，在（uaDate,UID）上建个非聚集索引
表：uaUsers  ： 在（uauUId,uaId)上建非聚集index  select  top 5  c.*  
  from uausers  a
  inner join (
       select max(id) id 
       from useraction 
       where uadate >= dateadd(d,datediff(d,0,getdate()-2),0)
       group by uid
    ) b on  a.uaid = b.id   
  inner join useraction c on b.id=c.id
  where a.uauuid=@uid 
  order by a.uaid desc 上面的查询依赖userAction的ID自增，且对于每个用户他的max(id)表示他的最近一次动作。

Answer 18

如果改变原表的索引，当然可以有其它更优的写法。但是我认为原来的执行计划中，成本最高的非table scan + sort(尽管占到67+15%的成本)，
因为table scan的结果很有可能已被cache到bp中，剩下是要针对每一笔已资料做对应的搜索动作，(即Nested Loop 里层的每一个操作，这里的3%的sort 跟table spool等操作才是最消耗资源的)。

Answer 19

不清楚啊，加了HASH JOIN 不知道执行计划变成了啥样~~都在猜。

Answer 20

1.table scan运算符里包含了 DATEDIFF(dd,uaDate, getdate() ) <= 2 的处理，假设满足条件的有N条记录。
2.15%的sort运算符的输出行数一定是<=N，原因是top运算符的存在，当然最差情况是一条不落的输出全部的N行记录。N有多大呢？:-)

Answer 21

如果sort输出的前5条记录又正好某一个人的，那么他走运了！哇，速度真快啊，功能不错。
而倒霉的人要等到第N条记录被输出后，才能看到结果。

Answer 22

TOP 运算符在最顶层，所以table scan的结果 (不知道DATEDIFF(dd,uaDate,Getdate())<=2会不会下推到table scan中)会一条不落的参與循環，这个N嘛，lz说了算，哈哈。

Answer 23

第一个NL运算符的逻辑运算方式应该是：Left Semi Join/Left Anti Semi Join(图片里只看到了Le两个字母)，
而判断的依据是看到了Row Count Spool运算符。所以，SQL Server已经将lz的查询优化成 exists/not exists 了。

Answer 24

如果DATEDIFF(dd,uaDate,Getdate()) <=2不是在table scan里完成的，微软可以关门了 :-）

Answer 25

我同意第1个NL运算符是 Left Semi Join/Left Anti Semi Join, 但我反对"SQL Server已经将lz的查询优化成 exists/not exists "的说法。用Left Semi Join/Left Anti Semi Join运算的原因是lz的语句中用了 IN , 所以要处理 NULL值的情况。
如果lz把 IN 改成 = ，应该会变成 NL/Inner　了。

Answer 26

没说清楚：
查询不复杂,逻辑等效的前提。 exists/ not exists  的计划和 in/not in 通常是一样的。

Answer 27

收回这句话：所以，SQL Server已经将lz的查询优化成 exists/not exists 了。这样说法本身就是有问题的，谢谢Garnett_KG提醒。

Answer 28

看提供计划基本可以断定DATEDIFF(dd,uaDate,Getdate()) <=2 是在table scan里完成的。
这个就未必了,正如30，31楼所说。
那个幸运的家伙最先输出的5条记录正好都是他的，查询就此over了。第一个嵌套循环
连接的内部处理过程只要被处理5次。没必要在执行剩下的N-5次了。这就是top
运算符的作用: 满足输出的行数后，查询即完成。 

Answer 29

你再回过头去再lz的执行计划，15% sort之後就是NL, 最上面才是TOP运算，NL里层的TOP 是子查询里面的TOP.

Answer 30

sheepcyk 的说法没问题，Garnett_KG你对整个查询计划的数据流控制的理解有一点偏差。

Answer 31

只有经过顶层的TOP才输出是5行啊换句话说，TOP 一要等所有的数据按uaDate排序完後才能决定是哪5行返回啊。

Answer 32

你说的没问题，sort是阻塞运算符，但这里说的是因为Top的存在，不是NL的外部输出的每一行都要到内部表里做查找的。

Answer 33

Garnett_KG：
对应这个查询，第一个NL的内部处理过程执行的次数是由sort运算符决定的。
假设table scan 中满足DATEDIFF(dd,uaDate,Getdate()) <=2条件的记录有N条。
所以sort的最小输出行数是5，最大输出行数是N，那么相应的NL的内部被执行
的次数也就是最少5次，最多N次。下面的查询，我瞎诌的，但愿能说明问题 ;-)USE Northwind
GO
SELECT TOP 10 * FROM dbo.[Order Details] AS a
WHERE 
a.OrderID IN (SELECT TOP 1 OrderID FROM dbo.Orders WHERE a.OrderID = ABS(OrderID - 1000000) 
ORDER BY ShipVia)
ORDER BY Discount DESCSELECT TOP 10 * FROM dbo.[Order Details] AS a
WHERE 
a.OrderID IN (SELECT TOP 1 OrderID FROM dbo.Orders WHERE a.OrderID = ABS(OrderID) 
ORDER BY ShipVia)
ORDER BY Discount DESC
两个不同的查询，用的相同的计划。Order Details聚集索引扫描输出的都是
2155行，第一个查询的sort也输出了2155行，因此NL的内部被执行了2155次。
而第2个查询sort输出了10行，那么NL的内部只被执行了10次。

Answer 34

我的文档大致整理好了。现在我慢慢的发上来！首先我们对4个语句进行测试
语句1：select top 1 b.id from userAction b,uaUsers c where c.uaId=b.id and c.uauUId=162235 order by b.uadate desc语句2：select max(b.id) from userAction b,uaUsers c where c.uaId=b.id and c.uauUId=162235语句3：select top 5 * from  userAction a 
where a.id =(select top 1 b.id from userAction b,uaUsers c where b.UID=a.UID and  c.uaId=b.id and c.uauUId=162235 order by b.uadate desc) and uadate >= dateadd(d,datediff(d,0,getdate()-2),0)
order by a.uadate desc 语句4：select top 5 * from  userAction a 
where a.id = (select max(b.id) from userAction b,uaUsers c where   b.UID=a.UID and c.uaId=b.id and c.uauUId=162235 ) and uadate >= dateadd(d,datediff(d,0,getdate()-2),0)
order by a.uadate desc 

Answer 35

由于之前的数据库数据量并不大，仅2万条内，因此我通过 insrt 插入自身数据（除ID）， select  top 5  c.*  
  from uausers  a
  inner join (
       select max(id) id 
       from useraction 
       where  uadate >= dateadd(d,datediff(d,0,getdate()-2),0)
       group by uid
    ) b on  a.uaid = b.id   
  inner join useraction c on b.id=c.id
  where a.uauuid=162235 
  order by a.uaid desc 此句未找到数据
SELECT TOP 5 * FROM userAction a
WHERE NOT EXISTS(
    SELECT TOP 1 1 
    FROM userAction b,uaUsers c 
    WHERE    b.UID=a.UID 
            AND c.uaId=b.id 
            AND c.uauUId=162235  
            AND b.uadate>a.uadate
            )
    AND DATEDIFF(dd,uaDate, getdate() ) <= 2 ORDER BY uadate DESC
此句找到重复数据，可能和我插入的数据重复有关（日期）