在数据库中存储照片参考的最有效方法
我目前希望存储来自大约 100/200k 用户的大约 350 万张照片.我只在 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:
- 优化 MyISAM 查询
- 优化 InnoDB 查询
相关文章