MySQLのsemijoin最適化バグを調査した話

MySQL

背景
調査過程
真の原因
対応方針
学び

背景

【エンジニア募集中】フルリモート可◎、売上/従業員数9年連続UP、平均残業8時間、有給取得率90％、年休124日以上 etc.　　詳細はこちらから＞

あるバッチ処理システムの集計クエリで、特定の契約条件下のデータが取得されない事象が発生した。APIはエラーを返さず、ログにも異常なし。テストも通っていたにもかかわらず、本番データが静かに欠落していた。

調査過程

問題のサブクエリでは、クエリ内のEXISTS句内でSUBSTRING関数を下記のように抽出条件に用いていた。

-- ※架空のサンプルです（テーブル名・カラム名は実際のものではありません）

-- 変更前
SELECT
...
LEFT JOIN (
  SELECT ...
  FROM table_A
  WHERE column_a = ...
  AND column_b = ...
  AND EXISTS (
    SELECT ...
    FROM table_b
    WHERE column_d = ...
    AND column_e = SUBSTRING(
        DATE_FORMAT(
          ...
        ), 1, 6
    )
  )
)

-- ※架空のサンプルです（テーブル名・カラム名は実際のものではありません）

-- 変更前

SELECT

...

LEFT JOIN (

SELECT ...

FROM table_A

WHERE column_a = ...

AND column_b = ...

AND EXISTS (

SELECT ...

FROM table_b

WHERE column_d = ...

AND column_e = SUBSTRING(

DATE_FORMAT(

...

), 1, 6

)

まずEXPLAIN FORMAT=JSONとオプティマイザトレースを用いてSUBSTRING版とDATE_FORMAT版の実行計画を比較した。実行計画の構造（dependent subquery / eq_ref / PRIMARY KEY lookup）は両者で完全に同一。関数が返す文字列値・HEX値・バイト長も一致していた。差異はlookup keyに使われる関数式のみ。「関数式の内部型属性差が原因では」と仮説を立て、DATE_FORMAT に変更したところ、問題が解消した。

-- 変更後
SELECT
...
LEFT JOIN (
  SELECT ...
  FROM table_A
  WHERE column_a = ...
  AND column_b = ...
  AND EXISTS (
    SELECT ...
    FROM table_b
    WHERE column_d = ...
    AND column_e = DATE_FORMAT(
          ...
    )
  )
)

-- 変更後

SELECT

...

LEFT JOIN (

SELECT ...

FROM table_A

WHERE column_a = ...

AND column_b = ...

AND EXISTS (

SELECT ...

FROM table_b

WHERE column_d = ...

AND column_e = DATE_FORMAT(

...

)

真の原因

追加調査として SET SESSION optimizer_switch = 'semijoin=off'; を実行したところ、変更前の式のままでもデータが正しく取得できることが判明した。
根本原因は MySQL 8.0 の semijoin 最適化バグ(※)だった。EXISTS句を含むサブクエリに対してセミジョイン変換が適用される際、依存関係の評価に不具合が生じ、意図しないデータ欠落が発生する。SUBSTRING(DATE_FORMAT(...)) という関数ネストがこのバグを顕在化させるトリガーになっていた。
※https://bugs.mysql.com/bug.php?id=110819

対応方針

今回は以下の3案を検討し、2を採用した。

No	内容	特徴
1	問題クエリの式をDATE_FORMATに変更	個別対応・影響範囲が最小
2	DBパラメータで`semijoin=off`	全体一括・パフォーマンスへの影響要確認
3	接続文字列でSET SESSION	接続レベルでの制御が必要

学び

・「同じ値を返す関数式でも、オプティマイザの挙動が変わることがある」という事実はドキュメントからは読み取りにくく、実際の障害で初めて気づくことが多い
・EXPLAIN FORMAT=JSON とオプティマイザトレースの組み合わせは、実行計画の微細な差異を特定するのに有効だった
・仮説（関数式差）→検証→別の視点（semijoin）という段階的な調査アプローチが根本原因の特定につながった

【エンジニア募集中】フルリモートも◎(リモート率85.7％)、平均残業8時間、年休124日以上、有給取得率90％ etc.　詳細はこちらから＞