最佳方式缩放数据，减少加载时间，使我的webhost快乐 [英] Best way to scale data, decrease loading time, make my webhost happy
问题描述
对于Facebook应用程序，我必须在我的MySQL数据库中存储一个用户的朋友列表。这个列表是从我的db请求的，与其他数据等相比。
For a Facebook Application, I have to store a list of friends of a user in my MySQL database. This list is requested from my db, compared with other data, etc.
目前，我将这个朋友列表存储在用户表中，朋友的uids在一个文本字段中，使用|作为分隔符。例如：
Currently, I store this list of friends within my user table, the uids of the friends are put together in one 'text' field, with a '|' as separator. For example:
ID - UID - NAME - FRIENDS => 1 - 123456789 - John Doe - 987654321 | 123456 | 765432
ID - UID - NAME - FRIENDS => 1 - 123456789 - John Doe - 987654321|123456|765432
我的PHP文件请求这一行，通过展开该字段（'|'）提取朋友列表。这一切都很好，每1000个用户大约5MB磁盘空间。
My PHP file requests this row and extracts the list of friends by exploding that field ('|'). This all works fine, every 1000 users are about 5MB diskspace.
现在的问题：
对于一个额外的功能，我还需要保存用户的朋友的名字。我可以通过不同的方式做到这一点：
For an extra feature, I also need to save the names of the friends of the user. I can do this in different ways:
1）将此数据保存在额外的表中。例如：
1) Save this data in an extra table. For example:
ID - UID - NAME => 1 - 1234321 - Jane Doe
ID - UID - NAME => 1 - 1234321 - Jane Doe
如果我需要ID为1234321的朋友的名字，我可以从这个表中请求名字。然而，问题是这个表将持续增长，直到Facebook上的所有用户都被索引（> 5亿行）。我的webhost不会这样！这样的表将占用大约25GB的磁盘空间。
If I need the name of the friend with ID 1234321, I can request the name from this table. However, the problem is that this table will keep growing, until all users on Facebook are indexed (>500million rows). My webhost is not going to like this! Such a table will take about 25GB of diskspace.
2）另一个解决方案是扩展保存在用户表中的数据，通过在朋友的UID中添加名称字段（带有额外的分隔符，让我们使用'，'）。例如：
2) Another solution is to extend the data saved in the user table, by adding the name to the UID in the friends field (with an extra separator, let's use ','). For example:
ID - UID - NAME - FRIENDS => 1 - 123456789 - John Doe - 987654321，Mike Jones | 123456 | 765432，Rick Smith
ID - UID - NAME - FRIENDS => 1 - 123456789 - John Doe - 987654321,Mike Jones|123456,Tom Bright|765432,Rick Smith
对于此解决方案，我必须更改脚本，添加另一个额外的爆炸（'，'）我不知道这将需要多少额外的磁盘空间...但是数据不容易处理这种方式！
For this solution I have to alter the script, to add another extra explode (','), etc. I'm not sure how many extra diskspace this is going to take... But the data doesn't get easy to handle this way!
3）第三解决方案给出了所有数据的良好概述，但将导致数据库是巨大的。在这个解决方案中，我们创建一个朋友表，每一个友谊一行。例如：
3) A third solution gives a good overview of all the data, but will cause the database to be huge. In this solution we create a table of friends, with a row for every friendship. For example:
ID - UID - FRIENDUID => 1 - 123456789 - 54321
ID - UID - FRIENDUID => 1 - 123456789 - 54321
ID - UID - FRIENDUID => 3 - 123456789 - 65432
ID - UID - FRIENDUID => 3 - 123456789 - 65432
ID - UID - FRIENDUID => 2 - 987654321 - 54321
ID - UID - FRIENDUID => 2 - 987654321 - 54321
ID - UID - FRIENDUID => 4 - 987654321 - 65432
ID - UID - FRIENDUID => 4 - 987654321 - 65432
给出了所有友谊的非常好的概述。然而，拥有大约5亿用户，我们假设每个用户平均有300个友谊，这将创建一个有150亿行的表。我的主机绝对不会喜欢这...我想这种表将需要很多磁盘空间...
As you can see in this example, it gives a very good overview of all the friendships. However, with about 500million users, and let's say an average of 300 friendships per user, this will create a table with 150billion rows. My host is definitely not going to like that... AND I think this kind of table will take a lot of diskspace...
所以...如何解决这个问题？你认为，什么是最好的方式来存储用户的Facebook的UIDs +朋友的名字？如何扩展这种数据？
So... How to solve this problem? What do you think, what is the best way to store the UIDs + names of friends of a user on Facebook? How to scale this kind of data? Or do you have another (better) solution than the three possibilities mentioned above?
希望你能帮助我！
推荐答案
我同意Amber，解决方案1将是最有效的方式来存储这些数据。如果你想坚持你当前的方法（类似于解决方案2），你可能想考虑将友谊数据存储为JSON字符串。它不会产生最短的字符串，但会很容易解析。
I agree with Amber, solution 1 is going to be the most efficient way to store this data. If you want to stick with your current approach (similar to solution 2), you may want to consider storing the friendship data as a JSON string. It won't produce the shortest possible string, but it will be very easy to parse.
要保存数据：
$friends = array(
'uid1' => 'John Smith',
'uid2' => 'Jane Doe'
);
$str = json_encode($friends);
// save $str to the database in the "friends" column
获取数据：
// get $str from the database
$friends = json_decode($str, TRUE);
var_dump($friends);
这篇关于最佳方式缩放数据，减少加载时间，使我的webhost快乐的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
