sql - 聚集索引应该放在哪一列上？

Question

最近，我一直在阅读所有类型的索引，主要建议是将聚集索引放在表的主键上，但是如果主键实际上没有在查询中使用(通过select 或 join)并且只是为了纯粹的关系目的而放置，因此在这种情况下不会对其进行查询。例如，假设我有一个 car_parts 表，它包含 3 列:car_part_id、car_part_no 和 car_part_title。 car_part_id 是唯一的主键标识列。在本例中，car_part_no 也是唯一的，并且很可能是 car_part_title。 car_part_no 是查询最多的内容，因此将聚集索引放在该列上而不是 car_part_id 上是否有意义？问题的基础是哪一列实际上应该具有聚集索引，因为只允许使用其中之一？

Answer 1

当且仅当过滤了索引中最左边的键时，查询优化器才可以使用聚集索引或非聚集索引。因此，如果您在列(A、B、C)上定义索引，则在 B=@b、C=@c 或 B 上定义 WHERE 条件=@b AND C=@c 将不会充分利用索引(见注释)。这也适用于加入条件。任何包含 A 的 WHERE 过滤器都会考虑索引:A=@a 或 A=@a AND B=@b 或 A=@a AND C=@c 或 A=@a AND B=@b AND C=@c。

因此，在您的示例中，如果您将 part_no 上的聚集索引设为最左边的键，那么查找特定 part_id 的查询将不会 使用索引，part-id 上必须存在单独的非聚集索引。

现在关于众多索引中的哪一个应该是聚集索引的问题。如果您有多个具有相同重要性和频率的查询模式，并且在所需的键方面相互矛盾(例如， part_no 或 频繁查询part_id)，然后您考虑其他因素:

• 宽度:聚集索引键被 所有其他非聚集索引用作查找键。因此，如果您选择宽键(例如两个唯一标识符列)，那么您将使所有其他索引变宽，从而消耗更多空间，产生更多 IO 并减慢一切速度。因此，从读取的角度来看，在同等好的键之间，选择最窄的键作为集群，而将较宽的键设置为非集群。
• 争用:如果您有特定的插入和删除模式，请尝试将它们物理分开，以便它们出现在聚集索引的不同部分。例如。如果表充当队列，所有插入都在一个逻辑端，所有删除都在另一个逻辑端，请尝试布局聚集索引，以便物理顺序与该逻辑顺序匹配(例如入队顺序)。
• 分区:如果表非常大并且您计划部署分区，那么分区键必须是聚集索引。典型的例子是使用滑动窗口分区方案归档的历史数据。即使实体具有像“entity_id”这样的逻辑主键，聚集索引也是由也用于分区函数的日期时间列完成的。
• 稳定性:经常更改的键不适合作为聚集键，因为每次都会更新聚集键值并强制所有非聚集索引更新查找他们存储的 key 。由于聚集键的更新也可能将记录重新定位到不同的页面，因此可能会导致聚集索引碎片。

注意:不能完全利用，因为有时引擎会选择非聚集索引来扫描，而不是聚集索引，仅仅是因为它更窄，因此要扫描的页面更少扫描。在我的示例中，如果您在 (A, B, C) 上有一个索引，在 B=@b 上有一个 WHERE 过滤器，并且在查询项目 C 上，则该索引可能是使用但不用作搜索，而是用作扫描，因为仍然比完整集群扫描(更少的页面)更快。