Игнорируемые индексы

Игнорируемые индексы — это индексы, которые видны и поддерживаются, но не используются оптимизатором. В MySQL 8 есть аналогичная функция, которую они называют «невидимые индексы».

Синтаксис

По умолчанию индекс не игнорируется. Можно отметить существующий индекс как игнорируемый (или неигнорируемый) с помощью оператора ALTER TABLE:

ALTER TABLE table_name ALTER {KEY|INDEX} [IF EXISTS] key_name [NOT] IGNORED;

Также можно указать атрибут IGNORED при создании индекса с помощью оператора CREATE TABLE или CREATE INDEX:

CREATE TABLE table_name (
  ...
  INDEX index_name ( ...) [NOT] IGNORED
  ...

CREATE INDEX index_name (...) [NOT] IGNORED ON tbl_name (...);

Первичный ключ таблицы не может быть проигнорирован. Это относится как к явно определенному первичному ключу, так и к неявным первичным ключам — если явного первичного ключа не определено, но таблица имеет уникальный ключ, содержащий только столбцы NOT NULL, то первый такой ключ становится неявным первичным ключом.

Обработка игнорируемых индексов

Оптимизатор будет рассматривать игнорируемые индексы так, как будто их не существует. Они не будут использоваться в планах запросов или как источник статистической информации. Также попытка использовать игнорируемый индекс в подсказке USE INDEX, FORCE INDEX, или IGNORE INDEX приведет к ошибке — так же, как и попытка использовать имя несуществующего индекса.

Информация о том, игнорируются индексы или нет, может быть отображена в столбце IGNORED в таблице Information Schema STATISTICS или в операторе SHOW INDEX.

Предполагаемое использование

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

1. Отметить индекс как игнорируемый
2. Проверить, все ли продолжает работать
3. Если нет, отметить индекс как неигнорируемый
4. Если все продолжает работать, можно безопасно удалить индекс

Примеры

CREATE TABLE t1 (id INT PRIMARY KEY, b INT, KEY k1(b) IGNORED);

CREATE OR REPLACE TABLE t1 (id INT PRIMARY KEY, b INT, KEY k1(b));
ALTER TABLE t1 ALTER INDEX k1 IGNORED;

CREATE OR REPLACE TABLE t1 (id INT PRIMARY KEY, b INT);
CREATE INDEX k1 ON t1(b) IGNORED;

SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_NAME = 't1'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
 TABLE_SCHEMA: test
   TABLE_NAME: t1
   NON_UNIQUE: 0
 INDEX_SCHEMA: test
   INDEX_NAME: PRIMARY
 SEQ_IN_INDEX: 1
  COLUMN_NAME: id
    COLLATION: A
  CARDINALITY: 0
     SUB_PART: NULL
       PACKED: NULL
     NULLABLE: 
   INDEX_TYPE: BTREE
      COMMENT: 
INDEX_COMMENT: 
      IGNORED: NO
*************************** 2. row ***************************
TABLE_CATALOG: def
 TABLE_SCHEMA: test
   TABLE_NAME: t1
   NON_UNIQUE: 1
 INDEX_SCHEMA: test
   INDEX_NAME: k1
 SEQ_IN_INDEX: 1
  COLUMN_NAME: b
    COLLATION: A
  CARDINALITY: 0
     SUB_PART: NULL
       PACKED: NULL
     NULLABLE: YES
   INDEX_TYPE: BTREE
      COMMENT: 
INDEX_COMMENT: 
      IGNORED: YES

SHOW INDEXES FROM t1\G
*************************** 1. row ***************************
        Table: t1
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Ignored: NO
*************************** 2. row ***************************
        Table: t1
   Non_unique: 1
     Key_name: k1
 Seq_in_index: 1
  Column_name: b
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment: 
Index_comment: 
      Ignored: YES

Оптимизатор не использует индекс, когда он игнорируется, в то время как если индекс не игнорируется (по умолчанию), оптимизатор учтет его в плане оптимизатора, как показано в выводе EXPLAIN.

CREATE OR REPLACE TABLE t1 (id INT PRIMARY KEY, b INT, KEY k1(b) IGNORED);

EXPLAIN SELECT * FROM t1 ORDER BY b;
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+
|    1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL | 1    | Using filesort |
+------+-------------+-------+------+---------------+------+---------+------+------+----------------+

ALTER TABLE t1 ALTER INDEX k1 NOT IGNORED;

EXPLAIN SELECT * FROM t1 ORDER BY b;
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id   | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | t1    | index | NULL          | k1   | 5       | NULL | 1    | Using index |
+------+-------------+-------+-------+---------------+------+---------+------+------+-------------+