[MySQL]WHERE句とORDER BY句の両方が使われている場合のINDEX

INDEXを追加することでWHERE句でのレコードの絞り込みが速くなることは、SQLを少しでも学んだことのある人なら当たり前に知っていることだと思う。
システムを運用していく期間が長くなればなるほどレコード数は膨大になり、WHERE句で絞り込んだとしても結果として返されるレコード数が多くなってしまうこともある。
単純に絞り込んだ結果を並び順関係なく出力させる場合は問題ないが、WEBシステムやアプリなどでは、登録された日付が近いもの順でソートしたい場合や、IDが若い順でソートしたい場合など、出力結果をソートしたい場合が多々ある。
そんな時はクエリにORDER BY句を記述することで対処することがあると思うが、実はこのORDER BY句によるソートがボトルネックの原因になってしまうことがある。

WHERE句で指定しているカラムにINDEXが張ってある場合

以前の記事で利用したsampleテーブルにさらにレコードとrankカラムを追加し、合計1100000レコード用意した。

こんな感じのテーブルが今回の検証対象。rankカラムはほとんどNULLで、’A’, ‘B’, ‘C’が10000レコードずつ入っている。
では、このテーブルからrankカラムにindexが張られている状態で、rankが’A’のレコードだけ絞ってみる。
結果が多いので、今回はlimit 10をつける。

INDEXが効いた状態では即レスポンスが返ってくる。
ではこのクエリに、さらにnameカラムの降順で並べるようORDER BY句を加えてみる。

レスポンスが遅くなった。レコード数が大した量ではないので、体感できるほどの差は出ていないが、大量にアクセスのあるシステムだった場合は負荷がかかりさらに開くこともある。
この問題を改善するには、WHERE句でもORDER BY句でも利用できるマルチカラムインデックスの作成を検討する。

ORDER BYでもINDEXが効くようマルチカラムインデックスを作成

今回のクエリを元にINDEXを張る場合を考える。

この場合はwhere句のrankとorder by句のnameを含むINDEXを作成すれば良いが、指定するカラムの順番が正しくなければ効果を発揮しない。

マルチカラムインデックスはカラムの順番に注意

この場合、where句、orderby句の両方にindexが適用されることはない。
※ MySQL5.7では改善されているかも

クエリの評価順序はwhere→order byの順番になるので、indexを作る際はwhere句で使われるカラムから指定する必要がある。

クエリを投げてみる。

即返ってくるようになった。
今回の記事は自社プロダクトで利用しているAurora MySQL 5.6.10aを想定して書いているが、最新のMySQL5.7では諸々再現できなかったので、Optimizerの改善でかなり状況が変わっているかもしれない。

シェアする

  • このエントリーをはてなブックマークに追加

フォローする