問題描述
我目前希望存儲來自大約 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 查詢
這篇關于在數據庫中存儲照片參考的最有效方法的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！