Понимание хеш-соединений в MySQL 8

Пересказ статьи Tibor Korocz. Understanding Hash Joins in MySQL 8

В MySQL 8.0.18 появилась новая функция, которая называется Hash Join, и я захотел посмотреть, как она работает, и в каких ситуациях она может нам помочь.

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

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

Во-первых, это работает только на неиндексированных полях, что сразу приводит к сканированию таблицы, и мы обычно не рекомендуем выполнять соединения без индексов, поскольку это медленно. Именно здесь 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 планирует это сделать.

Добавить комментарий