将用户个人资料图片存储在磁盘上还是数据库中？
我正在构建一个 asp.net MVC 应用程序，用户可以在其中将图片附加到他们的个人资料中，而且还可以在系统的其他区域中附加图片，例如仪表板上显示最近消息的消息小工具等。
当用户上传这些时，我想知道是否最好将它们存储在数据库或磁盘上。
数据库优势
轻松备份整个数据库并保留配置文件内容/图像与关联的配置文件/用户表
当我稍后构建 Web 服务时，他们可以从一个位置（数据库）提取所有个人资料相关数据
文件系统优势
从磁盘加载文件可能更快
还有其他优点吗？
其他网站在哪里存储此类信息？对于这样的事情，我有点担心数据库性能是否正确？
也许有办法将从数据库中取出的图像缓存一段时间？
或者，将这些图像存储在数据库中，但将它们卷影复制到磁盘以便 Web 服务器可以从那里加载它们，这个想法怎么样？这似乎既提供了数据库的备份和便利性，同时又提供了磁盘上文件的速度优势。
有问题的基础架构
- 该网站将部署到运行 NTFS 文件系统的 Windows Server 2003 上的 IIS。
- 数据库将是 SQL Server 2008
摘要
阅读了这里的许多相关主题，许多人现在倾向于使用 SQL Server Filestream 类型。然而，根据我收集到的信息（我可能是错的），当文件很小时，并没有多大好处。然而，当文件达到多个 MB 或更大时，文件流看起来可以极大地提高性能。
由于我的个人资料图片往往约为 5kb 左右，因此我决定将它们作为 varbinary(max) 存储在数据库的文件存储中。
在 ASP.NET MVC 中，我确实看到了一些性能问题，为从数据库中提取的图像返回 FileContentResults。因此，如果在我的应用程序缓存中找不到该文件的位置，我最终会在读取该文件时将其缓存在磁盘上。
所以我想我选择了混合动力车；
- 数据库存储使数据烘焙变得更容易，文件直接链接到配置文件
- 卷影复制到磁盘以实现更好的缓存
在任何时候我都可以删除磁盘上的缓存文件夹，并且当重新请求图像时，它们将被重新复制第一次命中时，然后从缓存中提供服务。
I'm building an asp.net MVC application where users can attach a picture to their profile, but also in other areas of the system like a messaging gadget on the dashboard that displays recent messages etc.
When the user uploads these I am wondering whether it would be better to store them in the database or on disk.
Database advantages
Easy to backup the entire database and keep profile content/images with associated profile/user tables
when I build web services later down the track, they can just pull all the profile related data from one spot(the database)
Filesystem advantages
loading files from disk is probably faster
any other advantages?
Where do other sites store this sort of information? Am I right to be a little concerned about database performance for something like this?
Maybe there would be a way to cache images pulled out from the database for a period of time?
Alternatively, what about the idea of storing these images in the database, but shadow copying them to disk so the web server can load them from there? This would seem to give both the backup and convenience of a Db, whilst giving the speed advantages of files on disk.
Infrastructure in question
- The website will be deployed to IIS on windows server 2003 running NTFS file system.
- The database will be SQL Server 2008
Summary
Reading around on a lot of related threads here on SO, many people are now trending towards the SQL Server Filestream type. From what I could gather however (I may be wrong), there isn't much benefit when the files are quite small. Filestreaming however looks to greatly improve performance when files are multiple MB's or larger.
As my profile pictures tend to sit around ~5kb I decided to just leave them stored in a filestore in the database as varbinary(max).
In ASP.NET MVC I did see a bit of a performance issue returning FileContentResults for images pulled out of the database like this. So I ended up caching the file on disk when it is read if the location to this file is not found in my application cache.
So I guess I went for a hybrid;
- Database storage to make baking up of data easier and files are linked directly to profiles
- Shadow copying to disk to allow better caching
At any point I can delete the cache folder on disk, and as the images are re-requested they will be re-copied on first hit and served from the cache there after.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您应该将对文件的引用存储在数据库上，并将实际文件存储在磁盘上。
这种方法更加灵活并且更容易扩展。
您可以拥有一个数据库和多个提供静态内容的服务器。让多个数据库来完成这项工作会更加棘手。
Flickr 就是这样工作的。
我在这里给出了更详细的答案，你可能会发现它很有用。
You should store a reference to the files on a database and store the actual files on disk.
This approach is more flexible and easier to scale.
You can have a single database and several servers serving static content. It will be much trickier to have several databases doing that work.
Flickr works this way.
I gave a more detailed answer here, you may find it useful.
实际上，使用数据库进行数据存储查找可能会更快，具体取决于您拥有的图像数量，除非您使用高度优化的文件系统引擎。数据库是为快速查找而设计的，并且使用了比文件系统更有趣的技术。
ReiserFS（已过时）对于查找来说确实很棒。 ZFS、XFS 和 NTFS 都有出色的哈希算法。 Linux ext4 看起来也很有前途。
就块读取而言，对系统的影响不会有任何不同。问题是：什么更快，一个返回文件名（可能是散列？）的查询查找，然后使用单独的打开、文件发送、关闭来访问文件名？或者只是把斑点倒掉？
有几件事需要考虑，包括网络命中、处理命中、可分发性等。如果您将内容存储在数据库中，那么您可以移动它。话又说回来，如果您将图像存储在内容交付服务上，这可能会更快，因为您不会对自己进行任何网络点击。
想一想，并记住一些基准测试不会伤害任何人:-) 因此，使用典型的数据集大小对其进行测试，并考虑同时查询等因素。
Actually, your data store lookup with the database may actually be faster, depending on the number of images you have, unless you are using a highly optimized filesystem engine. Databases are designed for fast lookups and use a lot more interesting techniques than a file system does.
ReiserFS (obsolete) is really awesome for lookups. ZFS, XFS, and NTFS all have fantastic hashing algorithms. Linux ext4 looks promising too.
The hit on the system is not going to be any different in terms of block reads. The question is: what is faster, a query lookup that returns the filename (maybe a hash?), which in turn is accessed using a separate open, file send, close? Or just dumping the blob out?
There are several things to consider, including network hit, processing hit, distributability, etc. If you store stuff in the database, then you can move it. Then again, if you store images on a content delivery service, that may be way faster, since you are not doing any network hits on yourself.
Think about it, and remember a bit of benchmarking never hurt anybody :-) So, test it out with your typical dataset size and take into account things like simultaneous queries, etc.