Create one collection for each user vs create one collection for all user
I'm using PHP and MySQL for a social network system under Ubuntu environment.
I have MySQL table named user_feed , in this table I save feeds as feed_id for each user my table structure in MySQL is:
|user_feed_id | user_id | content_id | seen |
and I have table user_follow that contain data followed by every user , so each user has set of record for what he/she followed content.
Table structure:
follow_id | user_id | content_id |
In user_feed table I have more than 170 million records, and every user has set of records and in user_follow table I have more than 500 000 record.
I currently work to migrate from MySQL to MongoDB., so I need to convert this table to collection in MongoDB. I think to build my collection for user_feed and user_follow as follow:
Create collection for each user and this collection has three document, one for follow ids and another one for feed_ids, so when I handle user profile I will run my query for one collection for each member:
Each collection name is refer to user_id like :
user_id_1 as collection name
{ user_id: '1'}
{
feed_ids: [
{ content_id: '10', 'seen' : 1 },
{ content_id: '11', 'seen' : 0 },
{ content_id: '12', 'seen' : 1 },
{ content_id: '13', 'seen' : 1 }
]
}
{
follow_ids: [
{ content_id: '10' },
{ content_id: '20'},
{ content_id: '23'},
{ content_id: '24'}
]
}
user_id_2 as collection name
{ user_id: '2'}
{
feed_ids: [
{ content_id: '14', 'seen' : 1 },
{ content_id: '15', 'seen' : 0 },
{ content_id: '16', 'seen' : 0 },
{ content_id: '17', 'seen' : 0 }
]
}
{
follow_ids: [
{ content_id: '22' },
{ content_id: '23'},
{ content_id: '24'},
{ content_id: '25'}
]
}
so if I have 70 000 user then I need to create 70 000 collection in MongoDB
and I have another option to create it like :
all user feeds for one collection and each user has one document inside collection like:
{
user_id: '1',
feed_ids: [
{ content_id: '10'},
{ content_id: '11'},
{ content_id: '12'}
],
follow_ids: [
{ content_id: '9'},
{ content_id: '11'},
{ content_id: '14'}
]
}
and the data in these tables grow very significantly and I need the collections and documents to be able to do all operation like (insert, update, select,..)
My feed_ids and follow_ids is grow very very significantly and my queries is :
select content_id from user_feed where user_id =1 limit 10 offset 20;
update user_feed set seen = 1 where user_id =1
select count(content_id) from user_feed where seen = 0;
select content_id from user_follow where user_feed_id =1 limit 10 offset 20;
insert into user_feed (user_id,content_id,seen) values (1,23,0);
is the first option is the optimal solution for my use case or the second one?
Thanks.
One collection for each user would never scale due to nssize limitations (2GB) since this would mean you are limited to 3 million users per database (assuming that database only holds users...). Once you start spanning such a thing over multiple databases as well then you are really starting to get into implementation problems.
This setup has no performance benefits since the main benefit would be the lock and that is on database level. I would still think my first point in the paragraph above would hold over the lock even if it was implemented per collection. As an added note you would get inefficient use of space due to the way that MongoDB treats a single document when updating unbound arays, it will create a "swiss cheese" effect and cause massive fragmentation lowering performance further.
So just based on that, no, I would not make one collection per user.
链接地址: http://www.djcxy.com/p/88752.html上一篇: 哪种方式会更有效率？