使用されず役に立たないインデックスを定義するのは、SQLアンチパターンの1つ「インデックスショットガン」として知られています。使用されていないインデックスを定義するのは、ディスク容量を圧迫して、さらに更新コストも掛かるという良いこと無しな状態です。
ただ実際には、あなたが使用されていないインデックスを見つけたとしても、安易にドロップするのは非常に危険です。ドロップするのは時間がかかりませんが、インデックスを再構築するまでには時間がかかります。
もしも万が一そのインデックスが使用されているクエリが存在するとしたら、その時点から障害につながってしまう可能性があります。ドロップはしたくないけど、使わないようにして影響を確認したい……、今回はそんな時に便利なMySQL 8.0の新機能「Invisible Indexes」を使ってインデックスを外した時の影響を調べてみましょう。
検証環境
今回はDockerHub上のMySQLイメージを利用して行います。バージョンは8.0.18を使用しています。
上記の操作で起動して、以下のようにMySQLに接続をしていきます。
検証用のテーブルとデータ
検証用のデータとしては、第2回 MySQLにはじめてのデータを入れてみるからKEN_ALL.CSVを利用して作成していきます。
第2回と異なる点としては、テーブルの構造を改変して以下の様にします。zip_codeとold_zipcodeの複合インデックス、そしてzip_code単体でのインデックスを追加で作成しています。
データベースにアクセスしてみて、以下のクエリを打って同じ結果が得られていれば大丈夫です。
無駄なインデックスを特定する
現在使われていないインデックスを特定する必要があります。今回は第9回 pt-query-digestを使って遅いクエリーを発見するでも紹介したPercona Toolkitのpt-duplicate-key-checkerを使う方法と、MySQL5.7から追加されたsys.schema_unused_indexes
を利用する方法を紹介したいと思います。
pt-duplicate-key-checker
こちらは以前Percona Toolkitの1つで、これを使うと名前の通り重複したキーやインデックスを発見することができます。Percona Toolkitのインストール方法に関しては解説しません。aptやyum、Homebrewといったソフトウェアで簡単にインストールができるので、OSやディストリビューションに合わせてインストールをしてください。公式のインストールガイドはこちらになります。
というわけで実行をしてみましょう。
結果の部分が2つあり、どこに重複するキーやインデックスが存在するのかというのが上部に表示され、下部にはサマリーが書かれています。サマリーから、インデックスが2個あって、そのうち1個が重複していることがわかります。上部からは詳細なメッセージと、そのインデックスを除外するためのDDLが出力されています。
また、この結果をファイルとして取っておいてMySQLクライアントで実行することで、重複するキーを一気に除外することも可能です。
sys.schema_unused_indexes
こちらはMySQL 5.7から導入されたviewで、MySQLが起動してから一度も使用されていないindexを表示してくれます。
まだクエリを一度も発行していないので、2件とも検知されていることがわかります。そこで、試しに下記のクエリを実行してみましょう。
実行後再度確認をしてみると、以下のようになるはずです。
気をつける点としては、後から追加したindexが使用されるようになって不要になっているindexがあっても、上手く検知ができない事に注意が必要です。また、十分な期間を置いて実施しないと、月次のバッチ等で使用しているindexを誤って無効にしてしまう可能性があるので注意しましょう。
Invisible Indexesを試してみる
さてここまでで不要なインデックスに関して簡単に知ることができました。さぁドロップをしてみましょう!…… といきたいところですが、冒頭に書いたとおり、開発環境や検証環境なら良いと思いますが、本番環境でいきなりDROP INDEXするのはかなり恐ろしいです。
というわけで、一旦使用されなくなるInvisible Indexesを使ってみましょう。
この時点の状態では、index_zipcode_on_zip_code_and_old_zip_code
が選択されていそうという事がわかります。ここで以下のクエリを実行し、invisibleな状態にしてみます。
この状態で再度explainを取ってみましょう。
ということでオプティマイザーから選択されない状態になりました。
続いて元に戻してみましょう。
元の状態に即座に直すことができました。使い方は簡単で、visible, invisibleのトグルになっていることはわかっていただけたと思います。
さて、ちょっと不安になることとして、ヒント句を指定している場合にどういった挙動になるか気になると思います。
一旦インデックスを再びinvisibleにします。その状態で、わざとinvisibleにしたindex_zipcode_on_zip_code_and_old_zip_code
を指定をしてみましょう。
エラーになってしまいました。というわけで、ヒント句を使用していたとしても実は使われていたという事にはなりません。ちなみに、存在しないindexを指定するとエラーになってしまうので、逆にヒント句を多用している場合には、事前に書き換えておく必要があるので注意をしましょう。
まとめ
今回は重複したキーやインデックスを調査し、一時的に使用しないようにする方法について説明しました。これから年末に向けて大掃除の時期が近づいておりますが、皆様のMySQLのインデックスの掃除にご利用ください。