database 在数据库中存储图像 - 是还是不是？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3748/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Storing Images in DB - Yea or Nay?
提问by James Hall
So I'm using an app that stores images heavily in the DB. What's your outlook on this? I'm more of a type to store the location in the filesystem, than store it directly in the DB.
所以我正在使用一个在数据库中大量存储图像的应用程序。您对此有何看法？我更喜欢将位置存储在文件系统中，而不是直接将其存储在数据库中。
What do you think are the pros/cons?
你认为有什么优点/缺点？
回答by Mark Harrison
I'm in charge of some applications that manage many TB of images. We've found that storing file pathsin the database to be best.
我负责管理许多 TB 图像的一些应用程序。我们发现在数据库中存储文件路径是最好的。
There are a couple of issues:
有几个问题：
- database storage is usually more expensive than file system storage
- you can super-accelerate file system access with standard off the shelf products
- for example, many web servers use the operating system's sendfile()system call to asynchronously send a file directly from the file system to the network interface. Images stored in a database don't benefit from this optimization.
- things like web servers, etc, need no special coding or processing to access images in the file system
- databases win out where transactional integrity between the image and metadata are important.
- it is more complex to manage integrity between db metadata and file system data
- it is difficult (within the context of a web application) to guarantee data has been flushed to disk on the filesystem
- 数据库存储通常比文件系统存储更昂贵
- 您可以使用标准的现成产品超加速文件系统访问
- 例如，许多 Web 服务器使用操作系统的sendfile()系统调用将文件直接从文件系统异步发送到网络接口。存储在数据库中的图像不会从这种优化中受益。
- 像网络服务器等，不需要特殊的编码或处理来访问文件系统中的图像
- 数据库在图像和元数据之间的事务完整性很重要的地方胜出。
- 管理数据库元数据和文件系统数据之间的完整性更为复杂
- 很难（在 Web 应用程序的上下文中）保证数据已刷新到文件系统上的磁盘
回答by Rad
As with most issues, it's not as simple as it sounds. There are cases where it would make sense to store the images in the database.
与大多数问题一样，这并不像听起来那么简单。在某些情况下，将图像存储在数据库中是有意义的。
- You are storing images that are changing dynamically, say invoices and you wanted to get an invoice as it was on 1 Jan 2007?
- The government wants you to maintain 6 years of history
- Images stored in the database do not require a different backup strategy. Images stored on filesystem do
- It is easier to control access to the images if they are in a database. Idle admins can access any folder on disk. It takes a really determined admin to go snooping in a database to extract the images
- 您正在存储动态变化的图像，比如发票，并且想要获得 2007 年 1 月 1 日的发票？
- 政府要你保持6年历史
- 存储在数据库中的图像不需要不同的备份策略。存储在文件系统上的图像做
- 如果图像位于数据库中，则更容易控制对图像的访问。空闲管理员可以访问磁盘上的任何文件夹。需要一个非常坚定的管理员去窥探数据库以提取图像
On the other hand there are problems associated
另一方面也存在相关问题
- Require additional code to extract and stream the images
- Latency may be slower than direct file access
- Heavier load on the database server
- 需要额外的代码来提取和流式传输图像
- 延迟可能比直接文件访问慢
- 数据库服务器上的负载较重
回答by jason saldo
File store. Facebook engineers had a great talk about it. One take away was to know the practical limit of files in a directory.
文件存储。Facebook 工程师对此进行了精彩的讨论。一个要点是了解目录中文件的实际限制。
Needle in a Haystack: Efficient Storage of Billions of Photos
回答by Ray
This might be a bit of a long shot, but if you're using (or planning on using) SQL Server 2008 I'd recommend having a look at the new FileStreamdata type.
这可能有点长，但如果您正在使用（或计划使用）SQL Server 2008，我建议您查看新的FileStream数据类型。
FileStream solves most of the problems around storing the files in the DB:
FileStream 解决了将文件存储在 DB 中的大部分问题：
- The Blobs are actually stored as files in a folder.
- The Blobs can be accessed using eithera database connection orover the filesystem.
- Backups are integrated.
- Migration "just works".
- Blob 实际上作为文件存储在文件夹中。
- 该斑点可使用被访问或者数据库连接或在文件系统中。
- 备份是集成的。
- 迁移“正常工作”。
However SQL's "Transparent Data Encryption" does not encrypt FileStream objects, so if that is a consideration, you may be better off just storing them as varbinary.
然而，SQL 的“透明数据加密”不加密 FileStream 对象，因此如果考虑到这一点，最好将它们存储为 varbinary。
From the MSDN Article:
来自 MSDN 文章：
Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. Win32 file system interfaces provide streaming access to the data.
FILESTREAM uses the NT system cache for caching file data. This helps reduce any effect that FILESTREAM data might have on Database Engine performance. The SQL Server buffer pool is not used; therefore, this memory is available for query processing.
Transact-SQL 语句可以插入、更新、查询、搜索和备份 FILESTREAM 数据。Win32 文件系统接口提供对数据的流式访问。
FILESTREAM 使用 NT 系统缓存来缓存文件数据。这有助于减少 FILESTREAM 数据可能对数据库引擎性能产生的任何影响。不使用 SQL Server 缓冲池；因此，该内存可用于查询处理。
回答by Greg Hurlman
File paths in the DB is definitelythe way to go - I've heard story after story from customers with TB of images that it became a nightmare trying to store any significant amount of images in a DB - the performance hit alone is too much.
数据库中的文件路径绝对是要走的路 - 我从拥有 TB 图像的客户那里听到一个又一个故事，试图在数据库中存储大量图像变成了一场噩梦 - 仅对性能的影响就太大了。
回答by Patrick McElhaney
In my experience, sometimes the simplest solution is to name the images according to the primary key. So it's easy to find the image that belongs to a particular record, and vice versa. But at the same time you're not storing anythingabout the image in the database.
根据我的经验，有时最简单的解决方案是根据主键命名图像。因此很容易找到属于特定记录的图像，反之亦然。但与此同时，您没有在数据库中存储有关图像的任何信息。
回答by dicroce
The trick here is to not become a zealot.
这里的诀窍是不要成为狂热分子。
One thing to note here is that no one in the pro file system camp has listed a particular file system. Does this mean that everything from FAT16 to ZFS handily beats every database?
这里要注意的一件事是专业文件系统阵营中没有人列出特定的文件系统。这是否意味着从 FAT16 到 ZFS 的所有内容都能轻松击败每个数据库？
No.
不。
The truth is that many databases beat many files systems, even when we're only talking about raw speed.
事实是，许多数据库击败了许多文件系统，即使我们只谈论原始速度。
The correct course of action is to make the right decision for your precise scenario, and to do that, you'll need some numbers and some use case estimates.
正确的做法是为您的精确场景做出正确的决定，为此，您需要一些数字和一些用例估计。
回答by mluebke
In places where you MUST guarantee referential integrity and ACID compliance, storing images in the database is required.
在您必须保证参照完整性和 ACID 合规性的地方，需要在数据库中存储图像。
You cannot transactionaly guarantee that the image and the meta-data about that image stored in the database refer to the same file. In other words, it is impossible to guarantee that the file on the filesystem is only ever altered at the same time and in the same transaction as the metadata.
您不能事务性地保证存储在数据库中的图像和有关该图像的元数据引用同一个文件。换句话说，不可能保证文件系统上的文件只在与元数据相同的时间和相同的事务中被修改。
回答by crucible
As others have said SQL 2008 comes with a Filestream type that allows you to store a filename or identifier as a pointer in the db and automatically stores the image on your filesystem which is a great scenario.
正如其他人所说，SQL 2008 带有 Filestream 类型，它允许您将文件名或标识符作为指针存储在数据库中，并自动将图像存储在您的文件系统上，这是一个很好的场景。
If you're on an older database, then I'd say that if you're storing it as blob data, then you're really not going to get anything out of the database in the way of searching features, so it's probably best to store an address on a filesystem, and store the image that way.
如果您使用的是较旧的数据库，那么我会说，如果您将其存储为 blob 数据，那么您实际上不会以搜索功能的方式从数据库中获取任何信息，因此这可能是最好的在文件系统上存储地址，并以这种方式存储图像。
That way you also save space on your filesystem, as you are only going to save the exact amount of space, or even compacted space on the filesystem.
这样，您还可以节省文件系统上的空间，因为您只会节省文件系统上的确切空间量，甚至是压缩空间。
Also, you could decide to save with some structure or elements that allow you to browse the raw images in your filesystem without any db hits, or transfer the files in bulk to another system, hard drive, S3 or another scenario - updating the location in your program, but keep the structure, again without much of a hit trying to bring the images out of your db when trying to increase storage.
此外，您可以决定使用某些结构或元素进行保存，以允许您浏览文件系统中的原始图像而不会出现任何 db 命中，或者将文件批量传输到另一个系统、硬盘驱动器、S3 或其他场景 - 更新中的位置你的程序，但保持结构，在尝试增加存储空间时再次尝试将图像从数据库中取出。
Probably, it would also allow you to throw some caching element, based on commonly hit image urls into your web engine/program, so you're saving yourself there as well.
可能，它还允许您根据常见的点击图像 url 将一些缓存元素放入您的 Web 引擎/程序，因此您也可以将自己保存在那里。
回答by urini
Small static images (not more than a couple of megs) that are not frequently edited, should be stored in the database. This method has several benefits including easier portability (images are transferred with the database), easier backup/restore (images are backed up with the database) and better scalability (a file system folder with thousands of little thumbnail files sounds like a scalability nightmare to me).
不经常编辑的小型静态图像（不超过几兆）应存储在数据库中。这种方法有几个好处，包括更容易移植（图像与数据库一起传输）、更容易备份/恢复（图像与数据库一起备份）和更好的可扩展性（包含数千个小缩略图文件的文件系统文件夹听起来像是可扩展性的噩梦）我）。
Serving up images from a database is easy, just implement an http handler that serves the byte array returned from the DB server as a binary stream.
从数据库中提供图像很容易，只需实现一个 http 处理程序，它将从数据库服务器返回的字节数组作为二进制流提供。
