10.9.2 Switchable Optimizations — MySQL 8.4

10.9.2 Переключаемые оптимизации

Переменная системы optimizer_switch позволяет управлять поведением оптимизатора. Ее значение представляет собой набор флагов, каждый из которых имеет значение on или off, чтобы указать, включено или выключено соответствующее поведение оптимизатора. Эта переменная имеет глобальные и сеансовые значения и может быть изменена во время выполнения. Глобальное значение по умолчанию может быть установлено при запуске сервера.

Чтобы увидеть текущий набор флагов оптимизатора, выберите значение переменной:

mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
                    index_merge_sort_union=on,index_merge_intersection=on,
                    engine_condition_pushdown=on,index_condition_pushdown=on,
                    mrr=on,mrr_cost_based=on,block_nested_loop=on,
                    batched_key_access=off,materialization=on,semijoin=on,
                    loosescan=on,firstmatch=on,duplicateweedout=on,
                    subquery_materialization_cost_based=on,
                    use_index_extensions=on,condition_fanout_filter=on,
                    derived_merge=on,use_invisible_indexes=off,skip_scan=on,
                    hash_join=on,subquery_to_derived=off,
                    prefer_ordering_index=on,hypergraph_optimizer=off,
                    derived_condition_pushdown=on,hash_set_operations=on
1 row in set (0.00 sec)

Чтобы изменить значение optimizer_switch, присвойте значение, состоящее из одного или нескольких команд, разделенных запятыми:

SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';

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

Синтаксис команды	Значение
`default`	Сбросить все оптимизации до значений по умолчанию
`opt_name=default`	Установить указанную оптимизацию на значение по умолчанию
`opt_name=off`	Отключить указанную оптимизацию
`opt_name=on`	Включить указанную оптимизацию

Порядок команд в значении не имеет значения, хотя команда default выполняется первой, если присутствует. Установка флага opt_name в значение default устанавливает его в значение по умолчанию, которое является либо on, либо off. Указание любого заданного флага opt_name более одного раза в значении запрещено и приводит к ошибке. Любые ошибки в значении приводят к тому, что назначение завершается ошибкой, оставляя значение optimizer_switch неизменным.

В следующем списке описаны допустимые имена флагов opt_name, сгруппированные по стратегии оптимизации:

Флаги пакетного доступа к ключам
- batched_key_access (по умолчанию off)
  
  Управляет использованием алгоритма соединения BKA.
Для того, чтобы batched_key_access оказал какое-либо воздействие при установке в значение on, флаг mrr также должен быть on. В настоящее время оценка стоимости для MRR слишком пессимистична. Следовательно, также необходимо, чтобы mrr_cost_based был off для использования BKA.

Для получения дополнительной информации см. Раздел 10.2.1.12, «Соединения Block Nested-Loop и пакетного доступа к ключам».
Флаги Block Nested-Loop
- block_nested_loop (по умолчанию on)
  
  Управляет использованием хэш-соединений, как и подсказки оптимизатора BNL и NO_BNL.
Для получения дополнительной информации см. Раздел 10.2.1.12, «Соединения Block Nested-Loop и пакетного доступа к ключам».
Флаги фильтрации условий
- condition_fanout_filter (по умолчанию on)
  
  Управляет использованием фильтрации условий.
Для получения дополнительной информации см. Раздел 10.2.1.13, «Фильтрация условий».
Флаги понижения вычисляемых условий
- derived_condition_pushdown (по умолчанию on)
  
  Управляет понижением вычисляемых условий.
Для получения дополнительной информации см. Раздел 10.2.2.5, «Оптимизация понижения вычисляемых условий»
Флаги слияния производных таблиц
- derived_merge (по умолчанию on)
  
  Управляет слиянием производных таблиц и представлений во внешний блок запроса.
