为SQL Server表选择最佳索引

Question

我有一个SQL Server表，具有以下结构：

CREATE TABLE [dbo].[Log](
 [LogID] [bigint] IDENTITY(1,1) NOT NULL,
 [A] [int] NOT NULL,
 [B] [int] NOT NULL,
 [C] [int] NOT NULL,
 [D] [int] NOT NULL,
 [E] [int] NOT NULL,
 [Flag1] [bit] NOT NULL,
 [Flag2] [bit] NOT NULL,
 [Flag3] [bit] NOT NULL,
 [Counter] [int] NOT NULL,
 [Start] [datetime] NOT NULL,
 [End] [datetime] NOT NULL)

这个表用于记录活动。列A-E表示外键，Flag1-Flag3表示某些日志状态，列Start和End标记一个活动的开始和结束。

平均每30秒更新一次该表，更新会进行大约50次插入/更新操作。

用户可以从UI中进行查询，并过滤任何给定列和所有组合的列和列类型的数据。

对于这个表，最好的优化数据检索的方式是什么：

1. 创建一个“主”索引，其中包含所有这些列
2. 识别一些最常用的过滤器组合，例如[A,D,E]，[A,Start,End]等，并为它们创建索引
3. 其他方法...

- Toni Frankola

Answer 1

我怀疑这里没有人能够给出正确的答案 - 你需要记录表格的使用情况，并从中了解正在查询哪些列的组合。

1. 创建一个“主”索引，它将包含所有这些列

那绝对不是一个好主意 - 如果您在（A,B,C,D,E）上有一个索引，并且您限制了B和D的值，那么该索引将完全无用。只有在以下情况下才有用：

• 频繁通过所有五列查询
• 频繁通过(A,B)、(A,B,C)、(A,B,C,D)等组合查询

在其他任何情况下，都是浪费 - 不要使用它。

1. 确定一些最常用的过滤器组合，例如[A，D，E]，[A，Start，End]等，并为它们创建索引

是的，这确实是唯一有希望成功的方法。您需要查看实际发生的查询类型，然后进行调整。

- marc_s

Answer 2

日志表很少被索引，因为索引会减慢插入、更新和删除语句的执行速度。

我建议使用以下方法之一：

• 在过滤之前将记录加载到表中（临时或实际的，索引的）
• 使用一个索引视图

基本上 - 如果速度/性能是一个重要问题，则在另一种形式的表中索引记录，以避免影响日志记录的性能。

- OMG Ponies

Answer 3

一种方法是让SQL Server告诉您最佳用法。在表处于“典型”使用状态时运行跟踪，然后运行数据库引擎调整顾问

- Scott Weinstein

---

...并且它返回0个建议！：)))) - Toni Frankola

太棒了，这就是正确的方式 :) - Steve Kiss

这个测试时表中有多少条记录是问题？有时索引扫描或表扫描是不好的，但对于一个小表来说并没有什么大不了的。我猜测引擎调整顾问认为没有任何索引可以提高性能。 - peter

Answer 4

在任何索引组合中，只有在外键也被引用的情况下才能使用内部键。比如你在 (A,B,C,D) 上建了一个索引：

• WHERE A=@a AND B=@b AND C=@c AND D=@d 将充分利用该索引。
• WHERE A=@a 可能 使用索引来过滤要扫描的行的范围。同样适用于WHERE A=@a AND B=@b、WHERE A=@a AND C=@c等任何具有最左侧列(A)的组合 可能 使用该索引。
• WHERe B=@b 无法使用索引。同样适用于 WHERE C=@c、WHERE D=@d 和任何缺少 A 的其他组合。换句话说，如果查询限制中没有列 A，则该索引无法使用。

这些是非常基本的规则。此外，连接条件可能会或可能不会被视为 WHERE 子句。对于更大的结果，非覆盖索引可能会触发临界点。索引不仅可以满足搜索条件，还可以帮助 ORDER BY 子句。实际要创建的索引很大程度上取决于查询模式、I/O 能力、更新负载，以及数据大小管理开销(文件和备份大小的影响)。引擎将为您提供关于可能用于查询的索引的提示(缺失索引功能)，但引擎绝不会平衡索引的好处与一个额外索引的成本(I/O、更新性能、数据大小)。有一些索引设计准则非常好，但当然，你必须阅读它们。最终，选择适当的索引取决于许多因素和考虑因素，无法给出标准答案。

- Remus Rusanu

Answer 5

我会在开始时间（datetime）上放置一个索引，这就是全部，假设对日志的查询很少是从开始到现在，大多数都是从某个起点开始。

- Tim