连接表通常应该创建为索引组织的表(聚集索引)吗？

Question

一般来说..。是否应将连接表(即关联表)创建为索引组织表(Oracle)、聚集索引(SQL Server) ...或者普通的老式堆表(在两列上有单独的索引)。

在我看来，if的好处是：

速度提升。您正在避免堆表查找。

空间改善。您将完全消除堆表，因此您可能节省了大约30%的空间。

缺点：

索引跳过扫描(仅适用于Oracle) ..将比全表扫描快，但比索引扫描慢。因此，在复合键的第二列上的搜索将稍微慢一些(Oracle)，慢得多(MSSQL)。

全索引扫描将比全表扫描慢-因此，如果大多数时间基于成本的优化器正在进行哈希连接(这不利用索引)……你可能会期望更差的性能。(假设RDBMS不首先过滤表)。

这让我怀疑，如果您主要使用Hash Join，连接表是否真的需要任何类型的索引。

Answer 1

我个人的经验法则是创建两个表的关联实体作为索引组织的表，主键约束是我希望更常用的访问“方向”。然后，我通常会添加一个惟一的索引来覆盖键的相反顺序，因此在所有情况下，优化器都应该能够使用唯一扫描或范围扫描访问。

三表(或更多)关联图元通常需要更多的分析。

此外，优化器将在散列连接操作中使用索引；通常是快速的完全扫描，但索引也是如此。

Answer 2

我只是列出并讨论了一些可能的解决方案，希望能帮助你做出决定。“联合表”包含两列或三列。左表的外键为a，右表的外键为b。可选的列是“联合表”的行标识，比如id。

a,b**.解决方案1: Columns 没有聚集索引(堆)，** (a,b) (b,a)和上的索引

这两列都存储在三个位置。它支持在a和b上查找，并且查找b不需要书签查找，因为a是(b,a)索引的一部分。不错的选择，但三重存储似乎是一种浪费。堆没有任何用处，但在insert和update查询期间必须进行维护。

解决方案2: Columns a, b**.** (b,a)上的** (a,b)**，索引的聚集索引

所有数据都存储两次。可以在没有书签查找的情况下在a和b上提供搜索。这将是最佳实践方法。它用磁盘存储来换取速度。

解决方案3: Columns a, b**.** (a,b)上的聚集索引

所有数据只存储一次。它可以在a上提供seek服务，但在b上不能。从右边到左边的表格将需要表格扫描。这是以速度换取磁盘空间。(您的问题提到了散列连接。散列联接始终执行完全扫描。)

解决方案4: Columns id, a, b**.聚集索引** (id)**，(b)上的** (a) 和索引

a或b上的搜索都需要书签查找。a和b都在磁盘上存储了两次，一次在它们自己的索引中，一次在聚集键中。这是我能想到的最糟糕的解决方案。

这份清单绝对不是详尽的。解决方案2将是一个很好的默认选择。除非另一种解决方案在测试中证明自己明显更好，否则我会选择它。

Answer 3

我不熟悉Oracle术语，但对于SQL Server来说，这个问题的表达方式令人困惑。澄清一下：

• 聚集索引确定表的物理顺序
• 非聚集索引基本上是主表的副本，按分配的关键字进行排序，您可以在非聚集索引中分配(“

”)其他列，这允许查询优化程序使用这些列来满足查询，而不是执行书签查找。

• 堆是没有任何类型索引的表。对堆的所有查询都需要扫描。
• 如果索引比表窄，并且不需要书签查找，则完全非聚集索引扫描比全表扫描更快。

因此，考虑到这一点，用于连接的键通常应该具有与其关联的聚集或非聚集索引，以避免表扫描。您可以根据需要在非聚集索引中包含其他列--对于覆盖连续键值范围且每行可以访问许多列的查询，您可以优先使用聚集索引。