MySQL может использовать буферы объединения не только для выполнения внутренних объединений без доступа к внутренней таблице по индексу, но также для внешних объединений и полуобъединений, которые появляются после выравнивания подзапросов. Более того, буфер объединения может быть эффективно использован при доступе к внутренней таблице по индексу.

Код управления буфером объединения несколько более эффективно использует пространство буфера объединения при хранении значений интересных столбцов строки: дополнительные байты не выделяются в буферах для столбца строки, если его значение равно NULL, и минимальное количество байтов выделяется для любого значения типа VARCHAR.

Код поддерживает два типа буферов: обычные и инкрементные. Предположим, что буфер объединения B1 используется для объединения таблиц t1 и t2, а результат этой операции объединяется с таблицей t3 с помощью буфера объединения B2:

Инкрементные буферы объединения всегда инкрементны относительно буфера объединения из предыдущей операции объединения, поэтому буфер из первой операции объединения всегда является обычным буфером. В приведенном выше примере буфер B1, используемый для объединения таблиц t1 и t2, должен быть обычным буфером.

Каждая строка инкрементного буфера, используемого для операции объединения, содержит только интересные столбцы строки из таблицы, которую требуется объединить. Эти столбцы дополняются ссылкой на интересные столбцы соответствующей строки из таблицы, полученной в результате первой операции объединения. Несколько строк в инкрементном буфере могут ссылаться на одну и ту же строку r, столбцы которой хранятся в предыдущих буферах объединения, поскольку все эти строки соответствуют строке r.

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

Флаги block_nested_loop и batched_key_access системной переменной optimizer_switch контролируют, как оптимизатор использует алгоритмы объединения с блочно-вложенным циклом и пакетным доступом по ключу. По умолчанию, block_nested_loop — on, а batched_key_access — off. См. раздел 8.9.2 «Переключаемые оптимизации». Также могут применяться подсказки оптимизатору; см. Подсказки оптимизатору для алгоритмов блочно-вложенного цикла и пакетного доступа по ключу.

Сведения о стратегиях полуобъединения см. в разделе 8.2.2.1 «Оптимизация подзапросов, производных таблиц и ссылок на представления с преобразованиями полуобъединения».

Исходная реализация алгоритма MySQL BNL расширена для поддержки внешних объединений и полуобъединений.

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

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

Флаг block_nested_loop системной переменной optimizer_switch управляет тем, как оптимизатор использует алгоритм блочно-вложенного цикла. По умолчанию, block_nested_loop — on. См. раздел 8.9.2 «Переключаемые оптимизации». Также могут применяться подсказки оптимизатору; см. Подсказки оптимизатору для алгоритмов блочно-вложенного цикла и пакетного доступа по ключу.

В выводе EXPLAIN использование BNL для таблицы обозначается, когда значение Extra содержит Using join buffer (Block Nested Loop), а значение type равно ALL, index или range.

Некоторые случаи, связанные с комбинацией одного или нескольких подзапросов с одним или несколькими левыми объединениями, особенно те, которые возвращают много строк, могут использовать BNL, даже если он не является идеальным в таких случаях. Это известная проблема, которая исправлена в MySQL 8.0. Если для вас нецелесообразно немедленно обновлять MySQL, вы можете временно отключить BNL, установив optimizer_switch='block_nested_loop=off' или используя подсказку оптимизатору NO_BNL, чтобы позволить оптимизатору выбрать лучший план, используя одну или несколько подсказок по индексу (см. раздел 8.9.4 «Подсказки по индексу»), или их комбинацию, для повышения производительности таких запросов.

Сведения о стратегиях полуобъединения см. в разделе 8.2.2.1 «Оптимизация подзапросов, производных таблиц и ссылок на представления с преобразованиями полуобъединения».

MySQL реализует метод соединения таблиц, называемый алгоритмом пакетного доступа по ключу (BKA). Алгоритм BKA может применяться, когда доступ к таблице обеспечивается по индексу, созданному вторым операндом соединения. Как и алгоритм BNL, алгоритм BKA использует буфер соединения для накопления интересных столбцов строк, созданных первым операндом операции соединения. Затем алгоритм BKA строит ключи для доступа к таблице, подлежащей соединению, для всех строк в буфере, и отправляет эти ключи в пакет в движок базы данных для поиска по индексу. Ключи передаются движку через интерфейс многодиапазонного чтения (MRR) (см. раздел 8.2.1.10, «Многодиапазонное чтение»). После отправки ключей функции движка MRR выполняют поиск по индексу оптимальным образом, извлекая строки соединенной таблицы, найденные по этим ключам, и начинают подавать алгоритму BKA соответствующие строки. Каждая соответствующая строка связывается со ссылкой на строку в буфере соединения.

При использовании BKA значение join_buffer_size определяет размер пакета ключей в каждом запросе к хранилищу. Чем больше буфер, тем более последовательно происходит доступ к правой таблице операции соединения, что может значительно улучшить производительность.

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

mysql> SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';

Существует два сценария, в которых выполняются функции MRR:

В первом сценарии часть буфера соединения резервируется для хранения идентификаторов строк (первичных ключей), выбранных в результате поиска по индексу и переданных в качестве параметра функциям MRR.

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

В выводе EXPLAIN использование BKA для таблицы обозначается, когда значение Extra содержит Using join buffer (Batched Key Access), а значение type равно ref или eq_ref.

В дополнение к использованию системной переменной optimizer_switch для управления использованием алгоритмов BNL и BKA на уровне сессии, MySQL поддерживает указания оптимизатору, чтобы повлиять на оптимизатор на уровне каждого оператора. См. раздел 8.9.3, «Указания оптимизатору».

Для использования подсказки BNL или BKA для включения буферизации соединения для любой внутренней таблицы внешнего соединения, буферизация соединения должна быть включена для всех внутренних таблиц внешнего соединения.