SQL Server插入速度缓慢- SqlBulkCopy性能

Question

我有一个拥有三千万行的数据库。主键聚集索引是由代码生成的GUID。

表格如下：

CREATE TABLE [dbo].[events](
    [imageEventGUID] [uniqueidentifier] NOT NULL,
    [imageSHAID] [nvarchar](256) NOT NULL,
    [queryGUID] [uniqueidentifier] NOT NULL,
    [eventType] [int] NOT NULL,
    [eventValue] [nvarchar](2050) NULL,
    [dateOfEvent] [datetime] NOT NULL,
 CONSTRAINT [PK_store_image_event] PRIMARY KEY CLUSTERED 
(
    [imageEventGUID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

简单来说，这是一个图像搜索引擎。

• imageEventGUID 是唯一标识符代码，
• imageSHAID 是图像 URL 的 SHA256 值
• queryGUID 是生成的外键代码（为简洁起见，在创建语句中排除）
• eventType 是分配给事件类型的数字
• eventValue 通常是图像的 URI，例如 "http://mywebpage.com/images/image123456789.jpg"

定期使用相当标准的代码通过 SqlBulkCopy（从 DataTable）将数据插入此表中：

using (SqlBulkCopy bulk = new SqlBulkCopy(storeConn, SqlBulkCopyOptions.KeepIdentity | SqlBulkCopyOptions.KeepNulls, null))
{
    bulk.DestinationTableName = "[dbo].[events]";
    bulk.WriteToServer(myeventsDataTable);
}

我通常试图一次性批量插入5k到10k行数据。但是，从这个大容量复制中，我的插入效果很差。我曾经在SSD上运行此数据库(只连接SATA 1)，非常快速(低于500毫秒)。由于SSD空间不足，所以我将DB换成了1TB 7200缓存旋转磁盘，自那时起，完成时间超过了120秒(120000毫秒)。当批量插入正在运行时，我可以看到大约1MB/秒的磁盘活动量，CPU使用率较低。

除PK外，此表上没有其他索引。

以下是我的问题：

您是否能看出我做错了什么导致这种情况？

只是因为“您的旋转磁盘不够快，无法满足这个DB的大小”吗？

在插入此数据时，确切发生了什么？因为它是聚集索引，所以在进行插入时，它是否重新排列磁盘上的数据页？它正试图插入GUIDS，因其本质上是无序的，因此可能会导致“随机插入性质”的读/写头在磁盘上移动到不同的页面？

感谢您的时间。

- user989056

---

你使用了 SqlBulkCopyOptions.KeepIdentity，但是你没有一个自增列，为什么？另外，是否有其他东西与同一张表进行通信（读或写）？这可能会导致锁争用。 - Scott Chamberlain

在数据库中，将GUID作为聚集主键本身就是一个非常糟糕的设计选择。请参考Kim Tripp的博客文章GUIDs as PRIMARY KEYs and/or the clustering key了解详细说明。 - marc_s

@ Scott，这是从我之前在代码中使用的另一个SqlBulkCopy复制和粘贴出现的问题。这是个坏习惯，可以忽略Keep Identity。 - user989056

@marc_s 我知道这一点，但不幸的是我的系统/应用程序确实需要从代码中动态生成PK值。它无法查询数据库为每个记录生成ID或依靠数据库使用identity来管理自己的PK。这是由于性能问题（具有讽刺意味）。如果需要，我可以进一步扩展此内容。 - user989056

你可以将你的GUID列作为（非聚集）主键，并引入一个新的“ INT IDENTITY”列作为聚集键。这样做已经可以帮助很多了，我相信！ - marc_s

Answer 1

我猜测主要问题在于你选择的聚集索引。 聚集索引决定了表中记录的物理顺序。 由于你的主键是GUID（我假设GUID是随机生成而不是顺序生成的），数据库必须将每个行插入到适当的位置，这可能位于两个现有记录之间，可能导致页面拆分、碎片化等问题。

至于为什么在SSD上比磁盘驱动器快，我不是专家，但很可能是由于其如何组织数据而使碎片化过程更快。 I/O吞吐量将更快，但不会大幅提高。

如果您可以使用数值自增的主键而不是GUID，则批量插入应该会快得多。 您仍然可以在GUID列上创建唯一索引以加快查询速度。

- D Stanley

---

此外 - 特别是如果您正在寻找更快的解决方案，可以考虑在此期间使用顺序 GUID，尽管这仍然可能需要主键更新，这取决于您的系统大小而并非简单。 - Sethcran

顺序 GUID 不是一个选项。@D.Stanley，目前正在测试这个自增的聚集索引和非 CI 的 GUID。我会让你知道的，谢谢。 - user989056

OP也可以增加聚集索引的填充因子以预测未来的分裂（尽管这并没有解决根本原因；它只是试图缓解症状）。此外，以下也是不错的阅读材料：https://dev59.com/iUnSa4cB1Zd3GeqPOX6S - tommy_o

我尝试了你和@marc_s建议的方法，我将其标记为答案，因为marc_s没有提交答案，非常感谢你们的帮助。另外，我有一个相关的问题，也许你会对同一项目的MERGE性能问题感兴趣：https://dev59.com/73XYa4cB1Zd3GeqP6nv6 再次感谢。 - user989056

Answer 2

尝试在imageEventGUID列上使用带有newsequentialid()的默认约束。

这将按正确顺序插入GUID，因此SQL Server无需在每次插入时重新排列表。

- Diego

---

只有在应用程序未为该列提供值时（似乎是这样），此方法才有效。如果应用程序已经向“INSERT”命令提供了一个值，则默认约束将不会被使用... - marc_s

你说得对，这只是一个“测试”解决方案，旨在尝试确定问题所在。如果他得出结论确实是这个问题，他可以考虑更改创建GUID的逻辑；否则，他可以在其他地方寻找答案。 - Diego

Answer 3

GUID 作为聚集主键本身是一个非常糟糕的设计选择 - 可以参考 Kim Tripp 的博客文章 GUIDs as PRIMARY KEYs and/or the clustering key 进行解释。使用随机（客户端生成的）GUID 将导致非常高的（通常为99%或更多）碎片化，并在批量插入大量行的过程中，会导致大量的页面分裂，这些操作非常昂贵。

如果您不能改变这个 - 您可以至少确保具有可怕的碎片化值的聚集索引每晚都被重建 - 或者更频繁地，如果您能够负担得起。

您还可以将 GUID 列保留为（非聚集）主键，并引入一个新的 INT IDENTITY 列用作聚集键。仅仅这样就已经可以帮助很多了，我相信，通过消除非常随意的 GUID 对聚集索引造成的极度碎片化。

- marc_s

Answer 4

您可以禁用其他索引，但不能禁用聚集 PK。
如果禁用了聚集 PK，则会禁用表格。
如果数据未按照 PK 的顺序加载，则会出现快速索引碎片化。
随着碎片化的增加，插入速度会降低。

请注意，您无法控制 GUID。

但是有几个选项。

在 [PK_store_image_event] 上使用填充因子，例如 50、20 或 10
这会留出空间进行插入，但会增加磁盘上的索引大小
定期重建索引-最少每晚一次。

您能否在加载之前对数据进行排序？
如果可以，请按 PK 排序加载。
如果您的数据在 DataTable 中，则可以对其进行排序。
您将无法使用现有的加载代码，但可以对其进行排序。
TVP 是一个选项。

在 PK 上使用 iden 并在 [imageEventGUID] 上使用唯一索引。
如果它具有唯一索引，则可以成为 FK。
禁用该索引，然后加载，再重建。
如果有重复项，则重建将失败。

或者作为以上方法的变体，只需跳过 iden PK。

- paparazzo