8.9.3 Optimizer Hints — MySQL 5.7

8.9.3 Указания оптимизатору

Один из способов управления стратегиями оптимизатора — установить системную переменную optimizer_switch (см. Раздел 8.9.2, «Переключаемые оптимизации»). Изменения этой переменной влияют на выполнение всех последующих запросов; чтобы по-разному повлиять на один запрос по сравнению с другим, необходимо изменить optimizer_switch перед каждым запросом.

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

Примеры:

SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
  FROM t3 WHERE f1 > 30 AND f1 < 33;
SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ NO_ICP(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */ * FROM t1 ...;
EXPLAIN SELECT /*+ NO_ICP(t1) */ * FROM t1 WHERE ...;

Примечание

Клиент mysql по умолчанию удаляет комментарии из SQL-операторов, отправляемых на сервер (включая указания оптимизатору), до MySQL 5.7.7, когда он был изменён на передачу указаний оптимизатору на сервер. Чтобы убедиться, что указания оптимизатору не удаляются, если вы используете более старую версию клиента mysql с версией сервера, которая понимает указания оптимизатору, вызовите mysql с параметром --comments.

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

Обзор указаний оптимизатору

Указания оптимизатору применяются на различных уровнях области применения:

Глобальные: указание влияет на весь оператор
Блок запроса: указание влияет на определённый блок запроса в операторе
Уровень таблицы: указание влияет на определённую таблицу в блоке запроса
Уровень индекса: указание влияет на определённый индекс в таблице

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

Таблица 8.2 Доступные указания оптимизатору

Таблица 8.2 Доступные указания оптимизатору
Имя указания	Описание	Применимые области
`BKA`, `NO_BKA`	Влияет на обработку объединения с пакетным доступом по ключу	Блок запроса, таблица
`BNL`, `NO_BNL`	Влияет на обработку объединения с блочно-вложенным циклом	Блок запроса, таблица
`MAX_EXECUTION_TIME`	Ограничивает время выполнения оператора	Глобально
`MRR`, `NO_MRR`	Влияет на оптимизацию многодиапазонного чтения	Таблица, индекс
`NO_ICP`	Влияет на оптимизацию сдвига условия по индексу	Таблица, индекс
`NO_RANGE_OPTIMIZATION`	Влияет на оптимизацию диапазонов	Таблица, индекс
`QB_NAME`	Присваивает имя блоку запроса	Блок запроса
`SEMIJOIN`, `NO_SEMIJOIN`	стратегии полуобъединения	Блок запроса
`SUBQUERY`	Влияет на материализацию, `IN`-в-`EXISTS` стратегии подзапросов	Блок запроса

Отключение оптимизации предотвращает использование оптимизатором данной стратегии. Включение оптимизации означает, что оптимизатор свободен использовать данную стратегию, если она применима к выполнению оператора, а не то, что оптимизатор обязательно её использует.

Синтаксис подсказок оптимизатора

MySQL поддерживает комментарии в SQL-запросах, как описано в разделе 9.6, «Комментарии». Подсказки оптимизатора должны быть указаны внутри /*+ ... */ комментариев. То есть, подсказки оптимизатора используют вариант /* ... */ синтаксиса комментариев C-стиля, с символом + после последовательности /* открытия комментария. Примеры:

/*+ BKA(t1) */
/*+ BNL(t1, t2) */
/*+ NO_RANGE_OPTIMIZATION(t4 PRIMARY) */
/*+ QB_NAME(qb2) */

Разрешается пробел после символа +.

Парсер распознает комментарии с подсказками оптимизатора после начального ключевого слова в SELECT, UPDATE, INSERT, REPLACE и DELETE запросах. Подсказки разрешены в этих контекстах:

В начале запросов и запросов изменения данных:

SELECT /*+ ... */ ...
INSERT /*+ ... */ ...
REPLACE /*+ ... */ ...
UPDATE /*+ ... */ ...
DELETE /*+ ... */ ...

В начале блоков запросов:

(SELECT /*+ ... */ ... )
(SELECT ... ) UNION (SELECT /*+ ... */ ... )
(SELECT /*+ ... */ ... ) UNION (SELECT /*+ ... */ ... )
UPDATE ... WHERE x IN (SELECT /*+ ... */ ...)
INSERT ... SELECT /*+ ... */ ...

В запросах, которые можно оптимизировать, с префиксом EXPLAIN. Например:
```
EXPLAIN SELECT /*+ ... */ ...
EXPLAIN UPDATE ... WHERE x IN (SELECT /*+ ... */ ...)
```
Это означает, что вы можете использовать EXPLAIN, чтобы увидеть, как подсказки оптимизатора влияют на планы выполнения. Используйте SHOW WARNINGS сразу после EXPLAIN, чтобы увидеть, как используются подсказки. Расширенный EXPLAIN вывод, отображаемый последующим SHOW WARNINGS, указывает, какие подсказки использовались. Проигнорированные подсказки не отображаются.

Комментарий с подсказкой может содержать несколько подсказок, но блок запроса не может содержать несколько комментариев с подсказками. Это допустимо:

SELECT /*+ BNL(t1) BKA(t2) */ ...

Но это недопустимо:

SELECT /*+ BNL(t1) */ /* BKA(t2) */ ...

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

Дублирующиеся подсказки: Для подсказки, такой как /*+ MRR(idx1) MRR(idx1) */, MySQL использует первую подсказку и выводит предупреждение о дублирующейся подсказке.
Конфликтующие подсказки: Для подсказки, такой как /*+ MRR(idx1) NO_MRR(idx1) */, MySQL использует первую подсказку и выводит предупреждение о второй конфликтующей подсказке.

Имена блоков запросов являются идентификаторами и следуют обычным правилам относительно допустимых имён и того, как их нужно указывать (см. раздел 9.2, «Имена объектов схемы»).

Имена подсказок, имена блоков запросов и имена стратегий не зависят от регистра. Ссылки на имена таблиц и индексов следуют обычным правилам чувствительности к регистру идентификаторов (см. раздел 9.2.3, «Чувствительность к регистру идентификаторов»).

Подсказки оптимизатора на уровне таблиц

Подсказки оптимизатора на уровне таблиц влияют на использование алгоритмов обработки объединений Block Nested-Loop (BNL) и Batched Key Access (BKA) (см. раздел 8.2.1.11, «Объединения Block Nested-Loop и Batched Key Access»). Эти типы подсказок относятся к конкретным таблицам или ко всем таблицам в блоке запроса.

Синтаксис подсказок на уровне таблиц:

hint_name([@query_block_name] [tbl_name [, tbl_name] ...])
hint_name([tbl_name@query_block_name [, tbl_name@query_block_name] ...])

Синтаксис ссылается на эти термины:

hint_name: Разрешены следующие имена подсказок:
- BKA, NO_BKA: Включить или выключить BKA для указанных таблиц.
- BNL, NO_BNL: Включить или выключить BNL для указанных таблиц.
Примечание

Чтобы использовать подсказку BNL или BKA для включения буферизации объединений для любой внутренней таблицы внешнего объединения, буферизация объединений должна быть включена для всех внутренних таблиц внешнего объединения.
tbl_name: Имя таблицы, используемой в запросе. Подсказка применяется ко всем таблицам, которые она называет. Если подсказка не называет таблицы, она применяется ко всем таблицам блока запроса, в котором она находится.

Если у таблицы есть псевдоним, подсказки должны ссылаться на псевдоним, а не на имя таблицы.

Имена таблиц в подсказках не могут быть квалифицированы именами схем.
query_block_name: Блок запроса, к которому применяется подсказка. Если подсказка не содержит ведущего @query_block_name, она применяется к блоку запроса, в котором она находится. Для tbl_name@query_block_name синтаксиса подсказка применяется к указанной таблице в указанном блоке запроса. Чтобы назначить имя блоку запроса, см. Подсказки оптимизатора для именования блоков запросов.

Примеры:

SELECT /*+ NO_BKA(t1, t2) */ t1.* FROM t1 INNER JOIN t2 INNER JOIN t3;
SELECT /*+ NO_BNL() BKA(t1) */ t1.* FROM t1 INNER JOIN t2 INNER JOIN t3;

Подсказка на уровне таблицы применяется к таблицам, которые получают записи из предыдущих таблиц, а не к таблицам-отправителям. Рассмотрим этот запрос:

SELECT /*+ BNL(t2) */ FROM t1, t2;

Если оптимизатор выбирает обработку t1 в первую очередь, он применяет объединение Block Nested-Loop к t2, буферизуя строки из t1 перед началом чтения из t2. Если оптимизатор выбирает обработку t2 в первую очередь, подсказка не влияет, потому что t2 — это таблица-отправитель.

Подсказки оптимизатора на уровне индексов

Подсказки оптимизатора на уровне индексов влияют на стратегии обработки индексов, которые использует оптимизатор для конкретных таблиц или индексов. Эти типы подсказок влияют на использование Index Condition Pushdown (ICP), Multi-Range Read (MRR) и оптимизаций диапазонов (см. раздел 8.2.1, «Оптимизация запросов SELECT»).

Синтаксис подсказок на уровне индексов:

hint_name([@query_block_name] tbl_name [index_name [, index_name] ...])
hint_name(tbl_name@query_block_name [index_name [, index_name] ...])

Синтаксис ссылается на эти термины:

hint_name: Разрешены следующие имена подсказок:
- MRR, NO_MRR: Включить или выключить MRR для указанной таблицы или индексов. Подсказки MRR применяются только к таблицам InnoDB и MyISAM.
- NO_ICP: Выключить ICP для указанной таблицы или индексов. По умолчанию ICP является кандидатом для оптимизации, поэтому для его включения нет подсказки.
- NO_RANGE_OPTIMIZATION: Выключить доступ к диапазонам индексов для указанной таблицы или индексов. Эта подсказка также выключает Index Merge и Loose Index Scan для таблицы или индексов. По умолчанию доступ к диапазонам является кандидатом для оптимизации, поэтому для его включения нет подсказки.
  
  Эта подсказка может быть полезной, когда количество диапазонов может быть высоким, и оптимизация диапазонов потребовала бы много ресурсов.
tbl_name: Таблица, к которой применяется подсказка.
index_name: Имя индекса в указанной таблице. Подсказка применяется ко всем индексам, которые она называет. Если подсказка не называет индексы, она применяется ко всем индексам в таблице.

Чтобы сослаться на первичный ключ, используйте имя PRIMARY. Чтобы увидеть имена индексов для таблицы, используйте SHOW INDEX.
query_block_name: Блок запроса, к которому применяется подсказка. Если подсказка не содержит ведущего @query_block_name, она применяется к блоку запроса, в котором она находится. Для tbl_name@query_block_name синтаксиса подсказка применяется к указанной таблице в указанном блоке запроса. Чтобы назначить имя блоку запроса, см. Подсказки оптимизатора для именования блоков запросов.

Примеры:

SELECT /*+ MRR(t1) */ * FROM t1 WHERE f2 <= 3 AND 3 <= f3;
SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1
  FROM t3 WHERE f1 > 30 AND f1 < 33;
INSERT INTO t3(f1, f2, f3)
  (SELECT /*+ NO_ICP(t2) */ t2.f1, t2.f2, t2.f3 FROM t1,t2
   WHERE t1.f1=t2.f1 AND t2.f2 BETWEEN t1.f1
   AND t1.f2 AND t2.f2 + 1 >= t1.f1 + 1);

Указания оптимизатора для подзапросов

Указания для подзапросов влияют на использование преобразований полусоединения и выбор стратегий полусоединения, а также, когда полусоединения не используются, на использование материализации подзапроса или преобразований из IN в EXISTS. Дополнительную информацию об этих оптимизациях см. в разделе 8.2.2, «Оптимизация подзапросов, производных таблиц и ссылок на представления».

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

hint_name([@query_block_name] [strategy [, strategy] ...])

В синтаксисе используются следующие термины:

hint_name: Допускаются следующие имена указаний:
- SEMIJOIN, NO_SEMIJOIN: Включение или отключение указанных стратегий полусоединения.
strategy: Стратегия полусоединения, которая должна быть включена или выключена. Допускаются следующие имена стратегий: DUPSWEEDOUT, FIRSTMATCH, LOOSESCAN, MATERIALIZATION.

Для указаний SEMIJOIN, если стратегии не указаны, полусоединение используется, если возможно, на основе стратегий, включенных в соответствии с системной переменной optimizer_switch. Если стратегии указаны, но неприменимы для оператора, используется DUPSWEEDOUT.

Для указаний NO_SEMIJOIN, если стратегии не указаны, полусоединение не используется. Если указаны стратегии, исключающие все применимые стратегии для оператора, используется DUPSWEEDOUT.

Если один подзапрос вложен в другой и оба объединены в полусоединение внешнего запроса, любые указания стратегий полусоединения для внутреннего подзапроса игнорируются. Указания SEMIJOIN и NO_SEMIJOIN по-прежнему могут использоваться для включения или отключения преобразований полусоединения для таких вложенных подзапросов.

Если DUPSWEEDOUT отключено, в некоторых случаях оптимизатор может сгенерировать план запроса, далекий от оптимального. Это происходит из-за эвристического удаления во время жадного поиска, чего можно избежать, установив optimizer_prune_level=0.

Примеры:

SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) */ * FROM t2
  WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);
SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2
  WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);

Синтаксис указаний, влияющих на использование материализации подзапроса или преобразований из IN в EXISTS:

SUBQUERY([@query_block_name] strategy)

Имя указания всегда SUBQUERY.

Для указаний SUBQUERY допускаются следующие значения strategy: INTOEXISTS, MATERIALIZATION.

Примеры:

SELECT id, a IN (SELECT /*+ SUBQUERY(MATERIALIZATION) */ a FROM t1) FROM t2;
SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ SUBQUERY(INTOEXISTS) */ a FROM t1);

Для указаний полусоединения и SUBQUERY ведущий @query_block_name указывает блок запроса, к которому относится указание. Если указание не содержит ведущего @query_block_name, указание относится к блоку запроса, в котором оно находится. Чтобы назначить имя блоку запроса, см. Указания оптимизатора для именования блоков запросов.

Если комментарий указания содержит несколько указаний для подзапросов, используется первое. Если есть другие последующие указания такого типа, они порождают предупреждение. Последующие указания других типов игнорируются без предупреждения.

Указания оптимизатора для времени выполнения операторов

Указание MAX_EXECUTION_TIME допускается только для операторов SELECT. Оно устанавливает ограничение N (значение тайм-аута в миллисекундах) на время выполнения оператора, прежде чем сервер завершит его:

