主页 / dba / 问题 / 174678
Accepted
Heinzi
Asked: 2017-05-27 05:20:49 +0800 CST

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

  • 772

这个高度赞成的 SO 答案建议将图像放在单独的表中，即使与另一个表只有 1:1 的关系：

如果您决定将图片放入 SQL Server 表中，我强烈建议您使用单独的表来存储这些图片 - 不要将员工照片存储在员工表中 - 将它们保存在单独的表中。这样，假设您并不总是需要选择员工照片作为查询的一部分，那么 Employee 表可以保持精简、平均和非常高效。

为什么？我的印象是SQL Server 只在表中存储了一个指向某些专用 BLOB 数据结构的指针，那么为什么还要手动创建另一层间接呢？它真的能显着提高性能吗？如果是，为什么？

sql-server blob

3 个回答

  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 数据读入内存。

    您能想出比存储 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，因为我知道你在检查腐败，对吧？
    • 如果您进行任何索引维护，它们也会减慢速度。

    希望这可以帮助！

    • 18
  2. Best Answer

    这些图像有多大，您希望有多少？虽然我大多同意@sp_BlitzErik的观点，但我认为在某些情况下可以这样做，因此更清楚地了解此处实际请求的内容会有所帮助。

    可以考虑减轻 Erik 指出的大部分负面影响的一些选项是：

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

    这有助于解决“内部或外部”问题，但不涉及单表与多表问题。我可以说，除了这个特定问题之外，当然还有根据使用模式将表拆分为列组的有效案例。通常，当一个列有 50 列或更多列时，有一些经常被访问，而另一些则不是。有些列经常被写入，而有些则主要被读取。将经常访问的列与不经常访问的列分成具有 1:1 关系的多个表通常是有益的，因为为什么要浪费缓冲池中的空间来存储您可能不使用的数据（类似于为什么定期存储大图像VARBINARY(MAX)列有问题）？您还可以通过减小行大小来提高频繁访问列的性能，从而在数据页上放置更多行，从而提高读取（物理和逻辑）的效率。当然，您也需要复制 PK 来引入一些低效率，现在有时您需要连接两个表，这也使一些查询复杂化（即使只是稍微复杂）。

    因此，您可以采取多种方法，哪种方法最好取决于您的环境和您要完成的工作。

    我的印象是 SQL Server 只在表中存储了指向某些专用 BLOB 数据结构的指针

    没那么简单。你可以在这里找到一些很好的信息，什么是（MAX）类型的 LOB 指针的大小，如 Varchar、Varbinary 等？，但基础是：

    • 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 个字节，另外两个链接需要 12 个，共 48 个字节所需的总行内空间），那么将只有一个 24 字节指针指向包含 LOB 页面链接的文本树页面）。
    • 12

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

    1. 将数据放在单独的表中意味着您可以将其存储在单独的数据库中。这对于定期维护具有优势。例如，您DBCC CHECKDB只能在包含 BLOB 数据的数据库上运行。

    2. 如果您不总是将超过 8000 个字节放入 BLOB 中，那么它可能会存储在某些行的行中。您可能不希望这样做，因为即使查询不需要该列，它也会减慢使用聚集索引访问数据的查询。将数据放在单独的表中可以消除这种风险。

    3. 当存储在行外时，SQL Server 使用最多 24 字节的指针来指向新页面。这会占用空间并限制您可以添加到单个表中的 BLOB 列的总数。有关更多详细信息，请参阅 srutzky 的答案。

    4. 不能在包含 BLOB 列的表上定义聚集列存储索引。此限制已删除，将在 SQL Server 2017 中删除。

    5. 如果您最终决定将数据移到 SQL Server 之外，那么如果数据已经在单独的表中，那么进行更改可能会更容易。

    • 8

相关问题