Флаг derived_merge управляет тем, пытается ли оптимизатор объединять производные таблицы, ссылки на представления и общие табличные выражения во внешний блок запроса, предполагая, что ни одно другое правило не препятствует слиянию; например, директива ALGORITHM для представления имеет приоритет над настройкой derived_merge. По умолчанию флаг имеет значение on для включения слияния.

Для получения дополнительной информации см. Раздел 10.2.2.4, «Оптимизация производных таблиц, ссылок на представления и общих табличных выражений с помощью слияния или материализации».
Флаги понижения условий ядра
- engine_condition_pushdown (по умолчанию on)
  
  Управляет понижением условий ядра.
Для получения дополнительной информации см. Раздел 10.2.1.5, «Оптимизация понижения условий ядра».
Флаги хэш-соединений
- hash_join (по умолчанию on)
  
  Не оказывает никакого влияния в MySQL 8.4. Используйте вместо этого флаг block_nested_loop.
Для получения дополнительной информации см. Раздел 10.2.1.4, «Оптимизация хэш-соединений».
Флаги понижения условий индекса
- index_condition_pushdown (по умолчанию on)
  
  Управляет понижением условий индекса.
Для получения дополнительной информации см. Раздел 10.2.1.6, «Оптимизация понижения условий индекса».
Флаги расширений индексов
- use_index_extensions (по умолчанию on)
  
  Управляет использованием расширений индексов.
Для получения дополнительной информации см. Раздел 10.3.10, «Использование расширений индексов».
Флаги слияния индексов
- index_merge (по умолчанию on)
  
  Управляет всеми оптимизациями слияния индексов.
- index_merge_intersection (по умолчанию on)
  
  Управляет оптимизацией доступа слияния индексов по пересечению.
- index_merge_sort_union (по умолчанию on)
  
  Управляет оптимизацией доступа слияния индексов сортировки-объединения.
- index_merge_union (по умолчанию on)
  
  Управляет оптимизацией доступа слияния индексов объединения.
Для получения дополнительной информации см. Раздел 10.2.1.3, «Оптимизация слияния индексов».
Флаги видимости индексов
- use_invisible_indexes (по умолчанию off)
  
  Управляет использованием невидимых индексов.
Для получения дополнительной информации см. Раздел 10.3.12, «Невидимые индексы».
Флаги оптимизации LIMIT
- prefer_ordering_index (по умолчанию on)
  
  Управляет тем, будет ли в случае запроса с ORDER BY или GROUP BY с предложением LIMIT оптимизатор пытаться использовать упорядоченный индекс вместо неупорядоченного индекса, сортировки файлов или какой-либо другой оптимизации. Эта оптимизация выполняется по умолчанию всякий раз, когда оптимизатор определяет, что ее использование позволит ускорить выполнение запроса.
  
  Поскольку алгоритм, который принимает это решение, не может обрабатывать все мыслимые случаи (отчасти из-за предположения, что распределение данных всегда более или менее равномерно), есть случаи, когда эта оптимизация может быть нежелательна. Эту оптимизацию можно отключить, установив флаг prefer_ordering_index в значение off.
Для получения дополнительной информации и примеров см. Раздел 10.2.1.19, «Оптимизация запросов LIMIT».
Флаги многодиапазонного чтения
- mrr (по умолчанию on)
  
  Управляет стратегией многодиапазонного чтения.
- mrr_cost_based (по умолчанию on)
  
  Управляет использованием многодиапазонного чтения на основе стоимости, если mrr=on.
Для получения дополнительной информации см. Раздел 10.2.1.11, «Оптимизация многодиапазонного чтения».

Флаги полусоединения
- duplicateweedout (по умолчанию on)
  
  Управляет стратегией полусоединения Duplicate Weedout.
- firstmatch (по умолчанию on)
  
  Управляет стратегией полусоединения FirstMatch.
- loosescan (по умолчанию on)
  
  Управляет стратегией полусоединения LooseScan (не путать с Loose Index Scan для GROUP BY).
