sql-server - 如何重新索引 AuditLog 表？非聚簇主键、聚簇覆盖索引、GUID

Question

使用 SQL Server 2016 标准版。我有一个现有的 AuditLog 表，在 bigint 列(生成的 C# 端)上有一个 PK 和一个附加索引。

CREATE TABLE [dbo].[AuditLog]
(
    [Id] [bigint] NOT NULL,
    [ChangeTime] [datetime] NOT NULL,
    [User] [varchar](100) NOT NULL,
    [RootId] [bigint] NOT NULL,
    [EntityId] [bigint] NOT NULL,
    [EntityName] [varchar](100) NOT NULL,
    [Operation] [varchar](100) NOT NULL,
    [OldValue] [varchar](max) NULL,
    [NewValue] [varchar](max) NULL
)

ALTER TABLE [dbo].[AuditLog] 
    ADD CONSTRAINT [PK_AuditLog] 
        PRIMARY KEY CLUSTERED ([Id] ASC)

CREATE NONCLUSTERED INDEX [IX_AuditLog_RootId] 
    ON [dbo].[AuditLog] ([RootId] ASC)

对于当前的 105,000,000 行，大小为(使用 used_pa​​ge_count * 8K 每页):

• PK_AuditLog:11,535,112 KB
• IX_AuditLog_RootId:2,370,480 KB

我现在必须通过 SQL 中的存储过程在此表中创建行，而不仅仅是在 C# 中，因此我需要一个可以在 SQL 端(以及 C# 仍然)生成的主键。我认为我的选择是 int identity 和 guid(默认为 NEWSEQUENTIALID)。

由于我的大部分用法都包括日期和按日期排序，所以我正在考虑将其聚类。听起来不错吧？

因为我几乎总是按 RootId 和 User 进行过滤，所以我正在考虑将它们包含在我的索引中。将其他列包含在聚簇索引中是个好主意吗？或者它们应该在一个单独的覆盖索引中？

每个索引都需要唯一地标识行，因此即使我没有指定主键，我的聚簇索引也会包含它。因此，使用 Guid 作为 PK 似乎不是存储的好主意，尤其是在有 1 亿行的情况下。所以我正在使用 bigint。

由于我的 PK 不是集群的(因此没有按该​​顺序物理存储)，SQL Server 如何计算出下一个标识？我怀疑它对 PK 进行排序以找到最大值。在非聚簇列上使用标识是个坏主意吗？

此外，我想我可以使用精度为 3(存储 7 个字节)的 datetime2 而不是 datetime(8 个字节)来保持相同的精度但节省一点空间(或者甚至精度 4 以提高相同存储的精度)？

所以我正在考虑:

CREATE TABLE dbo.AuditLog
(
    Id bigint NOT NULL IDENTITY (1, 1),
    ChangeTime datetime2(4) NOT NULL...


ALTER TABLE AuditLog   
    ADD CONSTRAINT [PK_AuditLog] 
        PRIMARY KEY NONCLUSTERED (Id)

CREATE CLUSTERED INDEX CIX_AuditLog_ChangetimeRootUser 
    ON AuditLog(Changetime, RootId, [User])

脚注

表格是这样使用的:

• 此表没有外键。

• insert heavy(任何添加/编辑/删除用户实体字段都会插入新的 AuditLog 行，在工作时间内不断，必须快速)

• 偶尔读取(用户检查什么或谁更改了什么，即每天读取 AuditLog 数次，最好不要等待查询返回很长时间)

• AuditLog 行一旦插入就永远不会更新或删除。

典型的过滤器和顺序:

• 仅按日期过滤
• 按日期和用户过滤
• 按日期和 objectId 过滤
• 按日期、用户和 objectId 过滤
• 仅按 objectId 过滤
• 几乎总是按反向日期排序，以首先显示最近的更改。
• 经常与分页一起使用，使用“偏移 x 行”和“仅获取下 x 行”
• 和一个特定的用例，这相当于使用 where 子句选择 PK 的子集，然后使用 PK 在主表上自连接以检索列值

PS:流程和时间我都清楚，创建临时新表，分块复制数据，创建索引等等...

Answer 1

由于我的大部分用法都包含日期和按日期排序，所以我正在考虑使用它进行聚类。听起来不错？

如果不动手并评估结果，就无法知道。

将其他列包含在聚簇索引中是个好主意吗？

您不能在聚簇索引中包含列，因为它毫无意义。聚集索引最终是表。您在 NC 索引中包含列以避免额外查找以访问行的其他列。

SQL Server 如何计算出下一个标识？

坦率地说，不用担心。引擎在表级别管理身份——它不需要引用任何特定的行来确定下一个值。

此外，我想我可以使用精度为 3(存储 7 个字节)的 datetime2 而不是 datetime(8 个字节)来保持相同的精度但节省一点空间(甚至精度 4 以提高相同存储的精度无论如何)？

不要为了每行保存一个字节而妨碍您的数据。根据您的要求选择正确的数据类型。存储很便宜。缺乏精确性是永远存在的。

此外，您的脚注不清楚。您指的是用户实体字段的添加/更新/删除(对于那些不熟悉您的架构的人来说这是一个毫无意义的术语)以及“从未更新或删除”。这似乎是一个矛盾，可能相关也可能不相关。

还有一个最后的评论。变革涉及风险。如果您当前的模式足够了，那么最安全的方法就是使用您的 ID 列作为标识简单地重新创建您的表(其他一切都保持不变)。