sql - 聚集索引问题

Question

在我们的生产系统 (SQL Server 2008/R2) 中有一个表，其中存储了生成的文档。

这些文档有一个引用 ( varchar ) 和一个 sequence_nr ( int )。文档可能会生成多次，每次迭代都会保存在这个表中，增加序列号。此外，每条记录都有一个数据列( varbinary )和一个时间戳以及一个用户标签。

查询此表的唯一原因是为了稍后和插入期间的审计目的。

表的主键聚集在 reference 上和 sequence_nr列。

因为您可能会猜测文档的生成，因此表中的数据(因为稍后可以再次生成文档)不会按顺序增长。

在表中的插入开始超时后，我意识到了这一点。

插入是通过存储过程执行的。存储过程确定当前最大sequence_nr对于给定的引用并插入带有下一个 sequence_nr 的新行.

我相当确定聚簇索引的错误选择会导致超时问题，因为将为已经存在的引用插入记录，只有不同的 sequence_nr 并且因此可能会在记录集合中的任何位置结束，但很可能不会在最后。

关于我的问题:将非聚集索引作为主键会更好还是引入标识列更好，使其成为聚集主键并保留引用和 sequence_nr 组合的索引?

知道暂时(并且在我们可以预见的范围内完全没有)没有必要密集查询此表，除非出现新 sequence_nr 的情况。必须确定。

编辑回答问题:
Tbh，我不确定生产环境中的超时。我确实知道在并行运行的进程中添加了新文档。

table :

CREATE TABLE [dbo].[tbl_document] (
    [reference]     VARCHAR(50)    NOT NULL,
    [sequence_nr]   INT            NOT NULL,
    [creation_date] DATETIME2      NOT NULL,
    [creation_user] NVARCHAR (50)  NOT NULL,
    [document_data] VARBINARY(MAX) NOT NULL
);

主键:

ALTER TABLE [dbo].[tbl_document]
    ADD CONSTRAINT [PK_tbl_document] PRIMARY KEY CLUSTERED ([reference] ASC, [sequence_nr] ASC) 
    WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);

存储过程:

CREATE PROCEDURE [dbo].[usp_save_document] @reference     NVARCHAR (50),
                                           @sequence_nr   INT OUTPUT,
                                           @creation_date DATETIME2,
                                           @creation_user NVARCHAR(50),
                                           @document_data VARBINARY(max)
AS
  BEGIN
      SET NOCOUNT ON;

      DECLARE @current_sequence_nr INT

      SELECT @current_sequence_nr = max(sequence_nr)
      FROM   [dbo].[tbl_document]
      WHERE  [reference] = @reference

      IF @current_sequence_nr IS NULL
        BEGIN
            SELECT @sequence_nr = 1
        END
      ELSE
        BEGIN
            SELECT @sequence_nr = @current_sequence_nr + 1
        END

      INSERT INTO [dbo].[tbl_document]
                  ([reference],
                   [sequence_nr],
                   [creation_date],
                   [creation_user],
                   [document_data])
      VALUES      (@reference,
                   @sequence_nr,
                   @creation_date,
                   @creation_user,
                   @document_data)
  END 

希望有帮助。

Answer 1

由于聚集索引在物理上对表的记录进行重新排序以匹配索引顺序，因此仅当您想按该顺序读出多个连续记录时才有用，因为这样可以通过在磁盘上进行顺序读取来读取整个记录。

如果您只使用存在于索引中的数据，那么将其聚集起来没有任何好处，因为索引本身(是否聚集)与数据分开并按顺序保持。

因此，对于您的特定情况，非聚集索引是正确的方法。插入不需要重新排序数据(只有索引)并找到新的 sequence_nr可以通过单独查看索引来实现。