存储少量图像：blob 还是 fs？
我正在向我的网站添加一些功能，以便用户可以上传自己的个人资料图片，因此我想知道是否将它们作为 BLOB 存储在数据库中，或者将它们放入文件系统中。
我在这里发现了一个与此类似的问题：在数据库中存储图像：是或否< /a>，但给出的答案更适合那些期待数千甚至数百万张图像的人，而我更关心小图像（JPEG 最大可能为 150x150 像素），以及少量图像：也许最多一两千。
在这种情况下，DB BLOB 与文件系统有何不同？客户端如何缓存来自数据库和文件系统的图像？
如果存储在数据库中的 BLOB 是正确的选择 - 关于存储它们的位置，我是否应该了解什么？ 由于我想象大多数用户不会上传图片，因此我是否应该创建一个 user_pics 表以在需要时（外部）联接到常规 users 表？
编辑：我正在重新打开这个问题，因为它不是您链接到的两个问题的重复。 这个问题具体是关于对少量图像使用 DB 或 FS 的优缺点。 正如我上面所说，另一个问题是针对需要存储成千上万张大图像的人。
I'm adding some functionality to my site so that users can upload their own profile pictures, so I was wondering about whether to store them in the database as a BLOB, or put them in the file system.
I found a question similar to this here: Storing images in DB: Yea or Nay, but the answers given were geared more towards people expecting many many thousands or even millions of images, whereas I'm more concerned about small images (JPEGs up to maybe 150x150 pixels), and small numbers of them: perhaps up to one or two thousand.
What are the feelings about DB BLOB vs Filesystem for this scenario? How do clients go with caching images from the DB vs from the filesystem?
If BLOBs stored in the DB are the way to go - is there anything I should know about where to store them? Since I imagine that a majority of my users won't be uploading a picture, should I create a user_pics table to (outer) join to the regular users table when needed?
Edit: I'm reopening this question, because it's not a duplicate of those two you linked to. This question is specifically about the pros/cons of using a DB or FS for a SMALL number of images. As I said above, the other question is targeted towards people who need to store thousands upon thousands of large images.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
回答您问题的部分内容：
对于数据库：数据库中有一个 last_modified 字段。 使用 Last-Modified HTTP 标头，以便客户端的浏览器可以正确缓存。 当浏览器“如果较新”请求图像时，请务必发送适当的响应（不记得它叫什么；一些 HTTP 请求标头）。
对于文件系统：执行相同的操作，但使用文件的修改时间。
我会将 BLOB 和相关元数据放在自己的表中，并在它和用户表之间建立某种关系。 这样做将使您更容易优化数据的表存储方法，使事情更加整洁，并为可扩展性留出空间（例如通用“文件”表）。
To answer parts of your question:
For a database: Have a last_modified field in your database. Use the Last-Modified HTTP header so the client's browser can cache properly. Be sure to send the appropriate responses when the browser requests for an image "if newer" (can't recall what it's called; some HTTP request header).
For a filesystem: Do the same thing, but with the file's modified time.
I would put the BLOB and related metadata in its own table, with some kind of relation between it and your user table. Doing this will make it easier to optimize the table storage method for your data, makes things tidier, and leaves room for expandability (e.g. a general "files" table).
我曾经在使用小型 DMS 处理 pdf 文件时遇到过类似的问题。 该场景与您的情况不同：最多可能有 100 个文件，每个文件大小最多 10 MB - 这不是您对个人资料图片的期望。 但当时一位朋友给我的答案也适用于你的情况：
这不是最终的答案（*），但对于初学者来说这是一个很好的经验法则。
正如 Aaron Digulla 在他的回答中所说，我从未听说过 Windows FS 速度慢且有时不可靠。 如果存在这样的问题，这当然需要考虑在内。但对于头像图片来说，我觉得这并不重要。
(*) 我知道，我知道，42...
I once faced a similar question with a small DMS for pdf files. The scenario was different from yours: A maximum of may be 100 files with sizes up to 10 MB each - not what you expect for profile pictures. But the answer a friend gave me back then applies to your case as well:
This is not the ultimate answer(*), but its a good rule of thumb for starters.
I have never heard of the Windows FS being slow and sometimes unreliable, as Aaron Digulla states in his answer. If there are such problems, this certainly needs to be factored in. But for avatar pictures, it does not strike me as important.
(*) I know, I know, 42...
DB 针对延迟、事务等进行了优化。
图像存储针对读取延迟、存储成本等进行了优化。Blob
存储非常适合存储数百万张图像。 我在 SeaweedFS 上工作。 它基于 Facebook 存储用户照片的设计。
DB is optimized for latency, transactions, etc.
Image storage is optimized for read latency, storage cost, etc.
A blob store is ideal for storing millions of images. I work on SeaweedFS. It was based on Facebook's design for storing their user photos.
从服务他们的角度来看，编写服务他们的代码、备份程序等会更方便吗？ 您想要的是适合自己的正确答案，而不是适合别人的正确答案。
What would be more convenient, from the perspective of serving them, writing the code to serve them, backup procedures, etc.? You want the right answer for you, not the right answer for someone else.
从我的角度来看，任何可能留在数据库之外的东西都应该留在数据库之外。 它可能是文件系统或单独的表，您不会每天复制或备份。 它使数据库变得更轻，增长更慢，并且更易于理解和维护。
如果您使用的是 MSSQL，请确保 blob 存储在单独的数据文件中。 不像其他事情一样在小学。
From my point of view anything what may be left outside of database should stay outside. It may be file system or separate tables which you do not replicate or backup every day. It makes database much lighter, it grows slower and it easier to understand and maintain.
If you are on MSSQL make sure that blobs are stored in separate data file. Not in PRIMARY as everything else.
在 Windows 上，将尽可能多的内容放入数据库中。 文件系统有点慢，有时甚至不可靠。
在 Linux 上，您有更多选择。 在这里，您应该考虑将大文件移动到文件系统中，并将名称保留在数据库中。 如果您使用像 Ext3 或 ReiseFS 这样的现代文件系统，您甚至可以创建许多具有相当好的性能的小文件。
您还需要考虑如何访问数据。 如果您拥有数据库中的所有内容，那么您就有一个访问路径，无需担心另一组权限，但您必须处理读/写 BLOB 的额外复杂性。 在许多数据库中，无法搜索 BLOB。
在文件系统上，您可以对数据运行其他工具，如果文件存储在数据库中，则这是不可能的。
On Windows, put as much as you can in the database. The filesystem is somewhat slow and sometimes even unreliable.
On Linux, you have more options. Here, you should consider moving big files into a filesystem and just keep the name in the DB. If you use a modern filesystem like Ext3 or ReiseFS, you can even create many small files with pretty good performance.
You also need to take into account how you can access the data. If you have everything in the DB, you have one access path, need not worry about another set of permissions, but you have to deal with the extra complexity of reading/writing BLOBs. In many DBs, BLOBs can't be searched.
On the filesystem, you can run other tools on your data which isn't possible if the files are stored in a DB.
我会将它们存储在数据库中：
由于您谈论的是少量图像，因此易用性/管理应该优先于性能问题在相关问题中进行辩论。
I would store them in the database:
Since you are talking about a small number of images, ease of use/administration should take preference over performance issues which are debated in the linked questions.
我认为将它们存储在数据库中具有可管理性优势； 它们可以与其他数据一致地备份和恢复 - 您不会忘记删除过时的数据（嗯，您可能会这样做，但可能性较小），并且如果您将数据库迁移到另一台机器，则图像会随着它。
I think there is a managability advantage storing them in the database; they can be backed up and restored consistently with the other data - you won't forget to delete obsolete ones (well, you might, but it's a bit less likely), and if you migrate the database to another machine, the images go with it.