Я полагаю, что в реальной разработке большинство людей будет использовать соединение для запросов к связанным таблицам, но некоторые люди считают, что использование соединения кажется очень неэффективным, а время выполнения отличается для разных управляющих таблиц. Итак, как именно работает объединение?

Вот две таблицы t1, t2

explain select * from t1 left join t2 on t1.a=t2.a;

Вышеприведенный оператор использует левое соединение, указывая, что t1 является управляющей таблицей (кто находится в левом объединении и кто является управляющей таблицей), а t2 является управляемой таблицей, выполните ее.

Видно, что тип ведущей таблицы — ВСЕ, поэтому это полное сканирование таблицы. Ведомая таблица имеет индекс а. Когда выполняется левое соединение, используется индекс а, поэтому поток выполнения этого оператора является:

1. Считайте строку данных из таблицы t1
2. Из строки данных выньте поле a в таблицу t2, чтобы найти
3. Выньте строки, которые соответствуют условиям в таблице t2, и сформируйте строку с данными t1 как часть набора результатов.
4. Повторяйте вышеуказанную операцию до тех пор, пока данные t1 не будут исчерпаны.

Этот процесс заключается в том, чтобы сначала просмотреть таблицу t1, а затем запросить записи, которые соответствуют условиям в таблице t2, в соответствии со значением a в каждой строке данных, извлеченных из таблицы t1. Здесь мы становимся "Index Nested-Loop Join" или сокращенно NLJ.

Как упоминалось выше, если мы выбираем таблицу драйверов, мы должны выбрать небольшую таблицу в качестве таблицы драйверов. В противном случае придется запрашивать все большие таблицы в качестве ведущей, и эффективность будет намного ниже. Конечно, предполагается, что «можно использовать индекс управляемой таблицы».

Здесь мы меняем оператор sql:

explain select * from t1 left join t2 on t1.a=t2.b;

Поле b таблицы t2 не индексируется

В результате обе таблицы нуждаются в полном сканировании таблицы.Здесь мы видим, что Extra показывает (Using where; Using join buffer (Block Nested Loop))

На самом деле это оптимизация соединения MySQL без полного сканирования таблицы, называемая BNL.

Процесс БНЛ:

1. Читаем данные таблицы t1 в память потока join_buffer, здесь мы помещаем в память всю таблицу t1.
2. Просканируйте таблицу t2, извлеките каждую строку в таблице t2, сравните ее с данными в join_buffer и верните те, которые удовлетворяют условиям соединения, как часть результирующего набора.

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

Если данные в таблице слишком велики, чтобы вместить все данные вообще, они будут размещены в сегментах. Вы также можете изменить join_buffer_size.

Для индексированных управляемых таблиц в MySQL 5.6 добавлены новые функции пакетного доступа к ключу (BKA).

Для оператора соединения нескольких таблиц, когда MySQL использует индекс для доступа к первой таблице соединения, он использует join_buffer для сбора значений соответствующих столбцов, сгенерированных первым объектом операции. После того, как BKA создаст ключ, он отправляет его механизму для запроса через интерфейс MRR.

Шаги БКА:

1. Поместите соответствующие столбцы управляющей таблицы в файл join_buffer.
2. Отправка ключа (значение ключа индекса) в интерфейс MRR пакетами.
3. MRR сортирует полученный ключ по соответствующему ROWID, а затем считывает данные.

Здесь BKA и BNL на самом деле похожи.Основное отличие состоит в том, что BKA предназначен для случая, когда управляемая таблица индексируется, а когда индекс не является индексом первичного ключа, он сортируется в соответствии с полем индекса, тем самым уменьшая случайные ввода-вывода и повышения производительности.

MRR

Оптимизация Multi-Range Read (MRR), поддерживаемая MySQL 5.6. Цель MRR — уменьшить произвольный доступ к диску и преобразовать произвольный доступ в более последовательный доступ к данным MRR можно применять к запросам типа range, ref, eq_ref.

Оптимизация MRR имеет следующие преимущества:

1. MRR делает доступ к данным более последовательным. При запросе к вспомогательному индексу сначала по полученным результатам запроса выполнить сортировку по первичному ключу, а затем искать закладки в порядке первичного ключа.
2. Уменьшите количество замен страниц в пуле буферов.
3. Пакетная обработка операций запроса по ключевым значениям

Идея дизайна MRR заключается в том, что большая часть данных вставляется в порядке возрастания первичного ключа, поэтому мы можем думать, что если запрос выполняется в порядке возрастания первичного ключа, чтение на диск происходит ближе к последовательному чтению, что может улучшить производительность чтения.

Суть способности MRR повышать производительность заключается в том, что этот оператор запроса выполняет запрос диапазона по индексу a (то есть это запрос с несколькими значениями) и может быть получено достаточное количество идентификаторов первичного ключа. Таким образом, после сортировки перейдите к индексу первичного ключа, чтобы проверить данные, чтобы отразить «последовательное» преимущество.