MySQLのサブクエリは危険？――深まる謎
最近、MySQLに振り回されていました。
私もエンジニアですから、普段はこういうこともしているんですよということで、今回はMySQLの技術的な話題を書いてみたいと思います。
初めに、強調しておきます。
MySQLでネストするクエリ（サブクエリ）は絶対に使ってはいけません。
このことを甘くみると、私のように振り回されてしまいます。世の中のエンジニアの方々には、声を大にしてお伝えしたい。レアケースの話ではなく、MySQLを使う場合の大前提として捉えてもよいほどです。
ちなみに、なぜこのような動作になるのかはまだ全然分かっていません。追って調べていこうと思っているので、分かり次第コラムの中でも書けるかもしれませんが、何かご存じの方はぜひコメントをください。
◆ 急に遅くなるクエリ
アプリケーションの開発や運用をしていて、RDBMSへのクエリが急に遅くなった、という問題を経験をしたことがない人はいないのではないでしょうか。私もこれまでに何度も経験をしてきていますが、最近またもやこの問題に遭遇してしまいました。
ただし、今回がこれまでと違って難航を極めたのは、実行計画そのものは大きくは変わっていないということです。ORACLEやPostgreSQLであれば、時間の経過と共にデータの分散具合が変わったりして統計情報に変化が生じ、同じクエリでも実行計画が影響を受けて速度が遅くなったりすることがあります。この場合にはExplain Analyze（Postgres）やEXPLAIN PLAN FOR（ORACLE）を使って実行計画を確認して、クエリのどの部分の実行に時間がかかっているのかという謎を解き明かしていきます。
私がORACLEやPostgreSQLに比べて、MySQLに慣れていないということが多分に影響しているのですが、今回も同じような方法で取り掛かろうにもなかなか解決策（回避策？）にたどりつけなかったのです。
◆ MySQLのサブクエリ
MySQLがサブクエリの実行に弱い（期待に反して実行に時間がかかることがある）というのは、よく知られた話です。これについて若干おさらいをしておきましょう。MySQLがクエリをどのように判定したかを確認するためには、EXPLAINやEXPLAIN EXTENDEDを使って確認します。
>mysql> EXPLAIN EXTENDED
-> SELECT * FROM (
-> SELECT lock_time FROM mysql.slow_log
-> ) t\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 43665
filtered: 100.00
Extra:
*************************** 2. row ***************************
id: 2
select_type: DERIVED
table: slow_log
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 43665
filtered: 0.00
Extra:
2 rows in set, 1 warning (1.05 sec)>
上の例では、MySQLサーバに対して発行されるクエリのうち、実行に指定時間以上必要となったクエリを記録するログテーブルに対して検索を実行しています。このテーブルにログを出力させるためには、以下のように設定します。この例では実行に3秒以上かかったクエリをmysql.slow_logというテーブルに出力するように設定しています。
slow_query_log=1
log_query_time=3
log_output=TABLE
これは極めてシンプルな例ですが、クエリが複雑になればなるほど、この結果からいろいろなことを読み取ってクエリが遅くなる原因を探っていかなければなりません。
EXPLAINの結果でselect_typeで表示されるのが、MySQLがそのテーブルの捜査をどのように行うと判断したか（SELECTの種類）ということです。この例では PRIMARY と DERIVED となっていますが、これがDEPENDENT SUBQUERYであると、外側のクエリに依存するので、外側のSELECTの結果行数だけサブクエリを実行することになります。
オプティマイザによって書き換えられる（最適化された）クエリが、元のクエリの意に反して必要のない結合をしてしまっていたり（SUBQUERYやDERIVEDになるべきサブクエリがDEPENDENT SUBQUERYとして解釈されてしまう）することもあるようです。本来必要のない回数だけテーブルやインデックスを捜査することになるわけすから、こうなってしまうと速度に影響を与えることになり、MySQLがサブクエリに弱いといわれたりします。
しかし、今回の問題はこれに起因するようなものではありませんでした。
◆ サブクエリにするだけで遅いのはなぜ？
先の例ではmysql.slow_logテーブルから全件のlock_timeの値を取ってくるという単純なクエリを意味もなくサブクエリにして実行しています。この実行には1.05秒かかったようです。さて、このクエリからまったく無意味な外側のクエリを削除します。
mysql> EXPLAIN EXTENDED
-> SELECT lock_time FROM mysql.slow_log\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: slow_log
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra:
1 row in set, 1 warning (0.01 sec)
あら不思議。0.01秒で完了しました。
これはどういう仕組みで起こるのでしょう。この例ではEXPLAINの結果を出していますが、これは普通のクエリにするとフェッチにものすごく時間がかかるのが嫌だからです。EXPLAINではないSELECTにしても結果に大きな差はありません。
私が解決しなければならなかったクエリも、プログラムの作り上無駄だと知りつつ、仕方ないと思っていたサブクリの部分をなくすようにしただけで、実行時間が100分の1程度になりました。
今回この問題を調べていて、サブクエリをなくすだけで実行時間が短くなるということに気付いたのは、正直なところ偶然でした。高速化したいクエリの一部分をEXPLAINで確認しては早くなりそうなところにあたりをつけて修正する、という作業を繰り返しました。修正しては全体で実行してみて変化があるかどうかを確認して、効果がなければまた元のクエリに戻って別の部分をEXPLAINで確認します。それを行ったり来たりしていたのですが、あるときふと、どうも意味が分からない変化があることに気付きました。
そこで思いつきでサブクエリだけを削除して、外側に付けていた条件句を内側のクエリに足して実行してみるとなんと激速。サブクエリの構造があまりにも単純だったので、そこを変更すると何かが変わるという発想がありませんでした。
◆ サブクエリは使わない
実際の開発においては、サブクエリを本当に一回も使わずに作るというのは難しいかもしれません。しかし、サブクエリを実行するというのは、このような影響があるのだと意識した上で判断をしていきたいものです。
皆さんもお気を付けください。