Введение в оптимизацию запросов

Операции соединения

Соединение отношений является наиболее часто выполняемой операцией в любой реляционной базе данных. Она также может требовать для выполнения много времени из-за того, что наиболее простейшая реализация операции соединения требует многократного просмотра всех строк одной из таблиц. SQLBase планирует выполнение этой операции исходя из ряда альтернативных путей доступа к данным. Все используемые операции имеют на входе две таблицы и одну таблицу на выходе. Эти входные и выходные таблицы могут быть комбинацией таблиц БД, временных таблиц и результирующего множества.

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

К операциям соединения относят следующие физические операции.

Простой алгоритм соединения в цикле (Simple loop join). Этот алгоритм является простейшим прямолинейным методом соединения двух таблиц. Он может иметь наихудшую стоимость выполнения для операции соединения, особенно если входные таблицы большие. Одно из преимуществ простого соединения в цикле состоит в том, что он может быть использован независимо от типа критерия соединения, наличия или отсутствия индексной структуры. Также существуют ситуации, где данный алгоритм может иметь лучшую производительность, в частности, если одна из входных таблиц помещается в кэш-память.

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

Благодаря вложенной циклической структуре метода наихудший случай для числа операций ввода/вывода есть число страниц данных во внутренней таблице, умноженное на число страниц данных во внешней таблице. Это может быть не так драматично, если внутренняя таблица невелика и полностью размещается в кэш-памяти. Тогда число операций ввода/вывода сокращается до суммы числа страниц данных внешней и внутренней таблиц. По этой причине SQLBase всегда выбирает меньшую из двух таблиц в качестве внутренней для размещения ее в кэш-памяти. Когда кэш-памяти достаточно для размещения одной из таблиц, то этот метод становится более предпочтительным по стоимости, чем другие.

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

Циклическое соединение с индексом (Loop join with index). Вариант вложенного циклического соединения может быть применен, когда существует индекс для одной из таблиц, который построен на колонках соединения по возрастанию значений ключа. Когда есть такая ситуация, оптимизатор использует таблицу с индексом как внутреннюю таблицу для алгоритма циклического соединения, и задействует индекс для того, чтобы ограничить поиск строк, соответствующих текущей удерживаемой строке из внешней таблицы. Это значительно уменьшает число операций ввода/вывода, необходимых для обработки внутренней таблицы. Наилучшим случаем является ситуация, когда внутренняя таблица кластеризована и обрабатывается эквисоединение, тогда объем ввода/вывода равен сумме числа страниц данных в двух таблицах, плюс высота дерева индекса, плюс число строк во внешней таблице. Последние две компоненты стоимости вычисляются для оценки доступа к индексной структуре во время реализации соединения строки.

Циклическое соединение с хэш-индексом (Loop join with hash index). Эта разновидность метода циклического соединения может применяться, когда одна из соединяемых таблиц имеет CHI. Два других требования к этому методу состоят в том, что хэш-индекс должен быть создан для всех колонок из критерия соединения (и никаких других) и соединение должно являться эквисоединением. Когда эти условия выполняются, этот метод соединения может быть очень эффективным. Наихудший случай есть ситуация, когда число операций в/в равно числу страниц данных во внешней таблице плюс число строк во внутренней таблице. Это происходит только в случае ввода/вывода, если каждая строка во внутренней таблице ссылается на внешнюю таблицу. Когда не все строки внутренней таблицы ссылаются на внешнюю таблицу, то число операций ввода/вывода ограничивается.

Соединение посредством объединения индекса (Index merge join). Этот метод может быть использован, когда существуют индексы для колонок критерия соединения обеих таблиц. Основной алгоритм состоит в сканировании подмножества последовательности этих двух индексов и объединении строк из таблицы, основываясь на соответствие ее критерия соединения. Например, когда обрабатывается эквисоединение, то сначала вход индекса читается из последовательности листьев внешней таблицы. Затем вход читается из последовательности листьев внутренней таблицы. Если колонка соединения внутренней таблицы меньше, чем внешней, то сканирование внутренней таблицы продолжается. Если больше, то сканируется последовательность листьев внешней таблицы до тех пор, пока значение, равное или большее, не будет найдено. Когда колонки соединения в обоих индексах удовлетворяют критерию соединения, строки таблиц читаются из страниц данных, добавляются одна к другой и записываются в выходную таблицу.

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

Соединение хэширования (Hash join). Этот метод может быть использован только для эквисоединения и не требует каких-либо индексов для колонок критерия соединения двух таблиц. Алгоритм сначала выполняет фазу установки, когда сканируется внешняя таблица и каждая строка помещается в хэш-таблицу согласно значению хэш-функции для критерия соединения. Наименьшая таблица всегда выбирается как внешняя, для того чтобы минимизировать память под хэш-таблицу.

На второй фазе, называемой проба (probe), внутренняя таблица сканируется, и хэш-функция используется для выборки возможных значений. Любая строка, найденная в хэш-бакете для первой таблицы, затем проверяется. Строки, которые удовлетворяют критерию поиска, добавляются к строке внутренней таблицы и записываются в выходную таблицу.

Это может быть очень быстрым методом соединения, особенно когда меньшая таблица может быть хэширована в таблицу в оперативной памяти. Когда это возможно, стоимость ввода/вывода равна сумме страниц данных двух таблиц. С другой стороны. дополнительный ввода/вывода требуется при разбиении хэш-таблицы на сегменты, а также для управления ими во временном файле на диске.