设想
我有一个名为 Pro2SQL by Progress 的中间件软件产品,可以将数据从 Progress 数据库实时复制到 SQL Server。Pro2SQL通过中间件管理SQL Server架构。Pro2SQL 在 SQL Server 端创建的唯一索引是唯一的非聚集行 ID 索引。我可以在查询中完全忽略此行 ID,因为它专门用于 Pro2SQL 并发。
有些表包含 100 多万行,所以我需要创建索引。从 Progress 复制到 SQL Server 的每个表中,都有一个“域”字段。该字段具有两个值之一,每个值三个字符。我们称它们为“ABC”或“XYZ”。在进度数据库方面,该domain
字段是索引的一部分。
问题
domain
在 SQL Server 端添加该字段作为多字段索引的一部分是否会增加任何价值,还是会让事情变得更糟?例如,我可能在表上有以下多字段非聚集索引(请注意,这些字段使 Progress db 表中的行具有唯一性):
isb_eu_nbr
(非常有选择性)isb_part
(非常有选择性)isb_serial
(非常有选择性)isb_ref
(适度选择性)isb_domain
(非常没有选择性,只有“ABC”或“XYZ”,我的问题的来源)
该表将包含大约 500,000 多行。
用法
所有查询都将是只读查询。不会有插入、更新或删除。这些由 Pro2SQL 中间件处理。在我的所有查询中,我必须在WHERE
子句中添加以下内容:
/* WHERE <tableAbbr>_domain = 'ABC' */
/* Example */
WHERE
/* Conditions 1 through N */
AND isb_domain = 'ABC'
/* Another Example, with JOINed tables */
WHERE
/* Conditions 1 through N */
AND isb_domain = 'ABC'
AND ls_domain = 'ABC'
AND pt_domain = 'ABC'
其他考虑因素
供应商 Progress 表示,由于整个工作日不断发生实时更新,因此所有查询都必须是非锁定的。所以他们实际上建议(!!!)NOLOCK
。我们已经SNAPSHOT
在数据库上启用了,因此所有直接连接到服务器的查询都将运行SET TRANSATION ISOLATION LEVEL SNAPSHOT
,并且所有通过链接服务器运行的查询都将运行SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
(接受副作用)。
更新
我发现我可以在 SQL Server 表上创建聚集的非唯一索引。另外,根据下面的建议,我可以创建非唯一、非聚集过滤索引,其中过滤器将位于<abc>_domain
每个表中的字段上。所以我的问题变成了:
我是否应该创建与进度数据库的字段匹配的非唯一聚集索引以实现唯一性,减去<abc>_domain
fields ,然后创建在每个表中包含该字段的非唯一非聚集过滤索引<abc>_domain
?
我确实明白,如果没有实际查看数据库和数据,硬性的是/否并不总是可行的。我还是会做性能测试。我更多地寻找“我会从......开始”的答案。
一般来说
如果查询定期搜索所有这些列,那么是的,将它们全部放在同一个索引中将是有益的,因此可以在计划中尽早应用所有搜索谓词。
如果您要创建一个不包含某些正在搜索的列的非聚集索引,优化器将可以选择:
查找可用于检索查询中其他位置所需的列(例如选择列表),或对 where 子句中的列应用附加过滤。
优化器在这些基于成本的选择中有点变化无常,并且计划可能会以有害的方式改变或重复使用。由于这是相当少量的列,因此我可能会采取更安全的路线,并将它们全部放在单个索引中以进行这些搜索。
当然#1,选择列表中的任何其他列可能也需要被考虑为非聚集索引中包含的(非键)列,但没有提供任何示例查询来做出该决定。
当然#2,如果您的搜索查询使用前三个选择性列来生成可靠的小型结果集,您对查找或计划重用的长期担忧就会少得多。
当然#3,仅仅因为值是选择性的并不意味着它们将以选择性的方式或组合进行搜索。例如,您可以在表中拥有一组完全唯一的日期时间值,但如果有人从 1900-01-01 到 9999-12-31 进行搜索,那么这并不是一个非常有选择性的范围。
没有聚集索引?...已经有了一个糟糕的开始。
如果没有看到现在运行的实际查询以及更改前后的执行计划,很难明确地说。基本上需要测试才能找出答案。
我会考虑一件事,因为
isb_domain
不是很有选择性,如果您总是只关心ABC
而不关心XYZ
,请尝试制作过滤索引。这将WHERE
在其定义的末尾有一个子句,例如CREATE NONCLUSTERED INDEX IX_YourIndexName ON TheTable (WhateverColumnsWereAlreadyBeingIndexed) WHERE isb_domain = 'ABC';
. 这提供了一些好处:通过将供应商应用程序解耦到单独的索引中,它可以防止您破坏供应商应用程序正在生成的索引,这些索引可能会被覆盖或导致供应商应用程序出现问题。
它最终不会成为供应商应用程序生成的索引的完全冗余索引,这使得数据库系统更容易维护,因为它只包含一半的数据。
它可能是您的查询的候选者,并通过扫描或查找较少的记录来提高 I/O 性能。