人気記事（ランキング）表示に関するDB設計や方法

質問

DBのテーブル設計と実装について教えて頂けると嬉しいです。

登録制サイトで、記事を投稿して閲覧者がお気に入りに登録できるような仕組みを作っています。
トップページ等で「人気の記事」を表示する際、お気に入り登録数が多いものを表示させたいと思っています。
このような場合、どのようなテーブル設計や方法を取るのが良いのでしょうか？

３つ方法を思いつきますが、どれも一長一短なのかなという気がしています。

（テーブル設計の例）
　「posts」テーブル　　：　post_id, user_id, text
　「favorites」テーブル：　favorites_id, user_id, post_id
　「users」テーブル　　：　user_id, user_name

（方法１）お気に入り登録数をテーブルに持たない（都度カウント処理する）
トップページ表示の都度、「favorites」各記事のお気に入り登録数をカウントして（post_id で group byしてcountする）、登録の多い記事を表示させます。シンプルな方法ですが、レコード数が多い場合の負荷を心配しています。

（方法２）お気に入り登録数をテーブルに持つ
お気に入り登録数を「posts」テーブル内に保持させます。

　「posts」テーブル　　：　post_id, user_id, text, favorite_count

閲覧者がお気に入り登録した場合は「favorites」テーブルへの追加を行うと供に「posts」テーブルの「favorite_count」を更新します。トップページ表示の際は「posts」テーブルの「favorites_count」を見に行って多いものを表示させるだけなので負荷の心配は減ります。しかし、冗長な？データをテーブルに持つのは何か嫌な気がしますし、またプログラム実装が少し困難になります。

（方法３）人気の記事テーブルを別に持つ
バッチ処理を1日1回等走らせ、登録数の多い記事をサーチして「人気の記事」テーブルに格納します。トップページでは、この「人気の記事」テーブルを見に行って表示させるだけになりますので負荷が減ります。しかし、リアルタイムな情報を表示できないのが残念です。

環境は、MySQL5.1、PHP5.2.17　となります。
アドバイス等頂けましたら嬉しいです。

回答No.2

Siegrune

ご自身では、もう解決して処理を進めておられるかもしれませんが。
（それなら無視しておいてください。）

レスポンス対策で、（方法１）の正規形を崩して（方法２）をとるというのは、別に問題ありません。
認識しておられないようですが、（方法３）も正規形を崩しておられます。
といいますのは、例えば、記事に問題があり緊急で削除する必要があるときに、
１つのデータを削除するために、２つのテーブルを削除する必要がでるからです。
・・・方法２は冗長な項目を持っていますが、方法３は冗長なレコードを持っています。

ということで、
（方法２）における
「favorites」テーブル追加時に発生する「posts」テーブル更新の負荷増大
（お気に入りクリック時の負荷増大＋レスポンス低下）と、
（方法３）におけるリアルタイムな情報を表示できない点および
緊急の修正あるいは、記事の修正の反映が翌日になる運用上のデメリットと
この２点を天秤にかけて選ばれるのがよいかと思います。

特に記事を投稿した人が、翌日にならないとトップページのほうだけ反映されないという
クレームを出さないならば、どちらでもいいと思いますし、
タブン私も（方法３）をとる可能性が大きいです。

迂回策・・・方法４）
「TopPage」テーブル　　：　favorite_count,post_id, user_id
を作って夜間バッチ処理で上位ｎレコード（TOPページに表示する分）だけ作成する。
プライマリキーはfavorite_count,post_id（, user_id）
()は同一post_idがありuser_idがないとユニークにならない場合。
表示は、「TopPage」テーブルと「posts」テーブルをjoinする
といった方法もあります。
アクセス数が多いとjoinする分ＤＢサーバの負荷が高くなるので
これもどれくらいのアクセスを想定するかとＤＢサーバの能力しだいで最良か最悪かが変わります。
（TOPページなので当然アクセス数は多いはずですし。）

回答No.1

OrangeCup150

方法その３のリアルタイム性については、人気記事の集計処理を Windows であればタスクスケジューラ、 Linux などであれば cron などで５分から６０分程度の間隔で起動し（ようするに自動バッチ処理）人気の記事テーブルを更新すれば一般的な利用者のニーズは満たせるかと思います。
また、人気の記事テーブル（データベース）にデータを置くよりも共有メモリ領域やテキストファイルに人気の記事のデータを置く（キャッシュしておく）方がＤＢにアクセスするよりも処理負荷が低く、処理速度が高速になることが考えられます。ただ、実装の手間がやや増えると思いますので参考に留める程度にしていただければいいかと思います。

とりあえず、タスクスケジューラもしくは cron などで人気記事の集計処理を実行する方法を検討してみてはどうでしょうか？

なお、方法その１は violinmaker さんの懸念の通り会員・記事数が増えたときに問題になることが考えられます。逆に、会員数が小数固定であれば、実装が簡潔になるのでいいかと思います。
方法その２は、データベース設計の基本を守るべきですので止めるべきと思います。

お礼

ありがとうございます。
こういった処理は初めてなので、頂いたコメントが参考になります。
確かに3番目の方法が負荷や将来性の面から良さそうに思います。（一般的にとられている手法なのでしょうか？）

実は方法２に傾きつつありましたが、やはりデータベース設計からすると好ましくないということですよね。

方法３のバッチ処理で人気記事の集計を行う形で検討してみたいと思います。