我目前正在尝试理解更大的应用程序中的一些行为,但它归结为针对这两个表的查询：
> Users表 – 大约750个条目,UserId(varchar(50))作为集群PK
> ActionLog表 – 数百万条目,包括UserId – 但没有FK关系
对于我的ASP.NET应用程序中的网格,我试图让所有用户加上他们上次日志条目的日期.
当前使用的sql语句如下所示：
SELECT
UserId,(other columns),LastLogDate = (SELECT TOP (1) [Timestamp] FROM dbo.ActionLog a WHERE a.UserId = u.UserId ORDER BY [Timestamp] DESC)
FROM
dbo.Users u;
它返回要显示的行 – 但它相当慢(大约20秒).
我的第一个想法是在UserId上的ActionLog表上添加一个索引,并在其中包含Timestamp列：
CREATE NONCLUSTERED INDEX [IDX_UserId] ON [dbo].[ActionLog]([UserId] ASC) INCLUDE ([Timestamp])
这些行现在非常快速地返回 – 在2秒内,在ActionLog表中有350’000个条目,我的索引正在使用,正如执行计划显示的那样.一切似乎都很好.
现在,为了近似生产场景,我们在ActionLog表中加载了大约200万行,其中95％或更多是指不存在的用户(即这些行具有Users表中不存在的UserId).
现在突然,查询变得非常慢(24分钟！),并且索引不再被使用.
我假设因为ActionLog表中的绝大多数条目都不与现有用户对齐,如果我使用过滤索引,我会看到性能提升 – 在没有相应用户的情况下“清除”所有那些杂乱的条目 – 所以我创建了这个索引(替换之前存在的另一个)：
CREATE NONCLUSTERED INDEX [IDX_UserId] ON [dbo].[Log]([UserId] ASC) INCLUDE ([Timestamp]) WHERE UserId <> 'user' -- that's the fixed,non-existing "UserId" I wanted to avoid
但令我沮丧的是 – 查询仍然大致相同 – 需要20多分钟才能完成.我更新了统计数据 – 没有变化 – 仍然非常慢.
有趣的事(对我来说)是：当我删除索引并重新创建它时 – >现在查询真的很快(再次不到3秒).哇！
但是一旦我开始再次添加更多条目,查询“倾斜”并变得非常慢……
我不完全理解为什么会发生这种情况 – 我认为通过过滤索引可以消除所有这些“流氓”条目,我会在尝试为现有用户找到最新的ActionLog条目时看到良好的性能 – 但这并不是’似乎是这样的.
为什么不？
有任何想法吗？思考？要尝试的事情??
