SQL查询在DB服务器上运行缓慢

Question

附执行计划

SELECT a.*, k.UserPrivileges, k.Type
FROM NotifyInterests2 a, KUAF k
WHERE a.UserID = k.ID
AND EXISTS (SELECT ID FROM KUAF WHERE a.UserID = ID AND NOT Type IN (1, 2, 4))
AND (a.NodeID IN ( SELECT 0 UNION ALL ( SELECT DISTINCT( -1 * ID )
FROM KUAF WHERE Type = 5 ) UNION ALL ( SELECT DISTINCT AncestorID
FROM DTreeAncestors 
WHERE Exists ( SELECT EventInt2 FROM LLEventQueue     
WHERE EventHandlerID = 9001 AND EventSeqNo <=45075882 AND EventInt2 = DataID    
UNION ALL SELECT EventInt2 * -1 FROM LLEventQueue 
WHERE EventHandlerID = 9001    
AND EventSeqNo <=45075882 AND EventInt2 = DataID ))))

Answer 1

这是相当的查询=)提示:尝试"分组"你的代码的部分可读性;它会让你的生活轻松100倍;现在，当你在写这个的时候，甚至在你将来维护这个的时候=)

还有一些问题:

• 什么叫"慢"?
• 每个表中有多少条记录?
• 查询返回多少条记录?
• 表上的索引是什么?(sp_helpindex)

另外，如果能看到整个查询计划可能会很有帮助，但是有一点很明显，KEAF似乎没有聚集索引。我猜它也因此丢失了一个主键?由于您在那里有一个字段ID，我希望它唯一地标识每个记录?如果是，向表中添加一个PK，并检查这对整个查询有什么影响。

我试着重新安排了一些可读性，我结束了下面的东西。我赌了一些别名，因为它们在原始查询中丢失了。

SELECT a.*, k.UserPrivileges, k.Type
FROM NotifyInterests2 a
JOIN KUAF k
ON k.ID = a.UserID
WHERE EXISTS ( SELECT kx.ID 
FROM KUAF kx
WHERE kx.ID = a.UserID
AND NOT kx.Type IN (1, 2, 4) )
AND  a.NodeID IN ( SELECT 0 
UNION ALL 
SELECT DISTINCT( -1 * k5.ID )
FROM KUAF k5
WHERE k5.Type = 5 
UNION ALL 
SELECT DISTINCT da.AncestorID
FROM DTreeAncestors da
WHERE EXISTS ( SELECT l1.EventInt2 
FROM LLEventQueue l1
WHERE l1.EventHandlerID = 9001 
AND l1.EventSeqNo <= 45075882 
AND l1.EventInt2 = da.DataID
UNION ALL 

SELECT l2.EventInt2 * -1 
FROM LLEventQueue l2
WHERE l2.EventHandlerID = 9001
AND l2.EventSeqNo <= 45075882 
AND l2.EventInt2 = da.DataID )
)

我认为有些部分可以简化。这真的没有意义:

SELECT DISTINCT da.AncestorID
FROM DTreeAncestors da
WHERE EXISTS ( SELECT l1.EventInt2 
FROM LLEventQueue l1
WHERE l1.EventHandlerID = 9001 
AND l1.EventSeqNo <= 45075882 
AND l1.EventInt2 = da.DataID
UNION ALL 

SELECT l2.EventInt2 * -1 
FROM LLEventQueue l2
WHERE l2.EventHandlerID = 9001
AND l2.EventSeqNo <= 45075882 
AND l2.EventInt2 = da.DataID )

您只是在WHERE EXISTS()部分中执行两次相同的查询。此外，由于该部分属于WHERE IN (...)结构，因此不需要DISTINCT。事实上，它可能更容易转换成WHERE EXISTS()结构。像这样:

AND (  a.NodeID = 0
OR EXISTS (SELECT *
FROM KUAF k5
WHERE k5.Type = 5 
AND ( -1 * k5.ID ) = a.node_id)
OR EXISTS ( SELECT *
FROM DTreeAncestors da
WHERE da.AncestorID = a.NodeID
AND EXISTS ( SELECT *
FROM LLEventQueue l1
WHERE l1.EventHandlerID = 9001 
AND l1.EventSeqNo <= 45075882 
AND l1.EventInt2 = da.DataID )
)

通常我不是OR的粉丝，但我认为在这种情况下它可能有效。

最后是:

SELECT a.*, k.UserPrivileges, k.Type
FROM NotifyInterests2 a
JOIN KUAF k
ON k.ID = a.UserID
WHERE EXISTS ( SELECT kx.ID 
FROM KUAF kx
WHERE a.UserID = kx.ID 
AND NOT kx.Type IN (1, 2, 4) )
AND (  a.NodeID = 0
OR EXISTS (SELECT *
FROM KUAF k5
WHERE k5.Type = 5 
AND ( -1 * k5.ID ) = a.node_id)
OR EXISTS ( SELECT *
FROM DTreeAncestors da
WHERE da.AncestorID = a.NodeID
AND EXISTS ( SELECT *
FROM LLEventQueue l
WHERE l.EventHandlerID = 9001 
AND l.EventSeqNo <= 45075882 
AND l.EventInt2 = da.DataID )
)
)

尝试一下并检查查询计划。如果它仍然太慢，我们需要查看定制表上的索引，看看我们可以在哪里优化它们。

Answer 2

您没有提到表中是否存在索引以及哪些索引存在。我将尝试创建以下索引，看看是否有任何性能增益:

CREATE INDEX kuaf_idx_id_type ON KUAF (ID,Type);
CREATE INDEX kuaf_idx_type ON KUAF (Type);
CREATE INDEX lleventqueue_idx_eventha_eventin_dataid_eventse ON LLEventQueue (EventHandlerID,EventInt2,DataID,EventSeqNo);