CakePHPMySQLPHPウェブサービスサイト発表2010/02/01マッチー
真面目にエロサイトを作ってみた【デザイナー編】はコチラから。
「スタイリッシュなアダルトサイト思いついたから作るぞ」
（たぶん）そんな感じの一言で始まったSheCoolのサイト制作。見た目がどれくらいスタイリッシュかってのはデザイナーの裁量次第なので、自分の担当はあくまでもシステム部分。発案者の意にできるだけ添えられるように、未熟ながらも自分の技術力を駆使してサイト制作に乗り出しました。
デザインの前に、とりあえず大まかな仕様を決めた時点で、今までやったことのない技術や、そこまで強く意識していなかったことがいくつも出てきました。主なところでは
辺りが自分にとっての未開領域でした。特にスクレイピングなんて、正直なところそういう単語すら初めて聞くくらいのレベル。所詮は素人プログラマです。ついでに、性欲は人よりあるわりに普段はほとんどアダルト動画を見ないもんですから、どんなアダルト動画の配信サイトがあるのかというのもよく知りませんでした。所詮は素人アダルト動画ウォッチャーです。
しかし今回の制作は何よりもスクレイピングが重要な部分なので、とにかくやってみるしかないってことでやってみました。
開発はCakePHPで行いました。どうしてCakePHPなのかは、今まで開発は基本的にCakePHPを使ってやってきたので一番使い慣れてるっていう、まあそれだけの、水たまりより浅い理由ですよ、ええ。海より深い理由などありゃしません。
データベースはMySQLを使用、ストレージエンジンは全テーブルがMyISAMです。基本的に検索なので、MyISAMが一番早いしトランザクションの必要な処理もないってことなで、MyISAMにしてます。それから後でもまた述べますが、表示側の動的な部分については基本的にjQueryを用いています。
何をおいてもまずはデータを取得することが必要。データがないと何も始まらない。ナニも始められない。まあ、俺ならデータがなくても妄想だけで始められｍ
最初はfile_get_contents関数を使ってページの情報を持って来て、必要な部分を正規表現で抜き取っていましたが、これが案外しんどい。意外と思い通りの形にデータを取れなかったりする。
そんなときに出会ったのがhtmlSQLというライブラリ。SQLを発行する感覚でスクレイピングが行える。しかもデータがHTMLタグ単位で返って来るので、欲しいところだけを抜き取りやすい。こいつは素晴らしい。中にはどうしてもhtmlSQLでは取れないデータがあったりもしたのですが、たいていのデータはhtmlSQLでいける。使い勝手が良いことは間違いないでしょう。
この解釈が正しいかはちょっと分かりませんが、タグの階層が深くなると、htmlSQLでは取れない場合があるような気がする。divタグの中にdivタグがあって、さらにその中にdivが…って感じでどんどん深くなると、divタグの中身を取って来ようと思った時に、一番深いところのdivの情報が上手く取れないようなケースがあるっぽい。でも取れてる場合もあるから、確かなことは言えないです。
デザイナー編でもあったように、女優のデータは全てDMMから取得しています。DMMには女優の一覧ページと各女優の詳細情報のページがあるので、まず女優一覧のページから各女優の詳細情報ページへのURLを取得し、それぞれのURLにリクエストを投げて、情報を取得する。名前を複数持っている女優に関しては、名前を分割してDBにデータを入力しています。
一旦全女優のデータを取得した後は、新人の女優さんだけを定期的に見に行くようにして、その女優のデータがDBに入っていなければINSERT処理を行うようにしました。
それにしても、最近の女優さんはみんな可愛いですね。
サーバー代、生ビール代をゲットするために必要なデータだから、ここはしっかり取得しなければならない。いえ、しっかり取得しなければならないのは他のデータも同じですけどね。
DMMは、各女優さんとその女優さんが出演しているDVDや動画がすでに関連づいているので、女優のデータを取得するときに一緒に商品データも取得するようにしています。女優ごとの商品の一覧ページへリクエストを投げて、その一覧ページから必要な情報を取得する。商品情報が複数ページにわたっている場合は、もちろんその全てのページのデータを取得する。
DMMではこの商品情報が人気順や価格順で見られるようになっているので、shecoolでもそこは連動させるようにしました。DMMを見ていると人気順はちょくちょく変化しているようなので、定期的に商品情報のページを見に行って、DBの方も更新するようにしています。人気順用のカラムを作っておいて、その中身を更新する感じ。毎回全商品の順位が変動するわけではないので、順位の変動があった商品だけを更新するようにしています。でないと、サーバーに無駄な負荷をかけてしまうからね。
DMMの方に追加された新しい商品情報の取得も、この人気順を入れ替える処理と同時に行っています。商品の一覧ページに載っている商品の情報がすでにDBに入っているかどうかを検証し、DBにデータがあった場合は、順位が変動していたら順位を入れ替えるUPDATE処理を行い、変動していなかったら何もしない、そしてDBにデータがなかった場合はINSERT処理を行うような分岐処理をしています。
考え方としては、取得先のサイトの検索ボックスにキーワードを入力して、見つかった動画の詳細な情報を取得する感じ。スクレイピングの場合は直接検索ボックスにキーワードを入力するわけではないけど、フォームから送信されるデータは、特にそれが検索フォームのような場合は、多くがGETメソッドによってURLの後ろにパラメータを追加するように作られているので、最初からパラメータのついたURLに接続すれば情報を取得できる。
今回は、DBに入っている女優の名前とタグをそれぞれパラメータとしてURLに追加し、見つかった動画を取得しました。例えば『麻美ゆま』というパラメータをURLに追加して見つかった動画を取得するということは、検索ボックスに『麻美ゆま』というワードを入力して見つかった動画の情報を取得することに等しいことになります。
当然ながらサイトによってデータの取得の仕方が多少変わってきますが、htmlSQLが存分に活躍してくれたおかげで、何とかなりました。
データを取得させていただいた諸サイトさんには、この場を借りてお礼を申し上げます。
スクレイピングは相手のサイトからデータを取ってくる作業なわけで、だからリクエストをたくさん投げ続ければそれだけサーバーにかかる負荷が大きくなります。自分側のサーバーはともかく、相手側のサーバーには極力迷惑をかけないようにしたいですね。連続してリクエストを投げすぎないようにする、取得する情報は必要最小限に抑える、余計なリクエストを投げない、その辺を意識しながらコーディングする必要があるかと思います。
データを取得したら当然次は入力です……が、しかし今回は入力する情報量が非常に多く、スクレイピングで必要な情報を取得する度にINSERT処理を行うと、1000件ほどのデータを入力するのに丸一日掛かってしまうようなこともありました。最初は数万件の動画情報を取得してDBに入れなければならなかったため、1000件の動画情報を入力するのに一日掛かっていたら時間がいくらあっても足りません。一日が40時間になってもきっと足りない。
更には、情報の取得だけならともかく、shecoolはタグや女優が動画やDMMの商品とHABTMで関連付けているため（関連付けに関しては後述）、アソシエーションを設定してCakePHPのsaveallメソッドでまとめて入力処理を行うと、一件辺りの動画情報の入力時間がもっと長くなる可能性も出てきます。というか、実際に長くなってました。
少しでも処理時間を短縮するためには、余計なインスタンスを生成しないようにするなど、考えられる点は多々あると思うのですが、今回は大幅な時間の短縮が求められたため、そんな焼け石にぬるま湯をかける程度の改善ではどうにもなりませんでした。かけるなら最低でも液体窒素くらいでないといけない。
そこで今回は、バッチ処理時にsaveメソッドやsaveallメソッドでのINSERT処理は行わずに、スクレイピングで必要な情報を持ってきたら、その情報を入力するINSERT文をテキストファイルに書き出す処理を行い、シェルから直接SQLを実行するようにしました。関連付けも、動画とタグ、動画同士、それぞれにINSERT用のテキストファイルを作成し、同様の処理を行いました。
コードはこんな感じ。
CakePHP 1.2にはシェル機能があり、それを利用すればバッチ処理をサーバー側で実行できるみたいです。サーバーでcronの設定を行い、毎日決まった時間に自動で処理を実行するようにしておけば、DBへのアクセスが比較的少ないと思われる時間帯（早朝とか）などに処理を走らせることができる。この辺りの設定とかに関しては、先輩プログラマのお力を借りました。というかほとんどやってもらいました。
おかげで、入力処理の時間は大幅に短縮されました。液体ヘリウムをかけたかのようでした（？）
shecoolでは、動画、タグ、女優がそれぞれ動画とHABTMで関連付いています（動画と動画は正確にはHABTMではないですが、多対多で結び付いているという意味で）。また、DMMの商品データとタグもHABTMで関連付いています。DMMの商品と女優は一対多。
動画同士は、タイトルがどれだけ類似しているかで関連付けを行っています。similar_text関数を使い、一定以上のパーセンテージ（今回は70%に設定している）が返って来たら関連していると判断し、バッチで処理を行うINSERT文をテキストファイルに書き出す。
処理自体はループ処理なので、ソースとしてはそんなに複雑にはなりません。ただ、10000件の動画があったら10000件の動画それぞれが他の9999件の動画と関連するのかどうかをチェックしなければならないため、処理自体はかなり重いです。動画の件数が増えれば増えるほど、処理はどんどん重くなる。なので、うっかりループの中で全動画データをSELECTするような処理を書くとメモリが大変なことになってしまうので、こういう時は、処理を行う前に一度全動画のデータを適当なメンバ変数などに持たせておくようにした方が良いと思います。処理時間を短くするには、とにかく必要以上にクエリは投げない。これが大事のようです。
本来なら、新規に動画データをDBに入力した時に同時に関連付けも行えば良いのですが、前述のデータ入力の項目にもあるように、それらを同時に行うのは負荷が大きいので、関連付けは関連付けで別個にINSERT処理を行っています。一度関連付けを行った動画データにもう一度同じ処理を行わせるのは無意味なので、今回は動画のテーブルに関連付けを行っていないかどうかの判定フラグ用のカラムを用意し、関連付けの処理を行うのはそのフラグが立っているデータのみ、つまりまだ関連付けが行われていない動画だけを抽出して処理を行うようにしました。
ただ、similar_text関数の検証という記事でも書いたように、マルチバイト文字の比較に関しては、そこまでの正確性はないように思えます。一つの動画を複数に分割してあるような動画（サンプル動画１、サンプル動画２、みたいな）を関連付けるのが一番の目的であり、それについては問題なく関連付いているので目的は達成できていますが、それ以外にも、およそ関連しているとは思えないような動画同士が関連している可能性は多分にあります。youtubeとかに比べると、関連付けのレベルは全然劣りますね。
動画とタグの関連付けは、DBに入っているタグ名をループで回し、動画のタイトルとディスクリプションの中にそのタグ名が入っているかどうかを判定し、入っていればその動画とタグを関連付けています。
それ以外に、スクレイピングのところで述べたように、動画は、タグ名をURLのパラメータに追加して、該当する動画が見つかったらそれらの情報を取得しています。だから例えば『女教師』というタグ名で動画を検索して引っかかった動画に関しては、タイトルやディスクリプションに『女教師』という単語がなくても、『女教師』のタグとは関連付けるようにしてあります。
動画と女優は、処理自体は動画とタグの関連付けと同様です。動画のタイトルやディスクリプションに女優名があるかどうかを判定し、あればその動画と女優を結び付ける。さらには女優名をURLのパラメータに追加して、例えば『麻美ゆま』という女優名で引っかかった動画に関しては、タイトルやディスクリプションの内容に関係なく、『麻美ゆま』とは関連付けています。
最初は、動画に女優のIDを持たせて、一対多で結び付けていました。つまり『麻美ゆま』で検索して見つかった動画に関しては、その動画の中に他の女優が出ていても、関連する女優は麻美ゆま一人だけでした。ですが話し合いの結果、複数の女優が出演している動画にはそれら全ての女優を関連付けた方が良いだろうという結論に達し、急遽HABTMに切り替えました。
ただし関連付けの判定方法がタイトルやディスクリプションの中に女優名が入っているかどうかだけなので、タイトルにもディスクリプションにも名前のない女優は、動画の中に出てきたとしても関連付いてはいないです。
DMMの商品の詳細情報が載っているページには、その商品に関連するジャンルの情報が載っています。そのジャンルとこちらのDBに入っているタグ名を比較し、一致するものがあった場合にはそのタグを関連付けるようにしています。
一つの商品に対し関連付いているジャンルは複数ある場合がほとんどですが、今回はそれら一つ一つをループさせてタグ名と一致するかどうか比較するような処理は行わずに、複数あるジャンルを一つの文字列として連結させ、正規表現を使ってタグ名がその文字列の中に存在するかどうかを判定して関連付けを行うようにしました。クエリが減るからこっちの方が処理時間は速いです。
特別な処理は何もしてないです。商品データの取得でも述べたように、商品は基本的に誰かしらの女優と結び付いているので、その女優さんのIDを商品テーブルに持たせているだけです。
日本のサーバーは、アダルトコンテンツを許可していないところが多いみたいですね。なので今回はMEGAFACTORYという海外のサーバーを利用しました。
当初は一台のサーバーで運用を考えていました。しかしクライアント（弊社社長）の度重なる要求に応えているうちにどんどんと仕様が膨らみ、それに伴って裏側の処理（動画の取得など）の負荷がかなり大きくなってしまったため、一台では運用が厳しくなってしまいました。
そこで、サーバーを二台に分割することにしました。複数台の運用、そして二つのDBを連動させるという試みは自分にとっては初めてだったため、何をどう設定すれば良いのかさっぱりでしたが、づや先輩やとりよし先輩のお力を最大限に借りることで、無事に乗り切りました。借りたっていうか、任せたの方が正しい表現ですけど。
要は、スレーブとマスターの二つにサーバーを分け、スレーブの方はSELECT処理のみ接続し、INSERTやUPDATE、DELETE処理を行う時には、マスターの方に接続するように設定している……らしいです。裏側の処理はずっとマスターの方に接続していれば問題ないですが、表示側はその時々に応じて接続先を切り替えなければなりません。shecoolでは動画や女優にランキングをつけているため、表示側でもINSERTやUPDATEの処理が行われることはあります。そういう時だけ、接続先をマスターの方に切り替えるわけです。
これはsaveメソッドやupdateメソッドをオーバーライドすることで切り替えを実現しています。一時的にマスターに接続して処理を行い、終わったら接続先をスレーブに戻す、そんな処理を書いています。
SQLにおいて、LIKE検索は速度が遅い。だから特定の文字列を検索したい時にはLIKE検索よりも全文検索を利用するのが常套だと思うのですが、しかしMySQLの全文検索はマルチバイトに対応していない。FULLTEXTのインデックスを張るだけでは残念ながら日本語の全文検索は上手くできないみたいです。
対応策はいくつかあるようですが、今回はSennaという組み込み型の全文検索エンジンを利用して、日本語での全文検索を可能にしました。
SennaをMySQLに組み込むにはMySQLをコンパイルし直す必要があるらしく、そこがちょっと大変で、結構苦戦するみたいです。しかしここでも当然のように先輩方のお力を借りた私は、特に苦労はしていないです。使えない後輩を持つと先輩は大変なんだってのがこれでよく分かりますね。気をつけましょう。
サイトの性質上、shecoolは画像や動画のデータが多いです。デザイナー編にある、どういうサイトにするかのところで、画像をあまりごちゃごちゃ使わないようにしたいとか何とか言っちゃおりますが、できるだけ使わないようにしたって、一度に結構な数の画像が表示されます。まあ、そういうサイトなんだから仕方ないわけなんですが、加えて、関連動画の表示や検索ワードに該当する動画データと関連するDMM商品の表示など、複雑なSQLクエリを発行する箇所が多々あり、ページ全体はかなり重いです。全体の読み込みが終わるまでにかなりの時間を要してしまう。
そういうわけで、少しでも速くするために、さまざまな試みが要求されました。Sennaの導入もその一つですね。
shecoolでは、表示の動的な変化にjQueryを多用しています。細かい動きがあるたびにページ全体を読み込んでいたら目的の動画にたどりつくまでに日がくれてしまいますからね。気持ちが萎えることこの上ないですね。萎えるのは気持ちだけではないと思います。
jQueryでなければならない理由は特にありませんが、Ajaxでスライドなどのアニメーション効果をつけたいと思った時に、個人的にはjQueryを利用するのが手軽に実現できて良いと思っています。HTTP通信でのページ読み込みも簡単に行えるし、自分はprototypeよりはjQueryを使うことが多いです。
本気で最適化をするとなったら、これから書くこと以外にもかなりいろいろとやらないといけませんが、とりあえずは明日のためのその１。技術レベルが素人以上素人未満の僕みたいな奴でもすぐに手を出せるような項目を、いくつか挙げてみます。
DBのテーブルを見直して、ちゃんとインデックスが張られているかチェックしましょう。主キーだけでなく、特にWHERE句に関わってくるようなカラムは、インデックスを張っておいた方がSELECT文の場合には実行速度が速くなります。
あとはCake側でアソシエーションの設定をしている場合は、必要なところ以外はunbindModelをしておくことも時間短縮の一つですね。joinしていると遅くなったりするから。
何にせよ、まずはどのクエリがボトルネックになっているかを洗い出すことが肝心です。基本的にはEXPLAINで地味にこつこつと見るのが良い。クエリを解析すればどのクエリが遅いのかは分かるので、そこに改善を施すわけです。今回は全文検索のインデックスがjoinの時にうまく効かなくて、構成から変えたところもあったりしました。
先輩プログラマ曰く、だいぶ頑張って実行時間を短縮したけどまだまだ遅い感じがする、だそうです。
ページにキャッシュを利かせれば、表示は当然速くなります。でも当サイトではユーザーの動きがダイレクトに出るところがあるから（最近見た動画とか）、ページキャッシュを作ってもすぐに消される羽目になり、それではキャッシュを利かせるメリットがない。
ということで、今回はクエリの方にキャッシュを利かせました。検索のように、毎回同じクエリが投げられるとは限らないようなところはともかく、人気の動画や人気の女優を取得してくるようなところは常に同じクエリが投げられるので、クエリ自体をキャッシュ化しても特に問題はないわけです。ページ読み込みの際に、すでにクエリのキャッシュが存在している場合にはそちらを見に行くようにすれば、これまた時間短縮につながります。
画像が多いと、どうしても全体が読み込み終わるまでにある程度の時間を要してしまいますね。そこで、画像の読み込みは後回しにしてしまっても良いような部分は、ページの読み込みが完了してからAjaxで読み込ませるようにする方法もあります。前述の通り、shecoolはjQueryを多用しているので、例えばフッター部など、スクロールしないと見えないような部分はonLoadでAjax通信を行って画像を読み込ませるというのも、有効な手段だと思います。読み込みが終わるまではloading用のgif画像でも出しておけば良いんじゃないでしょうか。
こんな感じで、ことあるごとに追加される機能や、その都度発生するバグ、そしてデザインの修正、果てはリリースしてないのに大幅にリニューアルするという、これはもはやパワハラなんじゃねーのってくらいの仕様変更に耐えながら地道なチューニングを行い、ようやくリリースまでこぎつけたわけです。
分からないことだらけで大変ではありましたが、とても有意義な制作でした。特に、今までそんなに重いページを作ったことがなかったせいか、クエリの最適化とかは全然意識したことがなかったんですけど、これは今後も必ず役に立ってくるところだと思うので、とても勉強になりました。
それに何といっても、動作テストと称して毎日仕事中に堂々とアダルト動画をひたすら見続けることができたのでとても有意義な制作でした。お気に入りの動画を探してマイリストに登録するところなんて、テストに見せかけてかなり真剣に選抜してました。とても有意義な制作でした。
ただ、大真面目にアダルト動画をチェックしてマイリストを作っている自分とか、取得するサイトの中でもどのサイトが優良な動画をたくさん持っているかを入念に吟味している自分とかを客観的に見たら、「ああ、こいつは絶対にモテないな。右手しか恋人候補がいねえ」って思いました。