用于存储图像的Server表/索引

Question

​

我在我们的数据库中存储图像时遇到了一些问题。我们目前有大约40万的记录，但我预计这将很快增加到数百万。目前，我已经遇到了性能问题，因此这是一个主要的关切。在决定使用SQL Server存储图像之前，我做了一些研究，我所读到的一切都表明它能够做到这一点。

我设计的表格非常简单，包含3列.

• Id (主键、唯一标识符、null)
• ImageHash (唯一标识符，null)
• BinaryImage (var二元(最大)，非空)

​

逻辑是在我的应用程序代码中生成ImageHash。图像哈希用于在插入之前进行查找，以查看数据库中是否已经存在二进制图像。剩下的时间，我只是直接使用Id查询表。

我正在使用.NET实体框架来执行数据访问。在insert上生成Id列，不确定这是否是最佳实践。

​

这是“我的表”的创建脚本。我为ImageHash创建了一个索引，但是我对Server索引并没有很好的理解。

CREATE TABLE [dbo].[ImageContent](
    [Id] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ImageHash] [uniqueidentifier] NOT NULL,
    [BinaryImage] [varbinary](max) NOT NULL,
 CONSTRAINT [PK_ImageData] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ALTER TABLE [dbo].[ImageContent] ADD  CONSTRAINT [DF_ImageData_Id]  DEFAULT (newid()) FOR [Id]

​

和索引..。

CREATE NONCLUSTERED INDEX [ImageHash_Index] ON [dbo].[ImageContent]
(
    [ImageHash] ASC
)
INCLUDE (   [Id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

​

我已经重建了所有的索引，但这并没有解决这个问题。我一直在玩，我发现是SQL Insert导致了这个问题(由实体框架生成)。下面是带有二进制的语句，但是我用.这是在30秒后超时..。

exec sp_executesql N'declare @generated_keys table([Id] uniqueidentifier)
insert [dbo].[ImageContent]([ImageHash], [BinaryImage])
output inserted.[Id] into @generated_keys
values (@0, @1)
select t.[Id]
from @generated_keys as g join [dbo].[ImageContent] as t on g.[Id] = t.[Id]
where @@ROWCOUNT > 0',N'@0 uniqueidentifier,@1 varbinary(max) ',@0='DF76D1FF-5C05-58E0-0933-1ADBCC6345A8',@1=0xFFD8FFE1214545786966000049492A00080000000D00000103...

所以我的问题是..。

• 有人能从我的安排方式中看出一些重大问题吗？
• 你有什么建议可以帮我提高业绩吗？
• Server是否能够以这种方式存储数百万图像？

提前谢谢你的时间！

Answer 1

因为您的索引(包括主键上的聚集索引)是在unique标识符上的，所以这些索引会非常快地被分割。

1. 考虑以单调递增的INT/BIGINT标识作为您的Id，除非您有充分的理由这样做。
2. 调整NCI (ImageHash_Index)上的填充因子，并确保您有一项任务可以定期重新组织/重建它
3. 如果实际映像大于2MB，请考虑使用FILESTREAM存储它们。这里有一份白皮书：http://msdn.microsoft.com/library/hh461480。如果您选择这条路线，另外两个性能考虑因素。有关他们的信息，在这里：http://msdn.microsoft.com/en-us/library/ee377058(v=bts.10).aspx。

禁用短文件名( 8.3 )生成当使用Windows文件系统创建长文件名时，默认行为是在旧的8.3 DOS文件名约定中生成相应的短文件名，以便与旧操作系统兼容。可以通过注册表项禁用此功能，从而提高性能。

disable8dot3 1行为集

禁用NTFS最后一次访问，更新NTFS卷上的每个文件和文件夹，包括一个名为“最后访问时间”的属性。此属性显示上次访问文件或文件夹的时间，例如用户执行文件夹列表、向文件夹添加文件、读取文件或更改文件的时间。维护这些信息会给文件系统带来性能开销，特别是在快速和短时间内访问大量文件和目录的环境中，例如在使用BizTalk文件适配器时。除了在高度安全的环境中，保留这些信息可能会给服务器增加负担，可以通过更新以下注册表项来避免这种负担：

行为集失效弹性存取1

Answer 2

你将需要做尽职调查，并收集至少一些最低限度的信息，因为这是谁的猜测问题是什么。您需要做的第一件事是阅读等待和排队，使自己熟悉解决Server性能问题的适当调查技术，并应用该方法收集相关信息。

这是我的观点，它没有任何证据。您的插入可能是阻塞的，我们不知道原因。使用活动监视器来理解什么阻塞您的插入。这并不是由碎片(永恒的红鲱鱼)造成的。如果我敢猜的话，罪魁祸首是使用默认范围--新的System.Transactions和这会带来可怕的序列化隔离。

顺便提一句：UNIQUEIDENTIFIER是一个糟糕的哈希数据类型选择。要生成与图像相关的散列，必须运行哈希算法，如MD5或SHA。您可能正在使用MD5并生成一个16字节键，但这绝对没有理由将其存储在一个16字节长的UNIQEUIDENTIFIER类型中。使用BINARY(16)。另外，如果您想在将来迁移到SHA1 (20字节散列)或其他散列，也可以使用condsider。