MySQLのインデックスダイブ（Index Dive）について

Reference

https://dev.mysql.com/doc/refman/8.4/en/range-optimization.html
https://mita2db.hateblo.jp/entry/2020/12/26/113000

概要

• オプティマイザがインデックスの統計情報を得るために、インデックスに「実際にアクセス」して範囲内の行数を見積もる処理。
• MySQL は統計情報（カーディナリティなど）をもとに見積もりするけど、統計が粗い（例: 高度に不均一なデータ）場合、正確性に欠ける可能性があるため、オプティマイザは必要に応じて実際にインデックスを辿って範囲に合致する行数を推測する。これがインデックスダイブ。
• インデックスダイブを利用しない場合は、インデックス統計情報を使用する。※やや精度が粗くなる

インデックスダイブは正確な行推定値を提供しますが、式内の比較値の数が増えると、オプティマイザによる行推定値の生成に時間がかかります。インデックス統計の使用はインデックスダイブよりも精度は低くなりますが、大きな値リストの行推定値をより高速に行うことができます。

eq_range_index_dive_limit について

• sqlのIN句に指定する要素が多すぎる場合、狙ったインデックスが利用されないケースがある。

• MySQLが実行計画を立てる際に、インデックスアクセスではなく、フルスキャンしたほうが早いと判断する場合がある。これを運用者が適切にコントロールするための設定値。

• デフォルト値は200で設定されており、0にした場合はインデックスダイブを無効化し、インデックス統計情報を使用して推測を行う。

• 注意

  • 200に設定されているからといって、仮にIN句に201個の要素が設定されたらフルスキャンになってしまう。とかではない。
  • あくまでも、この先頭から順にIN句の値について、最大200回までインデックスダイブを実行し、それぞれの値で行数を見積もり、その内容から「インデックスアクセスでいく」or 「フルスキャンのほうが早い」かを評価する。※つまり201個目の要素からは統計情報のみで見積もりされる（インデックスダイブされない）。

    SELECT * FROM users WHERE id IN (1, 2, 3, ..., 1000);
    

✅ 調整の目安

• 値を小さくするとプラン決定が速くなるが、見積もりの精度が落ちる可能性あり。
• 値を大きくすると見積もりは正確になるが、オプティマイザの負荷が増加する。

オプティマイザがフルスキャンを選ぶ条件としてはだいたいこんな時

• IN句の多くの値が 存在しない、または一致する行が非常に多い（高いカーディナリティ）
• テーブルが小さい（インデックスよりもテーブルスキャンの方がコストが安い）
• 複合インデックスのカーディナリティが低く、効率が悪い
• eq_range_index_dive_limit を超える要素があり、全体の選択性が悪く見積もられる