数据库与文件系统中的图像 [英] Images in database vs file system

问题描述

我们即将开展一个项目，我们将在该项目中构建一个完整的后端 CMS 系统，该系统将通过一个软件包为我们的整个外联网和内联网提供支持.我一直试图找到答案的问题是哪个更好:将图像存储在数据库 (SQL Server 2005) 中以便我们可以具有完整性、单一复制计划等，还是存储在文件系统上?

We have a project coming up where we will be building a whole backend CMS system that will power our entire extranet and intranet with one package. The question I have been trying to find an answer to is which is better: storing images in the database (SQL Server 2005) so we may have integrity, single replication plan, etc OR storing on the file system?

我们遇到的一个问题是我们有多个负载平衡的服务器，它们需要始终拥有相同的数据.到目前为止，我们有 SQL 复制来处理这个问题，但文件复制似乎有点困难.我们担心的另一个问题是我们希望同一图像有多个分辨率，我们不确定在文件系统上创建和存储每个版本是否最好，或者是否可以根据请求动态拉取和创建我们想要的分辨率图像.

One issue we have is that we have multiple servers load balanced that require to have the same data at all times. As of now we have SQL replication taking care of that but file replication seems to be a little tougher. Another concern we have is that we would like to have multiple resolutions of the same image, we are not sure if creating and storing each version on the file system would be best or maybe dynamically pulling and creating the resolution image we would like upon request.

我们的关注点如下:

• 数据完整性
• 数据复制
• 多种分辨率
• 数据库与文件系统的速度
• 数据库与文件系统的开销负载
• 数据管理和备份

有没有人有类似的情况或对推荐的内容有任何意见?在此先感谢您的帮助！

Does anyone have a similar situation or have any input on what would be recommended? Thanks in advance for the help!

推荐答案

Microsoft Research 发表了一篇不错的研究论文，名为 Blob 还是不 Blob，他们研究了各种变量和影响.

There was a nice research paper published by Microsoft Research called To Blob or not to Blob where they looked at all sorts of variables and impacts.

他们最终的发现:

• 大小高达 256 KB，与文件系统相比，blob 存储在数据库中的效率更高
• 对于 1 MB 和更大的文件系统，文件系统效率更高
• 介于两者之间

自从那篇论文发表以来，SQL Server 2008 还添加了 FILESTREAM 属性，这使得在文件系统中存储内容但在事务控制下成为现实.强烈建议您检查一下！

Since that paper was published, SQL Server 2008 has also added the FILESTREAM attribute which makes storing stuff in the file system, but under transactional control, a reality. Highly recommended you check that out!

这篇关于数据库与文件系统中的图像的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！