プログラミング

wpdb道場 (13) 〜おすすめユーザーを作ろう〜

高橋文樹

2014 年 12 月 25 日
読了7分（5,526文字）
MySQL WordPress wpdb道場

この投稿は 10年半前に公開されました。いまではもう無効になった内容を含んでいるかもしれないことをご了承ください。

前回までである程度できてきましたが、今回はちょっと端折ります。githubのwpdb-dojo/step9というブランチでこんな処理をやりました。

Codekitでの管理をGruntに変更
Models\Followersに相互フォローしているかどうかを把握するメソッドを追加
Models\Followersにフォローしている数とフォローされている数をカウントするメソッドを追加
コマンドにwp lonely-usersを追加し、誰もフォローしていないユーザーを抽出できるように
ユーザーが削除されたらwp_followersテーブルのデータを削除するフックを追加

今回の開発はwpdb-dojo/step10ブランチの先頭から始まります。チェックアウトするか、ダウンロードするかしてください。

さて、今回はおすすめユーザーを作ります。はじめてFacebookやtwitterを使った時のことを思い出して欲しいのですが、だいたい登録当初はこういった画面が出て、誰かしらフォローするよう促されますよね。

twitterのおすすめ画面

SNSにおけるこうした動作は「賢いデフォルト」と呼んでいいでしょう。もし、ここでなんの選択肢も与えなかったら、ほとんどのユーザーはそのSNSを使わないで、結局はやめてしまいます。なにも情報が出てこなくてつまんないですからね。

もっとも、よくわからない情弱のスマホから連絡帳をぶっこ抜くのが倫理的に正しいのかとか、結局は有名人が幅を利かせることになる「おすすめ」になんの意味があるのかとか、色々ありますが、ともあれコールドスタート問題を防ぐための簡単な方法ではあります。

コールドスタート問題というのは、文字通り「冷たいスタートが困る」ということなのですが、たとえばAmazonの「この商品を買ったユーザーはこんなものも買っています」を想像してみてください。

TENGAの下にはTENGA

もし、あなたの作ったECサイトが公開したばかりだとして、この機能は実現できるでしょうか。できないですよね。購買履歴をもとにしたリコメンドなんだから、誰も買っていない状況ではおすすめできないわけです。

こうした問題はこれからWebサイトを作ろうとしているあなたにとってかならずつきまといます。

表示エリアを作る

さて、ではこのおすすめを表示する場所を考えてみましょう。前回作った「フォローしている」というページにいったとき、誰もフォローしていないユーザーには「ユーザーは見つかりませんでした」と表示されます。

誰もフォローしていない場合

ここにおすすめユーザーを表示すればいいんですね。まずは、おすすめユーザーを表示するメソッドと、特定のユーザーがロンリー（誰もフォローしていない）かを判定するメソッドを仮で決めておきます。

まず、誰もフォローしていないかどうかは上ですでに実装済みの「フォローしている数をカウントするメソッド」を使えばいいですね。

/**
 * Get following count
 *
 * @param int $follower_id
 *
 * @return int
 */
public function followingCount($follower_id){
     $query = <<<SQL
          SELECT COUNT(user_id) FROM {$this->table}
          WHERE follower_id = %d
SQL;
     return (int) $this->get_var($query, $follower_id);
}

このメソッドの戻り値が0だったら、誰もフォローしていないということになります。続いて、おすすめユーザーを表示するメソッドを仮で実装しましょう。

これはControllers\FollowerListに実装します。

/**
 * Get user list for lonely user
 *
 * @return string
 */
public function followNone(){
     return '誰もフォローしていません';
}

続いて、フォロワーを表示していた部分に分岐を作ります。theContentメソッドをカスタマイズですね。

/**
 * Filter 'the_content'
 *
 * @param string $content
 *
 * @return string
 */
public function theContent($content){
     if( is_page($this->followers) ){
          return $this->getFollowersList('follower', true);
     }elseif( is_page($this->following) ){
          if( $this->models->followers->followingCount(get_current_user_id()) ){
               return $this->getFollowersList('following', true);
          }else{
               return $this->followNone();
          }
     }else{
          return $content;
     }
}

これで一旦表示してみましょう。そうそう、ユーザーをインポートするときにパスワードを全員同じにしましたよね？　WP_CLIを使って孤独なユーザーのリストを取得できるようにしているので、取得してみましょう。

wp lonely-users

wp lonely-usersの実行結果

僕の場合は持田昌弘さんがロンリーだったので、このユーザーとしてログインします（※ユーザーのフォロー数はランダムなので、一致しないかもしれません）

さっき上で実装した結果が出た

