背景:每天创建几个计算各种不同聚合值的临时表。它们都包含相同的唯一标识符(我将在该字段上创建 PRIMARY KEY)。每个表大约有 100k 行,只有 2-8 列。然后在临时表上执行几个 JOIN 和 UNION ALL。使用所有临时表中的每个字段。此外,大多数情况下,每一行都被使用——最坏的情况是,一半的行在某处使用,另一半在其他地方使用。查询完成后,结果将保存在磁盘上,以便个人可以在一天的剩余时间里访问数据。
问题:以下哪种方法应该是最快的:
• 临时表上没有索引。
• 在所有临时表上都有一个聚集索引(通过唯一标识符上的 PRIMARY KEY 声明)。
• 对唯一标识符有一个非聚集索引,同时在所有临时表上包括其余列。
• 最后两个要点放在一起。
想法:同时运行所有四个选项时,每个选项的查询成本为 25%(相对于批次);但是,当第一个要点运行时,执行计划(在 SELECT 查询上)声明我应该在唯一标识符上创建一个非聚集索引,同时包括每个临时表的剩余列。
我对此有点困惑。如果我有效地使用所有临时表中的每个字段和行,为什么会建议这样做?堆或聚集索引不是更好吗?
对我来说,堆可以最大限度地减少创建索引和排序数据的开销;因为我基本上需要每一行,所以进行表扫描没有害处。
另一方面,聚集索引应该改进通过 JOIN 在其他两个临时表上创建的临时表,以及改进依赖 JOIN 和 UNION ALL 的最终 SELECT 查询。
相关说明:在一个有 400 列和 70M 行的大表上——不要问我为什么我们有如此多的列——当使用非相关的非聚集索引时,只获取 PRIMARY KEY 的查询要快 50 倍聚集索引。
如果有人有任何见解,我将不胜感激。
总结一下(并包括评论中的一些信息),您有一个每天清晨运行一次的流程,该流程填充了几个 100k 行临时表,每个表有 2 到 8 列。这是一个广泛的问题,但我最初的反应是在所有表上创建主键。如果性能足够好,我会很满意。如果性能不够好,我会进一步调查以找到改进代码的方法。从技术上讲,删除主键是一种选择,但总的来说,我希望通过更改填充临时表的查询来获得性能提升。
堆是没有聚集索引的表。请注意,主键不需要以与聚集索引相同的方式定义。实际上,您可以使用主键定义一个堆。据我所知,这不是一件很常见的事情。在工作负载中使用堆有一些优势:
SELECT INTO
语法,则插入可以并行运行。请注意,查询优化器可能会出于各种原因(包括估计的数据大小和MAXDOP
设置)选择不使用并行插入。一般来说,我不会担心 100k 行,特别是如果这个过程在清晨运行,没有别的东西。对于如此少量的数据,并行插入也不太可能有很大帮助。
对这种工作负载使用堆有一些缺点:
很难在这些方面说更多,因为我对您的流程一无所知。您对临时表的查询可能会受益于 a
MERGE JOIN
但我怀疑它会对您的数量产生很大影响。现在让我们谈谈具有聚集索引的表。如前所述,聚集索引不需要匹配主键,但我会假设最常见的默认值是它们都是相同的。
PRIMARY KEY
当您将内联定义为表定义的一部分时,这就是您最终的结果。对于您的工作负载,具有聚簇索引的表有几个优点:
对于您的工作负载,具有聚簇索引的表有一些缺点:
优点和缺点大多与堆表完全相反,这不足为奇。
第三个和四个选项真的不值得考虑。您可以创建一个包含所有列的索引,但它会复制数据。只需以正确的方式定义您的主键,您就不应该使用包含所有列的附加非聚集索引。请注意,SQL Server Management Studio 不建议您在表上创建聚集索引。
聚集索引/主键和非聚集索引之间的一个区别是聚集索引/主键不允许键列中有 NULL 值,并自动强制执行唯一性约束。当然,您可以创建具有相同限制的非聚集索引。
最终,您所能做的就是使用不同的选项测试您的工作负载。所以我们不能肯定地说哪种方法最快。对于一组数据和查询,堆方法可能是最快的。对于一组不同的数据和查询,聚簇表方法可能是最快的。不要低估数据完整性的价值,但您可能有其他方式来强制执行数据完整性。