用于存储大量图像的SQL Server表结构
在 SQL Server 2008 中存储大量图像数据的最佳实践是什么？我预计使用大约 5 GB 的存储空间来存储大约 50,000 张图像。目前，我使用带有列的单个表来执行此操作：
ID: int/PK/identity
Picture: Image
Thumbnail: Image
UploadDate: DateTime
我很担心，因为在我预期总容量的 10% 左右，插入似乎需要很长时间。典型的图像约为 20k - 30k。有没有更好的逻辑结构来存储这些数据？或者我是否需要考虑集群或其他 IT 解决方案来适应数据负载？
What's the best practice for storing a large amount of image data in SQL Server 2008? I'm expecting to store around 50,000 images using approx 5 gigs of storage space. Currently I'm doing this using a single table with the columns:
ID: int/PK/identity
Picture: Image
Thumbnail: Image
UploadDate: DateTime
I'm concerned because at around 10% of my expected total capacity it seems like inserts are taking a long time. A typical image is around 20k - 30k. Is there a better logical structure to store this data? Or do I need to look into clustering or some other IT solution to accommodate the data load?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
Image是 SQL Server 2008 中已弃用的数据类型。自 SQL Server 2005 起，它已被VARBINARY(MAX)取代。如果您决定将图像存储在数据库中，那么您应该使用 VARBINARY(MAX) 字段并考虑添加 FILESTREAM 选项。根据 本白皮书：
（来源：microsoft.com）< /sub>
请注意，要实现这种流性能，您必须在设计中使用正确的 API 并获取 BLOB 的 Win32 句柄。请注意，更新到
FILESTREAM列（包括INSERTS）将比VARBINARY(MAX)慢。Imageis a deprecated data type in SQL Server 2008. It has been replaced withVARBINARY(MAX)since SQL Server 2005. If you decide to store the image in the DB, then you should useVARBINARY(MAX)fields and consider adding theFILESTREAMoption.For streaming data, like images,
FILESTREAMis much faster thanVARBINARY(MAX)alone, according to this white paper:(source: microsoft.com)
Note that to achieve this streaming performance you must use the proper API in your design and obtain the Win32 handle of the BLOB. Note that updates into a
FILESTREAMcolumn (includingINSERTS) will be slower thanVARBINARY(MAX).去 DB 还是不去 DB，这是一个问题。
你正在用 DB 中的图像开始一场宗教战争。
对于 SQL 2000，意见不一，但 2005 及更高版本在存储 blob 方面做得相当不错 - 只要看看使用 MS SQL Server 作为存储的 SharePoint 安装数量即可。我只会采用这条路线来存储较小的图像。
如果您最终将它们放入数据库中，我想说您应该将图像与其关联的数据分开，以便于查询并减少开发人员编写 SELECT * 时的 IO 和实例（是的，他们会的）。
查看 SQL 2008 中的 FILESTREAM - 它就是用于此类事情的。
以下是您可能需要考虑的有关数据库与文件系统的其他一些要点：
To DB or not to DB, that is the question.
You're starting a religious war here with Images in DB.
The opinion would be split for SQL 2000, but 2005 and above do a fairly decent job of storing blobs - just look at the number of SharePoint installations that use MS SQL Server as their storage. I would only go this route for minor image storage.
If you do end up putting them in the DB, I would say that you should separate the image from the data associated with it for ease of querying and reducing your IO and the instances when developers write
SELECT *(and yes, they will).Check out FILESTREAM in SQL 2008 - it is meant for things like this.
Here are some other points on DB vs. File system that you may want to consider:
查看 SQL Server 2008 中新的 Filestream 功能。本质上，它允许您可以将 blob（读取：图像）数据存储在数据库中，而无需在每次读写时将数据读入 sql 缓冲区。它无缝地使用文件系统来存储大文件而不是 sql 页面。这可以使较大文件的读取和写入时间更快，最重要的是，由于这一切都发生在幕后，因此您无需更改任何现有的存储过程即可使用文件流列。有关代码示例和一些性能分析，请参阅此处。
Check out the new Filestream features in SQL Server 2008. Essentially it lets you store blob (read: image) data in the database, without the overhead of having to read the data into sql buffers on every read and write. It seamlessly uses the filesytem to store your large files instead of sql pages. This can lead to much faster read and write times for larger files and , best of all, since this all happens under the hood, you don't need to change any existing stored procs to work with filestream columns. See here for code samples and some performance profiling.