MySQLの実行計画で Using intersect (...)な index_merge には要注意
実行計画での typeカラムに index_mergeが出たら
MySQLで、WHERE句で複数の条件をANDやORで繋いでいるクエリの実行計画をとると、 type カラムに index_merge というのが出ることがある。これはインデックスマージ最適化というものが使われるということを示している。
インデックスマージ最適化とは
インデックスマージ最適化とは、本来は単一のクエリではインデックスが1つしか利用できないところ、複数のインデックスを使ってそれらをマージすることで最終的な結果を得るというアルゴリズムを使う最適化だ。
これに対し、MySQL 5.0 以降のバージョンでは、クエリが両方のインデックスを使用でき、それらを同時にスキャンして結果をマージできる。OR条件の和集合、AND条件の積集合、2つの組み合わせの積集合の和集合という3種類のアルゴリズムがある。 （『実践ハイパフォーマンスMySQL 第3版』168ページ）
3種類のアルゴリズムのうちのどれが使われるかは、実行計画のExtra カラムで知ることができる。
Using union(...): 和集合アクセスアルゴリズムUsing intersect(...): 共通集合アクセスアルゴリズムUsing sort_union(...): ソート和集合アクセスアルゴリズム
「最適化」だから安心していい？
これらは「最適化」なので、一見すると「ありがとうMySQL」となりそうなのだが、実際にはその名に反して、クエリに問題があることを示唆しているケースがある。
インデックスマージ戦略が非常にうまくいくこともあるが、実際には、テーブルがうまくインデックス付けされていないことの裏返しであることのほうが多い。
（『実践ハイパフォーマンスMySQL 第3版』169ページ）
たとえば、 Using intersect(...) であれば、これはWHERE句で指定しているすべてのカラムを含んだ複合インデックスが作られているべき場面に個々のカラムへのインデックスしかない場合に出ていることがある。具体例として、以下のようなクエリを考える。
SELECT * FROM products WHERE category_id = 1 AND publication_year = '202209';
このクエリの実行計画で、 Using intersect(...) となっているとしたら、それは category_id と publication_year のそれぞれのカラムにインデックスが付いている場合だろう。その場合、個々のインデックスをスキャンし、category_id のインデックスについては category_id = 1 だが publication_year が 202209 ではない部分、publication_year のインデックスについては publication_year = '202209' だが category_id が 1 ではない部分も、それぞれ一旦（インデックススキャンの結果として）取得され、その後で共通部分をとって（マージして ）、テーブル本体にアクセスすることになる。
それぞれのインデックスの選択性が高ければ大きな問題にはならないが、選択性の低いインデックスの場合は、途中のインデックススキャンの段階で無駄なリソースを消費し、パフォーマンスも悪くなってしまう危険性がある。
このクエリの場合、category_id, publication_year のような複合インデックスがあれば、最初からインデックス上の category_id = 1 かつ publication_year = '202209' の部分のみを使ってテーブルにアクセスでき、パフォーマンスの改善につながるケースがある。
Using intersect(...) を見過ごしてしまいやすい理由
ここまで書いたことは、実行計画の typeカラムを見て index_merge になっていることに「おや？」と思えれば調べられるだろうが、あまり詳しくない人は見逃してしまう危険性がある。
実行計画を見て、 type が ALL になっていたり、 rows に巨大な数値が出ていたりすれば、「このクエリはまずそうだ」と思いやすいが、 index_merge で Using intersect(...) となる場合、 rows カラムに出る数値はあくまで「最終的に読み込まれる行数の見積り」なので、先述したインデックスマージの途中で読み取られるインデックスの範囲のうちマージ時に（共通部分ではなかったために）除外される部分については rows カラムの数値には含まれない。 rows カラムに小さな値が出ているからといって、パフォーマンスが良いとは限らないというわけだ。
実行計画を見るというのは、ある程度DBを触っていると基本的な仕草になってくるが、どこまで細かく見られるかというのはかなり違いがある（自分もとても詳しいわけではない）と思われ、「あれ？このクエリ？」と思うことがあったら都度勉強しなおしていきたい。
