MySQL インデックスマージの謎

最近運用しているシステムのINDEXチューニングをしています。
しかしながら、一度は大幅に改善したSQLが依然としてSlowQueryが出ていたので追跡調査した結果を共有してみます。

対象のSQLをそのまま貼ることはできないのですが、ざっくりいうと以下のようなSQL

SELECT
  COUNT(*) AS `count` 
FROM
  items
WHERE
  shop_id = N
  AND register_datetime BETWEEN 'S' AND 'S'
  AND status = N
  AND ((deleted_flg = 'S') OR (deleted_flg IS NULL)) 

このSQLはもともといくつか問題のあったものを改善した結果なので、一見実行計画もINDEXも問題ありません。

【前提1】MySQLに限らず、DBMSはどのINDEXを使うのが適切か自動的に最適化してくれる

せっかく設定したINDEXが使われないというのはよくあるケースです。
それは実行計画を行った環境に問題があることがほとんどです。
INDEXはそもそも万能ではないので、ケースによっては全件検索したほうが早いケースもあり、そのあたりはDBMSがいい感じに判断してくれます。（具体的にはデータ量によって挙動が変わる事が多い）
なので、テスト環境（結合やステージング）では狙ったINDEXが使用されているのに本番環境では使用されないというのはよくある話です。
実際、今回のSQLを本番環境で動作させてみると意図したINDEXが使われていませんでした。

【前提2】MySQLのINDEXは1テーブルに付き1つしか使われないけどインデックスマージという仕組みがある

前提と言っておきながら自分も最近教わったばかりなのですが、MySQLはINDEXを1テーブルに付き1つしか使わないらしい。
今回のSQLではitemsに対して複数の検索条件が指定されていますが、いくらINDEXを貼っても1つしか使ってくれないようです。
とはいえその代りにMySQLにはインデックスマージという仕組みがあるそうです。

自分の理解としては、複数の使えるINDEXがある場合、その結果をマージすることによってわざわざ複合INDEXを使わなくてもそれっぽい結果が得られると思っています。

正確に知りたい人は以下の公式文章をどうぞ
MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.2.1.4 インデックスマージの最適化

「それって結局複数のINDEXが使えてるってことでしょ？じゃあMySQLは事実上1テーブルに付き1つ以上のINDEXが使えるからあまり気にせずにINDEXを貼ればいいんだな！」
これが自分が思ってた落とし穴でした…

実行計画を見比べてみる

今回性能改善として実施した内容は、日付型であるregister_datetimeにINDEXが貼られていない、貼られていたとしても活用できるSQLになっていなかったと言うのが問題でした。
一方で、普通に考えてshop_idによる絞り込みも優先度が高いはずです。

それではいくつかの環境で行った実行計画を実際に見てみたいと思います。

テスト環境

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	Reception	NULL	range	PRIMARY, shop_id, status, register_datetime	register_datetime	6	NULL	1	5	Using index condition; Using where; Using temporary; Using filesort

実行計画の見方は色んな所で詳しい解説があるので（ここで間違った説明しても恥ずかしい）他所に譲りますが、ここで注目してほしいのはtype列のrangeです。
前述の通り日付型のカラムにINDEXを貼り、その範囲検索をしているのでそれを示すrangeとでています。

本番環境

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	Reception	NULL	index_merge	PRIMARY, shop_id, status, register_datetime	shop_id, status	4,5	NULL	74	0.07	Using intersect(shop_id,status); Using where; Using temporary; Using filesort

本番は他の環境と異なり、register_datetimeのINDEXを使っていません。
この実行計画ではrowsも少ないからわからなくもないですが、他の実行計画では1000を超えているものもあって、適切なINDEXになっているとは思えません。
とはいえ、DBMSがそう判断したということは、register_datetimeのINDEXを使ったほうがもっとパフォーマンスが出ないと判断されたのでしょう。

しかしながら、本当に欲しいのはそのどちらも含めたINDEXの利用です。
index_mergeなのはいいけど、keyがshop_id,register_datetimeとなっていればHappyなのに…

落とし穴 インデックスマージは万能ではなく、普通にマージできないケースがある

つまりそういうことだと思います。

MySQLの不具合の可能性

先程挙げた
MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.2.1.4 インデックスマージの最適化
を読むと

注記
インデックスマージ最適化アルゴリズムには次の既知の不具合があります。

クエリーに AND/OR の深いネストのある複雑な WHERE 句があり、MySQL が最適なプランを選択しない場合、次の同一律を使用して、項を分配してみてください。
(x AND y) OR z = (x OR z) AND (y OR z)
(x OR y) AND z = (x AND z) OR (y AND z)

インデックスマージは全文インデックスには適用できません。将来の MySQL リリースでこれらを扱うように、それを拡張する予定です。

MySQL 5.6.6 より前では、一部のキーに対して範囲スキャンが使用可能な場合、オプティマイザはインデックスマージ和集合またはインデックスマージソート和集合アルゴリズムを使用することを考慮しません。

のように3種類の不具合が明記されています。
（範囲スキャンが絡んでいるので3番めが怪しいですが、多分本番環境のMySQLは5.7.21なので関係なさそう）

異なるtypeだとマージできない？

裏が取れてないので想像なんですが、今回のケースはINDEXのtypeがrefとrangeという異なるタイプの使い方を期待しているINDEXなので、その場合インデックスマージができないのではと疑っています。

で、結局どうやってINDEXを効かせるのか

複合INDEXを活用するしかないのではと思っています。
個人的に不必要な複合INDEXは効率的なチューニングにならない気がしてますが、今回のケースは裏付けのある選択なのでありと思っています。

具体的には

ALTER TABLE items ADD INDEX (shop_id,register_datetime,status);

というようなINDEXを貼ることでショップIDで絞りつつ受付日時の範囲でスキャンすることが可能になります。
用途的にピンポイントなINDEXになるので、

ALTER TABLE items ADD INDEX (shop_id,register_datetime);

でもいいとは思いますが（statusは取りうる値の種類が少ないのでINDEXの恩恵少なそうだし）、元の実行計画がshop_id,statusのマージとなっていることを考えると含めておくほうがいいかもという判断です。