代码之家  ›  专栏  ›  技术社区  ›  cbp

我应该去掉guid列上的聚集索引吗

  •  30
  • cbp  · 技术社区  · 16 年前

    我正在处理一个通常使用guid作为主键的数据库。

    默认情况下,SQL Server会在主键列上放置聚集索引。我知道对于guid列来说这是一个愚蠢的想法,而且非聚集索引更好。

    你认为呢?我应该去掉所有的聚集索引,用非聚集索引替换它们吗?

    为什么SQL的性能调优器不推荐这样做?

    10 回复  |  直到 12 年前
        1
  •  27
  •   Mike Woodhouse    16 年前

    聚集索引的一个重要原因是,您经常希望为给定列的一系列值检索行。因为数据是按这种顺序物理排列的,所以可以非常有效地提取行。

    像guid这样的东西,虽然对于主键非常好,但可能会对性能造成积极的损害,因为插入会有额外的成本,而在选择上没有明显的好处。

    所以是的,不要在guid上集群索引。

    至于为什么不提供它作为建议,我建议调谐器知道这个事实。

        2
  •  24
  •   Graeme    12 年前

    几乎可以肯定,您希望在数据库中的每个表上建立一个聚集索引。 如果一个表没有聚集索引,它就是所谓的“堆”,大多数常见查询的性能都是 less for a heap than for a clustered index table .

    聚集索引应建立在哪些字段上取决于表本身以及针对表的查询的预期使用模式。在几乎所有情况下,您可能都希望聚集索引位于一列或唯一列的组合上,即(备用键),因为如果不是,SQL将在您选择的任何字段的末尾添加一个唯一的值。如果您的表中有一个或多个列,查询将经常使用该列来选择或筛选多个记录(例如,如果您的表包含销售交易记录,并且您的应用程序将经常按产品ID请求销售交易记录,甚至更好地说,是发票详细信息表,在这种情况下,您将检索所有特定发票或发票表的详细记录,您经常在其中检索特定客户的所有发票…无论您是通过单个值还是通过一系列值来选择大量记录,这都是正确的)

    这些列是聚集索引的候选列。聚集索引中列的顺序至关重要。索引中定义的第一列应该是将在预期查询的第一列上选择或筛选的列。

    所有这些的原因都是基于理解数据库索引的内部结构。这些索引称为平衡树(B树)索引。它们有点像二叉树,只是树中的每个节点可以有任意数量的条目(和子节点),而不是只有两个。使聚集索引不同的是,聚集索引中的叶节点是表本身的实际物理磁盘数据页。而非聚集索引的叶节点只“指向”表的数据页。

    因此,当一个表有一个clutered索引时,表数据页就是该索引的叶级,并且每个页都有一个指向上一页和下一页的指针(它们形成了一个双重链接的列表)。

    因此,如果查询请求的行范围与聚集索引的顺序相同…处理器只需遍历索引一次(或可能两次),就可以找到数据的起始页,然后跟随链接列表指针到达下一页和下一页,直到它读取了所需的所有数据页。

    对于非聚集索引,它检索的每一行都必须遍历索引一次…

    注:编辑
    要解决guid键列的顺序问题,请注意sql2K5具有newSequentialID(),它实际上以“旧”顺序方式生成guid。

    或者,您可以研究在客户端代码中实现的Jimmy Nielsens Comb-guid算法:

    COMB Guids

        3
  •  5
  •   Galwegian    16 年前

    guid字段中的聚集索引的问题是guid是随机的,因此当插入新记录时,必须移动磁盘上的大部分数据才能将记录插入到表的中间。

    但是,对于基于整数的聚集索引,整数通常是连续的(就像 IDENTITY 规范),所以它们只是添加到末尾,不需要移动任何数据。

    另一方面,聚集索引在guid上并不总是糟糕的…这完全取决于应用程序的需要。如果你需要 SELECT 快速记录,然后使用聚集索引…这个 INSERT 速度会受到影响,但是 选择 速度会提高。

        4
  •  5
  •   HTTP 410    16 年前

    虽然在一个guid上集群通常是一个坏主意,但是要注意guid在某些情况下可以 cause fragmentation even in non-clustered indexes .

    请注意,如果您使用的是SQL Server 2005, newsequentialid() 函数产生 相继的 GUIDs。这有助于防止碎片问题。

    我建议在做出任何决定之前使用如下的SQL查询来测量碎片(请不要使用非ANSI语法):

    SELECT OBJECT_NAME (ips.[object_id]) AS 'Object Name',
           si.name AS 'Index Name',
           ROUND (ips.avg_fragmentation_in_percent, 2) AS 'Fragmentation',
           ips.page_count AS 'Pages',
           ROUND (ips.avg_page_space_used_in_percent, 2) AS 'Page Density'
    FROM sys.dm_db_index_physical_stats 
         (DB_ID ('MyDatabase'), NULL, NULL, NULL, 'DETAILED') ips
    CROSS APPLY sys.indexes si
    WHERE si.object_id = ips.object_id
    AND   si.index_id = ips.index_id
    AND   ips.index_level = 0;
    
        5
  •  4
  •   Greg Dean    16 年前

    如果使用newID(),则可以切换到newSequentialID()。这将有助于插入性能。

        6
  •  2
  •   Corey Trager    16 年前

    是的,对随机值使用聚集索引是没有意义的。

    您可能希望在数据库中的某个位置使用聚集索引。例如,如果您有一个“author”表和一个“book”表,该表的外键为“author”,并且如果您的应用程序中有一个查询显示“select…从authorid=的书中,您将阅读一组书。如果这些书在磁盘上物理上相邻,那么速度会更快,这样磁盘头就不必从一个扇区跳到另一个扇区,收集该作者的所有书籍。

    因此,您需要考虑应用程序,以及它查询数据库的方式。

    进行更改。

    然后测试,因为你永远不知道…

        7
  •  1
  •   John    16 年前
        8
  •  0
  •   alexmac    16 年前

    是的,您应该删除guid主键上的聚集索引,原因是galwegian状态如上。我们已经在应用程序中完成了这项工作。

        9
  •  0
  •   GeekyMonkey    16 年前

    这取决于您是在做大量的插入,还是需要通过pk快速查找。

        10
  •  0
  •   Daeron Lockett    12 年前

    正如大多数人提到的,避免在聚集索引中使用随机标识符,您将无法获得聚集的好处。实际上,你会经历更大的延迟。除掉所有这些都是可靠的建议。还要记住,在多主机复制场景中,newSequentialID()可能是非常有问题的。如果数据库A和B在复制之前都调用newSequentialID(),则会发生冲突。