插入时的索引性能

2018-07-04 08:36:25

我有一张有4列的表，我在这张桌上有3个索引：

   CREATE TABLE [dbo].CustomerInfo(
        ID [int] IDENTITY(1,1) NOT NULL,
        UserHashID [varchar](20) NOT NULL,
        ShippingID [int] NOT NULL,
        Received [bit] NOT NULL,
     CONSTRAINT [PK_ID] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_CustomerInfo_ShippingID] ON [dbo].[CustomerInfo]  (  [ShippingID] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_CustomerInfo_UserHashID] ON [dbo].[CustomerInfo]  (  [UserHashID] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_CustomerInfo_UserHashIDShippingID] ON [dbo].[CustomerInfo]  (    [UserHashID] ASC,   [ShippingID] ASC )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

我插入了大约4-5百万条记录，这个过程大约需要45分钟。我意识到，如果我放弃索引，插入方式会更快（2-3分钟）。

想知道是否有任何副作用，通过删除索引，在插入完成后进行插入和重建索引。如果启用索引，整个过程需要5分钟，而45分钟。

不，不存在删除索引，在插入完成后执行插入和重新构建索引的副作用（假设执行插入时没有其他任何需要访问表）。

这是一个相当普遍的模式。

[这一切都说，我惊讶于4列3指数的桌子上的时差。你能发表你的模式和索引定义吗？

正如@ PJ8912指出的那样，根据您备份事务日志的频率，事务日志记录可能会有所不同。

更新：无关，但是这个索引

CREATE NONCLUSTERED INDEX [IDX_CustomerInfo_UserHashID] 
    ON [dbo].[CustomerInfo]  ([UserHashID] ASC)

是多余的，因为它被这个索引所涵盖：

CREATE NONCLUSTERED INDEX [IDX_CustomerInfo_UserHashIDShippingID] 
    ON [dbo].[CustomerInfo]  ([UserHashID] ASC, [ShippingID] ASC)

根据事务记录的级别，TLogs可能会填满定期重新创建索引。如果您截断索引以消除它们，那么该操作将不会被记录。

创建新索引后，执行计划的统计信息可能不是最新的。您可能想要使用FULL SCAN模式更新统计数据。

链接地址: http://www.djcxy.com/p/95681.html

上一篇: Index performance while doing insert

下一篇: Is it ok to reorganize indexes with > 90% of fragmentation?