PHPのテクメモ

レコメンデーションの仕組みで一般的となっている「この記事を読んだ人はこんな記事も見ています」。これをSQLで実現するにはどうすればいいかを模索していて、少し先が見えてきたのでメモしておく。

まずテーブルとしてusers_pagesを作成し、カラムは2つ（user_idとpage_id）。カラムの型はそれぞれinteger。本来はusersテーブルとpagesテーブルがあるが、とりあえずここでは考慮しない。user_idとpage_idの組み合わせを主キーとし、それぞれのカラム単体でもインデックスを張っておく。こんな感じ。

CREATE TABLE `users_pages`
(
`user_id` int(11) NOT NULL,
`page_id` int(11) NOT NULL,
PRIMARY KEY (`user_id`,`page_id`),
KEY `user_id` (`user_id`),
KEY `page_id` (`page_id`)
);

訪問者が訪れるたびに、user_idを発行し、user_idがページを閲覧するたびに、当該user_idと閲覧したページの番号（page_id）の組み合わせを行として挿入していくイメージ。

「この記事を見た人は」ということなので、記事ごとにSQL文を流す方法を考える。つまりpage_id=n（nはページ番号）。一番簡単に思いつくのはこんな感じと思う。

1. 当該page_idを閲覧したuser_idを検索する
2. 検索結果のuser_idが閲覧したpage_idを検索する（ただし当該ページは除く）
3. page_idでGROUP BYして件数をカウントする
4. ORDER BYで降順にソートする

つまりサブクエリを使う方法。しかしこれはどうも速度が出ない。1万件程度のデータ（ページ数500平均5ページビューとして2,000人のデータ）で以下のSQL文を実行してみた（nはページ番号）。

SELECT * FROM users_pages up1 WHERE up1.page_id IN (SELECT up2.user_id FROM users_pages up2 WHERE up2.page_id=n);

実行時間は・・・数分たっても結果が帰ってこなかった（非実用的）。次に自己結合を使って書き直してみた。

SELECT * FROM users_pages up1 LEFT JOIN users_pages up2 ON up1.user_id=up2.user_id WHERE up2.brand_id=n;

クエリ実行時間0.01秒。こちらは実用的な速度。後者のSQLでpage_id=nを閲覧した人が、他に閲覧したpage_idを抽出することができる。

ただこれ、50万件のデータになると処理速度がクエリ実行時間が1.25秒になった。ブラウザでSQLを流してから結果を受け取るまではすごく時間がかかった。「SELECT *」ではなくて必要なカラムを指定すれば早くなるけど。おそらく数万件程度のデータなら都度実行してもよさそうだが、それ以上はバッチ処理にしておかないとダメそう。COUNTしてORDER BYするともっと遅そうだから。

SQL苦手だな・・・。

現在案件の提案中。ほぼ受注できそうな感じなのだが、クライアントのリクエストの一つに「データベース処理に時間がかかるかも」と思われる部分があった。基本的にこういった不確定要素をもったまま受注してしまうのは嫌いなので、なるべく今のうちに処理したい。ということで、SQL文を書いてテストしてみることにした。

しかしテストSQL文を実行させるにあたって、なるべく本番と同等の条件にしたい。そう考えると、データとして100万件程度をINSERTしておかなければならなさそう。さてどうするか。

まずエクセルを使って乱数処理させた。できあがったデータをテキストファイルにし、PHPでSQL文に整形した（最初から全部SQL文にしたほうが早かった）。次にこのSQL文を実行しなくてはならない。

最初はphpMyAdminのインポート機能を使ってみた。しかし実行時間でエラーになる。時間がかかりすぎると他の作業に差し支えそうなので、Windows用のGUIツールを使うことにした。さて何を使おうか。いろいろ考えた結果MySQL Workbenchを使うことにした。コマンドラインからやっても良かったのだけれど、便利ツールはなるべく使いたい（Linuxならあきらめてコマンドラインだけれど）。

まずはソフトウェアをダウンロードしてインストール。

ダウンロードはこちらから

そしてSQLを実行。少々使い方がわからない部分があって悩んだけど、結果としてうまくいった。100万件のデータを登録するのに5分ちょっと位だったと思う。

とっても便利だし、インターフェイスもかっこいい。どうせ使うツールなら、こういうクールなアプリがいい。

NTTPC Communicationsが提供するWebARENAのVPSを追加契約した（現在3台目）。ここのVPSは基本的にまず落ちない。過去5年くらい使っているけど、落ちたのはメンテナンスの時だけ（もちろん事前のアナウンスあり）。だからミッションクリティカルな案件の時は必ずこのVPSを使うことにしている（サポートも悪くないし）。

そのサーバではMySQLでサービスを構築するはずだったのだが、急遽PostgreSQLを別件で使用することになった。早速使おうと思って、initdbコマンドを発行しようとしたところ、コマンドが見当たらない。「あれっ」と思って、/etc/rc.d/init.d/以下のPostgreSQLの起動コマンドを探したけど、それも見つからない。なぜだ。

いちおうデフォルトでPostgreSQLも使えることになっているのだが「もしやインストールされていないのでは」と思ってrpmコマンドで確認してみた。

• postgresql
• postgresql-libs

入っているけど・・・、これでよかったっけか。普段使っていないので、いいか悪いかもわからない。ちょっとウェブで調べてみたら・・・。ない。postgresql-serverがない！。

yumコマンドで早速インストール。これでinitdbコマンドも起動コマンドもインストールされた。初期状態じゃ、使えないんじゃん。

この前PHPのtimezoneの設定について記述した。この設定は当然PHPスクリプトのみで有効だ。MySQLと連携したシステムを作って、SQL文に日付関連の関数を組み込んだ場合、PHPに処理が渡る前にデータベース操作が終わってしまう。となると、データベース操作で得られる値は、期待したtimezoneでの値とは異なるものになる可能性が高い。そんな時に使うのがこれ。

SET time_zone = ‘+9:00′;

MySQL使用時に「SET NAMES binary;」というおまじないを付ける人も多いと思う。文字化け関連に対応するためにつけるわけだが、やっていることはこれと同じ。「SET」によって環境を設定している。つまりこれにより、一時的にtimezoneに変更を加えることができる。だからPHPからSQL文を発行するときに、毎回このSQL文を一番最初に発行しておいてやれば良さそうだ。

どんな具合に適用されるのかは、しばらく使ってみて確かめることにする。

最近気がついた。MySQLのデータ型でvarcharというのがある。varcharを指定した場合、そのカラムに格納することができる文字の長さを指定してやらなければならない。この「長さ」というのは「文字数である」ということに気がついた。

「文字数」とは文字通り「文字の数」。マルチバイトもシングルバイトも関係ない。全角も半角も関係ない。半角アルファベットの「ABCD」や「1234」、全角アルファベットの「ＡＢＣＤ」、平仮名の「あいうえ」、混在した「aＡあ1」。これらは全てvarcharでいうところの「長さ4」になる。

文字って奥が深い。