CMS - 将图片保存在数据库中，正确的结构是什么？
我目前构建了一个CMS系统，每篇文章需要保存大量图片。我有很多问题:-)
我需要显示几种尺寸的图片，带或不带水印。此外，我还需要原始图片，用于存档和管理目的。我现在想做的就是将图片保存到数据库中，有两个版本：1.原图，2.网页优化版本。
将所有图像保存在表格中确实很方便。但这真的是个好主意吗？假设数据库将包含十万张图片，原始图片大小可能约为3MB。因此数据库可以轻松达到 100TB 大小...这真的是一个好的策略吗？
另一方面，我为每张图片保存了较小的版本。该版本需要以几种尺寸显示，带水印和不带水印。目前我认为在每个请求中都要考虑这一点。请求将有参数宽度，根据这个我可以决定大小和水印。 （当然我会缓存这项工作）。再说一遍，这是一个好的策略吗？它真的有用吗，还是这是非常昂贵的额外工作？
将其保存在数据库上真的更好吗？我的意思是，每个对文章的请求都需要大约 50 个对其图像的请求，并且每个请求都需要打开/关闭与数据库的连接。
我将使用的技术：.net、sql-server 2008、NHibernate。
I currently build a CMS system that need to save a lot of pictures per article. I have a lot of questions :-)
I need to show the pictures in a few sizes, with or without watermark. In addition I need to have the original picture too, for archive and admin purpose. What that I think to do right now is to save the pictures in the database, in two versions: 1. the original picture, 2. web-optimized version.
It is really convenient way to save all the images in a table. But does it really good idea? Let say that the database will contain a hundred of thousand pictures, the original pictures size is probably around 3MB. so the db can be easily 100TB size.... Is this really good strategy?
On the other hand, I save a smaller version to each picture. This version need to be shown in a few sizes, with and without watermark. Currently I think to do think to this in on each request. the request will have parameters width, and according to this I can decide the size and the watermark. (I'll cache this work of course). Again, Is this a good strategy? do it really gonna work, or this is very expensive extra work?
Is it really better to save this on the db? I mean each request to article, will need around 50 another requests to its images, and each request required open/close connection to the database.
Technologies that I going to use: .net, sql-server 2008, NHibernate.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
最好的方法是将这些图像存储在文件系统中，并将 id 存储在数据库中。由于性能和维护方面的原因。在文件系统上备份和恢复会容易得多，而将 DBMS 推送到此类工作并不是最好的主意，您需要将它们从数据库传输到应用程序，然后推送到客户端。我只是相信这不是它的工作。放置一个 lighttpd 守护进程或其他用于图像托管的东西，然后让它完成它的工作。
但如果您喜欢这个想法，因为您使用的是 sql server 2008，所以您可以使用 FILESTREAM 将图像存储在表中。最终，它将在您选择的存储位置创建文件，并将二进制数据存储在文件系统中，同时提供事务功能和数据完整性，这是一个很大的好处。看看这个选项。我记得，这表现良好，并且实际的数据库会更加紧凑。
关于动态调整大小，我建议避免这种情况。存储比 CPU 时间便宜，只需在上传时创建各种缩略图和水印版本并将它们存储在某个地方，然后在需要时使用。不要一次又一次地执行相同的操作。您可以在第一次请求调整大小的版本时执行此操作，这样可以更轻松地添加新版本或定期清除缓存以删除未使用的文件。您还可以仅备份原始版本。
The best approach would be storing those images in filesystem and ids on database. Because of performance and maintenance reasons. Backing up and restoring would be much easier on filesystem and pushing the DBMS for such a work is not the best idea, you will need to transfer them from db to application and then push to the client. I just believe that's not it's job. Put a lighttpd daemon or something for image hosting and leave it do its job.
But if you like the idea, since you are going with sql server 2008, you can use FILESTREAM to store your images in your tables. Eventually, it will create files in a storage location that you choose and store the binary data in filesystem while providing transactional features and data integrity, it is a big bonus. Take a look at that option. As I remember, that performs good and the actual database will be much compact.
About the dynamic resizing, I say avoid that. Storage is cheaper than CPU time, just create variety of thumbnails and watermarked versions upon upload time and store them once in somewhere then use when required. Do not perform same operations again and again. You may do that at first request to the resized version, this way it will be easier to add new versions or purging the cache periodically to remove unused files. You will also be able to backup just the original versions.
将图像放入数据库有几个优点。我想到了 ACID 转换和备份一致性。如果您绝对需要，请将图像放入数据库中。正如您所指出的，这是有代价的：您需要庞大的数据库基础设施，如机器、许可证、运营团队。每个图像检索都是巨大的数据库 I/O 工作。
只需将元数据存储在数据库中并将图像 blob 放在文件系统上，很多事情就会变得容易得多。
做出决定的两种方法：
数据库中图像方法中您绝对需要的杀手级功能是什么（绝对像“如果我没有这个，整个事情就根本无法工作”） ？如果有的话，就去尝试
做一个餐巾纸后面的商业案例，计算数据库中图像方法的总成本（项目工作、基础设施、机器、许可证、操作）并进行比较使用文件系统中的图像方法。这应该为如何继续进行提供一些提示。
Putting the images in the database has a couple of advantages. ACID tanscations and backup consistency come to mind. If you absolutely need that then put the images in the database. As you pointed out, this comes with a price: you'll need a huge database infrastructure like machines, licenses, operation team. Each image retrieval is a huge DB I/O effort.
A lot of things will be much easier with only storing metadata in the DB and putting the image blobs on a filesystem.
Two approaches to come to a decison:
What is the killer feature you absolutely (absolutely like in "if I don't have that, the whole thing will not work at all") need from the image-in-database approach? If there is one, go for it
Do a back-of-the-napkin business case, calculating the total cost of the image-in-database approach (project efforts, infrastructure, machine, license, operation) and compare that with an image-in-filesystem approach. That should give some hints on how to proceed.