请帮忙看看如何优化sql

Question

第一个图，hash match有个叹号，鼠标移过去看看，另外把右下角20%的那个Index scan的具体信息也贴出来

Answer 1

select c.*  from 
Comment c (NOLOCK)
WHERE EXISTS
(select userId from 
(select top 10 u.id as userId
from [User] u (NOLOCK), Portfolio po (NOLOCK), Position ps (NOLOCK), trade t (NOLOCK)
where U.ID = po.UserId and po.id = ps.portfolio_Id and t.positionId = ps.id 
group by u.id, u.thDisplayName
order by tradeCount desc) abc 
WHERE abc.userId=c.userid) 
and isSysGenerated = 0
and c.visibilityTypeId <> 4 union ALL --此处用union all速度更快，将不用排除重复的记录select * from comment t (NOLOCK)
where EXISTS(SELECT 1 FROM 
(select top 10 inReplytoEventId as score 
from Comment 
where inReplyToEventId is not NULL --以后表设计时尽量不要让此字段为null这样查将会行扫瞄
group by inReplytoEventId, inReplyToDiscussionTypeId 
order by score) t1 ON T.inReplytoEventId=t1.inReplytoEventId
and t.visibilityTypeId <> 4union ALL --此处用union all速度更快，将不用排除重复的记录select * from comment (NOLOCK)
where inReplyToDiscussiontypeId < 5   
order by perf_commentCount desc, voteCount desc, id DESC

Answer 2

select c.*  from 
Comment c (NOLOCK)
WHERE EXISTS
(select userId from 
(select top 10 u.id as userId
from [User] u (NOLOCK), Portfolio po (NOLOCK), Position ps (NOLOCK), trade t (NOLOCK)
where U.ID = po.UserId and po.id = ps.portfolio_Id and t.positionId = ps.id 
group by u.id, u.thDisplayName
order by tradeCount desc) abc 
WHERE abc.userId=c.userid) 
and isSysGenerated = 0
and c.visibilityTypeId <> 4 union ALL --此处用union all速度更快，将不用排除重复的记录select * from comment t (NOLOCK)
where EXISTS(SELECT 1 FROM 
(select top 10 inReplytoEventId as score 
from Comment 
where inReplyToEventId is not NULL --以后表设计时尽量不要让此字段为null这样查将会行扫瞄
group by inReplytoEventId, inReplyToDiscussionTypeId 
order by score) t1 ON T.inReplytoEventId=t1.inReplytoEventId)
and t.visibilityTypeId <> 4union ALL --此处用union all速度更快，将不用排除重复的记录select * from comment (NOLOCK)
where inReplyToDiscussiontypeId < 5   
order by perf_commentCount desc, voteCount desc, id DESC

Answer 3

RE: rockyljt
谢谢~ 但是使用union all，将有重复记录，不符合业务需要。 我修改成Exists+union 还是同样时间。另外，和Nolock 无关，此处无blocking

Answer 4

SELECT TOP 10
                                                u.id AS userId ,
                                                u.thDisplayName ,
                                                COUNT(t.id) AS tradeCount
                                      FROM      [User] u ,
                                                Portfolio po ,
                                                Position ps ,
                                                trade t
                                      WHERE     U.ID = po.UserId
                                                AND po.id = ps.portfolio_Id
                                                AND t.positionId = ps.id
                                      GROUP BY  u.id ,
                                                u.thDisplayName
                                      ORDER BY  tradeCount DESC
                                    ) abc这一部分，除了ID之外，其他列你没用到，可以不用不？如果可以，那么去掉。
另外，union会引入distinct/sort操作，过早地排序对数据不好，我也建议改成Union all，并且在最外层用select distinct来去重和排序

Answer 5

是的，我记得也是这样，但是改成下面的情况，变成耗时 24~28s 了！执行计划和刚才一样。而且我清除了缓存。这是开发服务器，不存在blocking问题，只有几个人在用。select distinct * from (
select c.*  from 
Comment c (NOLOCK)
WHERE EXISTS
(select userId from 
(select top 10 u.id as userId 
from [User] u (NOLOCK), Portfolio po (NOLOCK), Position ps (NOLOCK), trade t (NOLOCK)
where U.ID = po.UserId and po.id = ps.portfolio_Id and t.positionId = ps.id 
group by u.id
order by count(u.id) desc) abc 
WHERE abc.userId=c.userid) 
and isSysGenerated = 0
and c.visibilityTypeId <> 4 union all --此处用union all速度更快，将不用排除重复的记录select * from comment t (NOLOCK)
where EXISTS(SELECT 1 FROM 
(select top 10 inReplytoEventId as score 
from Comment 
where inReplyToEventId is not NULL --以后表设计时尽量不要让此字段为null这样查将会行扫瞄
group by inReplytoEventId, inReplyToDiscussionTypeId 
order by score) t1 where T.inReplytoEventId=t1.score)
and t.visibilityTypeId <> 4union all --此处用union all速度更快，将不用排除重复的记录select * from comment (NOLOCK)
where inReplyToDiscussiontypeId < 5   ) a
order by perf_commentCount desc, voteCount desc, id DESC

Answer 6

这个问题最好的方式就是改写SQL语句，另外就是看索引是否合理。之前你提供的执行计划看到了一个部分：预估返回行数和实际返回行数相差很大，你的统计信息是否最新？这个有待确定

Answer 7

把这个的执行计划贴出来看看SELECT    c.*
          FROM      Comment c ( NOLOCK )
          WHERE     EXISTS ( SELECT userId
                             FROM   ( SELECT TOP 10
                                                u.id AS userId
                                      FROM      [User] u ( NOLOCK ) ,
                                                Portfolio po ( NOLOCK ) ,
                                                Position ps ( NOLOCK ) ,
                                                trade t ( NOLOCK )
                                      WHERE     U.ID = po.UserId
                                                AND po.id = ps.portfolio_Id
                                                AND t.positionId = ps.id
                                      GROUP BY  u.id
                                      ORDER BY  COUNT(u.id) DESC
                                    ) abc
                             WHERE  abc.userId = c.userid )
                    AND isSysGenerated = 0
                    AND c.visibilityTypeId <> 4

Answer 8

[USER]表，如果id不是聚集索引，试试建聚集索引在上面。
CREATE NONCLUSTERED INDEX IX_Trade ON Trade(positionid)如果这个表上positionid没有索引就加

Answer 9

另外将字段inReplyToEventId 为null的值全部更新为''或者0，然后表结构修改一下不可以为空，并设置一个默认值
再建立一索引select 语句建议都加上(nolock)

Answer 10

[USER]表id是聚集索引，positionid也有索引了

Answer 11

谢谢， 已经都有了index， 我再看看吧