- •Оптимизация sql-запросов
- •Последовательность оптимизации запросов и используемые законы реляционной алгебры
- •Основные шаги оптимизации
- •Законы реляционной алгебры
- •Построение логического плана
- •Преобразование запроса в формулу реляционной алгебры
- •Оптимизация формулы реляционной алгебры
- •Иллюстрация логической оптимизации
- •Пример построения логического плана
- •Оптимизация запроса
- •Порядок выполнения запроса на логическом уровне
- •Построение физического плана
- •Шаги построения физического плана
- •Отличие физического плана от логического
- •Методы выбора записей из исходной таблицы
- •1. Чтение всех записей таблицы и их фильтрация.
- •2. Чтение записей с помощью индекса и их фильтрация.
- •Оценка числа кортежей в промежуточной таблице q
- •Оценка числа блоков в промежуточной таблице q
- •Порядок соединения таблиц
- •Левостороннее дерево соединений
- •Кустовое дерево соединений
- •Правостороннее дерево соединений
- •Методы соединения таблиц
- •Метод вложенных циклов (nlj – Nested Loop Join)
- •Метод сортировки-слияния (smj – Sort Merge Join)
- •Метод хешированного соединения (hj – Hash Join)
- •Число кортежей, блоков и мощности атрибутов в соединении
- •Поиск физического плана с минимальной стоимостью
- •Алгоритм поиска для левостороннего дерева соединений
- •Формат экземпляра структуры данных
- •Спецификации процедуры AccessPlan
- •Спецификации процедуры JoinPlan
- •Спецификации процедуры OptPlanReturn
- •Пример построения оптимального физического плана
- •Логический план
- •Алгоритм поиска оптимального физического плана
- •Определение метода выбора записей из исходной таблицы r1
- •Определение метода выбора записей из исходной таблицы r2
- •Оценка соединения q1 и q2 методом nlj
- •Оценка соединения q1 и q2 методом smj
- •Выбор метода соединения q1 и q2 и заполнение структуры
- •Оценка и выбор метода соединения q2 и q1
- •Вывод физического плана
Порядок выполнения запроса на логическом уровне
1. Определение промежуточных таблиц Q1 и Q2 .
На основе полученной формулы
(5.3)
определяются промежуточные таблицы Q1 и Q2 :
1.
Из таблицы R1 (см. Рис. 1 .4) получим
Q1: номер_счета
-
102
2.
Из таблицы R2 (см. Рис. 1 .4) получим
Q2: номер_счета остаток
-
101
2000
102
3000
2. Соединение промежуточных таблиц Q1 и Q2 .
Выполняется соединение промежуточных таблиц Q1 и Q2 и передача результата клиенту. При этом реализуется формула
:
а) Q = Q1Q2 :
R1.номер_счета R2.номер_счета R2.остаток
102 |
101 |
2000 |
102 |
102 |
3000 |
б) Z = R1.номер_счета=R2.номер_счета(Q):
R1.номер_счета R2.номер_счета R2.остаток
-
102
102
3000
в) R2.остаток(Z):
остаток
-
3000
Эта величина остатка посылается клиенту.
Примечание. Если таблицы R1 и R2 хранятся на других различных серверах S1 и S2, то подзапросы Q1 и Q2 преобразуются в запросы SELECT и направляются на серверы S1 и S2, где параллельно выполняются.
Подзапрос Q1 преобразуется к виду:
SELECT номер_счета
FROM R1
WHERE код_пользователя=3;
Подзапрос Q2 преобразуется к виду:
SELECT номер_счета, остаток
FROM R2
WHERE остаток > 1500;
После выполнения запросов SELECT результаты возвращаются на исходный сервер СУБД, и там выполняется их соединение.
Построение физического плана
Шаги построения физического плана
При построении оптимального физического плана выполнения SQL-запроса (дерева физических операций) выполняются следующие действия:
1. Последовательно строится множество физических планов на основе логического плана (см. разделы 1.2, 1.3). Физические планы в основном отличаются следующим:
а) методом выбора записей из исходных таблиц (методом реализации подзапросов );
б) порядком соединения таблиц;
в) методом соединения таблиц (т.е. методом реализации операции естественного соединения ).
2. Для каждого физического плана рассчитывается стоимость его выполнения. Выбирается физический план с наименьшей стоимостью.
Отличие физического плана от логического
Логический план не указывает на то, как должны быть реализованы операции реляционной алгебры (проекция, селекция, декартово произведение, естественное соединение). Физический план определяет, как эти операции будут реализовываться.
Например, логический план может быть реализован с помощью физического плана, представленного на Рис. 1 .6.
Рис. 1.6. Логический и физический планы выполнения запроса.
Физический план выполняется в следующей последовательности.
1. Здесь операции реализуются следующим образом: читается вся таблица R1 (TableScan(R1)) и затем каждая запись проверяется, удовлетворяет ли она логическому условию F1, и если да, то из нее выделяются атрибуты, входящие в множество A1 ( ). Получившаяся промежуточная таблица является правым аргументом соединения.
2. Операции реализуются следующим образом: с помощью индекса читаются записи таблицы R2, удовлетворяющие подусловию φ (IndexScan(R2, φ)). Здесь φ – некоторое подусловие условия F2 по атрибуту, который имеет индекс.
Примеры подусловий φ: a = 5, a > 5, a ≥ 5, a < 5, a ≤ 5 (a – индексированный атрибут таблицы R2).
Считанные записи фильтруются ( ). Получившаяся промежуточная таблица является левым аргументом соединения.
3. Выполняется естественное соединение промежуточных таблиц, полученных на первом и втором шагах, методом сортировки и слияния (SMJ); тут же из получившегося результата выделяется множество атрибутов A.
Оптимизатор для каждого физического плана рассчитывает стоимость. Эта стоимость равна сумме составляющих. Для данного примера имеем:
Каждая из этих составляющих, в свою очередь, включает процессорную составляющую и составляющую дискового ввода-вывода:
C = CCPU + CI/O .
После перебора нескольких физических планов оптимизатор выбирает план с минимальной стоимостью.