PHPとSQLiteでアクセス解析を作る際の日付データ型とIPアドレスの話

PHPとSQLiteで運営サイトのPV数等を記録する仕組みを作っていたのですが、データ容量が肥大化したので設計を見直してみたお話。自分用の備忘録みたいなものです。

本当にGoogle Analyticsは必要なのか

アクセス解析といえばGoogle Analyticsですよね。いまや誰でもGoogleアカウントを持っている時代、法人はもちろんのこと、個人でサイト運営するにも普段使っているGoogleアカウントからサクっとGoogle Analyticsの登録をしてタグを貼り付ければ、あっという間に運営サイトのアクセス解析が開始できます。

これはこれで素晴らしいのですけれど、運営するサイトが増えてくるとGoogle Analyticsって使いづらくないですか？

ぼくが使いこなせていないだけの可能性は高いのですがｗ　運営サイトが20を超えはじめたくらいから、各サイトを切り替えつつアクセス解析をチェックするのが大変面倒に感じてきました。そもそも普段はPVとユニークアクセスの推移が見られれば十分だとも感じていて、それ以外の細かなデータは本当にたまにチェックできれば良いと思っています。

Google AnalyticsのAPIを使って各サイトのアクセスデータを収集する方法もあるのですが、この使い方がまたややこしい。

たかがPVを取得するだけのためにそんなに手間かけたくない。

ということで、うちでは各サイトのApacheログを自動ダウンロードして、ローカルのSQLiteに格納し、過去1週間分のPVと訪問者数の推移をレポートにして、自分へメールするような仕組みを作っています。…いえ、いました。

最近はレンタルサーバーの軽量化が流行っているのか、Apache自体が減少し、LiteSpeedやnginxを使っているところが増え、Apacheログが取得できなかったり、取得できてもリアルタイムではなかったりするので、もういっそリアルタイムでWeb上のSQLiteにアクセスログを記録すれば良いかな、と思い直しました。

アクセス解析用のSQLiteテーブルの例

■PV記録用

CREATE TABLE access_pv (
	ymd text,
	pv integer,
	PRIMARY KEY (ymd)
);

■UU記録用 (Unique User)

CREATE TABLE access_uu (
	ymd text,
	ip text,
	uu integer,
	PRIMARY KEY (ymd, ip)
);

…ひどいでしょうｗ

だいぶ昔に作った…というのは言い訳ですが、最初はSQLite自体ローカルのPCに置いていて、各Webサイトからダウンロードしてきたログからカウントした値をつっこんでいるだけだったので、データ型も何も気にしていませんでした。

そもそもSQLiteには厳密には日付型とかなくて、内部的には文字列で持っているだけなんですね。じゃあ、いちいち "2020-06-24 00:00:00" なんてdatetime風の書き方をせずとも、"20200624" という8桁の文字列で格納すれば良いじゃん、くらいの軽い気持ちで設計して運用していたわけです。

DBが膨れ上がってきたのでデータ型を見直すことにした

今まではローカルにSQLiteを置いていたので数十ギガのサイズになっても特に気にしていませんでしたが、Webサーバー側に置くとなるとそんなことも言っていられません。

具体的には月間PVが10万そこそこ、ユニークアクセスが1～2万人といった程度のサイトでも1ヶ月分のアクセス解析だけでSQLiteのDBサイズが20MBくらいになりました。まぁたかが20MBといえば20MBですが、この10倍以上の規模のサイトにも導入しようと思っているので、少なくとも200MBは超えるし、更に増加してくるとGB単位かぁ。デイリーバックアップのことも考えると、ちょっとデータ容量をダイエットしたほうが良いのではないか、と思い始めました。

そして、このように変更。

■PV記録用

CREATE TABLE access_pv (
	ymd integer,
	pv integer,
	PRIMARY KEY (ymd)
);

■UU記録用 (Unique User)

CREATE TABLE access_uu (
	ymd integer,
	ip integer,
	uu integer,
	PRIMARY KEY (ymd, ip)
);

何が変わったかというと、ymdとipをintegerにしただけ。

でもこれだけでSQLiteのデータサイズがほぼ半分にまで減少しました。

日付については元々 "20200624" という文字列なので8バイトくらいしか使わないし、integerにしたところで大した影響ないと思っていたのですが、SQLiteは入れる数値の最大値によって1～8バイトまで可変で使ってくれるようで劇的に容量が低下しました。

また、IPアドレスについては "192.168.0.1" みたいな文字列で格納していたのをPHPのip2long関数によって数値型へ変更。

↓具体的にはこんな感じですね。

<?php
$ip = ip2long($_SERVER['REMOTE_ADDR']);
?>

これもまた劇的に効果がありました。1レコード単位ではわずか数バイトの節約ですが、月に20万人くるとなると20万レコードですから馬鹿にできません。

ユニークアクセスの考え方は色々ありますが、うちではクッキー等を使うのはちょっとどうなのかなと思って、単にIPアドレス単位で判断しています。過去1ヶ月内に同じIPアドレスだったら訪問者数のカウントは1つだけ、みたいに。

SQLiteにアクセスを記録する例

ほぼ余談ですが、先程作ったアクセス解析用のテーブルにPVやUUを記録するPHPがこちら。

$db = new PDO('sqlite:[データベースファイル]');

$st = $db->prepare('INSERT OR IGNORE INTO access_uu (ymd,ip,uu) VALUES (?,?,1)');
$st->execute(array((int)date('Ymd'),(int)ip2long($_SERVER['REMOTE_ADDR'])));

$query = 'INSERT INTO access_pv (ymd,pv) VALUES (?,1)'.
		 ' ON CONFLICT (ymd)'.
		 ' DO UPDATE SET pv=pv+1';
$st = $db->prepare($query);
$st->execute(array((int)date('Ymd')));

実際の運用ではbot判定したり、もうちょっとだけ複雑な処理が入りますが、基本は変わりません。

なるべくアクセス記録で負荷をかけないよう、INSERT OR IGNOREを使ったり、INSERT ON CONFLICT文でUPSERT^※をしたりしています。

まとめ

たくさんのサイトを運営しているとGoogle Analyticsで全サイトを見てまわるのは骨なので、自前のアクセス解析…というほどでもない…昔ながらの「アクセスカウンター」みたいなものを作った。

けれど、ローカル運用を前提としていたため、データサイズが肥大化し、今回、日付とIPアドレスを数値型へ変更してみたら、劇的に効果があった。

…という、単なる設計ミスの恥ずかしい話ですが、ぼくは鳥頭なので忘れないよう、備忘録として記事にしてみました。