Covering Index と self-join と MySQL

Covering Index と self-join と MySQL

某サービスのクエリチューニングのお話。\n\nブログとか日記とかそういうサービス系で次のようなテーブルがあったとします。\n\n CREATE TABLE entries (\n id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,\n user_id INT UNSIGNED NOT NULL,\n posted_by TINYINT UNSIGNED NOT NULL, --#PC、mobileなどどこから投稿されたかのフラグ\n title VARCHAR(512) NOT NULL,\n body TEXT NOT NULL,\n created_at DATETIME NOT NULL,\n updated_at TIMESTAMP NOT NULL,\n status TINYINT UNSIGNED NOT NULL,\n INDEX (user_id,created_at,status)\n ) ENGINE=InnoDB\n\nまぁ、わりとありがちです。順調にサービスが使われ記事数が百万件ぐらいあったとしましょう。ある日\n\n「サービスの改善のため、1日に携帯から登録された件数をレポートとして出して欲しいんだけど」\n\nとディレクターからカジュアルに頼まれました。いろいろ言いたいことはさておき、あなたならどうしますか？\n\n最近上と同じような場面において、元々あったクエリを以下のように変更しました。\n\n SELECT COUNT(*) FROM entries a, entries b\n WHERE a.id = b.id\n AND a.created_at BETWEEN '2011-09-10 00:00:00' AND '2011-09-10 23:59:59'\n AND b.posted_by=2\n\nちょっと奇妙に見えそうですが、self-joinを使っています。なぜこんなクエリになったのか、順を追って説明します。\n\n通常、1日に携帯から投稿されたエントリ件数をだすには\n\n SELECT COUNT(*) FROM entries \n WHERE created_at between '2011-09-10 00:00:00' AND '2011-09-10 23:59:59'\n AND posted_by=2;\n\nこのように書くでしょう。ただし、インデックスが使えないのでテーブルスキャンとなってしまいます。EXPLAINすると\n\n +----+-------------+---------+------+---------------+------+---------+------+-------+-------------+\n | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |\n +----+-------------+---------+------+---------------+------+---------+------+-------+-------------+\n | 1 | SIMPLE | entries | ALL | NULL | NULL | NULL | NULL | 98326 | Using where |\n +----+-------------+---------+------+---------------+------+---------+------+-------+-------------+\n 1 row in set (0.00 sec)\n\nとなります。これを高速に実行するためにはインデックスの追加が必要です。\n\n ALTER TABLE entries ADD INDEX (posted_by,created_at);\n\nこんな感じでしょうか。\n\nただし今回のテーブルは既に100万件が登録されており、(サーバがそもそも古いとかInnoDB Pluginとか全く存在しないMySQL4.0だったなどなど)ALTERも大変そうです。そこでなんとか既存のインデックスの中で解決する方法を探します。\n\nMySQLではプライマリインデックス、セカンダリインデックスだけでクエリが解決できることをCovering Indexと呼びます。当然インデックスだけでクエリ処理ができるので、高速です。MySQLのオプティマイザはクエリがCovering Indexで解決できる場合、多少効率が悪くてもテーブルスキャンよりインデックスのフルスキャンを選択するようなので、次のクエリのように条件がcreated\\_atだけのカウントクエリの場合、\n\n mysql> EXPLAIN SELECT COUNT(*) FROM entries \n WHERE created_at between '2011-09-10 00:00:00' AND '2011-09-10 23:59:59';\n +----+-------------+---------+-------+---------------+---------+---------+------+-------+--------------------------+\n | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |\n +----+-------------+---------+-------+---------------+---------+---------+------+-------+--------------------------+\n | 1 | SIMPLE | entries | index | NULL | user_id | 13 | NULL | 98326 | Using where; Using index |\n +----+-------------+---------+-------+---------------+---------+---------+------+-------+--------------------------+\n 1 row in set (0.00 sec)\n\nこのように、type=indexでインデックスフルスキャン、ExtaにUsing indexが入っているのでCovering Indexで処理されます。created\\_atが先頭にきているインデックスでなくても、created\\_atが含まれていればそれを利用するようです。\n\nこの動作を使って、posted_by=2を探す範囲を制限かけるのが、最初のクエリ。\n\n SELECT COUNT(*) FROM entries a, entries b\n WHERE a.id = b.id\n AND a.created_at BETWEEN '2011-09-10 00:00:00' AND '2011-09-10 23:59:59'\n AND b.posted_by=2\n\naとb、同じテーブルを用意し、まずCovering Indexにてa.created\\_atから.a.idを求めています。user\\_idインデックスにはプライマリキーが含まれていないように見えますが、全てのセカンダリーインデックスにはプライマリキーが暗黙的に付加されます。そして、a.id = b.id とプライマリキーでテーブル結合し、検索範囲が狭めれたところで b.posted\\_by=2 は1行1行読み込んで調べます。\n\n実際、この方法で某サービスでは30分ぐらい掛かっていたクエリが数秒に短縮されました。\n\n注意するのは、これが有効になるパターン\n\n a.created_at BETWEEN '2011-09-10 00:00:00' AND '2011-09-10 23:59:59'\n\nこれで見つかる範囲がテーブル全体に対して広い場合。セカンダリインデックス => プライマリキーで検索というランダムリードの回数が多くなり、テーブルスキャン（シーケンシャルリード)よりも速度が落ちますので注意してください。どっちが効率がいいのかはテーブルの構造やデータ容量にもよりますので、やってみて判断するのがいいかと思います。\n\n突っ込みお待ちしています。\n