10.9.4 Index Hints — MySQL 8.4

10.9.4 Указания по индексам

Указания по индексам предоставляют оптимизатору информацию о том, как выбрать индексы во время обработки запроса. Указания по индексам, описанные здесь, отличаются от указаний оптимизатора, описанных в разделе 10.9.3, «Указания оптимизатору». Указания по индексам и указания оптимизатору можно использовать по отдельности или вместе.

Указания по индексам применяются к SELECT и UPDATE операторам. Они также работают с многотабличными DELETE операторами, но не работают с однотабличными DELETE, как показано позже в этом разделе.

Указания по индексам указываются после имени таблицы. (Для общего синтаксиса указания таблиц в SELECT операторе см. раздел 15.2.13.2, «Оператор JOIN».) Синтаксис ссылки на отдельную таблицу, включая указания по индексу, выглядит следующим образом:

tbl_name [[AS] alias] [index_hint_list]

index_hint_list:
    index_hint [index_hint] ...

index_hint:
    USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | {IGNORE|FORCE} {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)

index_list:
    index_name [, index_name] ...

Указание USE INDEX (index_list) сообщает MySQL использовать только один из указанных индексов для поиска строк в таблице. Альтернативный синтаксис IGNORE INDEX (index_list) сообщает MySQL не использовать некоторые конкретные индексы. Эти указания полезны, если EXPLAIN показывает, что MySQL использует неправильный индекс из списка возможных индексов.

Указание FORCE INDEX действует как USE INDEX (index_list), с добавлением предположения о том, что сканирование таблицы является очень дорогостоящим. Другими словами, сканирование таблицы используется только в том случае, если нет способа использовать один из указанных индексов для поиска строк в таблице.

Примечание

MySQL 8.4 поддерживает указания оптимизатора на уровне индексов JOIN_INDEX, GROUP_INDEX, ORDER_INDEX и INDEX, которые эквивалентны и предназначены для замены указаний по индексу FORCE INDEX, а также указания оптимизатора NO_JOIN_INDEX, NO_GROUP_INDEX, NO_ORDER_INDEX и NO_INDEX, которые эквивалентны и предназначены для замены указаний по индексу IGNORE INDEX. Таким образом, ожидается, что USE INDEX, FORCE INDEX и IGNORE INDEX будут устаревшими в будущей версии MySQL, а впоследствии будут полностью удалены.

Эти указания оптимизатора на уровне индексов поддерживаются как с однотабличными, так и с многотабличными DELETE операторами.

Дополнительную информацию можно найти в разделе Указания оптимизатора на уровне индексов.

Каждое указание требует имен индексов, а не имен столбцов. Для ссылки на первичный ключ используйте имя PRIMARY. Чтобы увидеть имена индексов для таблицы, используйте оператор SHOW INDEX или таблицу схемы информации STATISTICS.

Значение index_name не должно быть полным именем индекса. Это может быть недвусмысленный префикс имени индекса. Если префикс неоднозначен, возникает ошибка.

Примеры:

SELECT * FROM table1 USE INDEX (col1_index,col2_index)
  WHERE col1=1 AND col2=2 AND col3=3;

SELECT * FROM table1 IGNORE INDEX (col3_index)
  WHERE col1=1 AND col2=2 AND col3=3;

Синтаксис указаний по индексу имеет следующие характеристики:

Синтаксически допустимо опустить index_list для USE INDEX, что означает “не использовать индексы.” Пропуск index_list для FORCE INDEX или IGNORE INDEX является синтаксической ошибкой.
Вы можете указать область действия указания по индексу, добавив предложение FOR к указанию. Это предоставляет более точный контроль над выбором планом выполнения оптимизатора для различных фаз обработки запроса. Чтобы повлиять только на индексы, используемые при принятии MySQL решений о том, как найти строки в таблице и как обработать объединения, используйте FOR JOIN. Для влияния на использование индексов для сортировки или группировки строк используйте FOR ORDER BY или FOR GROUP BY.
Вы можете указать несколько указаний по индексу:
```
SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX FOR ORDER BY (i2) ORDER BY a;
```
Не является ошибкой указывать один и тот же индекс в нескольких указаниях (даже в рамках одного указания):
```
SELECT * FROM t1 USE INDEX (i1) USE INDEX (i1,i1);
```
Однако, объединять USE INDEX и FORCE INDEX для одной и той же таблицы является ошибкой:
```
SELECT * FROM t1 USE INDEX FOR JOIN (i1) FORCE INDEX FOR JOIN (i2);
```

Если указание по индексу не содержит предложения FOR, область действия указания распространяется на все части оператора. Например, это указание:

IGNORE INDEX (i1)

эквивалентно следующему сочетанию указаний:

IGNORE INDEX FOR JOIN (i1)
IGNORE INDEX FOR ORDER BY (i1)
IGNORE INDEX FOR GROUP BY (i1)

При обработке указаний по индексу они собираются в один список по типу (USE, FORCE, IGNORE) и по области действия (FOR JOIN, FOR ORDER BY, FOR GROUP BY). Например:

SELECT * FROM t1
  USE INDEX () IGNORE INDEX (i2) USE INDEX (i1) USE INDEX (i2);

эквивалентно:

SELECT * FROM t1
   USE INDEX (i1,i2) IGNORE INDEX (i2);

Затем указания по индексу применяются для каждой области действия в следующем порядке:

{USE|FORCE} INDEX применяется, если присутствует. (В противном случае используется определённый оптимизатором набор индексов.)
IGNORE INDEX применяется к результату предыдущего шага. Например, следующие два запроса эквивалентны:
```
SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX (i2) USE INDEX (i2);

SELECT * FROM t1 USE INDEX (i1);
```

Для поиска FULLTEXT указания по индексу работают следующим образом:

Для поиска в режиме естественного языка указания по индексу игнорируются. Например, IGNORE INDEX(i1) игнорируется без предупреждения, и индекс по-прежнему используется.
Для поиска в булевом режиме указания по индексу с FOR ORDER BY или FOR GROUP BY игнорируются. Указания по индексу с FOR JOIN или без модификатора FOR учитываются. В отличие от того, как указания применяются к поиску без FULLTEXT, указание используется для всех фаз выполнения запроса (поиск строк и извлечение, группировка и упорядочивание). Это верно, даже если указание дано для индекса без FULLTEXT.

Например, следующие два запроса эквивалентны:
```
SELECT * FROM t
  USE INDEX (index1)
  IGNORE INDEX FOR ORDER BY (index1)
  IGNORE INDEX FOR GROUP BY (index1)
  WHERE ... IN BOOLEAN MODE ... ;

SELECT * FROM t
  USE INDEX (index1)
  WHERE ... IN BOOLEAN MODE ... ;
```

Указания по индексу работают с DELETE операторами, но только если вы используете синтаксис многотабличного DELETE, как показано здесь:

mysql> EXPLAIN DELETE FROM t1 USE INDEX(col2)
    -> WHERE col1 BETWEEN 1 AND 100 AND COL2 BETWEEN 1 AND 100\G
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'use
index(col2) where col1 between 1 and 100 and col2 between 1 and 100' at line 1

mysql> EXPLAIN DELETE t1.* FROM t1 USE INDEX(col2)
    -> WHERE col1 BETWEEN 1 AND 100 AND COL2 BETWEEN 1 AND 100\G
*************************** 1. row ***************************
           id: 1
  select_type: DELETE
        table: t1
   partitions: NULL
         type: range
possible_keys: col2
          key: col2
      key_len: 5
          ref: NULL
         rows: 72
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)