По умолчанию MySQL использует хэш-соединения по возможности. Можно управлять использованием хэш-соединений, используя один из оптимизационных подсказок BNL и NO_BNL, или задав block_nested_loop=on или block_nested_loop=off в качестве части настройки системной переменной сервера optimizer_switch.

MySQL использует хэш-соединение для любого запроса, в котором каждое соединение имеет условие эквисоединения и в котором нет индексов, которые могут быть применены к условиям соединения, например, в этом:

SELECT *
    FROM t1
    JOIN t2
        ON t1.c1=t2.c1;

Хэш-соединение также может использоваться, когда имеется один или несколько индексов, которые могут использоваться для предикатов для одной таблицы.

В приведенном выше примере и в оставшихся примерах в этом разделе мы предполагаем, что три таблицы t1, t2 и t3 были созданы с помощью следующих операторов:

CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);
CREATE TABLE t3 (c1 INT, c2 INT);

Вы можете увидеть, что хэш-соединение используется, используя EXPLAIN, например так:

mysql> EXPLAIN
    -> SELECT * FROM t1
    ->     JOIN t2 ON t1.c1=t2.c1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   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: SIMPLE
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where; Using join buffer (hash join)

EXPLAIN ANALYZE также отображает информацию об используемых хэш-соединениях.

Хэш-соединение используется и для запросов, включающих несколько соединений, если условие соединения хотя бы для одной пары таблиц является эквисоединением, например, в запросе, показанном здесь:

SELECT * FROM t1
    JOIN t2 ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)
    JOIN t3 ON (t2.c1 = t3.c1);

В таких случаях, как показанный выше, использующий внутреннее соединение, все дополнительные условия, которые не являются эквисоединениями, применяются как фильтры после выполнения соединения. (Для внешних соединений, таких как левые соединения, полусоединения и антисоединения, они выводятся как часть соединения.) Это можно увидеть в выводе EXPLAIN:

mysql> EXPLAIN FORMAT=TREE
    -> SELECT *
    ->     FROM t1
    ->     JOIN t2
    ->         ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)
    ->     JOIN t3
    ->         ON (t2.c1 = t3.c1)\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join (t3.c1 = t1.c1)  (cost=1.05 rows=1)
    -> Table scan on t3  (cost=0.35 rows=1)
    -> Hash
        -> Filter: (t1.c2 < t2.c2)  (cost=0.70 rows=1)
            -> Inner hash join (t2.c1 = t1.c1)  (cost=0.70 rows=1)
                -> Table scan on t2  (cost=0.35 rows=1)
                -> Hash
                    -> Table scan on t1  (cost=0.35 rows=1)

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

Хэш-соединение используется, даже если у любой пары соединённых таблиц нет хотя бы одного условия эквисоединения, как показано здесь:

mysql> EXPLAIN FORMAT=TREE
    -> SELECT * FROM t1
    ->     JOIN t2 ON (t1.c1 = t2.c1)
    ->     JOIN t3 ON (t2.c1 < t3.c1)\G
*************************** 1. row ***************************
EXPLAIN: -> Filter: (t1.c1 < t3.c1)  (cost=1.05 rows=1)
    -> Inner hash join (no condition)  (cost=1.05 rows=1)
        -> Table scan on t3  (cost=0.35 rows=1)
        -> Hash
            -> Inner hash join (t2.c1 = t1.c1)  (cost=0.70 rows=1)
                -> Table scan on t2  (cost=0.35 rows=1)
                -> Hash
                    -> Table scan on t1  (cost=0.35 rows=1)

(Дополнительные примеры приведены позднее в этом разделе.)

Хэш-соединение также применяется для декартова произведения — то есть, когда не указано никакого условия соединения, как показано здесь:

mysql> EXPLAIN FORMAT=TREE
    -> SELECT *
    ->     FROM t1
    ->     JOIN t2
    ->     WHERE t1.c2 > 50\G
*************************** 1. row ***************************
EXPLAIN: -> Inner hash join  (cost=0.70 rows=1)
    -> Table scan on t2  (cost=0.35 rows=1)
    -> Hash
        -> Filter: (t1.c2 > 50)  (cost=0.35 rows=1)
            -> Table scan on t1  (cost=0.35 rows=1)

Для использования хэш-соединения не обязательно, чтобы соединение содержало хотя бы одно условие эквисоединения. Это означает, что типы запросов, которые могут быть оптимизированы с помощью хэш-соединений, включают те, что в следующем списке (с примерами):

По умолчанию MySQL использует хэш-соединения по возможности. Можно управлять использованием хэш-соединений, используя один из оптимизационных подсказок BNL и NO_BNL.

Использование памяти хэш-соединениями можно контролировать с помощью системной переменной join_buffer_size; хэш-соединение не может использовать больше памяти, чем это количество. Если объем памяти, необходимый для хэш-соединения, превышает доступный объем, MySQL обрабатывает это, используя файлы на диске. В этом случае следует знать, что соединение может не удастся, если хэш-соединение не помещается в память, и оно создает больше файлов, чем задано для open_files_limit. Чтобы избежать таких проблем, внесите одно из следующих изменений:

Буферы соединений для хэш-соединений выделяется по частям; таким образом, вы можете установить join_buffer_size выше, не заставляя небольшие запросы выделять очень большие объемы оперативной памяти, но внешние соединения выделяют весь буфер. Хэш-соединения используются и для внешних соединений (включая антисоединения и полусоединения), поэтому это больше не проблема.