MySQLのSQL文で間違った書き方。修正後は28倍速くなった！［日付編］

先日仕事で重いSQLを調べていたら、うっかりミス的なSQLの書き方を見つけたので紹介します。

得られた結果は同じですが、日付の扱い方によって28倍も速くなった事例です。

本番環境ではもう少し複雑なSQL文でしたが修正したら桁違いに速くなりました。

検証条件＆サンプルデータ

今回の検証は下記の条件です。

• 約21万件のアクセスログ。
• ストレージエンジンは、「InnoDB」
• クエリキャッシュは、「無効」
• インデックスは、「created_at」

本番環境は、数百万件、クエリキャッシュは有効になっています。

// サンプルデータ件数
mysql> SELECT COUNT(*) FROM `logs`;
+----------+
| COUNT(*) |
+----------+
|   217196 |
+----------+

例：アクセスログから24時間以内のデータを取得する

今回は、アクセスログから24時間以内のデータを取得する例で紹介します。

ログデータから24時間以内のデータを取得する場合、INTERVAL関数を使い「INTERVAL 1 DAY」や「INTERVAL 24 HOUR」と指定して24時間以内のデータを取得すると思いますが、この条件をどう書くかによって処理時間に大きな違いが出ました。

間違ったSQL文

今回のうっかりSQL文です。

// 修正前
mysql> SELECT COUNT(*) FROM `logs` WHERE DATE_ADD(created_at, INTERVAL 1 DAY) >= NOW();
+----------+
| COUNT(*) |
+----------+
|     2818 |
+----------+
1 row in set (0.28 sec)

ここで気がついたら素晴らしい。

修正したSQL文

// 修正後
mysql> SELECT COUNT(*) FROM `logs` WHERE `created_at`>= NOW() - INTERVAL 1 DAY;
+----------+
| COUNT(*) |
+----------+
|     2818 |
+----------+
1 row in set (0.00 sec)

うっかりSQLと同じ件数を得られていますが、処理時間は、「0.28 sec → 0.00 sec」と格段に速くなっています。

※ 0.28 sec → 0.00 secだとショボい感じで申し訳ないですが･･･。

ポイントは、カラム側の日付にINTERVAL関数を使うか、値側にINTERVAL関数を使うかでインデックスの使われ方に違いがあります。

MariaDB> SELECT COUNT(*) FROM logs WHERE DATE_ADD(created_at, INTERVAL 1 DAY) >= NOW();
|     2814 |
1 row in set (0.051 sec)

MariaDB> SELECT COUNT(*) FROM logs WHERE created_at >= NOW() - INTERVAL 1 DAY;
|     2814 |
1 row in set (0.001 sec)

| type  | possible_keys | key        | rows   | Extra                    |
| index | NULL          | created_at | 217878 | Using where; Using index |   <- 修正前
| range | created_at    | created_at |   2814 | Using where; Using index |   <- 修正後

MySQLのEXPLAINでインデックス状況

今回のSQLは最小限の状態ですが、たったこれだけの事で処理速度に違いが出ました。

それはなぜか？

インデックスの使われ方に違いがあります。

// 修正前
mysql> EXPLAIN SELECT COUNT(*) FROM `logs` WHERE DATE_ADD(created_at, INTERVAL 1 DAY) >= NOW();
+------+-------------+-------+-------+---------------+------------+---------+------+--------+--------------------------+
| id   | select_type | table | type  | possible_keys | key        | key_len | ref  | rows   | Extra                    |
+------+-------------+-------+-------+---------------+------------+---------+------+--------+--------------------------+
|    1 | SIMPLE      | logs  | index | NULL          | created_at | 5       | NULL | 186980 | Using where; Using index |
+------+-------------+-------+-------+---------------+------------+---------+------+--------+--------------------------+
1 row in set (0.00 sec)


// 修正後
mysql> EXPLAIN SELECT COUNT(*) FROM `logs` WHERE `created_at`>= NOW() - INTERVAL 1 DAY;
+------+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
| id   | select_type | table | type  | possible_keys | key        | key_len | ref  | rows | Extra                    |
+------+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
|    1 | SIMPLE      | logs  | range | created_at    | created_at | 5       | NULL | 2815 | Using where; Using index |
+------+-------------+-------+-------+---------------+------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)

修正前のSQL文は、
「type=index、rows=186980」

修正後のSQL文は、
「type=range、rows=2815」

結果は一目瞭然ですが、修正前のSQLはフルインデックススキャン（type=index）が行われています。

一方、修正後は「type=range」となっており、インデックスが上手く使われているのが分かります。

たったこれだけの違いですが、インデックスの存在を意識してSQLの条件を書かないとこうなります。

今回は、結果が得られれば良いと思ってSQLを組んでいると、、

結果的に、サイト利用者の時間を28倍も無題にするかもしれない事例の紹介でした。

ちょっと大げさに書きましたが、もしサイトが100万PVあったら1秒速くなるだけで、100万秒の無駄がなくなります。

• 分にすると16,666分
• 時間にすると694時間

一人1PVだと1秒しかと思うかもしれませんが、サーバーの立場で考えると100万秒ものリソースが無駄になっています。

データベースを扱う上で、インデックスの存在はすごく大事なので常に意識してSQLを書きましょう。

もう少しSQLを勉強したいなら、「達人に学ぶ SQL徹底指南書」とか参考になると思います。