でましたね。では、「おすすめユーザーを取得する」というメソッドをModels\Followersに実装しましょう。メソッド名はgetRecommendedUsersにしましょうか。

おすすめユーザーの人数を指定できる
おすすめの順はフォロワーが多い順番

こんな感じにしましょう。今回のクエリではサブクエリという機能を使います。以前はテーブルをJOINしましたが、今回は集計が挟まるので、JOINする前にサブクエリでフィルタリングしてしまいます。たぶん、その方が計算負荷は減るはず。

/**
 * Get recommended users list
 *
 * @param int $limit
 *
 * @return array Array of \WP_User
 */
public function getRecommendedUsers($limit = 10){
     $query = <<<SQL
          SELECT * FROM (
               SELECT user_id, COUNT(follower_id) as score
               FROM {$this->table}
               GROUP BY user_id
               ORDER BY score
               LIMIT %d
          ) AS f
          INNER JOIN {$this->db->users} AS u
          ON f.user_id = u.ID
          LIMIT %d
SQL;
     // Get MySQL rows
     $result = $this->get_results($query, $limit * 2, $limit);
     // Convert them to WP_User
     $return = array();
     foreach( $result as $row ){
          $return[] = new \WP_User($row);
     }
     return $return;
}

サブクエリというのは、ハイライトしたところです。JOINというのはSELECT * FROM table_1 LEFT JOIN table_2 ON table_1.some_id = table_2.any_idと書きますが、今回は「フォロワー数を集計して多い順のリストを作り、そこにwp_usersテーブルを合体する」という手法になります。

そのようなテーブルが存在しないので、wp_followersから集計して取得しているのです。一個一個表現を説明しますね。

(….) as f

サブクエリ。カッコ内から返ってくるクエリの結果を1つのテーブルとして扱う。この場合はエイリアスとしてfという名前をつけている。サブクエリでテーブルを作った場合は、かならずエイリアスが必要。ないとエラー。

GROUP BY user_id

指定したカラムで集約する。GROUP BY user_id と COUNT(follower_id)を組み合わせると、user_idごとのfollower_idの数が出せる。これにより、ランキングが出せる。

LIMIT %d に $limit * 2している意味とINNER JOIN

JOIN にはINNER, LEFT, RIGHT, CROSSというのがあります（詳しくはこちら）。で、INNERにした場合は、「2つのテーブルで結合条件にマッチしている行のみ」が取得できます。

この場合、wp_followersにuser_idが存在し、かつwp_usersのIDが存在する行だけが出てきます。

そもそもwp_followersに存在するuser_idやfollower_idはその存在を確定できていないため、場合によっては「集計結果に存在するuser_idの上位10件をwp_usersに結合してみたら、足りなかった」というケースが考えられます。

したがって、念のため上位20件を取得してからJOINし、そのうち上位10件を取得しているのですね。これにより近似的に正確なランキングが取れるだろうと想定しています。

サブクエリ内のORDER BY

そもそもなぜサブクエリ内でORDER BYしているかというと、ORDER BYというのは大変コストが高いのです。「上位n件を100,000,000件のデータから取得する」という場合、かならずテーブルスキャン（テーブルを全部読むこと）が発生します。

このテーブルスキャンを避けるための仕組みとして、インデックスという仕組みがあります。インデックスは以前も説明しましたが、文字通り索引であり、ビジュアル的には辞書のツメがそれにあたります。

辞書の横にあるツメ

今は辞書を引く人も少ないかもしれませんが、目的の単語を探すために、いちいち全ページめくる人はいないですよね。あたりをつけてから、「この辺かなー？」とめくっていくはずです。

このインデックスの効果は劇的で、場合によってはすごい効果が出るのですが、今回のように集計関数COUNTを使って並び替えしたときなんかは効果が少ないんですね。というわけで、少しでもパフォーマンスをあげようということで、サブクエリ内でORDER BYしています。

なんとなく、逃げの対応ですが、この対策は後述します。

表示してみる

とりあえず、実際の表示を見てみましょう。表示を司るControllers\FollowerListのメソッドを変更します。

/**
 * Get user list for lonely user
 *
 * @return string
 */
public function followNone(){
     $users = $this->models->followers->getRecommendedUsers(10);
     return '<p class="fs-recommend">誰もフォローしていません。この人たちをフォローしてみましょう。</p>'
            .$this->renderUserList($users, 'follower');
}

早速見てみると……

45年前って、まだ生まれてないよぉ

ありゃりゃ。45年前と出てます。これはアレです、取得したやり方が以前と変わっているので、wp_followers.createdというカラムがないんですね。そりゃ、まだフォローしてないんだから、いつフォローしたかという情報があるわけないですよね。

