ИТ Блог. Администрирование серверов на основе Linux (Ubuntu, Debian, CentOS, openSUSE)
В течение долгого времени единственным алгоритмом для выполнения объединения в MySQL были варианты алгоритма вложенного цикла. С выпуском MySQL 8.0.18 сервер теперь может выполнять соединения, используя хэш-соединение. В этом посте будет рассказано о том, как он работает, когда он используется и как он сравнивается со старыми алгоритмами соединения в MySQL с точки зрения производительности.
Хеш-соединение – это способ выполнения соединения, где хеш-таблица используется для поиска совпадающих строк между двумя входными данными (входные данные представляют собой одну или несколько таблиц). Обычно это более эффективно, чем соединения с вложенными циклами, особенно если один из входов может поместиться в памяти. Чтобы увидеть, как это работает, мы будем использовать следующий запрос в качестве примера:
SELECT given_name, country_name FROM persons JOIN countries ON persons.country_id = countries.country_id;
Литература обычно делит хеш-соединение на две фазы; фаза сборки и фаза зонда. На этапе сборки сервер создает хеш-таблицу в памяти, где хранятся строки из одного из входных данных, используя атрибуты соединения в качестве ключа хеш-таблицы. Этот вход также известен как входные данные при сборке, и давайте предположим, что «countries» обозначены как входные данные при сборке. В идеале сервер будет выбирать меньший из двух входов в качестве входных данных для сборки (измеряется в байтах, а не в количестве строк). Так как ‘country.country_id’ является условием соединения, которое принадлежит входу сборки, оно используется в качестве ключа в хэш-таблице. Как только все строки будут сохранены в хэш-таблице, этап сборки завершен.
Во время фазы проверки сервер начинает чтение строк с входа проверки ( в нашем примере это «persons» ). Для каждой строки сервер проверяет хеш-таблицу на соответствие строк, используя значение из «people.country_id» в качестве ключа поиска. Для каждого совпадения присоединенная строка отправляется клиенту. В конце концов, сервер отсканировал каждый вход только один раз, используя поиск с постоянным временем, чтобы найти совпадающие строки между двумя входами.
Это работает очень хорошо, учитывая, что сервер может хранить весь ввод данных сборки в памяти. Объем доступной памяти контролируется системной переменной join_buffer_size и может быть изменен во время выполнения. Но что произойдет, если вход для сборки будет больше доступной памяти? Мы пишем на диск!
Когда память заполняется во время фазы сборки, сервер записывает оставшуюся часть данных сборки в несколько блоков на диске. Сервер пытается установить количество блоков так, чтобы самый большой блок точно помещался в памяти (скоро мы увидим, почему), но MySQL имеет жесткий верхний предел в 128 блоков на вход. В какой файл блоков записана строка, определяется путем вычисления хэша атрибута соединения. Обратите внимание, что на иллюстрации используется другая хеш-функция, чем та, которая используется на этапе построения в памяти. Мы увидим почему чуть позже.
Во время фазы проверки сервер проверяет соответствие строк в хэш-таблице, точно так же, как когда все умещается в памяти. Но кроме того, строка может также соответствовать одной из строк из входных данных сборки, которые записываются на диск. Таким образом, каждая строка из входных данных зонда также записывается в набор блоков файлов. В какой файл блока записывается строка, определяется с помощью той же хеш-функции и формулы, которая используется при записи входных данных сборки на диск. Таким образом, мы точно знаем, что совпадающие строки между двумя входами будут расположены в одной и той же паре файлов блоков.
Когда фаза проверки завершена, мы начинаем читать блок файлов с диска. Как правило, сервер выполняет фазу сборки и проверки, используя первый набор файлов блока в качестве входных данных для сборки и проверки. Мы загружаем первый файл блока из входных данных сборки в хэш-таблицу в памяти. Это объясняет, почему мы хотим, чтобы самый большой кусок помещался точно в памяти; если файлы слишком велики, нам нужно разделить их на еще более мелкие куски. После загрузки фрагмента сборки мы читаем соответствующий файл фрагмента из входных данных зонда и проверяем совпадения в хэш-таблице, точно так же, как когда все умещается в памяти. Когда первая пара блоков обрабатывается, мы переходим к следующей паре блоков, продолжая до тех пор, пока не будет обработана вся пара блоков.
Возможно, вы уже догадались, почему мы должны использовать две разные хеш-функции при разбиении строк на файлы и записи строк в хеш-таблицу. Если бы мы использовали одну и ту же хеш-функцию для обеих операций, мы получили бы очень плохую хеш-таблицу при загрузке блок файл сборки в хеш-таблицу, поскольку все строки в одном и том же блоке хешировали бы одно и то же значение.
Хеш-соединение включено по умолчанию, поэтому для использования хеш-соединения никаких действий не требуется. Одна заметная вещь заключается в том, что хеш-соединение строится на новом исполнителе итератора , что означает, что вы можете (и должны) использовать EXPLAIN FORMAT = tree, чтобы увидеть, будет ли использоваться хеш-соединение:
mysql> EXPLAIN FORMAT=tree -> SELECT -> given_name, country_name -> FROM -> persons JOIN countries ON persons.country_id = countries.country_id; +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Inner hash join (countries.country_id = persons.country_id) (cost=0.70 rows=1) -> Table scan on countries (cost=0.35 rows=1) -> Hash -> Table scan on persons (cost=0.35 rows=1) | +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)
В общем случае хеш-соединение будет использоваться, если вы объединяете таблицы, используя одно или несколько условий равного соединения, и для условий соединения нет доступных индексов. Если индекс доступен, MySQL вместо этого предпочитает вложенный цикл с поиском по индексу.
Мы представили новый переключатель оптимизатора, который позволяет отключить хеш-соединение для любого запроса:
mysql> SET optimizer_switch="hash_join=off"; Query OK, 0 rows affected (0.00 sec) mysql> EXPLAIN FORMAT=tree -> SELECT -> given_name, country_name -> FROM -> persons JOIN countries ON persons.country_id = countries.country_id; +----------------------------------------+ | EXPLAIN | +----------------------------------------+ | | +----------------------------------------+ 1 row in set (0.00 sec)
При отключенном хэш-соединении MySQL вернется к циклу с вложенными блоками и, таким образом, к старому исполнителю (цикл с вложенными блоками не поддерживается в итераторе-исполнителе). Этот переключатель упрощает сравнение производительности хеш-соединения и циклического вложенного цикла.
Если вы видите, что хеш-соединение использует диск из-за того, что входные данные сборки слишком велики для размещения в памяти, можно увеличить размер буфера объединения . В отличие от циклически вложенного цикла, хеш-соединение будет выделять память постепенно, а это означает, что оно никогда не будет использовать больше памяти, чем нужно. По этой причине безопаснее играть с буфером большего размера при использовании хеш-соединения.
Перед Percona Live Europe 2019 мы провели несколько тестов, чтобы увидеть, как хеш-соединение сравнивается с циклически вложенным циклом, и результаты выглядят так:
Вы можете увидеть представление результатов здесь .Прежде всего, мы должны отметить, что мы отключили все индексы в этом тесте. Это было сделано для того, чтобы оптимизатор создавал планы выполнения с использованием циклического вложенного цикла и хеш-соединения, поэтому цифры, которые вы видите здесь, не отображают общего улучшения времени выполнения DBT-3. Этот тест был сделан, чтобы подчеркнуть разницу между циклически вложенным циклом и хеш-соединением. Но мы видим, что хеш-соединение явно превосходит циклически вложенный цикл во всех запросах, где оно используется. Размер пула буферов был отрегулирован таким образом, чтобы все данные находились в памяти, а размер буфера объединения не изменился по сравнению со значением по умолчанию (около 250 КБ). Значительное улучшение связано с тем, что хеш-соединение сканирует каждый вход только один раз и использует постоянный поиск по времени для поиска совпадающих строк между двумя таблицами.
К сожалению, в текущей реализации хеш-соединения есть несколько ограничений:
Мы надеемся снять оба эти ограничения в будущем, но хеш-соединение должно ускорить выполнение ваших запросов даже с этими двумя ограничениями.
Тэги: , MySQL, MySQL 8.0, база данных Категории: MySQL
