将图像数据存储在MySQL数据库中?

时间:2022-02-24 16:40:08

I am implementing a project that deals with a significant amount of images.

我正在实施一个处理大量图像的项目。

In your opinion what are the cons/pros of the following two approaches:

在您看来,以下两种方法的缺点是什么?

  • I need to store thousands of items, each item as several string properties and an image.
  • 我需要存储数千个项目,每个项目作为多个字符串属性和图像。
  • Each item as an ID (integer)
  • 每个项目作为ID(整数)
  • MyISAM tables
  • MyISAM表
  • How would you store the images:
    • approach 1: store images into a directory and each image named as ID.jpg
    • 方法1:将图像存储到目录中,每个图像命名为ID.jpg
    • approach 2: store images into the database as a binary BLOB
    • 方法2:将图像作为二进制BLOB存储到数据库中
  • 如何存储图像:方法1:将图像存储到目录中,每个图像命名为ID.jpg方法2:将图像作为二进制BLOB存储到数据库中

Using approach 1 I can access the image directly and that's it

使用方法1我可以直接访问图像,就是这样

<img src="same_directory/10.jpg" />  

Using approach 2, I can still use the above HTML, but need to redirect that jpg access to a PHP script which will return the real image from the DB.

使用方法2,我仍然可以使用上面的HTML,但需要将jpg访问重定向到PHP脚本,该脚本将从数据库返回真实图像。

In terms of performance which one do you think its faster?

在性能方面哪一个你觉得它更快?

I am keen to approach 1.

我很想接近1。

5 个解决方案

#1


5  

advantages of approach 1:

方法1的优点:

  • Retrieving the flat file form webserver is more faster.
  • 检索平面文件表单Web服务器更快。
  • most of the web hosts likely to follow this approach.
  • 大多数网站主机可能会采用这种方法。
  • the file system is faster for flat file storage.
  • 平面文件存储的文件系统更快。

advantages of approach 2:

方法2的优点:

  • All your data is kept in one place, if you migrate your website/database the images will just be there
  • 您的所有数据都保存在一个地方,如果您迁移您的网站/数据库,图像就会存在
  • Its easier to sort/delete/etc...
  • 它更容易排序/删除/等...
  • Since you have to serve it via a PHP script, you can perform additional things such as security if required, or image processing (obviously you can do this with flat file too, but you have to make sure the security cant be bypassed by leaving the images in a public directory).
  • 由于您必须通过PHP脚本提供服务,因此您可以执行其他操作,例如安全性(如果需要)或图像处理(显然您也可以使用平面文件执行此操作,但您必须确保绕过安全性不能绕过安全性公共目录中的图像)。

considering performance approach 1 is best to proceed.

考虑性能方法1最好继续进行。

#2


2  

Storing on filesystem is faster.

存储文件系统的速度更快。

#3


1  

I'm be tempted to use the first approach as there's no real value in cluttering up the database with image data. (Fetching the data from the database will also be significantly slower than simply loading it off disk.)

我很想使用第一种方法,因为用图像数据混淆数据库没有实际价值。 (从数据库中获取数据也会比简单地从磁盘上加载数据要慢得多。)

However, as an suggestion you might not want to store the full path on disk to the image in the database table, to aid portability in the future. (i.e.: Just store the portion of the path and filename off a 'known' base folder.)

但是,作为建议,您可能不希望将磁盘上的完整路径存储到数据库表中的映像,以便将来支持可移植性。 (即:只需将路径和文件名的一部分存储在“已知”基本文件夹中。)

#4


0  

Keep the image files as image files on the server to lower your DB load and allow the server to handle caching etc. Overall it really depends on the kind of images we're talking about. Small thumbnails (e.g. for file icons) wouldn't be that bad, but I wouldn't store whole images in the DB. In general I guess the file system approach would be faster.

将图像文件作为图像文件保存在服务器上以降低数据库负载并允许服务器处理缓存等。总体而言,它实际上取决于我们所讨论的图像类型。小缩略图(例如文件图标)不会那么糟糕,但我不会将整个图像存储在数据库中。总的来说,我猜文件系统方法会更快。

#5


0  

Lets investigate problem on web browser. When you load page with 10 pictures saved in database. You browser send new http request to the server. Each request init DB connection and server side script. Or just read static image from the file system.

让我们研究一下Web浏览器的问题。当您加载保存在数据库中的10张图片的页面时。您浏览器向服务器发送新的http请求。每个请求init数据库连接和服务器端脚本。或者只是从文件系统中读取静态图像。

what will be faster?

什么会更快?

Other part - get data from file system or database. If we do not use cache for the database (but for 10 GB of images you should have 10 GB RAM to cache this data). Database and HTTP server reads data from file system in any case. But I think HTTP browser reads data faster then Database server.

