sql-server - 非标识列上的聚簇索引可加速批量插入？

Question

我的两个问题是:

• 我可以使用聚簇索引来加快速度吗在大表中进行批量插入？
• 我还能有效地使用吗如果我的外键关系IDENTITY 列不是聚集的索引了？

详细来说，我有一个数据库，其中包含几个包含公司数据的非常大(100 到 10 亿行之间)的表。通常在这样的表中有大约 20-40 家公司的数据，每家公司都是自己的“ block ”，由“CompanyIdentifier”(INT) 标记。此外，每家公司都有大约 20 个部门，每个部门都有自己的“子 block ”，由“部门标识符”(INT) 标记。

经常会在表中添加或删除整个“ block ”或“子 block ”。我的第一个想法是在这些 block 上使用表分区，但由于我使用的是 SQL Server 2008 标准版，所以我无权使用它。不过，我的大多数查询都是在“ block ”或“子 block ”上执行的，而不是在整个表上执行的。

我一直致力于为以下功能优化这些表:

1. 在子 block 上运行的查询
2. 在整个表上运行的“基准”查询
3. 插入/删除大块数据。

对于 1) 和 2) 我没有遇到太多问题。我在关键字段上创建了几个索引(在有用的地方还包含 CompanyIdentifier 和 DepartmentIdentifier)并且查询运行良好。

但是对于 3) 我一直在努力寻找一个好的解决方案。我的第一个策略是始终禁用索引，批量插入一个大块并重建索引。刚开始是很快的，但是现在数据库里的公司很多，每次重建索引都需要很长时间。

目前我的策略已更改为在插入时只保留索引，因为现在这似乎更快。但我想进一步优化插入速度。

我似乎已经注意到，通过添加在 CompanyIdentifier + DepartmentIdentifier 上定义的聚集索引，可以更快地将新“ block ”加载到表中。在我放弃这种策略以支持在 IDENTITY 列上添加聚簇索引之前，正如几篇文章向我指出的那样，聚簇索引包含在所有其他索引中，因此聚簇索引应尽可能小。但现在我正在考虑恢复这个旧策略来加速插入。我的问题是，这是明智的，还是我会在其他方面遭受性能损失？这真的会加快我的插入速度还是只是我的想象？

我也不确定在我的情况下是否真的需要 IDENTITY 列。我希望能够与其他表建立外键关系，但我是否也可以为此使用 CompanyIdentifier+DepartmentIdentifier+[uniquifier] 方案？或者它是否必须是全表的、碎片化的 IDENTITY 编号？

非常感谢任何建议或解释。

Answer 1

好吧，我已经对其进行了测试，在两个“ block 定义”列上放置聚集索引提高了我的表的性能。

与我有聚簇 IDENTITY 键的情况相比，现在插入 block 的速度相对较快，而且与没有任何聚簇索引时的速度差不多。删除 block 比使用或不使用聚簇索引更快。

我认为我想删除或插入的所有记录都保证都在硬盘的某个部分上，这使得表速度更快 - 这对我来说似乎是合乎逻辑的。

---

更新:经过一年的这种设计经验，我可以说要使这种方法起作用，有必要安排定期重建所有索引(我们每周进行一次)。否则，索引很快就会碎片化，性能会下降。尽管如此，我们正在迁移到具有分区表的新数据库设计，这基本上在各个方面都更好——除了企业服务器许可成本，但我们现在已经忘记了它。至少我有。