sql-server - MS-SQL 何时维护表索引？

Question

为了论证起见，假设它适用于 SQL 2005/8。据我所知，当您在表上放置索引来调整 SELECT 语句时，这些索引需要在 INSERT/UPDATE/DELETE< 期间维护 Action 。

我的主要问题是:

SQL Server 何时维护表的索引？

我还有很多后续问题:

我天真地认为它会在命令执行后执行此操作。假设您要插入 20 行，它将在插入并提交 20 行后维护索引。

• 如果脚本具有多个语句靠在 table 上，但在其他方面不同的陈述？

• 服务器有智能吗毕竟要维护索引语句已执行或确实执行每条语句都有吗？

我见过在大量/多次 INSERT/UPDATE 操作后删除并重新创建索引的情况。

• 这可能会导致重建整个表的索引，即使您只更改少数行？

• 是否会有性能优势尝试整理 INSERT 和UPDATE 操作到更大的批处理中，通过收集要插入的行来表示临时表，而不是做许多较小的插入物？

• 整理上面的行会如何防止删除索引和受到维护影响？

抱歉问题太多了 - 这是我一直都知道要注意的事情，但是当尝试调整脚本以获得平衡时，我发现我实际上并不知道索引维护何时发生。

编辑:我知道性能问题很大程度上取决于插入/更新期间的数据量和索引数量。再次为了论证，我有两种情况:

• 调整了索引繁重的表选择。
• 索引灯台 (PK)。

这两种情况都会有大量插入/更新批处理，例如 10k+ 行。

编辑2:我知道能够在数据集上分析给定的脚本。然而，分析并没有告诉我为什么给定的方法比另一种方法更快。我更感兴趣的是索引背后的理论以及性能问题的根源，而不是明确的“这比那更快”的答案。

谢谢。

Answer 1

当您的报表(甚至不是交易)完成时，您的所有索引都是最新的。当您提交时，所有更改都将变为永久性，并且所有锁都将被释放。否则就不是“智能”，它会违反完整性并可能导致错误。

编辑:我所说的“完整性”是指:一旦提交，数据应该立即可供任何人使用。如果当时索引不是最新的，有人可能会得到不正确的结果。

随着批量大小的增加，性能最初会提高，然后会变慢。您需要运行自己的基准测试并找出最佳批量大小。同样，您需要进行基准测试以确定删除/重新创建索引是否更快。

编辑:如果您在一个语句中插入/更新/删除批量行，则每个语句都会修改您的索引一次。以下脚本演示了:

CREATE TABLE dbo.Num(n INT NOT NULL PRIMARY KEY);
GO
INSERT INTO dbo.Num(n)
SELECT 0
UNION ALL
SELECT 1;
GO
-- 0 updates to 1, 1 updates to 0
UPDATE dbo.Num SET n = 1-n;
GO
-- doing it row by row would fail no matter how you do it
UPDATE dbo.Num SET n = 1-n WHERE n=0;
UPDATE dbo.Num SET n = 1-n WHERE n=1;