在存储数据时，将数据存储在Blob中与存储文件指针有什么区别？

Question

我有一个关于MySQL中blob数据类型的问题。

我了解到，这种数据类型可以用来存储文件。我还了解到，另一种方法是将文件存储在磁盘上，并通过数据库中的指针（通过varchar列）包含其位置。

但我有些困惑，因为我读到blob字段不会被存储在行内，需要单独查找才能检索其内容。那么，这是否与在文件系统上存储文件的指针有所不同呢？

- user1832628

Answer 1

我读到数据类型可以用来存储文件。

根据 MySQL手册 Blob 页面，BLOB是一个二进制大对象，可以容纳可变数量的数据。

由于它是专门用于存储二进制数据的数据类型，因此通常将其用于以二进制格式存储文件，将图像文件存储为二进制格式是 Web 应用程序中非常常见的用途。

对于 Web 应用程序，这意味着您首先需要将文件转换为二进制格式，然后存储它，每次需要检索文件时都需要执行反向过程将其转换回原始格式。

除此之外，在数据库中存储大量数据可能会使其变慢。特别是在不仅仅是用于托管数据库的系统中。

我还读到一种替代方案是将文件存储在磁盘上，并在数据库中包含指向其位置的指针。

考虑到上述所有因素，Web应用程序的常见做法是将文件存储在MySQL之外，然后仅在数据库中存储其路径。这种方法在处理大量数据时可能加快数据库速度。

但我有点困惑，因为我读过blob字段不会存储在行内，需要单独查找才能检索其内容。

事实上，这取决于您使用的存储引擎，因为每个引擎都以不同的方式处理数据并存储它。对于适用于关系型数据库的InnoDB引擎，您可能希望阅读MySQL Performance blog上关于如何在MySQL中存储blob的文章。

但抽象地说，在MySQL 5及更高版本中，blob存储如下：

Innodb将整个blob存储在行页上，或者仅存储20字节BLOB指针，优先考虑将较小的列存储在页面上，这是合理的，因为您可以存储更多的列。

您现在可能认为正确的方法是将它们存储为单独的文件，但使用 blob 存储数据有一些优点，其中第一个（在我看来）是备份。我管理一个小型服务器，我不得不创建另一个子例程，仅将存储为路径的文件复制到另一个存储磁盘中（我们买不起一个体面的磁带备份系统）。如果我设计我的应用程序使用 blob，那么一个简单的 mysqldump 就足以备份整个数据库。

有关将 blob 用于备份的优点可以在 这篇文章 中进行更好的讨论，回答者遇到了与我类似的问题。

另一个优点是安全性和管理权限和访问的便利性。您 MySQL 服务器中的所有数据都受密码保护，您可以轻松地管理用户的权限，确定谁可以访问什么内容，谁不能访问。

在一个依赖于 MySQL 权限系统进行身份验证和使用的应用程序中，这肯定是一个加分项，因为对于入侵者来说，从磁盘检索图像（或像压缩文件这样的二进制文件）会更加困难，对于没有访问权限的用户也是如此。

所以我想说的是

如果您想管理MySQL和其中的所有数据，并且必须定期备份或计划更改甚至考虑未来更改操作系统，并且拥有良好的硬件并将其优化为MySQL，则应选择BLOB。

如果您不会管理MySQL（例如，作为Web主机），并且不打算更改操作系统或进行备份，请使用指向文件的varchar列。

希望这能帮到您。干杯！

- Bruno Vieira

Answer 2

如果您将数据存储在BLOB字段中，那么它就成为了对象抽象的一部分。

BLOB优势:

1. 如果您想删除具有BLOB的行，或者将其作为主/从表关系的一部分进行删除，或者可能是整个表层次结构，则您的BLOB将自动处理，并且与数据库中的任何其他对象具有相同的生命周期。

2. 您的脚本不需要访问除了数据库以外的任何内容就可以获取所有所需内容。 在许多情况下，直接文件访问会打开一个大问题，涉及如何绕过访问或安全限制。 例如，使用文件访问时，他们可能必须挂载包含实际文件的文件系统。 但是，对于在数据库中的BLOB，您只需要能够连接到数据库，无论您在哪里都可以。

