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

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

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

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

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

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

Флаг block_nested_loop системной переменной optimizer_switch управляет объединениями по хешу.

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

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

Сведения о стратегиях полусоединения см. в

Исходная реализация алгоритма BNL MySQL была расширена для поддержки операций внешнего соединения и полусоединения (позже она была заменена алгоритмом объединения по хешу; см. Раздел 10.2.1.4, «Оптимизация объединений по хешу»).

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

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

Флаг block_nested_loop системной переменной optimizer_switch управляет объединениями по хешу.

Дополнительная информация содержится в Разделе 10.9.2, «Переключаемые оптимизации». Также могут применяться подсказки оптимизатору; см. Рекомендации оптимизатору для алгоритмов блочного вложенного цикла и пакетного доступа к ключам.

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

Сведения о стратегиях полусоединения см. в

MySQL реализует метод соединения таблиц, называемый алгоритмом поочередного доступа по ключу (BKA). Алгоритм BKA может быть применён, когда доступ к таблице, полученной от второго операнда соединения, осуществляется по индексу. Как и алгоритм BNL, алгоритм BKA использует буфер соединения для накопления интересных столбцов строк, полученных от первого операнда операции соединения. Затем алгоритм BKA строит ключи для доступа к таблице, подлежащей соединению, для всех строк в буфере и отправляет эти ключи в базу данных для поиска по индексу в пакетной форме. Ключи отправляются в движок через интерфейс многодиапазонного чтения (MRR) (см. Раздел 10.2.1.11, «Многодиапазонное чтение (MRR)»). После отправки ключей, функции движка 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 поддерживает указания оптимизатору для влияния на оптимизатор на уровне каждого оператора. См. Раздел 10.9.3, «Указания оптимизатору».

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