SQL Server索引性能-长列

Question

在SQL Server（2005+）中，我需要索引一个nvarchar(2000+)的列（只进行精确匹配）。最可扩展、性能最佳的方法是什么？

在SQL Server（2005+）中，对以下类型的列进行索引会有哪些实际差异：

- nvarchar(2000) - char(40) - binary(16) 例如，针对已索引的binary(16)列进行查找是否比针对已索引的nvarchar(2000)列更快？如果是，差别有多大？

显然，在某些方面，较小的长度总是更好的，但我对SQL Server如何优化其索引的处理方式不够熟悉。

- Rex M

---

你需要搜索还是强制唯一性？ - A-K

@Alex 我需要强制唯一性，但只会进行精确匹配。 - Rex M

另一个想法是将您的nvarchar压缩为较小的二进制值，并在其上建立索引，但您能保证每个值始终压缩到900字节或更少吗？ - A-K

Answer 1

当然，一个二进制(16)的速度会快得多 - 只需进行最快的计算即可：

• SQL Server页面始终为8K
• 如果每个条目有16个字节，则可以在一页上存储500个条目
• 对于每个条目的4000个字节(nvarchar)，您将最多得到2个条目(最坏情况是如果您的NVARCHAR(2000)完全填充)

如果您有一个包含100,000个条目的表，则需要具有二进制(16)键的索引的页面数为200页，而需要具有nvarchar(2000)相同索引的页面为50,000页。

即使只是添加I/O来读取和扫描所有这些页面，也会破坏您可能拥有的任何性能。

Marc

更新:
对于我的通常索引，我尽量避免复合索引 - 从其他表引用它们会变得混乱(带有几个等式比较的WHERE子句)。

此外，定期检查和维护您的索引 - 如果碎片化超过30％，则重建 - 如果碎片化在5-30％之间，则重新组织。在http://sqlfool.com/2009/06/index-defrag-script-v30/网站上查看自动的、经过充分测试的DB索引维护脚本。

对于SQL Server表上的聚集键，请尽量避免使用GUID，因为它们是随机的，因此可能导致大规模的索引碎片化，从而影响性能。此外，虽然不是硬性要求，但请确保您的聚集键是唯一的 - 如果不是，则SQL Server将向其添加四字节的唯一标识符。此外，聚集键会被添加到每个非聚集索引中的每个条目中 - 因此，在聚集键中拥有一个小的、唯一的、稳定的(非更改)列非常重要(最理想的情况是逐渐递增，这可以提供最佳的特性和性能--> INT IDENTITY是完美的)。

- marc_s

---

除了纯粹的空间考虑之外，还有什么？如果将几个其他列与索引一起存储，那么您的页面数量比较就不会那么激烈，还会有哪些差异？ - Rex M

Answer 2

您的思路有些偏差：

• 如果需要达到性能目标，请创建索引
• 不要创建不必要的索引

无论列是binary(16)还是nvarchar(2000)，都没有太大区别，因为您不能随意添加索引。

不要让索引选择决定您的列类型。如果需要对nvarchar(2000)进行索引，请考虑使用全文索引或添加哈希值进行索引。

---

根据您的更新，我可能会创建一个校验和列或使用HashBytes()函数创建一个计算列并对其进行索引。请注意，校验和与加密哈希不同，因此可能会有碰撞，但您也可以匹配整个文本内容，并且它将首先通过索引进行过滤。HashBytes()更不容易发生碰撞，但仍然可能发生，因此您仍然需要比较实际列。对于每个查询和每个更改，计算哈希的成本也更高。

- Joel Coehoorn

---

实际上，这就是我问这个问题的原因之一 - 使用一个大字段的短二进制哈希来创建索引会更好吗？ - Rex M

哈希列只能寻找精确匹配。如果您不需要部分匹配（LIKE 'foo%'）或范围（BETWEEN 'A' AND 'B'），那么可以使用哈希。 - Remus Rusanu

好的，现在我们来看一个不同的问题：“我需要对一个nvarchar（2000）列进行索引。目标是使这种类型的查询运行更快：______。我该怎么做？” - Joel Coehoorn

@Joel 谢谢，我已经将问题的范围缩小到那个方面了。 - Rex M

如果（加密）哈希结果足够大，则发生冲突的几率非常低，您不需要比较实际列值。我怀疑128位对于几乎所有目的都足够了：您需要插入2 ^ 64个值（相当难以实现的数据量），才能有很好的机会观察到一对值发生冲突。如果出于某种原因您不信任这一点 - 只需使用更长的哈希；随着位数的增加，冲突的概率呈指数级下降。Sha512是常见且速度不太慢的哈希算法... - Eamon Nerbonne

Answer 3

每个索引条目最多只能有900字节，所以你的nvarchar(2000)行不通。最大的差异将是索引深度-从根页面到叶子页面遍历的页数。因此，如果你需要搜索，可以像这样在CHECKSUM上建立索引：

