当您想要在大型表中选择大段数据时，低选择性覆盖索引是否有用

运行于：SQL Server 2008 R2 Standard。虽然我想这是所有数据库的问题，而不仅仅是SQL Server。

背景：我一直听说/读到/被告知指数的前沿应该是高度选择性的。当您的查询寻求特定值或一小组值（产品 ID 或类似的东西）时，这是有意义的。

一般问题： 非高度选择性指数是否有用？

例如：我有一个包含 3.5 亿行的表。该表包含一堆价格。该表包含以下列：

然后我运行此查询：

select productId 
from price 
where warehouseId = 1 
    and algorithmId = 1 
order by priceDate

具体问题：我不会从这样的指数中受益吗？

create nonclustered index ix_p 
on price (warehouseId, algorithmId, priceDate) includes (productId)

看来我会受益 b/c 我已经创建了一个覆盖索引，过滤器列组织得很好，以便 SQL Server 可以一次雕刻出巨大的块并按priceDate排序。这有意义吗？它有效吗？

注意：我将尝试一下，并会让您知道我的发现。

简短的回答 - 是的，但你基本上已经把你的存储空间翻了一番。

长答案：

我在具有 1.5 亿行数据的 SQL 2012 VirtualBox Server 2008 VM 上对此进行了测试。文件组存储在 VM 映像上，该映像位于与固态驱动器的 USB 3.0 连接上（顺序读取似乎约为 250 MB/s，写入约为 150 MB/s）。

我用伪随机日期和 productId 构建了一个表，仓库 ID 从 1-10 均匀分布，算法 1-23 均匀分布。（基本上我在 SSIS 中编写了一个加载数据的源脚本组件）。

表存储空间约为 4.7 GB，主键 priceid 上有聚簇索引。

运行此查询：

select productId 
from price 
where warehouseId = 1 
    and algorithmId = 1 
order by priceDate

~100 万行在大约 30 秒内返回。计划表示聚集索引扫描和排序（按价格排序日期）。

然后，我添加了这个非聚集索引：

create nonclustered index ix_p 
on price (warehouseId, algorithmId, priceDate) include (productId)

此索引几乎与表一样大 - 大约 4.3 GB。

添加非聚集索引消除了 priceDate 上的 SORT 步骤，并更改为执行非聚集索引查找以访问数据。创建此索引花费了 11 分钟以上。

相同的查询：~100 万行在大约 4 秒内返回。计划指示非聚集索引查找。

我认为这样做的最大事情本质上是创建数据的两个副本 - 一个在聚集索引结构中，一个在"非聚集"结构中。

我预计插入大约需要两倍的时间，因为现在您必须为每个插入基本上创建两行。

您是否定期对此表进行更新？可能还有其他一些策略可能会有所帮助。

我刚刚完成了一个非聚集索引的实现，类似于我在问题中描述的索引。表有 101,308,183 行，每行 61 个字节。以下是一些结果：

使用当前以 productId 和仓库为键的"选择性"索引：

使用我最初问题中所述的新非选择性索引：

总而言之，非选择性索引使我的逻辑读取减少了 90 倍（987 万到 110k），物理读取减少了 100%（从 20k 减少到 0），预读减少了 100%（从 967k 到 0）。

同样，我相信这是因为SQL已经对所有数据进行了排序，因此很容易切割（即排除）大块数据。由于索引涵盖了此查询（这是我们在生产环境中对其运行的仅有的两个查询之一），因此我们不会浪费时间进行键查找。

相关内容