- semijoin (по умолчанию on)
  
  Управляет всеми стратегиями полусоединения.
  
  Это также применяется к оптимизации антисоединения.
Флаги semijoin, firstmatch, loosescan и duplicateweedout позволяют управлять стратегиями полусоединения. Флаг semijoin управляет использованием полусоединений. Если он установлен в on, флаги firstmatch и loosescan позволяют более тонко управлять разрешёнными стратегиями полусоединения.

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

Если semijoin и materialization оба on, полусоединения также используют материализацию, где это возможно. Эти флаги по умолчанию on.

Для получения дополнительной информации см. .
Флаги операций над множествами
- hash_set_operations (по умолчанию on)
  
  Включает оптимизацию хеш-таблицы для операций над множествами, включающих EXCEPT и INTERSECT; включено по умолчанию. В противном случае используется дедупликация на основе временных таблиц, как в предыдущих версиях MySQL.
  
  Объём памяти, используемый этой оптимизацией для хеширования, может быть контролируется с помощью системной переменной set_operations_buffer_size; увеличение этого значения обычно приводит к более быстрому выполнению запросов, использующих эти операции.
Флаги пропуска сканирования
- skip_scan (по умолчанию on)
  
  Управляет использованием метода доступа Skip Scan.
Для получения дополнительной информации см. Метод доступа Skip Scan Range.
Флаги материализации подзапросов
- materialization (по умолчанию on)
  
  Управляет материализацией (включая материализацию полусоединений).
- subquery_materialization_cost_based (по умолчанию on)
  
  Использовать выбор материализации на основе затрат.
Флаг materialization управляет использованием материализации подзапросов. Если semijoin и materialization оба on, полусоединения также используют материализацию, где это возможно. Эти флаги по умолчанию on.

Флаг subquery_materialization_cost_based позволяет контролировать выбор между материализацией подзапроса и преобразованием подзапроса из IN в EXISTS. Если флаг on (по умолчанию), оптимизатор выполняет выбор на основе затрат между материализацией подзапроса и преобразованием подзапроса из IN в EXISTS, если любой из методов может быть использован. Если флаг off, оптимизатор выбирает материализацию подзапроса вместо преобразования подзапроса из IN в EXISTS.

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

Флаги преобразования подзапросов

subquery_to_derived (по умолчанию off)

Оптимизатор во многих случаях может преобразовать скалярный подзапрос в SELECT, WHERE, JOIN или HAVING в левое внешнее соединение с производной таблицей. (В зависимости от возможности наличия значений NULL в производной таблице, это иногда можно упростить до внутреннего соединения.) Это может быть выполнено для подзапроса, удовлетворяющего следующим условиям:

Подзапрос не использует недетерминированные функции, такие как RAND().
Подзапрос не является подзапросом ANY или ALL, который можно переписать с использованием MIN() или MAX().
Родительский запрос не устанавливает пользовательскую переменную, так как её переписывание может повлиять на порядок выполнения, что может привести к непредсказуемым результатам, если к переменной обращаются более одного раза в одном запросе.
Подзапрос не должен быть коррелированным, то есть не должен ссылаться на столбец из таблицы во внешнем запросе или содержать агрегат, который оценивается во внешнем запросе.

Эта оптимизация также может быть применена к подзапросу таблицы, который является аргументом IN, NOT IN, EXISTS или NOT EXISTS, который не содержит GROUP BY.

Значение по умолчанию для этого флага off, так как в большинстве случаев включение этой оптимизации не приводит к заметному улучшению производительности (и во многих случаях может даже замедлить выполнение запросов), но вы можете включить оптимизацию, установив флаг subquery_to_derived в значение on. В основном он предназначен для тестирования.

Пример со скалярным подзапросом:

d
mysql> CREATE TABLE t1(a INT);

mysql> CREATE TABLE t2(a INT);

mysql> INSERT INTO t1 VALUES ROW(1), ROW(2), ROW(3), ROW(4);

