MySQL поддерживает невидимые индексы; то есть индексы, которые не используются оптимизатором. Эта функция применяется к индексам, отличным от первичных ключей (явных или неявных).

Индексы по умолчанию видимые. Чтобы явно контролировать видимость нового индекса, используйте ключевые слова VISIBLE или INVISIBLE в определении индекса для CREATE TABLE, CREATE INDEX или ALTER TABLE:

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;

Чтобы изменить видимость существующего индекса, используйте ключевые слова VISIBLE или INVISIBLE с операцией ALTER TABLE ... ALTER INDEX:

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

Информация о том, является ли индекс видимым или невидимым, доступна из таблицы Справочной схемы 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         |
+------------+------------+

Невидимые индексы позволяют проверить влияние удаления индекса на производительность запросов без внесения разрушительных изменений, которые необходимо будет отменить, если индекс окажется необходимым. Удаление и повторное добавление индекса может быть дорогостоящим для большой таблицы, тогда как приведение его к невидимому и видимому состоянию — это быстрые операции на месте.

Если индекс, переведённый в невидимое состояние, на самом деле необходим или используется оптимизатором, существует несколько способов заметить влияние его отсутствия на запросы для таблицы:

Флаг use_invisible_indexes переменной системы optimizer_switch управляет тем, использует ли оптимизатор невидимые индексы для построения плана выполнения запроса. Если флаг 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

Видимость индекса не влияет на обслуживание индекса. Например, индекс продолжает обновляться при изменениях строк таблицы, а уникальный индекс предотвращает вставку дубликатов в столбец независимо от того, является ли индекс видимым или невидимым.

Таблица без явного первичного ключа может все еще иметь эффективный неявный первичный ключ, если она имеет какие-либо UNIQUE индексы на NOT NULL столбцах. В этом случае первый такой индекс накладывает такое же ограничение на строки таблицы, как явный первичный ключ, и этот индекс нельзя сделать невидимым. Рассмотрим следующее определение таблицы:

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)