MAX_EXECUTION_TIME(N)

Пример с таймаутом в 1 секунду (1000 миллисекунд):

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t1 INNER JOIN t2 WHERE ...

Указание MAX_EXECUTION_TIME(N) устанавливает тайм-аут выполнения оператора в N миллисекундах. Если этот параметр отсутствует или N равно 0, применяется тайм-аут оператора, установленный системной переменной max_execution_time.

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

Для операторов с несколькими ключевыми словами SELECT, такими как объединения или операторы с подзапросами, MAX_EXECUTION_TIME относится ко всему оператору и должно быть указано после первого оператора SELECT.
Оно применяется к операторам SELECT только для чтения. Операторы, которые не являются только для чтения, — это те, которые вызывают хранимую функцию, изменяющую данные как побочный эффект.
Оно не применяется к операторам SELECT в хранимых программах и игнорируется.

Указания оптимизатора для именования блоков запросов

Указания оптимизатора на уровне таблиц, индексов и подзапросов позволяют задавать имена конкретным блокам запросов в качестве части синтаксиса аргументов. Для создания этих имен используйте указание QB_NAME, которое назначает имя блоку запроса, в котором оно расположено:

QB_NAME(name)

Указания QB_NAME позволяют четко указать, к каким блокам запросов относятся другие указания. Они также позволяют указать все указания, не относящиеся к именованию блоков запросов, в одном комментарии указания для лучшего понимания сложных операторов. Рассмотрим следующий оператор:

