logo标签列表

聚集索引和非聚集索引的区别

sql-serverdatabase-designindexingclustered-indexnon-clustered-index
132

我需要为我的表格添加适当的索引(index)，需要一些帮助。

我有些困惑，需要澄清几个问题：

  • 我应该为非整数(non-int)列使用索引吗？为什么/为什么不？

  • 我读了很多关于聚集(clustered)和非聚集(non-clustered)索引的文章，但我仍然无法决定何时使用其中之一。一个好的例子会帮助我和其他开发人员。

我知道我不应该为经常更新的列或表使用索引。我还应该注意什么？如何确保在进入测试阶段之前一切都很好？

26
项目的开始阶段绝对不是“匆忙”的最佳时机。现在你正在为数据库打下基础，任何建立在不良基础之上的东西都会在某种程度上失败。现在请花些时间，以后再加快速度；-) - iDevlop
6个回答
280

聚集索引(Clustered index)会改变行的存储方式。当您在某列(或多列)上创建聚集索引时，SQL服务器将按照该列(列集)对表中的行进行排序。它就像一个字典，其中所有单词按字母顺序排列在整本书中。

另一方面，非聚集索引(Non-clustered index)不会改变表中行的存储方式。它在表内创建一个完全不同的对象，该对象包含选择进行索引的列和指向包含数据的表中行的指针。这就像一个书的最后几页上的索引，其中关键字被排序并包含指向书的材料所在页码的链接，以便更快地查找参考内容。

83

您需要将两个问题区分开：

1）主键是逻辑构造 - 候选键之一，可以唯一并可靠地标识表中的每一行。这可以是任何东西 - 整数、GUID、字符串 - 选择最符合您情况的。

2）聚集键（定义表上“聚集索引”的列或列）- 这是一个与物理存储相关的内容，这里，小型、稳定、递增的数据类型是最佳选择 - INT或BIGINT作为默认选项。

默认情况下，SQL Server表上的主键也用作聚集键 - 但不必如此！

我会应用一个经验法则：任何“常规”表（用于存储数据的表，即查找表等）都应该有一个聚集键。真的没有不使用聚集键的意义。事实上，与常见观点相反，具有聚集键实际上可以加速所有常见操作 - 即使是插入和删除操作（因为表的组织方式不同，通常比使用堆的表更好 - 没有聚集键的表）。

索引女王Kimberly Tripp在聚集键的重要性以及如何最好地使用聚集键方面有很多优秀的文章。由于每个表只能有一个，因此选择正确的聚集键非常重要，而不仅仅是任何聚集键。

  • 聚集索引之争持续升温
  • 聚集键的不断增加 - 聚集索引之争再次掀起！
  • 硬盘空间很便宜 - 这并不是关键！

    • Marc

    2
    非主键的标识列是如何排序的？它是否聚集？ - Dhwani
    2
    @ProgrammerIT：不是 - IDENTITY列只是一个系统生成的值，没有其他作用。 - marc_s
    26

    您应该使用索引来帮助SQL服务器性能。通常，这意味着用于查找表中行的列被索引。

    聚集索引使SQL服务器根据索引顺序对磁盘上的行进行排序。这意味着如果您按照聚集索引的顺序访问数据，则数据将按正确的顺序存在磁盘上。但是，如果具有聚集索引的列经常更改，则行将在磁盘上移动，导致开销 - 通常不是一个好主意。

    拥有许多索引也不好。它们需要成本来维护。因此，从明显的开始，然后进行分析，以查看您错过了哪些索引，并且会从中受益。您不需要从一开始就拥有它们，可以稍后添加。

    大多数列数据类型都可以用于索引，但最好对小列进行索引而不是大列。此外，通常会在组合列上创建索引（例如国家+城市+街道）。

    此外，在表中有相当数量的数据之前，您不会注意到性能问题。还要考虑的另一件事是，SQL服务器需要统计信息以正确执行其查询优化，因此请确保生成它们。

    4
    另外还有两点需要注意。首先，在向表中添加主键时，默认情况下它是聚集索引。其次，每个表只能有一个聚集索引。 - richard
    21

    非聚集索引与聚集索引的比较及示例

    假设我们在EmployeeID列上有一个非聚集索引作为示例，非聚集索引将存储值为

    EmployeeID

    的数据以及指向实际存储该值行的指针。但是，聚集索引实际上将存储特定EmployeeID对应的行数据 - 因此，如果您运行搜索EmployeeID为15的查询，则聚集索引中的叶节点本身将存储表中其他列（例如

    EmployeeName、EmployeeAddress等

    ）的数据。

    这意味着使用非聚集索引需要额外的工作来跟随指针到达表中的行并检索任何其他所需的值，而聚集索引可以直接访问行，因为它按照聚集索引本身的顺序进行存储。所以，从聚集索引读取通常比从非聚集索引读取快。

    1
    一个不错的解释，除了最后一句话。从聚集索引中读取通常不比从非聚集索引中读取更快，因为聚集索引通常比同一表上的任何非聚集索引都要大得多，而且永远不会更小。这就是为什么查询优化器通常会在可以选择时优先选择非聚集索引而不是聚集索引 - 特别是对于“覆盖”查询，其中不需要书签查找。 - nvogel
    这是否意味着当我们有一个使用情况，其中数据经常更新时，SQL 需要每次更新数据时更新索引？并且在执行更新时，它会减慢 SQL 的速度吗？（当然，我是在谈论大型数据集） - Krunal
    4

    一般来说，在将要（频繁）搜索表的列上使用索引，例如主键（默认情况下具有聚集索引）。例如，如果您有以下查询（伪代码）：

    SELECT * FROM FOO WHERE FOO.BAR = 2

    您可能需要在FOO.BAR上放置一个索引。应该在用于排序的列上使用聚簇索引。聚簇索引用于对磁盘上的行进行排序，因此每个表只能有一个聚簇索引。例如，如果您有以下查询：

    SELECT * FROM FOO ORDER BY FOO.BAR ASCENDING

    您可能需要在FOO.BAR上考虑创建聚集索引。
    最重要的考虑因素可能是查询所需的时间。如果查询不需要太多时间或者没有经常使用，那么添加索引可能并不值得。一如既往，先进行性能分析，然后再进行优化。SQL Server Studio可以为您提供优化建议，MSDN也提供了一些信息1，您可能会发现它们很有用。
    我很困惑，PK不是聚集索引吗？而且我知道一张表只能有一个。这与你的回答不符。 - Pabuc
    4
    默认情况下，PK是聚集索引，但并非必须如此。您可以将PK设置为单列、非聚集索引，然后使用另一列作为聚集索引。 - marc_s
    @marc_s 感谢您澄清这一点。我已更新答案以反映这一点。 - Brandon Bohrer
    2

    聚簇索引比非聚簇索引更快，因为数据在物理上按索引顺序存储。每个表只能创建一个聚簇索引。

    非聚簇索引比聚簇索引更适合插入和更新操作。我们可以创建任意数量的非聚簇索引。

    网页内容由stack overflow 提供, 点击上面的
    可以查看英文原文，
    原文链接