加入表通常应该创建为索引组织表（聚集索引）吗？

Question

加入表通常应该创建为索引组织表（聚集索引）吗？

sql-serveroracleindexingclustered-index

6

一般来说，当涉及到加入表（即联结表）时，应该创建索引有序表（Oracle），聚集索引（SQL Server）或普通堆表（在两个列上分别建立单独的索引）。

我认为这样做的好处是：

1. 速度提升。您避免了对堆表的查找。

2. 空间利用率提高。您完全消除了堆表，因此可能节省约30%的空间。

缺点如下：

1. 索引跳过扫描（仅适用于Oracle）比全表扫描快，但比索引扫描慢。因此，在复合键的第二列上进行搜索将稍微慢一些（Oracle），更慢（MSSQL）。

2. 完全索引扫描将比完全表扫描慢 - 因此，如果大多数时间成本基于优化器正在执行哈希连接（不利用索引），则可以预期性能较差。（假设RDBMS没有先筛选表）。

这让我质疑是否真的需要任何类型的索引用于加入表，如果您主要将进行哈希连接。

- vicsz

你应该在这两列上设置一个复合主键，这样它们就会自动创建一个唯一索引。 - Martin Smith

2

“Full Index Scan will be slower than a Full Table Scan” 的意思是，Oracle 还有 INDEX FAST FULL SCAN，它基本上和全表访问一样快。请参见 http://use-the-index-luke.com/sql/explain-plan/oracle/operations#index_fast_full_scan。另请参见我下面关于哈希连接索引的评论。 - Markus Winand

@MarkusWinand - 很好的观点...感谢您提供如此棒的网站（在我看来，这是最简洁的数据库无关索引在线资源）。 - vicsz

3个回答

3

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

三个或更多表的关联实体通常需要进行更详细的分析。

此外，优化器将使用哈希连接操作中的索引；一般而言是快速全表扫描，但仍然使用索引。

- Adam Musch

0

我不熟悉Oracle术语，但对于SQL Server，问题的措辞有些令人困惑。为了澄清：

聚集索引决定表的物理顺序
非聚集索引基本上是主表的副本，按指定键排序
您可以在非聚集索引中分配（“包括”）其他列，这可以使查询优化器使用这些列来满足查询，而不是进行书签查找。
堆是没有任何类型的索引的表。对堆的所有查询都需要扫描。
如果索引比表窄，并且您不需要书签查找，则完整的非聚集索引扫描比完整的表扫描快。

因此，在考虑到这一点的情况下，用于连接的键通常应具有与之关联的聚集或非聚集索引，以避免表扫描。根据需要在非聚集索引中包括其他列，并优先选择涵盖许多行的连续键值范围的查询的聚集索引。

- RickNZ

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Andomar · Accepted Answer

我会列举并讲解几种可能的解决方案，希望能帮助你做出决定。 "联合表"包含两个或三个列。左表的外键为a，右表的为b。可选列是“联合表”的行标识符，称为id。

解决方案1：列a，b。无聚集索引（堆），索引在（a，b）和（b，a）上。这两个列都存储在三个位置。支持对a和b的搜索，并且对于b的搜索不需要书签查找，因为a是（b，a）索引的一部分。此方案还不错，但三倍的存储似乎有些浪费。堆没有用处，但必须在insert和update查询期间进行维护。

解决方案2：列a，b。在（a，b）上具有聚集索引，在（b，a）上具有索引。所有数据都存储了两次。可以为a和b提供搜索而不需要书签查找。这将是最佳实践方法。它以磁盘存储为代价换取速度。

解决方案3：列a，b。在（a，b）上具有聚集索引。所有数据仅存储一次。它可以为a提供搜索，但无法为b提供搜索。从右表到左表需要进行全表扫描。这以速度换取了磁盘空间。（您的问题提到了哈希联接。哈希联接总是进行完整扫描。）

解决方案4：列id，a，b。聚集索引（id），索引在（a）和（b）上。对a或b的搜索都需要书签查找。 a和b都存储在磁盘上两次，一次在自己的索引中，一次在聚集键中。这是我能想到的最差的解决方案。

这个列表远非详尽无遗。方案2将是一个很好的默认选择。除非其他方案在测试中明显更好，否则我会选择它。