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