SQL-Ex blog

Прекрасно, но даст ли это прирост производительности?

Во-первых, это работает только на неиндексированных полях, что сразу приводит к сканированию таблицы, и мы обычно не рекомендуем выполнять соединения без индексов, поскольку это медленно. Именно здесь hash join в MySQL может помочь, поскольку будет использоваться хеш-таблица в памяти вместо соединения вложенными циклами (nested loops join).

Давайте выполним несколько тестов. Сначала я создам следующие таблицы:

CREATE TABLE `t1` (

`id` int(11) NOT NULL AUTO_INCREMENT ,

`c1` int(11) NOT NULL DEFAULT '0',

`c2` int(11) NOT NULL DEFAULT '0',

PRIMARY KEY (`id`),

KEY `idx_c1` (`c1`)

) ENGINE=InnoDB;

CREATE TABLE `t2` (

`id` int(11) NOT NULL AUTO_INCREMENT ,

`c1` int(11) NOT NULL DEFAULT '0',

`c2` int(11) NOT NULL DEFAULT '0',

PRIMARY KEY (`id`),

KEY `idx_c1` (`c1`)

) ENGINE=InnoDB;

Я вставил 131072 случайных строк в обе таблицы.

mysql> select count(*) from t1;

+----------+

| count(*) |

+----------+

| 131072   |

+----------+

Первый тест - Hash Join

Выполним соединение по неиндексированному столбцу c2.

mysql> explain format=tree select count(*) from t1 join t2 on t1.c2 = t2.c2\G

*************************** 1. row ***************************

EXPLAIN: -> Aggregate: count(0)

-> Inner hash join (t2.c2 = t1.c2) (cost=1728502115.04 rows=1728488704)

-> Table scan on t2 (cost=0.01 rows=131472)

-> Hash

-> Table scan on t1 (cost=13219.45 rows=131472)

1 row in set (0.00 sec)

Мы должны использовать explain format=tree, чтобы увидеть, будет использован hash join или нет. Иначе это будет вложенный цикл, который, я думаю, вводит в заблуждение. Я отправил разработчикам баг-репорт, и в ответ получил:

The solution is to stop using traditional EXPLAIN (it will eventually go away).

Т.е. традиционный оператор EXPLAIN вскоре будет исключен, а пока рекомендуется использовать новый.

Вернемся к запросу. Мы видим, что для него предполагается использовать hash join, но насколько он быстрый?

mysql> select count(*) from t1 join t2 on t1.c2 = t2.c2;

+----------+

| count(*) |

+----------+

| 17172231 |

+----------+

1 row in set (0.73 sec)

0,73с для более чем 17 миллионов строк в соединенной таблице. Выглядит многообещающе.

Второй тест - без Hash Join

Мы можем запретить hash join с помощью хинта оптимизатора.

mysql> select /*+ NO_HASH_JOIN (t1,t2) */ count(*) from t1 join t2 on t1.c2 = t2.c2;

+----------+

| count(*) |

+----------+

| 17172231 |

+----------+

1 row in set (13 min 36.36 sec)

Теперь тот же самый запрос выполняется более 13 минут. Это огромная разница, и мы видим, что хеш-соединение может существенно помочь в подобных случаях.

Третий тест - Join на базе индексов

Давайте создадим индексы и посмотрим, насколько быстрым будет соединение по индексированным столбцам.

create index idx_c2 on t1(c2);

create index idx_c2 on t2(c2);

mysql> select count(*) from t1 join t2 on t1.c2 = t2.c2;

+----------+

| count(*) |

+----------+

| 17172231 |

+----------+

1 row in set (2.63 sec)

2,6 с

Hash Join в этом случае оказывается даже быстрей чем соединение на базе индексов.

Однако я могу заставить оптимизатор использовать Hash Join, даже если имеется индекс с помощью ignore index:

mysql> explain format=tree select count(*) from t1 ignore index (idx_c2) join t2 ignore index (idx_c2) on t1.c2 = t2.c2 

where t1.c2=t2.c2\G

*************************** 1. row ***************************

EXPLAIN: -> Aggregate: count(0)

-> Inner hash join (t2.c2 = t1.c2) (cost=1728502115.04 rows=17336898)

-> Table scan on t2 (cost=0.00 rows=131472)

-> Hash

-> Table scan on t1 (cost=13219.45 rows=131472)

1 row in set (0.00 sec)

Я также думаю, что будет прекрасно, если бы я смог сказать оптимизатору при помощи хинта использовать Hash Join, даже если имеется индекс. Этот способ не заставил бы игнорировать индексы на всех таблицах. Я отправил еще один запрос разработчикам по этому поводу.

Однако из ответа на мой первый баг-репорт следует, что, возможно, в этом может не будет необходимости:

BNL (Block Nested-Loop) will also go away entirely at some point, at which point this hint will be ignored.

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

Ограничения

Как видно, Hash Join может стать мощным средством, хотя имеются некоторые ограничения:

• Как уже упоминалось, это работает только на столбцах, которые не имеют индексов (или вы должны их игнорировать).


• Это работает только для экви-соединений.


• Это не работает для LEFT или RIGHT JOIN.

Заключение

Hash Join, судя по всему, оказывается очень мощным новым типом выполнения соединения, и будем продолжать следить за развитием событий, поскольку я не удивлюсь, если последуют и другие возможности. В теории, следует ожидать применения к левому и правому соединениям, т.к. Oracle планирует это сделать.