10.9.4 Index Hints — MySQL 9.2

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 9.2 поддерживает подсказки оптимизатора на уровне индексов 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)