单列与多列索引的性能开销

Question

您好，我想知道在插入方面，单列索引与多列索引的性能开销如何。因此，例如，如果我有3个单列索引，那么插入到该表中会更好，而不是让1个索引包含多个列。当我谈到性能时，我对原始速度感兴趣。

Answer 1

无论是单列还是多列，INDEXes的插入开销通常都可以忽略不计。

除非写入操作明显超过读取操作，否则应始终包含任何提高查询性能所需的索引。

在某些情况下，与单个多列索引相比，多个单列索引足以提高更多查询的性能。更常见的情况是，单个多列索引可以进一步提高较小查询集的性能。

一般规则是考虑查询性能，而不是插入性能，除非您可以预测或看到插入的特定问题。

Answer 2

我认为唯一的答案是在你的硬件上用你的系统使用模式测试一些场景。通常，与没有索引相比，索引开销是巨大的。因此，索引中的列越多，开销就越大。但是用户感知的影响可能看起来是零，但描述不同的测试并查看数字。我在一些地方工作过，在那里他们只会对所有东西都建立索引。我不同意这种方法。我认为你应该在你能证明它是有价值的时候建立一个索引。除了增加开销之外，它们还会占用空间。同样，您需要测试您自己的设置来回答您的问题。

Answer 3

如果我有3个单列索引，那么插入到该表中会更好，而不是只有1个索引包含多个列。

对于修改数据的操作，3列的单个索引应该比3个单列的索引更快，原因如下：

• 更新索引需要在B树中搜索要执行修改的正确位置。在一个索引中执行搜索(即使它是复合的)往往比在3个索引中执行3个搜索更快。
• 每个B树leaf都包含一个行“指针”。1个索引的行指针数将比3个索引少3倍(忽略NULL的影响，通常没有索引)。由于缓存的影响，较小的索引通常更快。与3个索引相比，
• 1索引的B树节点的splitting和合并可能更少。

请考虑以下MS SQL Server基准测试：

CREATE TABLE ONE_INDEX (
    ID int PRIMARY KEY NONCLUSTERED,
    F1 uniqueidentifier NOT NULL,
    F2 uniqueidentifier NOT NULL,
    F3 uniqueidentifier NOT NULL
);
CREATE INDEX ONE_INDEX_IE1 ON ONE_INDEX (F1, F2, F3);

CREATE TABLE THREE_INDEXES (
    ID int PRIMARY KEY NONCLUSTERED,
    F1 uniqueidentifier NOT NULL,
    F2 uniqueidentifier NOT NULL,
    F3 uniqueidentifier NOT NULL
);
CREATE INDEX THREE_INDEXES_IE1 ON THREE_INDEXES (F1);
CREATE INDEX THREE_INDEXES_IE2 ON THREE_INDEXES (F2);
CREATE INDEX THREE_INDEXES_IE3 ON THREE_INDEXES (F3);
GO



SET NOCOUNT ON
DECLARE @t DATETIME;
DECLARE @id INT;
DECLARE @count INT;
SET @count = 100000;



PRINT 'ONE_INDEX:'

SET @t = CURRENT_TIMESTAMP
SET @id = 0;
BEGIN TRANSACTION;
WHILE @id < @count BEGIN
    INSERT INTO ONE_INDEX VALUES(@id, NEWID(), NEWID(), NEWID());
    SET @id = @id + 1;
END
COMMIT TRANSACTION;
PRINT '    INSERT ' + CAST(@count AS VARCHAR) + ' rows: ' + CAST(DATEDIFF(ms, @t, CURRENT_TIMESTAMP) AS VARCHAR) + ' ms';

SET @t = CURRENT_TIMESTAMP
SET @id = 0;
BEGIN TRANSACTION;
WHILE @id < @count BEGIN
    UPDATE ONE_INDEX SET F1 = NEWID(), F2 = NEWID(), F3 = NEWID() WHERE ID = @id
    SET @id = @id + 1;
END
COMMIT TRANSACTION;
PRINT '    UPDATE ' + CAST(@count AS VARCHAR) + ' rows: ' + CAST(DATEDIFF(ms, @t, CURRENT_TIMESTAMP) AS VARCHAR) + ' ms';

SET @t = CURRENT_TIMESTAMP
DELETE FROM ONE_INDEX;
PRINT '    DELETE ' + CAST(@count AS VARCHAR) + ' rows: ' + CAST(DATEDIFF(ms, @t, CURRENT_TIMESTAMP) AS VARCHAR) + ' ms';



PRINT 'THREE_INDEXES:'

SET @t = CURRENT_TIMESTAMP
SET @id = 0;
BEGIN TRANSACTION;
WHILE @id < @count BEGIN
    INSERT INTO THREE_INDEXES VALUES(@id, NEWID(), NEWID(), NEWID());
    SET @id = @id + 1;
END
COMMIT TRANSACTION;
PRINT '    INSERT ' + CAST(@count AS VARCHAR) + ' rows: ' + CAST(DATEDIFF(ms, @t, CURRENT_TIMESTAMP) AS VARCHAR) + ' ms';

SET @t = CURRENT_TIMESTAMP
SET @id = 0;
BEGIN TRANSACTION;
WHILE @id < @count BEGIN
    UPDATE THREE_INDEXES SET F1 = NEWID(), F2 = NEWID(), F3 = NEWID() WHERE ID = @id
    SET @id = @id + 1;
END
COMMIT TRANSACTION;
PRINT '    UPDATE ' + CAST(@count AS VARCHAR) + ' rows: ' + CAST(DATEDIFF(ms, @t, CURRENT_TIMESTAMP) AS VARCHAR) + ' ms';

SET @t = CURRENT_TIMESTAMP
DELETE FROM THREE_INDEXES;
PRINT '    DELETE ' + CAST(@count AS VARCHAR) + ' rows: ' + CAST(DATEDIFF(ms, @t, CURRENT_TIMESTAMP) AS VARCHAR) + ' ms';
GO



DROP TABLE ONE_INDEX;
DROP TABLE THREE_INDEXES;
GO

(在我的MS SQL Server Express 2008 R2上的虚拟机下)打印：

ONE_INDEX:
    INSERT 100000 rows: 4173 ms
    UPDATE 100000 rows: 5530 ms
    DELETE 100000 rows: 2706 ms
THREE_INDEXES:
    INSERT 100000 rows: 6640 ms
    UPDATE 100000 rows: 10436 ms
    DELETE 100000 rows: 3516 ms

将@count增加到1000000会导致：

ONE_INDEX:
    INSERT 1000000 rows: 40143 ms
    UPDATE 1000000 rows: 55796 ms
    DELETE 1000000 rows: 95576 ms
THREE_INDEXES:
    INSERT 1000000 rows: 61360 ms
    UPDATE 1000000 rows: 91766 ms
    DELETE 1000000 rows: 99500 ms

请注意，在大多数工作负载中，读取的数量超过写入的数量，因此优化工作通常优先考虑索引的SELECT，而不是插入、更新和删除。此外，与3个单独的索引相比，复合索引将以不同的方式影响查询性能。

只有你才能执行适当的测试，并在所有这些关注点之间达到适当的平衡。