我有一个包含 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
是图像的完整 URL(最多 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
in the 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);
我通常尝试将临时表中的 2k-5k 行合并到store_image
任何一个合并过程中的表。
我曾经在 SSD 上运行此数据库(仅连接 SATA 1),速度非常快(低于 200 毫秒)。我的 SSD 空间不足,因此我将 DB 更换为 1TB 7200 缓存旋转磁盘,此后完成时间超过 6-100 秒(6000 - 100000MS)。当批量插入运行时,我可以看到磁盘活动约为 1MB-2MB/秒,CPU 使用率较低。
这是这种数据量的典型写入时间吗?我觉得有点慢,是什么导致性能缓慢?肯定与imageSHAID
被索引后,我们应该期望比这更快的寻道时间?
任何帮助,将不胜感激。
谢谢你的时间。