3. 如果将其存储在文件中，而文件被替换、删除或不再可用，则您的数据库永远不会知道-从效果上看，您无法保证数据的完整性。 并且，在使用文件时，很难可靠地支持多个版本。 如果您使用并依赖于事务，则几乎不可能实现。

文件优势:

1. 有些数据库在处理BLOB时表现不佳。 例如，虽然MySQL中的官方BLOB限制为4GB，但实际上在默认配置下仅为1MB。 您可以通过调整客户端和服务器配置来增加MySQL命令缓冲区的大小，将其增加到16-32MB，但这会对性能和安全性产生很多其他影响。

2. 即使数据库没有某些奇怪的尺寸限制，在存储BLOB时总会存在一些开销，与仅使用文件相比。 此外，如果BLOB很大，则某些数据库不提供分段访问blob或流式传输的接口，这可能会对您的工作流程造成很大的障碍。

最终，由您决定。 我通常会尝试将其保存在BLOB中，除非这会带来不合理的性能问题。

- mvp

Answer 3

是的，MySQL中不符合行大小要求的BLOB会存储在溢出页面中。需要注意的是，有些BLOB足够小，可以与其他列一起存储在行中。BLOB页面并不相邻于其所在行存储的页面，因此读取它们可能会导致额外的I/O消耗。

另一方面，就像任何其他页面类型一样，BLOB页面可以占用InnoDB缓冲池中的内存，因此即使它们位于不同的页面上，后续读取BLOB也非常快速。文件可以被操作系统缓存，但通常它们会从磁盘读取。

以下是可能影响您决策的几个因素：

- BLOB与行逻辑上一起存储。这意味着如果您删除行，则相关的BLOB将自动删除。但是，如果将BLOB存储在数据库之外，则在从数据库中删除行后，您将得到孤立的BLOB文件。您需要手动查找和删除这些文件。 - 存储在行中的BLOB也遵循事务语义。例如，新的BLOB或更新的BLOB对其他事务不可见，直到您提交。您还可以回滚更改。将BLOB存储在数据库之外会使这变得更加困难。 - 当您备份包含BLOB的数据库时，该数据库显然会变得更大，但是当您备份时，可以一步获得所有数据和相关BLOB。如果您将BLOB存储在外部，则必须备份数据库并备份存储BLOB文件的文件系统。如果需要确保从某个时间点捕获数据和BLOB，则几乎需要使用某种文件系统快照。 - 如果使用复制，则自动确保将BLOB复制到复制副本的唯一方法是将其存储在数据库中。

- Bill Karwin

Answer 4

通过文件系统访问将比通过数据库访问更快。 Blob列在索引/排序等方面存在一些缺点，如果您希望在将来进行这些操作，则可以使用文件名列。

对于大型Blob，数据库也会很快增长，然后像备份这样的任务就变得更慢了。 我建议在数据库中使用文件位置，并在文件系统上进行物理存储。

- Science_Fiction

Answer 5

更好的方法是将文件存储在文件系统文件夹中，并通过数据库中的varchar字段指向它们的路径。将文件保存在数据库中的缺点之一是会减慢数据库的速度或降低其性能。

- SaidbakR

---

假设他从Windows服务器更改为Linux，指向文件仍然是更好的方法吗？ - Bruno Vieira

如果您谈论文件夹分离 / 或 \，任何简单的SQL查询或迁移脚本都可以更改所有记录。此外，如果它是Web应用程序，通常会存储相对路径。 - SaidbakR

备份一个包含数十或数百GB文件的数据库非常麻烦。使用rsync备份一个拥有几TB数据的文件系统则很容易。 - tadman

而且，不仅如此，操作系统之间的文件夹和布局变化也使得这个过程变得困难，而实际上只需用一个mysqldump命令就可以解决这个问题。 - Bruno Vieira

嗨，谢谢回复。但我还是有一个疑问。如果Blob包含指向文件位置的指针，那么和使用varchar存储指向磁盘上文件的指针有什么不同？如果我的问题不太清楚，请原谅，我对硬件方面不是很熟悉。 - user1832628

它的不同之处在于，当您仅存储路径时，文件数据不会存储在您的数据库中。因此，您将无法进行比较、排序和执行MySQL搜索查询时执行的所有其他操作。 - Bruno Vieira