索引性能：BigInt vs VarChar

Question

这是数据仓库中的事实表。

它有以下复合索引：

This is a FACT Table in a Data Warehouse

它具有以下复合索引：

ALTER TABLE [dbo].[Fact_Data] 
ADD  CONSTRAINT [PK_Fact_Data] 
PRIMARY KEY CLUSTERED 
(
    [Column1_VarChar_10] ASC,
    [Column2_VarChar_10] ASC,
    [Column3_Int] ASC,
    [Column4_Int] ASC,
    [Column5_VarChar_10] ASC,
    [Column6_VarChar_10] ASC,
    [Column7_DateTime] ASC,
    [Column8_DateTime] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON
) ON [PRIMARY]
GO

在这个结构中，所有的varchar 10列都只有数字值。如果我将其更改为BIGINT而不是VARCHAR来进行查询和索引，这对我是否有益？

还有其他需要考虑的利弊吗？

- Raj More

Answer 1

你应该绝对引入一个代理的INT IDENTITY()主键!! INT已经给你潜在的20亿行 - 这不够吗？？

这个在SQL Server上的主键/聚集键大小将高达64字节（而不是4），这将使您的聚集索引和所有非聚集索引扩张到难以识别的程度。这个聚集键（您的8个列）将包含在每个单独页面的每个单独非聚集索引中，浪费了大量的空间。

因此，在任何给定的索引表上，使用代理INT聚集键将会有多达16倍的条目-这意味着较少的I/O，较少的时间浪费读取索引页。

想象一下如果要与该表建立外键关系…任何子表都必须将您的主键的全部8个列作为外键列，并在每个连接中指定所有8个列-多么可怕！

在7800万行时，即使仅将聚集键更改为INT IDENTITY，每行还能节省高达60字节-仅这一项就将占用高达4 GByte的磁盘空间（和服务器中的RAM使用）。而且这还没有开始计算非聚簇索引的节省...

当然，是的，我也会将VARCHAR(10)更改为INT或BIGINT-如果它是一个数字，那么将字段类型变成数字类型-确实没有留下VARCHAR(10)的意义。但这本身不会在速度或性能方面产生巨大的差异-它只是使处理数据更加容易（不必总是在比较值等时进行数字类型转换）。

Marc

- marc_s

---

这是我的数据仓库中的事实表。没有表使用此表的主键作为外键。 - Raj More

@Raj：啊，好的，这解释了一些事情——你忘了提到这个。但是还是有一个问题：你在这个表上有非聚集索引吗？如果有的话，使用单个 INT 或 BIGINT 主键/聚集键肯定会大大受益。 - marc_s

Bigint使用64位而不是字节，也就是说它的大小是两倍而不是16倍。 - Yrlec

@Yrlec：是的，BIGINT是8字节-GUID是16字节-两倍大。但OP最初由8列组成的聚集键将达到64字节大小-这比常规INT大16倍-这就是我所指的。 - marc_s

marc_s，谢谢！什么时候开始使用bigint而不是int作为我的标识列才有意义？如果我在一个表中有大约700,000行，并且每周删除并插入所有这些行（不重新生成），那么我可以使用60年，但如果我每天都这样做（现在没有要求），我只能用8年。我正在认真考虑使用bigint！ - JohnB

@JohnB：INT类型至少可以提供2（甚至4）十亿行 - 我认为你应该相当安全。 :-) 另外：您可以每年重新设置表格作为年终维护工作的一部分。 - marc_s

Answer 2

影响索引（和整个数据库）性能的两个因素：

1）索引页的大小 2）比较速度

对于第一个因素，通常来说，您的索引/数据页面越小，您就可以在内存中保存更多的页面，并且查询能够在缓存中找到页面而不是慢速磁盘的可能性更大。因此，您需要使用最小的数据类型，以便舒适地满足现有和未来的需求。

BigInt为8个字节；如果数据很小，则VARCHAR可以更小，因此它实际上取决于您的数据。但是，长度为10个字符的数字可能适合SQL Server的INT数据类型（http://msdn.microsoft.com/en-us/library/ms187745.aspx），具体取决于您的领域。

此外，如果整行数据长度固定，则SQL Server可以在扫描中执行某些特定优化，因为它知道下一行将在磁盘上的哪个位置（假设行是连续的）。这只是一个边缘案例，但它确实有帮助。

对于第二个因素，比较整数比Unicode字符串快。因此，如果您仅存储数字数据，则应切换到适当大小的数字数据类型。

最后，Marc正确指出这将成为一个非常复杂的主键。但是，如果您的数据需要它 - 例如这些是您唯一的列，并且您永远不会执行其他查询 - 那么使优化版本（具有BigInt等）成为您的主键可能完全没问题。这有点像代码异味，因此我会重申他的建议，真正审查您的数据模型并确定是否正确。

- Matt Rogish

Answer 3

Marc S是正确的，因为64字节的主键将被复制到每个NC索引中，因此您将支付I/O成本，这将影响内存中保存的数据量（因为您在NC索引页面上浪费了空间）。因此，基础问题不是“我应该转换我的varchars”，而是“我是否应该考虑将聚集索引转换为完全不同的东西。”

就varchar与bigint而言，如果您有时间，有一个很好的理由进行转换；除了每个字段存储的2个字节差异之外，当您比较两种不同类型的值时，SQL将被迫转换其中之一。这将发生在每个比较中，无论是索引连接还是where子句中的谓词。

根据您选择的数据，连接到事实表的维度表，您可能会在每个查询中遇到转换开销，以便它加入，因为它必须转换其中一侧。

- Andrew