アクセスログテーブルの構造について

質問

このジャンルでお願いします。

よくアクセスログの解析を無料で（リンクを貼ったりして）行ってくれるサービスがありますが、
そのデータベースの構造というかテーブルの構造はどのようになっているのでしょうか？
例えば、膨大なレコード数になると思うので
それを利用するユーザー毎にテーブルを分けるとかするのでしょうか？
あるいはユーザー毎、年毎、月毎、日毎、に分けるとか？
本来ならテーブルを分割したりするのは違うんじゃないかなぁと思ったりするのですが、
なので次のように、

CREATE TABLE `accesslog` (
`id` int NOT NULL auto_increment,
`user_id` varchar(16),
`created` datetime,
`ip` varchar(64),
FOREIGN KEY(`user_id`) REFERENCES `user`(`id`)
);

ユーザーも全ての期間のデータも1つのテーブルで管理するのが普通だとは思うのですが、
アクセスログだけは利用するユーザー数にもよりますがやはり膨大になりますよね？
そうすると1つのテーブルだと無理があると思うのです。
あるいは単にテキストファイルとして保存する方法もあるのかな？と思うのですが、
やはりなにかとＳＱＬで操作したいのでMySQLなどのテーブルの設計が知りたいです。

回答No.3

yambejp

たとえばアパッチのログであれば以下の通り

http://httpd.apache.org/docs/2.1/ja/logs.html

集計単位ごとに中間的に解析したデータを単位時間ごとで
ログしていけば、さほど大きなデータになることはないと思います。

(1)一次的に冗長なデータをとる
(2)定期的に集計単位ごとに集計して中間テーブルにおとしこむ
(3)いらない一次データを削除
という手法が有効だとおもいますが

>などのアクセス解析サイトがどのようにデータを保存してそれを利用しているのか
>が知りたいのです。

というのはそれぞれのサイトの集計ポリシーの問題なので特殊な技術を
もっているでしょうから回答しようがありません。
どうしても興味があるならそのサイトの管理者に聞くのが筋でしょう

>（yahooとか）がそのようなアクセス解析サイトを利用

ユニークユーザーが1000万人いて、1人1リクエストおくってくるなら
結局は1000万件の冗長なデータを保持しなくてはいけません。
結局集計単位をどこにもつかが重要でしょう

ただ、検索サイトがログっているのはユーザーの情報よりはむしろ
検索ワードなどだと思うので、それを集計単位にデータをプールすると
予想されます。

いずれにしろ大規模なアクセスがあるところは、ちんけな解析サイトは
つかわないし、専用の解析ツールをつかって集計するでしょう。
そうすることによってマーケティング情報を収集して、収入を得ているわけですから

お礼

ご回答ありがとうございます。
＞(1)一次的に冗長なデータをとる
＞(2)定期的に集計単位ごとに集計して中間テーブルにおとしこむ
＞(3)いらない一次データを削除
なるほど、このローテーションの流れはイメージできたのですが、
＞集計単位ごとに中間的に解析したデータ
というのが自分が想像してるやつだとそこまでデータの行数が減る感じではないのです・・・
例えば、同一の訪問者が2時から3時からまでの間に3回訪れたというデータなら

CREATE TABLE `accesslog_hour_from_2_to_3` (
`id` int NOT NULL auto_increment,
`user_id` varchar(16),
`ip` varchar(64),
`cnt` int,
FOREIGN KEY(`user_id`) REFERENCES `user`(`id`)
);

[id]　[user_id]　　　　　[ip]　　　　　　[cnt]
1　　user1　　　111.111.111.111　　　3

という生ログ（一次データ）では3行のデータを1行に纏めることはできますが、
その訪問者がどこから訪問してきたのかというデータは1行に纏めることはできなくて
例えば、その訪問者が2時から3時の間にA,B,Cというサイトから訪問してきた場合、
生ログ（一次データ）も3行で中間テーブルに押し込むデータも3行になると思うのです。

CREATE TABLE `accesslog_hour_from_2_to_3` (
`id` int NOT NULL auto_increment,
`user_id` varchar(16),
`ip` varchar(64),
`cnt` int,
`referer` varchar(64),
FOREIGN KEY(`user_id`) REFERENCES `user`(`id`)
);

[id]　[user_id]　　　　　[ip]　　　　　　[cnt]　[referer]
1　　user1　　　111.111.111.111　　　1　　　http://www.yahoo.co.jp/
2　　user1　　　111.111.111.111　　　1　　　http://www.google.co.jp/
3　　user1　　　111.111.111.111　　　1　　　http://www.goo.ne.jp/

yambejpさんが仰る中間テーブルとはこのような構造なのでしょうか？

回答No.2

yambejp

ごめんなさい・・・
補足によって状況がわからなくなってきたのですが

ここでいうアクセスログって、WEBのアクセスログをMySQLで
解析したいってことでよいのですよね？
WEBのログはSQLを利用しないでもwebalizerというツールで十分というのが
前回の回答です。

なんらかの理由でどうしてもMySQLでログ解析をしたいのであれば

＞ユーザー毎や月毎に新たにテーブルを作る

ようなことはないでしょう。
膨大だろうが冗長にデータをとっておくか、とったデータから集計期間に
あわせて中間処理をしたデータを別テーブルで管理するかのどちらかだと
思います

お礼

ご回答ありがとうございます。
＞ここでいうアクセスログって、WEBのアクセスログをMySQLで
＞解析したいってことでよいのですよね？
MySQLで解析したいというのはその通りなのですが、
自分でもWEBのアクセスログというのがいまいち分からないというか、
例えば、「`ip` varchar(64)」のカラムはPHPの$_SERVER["REMOTE_ADDR"]などを使えば
取得できますよね。ただ、様々な情報を取得しようと思ったら
たぶんApache 生ログからしか取得できない（？）と思うのですが、
http://ax.xrea.com/
などのアクセス解析サイトがどのようにデータを保存してそれを利用しているのか
が知りたいのです。
例えば1つのサイトのアクセスを解析するとして、
仮に大きなサイト（yahooとか）がそのようなアクセス解析サイトを利用してたとします。
その１つのサイトの1日のデータだけで1000万行を越えると思うんです。。
MySQLとかの1つのテーブルでそのような1000万行を超えてしまうと（推測なのですが）重くて使えないですよね？
そこまで大きなサイトでなくても、アクセス解析サイトをそのようなサイトが複数利用したら
1日どころが数時間で1000万行を超えてしまうと思います。1つのテーブルにデータを挿入してたら。
なのでやはりその利用するユーザ毎、期間毎にテーブルを新規に作って対応してるのかなぁと思ってしまうのです。。
でもそれだとテーブルの本来の在りかとして違うような気がしますし、、実際はどうなのでしょうか・・？

回答No.1

yambejp

webalizer以上の必要はなような気がしますが
やるならapacheの各行を定期的に流し込むような仕組みがよいのでは？
そうなると自然とaccess.logの標準的なデータにあわせたテーブル構成に
なると思います

お礼

ご回答ありがとうございます。
すいません、仰ってることの具体的なイメージができないのですが、
つまりそれはユーザー毎や月毎に新たにテーブルを作るのか
それとも1つのテーブルに全て（全てのユーザー、全ての期間）のレコードを管理するのでしょうか？