如果你愿意的话,在把我的非聚集索引改为聚集索引之后,你可以阅读后台,事情开始运行得更快了。但是,初始查询计划耗时2-3秒的问题仍然存在。但是,keep-plan查询提示改进了很多。

幕后故事

我有一个倒排索引,在这个索引中,我将要查找的内容存储在一个简单的搜索实现中(尽管搜索并不简单)。

当我输入这样的查询“ma-br”时,它将创建这个SQL

SELECT EntityType, EntityID FROM InvertedIndex WHERE Term LIKE @p0
INTERSECT
SELECT EntityType, EntityID FROM InvertedIndex WHERE Term LIKE @p1
--@p0 String --'ma%'
--@p1 String --'br%'

对于每个搜索词,都有另一个 INTERSECT 和 SELECT

在大多数情况下,这是非常有效的。我已经索引了 Term 正确列并检查执行计划中的潜在瓶颈。现在索引包含大约150000行,如预期的那样,搜索会立即进行。

不过,有点恼人的是,执行某个级别的第一个查询通常要花更长的时间。我敢打赌,这是因为查询优化器正在重新评估执行计划。但我该怎么处理呢?我对服务器运行的查询越多,它暂停的时间就越少,但每隔一个查询大约需要2-3秒的时间。这没什么大不了的,但有时时间会长得多,我就是不知道这是从哪里来的,也不知道如何处理。它应该是闪电般的快。

编辑

架构如下:

CREATE TABLE InvertedIndex (
    Term varchar(255) NOT NULL,
    Ordinal tinyint NOT NULL,
    EntityType tinyint NOT NULL,
    EntityID int NOT NULL
)

这两个指标是:

CREATE NONCLUSTERED INDEX IX_InvertedIndex ON InvertedIndex (Term) 
INCLUDE (Ordinal, EntityType, EntityID)

CREATE NONCLUSTERED INDEX IX_InvertedIndex_Reverse ON InvertedIndex (EntityType, EntityID) 

这个东西会留下来,当索引(invertdindex)需要更新并且实际上是一个完整的重建时,插入和删除操作会发生什么,这会影响查询计划的使用吗?

这里有一个完整查询的例子,它的ATM速度非常慢,3-5秒,我不知道为什么……这个 ORDER BY 子句的作用是赋予与某个位置匹配的单词更高的排序顺序(首先出现在结果集中),但是对于每个搜索词,这已经成指数级变慢。

WITH Search AS (
    SELECT EntityType, EntityID FROM InvertedIndex WHERE Term LIKE @p0
    INTERSECT
    SELECT EntityType, EntityID FROM InvertedIndex WHERE Term LIKE @p1
    INTERSECT
    SELECT EntityType, EntityID FROM InvertedIndex WHERE Term LIKE @p2
)
SELECT p.PersonID
, p.FullName
, p.Email
, p.MobilePhone
, p.HomeAddress
, p.HomeCity
FROM Search AS s
INNER JOIN Person AS p ON p.PersonID = s.EntityID AND s.EntityType = @pPersonEntityType
ORDER BY (CASE WHEN @p3 IN (SELECT Ordinal FROM InvertedIndex WHERE Term LIKE @p0 AND EntityID = s.EntityID AND EntityType = s.EntityType) THEN 0 ELSE 1 END) + (CASE WHEN @p4 IN (SELECT Ordinal FROM InvertedIndex WHERE Term LIKE @p1 AND EntityID = s.EntityID AND EntityType = s.EntityType) THEN 0 ELSE 1 END) + (CASE WHEN @p5 IN (SELECT Ordinal FROM InvertedIndex WHERE Term LIKE @p2 AND EntityID = s.EntityID AND EntityType = s.EntityType) THEN 0 ELSE 1 END)
@p0 String --'ma%'
@p1 String --'br%'
@p2 String --'mi%'
@p3 Int32 --1
@p4 Int32 --2
@p5 Int32 --3

上面的查询的要点是查找 InvertedIndex 然后,对于每个搜索项都有一个交叉点,这是我希望用来约束搜索的逻辑连接。序数表示索引时单词的原始位置。invertedIndex中的每个条目都代表一个元组,如果搜索项与这个n元组中的某个项匹配,则被认为是更好的匹配。这就是为什么我需要通过子查询来完成这个奇怪的顺序。但它真的很慢。

回答

如果我改变 IX_InvertedIndex 对于聚集索引,它可以将查询速度提高一个数量级(但我不知道为什么):

CREATE CLUSTERED INDEX IX_InvertedIndex ON InvertedIndex (Term)