実行計画での 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を触っていると基本的な仕草になってくるが、どこまで細かく見られるかというのはかなり違いがある(自分もとても詳しいわけではない)と思われ、「あれ?このクエリ?」と思うことがあったら都度勉強しなおしていきたい。