mysql> INSERT INTO t2 VALUES ROW(1), ROW(2);

mysql> SELECT * FROM t1
    ->     WHERE t1.a > (SELECT COUNT(a) FROM t2);
+------+
| a    |
+------+
|    3 |
|    4 |
+------+

mysql> SELECT @@optimizer_switch LIKE '%subquery_to_derived=off%';
+-----------------------------------------------------+
| @@optimizer_switch LIKE '%subquery_to_derived=off%' |
+-----------------------------------------------------+
|                                                   1 |
+-----------------------------------------------------+

mysql> EXPLAIN SELECT * FROM t1 WHERE t1.a > (SELECT COUNT(a) FROM t2)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 33.33
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: SUBQUERY
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: NULL

mysql> SET @@optimizer_switch='subquery_to_derived=on';


mysql> SELECT @@optimizer_switch LIKE '%subquery_to_derived=off%';
+-----------------------------------------------------+
| @@optimizer_switch LIKE '%subquery_to_derived=off%' |
+-----------------------------------------------------+
|                                                   0 |
+-----------------------------------------------------+

mysql> SELECT @@optimizer_switch LIKE '%subquery_to_derived=on%';
+----------------------------------------------------+
| @@optimizer_switch LIKE '%subquery_to_derived=on%' |
+----------------------------------------------------+
|                                                  1 |
+----------------------------------------------------+

mysql> EXPLAIN SELECT * FROM t1 WHERE t1.a > (SELECT COUNT(a) FROM t2)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 4
     filtered: 33.33
        Extra: Using where; Using join buffer (hash join)
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: NULL

Как видно из выполнения SHOW WARNINGS непосредственно после второго EXPLAIN оператора, при включенной оптимизации запрос SELECT * FROM t1 WHERE t1.a > (SELECT COUNT(a) FROM t2) переписывается в форме, аналогичной показанной здесь:

SELECT t1.a FROM t1
    JOIN  ( SELECT COUNT(t2.a) AS c FROM t2 ) AS d
            WHERE t1.a > d.c;

Пример с использованием запроса с IN (subquery):

mysql> DROP TABLE IF EXISTS t1, t2;

mysql> CREATE TABLE t1 (a INT, b INT);
mysql> CREATE TABLE t2 (a INT, b INT);

mysql> INSERT INTO t1 VALUES ROW(1,10), ROW(2,20), ROW(3,30);
mysql> INSERT INTO t2
    ->    VALUES ROW(1,10), ROW(2,20), ROW(3,30), ROW(1,110), ROW(2,120), ROW(3,130);

mysql> SELECT * FROM t1
    ->     WHERE   t1.b < 0
    ->             OR
    ->             t1.a IN (SELECT t2.a + 1 FROM t2);
+------+------+
| a    | b    |
+------+------+
|    2 |   20 |
|    3 |   30 |
+------+------+

mysql> SET @@optimizer_switch="subquery_to_derived=off";

mysql> EXPLAIN SELECT * FROM t1
    ->             WHERE   t1.b < 0
    ->                     OR
    ->                     t1.a IN (SELECT t2.a + 1 FROM t2)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using where

mysql> SET @@optimizer_switch="subquery_to_derived=on";

mysql> EXPLAIN SELECT * FROM t1
    ->             WHERE   t1.b < 0
    ->                     OR
    ->                     t1.a IN (SELECT t2.a + 1 FROM t2)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ref
possible_keys: <auto_key0>
          key: <auto_key0>
      key_len: 9
          ref: std2.t1.a
         rows: 2
     filtered: 100.00
        Extra: Using where; Using index
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using temporary

Проверка и упрощение результата SHOW WARNINGS после выполнения EXPLAIN на этом запросе показывает, что при включенном флаге subquery_to_derived, SELECT * FROM t1 WHERE t1.b < 0 OR t1.a IN (SELECT t2.a + 1 FROM t2) переписывается в форме, аналогичной показанной здесь:

SELECT a, b FROM t1
    LEFT JOIN (SELECT DISTINCT a + 1 AS e FROM t2) d
    ON t1.a = d.e
    WHERE   t1.b < 0
            OR
            d.e IS NOT NULL;

Пример с использованием запроса с EXISTS (subquery) и теми же таблицами и данными, что и в предыдущем примере:

mysql> SELECT * FROM t1
    ->     WHERE   t1.b < 0
    ->             OR
    ->             EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1);
+------+------+
| a    | b    |
+------+------+
|    1 |   10 |
|    2 |   20 |
+------+------+

mysql> SET @@optimizer_switch="subquery_to_derived=off";

mysql> EXPLAIN SELECT * FROM t1
    ->             WHERE   t1.b < 0
    ->                     OR
    ->                     EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 16.67
        Extra: Using where

mysql> SET @@optimizer_switch="subquery_to_derived=on";

mysql> EXPLAIN SELECT * FROM t1
    ->             WHERE   t1.b < 0
    ->                     OR
    ->                     EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using where; Using join buffer (hash join)
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using temporary

Если мы выполним SHOW WARNINGS после запуска EXPLAIN на запросе SELECT * FROM t1 WHERE t1.b < 0 OR EXISTS(SELECT * FROM t2 WHERE t2.a = t1.a + 1), когда subquery_to_derived включен, и упростим вторую строку результата, мы увидим, что она переписана в форме, подобной этой:

SELECT a, b FROM t1
LEFT JOIN (SELECT DISTINCT 1 AS e1, t2.a AS e2 FROM t2) d
ON t1.a + 1 = d.e2
WHERE   t1.b < 0
        OR
        d.e1 IS NOT NULL;

Для получения дополнительной информации см. Раздел 10.2.2.4, «Оптимизация производных таблиц, ссылок на представления и общих табличных выражений с объединением или материализацией», а также Раздел 10.2.1.19, «Оптимизация запросов LIMIT», и .

При присвоении значения optimizer_switch, флаги, которые не указаны, сохраняют свои текущие значения. Это позволяет включить или выключить определённые оптимизационные поведенческие характеристики в одном операторе, не затрагивая другие. Оператор не зависит от того, какие другие оптимизационные флаги существуют и каковы их значения. Предположим, что все оптимизации Index Merge включены:

mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
                    index_merge_sort_union=on,index_merge_intersection=on,
                    engine_condition_pushdown=on,index_condition_pushdown=on,
                    mrr=on,mrr_cost_based=on,block_nested_loop=on,
                    batched_key_access=off,materialization=on,semijoin=on,
                    loosescan=on, firstmatch=on,
                    subquery_materialization_cost_based=on,
                    use_index_extensions=on,condition_fanout_filter=on,
                    derived_merge=on,use_invisible_indexes=off,skip_scan=on,
                    hash_join=on,subquery_to_derived=off,
                    prefer_ordering_index=on

Если сервер использует методы доступа Index Merge Union или Index Merge Sort-Union для определённых запросов, и вы хотите проверить, может ли оптимизатор работать лучше без них, установите значение переменной так:

mysql> SET optimizer_switch='index_merge_union=off,index_merge_sort_union=off';

mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=off,
                    index_merge_sort_union=off,index_merge_intersection=on,
                    engine_condition_pushdown=on,index_condition_pushdown=on,
                    mrr=on,mrr_cost_based=on,block_nested_loop=on,
                    batched_key_access=off,materialization=on,semijoin=on,
                    loosescan=on, firstmatch=on,
                    subquery_materialization_cost_based=on,
                    use_index_extensions=on,condition_fanout_filter=on,
                    derived_merge=on,use_invisible_indexes=off,skip_scan=on,
                    hash_join=on,subquery_to_derived=off,
                    prefer_ordering_index=on