Подсказки оптимизатору применяются на разных уровнях:

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

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

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

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

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

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

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

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

Но это неверно:

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

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

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

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

Указания для порядка соединения влияют на порядок, в котором оптимизатор соединяет таблицы.

Синтаксис указания JOIN_FIXED_ORDER:

hint_name([@query_block_name])

Синтаксис других указаний порядка соединения:

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

Синтаксис ссылается на следующие термины:

Пример:

SELECT
/*+ JOIN_PREFIX(t2, t5@subq2, t4@subq1)
    JOIN_ORDER(t4@subq1, t3)
    JOIN_SUFFIX(t1) */
COUNT(*) FROM t1 JOIN t2 JOIN t3
           WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
             AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);

Указания управляют поведением таблиц полусоединения, которые объединяются с внешним блоком запроса. Если подзапросы subq1 и subq2 преобразуются в полусоединения, таблицы t4@subq1 и t5@subq2 объединяются с внешним блоком запроса. В этом случае указание, заданное во внешнем блоке запроса, управляет поведением таблиц t4@subq1, t5@subq2.

Оптимизатор разрешает указания порядка соединения в соответствии со следующими принципами:

Указатели на уровне таблиц влияют на:

Эти типы указателей применяются к определённым таблицам или ко всем таблицам в блоке запроса.

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

hint_name([@query_block_name] [tbl_name [, tbl_name] ...])
hint_name([tbl_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 /*+ NO_MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt;

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

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

Если оптимизатор выбирает обработку t1 первой, он применяет объединение «Блок вложенного цикла» к t2, буферизируя строки из t1 перед началом чтения из t2. Если оптимизатор вместо этого выбирает обработку t2 первой, указатель не оказывает никакого влияния, потому что t2 является таблицей-отправителем.

Для указателей MERGE и NO_MERGE применяются следующие правила приоритета:

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

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

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

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

Если один подзапрос вложен в другой, и оба объединены в полусоединение внешнего запроса, любые указания стратегий полусоединений для внутреннего подзапроса игнорируются. Подсказки 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-to-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 применяется следующим образом:

Указание SET_VAR временно устанавливает значение системной переменной в сеансе (на время выполнения одного оператора). Примеры:

SELECT /*+ SET_VAR(sort_buffer_size = 16M) */ name FROM people ORDER BY name;
INSERT /*+ SET_VAR(foreign_key_checks=OFF) */ INTO t2 VALUES(2);
SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=off') */ 1;

Синтаксис указания SET_VAR:

SET_VAR(var_name = value)

var_name указывает имя системной переменной, имеющей значение в сеансе (хотя не все такие переменные можно назвать, как объяснено позже). value — это значение, которое нужно присвоить переменной; значение должно быть скалярным.

SET_VAR производит временное изменение переменной, как показано в этих операторах:

mysql> SELECT @@unique_checks;
+-----------------+
| @@unique_checks |
+-----------------+
|               1 |
+-----------------+
mysql> SELECT /*+ SET_VAR(unique_checks=OFF) */ @@unique_checks;
+-----------------+
| @@unique_checks |
+-----------------+
|               0 |
+-----------------+
mysql> SELECT @@unique_checks;
+-----------------+
| @@unique_checks |
+-----------------+
|               1 |
+-----------------+

С SET_VAR нет необходимости сохранять и восстанавливать значение переменной. Это позволяет заменить несколько операторов одним оператором. Рассмотрим эту последовательность операторов:

SET @saved_val = @@SESSION.var_name;
SET @@SESSION.var_name = value;
SELECT ...
SET @@SESSION.var_name = @saved_val;

Последовательность можно заменить этим одиночным оператором:

SELECT /*+ SET_VAR(var_name = value) ...

Операторы SET позволяют использовать любой из этих синтаксисов для именования переменных сеанса:

SET SESSION var_name = value;
SET @@SESSION.var_name = value;
SET @@.var_name = value;

Поскольку указание SET_VAR применяется только к переменным сеанса, область действия сеанса неявна, и SESSION, @@SESSION. и @@ не нужны и не разрешены. Включение явного синтаксиса индикатора сеанса приведет к игнорированию указания SET_VAR с предупреждением.

Не все переменные сеанса разрешены для использования с SET_VAR. В описаниях отдельных системных переменных указано, разрешена ли каждая переменная для указаний; см. Раздел 7.1.8, «Системные переменные сервера». Вы также можете проверить системную переменную во время выполнения, попытавшись использовать ее с SET_VAR. Если переменная не разрешена для указаний, появляется предупреждение:

mysql> SELECT /*+ SET_VAR(collation_server = 'utf8mb4') */ 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 4537
Message: Variable 'collation_server' cannot be set using SET_VAR hint.

Синтаксис SET_VAR позволяет установить только одну переменную, но можно использовать несколько указаний для установки нескольких переменных:

SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=off')
           SET_VAR(max_heap_table_size = 1G) */ 1;

Если несколько указаний с одним и тем же именем переменной появляются в одном операторе, применяется первое, а остальные игнорируются с предупреждением:

SELECT /*+ SET_VAR(max_heap_table_size = 1G)
           SET_VAR(max_heap_table_size = 3G) */ 1;

В этом случае второе указание игнорируется с предупреждением о конфликте.

Указание SET_VAR игнорируется с предупреждением, если нет системной переменной с указанным именем или значение переменной некорректно:

SELECT /*+ SET_VAR(max_size = 1G) */ 1;
SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=yes') */ 1;

Для первого оператора нет переменной max_size. Для второго оператора mrr_cost_based принимает значения on или off, поэтому попытка установить ее в yes некорректна. В каждом случае указание игнорируется с предупреждением.

Указание SET_VAR разрешено только на уровне оператора. Если используется в подзапросе, указание игнорируется с предупреждением.

Реплики игнорируют указания SET_VAR в реплицированных операторах, чтобы избежать потенциальных проблем безопасности.

Указание оптимизатора RESOURCE_GROUP используется для управления группами ресурсов (см. Раздел 7.1.16, «Группы ресурсов»). Это указание временно (на время выполнения оператора) назначает поток, выполняющий оператор, указанной группе ресурсов. Требуется привилегия RESOURCE_GROUP_ADMIN или RESOURCE_GROUP_USER.

Примеры:

SELECT /*+ RESOURCE_GROUP(USR_default) */ name FROM people ORDER BY name;
INSERT /*+ RESOURCE_GROUP(Batch) */ INTO t2 VALUES(2);

Синтаксис указания RESOURCE_GROUP:

RESOURCE_GROUP(group_name)

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

Указание RESOURCE_GROUP должно появляться после начального ключевого слова оператора (SELECT, INSERT, REPLACE, UPDATE или DELETE).

Альтернативой RESOURCE_GROUP является оператор SET RESOURCE GROUP, который постоянно назначает потоки группе ресурсов. См. Раздел 15.7.2.4, «Оператор SET RESOURCE GROUP».

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

Результат следующий:

Имена блоков запросов являются идентификаторами и следуют обычным правилам относительно допустимых имён и способа их цитирования (см. Раздел 11.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") */ ...) ...