存储二进制或图像文件的最佳方式
存储二进制或图像文件的最佳方式是什么？
- 数据库系统
- 文件系统
请您解释一下，为什么？
What is the best way storing binary or image files?
- Database System
- File System
Would you please explain, why?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
没有真正的最佳方法，只有一堆权衡。
数据库优点：
1. 在集群环境中更容易处理。
2. 不依赖文件服务器等额外资源。
3. 负载均衡环境下无需设置“同步”操作。
4. 备份自动包含文件。
数据库缺点：
1. 数据库的大小/增长。
2. 根据数据库服务器和您的语言，可能会难以放入和检索。
3.速度/性能。
4. 根据数据库服务器的不同，上传和导出时必须对文件进行病毒扫描。
文件优点：
1. 对于单个 Web/单个数据库服务器安装，速度很快。
2. 良好理解操作文件的能力。换句话说，如果磁盘空间不足，可以轻松地将文件移动到其他位置。
3. 可以在文件“静止”时进行病毒扫描。这使您可以利用扫描仪更新。
文件缺点：
1. 在多 Web 服务器环境中，需要可访问的共享。也应该进行集群以进行故障转移。
2. 处理文件访问的附加安全要求。您必须小心 Web 服务器和/或共享不允许文件执行。
3. 事务性备份必须考虑文件系统。
上面说了，SQL 2008 有一个叫做 FILESTREAM 的东西，它结合了这两个世界。您上传到数据库，它会透明地将文件存储在磁盘上的目录中。检索时，您可以从数据库中提取；或者您可以直接转到它在文件系统上的位置。
There is no real best way, just a bunch of trade offs.
Database Pros:
1. Much easier to deal with in a clustering environment.
2. No reliance on additional resources like a file server.
3. No need to set up "sync" operations in load balanced environment.
4. Backups automatically include the files.
Database Cons:
1. Size / Growth of the database.
2. Depending on DB Server and your language, it might be difficult to put in and retrieve.
3. Speed / Performance.
4. Depending on DB server, you have to virus scan the files at the time of upload and export.
File Pros:
1. For single web/single db server installations, it's fast.
2. Well understood ability to manipulate files. In other words, it's easy to move the files to a different location if you run out of disk space.
3. Can virus scan when the files are "at rest". This allows you to take advantage of scanner updates.
File Cons:
1. In multi web server environments, requires an accessible share. Which should also be clustered for failover.
2. Additional security requirements to handle file access. You have to be careful that the web server and/or share does not allow file execution.
3. Transactional Backups have to take the file system into account.
The above said, SQL 2008 has a thing called FILESTREAM which combines both worlds. You upload to the database and it transparently stores the files in a directory on disk. When retrieving you can either pull from the database; or you can go direct to where it lives on the file system.
在数据库中存储二进制文件的优点：
您的系统需要的数据访问层
仅与 DB 接口，而不与 DB + 接口
文件系统。
基于相同的综合权限
保护其余部分的安全
数据库。
与其余的一起防止损失
通过数据库备份的方式保存您的数据。
没有单独的文件系统备份系统
必需的。
在数据库中存储二进制文件的缺点：
会占用很大的空间
可能会降低性能
（取决于你的二进制文件是否
文件存储在一个表中，该表是
经常查询其他内容或
不是）并进行更长时间的备份
次。
在文件系统中存储二进制文件的优点：
在。文件系统将处理
整理碎片并恢复
文件（例如将视频文件流式传输到
通过网络服务器）可能会
比使用数据库更快。
在文件系统中存储二进制文件的缺点：
层。需要自己的备份系统。
需要考虑参考性
完整性问题（例如删除
数据库中的指针需要
导致文件被删除
中没有“孤立”文件
文件系统）。
总的来说，我会使用文件系统。过去，使用 SQL Server 2005，我只需在数据库表中存储一个指向二进制文件的“指针”。该指针通常是 GUID。
如果您使用 SQL Server 2008（也许还有其他 - 我不知道），那么这里有个好消息：内置了对具有新 VARBINARY(MAX) FILESTREAM 数据类型的混合解决方案的支持。这些列在逻辑上的行为类似于 VARBINARY(MAX) 列，但在幕后，SQL Sever 2008 会将数据存储在文件系统中。
Pros of Storing binary files in a DB:
data access layer of your system need
only interface to a DB and not a DB +
file system.
same comprehensive permissions-based
security that protects the rest of
the database.
against loss along with the rest of
your data by way of database backups.
No separate filesystem backup system
required.
Cons of Storing binary files in a DB:
can take up significant space
potentially decreasing performance
(dependening on whether your binary
files are stored in a table that is
queried for other content often or
not) and making for longer backup
times.
Pros of Storing binary files in file system:
at. File systems will handle
defragmenting well and retrieving
files (say to stream a video file to
through a web server) will likely be
faster that with a db.
Cons of Storing binary files in file system:
layer. Needs its own backup system.
Need to consider referential
integrity issues (e.g. deleted
pointer in database will need to
result in deletion of file so as to
not have 'orphaned' files in the
filesystem).
On balance I would use the file system. In the past, using SQL Server 2005 I would simply store a 'pointer' in db tables to the binary file. The pointer would typically be a GUID.
Here's the good news if you are using SQL Server 2008 (and maybe others - I don't know): there is built in support for a hybrid solution with the new VARBINARY(MAX) FILESTREAM data type. These behave logically like VARBINARY(MAX) columns but behind the scenes, SQL Sever 2008 will store the data in the file system.
没有最好的办法。
什么？您需要更多信息吗？
我知道的有三种方式...其一，作为数据库中的字节数组。二、作为一个文件，其路径存储在数据库中。三、作为混合（仅当数据库允许时，例如使用 FileStream 类型）。
第一个非常酷，因为您可以在同一步骤中查询和获取数据。这总是好的。但是当您有大量文件时会发生什么？你的数据库变得很大。现在您必须处理大型数据库维护问题，例如尝试备份超过 1 TB 的数据库。如果您需要外部访问这些文件会发生什么？例如类型转换、质量操作（调整所有图像的大小、应用水印等）？这比有文件时要困难得多。
第二个非常适合大量文件。您可以将它们存储在 NAS 设备上、增量备份、保持数据库较小等等。但是，当您有大量文件时，您就会开始遇到文件系统的限制。如果你将它们分布在网络上，你就会遇到延迟问题、用户权限问题等。此外，如果你的网络被重新安排，我会很同情你。现在，您必须对数据库进行大量更新才能更改文件位置，如果出现问题，我很遗憾您。
然后是混合选项。它几乎是完美的——您可以通过查询获取文件，但您的数据库并不庞大。这能解决您所有的问题吗？可能不会。您的数据库不再可移植；您被锁定到特定的 DBMS。而且这个东西还没有成熟，所以你可以享受出牙的过程。谁说这可以解决所有不同的问题？
事实是，没有“最好”的方法。你只需要确定你的要求，根据它们做出最好的选择，然后当你发现你做错了事情时就忍气吞声。
There is no best way.
What? You need more info?
There are three ways I know of... One, as byte arrays in the database. Two, as a file with the path stored in the database. Three, as a hybrid (only if DB allows, such as with the FileStream type).
The first is pretty cool because you can query and get your data in the same step. Which is always nice. But what happens when you have LOTS of files? Your database gets big. Now you have to deal with big database maintenance issues, such as the trials of backing up databases that are over a terabyte. And what happens if you need outside access to the files? Such as type conversions, mass manipulation (resize all images, appy watermarks, etc)? Its much harder to do than when you have files.
The second is great for somewhat large numbers of files. You can store them on NAS devices, back them up incrementally, keep your database small, etc etc. But then, when you have LOTS of files, you start running into limitations in the file system. And if you spread them over the network, you get latency issues, user rights issues, etc. Also, I take pity on you if your network gets rearranged. Now you have to run massive updates on the database to change your file locations, and I pity you if something screws up.
Then there's the hybrid option. Its almost perfect--you can get your files via your query, yet your database isn't massive. Does this solve all your problems? Probably not. Your database isn't portable anymore; you're locked to a particular DBMS. And this stuff isn't mature yet, so you get to enjoy the teething process. And who says this solves all the different issues?
Fact is, there is no "best" way. You just have to determine your requirements, make the best choice depending on them, and then suck it up when you figure out you did the wrong thing.
我喜欢将图像存储在数据库中。只需更改数据库（无需复制文件）即可轻松从开发切换到生产。数据库可以像文件系统一样跟踪创建/修改日期等属性。
I like storing images in a database. It makes it easy to switch from development to production just by changing databases (no copying files). And the database can keep track of properties like created/modified dates just as well as the File System.
我个人从不出于性能目的将图像存储在数据库中。在我的所有网站中，我都有一个“/files”文件夹，我可以在其中根据要存储的图像类型放置子文件夹。然后我按照约定命名它们。
例如，如果我要存储个人资料图片，我会将其作为 profile_2.jpg 存储在“/files/profile/”中（如果 2 是帐户的 ID）。我总是制定一条规则，将服务器上的图像大小调整为我需要的最大尺寸，然后在需要时调整较小的尺寸。所以我会保存“profile_2_thumb.jpg”和“profile_2_full.jpg”。
通过为自己创建规则，您可以简单地在代码中调用 img src="/files/profile__thumb.jpg"
无论如何，我就是这样做的！
I personally never store images IN the database for performance purposes. In all of my sites I have a "/files" folder where I can put sub-folders based on what kind of images i'm going to store. Then I name them on convention.
For example if i'm storing a profile picture, I'll store it in "/files/profile/" as profile_2.jpg (if 2 is the ID of the account). I always make it a rule to resize the image on the server to the largest size I'll need, and then smaller ones if I need them. So I'd save "profile_2_thumb.jpg" and "profile_2_full.jpg".
By creating rules for yourself you can simply in the code call img src="/files/profile__thumb.jpg"
Thats how I do it anyway!