パズガの水色さやみんです(･ω･)/
「コードルームからの脱出」
が公開されるようでとても楽しみです！
勿論webエンジニアのわたくし、練習問題はちゃんと解きましたよ☆
（2問目は知識的に知らなかったのでググりましたが←）
===========================
さて・・・今日の記事は、
怒られたら消します！＼(^o^)／
えーっとね、 ハックガールズとして、
たまには技術ブログ書いても良いかなー？なんて☆
（誰得、というツッコミは不要です＾ｑ＾）
ほら、ヒミツキチラボでも実験的なイベントやってるじゃん？
だから、ブログの記事の内容も実験的に色々書いてみても良いかな？って（苦しいｗ）。
先にお断りしておきますが、
システム系じゃない方は、多分
ここから先、読んでも全部意味がわからないと思うので、
見なくて大丈夫です！ww
では、ハックモード全開で、
好き勝手書かせていただきますね（小声）。
===========================
最近、私はあるアプリケーションのユーザさんの動向分析で、
色んなクエリを書いてDBに投げています。
今までは、比較的小規模なアプリケーションの運営に携わってきたので、
恥ずかしながら、
殆どデータベースのパフォーマンスを意識したことが…ありませんでした…（懺悔）。
が、最近は見ているアプリケーションのユーザさんも増えてきて、
そんなことも言っていられなくなったわけです。
うっかり変テコなクエリを投げるとポーンとDB落ちるなんてことになりかねません。
あ、ちなみにmysqlを使用しています。
そんなわけで、
今回はmysqlパフォーマンスを意識するにあたってスーパー初心者の私が、
mysqlのEXPLAINを投げた結果の見方を勉強するところから始まり、
パフォーマンス改善をひよっこながら考えてみるという内容です。
エンジニアの諸先輩方にとっては、
当たり前すぎて恥ずかしいような内容になっていると思いますが、ご勘弁下さい！
===========================
まずは対象のクエリ。
ユーザさんがアプリケーション内で「ポイント」を獲得する場合おいて、
userとその獲得point_logのテーブルをJOINして、
ログのうち特定typeのレコード数を、ユーザごとにCOUNTする、
といった意図のクエリです。
point_logテーブルは下記のような構成（仮）
CREATE TABLE `point_log` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(10) unsigned NOT NULL,
`point` int(10) NOT NULL,
`type` int(1) NOT NULL,
`ctime` timestamp NOT NULL DEFAULT '2014-01-01 00:00:00',
PRIMARY KEY (`id`,`ctime`),
KEY `fk_point_log_user1` (`user_id`),
KEY `index_point_log_type` (`type`)
)idとctime(created time）が主キー、そしてuser_idとtypeにも各々indexが貼られている状態です。
早速投げてみます。
mysql> EXPLAIN SELECT COUNT(DISTINCT user.id) as user_count
FROM point_log
INNER JOIN user ON user.id = point_log.user_id
WHERE point_log.ctime BETWEEN '2014-01-01 00:00:00' AND '2014-01-31 23:59:59'
AND point_log.type = 1;+----+-------------+-----------+--------+-----------------------------------------+----------------------+---------+--------------------------+------+--------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+--------+------------------------------------+----------------------+---------+--------------------------+------+--------+
| 1 | SIMPLE| point_log | ref | fk_point_log_user1,index_point_log_type | index_point_log_type | 4 | const | 1 | Using where |
| 1 | SIMPLE| user | eq_ref | PRIMARY | PRIMARY | 4 | rs_fla.point_log.user_id | 1 | Using index |
+----+-------------+-----------+--------+------------------------------------+----------------------+---------+--------------------------+------+--------+
2 rows in set (0.00 sec)注目してみるのはtypeで、投げたクエリの場合は、
ref
eq_ref（userテーブルはidのみがprimary_keyとなっています）
インデックスを貼っている事が功を奏し、
JOINしているにしてはスマートに取得できているようです。
参考：
6.2.1. EXPLAINを使用して、クエリを最適化する
仮にtypeへのindexが無かった場合の結果はこうなります。
+----+-------------+-----------+--------+--------------------+---------+---------+------------------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+--------+--------------------+---------+---------+------------------------+------+-------------+
| 1 | SIMPLE | point_log | ALL | fk_point_log_user1 | NULL | NULL | NULL | 1 | Using where |
| 1 | SIMPLE | user | eq_ref | PRIMARY | PRIMARY | 4 | test.point_log.user_id | 1 | Using index |
+----+-------------+-----------+--------+--------------------+---------+---------+------------------------+------+-------------+
フルスキャン／(^o^)＼(ALL）すなわち、1条件になっているtypeへのindexが振られていないというだけで、
恐ろしいロスが発生する模様。
逆に、現在eq_refになっているuserテーブルに対して、
「ctime」をindexとして追加したらこうなります。
+----+-------------+-----------+------+------------------------------------+----------------------+---------+------------------------+------+--------+
| id | select_type | table | type | possible_keys | key | key_len | ref| rows | Extra |
+----+-------------+-----------+------+------------------------------------+----------------------+---------+------------------------+------+--------+
| 1 | SIMPLE| point_log | ref | fk_point_log_user1,index_point_log_type | index_point_log_type | 4 | const | 1 | Using where |
| 1 | SIMPLE| user | ref | PRIMARY| PRIMARY | 4 | test.point_log.user_id | 1 | Using index |
+----+-------------+-----------+------+------------------------------------+----------------------+---------+------------------------+------+---------+
2 rows in set (0.00 sec)両テーブルともtypeがrefに変わりました！
このようにindexの1つ2つで
パフォーマンスが明確に変わるというのが目に見えますね。
事実上の速度を測らずともtypeで判別できるのはとても便利です。
===========================
様々な条件句でクエリを投げる身としては、
とりあえずindex貼りまくりたい衝動に駆られますが、
indexを貼れば勿論その分insertやupdateの処理が遅くなったりします。
あまりにindexの需要数が増えて場合には、
テーブルの分割や正規化を検討するのが良いようです。
あとは直接的にSQLでデータを出さなくていい状況ならば、
アプリケーション側で取得方法を制御してしまうというのも一手かと思います。
例えば、必要なレコードを10000件ずつ取得して、次の処理を行っていくと同時に
別プロセスで次のタームの処理を進める、と言った形です。
このへんは最近うっかりphpでメモリオーバーを吐いた時に考えた反省です（小声）。
===========================
続きも色々書きたいのですが、
パズガ好きでここに来た人が
ドン引きしてるのが目に見えるので自重しますね！ww
アイドルなのにー？エンジニア♡
アナタのハートをハックする！
パズガの水色、さやみんでした！(*´艸｀*)
twitter @saya_pzg