alter table recipe add text_checksum as checksum(recipe_text)
create index text_checksum_ind on recipe(text_checksum)

（来自此处的示例计算列上的索引：加速查询，添加业务规则），这将不会给你一个精确的匹配，只会很好地缩小你的搜索范围。

当然，如果你需要强制唯一性，你就必须使用触发器。

另一个想法是将你的nvarchar压缩成一个较小的二进制值，并对其进行索引，但你能保证每个值都始终被压缩到900字节或更少吗？

- A-K

---

+1 优秀的观点，是的 - 900 字节是索引条目的最大值。 - marc_s

你需要比32位校验和更大的哈希表。CHECKSUM返回int类型，即使在最好的情况下，只有64k条记录，也会有50%的概率发生冲突，这是一个非常非常小的表。http://rusanu.com/2009/05/29/lockres-collision-probability-magic-marker-16777215/ - Remus Rusanu

Remus，使用更大的哈希表可以降低误判的概率，但仍然可能存在一些情况。这种情况下只会触发一些触发器。 - A-K

如果您决定使用触发器来强制执行它，那么快速、小型的哈希就可以了，因为您将手动解决冲突。另一方面，足够大的哈希允许您仅依靠机会，并且不允许重复项（如果冲突相对不太可能，即使在中间遇到），然后您可以依赖于索引唯一性，比触发器更有效率。当然，这是一个权衡，正确的路径取决于具体情况。 - Remus Rusanu

Answer 4

索引的最大长度是900字节，因此您不能索引NVARCHAR（2000）。

更大的索引键意味着更少的键适合于索引页面，因此它会创建一个更大的树，使用更多磁盘空间，更多的I/O，更多的缓冲区拉取和更少的缓存。对于聚集键来说，这是更糟糕的，因为聚集键值被用作所有其他非聚集、索引上的查找值，因此它增加了所有索引的大小。

最终，在查询中最普遍的性能驱动指标是扫描/查找的页数。这转化为物理读取(=I/O等待时间)或逻辑读取(=缓存污染)。

除了空间考虑之外，数据类型在查询行为方面几乎没有任何影响。char/varchar/nchar/nvarchar具有需要考虑比较的排序规则，但排序顺序查找的成本通常不是决定性因素。

最后但并非最不重要的因素是您的应用程序访问模式。对能使查询SARGable的列进行索引，维护未被优化器使用的索引没有任何好处。

有时候，您必须考虑并发问题，比如当您需要消除由不同更新访问路径导致的死锁（deadlocks caused by distinct update access path to the same record）时。

发布编辑后的更新

使用持久化的MD5哈希列：

create table foo (
    bar nvarchar(2000) not null, 
    [hash] as hashbytes('MD5', bar) persisted not null,
    constraint pk_hash unique ([hash]));
go


insert into foo (bar) values (N'Some text');
insert into foo (bar) values (N'Other text');
go

select * from foo
    where [hash] = hashbytes('MD5', N'Some text');
go

你必须非常小心地处理你的查找，因为哈希值会因输入的任何差异而大幅不同，例如，如果你寻找Ascii参数而不是Unicode参数...如果表格变得很大，你将有一个相当高的碰撞几率。

- Remus Rusanu

Answer 5

其实最好的方法是进行基准测试并自己观察。例如，以下脚本比较了通过4字节整数和50字节字符进行索引查找的情况。对于整数（建立在INT列上的B树深度），需要3次读取，而对于字符（建立在CHAR列上的B树深度），需要4次读取。

CREATE TABLE dbo.NarrowKey(n INT NOT NULL PRIMARY KEY, m INT NOT NULL)
GO
DECLARE @i INT;
SET @i = 1;
INSERT INTO dbo.NarrowKey(n,m) SELECT 1,1;
WHILE @i<1024000 BEGIN
  INSERT INTO dbo.NarrowKey(n,m)
    SELECT n + @i, n + @i FROM dbo.NarrowKey;
  SET @i = @i * 2;
END;
GO
DROP TABLE dbo.WideKey
GO
CREATE TABLE dbo.WideKey(n CHAR(50) NOT NULL PRIMARY KEY, m INT NOT NULL)
GO
DECLARE @i INT;
SET @i = 1;
INSERT INTO dbo.WideKey(n,m) SELECT '1',1;
WHILE @i<1024000 BEGIN
  INSERT INTO dbo.WideKey(n,m)
    SELECT CAST((m + @i) AS CHAR(50)), n + @i FROM dbo.WideKey;
  SET @i = @i * 2;
END;
GO
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
SELECT * FROM dbo.NarrowKey WHERE n=123456
SELECT * FROM dbo.WideKey WHERE n='123456'

对于更宽的键，索引查找速度会慢33％，但表的大小增加了4倍：

EXEC sp_spaceused 'dbo.NarrowKey';
-- 32K
EXEC sp_spaceused 'dbo.WideKey';
-- 136K

- A-K