MySQL では、不可視のインデックス (オプティマイザで使用されないインデックス) がサポートされています。 この機能は、主キー以外のインデックス (明示的または暗黙的) に適用されます。

インデックスはデフォルトで可視化されます。 新しいインデックスの可視性を明示的に制御するには、CREATE TABLE、CREATE INDEX または ALTER TABLE のインデックス定義の一部として VISIBLE または INVISIBLE キーワードを使用します:

CREATE TABLE t1 (
  i INT,
  j INT,
  k INT,
  INDEX i_idx (i) INVISIBLE
) ENGINE = InnoDB;
CREATE INDEX j_idx ON t1 (j) INVISIBLE;
ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;

既存のインデックスの可視性を変更するには、ALTER TABLE ... ALTER INDEX 操作で VISIBLE または INVISIBLE キーワードを使用します:

ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE;
ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;

インデックスが可視か不可視かに関する情報は、INFORMATION_SCHEMA.STATISTICS テーブルまたは SHOW INDEX 出力から入手できます。 例:

mysql> SELECT INDEX_NAME, IS_VISIBLE
       FROM INFORMATION_SCHEMA.STATISTICS
       WHERE TABLE_SCHEMA = 'db1' AND TABLE_NAME = 't1';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| i_idx      | YES        |
| j_idx      | NO         |
| k_idx      | NO         |
+------------+------------+

不可視のインデックスを使用すると、インデックスが必要になった場合に元に戻す必要がある破壊的な変更を行わずに、クエリーのパフォーマンスに対するインデックスの削除の影響をテストできます。 大規模なテーブルの場合、インデックスを削除して再追加するとコストがかかる可能性がありますが、インデックスを非表示にして可視にすると、高速なインプレース操作になります。

インデックスが実際に不可視になった場合、またはオプティマイザによって使用された場合は、そのインデックスが存在しないことがテーブルのクエリーに与える影響に注意する方法がいくつかあります:

optimizer_switch システム変数の use_invisible_indexes フラグは、オプティマイザがクエリー実行計画の構成に不可視のインデックスを使用するかどうかを制御します。 フラグが off (デフォルト) の場合、オプティマイザは不可視のインデックス (このフラグの導入前と同じ動作) を無視します。 フラグが on の場合、不可視のインデックスは不可視のままですが、オプティマイザは実行計画の構成を考慮に入れます。

SET_VAR オプティマイザヒントを使用して一時的に optimizer_switch の値を更新すると、次のように、単一のクエリーの間のみ不可視インデックスを有効にできます:

mysql> EXPLAIN SELECT /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */
     >     i, j FROM t1 WHERE j >= 50\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: range
possible_keys: j_idx
          key: j_idx
      key_len: 5
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: Using index condition

mysql> EXPLAIN SELECT i, j FROM t1 WHERE j >= 50\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 33.33
        Extra: Using where

インデックスの可視性は、インデックスメンテナンスには影響しません。 たとえば、インデックスはテーブルの行に対する変更ごとに引き続き更新され、一意のインデックスによって、インデックスが可視か不可視かに関係なく、カラムへの重複の挿入が防止されます。

明示的な主キーを持たないテーブルは、NOT NULL カラムに UNIQUE インデックスがある場合でも、有効な暗黙的主キーを持つことができます。 この場合、最初のこのようなインデックスは明示的な主キーと同じ制約をテーブルの行に配置し、そのインデックスを不可視にすることはできません。 次のテーブル定義について考えてみます:

CREATE TABLE t2 (
  i INT NOT NULL,
  j INT NOT NULL,
  UNIQUE j_idx (j)
) ENGINE = InnoDB;

この定義には明示的な主キーは含まれていませんが、NOT NULL カラム j のインデックスは主キーと同じ制約を行に設定し、非表示にすることはできません:

mysql> ALTER TABLE t2 ALTER INDEX j_idx INVISIBLE;
ERROR 3522 (HY000): A primary key index cannot be invisible.

ここで、明示的な主キーがテーブルに追加されるとします:

ALTER TABLE t2 ADD PRIMARY KEY (i);

明示的な主キーは非表示にできません。 また、j の一意インデックスは暗黙的主キーとして機能しなくなり、その結果非表示になる可能性があります:

mysql> ALTER TABLE t2 ALTER INDEX j_idx INVISIBLE;
Query OK, 0 rows affected (0.03 sec)