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

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

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

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

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

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

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

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

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

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

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

間違ったSQL文

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

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

修正したSQL文

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

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

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

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

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

それはなぜか?

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

修正前の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徹底指南書」とか参考になると思います。

読んで頂いて有り難うございます!