幸い、wp_usersにはuser_registeredというカラムがあるので、それを突っ込みましょう。Models\FollowersのgetRecommendedUsersをこんな風に変えてください。

$return = array();
foreach( $result as $row ){
     $row->created = $row->user_registered;
     $return[] = new \WP_User($row);
}

はい、治りました。実際のところ、同じエリアが場合によってフォローされた日だったりユーザー登録した日だったりするのはおかしいのですが、今回はお茶を濁します。

7日前にまど改善

クエリを改善できなければキャッシュしよう

さて、先ほど書いたクエリですが、改善の余地はないでしょうか？　それとも、そもそもそれをどうやって調べたらいいのでしょう。この方法が実は存在します。まず、さっきのクエリからPHPの変数を抜いて、純粋なSQL文に変えてみましょう。

SELECT * FROM (
     SELECT user_id, COUNT(follower_id) as score
     FROM wp_followers
     GROUP BY user_id
     ORDER BY score
     LIMIT 20
) AS f
INNER JOIN wp_users AS u
ON f.user_id = u.ID
LIMIT 20

元のクエリは147msかかっていました。これは「遅い」という範疇に入ります。

ミリ秒で3桁だと遅いなーと感じましょう

で、この先頭にEXPLAINという文字をくっつけると、このクエリのパフォーマンスがわかるんですね。SequelProとかで発行してみてください。

見方がわからん……

はて……この見方はどうしたらいいんだろう？　ここでお役立ちブログを一つ紹介します。それは漢のコンピュータ道です。MySQLを作っている人の書いているブログなので、ぜひ購読してください。

で、この方の記事でMySQLのEXPLAINを徹底解説!!というのが必読です。これによると……

derivedというのはFROM句のサブクエリのことらしいので、ここが重要。
typeがindexは遅い、ALLはテーブルスキャンで最悪。
rowsは対象となる行数。サブクエリの対象は228,420行だから、現時点ですでに多いし、今後も増える（←重要）。
Extraフィールドでは、Using indexが表示されている。したがって、この検索条件はインデックスだけで解決できている。つまり、改善の余地はない？
同じくExtraフィールドにUsing temporary, Using Filesortが出ている。これは遅い（参考：filesort）。

さて、いろいろチューニングの余地はありそうですが、どうしましょう。クエリを改善したり、テーブル構造を見直したりすることもできますが、ここでは大人の対応キャッシュをやりましょう。わかんないんじゃないですよ？　そろそろキャッシュとか知りたいだろうなって思ったからですよ？

さて、WordPressにはキャッシュ機能がいくつかあります。今回はTransient APIというものを使いましょう。幸い日本語のCodexにもありますしね。こういうの本当に助かります。偉いなあ。誰が翻訳したんだろう(ﾁﾗｯﾁﾗｯ

えっ、えらいなあ！

それはさておき、このTransient APIを使えば、時間指定付きでキャッシュが可能です。今回のような「ランキング」というのは、キャッシュするのにうってつけです。なぜかというと……

「誰もフォローしていないユーザーにオススメする」という目的上、かならず正確なリストを常に見せる必要がない（だいたいあってりゃいい）
誰がも見ても同じ結果で構わない

というわけで、キャッシュの期限を2時間にしましょう。CodexにはObjectも保存できるって書いてますが、WP_Userクラスのオブジェクトを保存できるかどうかわからないので、MySQLの行だけを保存するようにします。Models\FollowersのgetRecommendedUsersをこんな風に変えましょう。

/**
 * Get recommended users list
 *
 * @param int $limit
 *
 * @return array Array of \WP_User
 */
public function getRecommendedUsers($limit = 10){
     $cache_key = 'freundschaft_recommended_users';
     // Get cache
     $result = get_transient($cache_key);
     if( false === $result ){
          // No cache. Query to DB
          $query = <<<SQL
               SELECT * FROM (
                    SELECT user_id, COUNT(follower_id) as score
                    FROM {$this->table}
                    GROUP BY user_id
                    ORDER BY score
                    LIMIT %d
               ) AS f
               INNER JOIN {$this->db->users} AS u
               ON f.user_id = u.ID
               LIMIT %d
SQL;
          // Get MySQL rows
          $result = $this->get_results($query, $limit * 2, $limit);
          // Save Cache.
          set_transient($cache_key, $result, 60 * 60 * 2);
     }
     // Convert them to WP_User
     $return = array();
     foreach( $result as $row ){
          $row->created = $row->user_registered;
          $return[] = new \WP_User($row);
     }
     return $return;
}

はい、オッケーですね。これでおそらく、キャッシュが使われていることでしょう。