sql - 我可以使用索引优化包含 WHERE Value <> 2 的请求吗

Question

这是我的要求:
SELECT TOP 10 * FROM BigTable WHERE Value <> 2;
BigTable 有点特殊，如 Value列的每一行都包含相同的值:2 .在实际情况下，可能有几行具有不同的值，但不会很多。我正是需要找到这些流氓行。但是，我在设计时不知道哪个值(2 只是一个例子)(但我在查询时知道)。

请求很慢(大约 5 分钟)； BigTable 包含 1000 万行。

所以我在Value上加了一个索引列，类型为 smallint。 10 分钟后，索引建立，我再次运行请求。它仍然很慢。

该问题可以在这里重现:http://sqlfiddle.com/#!6/6ce0f/1

在这一点上，我的猜测是 SQL Server 不能对带有 <> 的查询使用索引。运营商，但我不知道为什么？例如，这个其他查询只需要 2 秒:SELECT TOP 10 Value FROM BigTable GROUP BY Value (并且它返回单行，如预期的那样具有值 2)。

我正在考虑拆分为多个查询:一个获取不同值的列表，另一个获取所有恶意行，例如 SELECT TOP 10 * FROM BigTable WHERE Value = x等(所有不是 2 的值)，但是有更好的解决方案吗？

编辑:

此查询的想法是在更新大多数行的过程之后查找尚未更新的行。基本上，我正在与另一个数据源同步。每次我运行这个过程时，我都会增加这个值，它会用新值(以及更新的数据)更新每一行。在该过程结束时，我可以检查哪些行具有旧值，然后将其删除。这个过程有点长，这就是为什么我不想先截断表然后插入，因为我需要在这个过程执行期间保持以前的数据可用。

索引是使用此请求创建的(由 Entity Framework Core 生成，但我手动进行查询测试):

CREATE NONCLUSTERED INDEX [IX_Value] ON [dbo].[BigTable]
(
    [Value] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

编辑2:

这是来自 SSMS 的估计查询计划(不是绝密，但由于我没有问我是否可以透露该项目是关于什么的，我已经模糊了数据库名称；在这些屏幕截图中，表和列名称也是真实的那些)
Value <> 2 的查询计划


你可以看到它没有使用 IX_Establishments_UpdateTag根本(索引扫描在主键上)。执行时间:5min 18s(在这次运行中，我有一行 Value/UpdateTag 不是 2)
Value < 2 的查询计划


这里 IX_Establishments_UpdateTag索引被使用。执行时间小于 1 秒(SSMS 报告 0s)。

Answer 1

SQL 可以为 <> 使用索引查询。 “可以”不代表会，只代表优化器会考虑。

SQL一般在使用时有问题OR存在——这些通常(也许总是？)导致全表扫描。 Value IN (1,2,3)必须“转换”为“Value = 1 OR Value = 2 OR Value = 3`。
Value < 2 OR Value > 2看起来很明显，但优化器可能不够聪明，无法意识到这相当于 Value <> 2 ... 所以它留下了 OR , 并伴随着表扫描。

至于为什么Value <> 2运行速度不快，这取决于您的数据。在这里做一些猜测，但没有深入必要的细节:

您有大量数据(1000 万行，好吧，但每行有多少字节？...引发了对页面和范围的讨论。)

您说很少是 <> 2(或任何您的目标值)

查询优化器使用索引统计信息来决定是否使用给定的索引

统计数据是通过对数据进行采样来构建的。如果数据如此稀少，则可能是在构建统计信息时没有采样非 2 值，因此优化器认为所有值都是 2，并将索引标记为基本无用。

(可以通过运行查看统计信息

dbcc show_statistics (<TableName>, <IndexName>)

然而，理解统计数据完全是另一回事。如果您想深入了解，请查看网络上的相关文章和讨论。)

上面有很多“为什么”。如果没有深入研究数据，分析统计数据，以及诸如此类的胡言乱语，我还没有现成的解决办法。作为一个实验，如果我们把你的 < OR >查询和 AND询问？尝试

where not (Value >= 2 and Value <= 2)

看起来很傻，可能不起作用，但值得一试，看看会发生什么。