其他部分 - 从文件系统或数据库获取数据。如果我们不为数据库使用缓存(但对于10 GB的映像,您应该有10 GB RAM来缓存此数据)。无论如何,数据库和HTTP服务器都从文件系统中读取数据。但我认为HTTP浏览器比数据库服务器更快地读取数据。

Only one thing cons for the Database storage - very easy to migrate data from one server to other. But this is not matter for system performance.

数据库存储只有一个缺点 - 很容易将数据从一台服务器迁移到另一台服务器。但这对系统性能无关紧要。

And do not forget make path for images like /a/b/c/abc.jpg - it will be faster for big amount of images, then put all images in one directory.

并且不要忘记像/a/b/c/abc.jpg这样的图像的制作路径 - 对于大量图像来说会更快,然后将所有图像放在一个目录中。

#1


5  

advantages of approach 1:

方法1的优点:

  • Retrieving the flat file form webserver is more faster.
  • 检索平面文件表单Web服务器更快。
  • most of the web hosts likely to follow this approach.
  • 大多数网站主机可能会采用这种方法。
  • the file system is faster for flat file storage.
  • 平面文件存储的文件系统更快。

advantages of approach 2:

方法2的优点:

  • All your data is kept in one place, if you migrate your website/database the images will just be there
  • 您的所有数据都保存在一个地方,如果您迁移您的网站/数据库,图像就会存在
  • Its easier to sort/delete/etc...
  • 它更容易排序/删除/等...
  • Since you have to serve it via a PHP script, you can perform additional things such as security if required, or image processing (obviously you can do this with flat file too, but you have to make sure the security cant be bypassed by leaving the images in a public directory).
  • 由于您必须通过PHP脚本提供服务,因此您可以执行其他操作,例如安全性(如果需要)或图像处理(显然您也可以使用平面文件执行此操作,但您必须确保绕过安全性不能绕过安全性公共目录中的图像)。

considering performance approach 1 is best to proceed.

考虑性能方法1最好继续进行。

#2


2  

Storing on filesystem is faster.

存储文件系统的速度更快。

#3


1  

I'm be tempted to use the first approach as there's no real value in cluttering up the database with image data. (Fetching the data from the database will also be significantly slower than simply loading it off disk.)

我很想使用第一种方法,因为用图像数据混淆数据库没有实际价值。 (从数据库中获取数据也会比简单地从磁盘上加载数据要慢得多。)

However, as an suggestion you might not want to store the full path on disk to the image in the database table, to aid portability in the future. (i.e.: Just store the portion of the path and filename off a 'known' base folder.)

但是,作为建议,您可能不希望将磁盘上的完整路径存储到数据库表中的映像,以便将来支持可移植性。 (即:只需将路径和文件名的一部分存储在“已知”基本文件夹中。)

#4


0  

Keep the image files as image files on the server to lower your DB load and allow the server to handle caching etc. Overall it really depends on the kind of images we're talking about. Small thumbnails (e.g. for file icons) wouldn't be that bad, but I wouldn't store whole images in the DB. In general I guess the file system approach would be faster.

将图像文件作为图像文件保存在服务器上以降低数据库负载并允许服务器处理缓存等。总体而言,它实际上取决于我们所讨论的图像类型。小缩略图(例如文件图标)不会那么糟糕,但我不会将整个图像存储在数据库中。总的来说,我猜文件系统方法会更快。

#5


0  

Lets investigate problem on web browser. When you load page with 10 pictures saved in database. You browser send new http request to the server. Each request init DB connection and server side script. Or just read static image from the file system.

让我们研究一下Web浏览器的问题。当您加载保存在数据库中的10张图片的页面时。您浏览器向服务器发送新的http请求。每个请求init数据库连接和服务器端脚本。或者只是从文件系统中读取静态图像。

what will be faster?

什么会更快?

Other part - get data from file system or database. If we do not use cache for the database (but for 10 GB of images you should have 10 GB RAM to cache this data). Database and HTTP server reads data from file system in any case. But I think HTTP browser reads data faster then Database server.

其他部分 - 从文件系统或数据库获取数据。如果我们不为数据库使用缓存(但对于10 GB的映像,您应该有10 GB RAM来缓存此数据)。无论如何,数据库和HTTP服务器都从文件系统中读取数据。但我认为HTTP浏览器比数据库服务器更快地读取数据。

Only one thing cons for the Database storage - very easy to migrate data from one server to other. But this is not matter for system performance.

数据库存储只有一个缺点 - 很容易将数据从一台服务器迁移到另一台服务器。但这对系统性能无关紧要。

And do not forget make path for images like /a/b/c/abc.jpg - it will be faster for big amount of images, then put all images in one directory.

并且不要忘记像/a/b/c/abc.jpg这样的图像的制作路径 - 对于大量图像来说会更快,然后将所有图像放在一个目录中。