Когда я вчера днем проверял информацию, я случайно нажал на официальный сайт MySQL. Выяснил, что MySQL выпустила новую версию.
Кто-нибудь не знаком с базой данных Mysql? нет нужды? нет.
В конце 2019 года MySQL выпустила версию 8.0.18 GA, в которой появились некоторые новые функции и улучшения. Одним из самых ярких является запрос соединения нескольких таблиц.Поддержка хеш-соединения.
Это все то же самое, я предлагаю студентам с хорошим английским читать прямо здесь: https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html
Особенности MySQL Hash Join:
- 1. Для ассоциаций таблиц с большими объемами данных HJ (Hash Join) будет значительно быстрее, чем NL (Nested Loop).
- 2. Процесс в памяти
- 3. Дисковое пространство будет использовано при необходимости
- 4. Используется для внутреннего соединения, может быть расширен до внешнего соединения, полусоединения и антисоединения.
- 5. Замените блочный вложенный цикл в плане запроса
- 6. Вы можете заставить SQL перейти в HJ или NL через HINT
Некоторые студенты, возможно, были ошеломлены. Что такое хэш-соединение? Что такое НЛ? Какой к черту ПОДСКАЗКА?
Первая часть - сделать простую науку
Прежде всего, в совместном запросе нескольких таблиц, если мы посмотрим на его план выполнения, мы найдем метод соединения между несколькими таблицами. Существует три способа объединения нескольких таблиц: вложенные циклы, хеш-соединение и сортировка слиянием.
Некоторые люди, должно быть, говорили, что в спецификациях Alibaba указано, что многотабличный запрос нельзя использовать в параллельных случаях. Насколько вы одновременны? Серверная часть любой системы будет использовать многотабличный совместный запрос.
Hash Join используется при присоединении к SQL-части Spark и Flink. Ранее мы публиковали статью:
[Три реализации Spark SQL Join].
Соединение по хэшу Соединение по хешу — это распространенный способ объединения больших наборов данных для CBO, который обычно подходит для соединения между большими и малыми таблицами. Вообще говоря, используйте маленькую таблицу для создания хэш-таблицы в памяти с помощью ключа соединения (КЛЮЧ СОЕДИНЕНИЯ), сохраните данные столбца в списке хэшей, а затем просмотрите большую таблицу, а также выполните ХЭШ на КЛЮЧЕ СОЕДИНЕНИЯ, чтобы обнаружить хеш-таблицу, чтобы найти строки, соответствующие хеш-таблице.
Некоторые студенты снова были ошеломлены. Что такое ЦБО? Мы не будем его здесь расширять, просто говоря, CBO — это метод оптимизации SQL, который оценит план выполнения в соответствии с реальной ситуацией с данными и выберет план выполнения с наименьшими затратами.
Что такое план выполнения? Baidu go... [Черное лицо со знаком вопроса]
Так что же такое вложенные циклы? Проще говоря, это двухслойный цикл.Первая таблица используется как внешний цикл, а вторая таблица используется как внутренний цикл.Каждая запись внешнего цикла сравнивается с записями внутреннего цикла, чтобы выяснить данные, соответствующие условиям. Конечно, у вложенных циклов есть несколько ситуаций. Возьмем самый простой пример, псевдокод выглядит следующим образом:
for (r in R) {
for (s in S) {
if (r satisfy condition s) {
output <r, s>;
}
}
}
Что такое подсказка? Английское слово Hint означает подсказку. Проще говоря, подсказка очень похожа на комментарии, когда мы разрабатываем код.Комментарии в коде должны напоминать разработчику или другим людям о значении этого кода. Тогда эта подсказка будет играть особую роль в SQL: это подсказка к базе данных, указывающая, что я надеюсь, что база данных будет выполняться в соответствии с моей подсказкой. Здесь нет примера.
Книга возвращается к основному тексту Как использовать Hash Join в новой версии MySQL?
Используем непосредственно пример с официального сайта.
Предположим, у нас есть три таблицы:
CREATE TABLE t1 (c1 INT, c2 INT);
CREATE TABLE t2 (c1 INT, c2 INT);
CREATE TABLE t3 (c1 INT, c2 INT);
Существует простой запрос ассоциации таблиц:
SELECT *
FROM t1
JOIN t2
ON t1.c1=t2.c1;
Мы используемEXPLAIN FORMAT=TREEКоманда может увидеть план выполнения вышеуказанного SQL:
mysql> EXPLAIN FORMAT=TREE
-> SELECT *
-> FROM t1
-> JOIN t2
-> ON t1.c1=t2.c1\G
*************************** 1. row ***************************
EXPLAIN: -> 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)
мы видим ключевые словаInner hash joinЭто означает, что этот SQL использует Hash Join.
Кроме того, эту оптимизацию также будут выполнять запросы, использующие эквивалентные соединения между несколькими таблицами. Например, следующий запрос:
SELECT *
FROM t1
JOIN t2
ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)
JOIN t3
ON (t2.c1 = t3.c1);
Глядя на вывод команды EXPLAIN FORMAT=TREE, мы также можем обнаружить, что условие неэквивалентности в конце станет фильтром.
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)
Вы также можете видеть из приведенного выше журнала, что если ваш SQL содержит несколько эквивалентных соединений, то MySQL будет использовать несколько хэш-соединений.
Но, обратите внимание! Если условие on в вашем SQL не является эквивалентным соединением, то хеш-соединение не будет использоваться.
Например:
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: <not executable by iterator executor>
Давайте ОБЪЯСНИМ посмотрим:
mysql> EXPLAIN
-> SELECT *
-> FROM t1
-> JOIN t2
-> ON (t1.c1 = t2.c1)
-> JOIN t3
-> ON (t2.c1 < t3.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 (Block Nested Loop)
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: t3
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 (Block Nested Loop)
Видите ли, сейчас MySQL выберет Nested Loop.
Запросы декартовых произведений также могут использовать HJ:
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 будет максимально использовать Hash Join. Он также предоставляет два метода управления использованием Hash Join. Например, если мне не нравится HJ, мне нравится NL Turtle Join, а затем менеджер проекта просит открыть запрос HJ во время оптимизации, разве это не здорово?
Выберите один из двух способов:
- Глобально установите системную переменную сервера hash_join=on
- Указать HASH с подсказкой в SQLПРИСОЕДИНЯЙТЕСЬ или НЕТHASH_JOIN
Что еще более удивительно, так это то, что сам HJ не зависит от индекса, а это означает, что даже без оптимизации индекса HJ по-прежнему очень быстр.
Ниже приведен тест, который я нашел у других людей в Интернете, чтобы показать силу HJ.
Сначала сгенерируйте 1000000 записей для t1, t2 и t3 соответственно:
set join_buffer_size=2097152000;
SET @@cte_max_recursion_depth = 99999999;
INSERT INTO t1
-- INSERT INTO t2
-- INSERT INTO t3
WITH RECURSIVE t AS (
SELECT 1 AS c1, 1 AS c2
UNION ALL
SELECT t.c1 + 1, t.c1 * 2
FROM t
WHERE t.c1 < 1000000
)
SELECT *
FROM t;
Хэш-соединение без индекса:
mysql> EXPLAIN ANALYZE
-> SELECT COUNT(*)
-> FROM t1
-> JOIN t2
-> ON (t1.c1 = t2.c1)
-> JOIN t3
-> ON (t2.c1 = t3.c1)\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0) (actual time=22993.098..22993.099 rows=1 loops=1)
-> Inner hash join (t3.c1 = t1.c1) (cost=9952535443663536.00 rows=9952435908880402) (actual time=14489.176..21737.032 rows=1000000 loops=1)
-> Table scan on t3 (cost=0.00 rows=998412) (actual time=0.103..3973.892 rows=1000000 loops=1)
-> Hash
-> Inner hash join (t2.c1 = t1.c1) (cost=99682753413.67 rows=99682653660) (actual time=5663.592..12236.984 rows=1000000 loops=1)
-> Table scan on t2 (cost=0.01 rows=998412) (actual time=0.067..3364.105 rows=1000000 loops=1)
-> Hash
-> Table scan on t1 (cost=100539.40 rows=998412) (actual time=0.133..3395.799 rows=1000000 loops=1)
1 row in set (23.22 sec)
mysql> SELECT COUNT(*)
-> FROM t1
-> JOIN t2
-> ON (t1.c1 = t2.c1)
-> JOIN t3
-> ON (t2.c1 = t3.c1);
+----------+
| COUNT(*) |
+----------+
| 1000000 |
+----------+
1 row in set (12.98 sec)
Фактический пробег занял 12,98 секунды. В настоящее время, если вы используете блочный вложенный цикл:
mysql> EXPLAIN FORMAT=TREE
-> SELECT /*+ NO_HASH_JOIN(t1, t2, t3) */ COUNT(*)
-> FROM t1
-> JOIN t2
-> ON (t1.c1 = t2.c1)
-> JOIN t3
-> ON (t2.c1 = t3.c1)\G
*************************** 1. row ***************************
EXPLAIN: <not executable by iterator executor>
1 row in set (0.00 sec)
SELECT /*+ NO_HASH_JOIN(t1, t2, t3) */ COUNT(*)
FROM t1
JOIN t2
ON (t1.c1 = t2.c1)
JOIN t3
ON (t2.c1 = t3.c1);
EXPLAIN показывает, что хэш-соединение использовать нельзя. Запрос выполнялся в течение десятков минут безрезультатно, а загрузка ЦП одного из них достигла 100%, поскольку вложенный цикл (1000000 в 3-й степени) был выполнен.
Посмотрите на метод вложенного цикла блока, когда есть индекс, и увеличьте индекс:
mysql> CREATE index idx1 ON t1(c1);
Query OK, 0 rows affected (7.39 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> CREATE index idx2 ON t2(c1);
Query OK, 0 rows affected (6.77 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> CREATE index idx3 ON t3(c1);
Query OK, 0 rows affected (7.23 sec)
Records: 0 Duplicates: 0 Warnings: 0
Просмотрите план выполнения и запустите тот же оператор запроса:
mysql> EXPLAIN ANALYZE
-> SELECT COUNT(*)
-> FROM t1
-> JOIN t2
-> ON (t1.c1 = t2.c1)
-> JOIN t3
-> ON (t2.c1 = t3.c1)\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0) (actual time=47684.034..47684.035 rows=1 loops=1)
-> Nested loop inner join (cost=2295573.22 rows=998412) (actual time=0.116..46363.599 rows=1000000 loops=1)
-> Nested loop inner join (cost=1198056.31 rows=998412) (actual time=0.087..25788.696 rows=1000000 loops=1)
-> Filter: (t1.c1 is not null) (cost=100539.40 rows=998412) (actual time=0.050..5557.847 rows=1000000 loops=1)
-> Index scan on t1 using idx1 (cost=100539.40 rows=998412) (actual time=0.043..3253.769 rows=1000000 loops=1)
-> Index lookup on t2 using idx2 (c1=t1.c1) (cost=1.00 rows=1) (actual time=0.012..0.015 rows=1 loops=1000000)
-> Index lookup on t3 using idx3 (c1=t1.c1) (cost=1.00 rows=1) (actual time=0.012..0.015 rows=1 loops=1000000)
1 row in set (47.68 sec)
mysql> SELECT COUNT(*)
-> FROM t1
-> JOIN t2
-> ON (t1.c1 = t2.c1)
-> JOIN t3
-> ON (t2.c1 = t3.c1);
+----------+
| COUNT(*) |
+----------+
| 1000000 |
+----------+
1 row in set (19.56 sec)
Фактический пробег занял 19,56 секунды. Итак, результаты теста в нашем сценарии следующие:
Добавьте еще один результат Hash Join, когда в Oracle 12c нет индекса: 1,282 с. Добавить еще один результат Hash Join при отсутствии индекса в PostgreSQL 11.5: 6,234 с. Добавить еще один результат хэш-соединения, когда в SQL Server 2017 нет индекса: 5,207 с.
Видите мощь Hash Join? Ты учил?
Обратите внимание на мой официальный аккаунт и ответьте на [JAVAPDF] в фоновом режиме, чтобы получить 200 страниц тестовых вопросов!Большие данные, на которые обращают внимание 50 000 человек, — это дорога к Богу, почему бы вам не прийти и не узнать об этом?50 000 человек обращают внимание на то, как большие данные становятся богом, разве вы не хотите узнать об этом?50 000 человек обращают внимание на то, как стать богом больших данных, вы уверены, что действительно не хотите прийти и узнать об этом?