スマートスタイル TECH BLOG|データベース&クラウドの最新技術情報を配信

MySQL 8.0 の invisible index について

MySQL 8.0 では invisible index が実装されました。
これは MySQLのオプティマイザーからインデックスを見えなくする(使われなくする)ものです。

MySQL :: MySQL 8.0 Reference Manual :: 8.3.12 Invisible Indexes

invisible index の挙動を確認する

テストデータの作成

以下のようなテーブルを作成しました。

MySQL sandbox で建てた MySQL 8.0 に以下のようにして100万レコード投入します。

実行されるSQLは以下のようになります。c2にランダムで255文字が入ります。

100万レコードの投入が完了しました。

パフォーマンス比較

インデックスを作成する

作成には12秒かかりました。

インデックスを削除する

インデックスの削除は0.11秒と比較的高速に完了しました。

インデックスを INVISIBLE に変更する

こちらは0.01秒で完了しました。

インデックスを VISIBLE に変更する

実際のINDEX作成とは異なり、0.03秒で完了しました。

やはり実際にインデックスを作ったり削除するよりも高速でした。

インデックスの表示、非表示の管理

CREATE TABLE を見ると INVISIBLE になっているインデックスには /*!80000 INVISIBLE */ が付与されています。

SHOW INDEX でも Visible の項目が追加されました。

information_schema.statistics にも IS_VISIBLE の項目が追加されています。

インデックスが使われるかどうかの確認

インデックスが見えている状態だとEXPLAINの結果を見てもインデックスが使われることがわかります。

同様に、インデックスが見えていない状態だと使われないことがわかります。

注意が必要なのは、INVISIBLE の場合、USE INDEXFORCE INDEX を使うとインデックスが見えてないため、エラーになります。

カジュアルにインデックスを INVISIBLE にすると、場合によってはエラーになってしまうかもしれません。

使われていないインデックスを探す

MySQLでは起動してから使われていないインデックスは sys.schema_unused_indexes テーブルに記録されています。

こういった情報を元に使われていないインデックスかどうかの判断をすることになるかと思います。

INVISIBLE 時のインデックスの更新

カーディナリティは 982067 とほぼ100万に近い値です。

インデックスを INVISIBLE に変更してさらに100万行を追加しました。

analyze table を実行してインデックの統計情報を更新します。(インデックスデータ自体を更新しているわけではありません)

カーディナリティが 1913219 と200万に近い値に変化しました。

このことから、インデックスを INVISIBLE に変更しても、実際のインデックスデータは更新され続けていることがわかります。
そのため、例えば長期間に渡って INVISIBLE にしたインデックスであっても VISIBLE に変更した直後から問題なく使えることがわかります。

まとめ

この機能を使うことで、インデックスを削除した場合、どれくらいSELECTのパフォーマンスに影響が出るか簡単に調べることができます。(インデックスの削除や作成を伴わないので比較的簡単に試せる)

ただし、以下の点で注意が必要です。

  • USE INDEX、FORCE INDEX でインデックスを使っていると、そのインデックスを INVISIBLE にするとエラーになってしまう。(事前に sys.schema_unused_indexes テーブルの情報や、アプリなどで使われていないかの確認が必要です)
  • INVISIBLE にしても、インデックスそのものは存在するため、更新処理にかかる負荷は変化しません。

MySQL

 

Return Top