为什么建议将BLOB存储在单独的SQL Server表中？

#1 楼

虽然我不同意BLOB应该只在另一个表中，但根本不应该在数据库中。将指针存储到文件在磁盘上的位置，然后从数据库中获取指针。

（对我而言）它们引起的主要问题是索引。将XML与查询计划一起使用，因为每个人都准备好了，让我们创建一个表：

SELECT TOP 1000
ID = IDENTITY(INT,1,1),
deq.query_plan
INTO dbo.index_test
FROM sys.dm_exec_cached_plans AS dec
CROSS APPLY sys.dm_exec_query_plan(dec.plan_handle) AS deq

ALTER TABLE dbo.index_test ADD CONSTRAINT pk_id PRIMARY KEY CLUSTERED (ID)

它只有1000行，但是要检查大小...

sp_BlitzIndex @DatabaseName = 'StackOverflow', @SchemaName = 'dbo', @TableName = 'index_test'

仅1000行就超过40 MB。假设每1000行添加40 MB，那么很快就会变得很丑陋。当您达到100万行时会发生什么？那里大约只有1 TB的数据。



现在任何需要使用聚簇索引的查询都需要将所有BLOB数据读入内存中以进行澄清：何时

与存储BLOB相比，您能想到使用SQL Server内存的更好方法吗？因为我肯定可以。

将其扩展为非聚集索引：

CREATE INDEX ix_noblob ON dbo.index_test (ID)

CREATE INDEX ix_returnoftheblob ON dbo.index_test (ID) INCLUDE (query_plan)

您可以设计非聚集索引以在很大程度上避免常规的BLOB列查询可以避免聚集索引，但是一旦需要该BLOB列，就需要聚集索引。

如果将它作为INCLUDED列添加到非聚集索引以避免关键查找情况，则会导致巨大的非聚集索引：

它们引起的更多问题：


如果有人运行SELECT *查询，他们将获得所有BLOB数据。
它们会占用备份和还原空间，从而减慢它们的速度
会减慢DBCC CHECKDB的速度，因为我知道您正在检查损坏，对吗？
如果执行任何索引维护，它们也会减慢速度。

希望这会有所帮助！

#2 楼

这些图像有多大，您期望有几张？尽管我大体上同意@sp_BlitzErik，但我认为在某些情况下可以执行此操作，因此有助于更清晰地了解此处实际需要的内容。

某些选项考虑减轻Erik指出的大多数负面影响是：



FILESTREAM（从SQL Server 2008开始）

FileTables（开始在SQL Server 2012中）

这两个选项都被设计为完全在SQL Server中存储BLOB或完全在外部存储BLOB之间的中间地带（保留用于保留路径的字符串colun除外）。它们允许BLOB成为数据模型的一部分并参与事务，而不会浪费缓冲池（即内存）中的空间。 BLOB数据仍包含在备份中，这确实使它们占用更多空间，并且备份和还原需要更长的时间。但是，我很难将其视为真正的否定项，因为如果它是应用程序的一部分，则需要以某种方式对其进行备份，并且仅将包含路径的字符串列完全断开并允许BLOB文件被获取在数据库中没有指示的情况下被删除（即无效的指针/丢失的文件）。它还允许在数据库内“删除”文件，但文件仍存在于文件系统中，最终需要清理（即头痛）。但是，如果文件很大，那么最好最好将其完全保留在SQL Server之外，除了path列。

这有助于解决“内部还是外部”问题，但不会涉及单表还是多表问题。我可以说，除了这个特定问题之外，当然还有一些有效的情况，可以根据使用模式将表分为几列。通常，当一列有50个或更多列时，经常会访问一些列，而某些列则不会经常访问。有些列经常被写入，而有些则经常被读取。将频繁访问与不频繁访问的列分隔为具有1：1关系的多个表通常是有好处的，因为为什么要浪费缓冲池中的空间来存储您可能不使用的数据（类似于为什么将大图像存储在常规VARBINARY(MAX)列中问题）？您还可以通过减小行大小，从而在数据页上容纳更多行，从而提高频繁访问列的性能，从而提高读取效率（物理和逻辑读取）。当然，您还需要通过重复PK来引入一些效率低下的问题，现在有时您需要将两个表连接起来，这也会使某些查询（即使只是轻微地）变得复杂。

因此，您可以采取几种方法，哪种方法最好取决于您的环境以及您要实现的目标。



我的印象是SQL Server只存储一个指针。表中的一些专用BLOB数据结构


不是那么简单。您可以在此处找到一些不错的信息，例如（VAR），Varbinary，Etc等（MAX）类型的LOB指针的大小是多少？但是基本知识是：




TEXT，NTEXT和IMAGE数据类型（默认情况下）：16字节指针

VARCHAR(MAX)，NVARCHAR(MAX)，VARBINARY(MAX)（默认情况下）：


如果数据适合在该行中，然后将其放置在此处
如果数据小于大约。 40,000字节（链接的博客文章显示的上限为40,000，但是我的测试显示的值略高），并且如果该结构的行上有空间，那么将有1到5个直接链接到LOB页面，从第一个链接的24个字节到前8000个字节，对于每增加一个8000字节的集合，每个附加链接增加12个字节，最大不超过72个字节。 40,000字节，或者没有足够的空间来存储适当数量的直接链接（例如，行上仅剩40个字节，而20,000字节的值则需要3个链接，第一个是24字节，另外两个是48字节，另外两个是12个总所需的行内空间），那么将只有24个字节的指针指向文本树页面，其中包含到LOB页面的链接。）

#3 楼

如果出于某种原因必须将数据存储在SQL Server中，我可以想到将数据存储在单独的表中的一些好处。有些比其他更具说服力。


将数据放在单独的表中意味着您可以将其存储在单独的数据库中。这对于计划维护可能具有优势。例如，您只能在包含BLOB数据的数据库上运行DBCC CHECKDB。
如果您不总是将超过8000个字节放入BLOB中，则有可能在行中将其存储一些行。您可能不希望这样做，因为它会减慢使用聚簇索引访问数据的查询，即使查询不需要该列。将数据放在单独的表中可以消除这种风险。
当存储在行外时，SQL Server使用最多24个字节的指针来指向新页面。这会占用空间，并限制了可以添加到单个表中的BLOB列的总数。有关更多详细信息，请参见srutzky的答案。
不能在包含BLOB列的表上定义群集的列存储索引。此限制已被删除，将在SQL Server 2017中删除。
如果您最终决定将数据移到SQL Server外部，则如果数据已经在单独的表中，则进行更改可能会更容易。 br />