某サービスのクエリチューニングのお話。

ブログとか日記とかそういうサービス系で次のようなテーブルがあったとします。

CREATE TABLE entries (
  id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  user_id INT UNSIGNED NOT NULL,
  posted_by TINYINT UNSIGNED NOT NULL,  --#PC、mobileなどどこから投稿されたかのフラグ
  title VARCHAR(512) NOT NULL,
  body TEXT NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at TIMESTAMP NOT NULL,
  status TINYINT UNSIGNED NOT NULL,
  INDEX (user_id,created_at,status)
) ENGINE=InnoDB

まぁ、わりとありがちです。順調にサービスが使われ記事数が百万件ぐらいあったとしましょう。ある日

「サービスの改善のため、1日に携帯から登録された件数をレポートとして出して欲しいんだけど」

とディレクターからカジュアルに頼まれました。いろいろ言いたいことはさておき、あなたならどうしますか?

最近上と同じような場面において、元々あったクエリを以下のように変更しました。

SELECT COUNT(*) FROM entries a, entries b
 WHERE a.id = b.id
  AND a.created_at BETWEEN '2011-09-10 00:00:00' AND '2011-09-10 23:59:59'
  AND b.posted_by=2

ちょっと奇妙に見えそうですが、self-joinを使っています。なぜこんなクエリになったのか、順を追って説明します。

通常、1日に携帯から投稿されたエントリ件数をだすには

SELECT COUNT(*) FROM entries 
  WHERE created_at between '2011-09-10 00:00:00' AND '2011-09-10 23:59:59'
    AND posted_by=2;

このように書くでしょう。ただし、インデックスが使えないのでテーブルスキャンとなってしまいます。EXPLAINすると

+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
|  1 | SIMPLE      | entries | ALL  | NULL          | NULL | NULL    | NULL | 98326 | Using where |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)

となります。これを高速に実行するためにはインデックスの追加が必要です。

ALTER TABLE entries ADD INDEX (posted_by,created_at);

こんな感じでしょうか。

ただし今回のテーブルは既に100万件が登録されており、(サーバがそもそも古いとかInnoDB Pluginとか全く存在しないMySQL4.0だったなどなど)ALTERも大変そうです。そこでなんとか既存のインデックスの中で解決する方法を探します。

MySQLではプライマリインデックス、セカンダリインデックスだけでクエリが解決できることをCovering Indexと呼びます。当然インデックスだけでクエリ処理ができるので、高速です。MySQLのオプティマイザはクエリがCovering Indexで解決できる場合、多少効率が悪くてもテーブルスキャンよりインデックスのフルスキャンを選択するようなので、次のクエリのように条件がcreated_atだけのカウントクエリの場合、

mysql> EXPLAIN SELECT COUNT(*) FROM entries 
        WHERE created_at between '2011-09-10 00:00:00' AND '2011-09-10 23:59:59';
+----+-------------+---------+-------+---------------+---------+---------+------+-------+--------------------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows  | Extra                    |
+----+-------------+---------+-------+---------------+---------+---------+------+-------+--------------------------+
|  1 | SIMPLE      | entries | index | NULL          | user_id | 13      | NULL | 98326 | Using where; Using index |
+----+-------------+---------+-------+---------------+---------+---------+------+-------+--------------------------+
1 row in set (0.00 sec)

このように、type=indexでインデックスフルスキャン、ExtaにUsing indexが入っているのでCovering Indexで処理されます。created_atが先頭にきているインデックスでなくても、created_atが含まれていればそれを利用するようです。

この動作を使って、posted_by=2を探す範囲を制限かけるのが、最初のクエリ。

SELECT COUNT(*) FROM entries a, entries b
 WHERE a.id = b.id
  AND a.created_at BETWEEN '2011-09-10 00:00:00' AND '2011-09-10 23:59:59'
  AND b.posted_by=2

aとb、同じテーブルを用意し、まずCovering Indexにてa.created_atから.a.idを求めています。user_idインデックスにはプライマリキーが含まれていないように見えますが、全てのセカンダリーインデックスにはプライマリキーが暗黙的に付加されます。そして、a.id = b.id とプライマリキーでテーブル結合し、検索範囲が狭めれたところで b.posted_by=2 は1行1行読み込んで調べます。

実際、この方法で某サービスでは30分ぐらい掛かっていたクエリが数秒に短縮されました。

注意するのは、これが有効になるパターン

a.created_at BETWEEN '2011-09-10 00:00:00' AND '2011-09-10 23:59:59'

これで見つかる範囲がテーブル全体に対して広い場合。セカンダリインデックス => プライマリキーで検索というランダムリードの回数が多くなり、テーブルスキャン(シーケンシャルリード)よりも速度が落ちますので注意してください。どっちが効率がいいのかはテーブルの構造やデータ容量にもよりますので、やってみて判断するのがいいかと思います。

突っ込みお待ちしています。

このブログ記事について

このページは、Masahiro Naganoが2011年8月14日 00:08に書いたブログ記事です。

ひとつ前のブログ記事は「memcached-1.4.7-rc1でmixiの大規模障害の原因となったmemcachedの不具合が全て解消されました」です。

次のブログ記事は「Software Design 2011年9月号に運用エンジニアに関する記事を寄稿しました 」です。

最近のコンテンツはインデックスページで見られます。過去に書かれたものはアーカイブのページで見られます。

ウェブページ

OpenID対応しています OpenIDについて
Powered by Movable Type 4.27-ja