SQL Server：合并性能

Question

我有一个500万行的数据库表。聚簇索引是“自动增量标识”列。PK是代码生成的256个字节VARCHAR，它是URL的SHA256哈希，这是表上的非聚集索引。

下表如下：

CREATE TABLE [dbo].[store_image](
    [imageSHAID] [nvarchar](256) NOT NULL,
    [imageGUID] [uniqueidentifier] NOT NULL,
    [imageURL] [nvarchar](2000) NOT NULL,
    [showCount] [bigint] NOT NULL,
    [imageURLIndex]  AS (CONVERT([nvarchar](450),[imageURL],(0))),
    [autoIncID] [bigint] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_imageSHAID] PRIMARY KEY NONCLUSTERED 
(
    [imageSHAID] 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

CREATE CLUSTERED INDEX [autoIncPK] ON [dbo].[store_image] 
(
    [autoIncID] 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]
GO

• imageSHAID是图像URL的SHA256哈希，例如“ http://blah.com/image1.jpg ”，它哈希为256个长度的varchar。

• imageGUID 是由代码生成的guid，可在其中标识图像（以后将用作索引，但现在我已将此列省略为索引）

• imageURL 是图片的完整网址（最多2000个字符）

• showCount 是显示图像的次数，每次显示此特定图像时，该值都会增加。

• imageURLIndex 是由450个字符限制的计算列，这使我可以在应选择的imageURL上进行文本搜索，它是可索引的（为简洁起见，同样省略了索引）

• autoIncID 是聚集索引，应允许更快地插入数据。

我会定期从临时表合并到store_image表中。临时表的结构如下（非常类似于store_image表）：

CREATE TABLE [dbo].[store_image_temp](
    [imageSHAID] [nvarchar](256) NULL,
    [imageURL] [nvarchar](2000) NULL,
    [showCount] [bigint] NULL,
) ON [PRIMARY]

GO

运行合并过程时，我DataTable使用以下代码将写入临时表：

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

我然后运行合并命令来更新showCount在store_image通过基于该临时表合并表格imageSHAID。如果store_image表中当前不存在该图像，则创建它：

merge into store_image as Target using [dbo].[store_image_temp] as Source
on Target.imageSHAID=Source.imageSHAID 
when matched then update set 
Target.showCount=Target.showCount+Source.showCount 
when not matched then insert values (Source.imageSHAID,NEWID(), Source.imageURL, Source.showCount);

我通常试图store_image在任何一个合并过程中将2k-5k行从temp表合并到表中。

我曾经在SSD（仅连接SATA 1）上运行此数据库，并且运行速度非常快（不到200毫秒）。我的SSD空间不足，因此将数据库交换到1TB
7200高速缓存旋转磁盘上，因为此后完成时间超过6-100秒（6000-100000MS）。运行批量插入时，我可以看到磁盘活动约为1MB-2MB
/秒，CPU使用率较低。

这是此数据量的典型写入时间吗？对我来说似乎有点慢，是什么原因导致性能下降？当然，有了imageSHAID索引，我们应该期望比这更快的查找时间？

任何帮助，将不胜感激。

谢谢你的时间。

Answer 1

您的UPDATE条款在MERGE更新中showCount。这需要在聚簇索引上进行键查找。

但是，聚簇索引也被声明为非唯一的。即使基础列是唯一的，这也将信息提供给优化器。

所以，我将进行这些更改

• html" target="_blank">集群主键为 autoIncID
• 当前的PKimageSHAID成为独立的唯一索引（不是约束），并为添加INCLUDE showCount。唯一约束不能包含INCLUDEs

更多观察结果：

• 您不需要nvarchar哈希或URL列。这些不是unicode。
• 哈希也是固定长度的，因此可以是固定长度的char(64)（对于SHA2-512）。
• 列的长度定义要分配给查询的内存量。看到更多信息：varchar（500）比varchar（8000）有优势吗？