ハウテレビジョンブログ

『外資就活ドットコム』『Liiga』『Mond』を開発している株式会社ハウテレビジョンのブログです。

MySQLのキャッシュを有効活用するために気を付けること

ソフトウェアエンジニアリング

明けましておめでとうございます！　大晦日には父と二人で一升瓶をまるまる空けてしまった@who_you_meです。

f:id:tomurakami:20150109165645j:plain

DBのパフォーマンスチューニングにはさまざまな要素がありますが、キャッシュの活用はそのなかでもかなり重要な項目です*1。

「そもそもキャッシュが有効になっているか」「メモリが適切に割り当てられているか」などDBMS側で設定する内容もありますが、同じぐらい大事なのが「キャッシュが効きやすいクエリを発行しているか」です。

Webアプリのコードを書く場面でも、少しの注意でキャッシュをより有効活用できる場面がいくつかあるので、今回はそれについてご説明します。

弊社の環境に合わせてCakePHP + MySQLの例を記載してますが、他のWebフレームワーク、RDBMSでも応用可能な内容のはずです。

大文字と小文字

Webサービスを作る上ではあまり関係ないかと思いますが、クエリをキャッシュするときには大文字と小文字が区別されるようです。

# この2つは区別される！
SELECT * FROM my_table;
select * from my_table;

時間で条件を区切るときは気をつけよう

例えばブログサービスを作っているとしましょう。 「1週間以内についたコメントを取得する」場合、MySQLなら普通こう書きますね。

SELECT * from comments WHERE created >= (NOW() - INTERVAL 1 WEEK);

CakePHPならこう書くことが多いでしょう。

<?php
$time_begin = date("Y-m-d H:i:s", strtotime('-1 week');
$this->Comment->find('all', array(
    'conditions' => array('created >=' =>　$time_begin)
));

しかしこのクエリはキャッシュが効きません。当然ですがNOW()やdate()の中身が、実行した時間によって変化してしまうためです*2。

もちろん「秒単位で正確に1週間以内か判定する必要がある」という事情があるなら仕方ありません。しかしサービスの性質によってはそこまでの厳密さは求められない場合も多いでしょう。

そんな場合には、下のように書いてみましょう。

<?php
$time_begin = date("Y-m-d H:00:00", strtotime('-1 week');
$this->Comment->find('all', array(
    'conditions' => array('created >=' =>　$time_begin)
));

2015-01-08 14:28:34にこれを実行した場合（今時計を見ました）、$time_beginには2015-01-01 14:00:00が入ります。 そのため、厳密には1週間よりちょっと（20分ほど）前のコメントも取得されてしまいますが、その代わりにキャッシュが効くようになります。

すぐに推測できると思いますが、2015-01-08 14:00:00 〜 2015-01-08 14:59:59の間はすべて$time_beginの中身が同じになるため、断続的に実行されても実際にクエリが走るのは1時間に1回だけで、他はキャッシュから返せるようになるからです。

時間を検索条件とする場合には、このように「多少の厳密さは犠牲にしてキャッシュを効かせられるようにできないか」をいつも考えるようにしましょう。

キャッシュとか関係なくRAND()には気をつけよう

これはよく言われる話で、『SQLアンチパターン』でも1章を割いて説明されています。

「全件取得しておいてアプリ側でランダムに選ぶ」みたいなワイルドなことをしない限り、そもそもキャッシュを効かせることは原理的に不可能ですが*3、そうだとしてもORDER BY RAND()はあまり使わない方が望ましいです。 理由としては、結果として全件ソートすることになるため負荷が上がるためです。

対策としては、

  • ランダムに1件取得する場合
    • まず行数(N)を取得し、1以上N以下の範囲でランダムに数値を1つ求めて(R)、R行目を取得する
  • ランダムに複数件(M)取得する場合
    • ↑の手順をM個に達するまで繰り返す
    • IDのカラムがあり、IDが歯抜けになっていない場合は1以上N以下の範囲でランダムにM個数値を求めてその行をINで選択

などがあります。なかなか煩雑ではありますね……

最後に

DBのチューニングと聞くと「職人芸が必要なのでは」とちょっと身構えてしまいますが、こうして普段コードを書く際に少し気を付けるだけで、キャッシュ機能を有効に活用して負荷を減らすことができます。

1点目の大文字小文字についてはWebフレームワークを使う分には意識する必要はほぼないですが、2点目の時間指定についてはいつも気にかけておいた方がいいでしょう。

ランダム取得についてはロジックがやや複雑になることもあり、「件数があまり多くなく、今後もさほど増えない」ことが事前に分かっているテーブルについてはORDER BY RAND()を使ってしまっても実運用上問題はないかと思います。ただし数万、数十万行になる場合はかなりパフォーマンスに響いてきます。

ここではポイントを絞って解説しましたが、より詳細な説明や「どの関数を使うとキャッシュされないのか」などの情報はもちろん公式のドキュメントに記載されてますので、興味のある方はそちらも参照してください。

http://dev.mysql.com/doc/refman/5.7/en/query-cache-operation.html

*1:ただし、キャッシュの生成にはオーバーヘッドが発生するため、SNSやソーシャルゲームのように更新クエリの頻度が高いサービスの場合には、キャッシュを切っておいた方が早くなる場合もあるようです。 また一般的な目安として、キャッシュヒット率が2割を切るような場合にもキャッシュが逆効果になってしまうと言われています。

*2:正確には、CakePHPの例では最大で1秒の間だけキャッシュが効きますね……

*3:いつも同じクエリを吐く＝いつも同じ結果が返ってくる　ではランダムではないですからねｗ