最有效的方式来存储在数据库中的照片参考 [英] The most efficient way to store photo reference in a database
问题描述
目前，我正在寻找存储大约350万照片的距离大约为100/20万用户。我只使用AWS上的MySQL数据库。我的问题是关于存储照片参考的最有效方式。我只知道两种方式，我正在寻找一个专家的意见。
I'm currently looking to store approximately 3.5 million photo's from approximately 100/200k users. I'm only using a mysql database on aws. My question is in regards to the most efficient way to store the photo reference. I'm only aware of two ways and I'm looking for an expert opinion.
选项A
与PHOTO_URL列用户表，该列中我将建立一个逗号分隔的照片的列表，无论保持名称和排序顺序。业务逻辑将处理提取照片名称的路径和追加照片尺寸。缺点是加工费用。
A user table with a photo_url column, in that column I would build a comma separated list of photo's that both maintain the name and sort order. The business logic would handle extracting the path from the photo name and append photo size. The downside is the processing expense.
数据库例子
"0ea102, e435b9, etc"
业务逻辑将修建从照片名称
Business logic would build the following urls from photo name
/0e/a1/02.jpg
/0e/a1/02_thumb.jpg
/e4/35/b9.jpg
/e4/35/b9_thumb.jpg
选项B - 关系表加入了对用户表具有以下字段。我只是担心我可能有潜在的数据库性能问题。
Choice B - Relational Table joined on user table with the following fields. I'm just concerned I may have potential database performance issues.
pk
user_id
photo_url_800
photo_url_150
photo_url_45
order
没有任何人有更好的解决方案有什么建议？
Does anybody have any suggestions on the better solution?
推荐答案
最好的和最常见的答案是：选项B - 关系表用户表具有以下字段加入
The best and most common answer would be: choice B - Relational Table joined on user table with the following fields.
id
order
user_id
desc
photo_url_800
photo_url_150
photo_url_45
date_uploaded
或混合，其中，您分别保存的文件名，并与您的业务逻辑层添加的照片目录。
Or a hybrid, wherein, you store the file names individually and add the photo directory with your business logic layer.
我的分析，你的第一个选项是一个不好的做法。逗号分隔的字段是不可取的数据库。这将是很难为你更新这些领域并在其上添加描述。
My analysis, your first option is a bad practice. Comma separated fields are not advisable for database. It would be difficult for you to update these fields and add description on it.
关于表的优化，你可能想看看这些文章：
Regarding the table optimization, you might want to see these articles:
- Optimizing MyISAM Queries
- Optimizing InnoDB Queries
这篇关于最有效的方式来存储在数据库中的照片参考的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
