MySQLのwhere ~ inとサブクエリの組み合わせで悩んだ話

MySQL5.5系において

とあるテーブル(tableA)からある条件を満たすIDを重複無しで
取得し、そのIDが含まれるデータをもう一方のテーブル(tableB)から
取得したかった。

tableAからhogeIDが100以上のものをグルーピングして、
そこから取得したhogeIDをもつtableBのデータを全部取得する。

なんてことはない、と思ったんですが、これが異常に実行速度が遅い。

原因を調べてみると、いろいろわかったのでまとめ。
(ここに挙げている問題はmysql5.6系では解消されています。)

まずは前提として知っていると良い項目をいくつか
(僕はこの件を調査している課程で知りました。多分mysqlの
チューニングをちゃんとやる人にとっては常識かもしれません。)


相関サブクエリ(相関副問い合わせ)と、そうでないサブクエリ

まずは相関のないサブクエリ

WHERE句の後に書かれた()内がサブクエリ。

table_bのidが1から10000までのデータを取り出し、
その一覧に含まれるtable_a.b_idの一覧を取得している。

評価される順番も

  1. サブクエリが実行されtable_b.idが1~10000のリストが作成される。
  2. 親クエリが実行され、1で取得したデータと一致していればデータを残す。
  3. 最終的に残ったデータが取得データとなる。

そして相関サブクエリがこちら。

自分で書いてて思ったのが、『狙わないでこういう書き方するかな・・?』と疑問ですが
サンプルなので許してください><

このSQLに注目してほしいのが
サブクエリに親クエリで参照されているテーブルであるtable_aが
WHERE句に含まれていることです。

これによってサブクエリと親クエリの評価順が変わります。

  1. 親クエリが実行される(全レコード, ここでは100レコードとする)
  2. サブクエリに1で取得したレコードのうちから1つフェッチし、サブクエリ内のwhere句に割り当てる
  3. 割り当てられたときに、初めてサブクエリが実行される。
  4. 2~3が100レコード分繰り返される。

つまりサブクエリが親クエリの結果に依存している状態。これが相関サブクエリ。
相関サブクエリは、相関しているもう一方の結果に依存するので、その結果の数だけ
実行しなければならなくなる。

使わなくて済むのであれば、できるだけそうしたい、でもあると便利な機能。そんな感じです。


MySQLのクエリオプティマイザ、とは

ユーザが入力したSQLをmysqlで実行する前に環境に
応じた最適化を行うmysqlの頭脳的な存在。

どのデータベースミドルウェアにも備わっているもので、性能を左右する
とても大切な存在。


オプティマイザが吐き出したクエリを確認する

割とめんどくさい。

SQLの実行計画を確認するためのEXPLAINの後ろにEXTENDEDを
追加するとWARNINGとして、オプティマイザが吐き出したクエリが出てくる。
なんだろう、すごい、むずむずする。

 まぁこれでユーザが入力したSQLと、実際に実行されたSQLが比較できる、と。


MySQLのwhere~inの欠点

mysql 5.6では解決した問題です。
以下の問題はmysql5.5以下で発生します。

長い前置きからの、ようやく本題。

最初に実行したSQLがこちら。
これを見ると、相関クエリはないように思えます。

しかし、MySQLのWHERE ~~ IN (サブクエリ)はそのまま処理することができず
EXISTSにオプティマイザによって変換される。

これによって

つまり、相関サブクエリへと変換されてしまいました。
だから実行速度が著しく低下します・・・。これは知らないとわからないですね・・・
インターネット万歳。

※繰り返しますが、この問題はmysql5.6系で解消されています。
where ~ inは非相関クエリとして実行することができます。

このようなケースでは
プログラムでinの後を外部で生成してwhere~in句のサブクエリを排除するか、JOINでの結合に
修正したほうが懸命でしょう。

余談

会社で触ったデータベースでこの現象が発生して、不思議だったので
自宅に帰ってから同じようなデータベースを作成して色々試したのに、
手元で再現しなくて当初焦りました。(手元のmysqlは5.6系だった)

現在は解消されているようなので、理解できたときは少し意気消沈しましたが、
まぁ相関サブクエリや、オプティマイザ、MySQLのチューニングのとっかかりを
触ることができたので、まぁよしとします。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です