サブクエリオプティマイザが有能すぎた話

cloudpackあら便利カレンダー 15日目です。

コードレビューで実行されるクエリを確認していたところ、
本番で動かしたらデータ量が多いから処理が止まるぞと思ったクエリが、
高速に動作したのでそれについて調べたことをまとめます。

状況

  • MySQL 5.6.10a （Amazon Aurora）

どんなテーブルか

今回発生したケースに似たテーブル構成で再現してみます。
※学校の先生から見るとデータとして足りないものがあると思いますが許してください。

  • 学生テーブル
    • 生徒IDがprimary
    • 学年、所属クラスがindex
  • 成績テーブル
    • 生徒IDが外部キーインデックス
    • 定期試験が行われるごとにデータがどんどん増えていく

1対Nのテーブル構成で、成績は生徒IDで引っ張る形なので他のindexがない、という感じです。

でもたまにこんな要望でるよね

卒業生で数学に100点つけたのって何人いたっけ
このクエリ実装としては以下の2パターンになると思います。

1） 先に学生を絞り込む
卒業済みの生徒IDのリストを取得して、
それを基に成績テーブルprimaryで絞り込んで数学100点かどうかチェックする

デメリット：卒業済みの学生が多い場合検索対象が非常に多くなる

2) 先に成績で絞り込む
数学100点の生徒IDのリストを取得して、
それを基に成績テーブルprimaryで絞り込んで数学100点かどうかチェックする

デメリット：成績テーブルが大きい場合検索対象が非常に多くなる

どっちもメリットは学生:成績の1:N関係のNが大きければ1のほうが優位、
小さければ2の方が優位という感じと私は認識しています。
で、今回実は定期試験が非常に多いパターンでした。
対比が1:100くらいになっていたので、明らかに1の実装で行うべきという所感でした。
ところが実装は2で行われていたのでこれは危ないと思ったのですが、、、

何が起こったのか

-- 説明用日本語クエリ
SELECT
    COUNT(*)
FROM
    `学生`
WHERE
    `生徒ID` IN (
        SELECT 
            DISTINCT `生徒ID`
        FROM
            `成績`
        WHERE
            `数学` = 100
    )
    AND `卒業フラグ` = 1

サブクエリ内で先に成績テーブルをフルスキャンする状態になっていました。
サブクエリだけをexplainした結果が以下の画像です。
rows が 2,338,344 となり完全にフルスキャンでした。

しかし、実際に流したクエリは以下の画像の通り、先に学生テーブルをindexで絞り込み、
最終的に成績テーブルは7件しかチェックしていないという結果になりました。

ということで何が起こっているのか調べました。
MySQL 5.6 リファレンスマニュアル 8.2.1.18 サブクエリーの最適化

MySQL5.6.6以降で、サブクエリの最適化が行われるようになり、
その中の、SEMI JOIN（順結合変換）というのが行われていました。
確認方法は下記の通りです。

  • EXPLAIN EXTENDED
    • EXPLAINと使い方は同じ、EXTENDEDをつけるだけで通常より詳細な情報を取得できます。
  • SHOW WARNINGS
    • EXPLAIN EXTENDED の次に実行すると、 EXPLAINによって生成された追加の情報を表示できます。

SHOW WARNINGSを実行した結果がこちら。

/* select#1 */ select count(0) from `学生` semi join (`成績`) 
    where ((`成績`.`生徒ID` = `学生`.`生徒ID`) 
        and (`学生`.`卒業フラグ` = '1')
        and (`成績`.`数学` = '100))

成績をフルスキャンしてIN()で学生の絞り込みをしようとしていた所が、
準結合されて絞り込みの演算が簡略化されています。

準結合変換の条件は細かく設定されており、公式のドキュメントでは下記の通りです。

  • それは、おそらく AND 式内の項として、WHERE 句または ON 句のトップレベルに表示される IN (または =ANY) サブクエリーである必要があります。
  • それは UNION コンストラクトのない単一の SELECT である必要があります。
  • それには GROUP BY または HAVING 句または集約関数が含まれていてはなりません。
  • それには、LIMIT を使用した ORDER BY があってはなりません。
  • 外部テーブルとおよび内部テーブルの合計数が結合で許可されている最大テーブル数より少なくなければなりません。

つまり簡単にまとめるとWHERE句でIN()の中に入れるサブクエリで、
UNION/GROUP BY/HAVING/LIMIT/ORDER BYを使わなければ、
準結合変換が行われる可能性があるということですね。

まとめ

クエリが遅そうに見えてもオプティマイザが良い感じに高速化してくれるあら便利な時代になりました。
実際にどう実行されるかはやはりオプティマイザ次第なので、
まずはEXPLAINを（EXTENDEDオプションも忘れずに）かけて確認すると良さそうです。
適当なクエリでも高速化してくれるから良いじゃん（いいじゃん）とあぐらをかかずに、
そのままでも高速に動作するクエリを実行するように心がけるといいかなと思いました。

元記事はこちら

「MySQLであら便利なサブクエリ最適化を知る」