Facebookのような限定投稿を実現する方法

Question

現在Ruby on Railsを利用して、Facebookのようなアプリケーションを作成しております。
Facebookのようなウォールに投稿し、友達が見ることが出来るという構造になっています。

そこで、ある特定の友達（複数可）を除いた人のみに対して、投稿するという機能を実装したいと思っています。
一つひとつの投稿に対して公開範囲が設定してあるという状況をどのように実現するのか、というのが検討もつかなかったので、ぜひお力を貸しいただきたいです。

データ構造

ユーザー

• id
• name

友達

FriendRelationship

• id
• user_id
• friend_user_id

投稿

• id
• user_id
• content

実現したいイメージ

こちらの対象外の設定のようなものをやりたい。
パフォーマンス、コスト面まで配慮できるとこの上ないです...!

Answer 1

CREATE DATABASE hoge;
use hoge;

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) COMMENT='ユーザ';

CREATE TABLE `posts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  CONSTRAINT `fk_posts_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  PRIMARY KEY (`id`)
) COMMENT='記事';

CREATE TABLE `friend_relations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `friend_user_id` int(11) NOT NULL,
  UNIQUE KEY `uidx_friend_relations` (`user_id`,`friend_user_id`),
  CONSTRAINT `fk_friend_relations_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `fk_friend_relations_friend_user_id` FOREIGN KEY (`friend_user_id`) REFERENCES `users` (`id`),
  PRIMARY KEY (`id`)
) COMMENT='友人関係';

CREATE TABLE `limited_disclosures` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  UNIQUE KEY `uidx_friend_relations` (`post_id`,`user_id`),
  CONSTRAINT `fk_limited_disclosures_post_id` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`),
  CONSTRAINT `fk_limited_disclosures_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  PRIMARY KEY (`id`)
) COMMENT='限定公開設定';

