8.9.4 Index Hints — MySQL 5.7

8.9.4 Подсказки индексов

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

Подсказки индексов применяются к SELECT и UPDATE операторам. Они также работают с DELETE операторами для нескольких таблиц, но не с DELETE операторами для одной таблицы, как показано позже в этом разделе.

Подсказки индексов указываются после имени таблицы. (Для общего синтаксиса указания таблиц в SELECT операторе см. раздел 13.2.9.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), с добавлением предположения, что сканирование таблицы является очень дорогостоящим. Другими словами, сканирование таблицы используется только в том случае, если нет способа использовать один из указанных индексов для поиска строк в таблице.

Для каждой подсказки требуются имена индексов, а не имена столбцов. Для ссылки на первичный ключ используйте имя 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)

В MySQL 5.0 область действия подсказки без предложения FOR применялась только к извлечению строк. Для того, чтобы заставить сервер использовать это старое поведение при отсутствии предложения FOR, активируйте системную переменную old при запуске сервера. Будьте внимательны при включении этой переменной в настройке репликации. При использовании журнала бинарных логов на основе операторов, различные режимы для источника и реплик могут привести к ошибкам репликации.

При обработке подсказок индексов они собираются в одном списке по типу (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)