8.9.2 Switchable Optimizations — MySQL 5.7

8.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,
                    prefer_ordering_index=on

Чтобы изменить значение 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.

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

Для получения дополнительной информации см. Раздел 8.2.2.4, «Оптимизация производных таблиц и ссылок на представления с помощью слияния или материализации».
Флаги пересылки условий ядра
- engine_condition_pushdown (по умолчанию on)
  
  Управляет пересылкой условий ядра.
Для получения дополнительной информации см. Раздел 8.2.1.4, «Оптимизация пересылки условий ядра».
Флаги пересылки условий индекса
- index_condition_pushdown (по умолчанию on)
  
  Управляет пересылкой условий индекса.
Для получения дополнительной информации см. Раздел 8.2.1.5, «Оптимизация пересылки условий индекса».
Флаги расширений индексов
- use_index_extensions (по умолчанию on)
  
  Управляет использованием расширений индексов.
Для получения дополнительной информации см. Раздел 8.3.9, «Использование расширений индексов».
Флаги слияния индексов
- index_merge (по умолчанию on)
  
  Управляет всеми оптимизациями слияния индексов.
- index_merge_intersection (по умолчанию on)
  
  Управляет оптимизацией доступа к пересечению слияния индексов.
- index_merge_sort_union (по умолчанию on)
  
  Управляет оптимизацией доступа к сортировке-объединению слияния индексов.
- index_merge_union (по умолчанию on)
  
  Управляет оптимизацией доступа к объединению слияния индексов.
Для получения дополнительной информации см. Раздел 8.2.1.3, «Оптимизация слияния индексов».
Флаги оптимизации LIMIT
- prefer_ordering_index (по умолчанию on)
  
  Управляет тем, пытается ли оптимизатор использовать упорядоченный индекс вместо неупорядоченного индекса, сортировки файлов или какой-либо другой оптимизации в случае запроса с ORDER BY или GROUP BY с предложением LIMIT. Эта оптимизация выполняется по умолчанию всякий раз, когда оптимизатор определяет, что ее использование позволит ускорить выполнение запроса.
  
  Поскольку алгоритм, который принимает это решение, не может обрабатывать все мыслимые случаи (отчасти из-за предположения, что распределение данных всегда более или менее равномерно), есть случаи, когда эта оптимизация может быть нежелательна. До MySQL 5.7.33 отключить эту оптимизацию было невозможно, но в MySQL 5.7.33 и более поздних версиях, хотя она остается поведением по умолчанию, ее можно отключить, установив флаг prefer_ordering_index в off.
Для получения дополнительной информации и примеров см. Раздел 8.2.1.17, «Оптимизация запросов LIMIT».
Флаги многодиапазонного чтения
- mrr (по умолчанию on)
  
  Управляет стратегией многодиапазонного чтения.
- mrr_cost_based (по умолчанию on)
  
  Управляет использованием основанного на стоимости MRR, если mrr=on.
Для получения дополнительной информации см. Раздел 8.2.1.10, «Оптимизация многодиапазонного чтения».
Флаги полусоединения
- duplicateweedout (по умолчанию on)
  
  Управляет стратегией удаления дубликатов полусоединения.
- 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 по умолчанию.

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

Флаги материализации подзапросов
- materialization (по умолчанию on)
  
  Управляет материализацией (включая материализацию полусоединений).
- subquery_materialization_cost_based (по умолчанию on)
  
  Использовать выбор материализации на основе затрат.
Флаг materialization управляет использованием материализации подзапросов. Если флаги semijoin и materialization оба установлены on, полусоединения также используют материализацию там, где это применимо. По умолчанию эти флаги установлены on.

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

Более подробную информацию см. в разделе 8.2.2 «Оптимизация подзапросов, производных таблиц и ссылок на представления».

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

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,
                    prefer_ordering_index=on

Если сервер использует методы доступа «Слияние индексов UNION» или «Слияние индексов 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,duplicateweedout=on,
                    subquery_materialization_cost_based=on,
                    use_index_extensions=on,
                    condition_fanout_filter=on,derived_merge=on,
                    prefer_ordering_index=on