首页 文章

群集与非群集

提问于
浏览 次
95

我对SQL（Server 2008）的较低级别知识是有限的，现在由我们的DBA挑战 . 让我解释一下（我已经提到了明显的陈述，希望我是对的，但如果你看错了，请告诉我）情景：

我们有一张 table 可以为人们提供“法院命令” . 当我创建表（Name：CourtOrder）时，我创建了它：

CREATE TABLE dbo.CourtOrder
(
  CourtOrderID INT NOT NULL IDENTITY(1,1), (Primary Key)
  PersonId INT NOT NULL,
  + around 20 other fields of different types.
)

然后，我将非聚集索引应用于主键（以提高效率） . 我的理由是它是一个唯一的字段（主键），并且应该被编入索引，主要用于选择目的，因为我们经常 Select from table where primary key = ...

然后我在PersonId上应用了CLUSTERED索引 . 原因是在物理上对某个人进行分组，因为绝大多数工作都是为了获得一个人的订单 . 所以， select from mytable where personId = ...

我现在已被拉上了这个 . 有人告诉我，我们应该将聚簇索引放在主键上，将正常索引放在personId上 . 这对我来说似乎很奇怪 . 首先，为什么要在特殊列上放置聚簇索引？什么是聚类？当然这是浪费聚集索引？我相信一个普通的索引会用在一个独特的列上 . 此外，聚类索引意味着我们不能聚集不同的列（每个表一个，对吧？） .

我被告知我犯了一个错误的原因是他们认为在PersonId上放置聚集索引会使插入变慢 . 对于选择速度增加5％，我们将在插入和更新时降低95％的速度 . 这是正确有效的吗？

他们说因为我们聚集了personId，所以当我们插入或更改PersonId时，SQL Server必须重新排列数据 .

那么我问过，为什么SQL会有一个CLUSTERED INDEX的概念，如果它这么慢？它和他们说的那么慢吗？我应该如何设置索引以获得最佳性能？我以为SELECT比INSERT更多......但他们说我们在INSERTS上遇到了锁定问题......

希望可以有人帮帮我 .

sql performance sql-server-2008 indexing

5 回答

  • 3

    聚簇索引与非聚簇索引之间的区别在于聚簇索引确定数据库中行的物理顺序 . 换句话说，将聚簇索引应用于 PersonId 意味着行将在表中按 PersonId 进行物理排序，允许对此进行索引搜索直接到达行（而不是非聚集索引，这将指导您行的位置，添加一个额外的步骤） .

    也就是说，主键不是聚集索引是不寻常的，但并非闻所未闻 . 您的方案的问题实际上与您假设的相反：您希望聚簇索引中的唯一值，而不是重复 . 由于聚簇索引确定行的物理顺序，因此如果索引位于非唯一列上，则服务器必须向具有重复键值的行添加背景值（在您的情况下，任何具有相同键的行） PersonId ）以便组合值（关键背景值）是唯一的 .

    我建议的唯一不是使用代理键（您的 CourtOrderId ）列作为主键，而是使用 PersonId 的复合主键和其他一些唯一标识列或列集 . 但是，如果这不可能（或不实用），则将聚集索引放在 CourtOrderId 上 .

    回复于
  • -2

    我绝不是SQL专家......所以把它作为开发人员的视图而不是DBA视图 .

    在不按顺序排列的集群（物理排序）索引上插入会导致插入/更新的额外工作 . 此外，如果您同时发生多次插入并且它们都发生在同一位置，则最终会出现争用 . 您的具体表现会因您的数据以及访问方式而异 . 一般的经验法则是在表中最独特的窄值上构建聚簇索引（通常是PK）

    我假设你的PersonId不会改变，所以更新不会在这里发挥作用 . 但考虑一下PersonId为1 2 3 3 4 5 6 7 8 8的几行快照

    现在为PersonId为3插入20个新行 . 首先，由于这不是唯一键，服务器会在您的值（幕后）中添加一些额外的字节以使其唯一（这也会增加额外的空间），然后是位置这些将驻留必须改变 . 将其与插入自动递增PK进行比较，其中插入发生在最后 . 非技术性解释可能归结为：有如果在 table 末端自然地进行更高的值而不是在插入项目时在该位置重新处理现有项目的位置，则需要做更少的“叶子改组”工作 .

    现在，如果您遇到插入问题，那么您可能会同时插入一堆相同（或类似）的PersonId值，这会导致整个表中各个位置的额外工作，并且碎片会导致您死亡 . 在你的情况下转换到PK聚集的缺点是，如果你今天在PersonIds上有插入问题，如果你将聚簇索引切换到PK并且所有插入现在都在一个由于争用集中度增加，您的问题实际上可能会变得更糟 . （另一方面，如果你今天的插页没有全部展开，但通常都是在类似的区域内聚集，那么你的问题可能会通过将聚集索引从PersonId切换到你的PK来缓解，因为你将最小化碎片 . ）

    应根据您的独特情况分析您的性能问题，并仅将这些类型的答案作为一般指导原则 . 您最好的选择是依靠DBA来准确验证问题所在 . 听起来你有资源争用问题可能超出简单的索引调整 . 这可能是一个更大问题的症状 . （可能的设计问题......否则资源有限 . ）

    无论如何，祝你好运！

    回复于
  • 13

    如果有一个替代方案可以使范围查询受益，一些作者建议不要 identity 在 identity 列上 .

    从MSDN Clustered Index Design Guidelines，应根据以下标准选择密钥

    • 可用于常用查询 .

    • 提供高度的独特性 .

    • 可用于范围查询 .

    您的 CourtOrderID 列符合 2 . 您的 PersonId 符合 1 和 3 . 因为大多数行最终会添加 uniqueifier ，你也可以将它声明为唯一并使用 PersonId,CourtOrderID ，因为这将是相同的宽度但更有用，因为聚集索引键被添加到所有NCI作为行定位器和此将允许他们覆盖更多的查询 .

    使用 PersonId,CourtOrderID 作为CI的主要问题是可能会发生逻辑碎片（这尤其会影响您尝试提供帮助的范围查询），因此您需要更频繁地监视填充因子和碎片级别并执行索引维护 .

    回复于
  • 5

    它在以下链接中解释：https://msdn.microsoft.com/en-us/ms190457.aspx

    Clustered

    • 聚簇索引根据键值对表或视图中的数据行进行排序和存储 . 这些是索引定义中包含的列 . 每个表只能有一个聚簇索引，因为数据行本身只能按一个顺序排序 .

    • 表中的数据行以排序顺序存储的唯一时间是表包含聚簇索引 . 当表具有聚簇索引时，该表称为聚簇表 . 如果表没有聚簇索引，则其数据行存储在称为堆的无序结构中 .

    Nonclustered

    • 非聚簇索引具有与数据行分开的结构 . 非聚簇索引包含非聚簇索引键值，每个键值条目都有一个指向包含键值的数据行的指针 .

    • 从非聚簇索引中的索引行到数据行的指针称为行定位符 . 行定位器的结构取决于数据页是存储在堆还是聚簇表中 . 对于堆，行定位器是指向该行的指针 . 对于集群表，行定位器是聚簇索引键 .

    • 您可以将非键列添加到非聚簇索引的叶级别，以绕过现有索引键限制，900字节和16个键列，并执行完全覆盖的索引查询 .

    回复于
  • 114

    一些带有一些令人讨厌的选择的db，在存储过程中连接 - 只有diffrence才是索引

    INDEXES - 聚簇与非聚簇

    891 rows
  10 sec
  NONCLUSTERED 

  OR

  891 rows
  14 sec
  CLUSTERED
    回复于

相关问题