将用户个人资料图片存储在磁盘上还是数据库中? [英] Store user profile pictures on disk or in the database?

问题描述

我正在构建一个asp.net mvc应用程序，用户可以在该应用程序中将图片附加到其个人资料上，还可以在系统的其他区域(例如显示最近消息等的仪表板上的消息收发小工具)附加

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.

数据库的优势

• 易于备份整个数据库，并与关联的配置文件/用户表一起保存配置文件内容/图像

• easy to backup entire database and keep profile content/images with associated profile/user tables

当我稍后构建Web服务时，它们可以从一个位置(数据库)中提取所有与利润相关的数据

when I build web services later down the track, they can just pull all the profiile related data from one spot(the database)

文件系统的优势

• 从磁盘加载文件可能更快

• loading files from disk is probably faster

还有其他优势吗?

其他站点在哪里存储此类信息.我应该有点担心像这样的数据库性能吗?

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?

或者，将这些图像存储在数据库中，但是将它们影子复制到磁盘，以便Web服务器可以从那里加载它们的想法又如何呢?这似乎既可以提供Db的备份和便利，又可以提供磁盘上文件的速度优势.

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.

有问题的基础设施

• 该网站将部署到运行NTFS文件系统的Windows Server 2003上的IIS.
• 数据库将为SQL Server 2008

摘要

在SO上阅读了许多相关的主题之后，许多人现在都趋向于使用SQL Server Filestream类型.但是据我所收集的(可能是错误的)，当文件很小时，并没有太大的好处.但是，当文件为多个MB或更大的文件时，文件流看起来可以大大提高性能.

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.

由于我的个人资料图片通常位于〜5kb左右，因此我决定将它们以varbinary(max)的形式保存在数据库的文件存储中.

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).

在ASP.NET MVC中，我确实看到了一些性能问题，例如为从数据库中拉出的图像返回FileContentResults.因此，如果在我的应用程序缓存中找不到该文件的位置，那么我最终在读取文件时将其缓存在磁盘上.

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;

• 数据库存储使数据的烘焙更加容易，文件直接链接到配置文件
• 将阴影复制到磁盘以实现更好的缓存

我随时可以删除磁盘上的缓存文件夹，并且当重新请求图像时，它们将在第一次命中时被重新复制并从那里的缓存中提供.

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.

推荐答案

实际上，根据您拥有的图像数量，您在数据库中查找数据的速度实际上可能会更快，除非您使用的是高度优化的文件系统引擎.数据库是为快速查找而设计的，并且使用了比文件系统更有趣的技术.

Actually your datastore look up with the database may actually be faster depending on the number of images you have, unless you are using highly optimized filesystem engine. Databases are designed for fast lookups and use a LOT more interesting techniques than a file system does.

reiserfs(已过时)对于查找真的很棒，zfs，xfs和NTFS都具有出色的哈希算法，Linux ext4看起来也很有前途.

reiserfs (obsolete) really awesome for lookups, zfs, xfs and NTFS all have fantastic hashing algorithms, linux ext4 looks promising too.

在系统读取方面，块读取不会有任何不同.问题是，查询查找返回文件名(可能是哈希?)的速度更快，而文件名又可以使用单独的打开，文件发送关闭来访问?还是只是将Blob丢弃?

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 (may be a hash?) which in turn is accessed using a separate open, filesend 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 yrouself.

考虑一下，并记住一点基准测试永远不会伤害任何人:-)，因此请使用您的典型数据集大小对其进行测试，并考虑诸如同时查询之类的问题.

Think about it, and remember bit of benchmarking never hurt nobody :-) so test it out with your typical dataset size and take into account things like simultaneous queries etc.

这篇关于将用户个人资料图片存储在磁盘上还是数据库中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！