部分インデックス対応状況

Question

mysql5.7で部分インデックスは使えますか？IN句をもちいて、処理中、保留中、待機中のフラグカラムを持ったテーブルを条件つきで参照するインデックスが使いたいです。

また、厳密にインデックスにおいて速度を追求するアプリを作る必要がある場合、mysqlで現在でも非対応なのであれば、postgresを使うべきでしょうか？

それとも、現実的にそこまでのチューニングは商用サイトにおいてする必要がないと思っていいのでしょうか？（実際yahooやgoogleなんかもmysqlで動いてるわけですし）

Answer 1

mysql5.7で部分インデックスは使えますか？

まず、2018年の時点ではMySQLはどのバージョンでも部分インデックスをサポートしていないようです

• sql - Does MySQL support partial indexes? - Stack Overflow

ただし、MySQLはバージョン8以降でインデックスにCASE句を含められるようになっているので、これを使えば部分インデックスと同様の効果が得られるかもしれません

• indexing - Workaround in mysql for partial Index or filtered Index? - Stack Overflow

また、厳密にインデックスにおいて速度を追求するアプリを作る必要がある場合、mysqlで現在でも非対応なのであれば、postgresを使うべきでしょうか？

速度が欲しい場合は部分インデックスでも通常のインデックスでもそこまで差は出ないはずです。ただし通常のインデックスの方がデータ容量は大きくなります。

---

処理中、保留中、待機中(フラグ)の区分しかないカラムであれば、インデックスはほとんど意味がありません。

この辺りちょっと補足の必要がありそうなので書きます

処理中、保留中、待機中のフラグカラムを持ったテーブルを条件つきで参照するインデックスが使いたいです。

処理済み を意味するフラグは不要なのでしょうか

もし処理済みのレコードが残るようなテーブル設計ならば、部分インデックスでなくても普通のインデックスをはることが有効です。なぜならそのテーブルを何年も運用すると履歴が積み重なり、そのテーブルは 処理済み にレコードが大多数になるはずだからです。

仮に99%のレコードが 処理済み で、その他のステータスになっているレコードが1%しかないのなら、インデックスを使えばMySQLが調べるレコードは100分の1で済むようになります

もちろん部分インデックスと比べると通常のインデックスはディスク容量を食いますが、メリットのほうが大きいはずです

Answer 2

処理中、保留中、待機中(フラグ)の区分しかないカラムであれば、インデックスはほとんど意味がありません。
カーディナリティが3ですから、均等に分散しているなら、1区分あたり33％。
インデックスはレコード全体の20～25％未満のデータを抽出するのに効果があるのであって、大量にヒットするときは意味がないのです。

例えば、20人の部署に2名の女性がいるとします。

その場合、「名前は分からないけれど、女性の方」でほとんど特定できますから、性別にインデックスを付けて使う意味があります。
逆に、「名前は分からないけれど、男性の方」で、男性を集める(性別にインデックスを付けて使う)ことに意味はないのです。

もし、数億レコードにもなり、区分ごとに処理することがクリティカルであるならば、パーティーションを区切ることをお勧めします。