SELECT ...
  FROM (SELECT ...
  FROM (SELECT ... FROM ...)) ...

Указания QB_NAME назначают имена блокам запросов в операторе:

SELECT /*+ QB_NAME(qb1) */ ...
  FROM (SELECT /*+ QB_NAME(qb2) */ ...
  FROM (SELECT /*+ QB_NAME(qb3) */ ... FROM ...)) ...

Затем другие указания могут использовать эти имена для ссылки на соответствующие блоки запросов:

SELECT /*+ QB_NAME(qb1) MRR(@qb1 t1) BKA(@qb2) NO_MRR(@qb3t1 idx1, id2) */ ...
  FROM (SELECT /*+ QB_NAME(qb2) */ ...
  FROM (SELECT /*+ QB_NAME(qb3) */ ... FROM ...)) ...

Результирующий эффект следующий:

MRR(@qb1 t1) применяется к таблице t1 в блоке запроса qb1.
BKA(@qb2) применяется к блоку запроса qb2.
NO_MRR(@qb3 t1 idx1, id2) применяется к индексам idx1 и idx2 в таблице t1 в блоке запроса qb3.

Имена блоков запросов являются идентификаторами и следуют обычным правилам относительно допустимых имен и способа их цитирования (см. раздел 9.2, «Имена объектов схемы»). Например, имя блока запроса, содержащее пробелы, должно быть заключено в кавычки, что можно сделать с помощью обратных кавычек:

SELECT /*+ BKA(@`my hint name`) */ ...
  FROM (SELECT /*+ QB_NAME(`my hint name`) */ ...) ...

Если включен режим SQL ANSI_QUOTES, также можно заключить имена блоков запросов в двойные кавычки:

SELECT /*+ BKA(@"my hint name") */ ...
  FROM (SELECT /*+ QB_NAME("my hint name") */ ...) ...