CREATE TABLE `no_disclosures` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `post_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  UNIQUE KEY `uidx_friend_relations` (`post_id`,`user_id`),
  CONSTRAINT `fk_no_disclosures_post_id` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`),
  CONSTRAINT `fk_no_disclosures_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  PRIMARY KEY (`id`)
) COMMENT='限定公開設定';

INSERT INTO users (name) VALUES ('山田'), ('鈴木'), ('佐藤'), ('John');
INSERT INTO posts
(user_id, title)
VALUES
(1, '山田の全員に見える投稿'),
(1, '山田の鈴木にしか見えない投稿'),
(1, '山田の鈴木には見えない投稿'),
(2, '鈴木の全員に見える投稿'),
(2, '鈴木の佐藤にしか見えない投稿'),
(2, '鈴木の佐藤には見えない投稿'),
(3, '佐藤の全員に見える投稿'),
(3, '佐藤の山田にしか見えない投稿'),
(3, '佐藤の山田には見えない投稿'),
(4, 'Johnの全員に見える投稿');
INSERT INTO friend_relations (user_id, friend_user_id) VALUES (1, 2), (1, 3), (2, 1), (2, 3), (3, 1), (3, 2);
INSERT INTO limited_disclosures (post_id, user_id) VALUES (2, 2), (5, 3), (8, 1);
INSERT INTO no_disclosures (post_id, user_id) VALUES (3, 2), (6, 3), (9, 1);

山田の友達の記事を取得するSQLです。

SELECT 
posts.* 
FROM posts 
JOIN friend_relations 
  ON friend_relations.user_id = 1
WHERE posts.user_id = friend_relations.friend_user_id

これに対してlimited_disclosuresとno_disclosuresをLEFT JOIN

SELECT 
  posts.*, limited_disclosures.*, no_disclosures.*
FROM posts 
JOIN friend_relations 
  ON friend_relations.user_id = 1
LEFT JOIN limited_disclosures
  ON limited_disclosures.post_id = posts.id
LEFT JOIN no_disclosures
  ON no_disclosures.post_id = posts.id
WHERE posts.user_id = friend_relations.friend_user_id;

+----+---------+--------------------------------------------+------+---------+---------+------+---------+---------+
| id | user_id | title                                      | id   | post_id | user_id | id   | post_id | user_id |
+----+---------+--------------------------------------------+------+---------+---------+------+---------+---------+
|  4 |       2 | 鈴木の全員に見える投稿                     | NULL |    NULL |    NULL | NULL |    NULL |    NULL |
|  5 |       2 | 鈴木の佐藤にしか見えない投稿               |    2 |       5 |       3 | NULL |    NULL |    NULL |
|  6 |       2 | 鈴木の佐藤には見えない投稿                 | NULL |    NULL |    NULL |    2 |       6 |       3 |
|  7 |       3 | 佐藤の全員に見える投稿                     | NULL |    NULL |    NULL | NULL |    NULL |    NULL |
|  8 |       3 | 佐藤の山田にしか見えない投稿               |    3 |       8 |       1 | NULL |    NULL |    NULL |
|  9 |       3 | 佐藤の山田には見えない投稿                 | NULL |    NULL |    NULL |    3 |       9 |       1 |
+----+---------+--------------------------------------------+------+---------+---------+------+---------+---------+

ここから絞り込みます

SELECT 
  posts.*, limited_disclosures.*, no_disclosures.*
FROM posts 
JOIN friend_relations 
  ON friend_relations.user_id = 1
LEFT JOIN limited_disclosures
  ON limited_disclosures.post_id = posts.id
LEFT JOIN no_disclosures
  ON no_disclosures.post_id = posts.id
WHERE posts.user_id = friend_relations.friend_user_id
  AND (limited_disclosures.id IS NULL OR limited_disclosures.user_id = 1)
  AND (no_disclosures.id IS NULL OR no_disclosures.user_id != 1);

+----+---------+--------------------------------------------+------+---------+---------+------+---------+---------+
| id | user_id | title                                      | id   | post_id | user_id | id   | post_id | user_id |
+----+---------+--------------------------------------------+------+---------+---------+------+---------+---------+
|  4 |       2 | 鈴木の全員に見える投稿                     | NULL |    NULL |    NULL | NULL |    NULL |    NULL |
|  6 |       2 | 鈴木の佐藤には見えない投稿                 | NULL |    NULL |    NULL |    2 |       6 |       3 |
|  7 |       3 | 佐藤の全員に見える投稿                     | NULL |    NULL |    NULL | NULL |    NULL |    NULL |
|  8 |       3 | 佐藤の山田にしか見えない投稿               |    3 |       8 |       1 | NULL |    NULL |    NULL |
+----+---------+--------------------------------------------+------+---------+---------+------+---------+---------+

いやあもっといい方法あるのかな。私も知りたいですね。

Comments

[yukitoto]

ありがとうございます！！
確かに別テーブルに持たせるのはありですね！

おそらくどの手法を使っても同じ問題にぶち当たると思いますが、データ数が膨大になるのが怖いですね..(パフォーマンスも...

[popobot]

自分が作っても回答のようなイメージです。
なおパフォーマンスを気にされていますが、想定ユーザ数はどのぐらいなのでしょうか。
この手の機能って使いこなすのも難しいし、例外的な投稿に使われるようなきがするので、そんなにデータ数多くならないような気がしました。具体的に想定される数字を計算してみてはいかがでしょうか。

[退会済みユーザー]

> データ数が膨大になるのが怖いですね..(パフォーマンスも...

そうですねー、ここに書かれていない要件があるだろうから正確には言えないですけど、こういうアプリで重くなるのはたいていページング処理です。

単純にページングを実装したらこうなりますが

```
-- 2001ページ目を取得
SELECT
posts.*,
FROM posts
JOIN friend_relations
ON friend_relations.user_id = 1
LEFT JOIN limited_disclosures
ON limited_disclosures.post_id = posts.id
LEFT JOIN no_disclosures
ON no_disclosures.post_id = posts.id
WHERE posts.user_id = friend_relations.friend_user_id
AND (limited_disclosures.id IS NULL OR limited_disclosures.user_id = 1)
AND (no_disclosures.id IS NULL OR no_disclosures.user_id != 1)
ORDER BY posts.created_at DESC
-- ここから下追加
LIMIT 50
OFFSET 100000;
```

こんなクエリを書いたらひどいことになります。
この問題にどう対応するかというのよく話題になっていて、今までにもたくさんの人が記事を書いています。

http://blog.yappo.jp/yappo/archives/000835.html
http://togetter.com/li/640847
http://www.slideshare.net/kazeburo/isucon-summerclass2014action2final

こっからはただのあてずっぽうの勘です。

- postsのUPDATE頻度は１ユーザあたり１日数回
- 上記のノウハウを活用する
- アプリケーションキャッシュ
- (可能なら)HTTPアクセラレータ
- ハードウェアにRDSのdb.m4.4xlarge(16 CPU, 64GB)

postsが千万件 楽勝
postsが数千万件 下手なクエリ投げると固まるけどまあ動くんじゃない
postsが億以上 下手なクエリ投げると死ぬ。サイト遅いんだけどとよく言われる。MySQL以外のデータストアの検討か、札束で殴り倒すか。